Convert Query to PL/SQL (IF i.div = 10 replace with W, ELSE IF 1

[iteach2]

Hi Gurus,

I have a query below:


select o.loc_num AS "LOCATION", o.case_id AS "CASE_NUM",
i.division AS "DIV", substr(o.store_num,1,length(o.store_num) -1) AS "STORE", o.ctrl_dt AS "DATE"
from ord_case o, cases c, item i
where o.case_id = c.case_id
AND c.item_num = i.item_num
AND o.ctrl_dt < sysdate-3/24
AND o.loc_num LIKE '10%%'
AND o.ctrl_user_id ='I285';

This query runs fine except I would like to have an IF/ELSE loop to replace a Number with its corresponding Letter.


For example if i.division is 10 replace 10 with WO; if i.division is 4 replace with H and so on until the end.

I am new to PL/SQL and really don't even know how to create such a thing; I have looked at examples but am a little confused just where to start and how to implement.

 

[LKBrwnDBA]

Use decode:

SELECT ...
DECODE(i.division,10,'WO',4,'H',...,'?') ...etc...

Notice the "pairs" and after the last pair you set the default value if no conditions have been met.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[iteach2]

Here is my code...I have done something wrong...I get error

ORA-01722: invalid number


select o.loc_num AS "LOCATION", o.case_id AS "CASE_NUM",
DECODE(i.division, 01,'WA', 02, 'ME', 03, 'CH', 04,'H', 05,'JE', 06,'FE', 07,'SH', 08,'WO', 09,'SA','00') AS "DIV", substr(o.store_num,1,length(o.store_num) -1) AS "STORE", o.ctrl_dt AS "DATE"
from ord_case o, cases c, item i
where o.case_id = c.case_id
AND c.item_num = i.item_num
AND o.ctrl_dt < sysdate-3/24
AND o.loc_num LIKE '10%%'
AND o.ctrl_user_id ='I285';

 

[iteach2]

Thank you very much LKBrwnDBA this works PERFECTLY .

I had to put single quotes around because the data was VARCHAR2 not number;

Thank once again!!!!


NEW CODE:

select o.loc_num AS "LOCATION", o.case_id AS "CASE_NUM",
DECODE(i.division, '01','WA','02','ME','03','CH','04','H','05','JE','06','FE','07','SH','08','WO','09','SA','N') AS "DIV", substr(o.store_num,1,length(o.store_num) -1) AS "STORE", o.ctrl_dt AS "DATE"
from ord_case o, cases c, item i
where o.case_id = c.case_id
AND c.item_num = i.item_num
AND o.ctrl_dt < sysdate-3/24
AND o.loc_num LIKE '10%%'
AND o.ctrl_user_id ='I285';

 

[amrita418]

What is the datatype of i.division?

Your code assumes it to be integer and throws an error when it cannot implicitly convert into integer.

 

[amrita418]

Too late I guess, you figured that yourself.