IF ELSE condition in an SQL query

Question

I have a SQL query where in I have to fetch an additional param from a different table, only if a value is present in the primary table. I have tried using the if else clause, but it never works.

The SQL looks like this:

SELECT  
   hist.I_PAYT_REF AS PAY_REF,
   hist.C_USERID AS USER_ID,
   IF(@hist.I_PAYT_REF IS NULL)
   BEGIN
      SELECT 
         PAYT.I_PAYT_REQ_SUM AS PAYMENT_FILE_ID
      Join 
         SP.TSP_CP_PAYT_RQ_DTL PAYT ON PAYT.I_PAYT_REF = hist.I_PAYT_REF
   END
FROM 
   sp.TSP_CP_HIST hist
WHERE 
   hist.C_HIST_TYPE ='V'

All I want to do is fetch a a new parameter if the hist.I_PAYT_REF is not null. Any help will be greatly appreciated.

Thanks, Yeshwanth

SQL is just the Structured Query Language - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what database system (and which version) you're using.... — marc_s
– marc_s, Commented Oct 27, 2012 at 15:05
@ marc_s: I apologize for not providing the complete details. I will get the details and update the post at the earliest. — Yeshwanth Kota
– Yeshwanth Kota, Commented Oct 27, 2012 at 15:45

jenson-button-event · Accepted Answer · 2012-10-27 15:26:14Z

2

You just need a left join, if I_PAYT_REF is null the join will not exclude the main hist record and PAYMENT_FILE_ID will be returned null anyway.

SELECT  
    hist.I_PAYT_REF AS PAY_REF,
    hist.C_USERID AS USER_ID,
    PAYT.I_PAYT_REQ_SUM AS PAYMENT_FILE_ID
    FROM 
         SP.TSP_CP_HIST hist left join 
         SP.TSP_CP_PAYT_RQ_DTL PAYT ON PAYT.I_PAYT_REF = hist.I_PAYT_REF 
    WHERE hist.C_HIST_TYPE ='V'

edited Oct 27, 2012 at 15:26

answered Oct 27, 2012 at 15:07

jenson-button-event

19k13 gold badges99 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yeshwanth Kota Over a year ago

This is the answer that worked for me and that too without if condition which I am not familiar with. But the other suggestions worked too :)

roman · Accepted Answer · 2012-10-27 15:29:22Z

2

sometimes you may be need that kind of inline if, and it's a CASE clause, it works like inline if :)

select
    hist.I_PAYT_REF as PAY_REF,
    hist.C_USERID as USER_ID,
    case
        when hist.I_PAYT_REF is null then PAYT.I_PAYT_REQ_SUM
        else null
    end as PAYMENT_FILE_ID
from sp.TSP_CP_HIST as hist
    left outer join SP.TSP_CP_PAYT_RQ_DTL as PAYT on PAYT.I_PAYT_REF = hist.I_PAYT_REF
where hist.C_HIST_TYPE ='V'

but as @jenson-button-event says - there's no point to use case here - if I_PAYT_REF is null then left outer join return no records from SP.TSP_CP_PAYT_RQ_DTL. So you can write just

select
    hist.I_PAYT_REF as PAY_REF,
    hist.C_USERID as USER_ID,
    PAYT.I_PAYT_REQ_SUM
from sp.TSP_CP_HIST as hist
    left outer join SP.TSP_CP_PAYT_RQ_DTL as PAYT on PAYT.I_PAYT_REF = hist.I_PAYT_REF
where hist.C_HIST_TYPE ='V'

edited Oct 27, 2012 at 15:29

answered Oct 27, 2012 at 15:05

roman

118k30 gold badges205 silver badges209 bronze badges

1 Comment

jenson-button-event Over a year ago

your 'case' is redundant see my answer

John Woo · Accepted Answer · 2012-10-27 15:12:05Z

0

Join both tables and apply CASE statement on your select clause.

SELECT  
    hist.I_PAYT_REF AS PAY_REF,
    hist.C_USERID AS USER_ID,
    CASE WHEN hist.I_PAYT_REF IS NULL
        THEN PAYT.I_PAYT_REQ_SUM
        ELSE I_PAYT_REF
    END as PAYMENT_FILE_ID
FROM    sp.TSP_CP_HIST hist
        INNER JOIN SP.TSP_CP_PAYT_RQ_DTL AS PAYT
              ON PAYT.I_PAYT_REF = hist.I_PAYT_REF
WHERE hist.C_HIST_TYPE ='V'

edited Oct 27, 2012 at 15:12

answered Oct 27, 2012 at 15:07

John Woo

265k70 gold badges509 silver badges500 bronze badges

2 Comments

roman Over a year ago

I think it's better to use left outer join instead of inner join or you can miss some data, but it depends on schema

roman Over a year ago

also you have two ON keywords in the JOIN clause

Collectives™ on Stack Overflow

IF ELSE condition in an SQL query

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related