Home » RDBMS Server » Server Utilities » DECODE (Oracle 9i R2)
DECODE [message #396714] Tue, 07 April 2009 16:26 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I am using DECODE in my Insert sql while loading from Source table to Target table and i have to replace the Sales dept number with Sales name.

My logic was:
If SALES =  01  then the DEPT_NAME = SAN
If SALES =  02  then the DEPT_NAME = GOOD
If SALES =  03  then the DEPT_NAME = GREAT
If SALES =  04  then the DEPT_NAME = CLEAN


My current decode is:
DECODE(SALES,  '01','SAN',  '02', 'GOOD',  '03', 'GREAT',  '04', 'CLEAN')


Now business logic get changed as they added now DEPT code with Sales code:
If SALES =  01 and DEPT = 01 then the DEPT_NAME = SAN
If SALES =  02 and DEPT = 09 then the DEPT_NAME = GOOD
If SALES =  02 and DEPT = 15 then the DEPT_NAME = SOLD ==> this one added with different dept
If SALES =  03 and DEPT = 10 then the DEPT_NAME = GREAT
If SALES =  04 and DEPT = 11 then the DEPT_NAME = CLEAN

How can i add change?
Is it DECODE take both the if arguments?
Please let me know if you want full insert:

My insert is like:
truncate table TARGET_table
/
Insert into TARGET_TABLE (
 ID,
 DESC   ,
 DEPT         ,
 SALES        ,
 DEPT_NAME
)
select
ID,
DESC   ,
 DEPT         ,
 SALES        ,
 DECODE(SALES,  '01',  'SAN',  '02',  'GOOD',  '03',  'GREAT',  '04', 'CLEAN')
from  (select ID,
 DESC   ,
 SALES             ,
 DECODE(SALES,  '01',  'SAN',  '02',  'GOOD',  '03',  'GREAT',  '04', 'CLEAN') ,
row_number() over (partition by  id, sales, DEPT order by id, sales, DEPT desc) rnum
from SOURCE
)
  Where rnum = 1 and sales IN ('01', '02', '03', '04')
and DEPT is not null
/
commit;


Thanks for your help!

Re: DECODE [message #396716 is a reply to message #396714] Tue, 07 April 2009 16:41 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Use the CASE Expression
Re: DECODE [message #396732 is a reply to message #396716] Tue, 07 April 2009 19:57 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Pablolee for looking into and suggestion.
Could you please chow me an example if you don't mind?

Thanks,
Re: DECODE [message #396733 is a reply to message #396732] Tue, 07 April 2009 20:07 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check CASE.
Re: DECODE [message #396747 is a reply to message #396733] Tue, 07 April 2009 23:15 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks EBrian for the link.
I tried just to test in SELECT but getting error:
Missing key word

I an using following way:
SELECT SALES, 
CASE SALES when '01' and dept 01 then 'SAN'
               when '02' and DEPT 09 then 'SOLD'
               when '02' and DEPT 15 then 'GOOD'
               ELSE 'NO DEPT' END
FROM TARGET WHERE sales IN ('01', '02','03','04')



Thanks for your help!
Re: DECODE [message #396749 is a reply to message #396714] Tue, 07 April 2009 23:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I tried just to test in SELECT but getting error:
ERROR? What Error? I do not see any error.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm
RTFM to learn valid SQL syntax.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

[Updated on: Tue, 07 April 2009 23:23]

Report message to a moderator

Re: DECODE [message #396752 is a reply to message #396749] Tue, 07 April 2009 23:49 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Try your query now.

IND> select sal,deptno,case when deptno =30 and sal between 800 and 1300 then 'sriram'
  2  else 'DAWN' end
  3  from emp;
       800         20 DAWN
      1600         30 DAWN
      1250         30 sriram
      2975         20 DAWN
      1250         30 sriram
      2850         30 DAWN
      2450         10 DAWN
      3000         20 DAWN
      5000         10 DAWN
      1500         30 DAWN
      1100         20 DAWN
       950         30 sriram
      3000         20 DAWN
      1300         10 DAWN
      7000            DAWN

15 rows selected.




@Black swan ebrain alreadysuggested that link,but he did n`t get where is doing mistake...so please provide him direct answer
Re: DECODE [message #396753 is a reply to message #396749] Tue, 07 April 2009 23:50 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@poratips

always post yous question with what you tried and if you got any kind of errors paste that error also.

read the post guide lines.

[Updated on: Tue, 07 April 2009 23:55]

Report message to a moderator

Re: DECODE [message #396756 is a reply to message #396714] Wed, 08 April 2009 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
ramoradba
I have an error.
Please tell me how to fix it.

Get creative & provide useful response!







Re: DECODE [message #396763 is a reply to message #396756] Wed, 08 April 2009 00:20 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:

@poratips
I tried just to test in SELECT but getting error:
Missing key word



IND> ed
Wrote file afiedt.buf

  1  select sal,deptno,case deptno when 30 and sal between 800 and 1300 then 'sriram'
  2  else 'DAWN' end
  3  from emp
  4* where hiredate in('17-DEC-80','12-JAN-83')
IND> /
select sal,deptno,case deptno when 30 and sal between 800 and 1300 then 'sriram'
                                      *
ERROR at line 1:
ORA-00905: missing keyword
This is what his case i guess


For you...
@Mr.Blackswan,
Forum will help you.

if you post atleast some part of work you did, (i.e why you got that error).



Thanks & regards
Sriram



Re: DECODE [message #396764 is a reply to message #396714] Wed, 08 April 2009 00:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>This is what his case i guess
Why should you waste your time trying to guess what actually happened?
Numerous other happenings could have resulted in same error.
If you choose to waste your time guessing what did or did not happen, so be it; your time & your effort.
Re: DECODE/CASE [message #398747 is a reply to message #396764] Fri, 17 April 2009 07:19 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
Than you very much for your resposne.
I was able to troubleshoot CASE example you have provided and it helped me to check the syntex error and it got resolved.
Appreciated all your help!

How can i use CASE statements in SQL*Loader or External table?

Regards,
Poratips
Re: DECODE/CASE [message #398756 is a reply to message #398747] Fri, 17 April 2009 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
External table is used along with SQL, so you can do it when you load the target table(s) from the external table.

For SQL*loader, I let Barbara answers you.

Regards
Michel
Re: DECODE/CASE [message #398792 is a reply to message #398756] Fri, 17 April 2009 10:08 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Michel for your quick response.
I will keep in mind that option but i will wait for Barbara's response.

Thanks once again1
Re: DECODE/CASE [message #399198 is a reply to message #398747] Tue, 21 April 2009 02:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
poratips wrote on Fri, 17 April 2009 05:19

How can i use CASE statements in SQL*Loader?



-- test.ctl:
load data
infile *
into table target_table
fields terminated by ','
trailing nullcols
(sales,
 dept,
 dept_name "case when :sales='01' and :dept='01' then 'SAN'
                 when :sales='02' and :dept='09' then 'GOOD'
                 when :sales='02' and :dept='15' then 'SOLD'
                 when :sales='03' and :dept='10' then 'GREAT'
                 when :sales='04' and :dept='11' then 'CLEAN'
            end")
begindata:
01,01,
02,09,
02,15,
03,10,
04,11,


SCOTT@orcl_11g> create table target_table
  2    (sales	   varchar2 ( 4),
  3  	dept	   varchar2 ( 4),
  4  	dept_name  varchar2 (15))
  5  /

Table created.

SCOTT@orcl_11g> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11g> select * from target_table
  2  /

SALE DEPT DEPT_NAME
---- ---- ---------------
01   01   SAN
02   09   GOOD
02   15   SOLD
03   10   GREAT
04   11   CLEAN

SCOTT@orcl_11g>

Re: DECODE/CASE [message #402137 is a reply to message #399198] Thu, 07 May 2009 16:17 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks much! it resolve my problem.

Appreciated your help!

Regards,
Re: DECODE/CASE [message #402600 is a reply to message #402137] Mon, 11 May 2009 11:00 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks lot Barbara.
Its resolved my problem but I ma getting error while adding more Decode CASE statement.
Is it any limiit?
I am using:

load data
infile 'dept.txt'
into table target_table
fields terminated by ','
trailing nullcols
(sales,
 dept,
 dept_name "case when :sales='01' and :dept='01' then 'SAN'
                 when :sales='02' and :dept='09' then 'GOOD'
                 when :sales='02' and :dept='15' then 'SOLD'
                 when :sales='03' and :dept='10' then 'GREAT'
                 when :sales='04' and :dept='11' then 'CLEAN'
                 when :sales='05' and :dept='12' then 'dept5'
                 when :sales='06' and :dept='13' then 'dept6'
                 when :sales='07' and :dept='01' then 'dept7'
                 when :sales='08' and :dept='09' then 'dept8'
                 when :sales='09' and :dept='15' then 'dept9'
                 when :sales='10' and :dept='10' then 'dept10'
                 when :sales='11' and :dept='11' then 'dept11'
                 when :sales='12' and :dept='12' then 'dept12'
                 when :sales='13' and :dept='13' then 'dept13'
                 when :sales='14' and :dept='12' then 'dept14'
                 when :sales='15' and :dept='13' then 'dept15'
                 ELSE 'NO DEPT'
            end")


Error:
Token longer than max allowable length of 258 chars
      WHEN :sales= '01' AND :dept='01' then 'SAN'


Thanks for your help!
Re: DECODE/CASE [message #402632 is a reply to message #402600] Mon, 11 May 2009 13:00 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
poratips wrote on Mon, 11 May 2009 12:00

but I ma getting error while adding more Decode CASE statement.
Is it any limiit?

Error:
Token longer than max allowable length of 258 chars
      WHEN :sales= '01' AND :dept='01' then 'SAN'




Put two and two together.
Re: DECODE/CASE [message #402636 is a reply to message #402632] Mon, 11 May 2009 13:13 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks joy!
I am little confuse with your answer.
Could you please let me know what exactly you want me to make CASE statements?

when :sales='02' and :dept='09' then 'GOOD'
when :sales='02' and :dept='15' then 'SOLD'


What exactly you are suggesting?

There are sales = 02 can be repeated but dept is different.

Thanks,


Re: DECODE/CASE [message #402646 is a reply to message #402636] Mon, 11 May 2009 15:19 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I meant put two and two together as a slang term pointing to the fact that the error states:

Token longer than max allowable length of 258 chars

and asking you to look at your token for dept_name

[Updated on: Mon, 11 May 2009 15:21]

Report message to a moderator

Re: DECODE/CASE [message #402648 is a reply to message #402646] Mon, 11 May 2009 15:51 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks once again Joe for your time.
I couldn't understand your term still, sorry about it.
Are you trying to tell that whole CASE statements is longer that it allowable 255 chars?

regrads,
Poratips
Re: DECODE/CASE [message #402662 is a reply to message #402648] Mon, 11 May 2009 23:08 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Yes, it is saying that the case statement is too long. As a workaround, put the case statement in a function and use the function in your SQL*Loader control file.
Previous Topic: table mode exp/imp
Next Topic: DMP FILE IMPORT
Goto Forum:
  


Current Time: Fri Dec 09 17:28:08 CST 2016

Total time taken to generate the page: 0.18194 seconds