Solved

SQL Decode function for all values in a field

Posted on 2011-09-26
5
272 Views
Last Modified: 2012-05-12
I am currently using the code below to find all data values associated with difference event actions for reports in a table. Right now I need to add a line for each component that I want to find a date value for. Is there way to modify the code so that I can just have it pull up a new colunn for every unique action item that is in the column "ACTION". So say there are 15 different unique values I will end up with 15 columns across the top. Not sure if this is possible.

select * from
(select a.rptkey,
       b.payid,
       b.empfirst,
       b.emplast,
       b.empno,
       to_char (c.startdate,'MM/DD/YYYY') as "CREATE"
       ,max(decode(action,'SUBMIT',to_char (a.evtdate,'MM/DD/YYYY'))) SUBMIT
       ,max(decode(action,'APPROVE',to_char (a.evtdate,'MM/DD/YYYY'))) APPROVE
       ,max(decode(action,'LOADED',to_char (a.evtdate,'MM/DD/YYYY'))) LOADED
       ,max(decode(action,'AVAILPAY',to_char (a.evtdate,'MM/DD/YYYY'))) AVAILPAY
       ,max(decode(action,'LEDGER EXTRACT',to_char (a.evtdate,'MM/DD/YYYY'))) LEDGER
 from jjgxrsadm.evt a, jjgxrsadm.emp b, jjgxrsadm.clm c
 where
 a.rptkey = b.rptkey
 and a.rptkey = c.rptkey
 group by a.rptkey,
       b.payid,
       b.empfirst,
       b.emplast,
       b.empno,
       to_char (c.startdate,'MM/DD/YYYY'))
order by rptkey;

Open in new window

0
Comment
Question by:aehrenwo
  • 3
  • 2
5 Comments
 
LVL 31

Expert Comment

by:awking00
ID: 36599797
Can you provide some sample data for the three tables and what your deisred output should be?
0
 
LVL 31

Expert Comment

by:awking00
ID: 36599802
Also, what version of Oracle?
0
 

Author Comment

by:aehrenwo
ID: 36599944
I believe iti s Oracle 10g

I have attached a small sample from each table. The main one that has the variable I am interested is the EVT table. I basically want to have it find all unique values for field Action and put them across the top and have the unqiue RPTKEY along the left hand side and then have have the asscoaited "EVTDATE" for that "RPTKEY" as the value.

I limited the scope to only 1 unique employee number but in reality these tables would have thousands of different employees. Not sure if that changes things.

I also attached what I would prefer the result to look like in case the detail above is unclear.  For the ACTION field there can be up to 15 differnent values.

Thanks

Adam E
CLM.xls
EMP.xls
EVT.xls
RESULT.xls
0
 
LVL 31

Accepted Solution

by:
awking00 earned 250 total points
ID: 36600507
I was hoping you were using 11g or better which introduced the PIVOT function to do what you want. I'm afraid that, prior to 11g, you will need to write decode statements for every possible combination to accomplish what you need :-(
0
 

Author Closing Comment

by:aehrenwo
ID: 36600582
was directed that there was no easy way to accomplish what I want in the version of Oracle we use.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now