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