I'm working on a project where all queries are like this one. It works perfectly but I think is quite hard to understand. What do you think is this a coding horror?
SELECT DISTINCT
TDB_Child.id_childtdb,
TDB_Child.id_tdb,
TDB_Child_Horizontal.dim5,
ISNULL ((SELECT description FROM TDB_ChildDesc WHERE (id_childtdb = TDB_Child.id_childtdb) AND (id_lang = @Language) AND (id_tdb = @Template) AND (id_dim = 'PDC_SINT')),'[DESC - ' + CAST(TDB_Child.id_childtdb AS nvarchar) + ']') AS childtdb_Description,
ISNULL((SELECT description FROM TDB_ChildDesc AS TDB_ChildDesc_3 WHERE (id_childtdb = TDB_Child_Horizontal.dim5) AND (id_lang = @Language) AND (id_tdb = @Template) AND (id_dim = 'COM_PROP')), '[DESC - ' + CAST(TDB_Child_Horizontal.dim5 AS nvarchar) + ']') AS childtdb_Description2,
TDB_Child.flag,
TDB_Child.ordine,
(SELECT ISNULL(SUM(valore), 0) AS Expr1 FROM TDB_FlatTable AS Act WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal.dim5) AND (dim3 = 'R4') AND (dim8 = TDB_FlatTable.dim8) AND (dim2 = @Period)) AS R4,
(SELECT ISNULL(SUM(valore), 0) AS Expr1 FROM TDB_FlatTable AS Act WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal.dim5) AND (dim3 = '3YP2008-2010') AND (dim8 = TDB_FlatTable.dim8) AND (dim2 = @Period)) AS Budget,
(SELECT ISNULL(SUM(valore), 0) AS Expr1 FROM TDB_FlatTable AS Act WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year - 1) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal.dim5) AND (dim3 = 'Consuntivo') AND (dim8 = TDB_FlatTable.dim8) AND (dim2 = @Period)) AS Consuntivo,
TDB_Child_Horizontal.Ordine2,
CAST(0 AS NUMERIC) AS Delta
FROM TDB_Child
INNER JOIN (SELECT DISTINCT TDB_Child_5.id_childtdb, TDB_H.dim5, TDB_H.Ordine2 FROM TDB_Child AS TDB_Child_5 CROSS JOIN (SELECT id_childtdb AS dim5, ordine AS Ordine2 FROM TDB_Child AS TDB_Child_4 WHERE (id_report = 'CE') AND (id_tdb = @Template) AND (id_dim = 'COM_PROP') AND (visibile = 1)) AS TDB_H WHERE (TDB_Child_5.id_tdb = @Template) AND (TDB_Child_5.id_dim = 'PDC_SINT') AND (TDB_Child_5.id_report = 'CE') AND (TDB_Child_5.visibile = 1)) AS TDB_Child_Horizontal ON TDB_Child.id_childtdb = TDB_Child_Horizontal.id_childtdb
LEFT OUTER JOIN TDB_FlatTable ON TDB_Child.id_childtdb = TDB_FlatTable.dim8
WHERE
(TDB_Child.id_tdb = @Template) AND
(TDB_Child.id_dim = 'PDC_SINT') AND
(TDB_Child.id_report = 'CE') AND
(TDB_Child.id_childtdb NOT IN ('Taxes', 'NetIncome', '<SPACES6>', '<SPACES7>', 'ROS')) AND (TDB_Child.visibile = 1)
UNION
SELECT
id_childtdb,
id_tdb,
dim5,
childtdb_Description,
childtdb_Description2,
flag,
ordine,
CASE R4_PV WHEN 0 THEN 0 ELSE R4_GM / R4_PV * 100 END AS R4,
CASE Budget_PV WHEN 0 THEN 0 ELSE Budget_GM / Budget_PV * 100 END AS Budget,
CASE Consuntivo_PV WHEN 0 THEN 0 ELSE Consuntivo_GM / Consuntivo_PV * 100 END AS Consuntivo,
Ordine2,
CAST(0 AS NUMERIC) AS Delta
FROM (SELECT DISTINCT TDB_Child_3.id_childtdb, TDB_Child_3.id_tdb, TDB_Child_Horizontal_1.dim5, ISNULL
((SELECT description
FROM TDB_ChildDesc AS TDB_ChildDesc_2
WHERE (id_childtdb = TDB_Child_3.id_childtdb) AND (id_lang = @Language) AND (id_tdb = @Template) AND (id_dim = 'PDC_SINT')),
'[DESC - ' + CAST(TDB_Child_3.id_childtdb AS nvarchar) + ']') AS childtdb_Description, ISNULL
((SELECT description
FROM TDB_ChildDesc AS TDB_ChildDesc_1
WHERE (id_childtdb = TDB_Child_Horizontal_1.dim5) AND (id_lang = @Language) AND (id_tdb = @Template) AND
(id_dim = 'COM_PROP')), '[DESC - ' + CAST(TDB_Child_Horizontal_1.dim5 AS nvarchar) + ']') AS childtdb_Description2,
TDB_Child_3.flag, TDB_Child_3.ordine,
(SELECT ISNULL(SUM(valore), 0) AS Expr1
FROM TDB_FlatTable AS Act
WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5) AND
(dim3 = 'R4') AND (dim8 = 'ProductionValue') AND (dim2 = @Period)) AS R4_PV,
(SELECT ISNULL(SUM(valore), 0) AS Expr1
FROM TDB_FlatTable AS Act
WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5) AND
(dim3 = '3YP2008-2010') AND (dim8 = 'ProductionValue') AND (dim2 = @Period)) AS Budget_PV,
(SELECT ISNULL(SUM(valore), 0) AS Expr1
FROM TDB_FlatTable AS Act
WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year - 1) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5)
AND (dim3 = 'Consuntivo') AND (dim8 = 'ProductionValue') AND (dim2 = @Period)) AS Consuntivo_PV,
(SELECT ISNULL(SUM(valore), 0) AS Expr1
FROM TDB_FlatTable AS Act
WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5) AND
(dim3 = 'R4') AND (dim8 = 'GrossOperatingMargin') AND (dim2 = @Period)) AS R4_GM,
(SELECT ISNULL(SUM(valore), 0) AS Expr1
FROM TDB_FlatTable AS Act
WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5) AND
(dim3 = '3YP2008-2010') AND (dim8 = 'GrossOperatingMargin') AND (dim2 = @Period)) AS Budget_GM,
(SELECT ISNULL(SUM(valore), 0) AS Expr1
FROM TDB_FlatTable AS Act
WHERE (dim10 LIKE @Country + '%') AND (dim1 = @Year - 1) AND (dim6 <> 'TotaleBu') AND (dim5 = TDB_Child_Horizontal_1.dim5)
AND (dim3 = 'Consuntivo') AND (dim8 = 'GrossOperatingMargin') AND (dim2 = @Period)) AS Consuntivo_GM,
TDB_Child_Horizontal_1.Ordine2, CAST(0 AS NUMERIC) AS Delta
FROM TDB_Child AS TDB_Child_3 INNER JOIN
(SELECT DISTINCT TDB_Child_2.id_childtdb, TDB_H_1.dim5, TDB_H_1.Ordine2
FROM TDB_Child AS TDB_Child_2 CROSS JOIN
(SELECT id_childtdb AS dim5, ordine AS Ordine2
FROM TDB_Child AS TDB_Child_1
WHERE (id_report = 'CE') AND (id_tdb = @Template) AND (id_dim = 'COM_PROP') AND (visibile = 1)) AS TDB_H_1
WHERE (TDB_Child_2.id_tdb = @Template) AND (TDB_Child_2.id_dim = 'PDC_SINT') AND (TDB_Child_2.id_report = 'CE') AND
(TDB_Child_2.visibile = 1)) AS TDB_Child_Horizontal_1 ON
TDB_Child_3.id_childtdb = TDB_Child_Horizontal_1.id_childtdb LEFT OUTER JOIN
TDB_FlatTable AS TDB_FlatTable_1 ON TDB_Child_3.id_childtdb = TDB_FlatTable_1.dim8
WHERE (TDB_Child_3.id_tdb = @Template) AND (TDB_Child_3.id_dim = 'PDC_SINT') AND (TDB_Child_3.id_childtdb IN ('ROS')) AND
(TDB_Child_3.id_report = 'CE') AND (TDB_Child_3.visibile = 1)) AS ROS
ORDER BY TDB_Child.ordine, TDB_Child_Horizontal.Ordine2
|