March 27, 2012 at 2:16 pm
I was given this to translate and I've figured out the obvious syntax differences. But I need help with a problem I just don't see. I've struggled with it for a day and need some help. Any help is appreciated. Probably something to do with all the nesting. Also, I don't really get the first set of CASE statements. Thanks for looking.
SELECT
calc.subclient
, calc.full_queue
, calc.region
, calc.bucket
, calc.cat as queue
, calc.max as sixty_day_High
, calc.avg as sixty_day_ave
, calc.min as sixty_day_low
, round((calc.max/calc.avg)-(calc.min/calc.avg),4) as Difference
, casewhen vol.subclient is null then 0 else vol.today end as today
, case when vol.subclient is null then 0 else vol.one_da end as one_da
, case when vol.subclient is null then 0 else vol.two_da end as two_da
, case when vol.subclient is null then 0 else vol.three_da end as three_da
, case when vol.subclient is null then 0 else vol.four_da end as four_da
, case when vol.subclient is null then 0 else vol.five_da end as five_da
, case when vol.subclient is null then 0 else vol.six_da end as six_da
, case when vol.subclient is null then 0 else vol.seven_da end as seven_da
, case when vol.subclient is null then 0 else vol.eight_da end as eight_da
, case when vol.subclient is null then 0 else vol.nine_da end as nine_da
, case when vol.subclient is null then 0 else vol.ten_da end as ten_da
, case when vol.subclient is null then 0 else vol.eleven_da end as eleven_da
, case when vol.subclient is null then 0 else vol.twelve_da end as twelve_da
, case when vol.subclient is null then 0 else vol.thirteen_da end as thirteen_da
, case when vol.subclient is null then 0 else vol.fourteen_da end as fourteen_da
FROM
-------------------------------------------------------------------------
------------------------------------------------------------------------
---------------------------------------------------------------------
(select
subclient
, full_queue
, region
, bucket
, queue
, sum(TODAY) as TODAY
, sum(ONE_DA) as ONE_DA
, sum(TWO_DA) as TWO_DA
, sum(THREE_DA) as THREE_DA
, sum(FOUR_DA) as FOUR_DA
, sum(FIVE_DA) as FIVE_DA
, sum(SIX_DA) as SIX_DA
, sum(SEVEN_DA) as SEVEN_DA
, sum(EIGHT_DA) as EIGHT_DA
, sum(NINE_DA) as NINE_DA
, sum(TEN_DA) as TEN_DA
, sum(ELEVEN_DA) as ELEVEN_DA
, sum(TWELVE_DA) as TWELVE_DA
, sum(THIRTEEN_DA) as THIRTEEN_DA
, sum(FOURTEEN_DA) as FOURTEEN_DA
from(
select
sub_client_id as SubClient
, full_queue
, region
, bucket
, cat as queue
, case when work_of_date = GETDATE() then total_volume else 0 end as TODAY
, case when work_of_date = GETDATE()-1 then total_volume else 0 end as ONE_DA
, case when work_of_date = GETDATE()-2 then total_volume else 0 end as TWO_DA
, case when work_of_date = GETDATE()-3 then total_volume else 0 end as THREE_DA
, case when work_of_date = GETDATE()-4 then total_volume else 0 end as FOUR_DA
, case when work_of_date = GETDATE()-5 then total_volume else 0 end as FIVE_DA
, case when work_of_date = GETDATE()-6 then total_volume else 0 end as SIX_DA
, case when work_of_date = GETDATE()-7 then total_volume else 0 end as SEVEN_DA
, case when work_of_date = GETDATE()-8 then total_volume else 0 end as EIGHT_DA
, case when work_of_date = GETDATE()-9 then total_volume else 0 end as NINE_DA
, case when work_of_date = GETDATE()-10 then total_volume else 0 end as TEN_DA
, case when work_of_date = GETDATE()-11 then total_volume else 0 end as ELEVEN_DA
, case when work_of_date = GETDATE()-12 then total_volume else 0 end as TWELVE_DA
, case when work_of_date = GETDATE()-13 then total_volume else 0 end as THIRTEEN_DA
, case when work_of_date = GETDATE()-14 then total_volume else 0 end as FOURTEEN_DA
from(
SELECT (a.work_of_date) as work_of_date
, a.sub_client_id
, a.region+a.bucket+a.cat as full_queue
, a.region
, a.bucket
, a.cat
, a.total_volume
FROM queuing_report_tab_arc a
where a.work_of_date = '2012/03/14'
and a.region = 'T'
-------------------------------------------------------
Union
-------------------------------------------------------
SELECT a.work_of_date as work_of_date
, a.sub_client_id
, a.region+a.bucket+a.cat as full_queue
, a.region
, a.bucket
, a.cat
, a.total_volume
FROM queuing_report_tab a
where a.region = 'T'))
group by subclient, full_queue, region, bucket, queue
) vol,
---------------------------------------------------------
--------------------------------------------------------
------------------------------------------------------
(select sub_client_id as subclient
, region+bucket+cat as FULL_QUEUE
, region
, bucket
, cat
, max(total_volume) as MAX
, case when count(work_of_date) < 60 then 0 else min(total_volume) end as MIN
, round(sum(total_volume)/60,2) as avg
from (
select
a.work_of_date
, a.sub_client_id
, a.region
, a.bucket
, a.cat
, a.total_volume
FROM queuing_report_tab_arc a
where a.work_of_date >= sysdate-60 and a.region = 'T'
-----------------------------------------------------
Union
-----------------------------------------------------
select
a.work_of_date
, a.sub_client_id
, a.region
, a.bucket
, a.cat
, a.total_volume
FROM queuing_report_tab a
where a.region = 'T'
)
group by sub_client_id
, region+bucket+cat
, region, bucket
, cat) calc
---------------------------------------------
where vol.subclient(+) = calc.subclient and vol.full_queue(+) = calc.full_queue
order by subclient, full_queue
March 27, 2012 at 2:24 pm
It sounds like you have some question other than the first case statements but you didn't say what the question is.
The first set of case statements
casewhen vol.subclient is null then 0 else vol.today end as today
This just says if vol.subclient is null then the value needs to be 0 otherwise use vol.today.
Maybe a c# style syntax will help???
if(vol.subclient == null)
{
today = 0;
}
else
{
today = vol.today;
}
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2012 at 2:59 pm
Not sure what isn't working, since you did not tell us. However, after a quick look I think sysdate may be referring to some PERL function? SQL uses GETDATE(). You really need to give us some table structures to debug this. Also, the (+) is something I have never seen.
Jared
CE - Microsoft
March 27, 2012 at 3:10 pm
SQLKnowItAll (3/27/2012)
Also, the (+) is something I have never seen.
Same as SQL's old join syntax. Table1.Column1 *= Table2.Column2. Oracle syntax is Table1.Column1 (+)= Table2.Column2
sysdate is the Oracle function to return current date and time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 3:12 pm
Jared, Thanks. The + just concatenates the character values, PL/SQL uses || so I get the things like that. I'm currently getting :
Msg 156, Level 15, State 1, Line 140
Incorrect syntax near the keyword 'group'.
This works in a pass-through query and so it's something that's ok in PL/SQL but not legal in T-SQL.
March 27, 2012 at 3:15 pm
Scott Solice (3/27/2012)
Jared, Thanks. The + just concatenates the character values, PL/SQL uses || so I get the things like that.
Jared's refering to this line:
vol.subclient(+) = calc.subclient and vol.full_queue(+) = calc.full_queue
That's not T-SQL syntax. SQL 2008 doesn't support the *= syntax either (too old), so you need to use the LEFT/RIGHT OUTER JOIN syntax as well. Rough example
FROM (<subquery 1 here>) Sub1 LEFT OUTER JOIN (<subquery 2 here>) Sub2 ON Sub1.JoinColumn = Sub2.JoinColumn
The group by error is because yo need to alias this subquery
Union
-----------------------------------------------------
select
a.work_of_date
, a.sub_client_id
, a.region
, a.bucket
, a.cat
, a.total_volume
FROM queuing_report_tab a
where a.region = 'T'
) <need alias here!> --<<<---------------------------
group by sub_client_id
, region+bucket+cat
, region, bucket
, cat) calc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 3:23 pm
GilaMonster (3/27/2012)
SQLKnowItAll (3/27/2012)
Also, the (+) is something I have never seen.Same as SQL's old join syntax. Table1.Column1 *= Table2.Column2. Oracle syntax is Table1.Column1 (+)= Table2.Column2
sysdate is the Oracle function to return current date and time.
As Gail mentioned... The (+) in the join at the end of the script is old. I don't think that is supported. Take that out, for starters and just leave the line as:
where vol.subclient = calc.subclient and vol.full_queue = calc.full_queue
Jared
CE - Microsoft
March 27, 2012 at 3:33 pm
SQLKnowItAll (3/27/2012)
GilaMonster (3/27/2012)
SQLKnowItAll (3/27/2012)
Also, the (+) is something I have never seen.Same as SQL's old join syntax. Table1.Column1 *= Table2.Column2. Oracle syntax is Table1.Column1 (+)= Table2.Column2
sysdate is the Oracle function to return current date and time.
As Gail mentioned... The (+) in the join at the end of the script is old. I don't think that is supported. Take that out, for starters and just leave the line as:
where vol.subclient = calc.subclient and vol.full_queue = calc.full_queue
It's not that the (+)= is old, SQL never supported it, SQL supported *=.
Your where clause is not equivalent. The *= or (+)= denotes an outer join comparison. The = alone is an inner join comparison.
The (+)= is equivalent to
FROM vol LEFT OUTER JOIN Calc ON vol.subclient = calc.subclient and vol.full_queue = calc.full_queue
(or Right outer, I can't remember which)
Your suggested rewrite is equivalent to
FROM vol INNER JOIN Calc ON vol.subclient = calc.subclient and vol.full_queue = calc.full_queue
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 3:42 pm
Thanks Gail, I got the correct join in my copy and understand the LOJ for the + in PL/SQL. Just forgot to take it out of this copy. I beleive it is something to do with the derived tables and the alias, like you pointed out. I'll try that.
March 27, 2012 at 4:25 pm
GilaMonster (3/27/2012)
SQLKnowItAll (3/27/2012)
GilaMonster (3/27/2012)
SQLKnowItAll (3/27/2012)
Also, the (+) is something I have never seen.Same as SQL's old join syntax. Table1.Column1 *= Table2.Column2. Oracle syntax is Table1.Column1 (+)= Table2.Column2
sysdate is the Oracle function to return current date and time.
As Gail mentioned... The (+) in the join at the end of the script is old. I don't think that is supported. Take that out, for starters and just leave the line as:
where vol.subclient = calc.subclient and vol.full_queue = calc.full_queueIt's not that the (+)= is old, SQL never supported it, SQL supported *=.
Your where clause is not equivalent. The *= or (+)= denotes an outer join comparison. The = alone is an inner join comparison.
The (+)= is equivalent to
FROM vol LEFT OUTER JOIN Calc ON vol.subclient = calc.subclient and vol.full_queue = calc.full_queue
(or Right outer, I can't remember which)
Your suggested rewrite is equivalent to
FROM vol INNER JOIN Calc ON vol.subclient = calc.subclient and vol.full_queue = calc.full_queue
Ahh... Now I see. Thanks Gail.
Jared
CE - Microsoft
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply