Home » SQL & PL/SQL » SQL & PL/SQL » Reorganize values in columns (Oracle 9.2.0.8, Unix)
Reorganize values in columns [message #317136] Tue, 29 April 2008 12:20 Go to next message
fchern
Messages: 2
Registered: April 2008
Location: New Jersey, USA
Junior Member
I'm trying to format a reporting table in a data mart. The source data contains Race Categories for each employee beginning in the first column 'RaceCat1'. RaceCat1 contains many values in the source data. In the output table, I want only R1 to be in RaceCat2, R2 in RaceCat3, R3 in RaceCat3 etc... I can do this in a long drawn out Union Materialized View, but would like to do it more efficiently in the PL/SQL I use to load my data mart tables. Anybody have any ideas??

Data Coming in:

EmpID   RaceCat1   RaceCat2   RaceCat3   RaceCat4
23421   R4         null       null       null
29832   R3         R4         null       null
23829   R1         R4         null       null


Desired Output:

EmpID   RaceCat1   RaceCat2   RaceCat3   RaceCat4
23421   null       null       null       R4
29832   null       null       R3         R4
23829   R1         null       null       R4



Thanks,
Frank C.
Re: Reorganize values in columns [message #317143 is a reply to message #317136] Tue, 29 April 2008 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL will never be faster than a good SQL.
Post your query and table description.

Regards
Michel
Re: Reorganize values in columns [message #317163 is a reply to message #317143] Tue, 29 April 2008 14:16 Go to previous messageGo to next message
fchern
Messages: 2
Registered: April 2008
Location: New Jersey, USA
Junior Member
I posted shorter version of code through level 3. It continues through level 10. Table Def follows:

SELECT STG_PERS_DATA.EMP_ID,
        STG_PERS_DATA.BEGIN_DT,
        STG_PERS_DATA.END_DT,
        STG_PERS_DATA.CHNG_DT,
        STG_PERS_DATA.CHNG_USER,
        STG_PERS_DATA.A_APPRAISER_NAME,
        STG_PERS_DATA.A_APPRAISAL_DT,
        STG_PERS_DATA.A_AFFECTS_REM,
        STG_PERS_DATA.A_NOTIFIED_IND,
       CASE RaceCat1
          WHEN '01' THEN
           RaceCat1
          ELSE
           ' '
       END AS CRIT_1,
       CASE RaceCat2
          WHEN '02' THEN
           RaceCat2
          ELSE
           CASE RaceCat1
          WHEN '02' THEN
           RaceCat1
          ELSE
           ' '
       END END AS CRIT_2,
       CASE RaceCat3
          WHEN '03' THEN
           RaceCat3
          ELSE
           CASE RaceCat1
          WHEN '03' THEN
           RaceCat1
          ELSE
           CASE RaceCat2
          WHEN '03' THEN
           RaceCat2
          ELSE
           ' '
       END END END AS CRIT_3,
       CASE RaceCat4
          WHEN '04' THEN
           RaceCat4
          ELSE
           CASE RaceCat1
          WHEN '04' THEN
           RaceCat1
          ELSE
           ' '
       END END AS CRIT_4,
       
	   ...
	   
  FROM HRSTG.STG_PERS_DATA


Table Def: STG_PERS_DATA
EMP_ID VARCHAR2
BEGIN_DT date
END_DT date
CHNG_DT  date
CHNG_USER  VARCHAR2 
A_APPRAISER_NAME VARCHAR2
A_APPRAISAL_DT date
A_AFFECTS_REM VARCHAR2
A_NOTIFIED_IND VARCHAR2
RaceCat1 VARCHAR2
RaceCat2 VARCHAR2
RaceCat3 VARCHAR2
RaceCat4 VARCHAR2
RaceCat5 VARCHAR2
RaceCat6 VARCHAR2
RaceCat7 VARCHAR2
RaceCat8 VARCHAR2
RaceCat9 VARCHAR2
 


Frank C.
Re: Reorganize values in columns [message #317199 is a reply to message #317163] Tue, 29 April 2008 22:11 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Hi Frank - looks like a good challenge. Dont doubt the power of the union in specific circumstances as this because (without the ALL clause) it employs a uniquesort algorithm that ensures mutual exclusion - (a rec in result set 1 is filtered from result set 2 and so on - notice the expanding case statements always having to account for the other categories)

however i skipped that approach and this is just off the top of my head so i could have fat fingered some syntax. I call
the main table for simplicity input_table and
try to go for a merge join by ordering the individual result set orders.

that may backfire in which case i'd prob go with use_hash and drop the order by clauses. I'd prob throw a bitmap index on racecat fields... here's my shorthand:

SELECT /*+ USE_MERGE(E,R1,R2,R3,R4) */
E.EMPID, NVL(R1.RC,'NULL'), NVL(R2.RC,'NULL'),NVL(R3.RC,'NULL'),NVL(R4.RC,'NULL)
FROM
(SELECT DISTINCT EMPID FROM INPUT_TABLE ORDER BY EMPID) E,

(SELECT EMPID, 'R1' AS RC FROM INPUT_TABLE I WHERE
I.RACECAT1 = 'R1' OR
I.RACECAT2 = 'R1' OR
I.RACECAT3 = 'R1' OR
I.RACECAT4 = 'R1'
ORDER BY EMPID
) R1,

(SELECT EMPID, 'R2' AS RC FROM INPUT_TABLE I WHERE
I.RACECAT1 = 'R2' OR
I.RACECAT2 = 'R2' OR
I.RACECAT3 = 'R2' OR
I.RACECAT4 = 'R2'
ORDER BY EMPID
) R2,

(SELECT EMPID, 'R3'AS RC FROM INPUT_TABLE I WHERE
I.RACECAT1 = 'R3' OR
I.RACECAT2 = 'R3' OR
I.RACECAT3 = 'R3' OR
I.RACECAT4 = 'R3'
ORDER BY EMPID
) R3,

(SELECT EMPID, 'R4'AS RC
FROM INPUT_TABLE I WHERE
I.RACECAT1 = 'R4' OR
I.RACECAT2 = 'R4' OR
I.RACECAT3 = 'R4' OR
I.RACECAT4 = 'R4'
ORDER BY EMPID
) R4

ETC...

I'm really curious on performance in merge vs hash.

if we have a primekey on empid and a bitmap idx on racecat fields the initial scans should fly and the sort order is perfect for a merge.

if you have under a million rows in the input table and this doesnt finish in a half hour or so send me the explain plan -
im a bit sleepy right now and trying to review that i dont get dup rows... try this select out as a thought.

if you are using pl/sql we can take advantage of bulk collections and forall inserts to make this fly like an eagle!

best wishes
-harry
Re: Reorganize values in columns [message #317208 is a reply to message #317199] Wed, 30 April 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Reorganize values in columns [message #321633 is a reply to message #317208] Tue, 20 May 2008 22:21 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Sorry Michel, just saw this response. Will do!

Regards,
Harry
Re: Reorganize values in columns [message #321788 is a reply to message #317143] Wed, 21 May 2008 05:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Despite Michel's protestations against PL/SQL, there are times when it makes good sense and is efficient. This is one of those times.

CREATE OR REPLACE TYPE racecat AS OBJECT(
    r1 varchar2(255)
,   r2 varchar2(255)
,   r3 varchar2(255)
,   r4 varchar2(255)
);
/

CREATE OR REPLACE FUNCTION f_racecat (
    s1 IN varchar2
,   s2 IN varchar2
,   s3 IN varchar2
,   s4 IN varchar2
) RETURN racecat IS
    r racecat := racecat(null, null, null, null);
BEGIN
    IF 'R1' IN (s1, s2, s3, s4) THEN
        r.r1 := 'R1';
    END IF;
    IF 'R2' IN (s1, s2, s3, s4) THEN
        r.r2 := 'R2';
    END IF;
    IF 'R3' IN (s1, s2, s3, s4) THEN
        r.r3 := 'R3';
    END IF;
    IF 'R4' IN (s1, s2, s3, s4) THEN
        r.r4 := 'R4';
    END IF;
    return(r);
END;
/


And the test case:
DROP TABLE racecat_test PURGE;
CREATE TABLE racecat_test (
    empid NUMBER
,   r1    varchar2(255)
,   r2    varchar2(255)
,   r3    varchar2(255)
,   r4    varchar2(255)
);

INSERT INTO racecat_test VALUES (23421,   'R4', null, null, null);
INSERT INTO racecat_test VALUES (29832,   'R3', 'R4', null, null);
INSERT INTO racecat_test VALUES (23829,   'R1', 'R4', null, null);

select empid, x.r.r1, x.r.r2, x.r.r3, x.r.r4
FROM (
    select empid, f_racecat(r1,r2,r3,r4) AS r
    from   racecat_test
) x;

EMPID R1 R2 R3 R4 
23421 -  -  -  R4 
29832 -  -  R3 R4 
23829 R1 -  -  R4 


Forgive my formatting, I only have access to Oracle via Apex at OTN.

Ross Leishman
Re: Reorganize values in columns [message #321887 is a reply to message #317136] Wed, 21 May 2008 13:57 Go to previous message
shivaram9
Messages: 35
Registered: August 2006
Member
I am using Decode here
CREATE TABLE racecat_test (
    empid NUMBER
,   r1    VARCHAR2(255)
,   r2    VARCHAR2(255)
,   r3    VARCHAR2(255)
,   r4    VARCHAR2(255)
);
INSERT INTO racecat_test
     VALUES (23421, 'R4', NULL, NULL, NULL);
INSERT INTO racecat_test
     VALUES (29832, 'R3', 'R4', NULL, NULL);
INSERT INTO racecat_test
     VALUES (23829, 'R1', 'R4', NULL, NULL);
SELECT empid,
       DECODE ((SELECT 1
                  FROM racecat_test rr
                 WHERE rr.empid = ract.empid
                   AND (   rr.r1 = 'R1'
                        OR rr.r2 = 'R1'
                        OR rr.r3 = 'R1'
                        OR rr.r4 = 'R1'
                       )),
               1, 'R1',
               'NULL'
              ) "R1",
       DECODE ((SELECT 1
                  FROM racecat_test rr
                 WHERE rr.empid = ract.empid
                   AND (   rr.r1 = 'R2'
                        OR rr.r2 = 'R2'
                        OR rr.r3 = 'R2'
                        OR rr.r4 = 'R2'
                       )),
               1, 'R2',
               'NULL'
              ) "R2",
       DECODE ((SELECT 1
                  FROM racecat_test rr
                 WHERE rr.empid = ract.empid
                   AND (   rr.r1 = 'R3'
                        OR rr.r2 = 'R3'
                        OR rr.r3 = 'R3'
                        OR rr.r4 = 'R3'
                       )),
               1, 'R3',
               'NULL'
              ) "R3",
       DECODE ((SELECT 1
                  FROM racecat_test rr
                 WHERE rr.empid = ract.empid
                   AND (   rr.r1 = 'R4'
                        OR rr.r2 = 'R4'
                        OR rr.r3 = 'R4'
                        OR rr.r4 = 'R4'
                       )),
               1, 'R4',
               'NULL'
              ) "R4"
  FROM racecat_test ract

Previous Topic: Move LOB to new tablespace but still visible in old...
Next Topic: help needed in simple pl/sql procedure
Goto Forum:
  


Current Time: Wed Dec 07 22:01:38 CST 2016

Total time taken to generate the page: 0.15031 seconds