1
select doc_no,date,acc_no,amount,
if xvar=1
(select acc_head from my_crem c where c.acc_no=o.acc_no) as acc_head
else
(select acc_head from my_debm c where c.acc_no=o.acc_no) as acc_head
from my_ordm o;

In the above query, xvar is not a column name. It's a local variable name. Then how can I write the above query?

Smi
  • 13,850
  • 9
  • 56
  • 64
Ajeesh Kumar S
  • 11
  • 3

2 Answers2

3
select 
    o.doc_no,
    o.date,
    o.acc_no,
    o.amount, 
    CASE 
         WHEN @xvar = 1 THEN c.acc_head 
         ELSE d.acc_head
    END as acc_head
from 
    my_ordm o
    left join my_crem c on c.acc_no = o.acc_no   
    left join my_debm d on d.acc_no = o.acc_no;
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • here first acc_head is from my_crem table and second from my_debm table...can u help for this one... – Ajeesh Kumar S Apr 04 '11 at 07:38
  • thr xvar is not a column name it's a local variable ...thn how can i do that one ...xvar have 2 value either 1 or 2....plz help me – Ajeesh Kumar S Apr 04 '11 at 07:45
  • 1
    Put @xvar instead of xvar, and it should be fine. The order of the join is irrelevant in this context, just make sure the CASE is correct, so when @xvar = 1 you're getting acc_head from my_crem instead of my_debm. – cairnz Apr 04 '11 at 08:07
  • srry...now aLSO IT DOES'T WRKS....must declare the scalar variable like that one error comess.... – Ajeesh Kumar S Apr 04 '11 at 11:07
  • 1
    @Ajeesh Kumar S: So what is wrong then? You clarified in your question that `xvar` is a variable. Now the script is using the variable `@xvar`. It is a correct solution to your present question. Otherwise your question is missing important details. – Andriy M Apr 05 '11 at 05:02
0
select
    doc_no,
    date,
    acc_no,
    amount,
    Case @xvar 
           WHEN 1 Then (select acc_head from my_crem c where c.acc_no = o.acc_no)
           ELSE (select acc_head from my_debm c where c.acc_no = o.acc_no)
    END AS acc_head 
from my_ordm o;
Alaa
  • 545
  • 1
  • 5
  • 11