How to fetch data from another table based on condition with in a select query in SQL?

Question

0.00/5 (No votes)

See more:

I want to fetch data from a table (say AutoReturnPostingDetails) or from another table (say Paramconfig) based on status with in a main select query in sql.

My query is as below:

select  id,                           
CASE WHEN ITCFinal.AutoReturnStatus=7004          
      THEN (select DebitAccNo from AutoReturnPostingDetails AP         
      where AP.OutwardInstFinalId=ITCFinal.OutwardInstFinalId)        
      ELSE @DebitAccount END AS DebitAccount       
                                         
                                          
 FROM OUT_ITC_Final ITCFinal

What I have tried:

Select  id,                           
CASE WHEN ITCFinal.AutoReturnStatus=7004          
      THEN (select DebitAccNo from AutoReturnPostingDetails AP         
      where AP.OutwardInstFinalId=ITCFinal.OutwardInstFinalId)        
      ELSE @DebitAccount END AS DebitAccount       
                                         
                                          
 FROM OUT_ITC_Final ITCFinal

Posted 7-Sep-17 6:14am

Alex2 101

Updated 7-Sep-17 9:53am

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2017-09-07T09:53:00

Solution 1

You'll want to use a join instead. Essentially, if I understand your attempt at SQL correctly, you want to use the DebitAccNo from a matching record if it exists and if it does not you want to use the passed in parameter. Use LEFT JOIN for this.

SQL

SELECT id, COALESCE(t2.DebitAccNo, @DebitAccount) 
FROM OUT_ITC_Final t1 
LEFT JOIN AP.OutwardInstFinalID t2 ON t1.OutwardInstFinalID = t2.OutwardInstFinalID
WHERE t1.AutoReturnStatus = 7004

Something along these lines.

Posted 7-Sep-17 9:53am

ZurdoDev

Comments

[no name] 8-Sep-17 4:48am

This will not work. It takes the first value if there on joining. I used a scalar function and passed the parameter as required and returned Account no based on the condition.

ZurdoDev 8-Sep-17 7:33am

I don't follow, but if you got it working, good for you. It still sounds like you are overcomplicating it.

How to fetch data from another table based on condition with in a select query in SQL?

1 solution

Solution 1

Add your solution here

Preview 0

How to fetch data from another table based on condition with in a select query in SQL?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us