DECODE 2

[ptheriault]

I understand the DECODE() statement in it's basic form. But can anyone tell me what it is doing here?

DECODE(TA.POLICY_ID,'','N','Y') AS AGENCY_TERM

I thought it would select 'N' if the POLICY_ID is ''? But I don't understand the 'Y'.

 

[Dagon]

'Y' is the default condition - it gets set to 'Y' in all situations where policy_id is not ''.

 

[ptheriault]

Thanks Dagon, Can you explain then what the 'N' is for.
Here is my query
I guess I'm a little confured. Im still new to PL/SQL.

SELECT TP.POLICY_ID
,TP.POLICY_NUMBER
,TP.POL_EFF_DATE
,TP.POL_EXP_DATE
,DECODE(TA.POLICY_ID,'','N','Y') AS AGENCY_TERM

FROM T_POLICY T, T_POLICY_DETAIL TD, COMPANY C
WHERE T.POL_POLICY_STATUS <> 'CN'
AND T.POL_POLICY_NUMBER IS NOT NULL
AND T.POL_POLICY_ID = C.POLICY_ID
AND T.POL_POLICY_ID = TD.POD_POL_POLICY_ID
AND C.POL_EXP_DATE > SYSDATE) TP,

(SELECT VP.POLICY_ID
FROM PRODUCER VP, COMPANY TD
WHERE VP.POLICY_ID = TD.POLICY_ID
AND TD.POL_EXP_DATE > SYSDATE
AND VP.PVR_EFFECTIVE_END_DATE IS NOT NULL) TA

WHERE TP.POLICY_ID = TA.POLICY_ID(+)
AND TP.POLICY_ID = TL.POLICY_ID(+)
AND TP.POLICY_ID = TB.POLICY_ID(+)
AND TP.POLICY_ID = TLP.POLICY_ID(+)
AND TP.POLICY_ID = TS.POLICY_ID;

 

[KenCunningham]

Presumably it gets set to N where policy_id is ''.

I don't mind people who aren't what they seem. I just wish they'd make their mind up.

Alan Bennett.