July 14, 2016 at 10:58 am
I am tasked with deciphering a shed load of COGNOS pseudo code - basically it runs against Oracle DB but I only have access to the pseudo SQL it generates which I have to convert into MS SQL Server T-SQL...
Some horrible SQL below which I am sure is a candidate for the CROSS APPLY... but I am getting into mess with it given the number of table joins and nonsense COGNOS generated pseudo SQL... I have tidied it up in terms of layout but generally left things as is...
Can any kind soul give me some guidance as how I might improve on this...
I know I can attack some of the where clauses by including them as ON clauses in the JOIN statements but my aim is to try and get CROSS APPLY working and understand it well enough to add it to my armoury since I have a shed load of these to convert.
Any and all assistance gratefully received
Cheers
Jabba
[Code]
selectc25 as c1
,c42 as c3
,c41 as c4
,c40 as c5
,c39 as c6
,c38 as c7
,c37 as c8
,c36 as c9
,c35 as c12
,c34 as c13
,c33 as c14
,c32 as c15
,c31 as c16
,c30 as c17
,c29 as c18
,c28 as c19
,c27 as c20
,c26 as c21
,c24 as c22
from (selectT1."EMPLOYEE_NUMBER"as c23
,COUNT(T4."LONG_DESC")as c24
fromRLT.dbo."D550M" T1
,RLT.dbo."D500M" T2
,RLT.dbo."D200M" T4
,RLT.dbo."D539M" T5
,RLT.dbo."D800M" T6
,RLT.dbo."D100M" T7
,RLT.dbo."D400M" T8
,RLT.dbo."D868M" T12
,RLT.dbo."D580M" T3left outer join RLT.dbo."D554M" T9 on T3."PERSON_REF" = T9."PERSON_REF" and T3."REF" = T9."POST_REF"
left outer join RLT.dbo."D581M" T11 on T3."PERSON_REF" = T11."PERSON_REF" and T3."REF" = T11."EMP_POST_REF" and T3."START_COMPDATE" = T11."EMP_POST_START_COMPDATE"
left outer join RLT.dbo."D850M" T10 on T11."REF" = T10."LOCATION_REF"
whereT1."PERSON_REF" = T2."PERSON_REF"
and T1."PERSON_REF" = T3."PERSON_REF"
and T3."REF" = T4."REF"
and T3."PERSON_REF" = T5."PERSON_REF"
and T3."REF" = T5."REF"
and T3."START_COMPDATE" = T5."START_COMPDATE"
and T6."NARRATIVE_CATEGORY" = 'POSTPS'
and T5."POSITION_STATUS" = T6."NARRATIVE_CODE"
and T7."REF" = T1."LEVEL1_PAY_STR_REF"
and T9."REF" = T8."REF"
and T2."PERSON_REF" = T12."PERSON_REF"
and (not T5."POSITION_STATUS" IN ('PENS'))
and T12."PROFILE_ID" = 'HRUSER'
and T12."IQ_ACCESS_FLAG" = 'Y'
and T3."START_DATE" <= @prmDate
and (T3."END_DATE" IS NULL or T3."END_DATE" >= @prmDate)
and (T5."POSN_STAT_START_DATE" IS NULL or T5."POSN_STAT_START_DATE" <= @prmDate)
and (T5."POSN_STAT_END_DATE" IS NULL or T5."POSN_STAT_END_DATE" >= @prmDate)
and (T9."START_DATE" IS NULL or T9."START_DATE" <= @prmDate)
and (T9."END_DATE" IS NULL or T9."END_DATE" >= @prmDate)
and (T11."START_DATE" IS NULL or T11."START_DATE" <= @prmDate)
and (T11."END_DATE" IS NULL or T11."END_DATE" >= @prmDate)
group byT1."EMPLOYEE_NUMBER"
having COUNT(T4."LONG_DESC") > 1
) D3,
(selectT1."EMPLOYEE_NUMBER"as c25
,T11."END_DATE"as c26
,T11."START_DATE"as c27
,T10."LONG_DESC"as c28
,T9."END_DATE"as c29
,T9."START_DATE"as c30
,T8."LONG_DESC"as c31
,T7."LONG_DESC"as c32
,T6."LONG_DESC"as c33
,T5."POSN_STAT_START_DATE"as c34
,T5."POSITION_STATUS"as c35
,T4."LONG_DESC"as c36
,T3."MAIN_FLAG"as c37
,COALESCE(T2."KNOWN_AS", T2."FIRST_FORNAME")as c38
,T2."FIRST_FORNAME"as c39
,T1."EMPLOYEE_TYPE"as c40
,T2."KNOWN_AS"as c41
,T2."SURNAME"as c42
fromRLT.dbo."D550M" T1
,RLT.dbo."D500M" T2
,RLT.dbo."D200M" T4
,RLT.dbo."D539M" T5
,RLT.dbo."D800M" T6
,RLT.dbo."D100M" T7
,RLT.dbo."D400M" T8
,RLT.dbo."D868M" T12
,RLT.dbo."D580M" T3left outer join RLT.dbo."D554M" T9 on T3."PERSON_REF" = T9."PERSON_REF" and T3."REF" = T9."POST_REF"
left outer join RLT.dbo."D581M" T11 on T3."PERSON_REF" = T11."PERSON_REF" and T3."REF" = T11."EMP_POST_REF" and T3."START_COMPDATE" = T11."EMP_POST_START_COMPDATE"
left outer join RLT.dbo."D850M" T10 on T11."REF" = T10."LOCATION_REF"
whereT1."PERSON_REF" = T2."PERSON_REF"
and T1."PERSON_REF" = T3."PERSON_REF"
and T3."REF" = T4."REF"
and T3."PERSON_REF" = T5."PERSON_REF"
and T3."REF" = T5."REF"
and T3."START_COMPDATE" = T5."START_COMPDATE"
and T6."NARRATIVE_CATEGORY" = 'POSTPS'
and T5."POSITION_STATUS" = T6."NARRATIVE_CODE"
and T7."REF" = T1."LEVEL1_PAY_STR_REF"
and T9."REF" = T8."REF"
and T2."PERSON_REF" = T12."PERSON_REF"
and (not T5."POSITION_STATUS" IN ('PENS'))
and T12."PROFILE_ID" = 'HRUSER'
and T12."IQ_ACCESS_FLAG" = 'Y'
and T3."START_DATE" <= @prmDate
and (T3."END_DATE" IS NULL or T3."END_DATE" >= @prmDate)
and (T5."POSN_STAT_START_DATE" IS NULL or T5."POSN_STAT_START_DATE" <= @prmDate)
and (T5."POSN_STAT_END_DATE" IS NULL or T5."POSN_STAT_END_DATE" >= @prmDate)
and (T9."START_DATE" IS NULL or T9."START_DATE" <= @prmDate)
and (T9."END_DATE" IS NULL or T9."END_DATE" >= @prmDate)
and (T11."START_DATE" IS NULL or T11."START_DATE" <= @prmDate)
and (T11."END_DATE" IS NULL or T11."END_DATE" >= @prmDate)
) D2
where ((c23 = c25) or ((c25 IS NULL) and (c23 IS NULL)))
[/Code]
July 14, 2016 at 11:20 am
Why do you think that you need to use the APPLY operator? You should probably change the joins to the SQL-92 format, but that won't improve performance.
The query seems to be the repeated and a window function might help you.
I'm not sure if I got this right, but it might give you an idea at least.
selectT1.EMPLOYEE_NUMBERas c1
,T2.SURNAMEas c3
,T2.KNOWN_ASas c4
,T1.EMPLOYEE_TYPEas c5
,T2.FIRST_FORNAMEas c6
,COALESCE(T2.KNOWN_AS, T2.FIRST_FORNAME) as c7
,T3.MAIN_FLAGas c8
,T4.LONG_DESCas c9
,T5.POSITION_STATUSas c12
,T5.POSN_STAT_START_DATEas c13
,T6.LONG_DESCas c14
,T7.LONG_DESCas c15
,T8.LONG_DESCas c16
,T9.START_DATEas c17
,T9.END_DATEas c18
,T10.LONG_DESCas c19
,T11.START_DATEas c20
,T11.END_DATEas c21
,COUNT(T4.LONG_DESC) OVER( PARTITION BY T1.EMPLOYEE_NUMBER)as c22
fromRLT.dbo.D550M T1
JOINRLT.dbo.D500M T2 ON T1.PERSON_REF = T2.PERSON_REF
JOINRLT.dbo.D580M T3 ON T1.PERSON_REF = T3.PERSON_REF
JOINRLT.dbo.D200M T4 ON T3.REF = T4.REF
JOINRLT.dbo.D539M T5 ON T3.PERSON_REF = T5.PERSON_REF and T3.REF = T5.REF and T3.START_COMPDATE = T5.START_COMPDATE
JOINRLT.dbo.D800M T6 ON T5.POSITION_STATUS = T6.NARRATIVE_CODE and T5.POSITION_STATUS = T6.NARRATIVE_CODE
JOINRLT.dbo.D100M T7 ON T7.REF = T1.LEVEL1_PAY_STR_REF
JOINRLT.dbo.D868M T12 ON T2.PERSON_REF = T12.PERSON_REF
left outer join RLT.dbo.D554M T9 on T3.PERSON_REF = T9.PERSON_REF and T3.REF = T9.POST_REF
JOINRLT.dbo.D400M T8 ON T9.REF = T8.REF
left outer join RLT.dbo.D581M T11 on T3.PERSON_REF = T11.PERSON_REF and T3.REF = T11.EMP_POST_REF and T3.START_COMPDATE = T11.EMP_POST_START_COMPDATE
left outer join RLT.dbo.D850M T10 on T11.REF = T10.LOCATION_REF
whereT6.NARRATIVE_CATEGORY = 'POSTPS'
and (not T5.POSITION_STATUS IN ('PENS'))
and T12.PROFILE_ID = 'HRUSER'
and T12.IQ_ACCESS_FLAG = 'Y'
and T3.START_DATE <= @prmDate
and (T3.END_DATE IS NULL or T3.END_DATE >= @prmDate)
and (T5.POSN_STAT_START_DATE IS NULL or T5.POSN_STAT_START_DATE <= @prmDate)
and (T5.POSN_STAT_END_DATE IS NULL or T5.POSN_STAT_END_DATE >= @prmDate)
and (T9.START_DATE IS NULL or T9.START_DATE <= @prmDate)
and (T9.END_DATE IS NULL or T9.END_DATE >= @prmDate)
and (T11.START_DATE IS NULL or T11.START_DATE <= @prmDate)
and (T11.END_DATE IS NULL or T11.END_DATE >= @prmDate)
group byT1.EMPLOYEE_NUMBER
having COUNT(T4.LONG_DESC) > 1;
July 14, 2016 at 11:50 am
I TOTALLY LOVE working with Cognos and Business Objects clients because I get to make GOBS OF MONEY trying to decipher and tune horrible stuff like that!!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 15, 2016 at 3:49 am
Thanks for looking at this Luis...
The good news is that I had already adopted the windows partitioning approach as you suggested - so good to know I was heading in the right direction.
In essence you are of course on the right lines and thanks for the suggestion and time to reply - much appreciated.
The good news is that I had already adopted the windows partitioning approach as you suggested - so good to know I was heading in the right direction.
The reason I wanted to use the apply operator was to avoid repeating the where clause and having to include all the fields in the group by... along the lines of this article http://www.sqlservercentral.com/articles/Queries/124140/ - the code I provided was a sub-section of multiple sub-queries which repeat the where clause obsessively...!!
My problem in trying to apply (excuse pun !!) the CROSS APPLY approach from details in the article using one table - where I have many... basically its confusing the hell out of me... but guess I just need to sit down and work my way through it.
Finally - as for SQL-92 - yep - this was machine generated pseudo code hence I've left it as is for now but yes SQL-92 would be better of course.
Cheers
Jabba
July 15, 2016 at 3:52 am
Lol Kevin - wish I was on the same rate you were 🙂
July 15, 2016 at 5:07 am
Kevin,
Does your computer automatically play Pink Floyd whenever you're forced to work on it...? The till working in the background might be good incentive...
July 15, 2016 at 7:29 am
pietlinden (7/15/2016)
Kevin,Does your computer automatically play Pink Floyd whenever you're forced to work on it...? The till working in the background might be good incentive...
HAH!! Love that one! I need to work that music clip into some of my presentations. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 15, 2016 at 8:06 am
Jabba1963 (7/15/2016)
Thanks for looking at this Luis...The good news is that I had already adopted the windows partitioning approach as you suggested - so good to know I was heading in the right direction.
In essence you are of course on the right lines and thanks for the suggestion and time to reply - much appreciated.
The good news is that I had already adopted the windows partitioning approach as you suggested - so good to know I was heading in the right direction.
The reason I wanted to use the apply operator was to avoid repeating the where clause and having to include all the fields in the group by... along the lines of this article http://www.sqlservercentral.com/articles/Queries/124140/ - the code I provided was a sub-section of multiple sub-queries which repeat the where clause obsessively...!!
My problem in trying to apply (excuse pun !!) the CROSS APPLY approach from details in the article using one table - where I have many... basically its confusing the hell out of me... but guess I just need to sit down and work my way through it.
Finally - as for SQL-92 - yep - this was machine generated pseudo code hence I've left it as is for now but yes SQL-92 would be better of course.
Cheers
Jabba
APPLY will allow you to avoid repeating expressions while writing code, but it won't avoid the code to be executed multiple times.
The code you posted does not need a GROUP BY, so you don't need to repeat anything (just remove the GROUP BY that I incorrectly left). The code I posted was supposed to replace the whole code you posted, so no repetition whatsoever.
Be sure to understand what's going on and why is it repeating the same WHERE clauses to get things done in a single time. Of course, a consultant would be fascinated to help as you seem to have a lot of work ahead (and great learning opportunities).
July 15, 2016 at 8:25 am
Jabba1963 (7/15/2016)
My problem in trying to apply (excuse pun !!) the CROSS APPLY approach from details in the article using one table - where I have many... basically its confusing the hell out of me... but guess I just need to sit down and work my way through it.
"There is no spoon."
But you do only have one table that you are working with. Every single JOIN creates a virtual table, and the CROSS APPLY operates on that virtual table. It's like working with a CTE, derived table, or view--except that the naming maintains the illusion that you still have separate tables. It doesn't matter how complex the CTE, derived table, view or virtual table is--at that point, you have a single virtual table that you are working with.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 15, 2016 at 11:45 am
Hear hear Luis - and yes that's exactly how I see it - a great learning opportunity 🙂 - and yes given the partition approach it does alleviate the need for the Group By - thanks again 🙂
July 15, 2016 at 11:50 am
Hi Drew,
Love the "There is no spoon" reference... 🙂
And having read your response - hits forehead with palm - of course of course of course 🙂
Thanks for the beautifully put and succinct answer... and I will now opt for the red pill 🙂
Best regards
Jabba
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply