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