how to implement if ..then condition in a sql query

Question

I have a logic in my plsql code and that uses a IF THEN ELSE logic . I want to move the logic to sql. How do I convert the logic of the IF THEN ELSE to SQL.

plsql logic

IF sns.quantity >= 0 /*stage_na_shipment sns*/
THEN
   l_code := 'O';/*l_code is local variable*/
ELSE
   l_code := 'C';
END IF;

SELECT bt_id  INTO l_bt_bt_id  FROM bill_type
WHERE code = l_code
AND ds_ds_id = 1;

how can i do the above logic using sql,i need to append the bt.id to this query

SELECT sns.order_num_cos, 
  1, 
  sns.ship_date, 
  sns.haulier_name, 
  sns.haulier_num, 
  sns.ship_date, 
  sns.order_num_cos, 
  sf.sf_id, 
  c.cust_id, 
  2, 
  1,
  m.mkt_id, 
  0, 
  sns.ship_date, 
  sns.po_number, 
  sns.ship_date, 
  bt.bt_id, 
  sns.ship_date, 
  sns.stor_loc
FROM stage_na_shipment sns, 
  market m,
  ship_from sf,
  customer c,
  bill_type bt
WHERE m.code = DECODE(SUBSTR(TO_CHAR(sns.client), 1, 4), 'US23', 'MM', 'US99', 'AFFCO') 
AND sf.source_key = sns.ship_from
AND c.true_gcdb_source_key = TO_CHAR(sns.del_point);

Use Case Then 1 Else 0 End. Try to google it – AK47 Mar 21 '14 at 07:38 — AK47, Mar 21 '14 at 07:38

score 1 · Answer 1 · answered Mar 21 '14 at 07:58

Basically, you could do it with a select case statement, something like this:

SELECT CASE 
        WHEN sns.quantity >= 0 then 'O'
        ELSE 'C'
       END l_code
FROM sns

Or with a DECODE:

SELECT DECODE(sign(sns.quantity),-1,'C','O') 
FROM sns

This means that the decode function returns 'C' if sign(sns.quantity) equals -1, and 'O' otherwise.

score 1 · Answer 2 · answered Mar 21 '14 at 07:58

1

You could create your syntax like this:

Select case when sns.quantity >= 0 then '0'
when sns.quantity < 0 then 'C' end as lcode from table_name_here

since your table contains an id from another table, you might have to use a join.

fiddle

answered Mar 21 '14 at 07:58

Rat-a-tat-a-tat Ratatouille

7,109
5
31
41

score 1 · Answer 3 · edited May 23 '17 at 11:57

1

Join the bill_type table, like so...

JOIN bill_type bt ON bt.code = CASE WHEN sns.quantity >= 0 THEN 'O' ELSE 'C' END
                 AND bt.ds_ds_id = 1

ANSI join syntax is generally preferred these days.

See ANSI vs. non-ANSI SQL JOIN syntax and Please use ANSI join syntax.

edited May 23 '17 at 11:57

Community

1
1

answered Mar 21 '14 at 08:26

davmos

9,324
4
40
43

score 0 · Answer 4 · edited Mar 21 '14 at 14:30

0

 SELECT BT_ID 
 FROM BILL_TYPE BT2
 WHERE (CASE 
            WHEN SNS_QUANTITY >=0 THEN 'O'
            ELSE 'C'
        END AS L_CODE) = BT.CODE
        AND ds_ds_id = 1;

you can replace the "bt.bt_id" in the sql code with the above code.

edited Mar 21 '14 at 14:30

Johnie Karr

2,744
2
35
44

answered Mar 21 '14 at 14:10

user3446787

27
1
4

how to implement if ..then condition in a sql query

4 Answers4