?
Solved

SQL Decode function for all values in a field

Posted on 2011-09-26
5
Medium Priority
?
286 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 32

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 32

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 32

Accepted Solution

by:
awking00 earned 1000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

864 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