Is this a coding horror?
-
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 DeltaFROM 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.dim8WHERE
(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', '< -
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 DeltaFROM 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.dim8WHERE
(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', '<Definately a coding horror, but my favorite part is I now know all the table names for your database :)
-
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 DeltaFROM 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.dim8WHERE
(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', '< -
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 DeltaFROM 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.dim8WHERE
(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', '<Looks straightforward but rather tedious to read...I'd hate to be the guy testing it correctness :rolleyes:
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
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 DeltaFROM 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.dim8WHERE
(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', '< -
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 DeltaFROM 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.dim8WHERE
(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', '<It doesn't look terribly much worse than some of the more complicated queries that we have in our application... Despite it's name, it's not that easy to write an SQL query in a structured way...
Despite everything, the person most likely to be fooling you next is yourself.
-
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 DeltaFROM 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.dim8WHERE
(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', '< -
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 DeltaFROM 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.dim8WHERE
(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', '<Not really, your's should be automatically generated by a query tool. So you don't care about the query text but its graphical representation. Just have a look at products like Business Object ou DataStage and you will see what I mean ;)
-
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 DeltaFROM 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.dim8WHERE
(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', '<It's....beautiful
-
It's....beautiful
-
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 DeltaFROM 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.dim8WHERE
(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', '<Yes. It might work perfectly, but it isn't (a) easy to read/understand (b) commented, and is therefore (c) not easy to maintain/debug. I would probably rewrite all the sub-queries as either Views (if reused in the database elsewhere) or as sub-select statements.
'Howard
-
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 DeltaFROM 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.dim8WHERE
(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', '<Sometimes SQL gets like this. Be nice if they would comment it. If this is in Stored proc they could put comments in the middle of it. Not sure I like seeing dim1,dim2,dim3,dim4. Typically a bad db structure. Sometimes its the Database that causes stuff to be complicated to query and if you didn't make the schema you have to do your best with it. Don't like to see Expr1 though. Thats sloppy.
-
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 DeltaFROM 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.dim8WHERE
(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', '<Wow. This is a... (crystall ball) report query (Crystal XI?) for an Italian ERP, right? "Periodo consuntivo budget"? "Ordini di produzione" ? :D Looks like a some kind of analysis of production/orders vs. budget planned. Sorry for bad Italian... I'm just a Romanian guy that happened to work for an Italian ERP for almost 10 years...
Nuclear launch detected