Home » SQL & PL/SQL » SQL & PL/SQL » dynamic decode (10g, win xp)
dynamic decode [message #342711] Sun, 24 August 2008 10:20 Go to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
hii all

i have a sql statement like this

Select
   DECODE(H.COSTCENTER_CODE,'1442','I-SALES FOR RETAIL',
   '1432','I-SALES TO WHOLE SALE',C.MISC_TRX_TYPE) a5 
   From  TRX_HEADERS H,
   costcenters c
 


i want to make it more dynamic as i don't want to write in this
statment 'I-SALES FOR RETAIL'
i want when found '1442' then select from other table

for more calrification
'I-SALES FOR RETAIL'
i put it static value upon the value of '1442'
as there is another table called "costcenter" has a column named
type hold the value of the decode statment

i want my query to be something like this
to be like this


Select
   DECODE(H.COSTCENTER_CODE,'1442',select type from costcenters where code = '1442',
   '1432',select type from costcenters where code = '1432',C.MISC_TRX_TYPE) a5
   From  TRX_HEADERS H,
   costcenters c




[Updated on: Sun, 24 August 2008 10:22]

Report message to a moderator

Re: dynamic decode [message #342713 is a reply to message #342711] Sun, 24 August 2008 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case don't use decode, join the 2 tables.

Regards
Michel
Re: dynamic decode [message #342715 is a reply to message #342711] Sun, 24 August 2008 10:45 Go to previous messageGo to next message
emadnabil
Messages: 163
Registered: August 2007
Senior Member
i don't want to join the two table because the table of costcenters is a definition table
and the table of trx_headers holds the history
and i cannot join also because i don't want to match them because trx_headers data could be changed
Re: dynamic decode [message #342719 is a reply to message #342715] Sun, 24 August 2008 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This "DECODE(H.COSTCENTER_CODE,'1442',select type from costcenters where code = '1442'..." is a join that does not say its name.

The correct way is to join.

Regards
Michel
Re: dynamic decode [message #342723 is a reply to message #342711] Sun, 24 August 2008 11:36 Go to previous message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Previous Topic: last row from a query result
Next Topic: problem when passing values to Execute immediate
Goto Forum:
  


Current Time: Mon Dec 05 06:42:37 CST 2016

Total time taken to generate the page: 0.08392 seconds