Home » SQL & PL/SQL » SQL & PL/SQL » Help Needed with SQL (Oracle 10G, Solaris)
Help Needed with SQL [message #314785] Thu, 17 April 2008 15:04 Go to next message
rkrishna
Messages: 4
Registered: March 2007
Location: Silicon Valley, Californi...
Junior Member
I am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0.

I have a table that has 2 columns. CATEGORY VARCHAR2(15), CODE VARCHAR2(30). Each code belongs to a Category. I would like to
create a report that first lists a Category and all codes below it. It would look something like this:

ORDER_STATUS - (this is the CATEGORY)
OS_A=Acknowledged
OS_M=Mismatch
OS_C=Closed
OS_F=Finalized
OS_O=Open
OS_R=Rejected
OS_S=Submitted
OS_V=Verified

T_STATUS - CATEGORY (this is the category)
TS_A=Active
TS_C=Canceled
TS_I=Interim
TS_N=New
TS_IA=Inactive

Is there a way to do this using sql? I tried the following sql:

select decode(a.sequence_number,1,a.category,a.code) lookup
from (select category, code lookup,
ROW_NUMBER() OVER (partition by category order by category) as sequence_number from lookup_code) a

However, obviously it always does not return me the first row,since it returns only the category when sequence number is 1.

Anyone has ideas on how to do this in SQL?
- Ramesh
rkrishna@yahoo.com

[Updated on: Thu, 17 April 2008 15:05]

Report message to a moderator

Re: Help Needed with SQL [message #314807 is a reply to message #314785] Thu, 17 April 2008 17:02 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about doing the following :

a) Read the forum guidelines
b) Post some create table script/insert statements or something like
SQL> with t
as
(
select 'ORDER_STATUS' category, null code from dual  union all
select null, 'OS_A' from dual
)
select * from t;


CATEGORY     CODE
------------ ----
ORDER_STATUS
             OS_A

Regards

Raj
Previous Topic: Finding code changes from source table
Next Topic: STRING TO RAW
Goto Forum:
  


Current Time: Mon Dec 05 11:04:53 CST 2016

Total time taken to generate the page: 0.08940 seconds