Help with SQL

[baldr1c]

I am new with Oracle and I am having a problem getting the correct syntax for the following query;


SELECT
BRB_CALLDET_T.ACC_NUM,
BRB_CALLDET_T.BILL_NUM,
BRB_CALLDET_T.SERV_NUM,
IF(BRB_CALLDET_T.GNRC_PRC_AMT_CR)='CR' THEN SUM(BRB_CALLDET_T.GNRC_PRC_AMT* -1)
ELSIF SUM(BRB_CALLDET_T.GNRC_PRC_AMT) END IF AS USAGE_TOT
FROM
EDX_DBA.BRB_CALLDET_T
GROUP BY
BRB_CALLDET_T.BILL_NUM,
BRB_CALLDET_T.ACC_NUM,
BRB_CALLDET_T.SERV_NUM
ORDER BY
BRB_CALLDET_T.BILL_NUM ASC,
BRB_CALLDET_T.ACC_NUM ASC,
BRB_CALLDET_T.SERV_NUM ASC


What I need is to make the BRB_CALLDET_T.GNRC_PRC_AMT field negative if the BRB_CALLDET_T.GNRC_PRC_AMT_CR field contains "cr". (If it does not, then it is null)
I get an error message saying "Keyword FROM not found where expected"
What is wrong with my syntax and is there a better way to do this??

Thanks

 

[stefanhei]

Hi.
Use DECODE instead of IF:

SELECT
BRB_CALLDET_T.ACC_NUM,
BRB_CALLDET_T.BILL_NUM,
BRB_CALLDET_T.SERV_NUM,
sum(decode(BRB_CALLDET_T.GNRC_PRC_AMT_CR),'CR',(BRB_CALLDET_T.GNRC_PRC_AMT* -1),(BRB_CALLDET_T.GNRC_PRC_AMT))) AS USAGE_TOT
FROM
EDX_DBA.BRB_CALLDET_T
GROUP BY
BRB_CALLDET_T.BILL_NUM,
BRB_CALLDET_T.ACC_NUM,
BRB_CALLDET_T.SERV_NUM
ORDER BY
BRB_CALLDET_T.BILL_NUM ASC,
BRB_CALLDET_T.ACC_NUM ASC,
BRB_CALLDET_T.SERV_NUM ASC

Stefan