SQL Script

[aarthibox]

Hi Friends

I am stuck writing a SQL script, I will have to extract the CRDT_STS of each customer from a table with below logic.

IF DRVR_CLCT_IN=’Y’ then CRDT_STS = ‘Y’
else IF DRVR_CLCT_IN=’N’ and PARN_CMF_ID is null then CRDT_STS=’N’
else IF DRVR_CLCT_IN=’N’ and PARN_CMF_ID is not null then CRDT_STS= DRVR_CLCT_IN of parent customer
else CRDT_STS = ‘X’

I am half way done with SQL and stuck with condition 3.

Select CUS_ID, PARN_CMF_ID, DRVR_CLCT_IN,
Case when DRVR_CLCT_IN=’Y’ then ‘Y’
When DRVR_CLCT_IN=’N’ and PARN_CMF_ID is Null then ‘N’
When DRVR_CLCT_IN=’N’ and PARN_CMF_ID is not null then
Else ‘X ‘ END AS CRDT_STS


CUS_ID PARN_CMF_ID DRVR_CLCT_IN
1234-1-001 ? Y
1234-2-001 1234-1-001 N
1234-2-002 1234-1-001 N
3235-1-001 ? N
3235-22-001 3235-1-001 Y
3235-23-001 3235-22-001 Y
3235-24-050 3235-23-001 N

So for the above data I should be able to derive my CRDT_STS as follows

CUS_ID PARN_CMF_ID DRVR_CLCT_IN CRDT_STS
1234-1-001 ? Y Y
1234-2-001 1234-1-001 N Y
1234-2-002 1234-1-001 N Y
3235-1-001 ? N N
3235-22-001 3235-1-001 Y Y
3235-23-001 3235-22-001 Y Y
3235-24-050 3235-23-001 N Y


Please please give me for the Solution for this.

Murali

 

[GuptaSteve]

You have not specified your DBMS, nor your table name, but assuming it is Sqlbase, as this is Sqlbase forum, something like.....

Select CUS_ID, PARN_CMF_ID, DRVR_CLCT_IN,
@DECODE( DRVR_CLCT_IN,
'Y' , 'Y',
'N', @DECODE(PARN_CMF_ID ,NULL,'N' ,DRVR_CLCT_IN ),
'X') as CRDT_SDS
from YOUR_TABLE;

 

[GuptaSteve]

Hello ? Hello ? Earth to aarthibox - anyone home?