Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you have been placed in read-only mode.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Is this a coding horror?

The Weird and The Wonderful

20 Posts 19 Posters 0 Views 1 Watching

M Mattia Locatelli

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', '<
M Offline
M Offline
Megidolaon

wrote on last edited by

#2

At least it looks like one. :doh: Don't know if it got any real bad practices but it's really hard to read.
1 Reply Last reply

0
M Mattia Locatelli

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', '<
P Offline
P Offline
PIEBALDconsult

wrote on last edited by

#3

Something like that should be the exception, not the rule.
1 Reply Last reply

0
M Mattia Locatelli

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', '<
Q Offline
Q Offline
QuiJohn

wrote on last edited by

#4

Not at all... it's obviously obfuscation to prevent anyone from understanding what it does in case any disgruntled employees ever decide to post sensitive internal code to the internet.

Faith is a fine invention For gentlemen who see; But microscopes are prudent In an emergency! -Emily Dickinson
1 Reply Last reply

0
M Mattia Locatelli

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', '<
L Offline
L Offline
leoinfo

wrote on last edited by

#5

Member 3084160 wrote:

What do you think is this a coding horror?

YEP! :)

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.
1 Reply Last reply

0
M Mattia Locatelli

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', '<
G Offline
G Offline
GibbleCH

wrote on last edited by

#6

It depends, sometimes 'clean, neat, readable' code is not fast.
G 1 Reply Last reply

0
M Mattia Locatelli

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', '<
D Offline
D Offline
darkelv

wrote on last edited by

#7

Well, at least it is formatted.
1 Reply Last reply

0
M Mattia Locatelli

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', '<
S Offline
S Offline
StevenWalsh

wrote on last edited by

#8

Definately a coding horror, but my favorite part is I now know all the table names for your database :)
1 Reply Last reply

0
M Mattia Locatelli

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', '<
L Offline
L Offline
Lost User

wrote on last edited by

#9

Wow.. now I know why I passed Databases with just a 5.5 - I don't get it at all :omg:
1 Reply Last reply

0
M Mattia Locatelli

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', '<
P Offline
P Offline
Paul Conrad

wrote on last edited by

#10

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
1 Reply Last reply

0
M Mattia Locatelli

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', '<
S Offline
S Offline
StM0n

wrote on last edited by

#11

impressive... oh, i think, i'm going blind... :wtf:

(yes|no|maybe)*
1 Reply Last reply

0
G GibbleCH

It depends, sometimes 'clean, neat, readable' code is not fast.
G Offline
G Offline
Guffa

wrote on last edited by

#12

It doesn't look fast either, with all those nested queries...

Despite everything, the person most likely to be fooling you next is yourself.
1 Reply Last reply

0
M Mattia Locatelli

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', '<
G Offline
G Offline
Guffa

wrote on last edited by

#13

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.
1 Reply Last reply

0
M Mattia Locatelli

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', '<
N Offline
N Offline
Nile Hawk

wrote on last edited by

#14

I was ordered to work in a project that contains a lot of queries like this, I could not maintain it, and the project failed :(. I'm human :D.
1 Reply Last reply

0
M Mattia Locatelli

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', '<
M Offline
M Offline
Mohammad Akbar

wrote on last edited by

#15

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 ;)
1 Reply Last reply

0
M Mattia Locatelli

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', '<
A Offline
A Offline
ajtunbridge

wrote on last edited by

#16

It's....beautiful
S 1 Reply Last reply

0
A ajtunbridge

It's....beautiful
S Offline
S Offline
SciGama

wrote on last edited by

#17

its ok ive done queries like this..the key to these is just split the query up and study it piece by piece. SQL is very power and can perform queries like this within a minute as opposed to doing stuff like this in front end code.
1 Reply Last reply

0
M Mattia Locatelli

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', '<
H Offline
H Offline
Howard Richards

wrote on last edited by

#18

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
1 Reply Last reply

0
M Mattia Locatelli

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', '<
T Offline
T Offline
Tad McClellan

wrote on last edited by

#19

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.

TadMcClellan.Com
1 Reply Last reply

0
M Mattia Locatelli

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', '<
C Offline
C Offline
Cristian Amarie

wrote on last edited by

#20

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
1 Reply Last reply

0