Solved

SQL Decode function for all values in a field

Posted on 2011-09-26
5
273 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 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 66
Oracle function works in 11g but not in 12c 21 72
SQL Query - Oracle 10g - Subract date from next data row 4 53
query in Oracle forms Builder 2 41
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

920 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

12 Experts available now in Live!

Get 1:1 Help Now