26
Views
4
Comments
Solved
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 :)

Rank: #95
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

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