|
For 1000 columns, all can be done is to emit a fatwa for the db "architect".
("Tell me, people, am I going insane?" - Black Sabbath - Sabotage, 1975, "Am I going insane (Radio)").
Nuclear launch detected
|
|
|
|
|
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
|
|
|
|
|
At least it looks like one.
Don't know if it got any real bad practices but it's really hard to read.
|
|
|
|
|
Something like that should be the exception, not the rule.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
It depends, sometimes 'clean, neat, readable' code is not fast.
|
|
|
|
|
It doesn't look fast either, with all those nested queries...
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Well, at least it is formatted.
|
|
|
|
|
Definately a coding horror, but my favorite part is I now know all the table names for your database
|
|
|
|
|
Wow.. now I know why I passed Databases with just a 5.5 - I don't get it at all
|
|
|
|
|
Looks straightforward but rather tedious to read...I'd hate to be the guy testing it correctness
"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
|
|
|
|
|
impressive... oh, i think, i'm going blind...
(yes|no|maybe)*
|
|
|
|
|
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 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 .
|
|
|
|
|
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
|
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
Wow. This is a... (crystall ball) report query (Crystal XI?) for an Italian ERP, right?
"Periodo consuntivo budget"? "Ordini di produzione" ?
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
|
|
|
|
|
MS are not letting you to catch a StackOverFlowException. I wrote a evaluator/interperter using C# 1.1 . The language supports recursion about a year ago I moved to C# 2.0 and only now we found out that a too deep of a recursion throws the application without any notice. It just goes away. A simple factorial like:
fact(n)(if(n == 0,1,n * fact(n-1)))
vaporizes the application at fact(46)
I checked MSDN and it says:
Starting with the .NET Framework version 2.0, a StackOverflowException object cannot be caught by a try-catch block and the corresponding process is terminated by default
http://msdn.microsoft.com/en-us/library/system.stackoverflowexception.aspx
On this page they tell you to stay away from unbound recursions. What kind of recommendation is this? Guys stay away from null pointers!!
Natza Mitzi
|
|
|
|
|
Natza Mitzi wrote: MS are not letting you to catch a StackOverFlowException. I wrote a evaluator/interperter using C# 1.1 . The language supports recursion about a year ago I moved to C# 2.0 and only now we found out that a too deep of a recursion throws the application without any notice. It just goes away. A simple factorial like:
fact(n)(if(n == 0,1,n * fact(n-1)))
vaporizes the application at fact(46)
That's how the CLR works. In fact even a tail recursive language like Scheme will fail on that.
You have 2 options:
1. Increase stack space.
2. Rewrite to use iteration or tail-recursion.
|
|
|
|
|
Natza Mitzi wrote: Guys stay away from null pointers!!
And from people posting on the wrong forum.
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
[My articles]
|
|
|
|
|
CPallini wrote: people posting on the wrong forum
Geeze, another one
"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
|
|
|
|
|