Solved

SQL Decode function for all values in a field

Posted on 2011-09-26
5
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

734 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