Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Decode function for all values in a field

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 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

730 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