By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,617 Members | 1,166 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,617 IT Pros & Developers. It's quick & easy.

Syntax on View/Cross tab join

P: 6
Hoping someone can help me here!
I'm having some problems trying to get the right syntax for a view, and am wondering if someone could point me in the right
direction!

My code is currently:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT a.Department,
  2.         a.Section,
  3.             SUM(CASE b.Type WHEN 'T' THEN b.LYAmt ELSE 0 END) AS TLYAmt,
  4.             SUM(CASE b.Type WHEN 'I' THEN b.LYAmt ELSE 0 END) AS ILYAmt,
  5.             SUM(CASE b.Type WHEN 'A' THEN b.LYAmt ELSE 0 END) AS ALYAmt,
  6.             SUM(CASE b.Type WHEN 'T' THEN b.CYAmt ELSE 0 END) AS TCYAmt,
  7.             SUM(CASE b.Type WHEN 'I' THEN b.CYAmt ELSE 0 END) AS ICYAmt,
  8.             SUM(CASE b.Type WHEN 'A' THEN b.CYAmt ELSE 0 END) AS ACYAmt    
  9.         FROM tbl_Departments a
  10.         INNER JOIN tbl_AccountDetail b ON
  11.         a.DeptCode = b.DeptCode
  12.         GROUP BY a.section
So basically a cross-tab join. What I would like to do however, is have multiple values in Type making up 'TLYAmt'; so rather than having

Expand|Select|Wrap|Line Numbers
  1. SUM(CASE b.Type WHEN 'T' THEN b.LYAmt ELSE 0 END) AS TLYAmt
I would be like to have
Expand|Select|Wrap|Line Numbers
  1. SUM(CASE b.Type WHEN 'T' or 'P' or 'H' THEN b.LYAmt ELSE 0 END) AS TLYAmt 
Except of course that that syntax doesn't work!
(so for example, if LyAmt for T was 1, P was 2, and H was 3, TLYAmt would return 6. Just to make it a bit more complicated, I wouldn't have multiple values for each CASE statement.

I've googled around and can't seem to find a solution, so am wondering if anyone knows whether this can be done? If it can't (and I am fast beginning to believe I am barking up the wrong tree!), can someone suggest something that might work in it's place?

Many thanks!
Dec 2 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
OR does not work that way...

try:

Expand|Select|Wrap|Line Numbers
  1. SUM(CASE WHEN b.Type  = 'T' or b.Type  = 'P' or b.Type  = 'H' THEN b.LYAmt ELSE 0 END) AS TLYAmt 
  2.  
for cleaner code, you can also

Expand|Select|Wrap|Line Numbers
  1. SUM(CASE WHEN b.Type  in ('T', 'P','H') THEN b.LYAmt ELSE 0 END) AS TLYAmt 
  2.  

Happy coding!
Dec 2 '08 #2

P: 6
That works fantastically - thank you very much!
Dec 5 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.