INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Script

SQL Script

(OP)


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

 

RE: SQL Script


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;

RE: SQL Script

Hello ?  Hello ? Earth to aarthibox  - anyone home?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close