SQL Help

empirix

Lads,

I am trying to insert a case statement into some code because it generates the variable name for the rest of the code(if you know what i mean), only learning this in my new job and to be honest i am struggling. Also anybody know any good sql sites - i fear gettin the bullet


Case statement:

Case
When BAL_FOR_INT < 0 THEN 'DR'
When BAL_FOR_INT > 0 THEN 'CR'
When BAL_FOR_INT = 0 THEN 0
ELSE 'N/A'

End) As Bal_for_int_flag
FROM accountname_here




Code i need to insert it into:

SELECT COUNT(*), Avg(INT_RTE), SUM(BAL_AMT), SUM(NET_BAL_FOR_PRNCPL), 'L' AS GROSS_BAL_INT_AMT
FROM accountname_here

WHERE gross_bal_for_int_amt < 0

And load_last_action<>'D'
And period_dte='2006-11-30'
And ((Dr_Int_Cat Not In ( 11,12)
And BAL_FOR_INT_FLAG='DR')
And (Cr_Int_Cat <>200
AND BAL_FOR_INT_FLAG='CR'))
AND BAL_FOR_INT<>0
AND srce_prod_cde<>'1110'
AND LEFT(srce_prod_cde,1) IN ('1','9','10')


UNION
SELECT COUNT(*), Avg(INT_RTE), SUM(BAL_AMT), SUM(NET_BAL_FOR_PRNCPL),'G' AS GROSS_BAL_INT_AMT
FROM accountname_here

WHERE gross_bal_for_int_amt > 0

And load_last_action<>'D'
And period_dte='2006-11-30'
And ((Dr_Int_Cat Not In ( 11,12)
And BAL_FOR_INT_FLAG='DR')
And (Cr_Int_Cat <>200
AND BAL_FOR_INT_FLAG='CR'))
AND BAL_FOR_INT<>0
AND srce_prod_cde<>'1110'
AND LEFT(srce_prod_cde,1) IN ('1','9,'10')

UNION
SELECT COUNT(*), Avg(INT_RTE), SUM(BAL_AMT), SUM(NET_BAL_FOR_PRNCPL), '0' AS GROSS_BAL_INT_AMT
FROM accountname_here

WHERE gross_bal_for_int_amt = 0

And load_last_action<>'D'
And period_dte='2006-11-30'
And ((Dr_Int_Cat Not In ( 11,12)
And BAL_FOR_INT_FLAG='DR')
And (Cr_Int_Cat <>200
AND BAL_FOR_INT_FLAG='CR'))
AND BAL_FOR_INT<>0
AND srce_prod_cde<>'1110'
AND LEFT(srce_prod_cde,1) IN ('1','9,'10')


Any info greatly appreciated

cheers

Gosh

Can you tell us what platform you are using for SQL? I'm not sure all versions of SQL support the Case statement ...

jaggiebunnet

Looks like Oracle to me.

Have a look at the decode statement, although the case statement would work just as well here:

http://tahiti.oracle.com

You will need to register for an OTN account but it is free.

You can also look at

http://asktom.oracle.com

hth.

amen

only learning this in my new job and to be honest i am struggling. Also anybody know any good sql sites - i fear gettin the bullet

unless you told fibs on your CV any good place to work don't expect you to know everything and will surely help you with any coding issues/sql etc.

I know if I was you boss I would be more annoyed that you posted on the internet without first coming and talking to me than any perceived lack of sql skills

Gosh

AFAIK this should work (in MYSQL) ...

SELECT    COUNT(*), Avg(INT_RTE), SUM(BAL_AMT), SUM(NET_BAL_FOR_PRNCPL), 'L' AS GROSS_BAL_INT_AMT,
 
    Case
       When    BAL_FOR_INT < 0 THEN 'DR' 
       When    BAL_FOR_INT > 0 THEN 'CR' 
       ELSE    '0'        
    End as BAL_FOR_INT_FLAG
 
 FROM    accountname_here

I doubt whether you would get anything selected with the following conditions in your SQL

     And         ((Dr_Int_Cat Not In ( 11,12)
     And    BAL_FOR_INT_FLAG='DR')
     And         (Cr_Int_Cat <>200
     AND    BAL_FOR_INT_FLAG='CR'))

BAL_FOR_INT_FLAG can't be DR And CR at the same time. Should the 3rd And be an OR?

AND    BAL_FOR_INT<>0

This is always true if the preceding conditions are true so no point including it.

Instead of the UNION statements could you not define another case as follows:

       Case
          When GROSS_BAL_INT_AMT < 0 THEN 'L'
          When GROSS_BAL_INT_AMT > 0 THEN 'G'
          Else '0'
       End  as GROSS_BAL_INT_AMT_FLAG

and then GROUP BY on GROSS_BAL_INT_AMT_FLAG

Your SQL would then look like this

 SELECT COUNT(*), Avg(INT_RTE), SUM(BAL_AMT), SUM(NET_BAL_FOR_PRNCPL),
 Case
       When    BAL_FOR_INT < 0 THEN 'DR' 
       When    BAL_FOR_INT > 0 THEN 'CR' 
       ELSE    '0'        
 End as BAL_FOR_INT_FLAG,
 Case
          When GROSS_BAL_INT_AMT < 0 THEN 'L'
          When GROSS_BAL_INT_AMT > 0 THEN 'G'
          Else '0'
 End  as GROSS_BAL_INT_AMT_FLAG
 
 FROM    accountname_here
     
     WHERE load_last_action<>'D'
     And period_dte='2006-11-30'
     And ((Dr_Int_Cat Not In ( 11,12)
     And BAL_FOR_INT_FLAG='DR')
     OR (Cr_Int_Cat <>200
     AND BAL_FOR_INT_FLAG='CR'))
     AND srce_prod_cde<>'1110'
     AND LEFT(srce_prod_cde,1) IN ('1','9','10')
 
 GROUP BY GROSS_BAL_INT_AMT_FLAG

empirix

Cheers guys, will give this a go.

And to the guy about the boss being annoyed part, maybe i phrased this wrong, its a new contract for me with an overseas organisation in OZ, just there is some ssql involved here and while i have used sql before mainly building databases in college, its been a while and its also difficult to ask your boss when there in bed - you get the drift.

I have already worked with these guys when i was in Sydney and from home before.
thanks again

Gosh

AND LEFT(srce_prod_cde,1) IN ('1','9','10')

The LEFT statement will return only 1 character - therefore the test for '10' will never be true.