Stuck with a particular SQL statement which is not working at all

I am stuck with a particular SQL statement which is not working at all. I have been troubleshooting it, but I get error messages which are simple but I AM not able to fix it.

However, I am not able to make it work. This query's output is a calculated value-

Please help me in writing the correct query which meets the Buisness Rule

Table name- MED_CLM  M
Different Columns - EXCL_AMT, BILL_CHRG_AMT

Buisness rule - Get Sum Of Not Covered Amount

If M.EXCL_AMT < 0 and M.BILL_CHRG_AMT > 0 use -1*NVL(EXCL_AMT,0), 
else if EXCL_AMT > 0 and BILL_CHRG_AMT < 0 use -1*NVL(EXCL_AMT,0),
else use NVL(EXCL_AMT,0) 
SELECT SUM(NVL(EXCL_AMT,0))
FROM MED_CLM 

I have to write one SQL query including each condition above

Below I hav开发者_如何学Pythone put the query.I suppose I am missing things in the IF-ELSE syntax -

IF (EXCL_AMT <0 AND BILL_CHg_AMT >0)

    BEGIN
      select sum (-1 * NVL(EXCL_AMT,0))
      from MED_CLM  
    END

ELSEIF (EXCL_AMT > 0 and BILL_CHRG_AMT < 0)

    BEGIN
      select sum (-1 * NVL(EXCL_AMT,0))
      from MED_CLM   
    END 

ELSEIF select SUM(NVL(EXCL_AMT,0))
       from MED_CLM
ENDIF;

SQL doesn't support conditional execution of SELECT statements as you are trying to do. PL/SQL does but that is irrelevant, as the values for the tests come from the table you're querying.

The correct answer is a simple ane elegant (well, elegantish) CASE() statement in the query's projection:

select sum(
        case
            when m.excl_amt < 0 and m.bill_chrg_amt > 0 then -1*nvl(m.excl_amt,0)
            when m.excl_amt > 0 and m.bill_chrg_amt < 0 then -1*nvl(m.excl_amt,0)
            else nvl(excl_amt,0)
        end
    ) as total_excl_amt
from med_clm m
/

The syntax is IF ... THEN and not IF ... BEGIN. But your main problem seems to be, that you try to execute that in a normal SQL statement and not in a stored procedure. You need to use CASE instead.

I think this should implement the logic described:

SELECT
  SUM(
    CASE
      WHEN EXCL_AMT <> 0 AND SIGN(EXCL_AMT) = -SIGN(BILL_CHRG_AMT)
      THEN -NVL(EXCL_AMT, 0)
      ELSE NVL(EXCL_AMT, 0)
    END
  )
FROM MED_CLM