GETTING AN ERROR WHEN RUN THIS QUERY PLEASE HELP

Question

GETTING AN ERROR WHEN RUN THIS QUERY PLEASE HELP

RDOT

SSC Veteran

Points: 204
More actions
October 12, 2022 at 9:30 pm

#4098848

CASE
WHEN PM_LETTING_DATA.CONST_EST_AMT IS NOT NULL THEN
PM_LETTING_DATA.CONST_EST_AMT
ELSE '0.00'
END CONST_EST,error Message below Image

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3666 More actions · Answer 1

This looks like an Oracle query.

What data type is CONST_EST_AMT? If it is numeric the query may work if you remove the single quotes from '0.00' or cast it to a number.

CASE
  WHEN PM_LETTING_DATA.CONST_EST_AMT IS NOT NULL 
  THEN PM_LETTING_DATA.CONST_EST_AMT
  ELSE CAST(0 AS NUMBER)
END

You may also be able to use

NVL(PM_LETTING_DATA.CONST_EST_AMT,0)

RDOT SSC Veteran Points: 204 More actions · Answer 2

CASE

WHEN NVL( OPS_FUNDING_DETAILS.REQUESTED_TOT_AMT,0) IS NULL THEN '0'

ELSE NVL( OPS_FUNDING_DETAILS.REQUESTED_TOT_AMT,0) +

CASE WHEN NVL(OPS_FUNDING_DETAILS.OBLIGATED_TOT_AMT,0) IS NULL THEN '0'

ELSE NVL(OPS_FUNDING_DETAILS.OBLIGATED_TOT_AMT,0)

END

END OBLIG, CaptureSQL2

Ed B Hall of Fame Points: 3666 More actions · Answer 3

Either use the CASE statement or NVL. I think this is the right CASE syntax for oracle.

SELECT CASE OPS_FUNDING_DETAILS.REQUESTED_TOT_AMT
           WHEN NULL THEN 0
           ELSE OPS_FUNDING_DETAILS.REQUESTED_TOT_AMT
        END +
        CASE OPS_FUNDING_DETAILS.OBLIGATED_TOT_AMT
           WHEN NULL THEN 0
           ELSE OPS_FUNDING_DETAILS.OBLIGATED_TOT_AMT
       END Total

Alternatively

SELECT  NVL(OPS_FUNDING_DETAILS.REQUESTED_TOT_AMT,0) +
        NVL(OPS_FUNDING_DETAILS.OBLIGATED_TOT_AMT,0) Total