SQL CASE with multiple join

I am trying to run a sql select code as below. I am trying to fetch the trans_date which will either be in asutrans or asuhistr. But the below code doesnt give me any value and nor does it fail.

Do you think we can use CASE statement like that?

SELECT 
    v.voucher_no,
    CASE
        WHEN t.voucher_no = v.voucher_no THEN t.trans_date
        ELSE t1.trans_date
    END AS valuedate
FROM
    aipdethis v
    JOIN asutrans t ON t.client = v.client and t.voucher_no = v.voucher_no
    JOIN asuhistr t1 ON  t1.client = v.client  and t1.voucher_no = v.voucher_no
WHERE
    v.voucher_no ='22094200'

If I run the codes separately, then it does give me the result from asihistr. As below-

SELECT
    v.voucher_no, t1.trans_Date FROM  aipdethis v
    JOIN asuhistr t1 ON t1.client = v.client and t1.voucher_no = v.voucher_no
WHERE
    v.voucher_no ='22094200'

Answer

You cannot have case statement which checks voucher_no because you are already joining in the join query. I think this is what you’re expecting:

select 
    v.voucher_no,
    CASE
         WHEN t.trans_date is null then t1.trans_date
         ELSE t.trans_date
    END AS valuedate
    
   FROM  aipdethis v
   JOIN  asutrans t ON  t.client = v.client and t.voucher_no = v.voucher_no
   JOIN asuhistr t1 ON  t1.client = v.client  and t1.voucher_no = v.voucher_no
  where v.voucher_no ='22094200';