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 <Condition> Then 1 Else 0 End. Try to google it

AK47
– AK47

2014-03-21 07:38:16 +00:00
Commented Mar 21, 2014 at 7:38 — AK47
– AK47, Commented Mar 21, 2014 at 7:38

pablomatico · Accepted Answer · 2014-03-21 07:58:05Z

1

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.

answered Mar 21, 2014 at 7:58

pablomatico

2,24221 silver badges25 bronze badges

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

Comments

DSS · Accepted Answer · 2014-03-21 07:58:51Z

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, 2014 at 7:58

DSS

7,2995 gold badges33 silver badges42 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:57:06Z

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, 2017 at 11:57

CommunityBot

11 silver badge

answered Mar 21, 2014 at 8:26

davmos

9,6074 gold badges45 silver badges46 bronze badges

Comments

Johnie Karr · Accepted Answer · 2014-03-21 14:30:47Z

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, 2014 at 14:30

Johnie Karr

2,8203 gold badges36 silver badges44 bronze badges

answered Mar 21, 2014 at 14:10

user3446787

271 silver badge4 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related