SQL WITH Query isn't working...
Question
Application Type
Reactive

Hello everyone.

I am trying to run an Advanced SQL Query that uses a WITH statement, but it's not working, and I truly can't figure out why :(

Here's my query:

WITH nonformatted_result AS(

   WITH productos_laboratorio AS(

      SELECT {Productos}.[Id],

      {Productos}.[Nombre] AS nombre,

      {Productos}.[Funcion] AS id_funcion,

      {Productos}.[Laboratorio]

      FROM {Productos}

      WHERE {Productos}.[Laboratorio] = @IdLaboratorio

   )

   SELECT {FuncionesProductos}.[Id] AS id_funcion, {FuncionesProductos}.[Nombre] AS funcion,

   productos_laboratorio.nombre AS producto

   FROM {FuncionesProductos}

   LEFT JOIN productos_laboratorio

   ON {FuncionesProductos}.[Id] = productos_laboratorio.id_funcion

)

SELECT id_funcion, funcion

   CASE

      WHEN producto IS NULL THEN FALSE

      ELSE TRUE

   END AS available

FROM nonformatted_result;


(Please excuse the name of the columns in spanish)


And I am getting these errors:


Any help would be truly appreciated :)

mvp_badge
MVP
Solution

Hello Yizuhi,

Hope you are doing well.

I see some problems in your query, I will try to resume them :)


1) You have some problems in the syntax of the keyword WITH

The correct syntax for WITH clause is something like:

WITH t1 AS
(
    SELECT * FROM Table
),
t2 AS
(
    SELECT * FROM t1
)
SELECT * FROM t2

This means that you need to change the code of your sequential WITH clauses to follow this syntax.


2) You're missing a comma in the last select, right before of the keyword CASE

After the field funcion, you should add a comma:

SELECT id_funcion, funcion,
    CASE
        WHEN producto IS NULL THEN FALSE
        ELSE TRUE
    END AS available
FROM nonformatted_result;


3) This is optional, but I'd suggest that you add some additional column names for each WITH clause just to make the query easier to read


Your query should result in something like this:


WITH productos_laboratorio (Id, nombre, id_funcion, Laboratorio) AS(

      SELECT {Productos}.[Id],

      {Productos}.[Nombre] AS nombre,

      {Productos}.[Funcion] AS id_funcion,

      {Productos}.[Laboratorio]

      FROM {Productos}

      WHERE {Productos}.[Laboratorio] = @IdLaboratorio

   ),

nonformatted_result (id_funcion, funcion, producto) AS(

   SELECT {FuncionesProductos}.[Id] AS id_funcion, {FuncionesProductos}.[Nombre] AS funcion,

   productos_laboratorio.nombre AS producto

   FROM {FuncionesProductos}

   LEFT JOIN productos_laboratorio

   ON {FuncionesProductos}.[Id] = productos_laboratorio.id_funcion

)

SELECT id_funcion, funcion,

   CASE

      WHEN producto IS NULL THEN FALSE

      ELSE TRUE

   END AS available

FROM nonformatted_result;


Hope that this helps you!


Kind regards,

Rui Barradas

Totally agreed, nice explanation

The query should be like the one mentioned in item 3

Thanks so so much, that worked perfectly.

Hi Yizuhi, 


With clause does not support nesting, instead you can achieve same with subsequent one. Rui has provided correct syntax and explained well, hope that works fine. If not, I will try it in next.


Regards,

Sanjay

Hello, if someone is looking for information on how to use WITH (CTE) in advanced SQL,

Watch this video; https://youtu.be/GwOnfaIpvoI

See examples in this component; https://www.outsystems.com/forge/component-overview/10200/sql-advanced-query-samples-for-dummies


Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.