Solved

Help with a pivot VARRAY query. Totally weird results are being returned.

Posted on 2008-10-11
4
386 Views
Last Modified: 2012-05-05
I am trying to write a pivot query for a view which takes the values of a VARRAY column and turns each value into a column in the view. I almost have it, but I'm having a strange problem. The wrong values are being returned. I know I'm doing something stupid, but I can't figure out what.



If I execute the following query, I get the correct values from the VARRAY, with the correct indices, as you can see from the result set:
 
	SQL> select rownum as rn, column_value as cc_res from table(select cc_result from orig_jcsd_gh where sample_id=1);
	
	        RN CC_RES
	---------- ------
	         1 1
	         2 1
	         3 1
	         4 0
	         5 1
	         6 1
	         7 1
	         8 1
	         9 1
	        10 1
	        11 1
	        12 0
	        13 1
	        14 1
	        15 0
	        16 1
	        17 0
	        18 0
	        19 0
	        20 0
	
	20 rows selected.
 
This is correct, here is the output of the actual VARRAY being queried:
 
	SQL> SELECT CC_RESULT FROM ORIG_JCSD_GH WHERE SAMPLE_ID=1;
	
	CC_RESULT
	--------------------------------------------------------------------------------
	CHRARAY20('1', '1', '1', '0', '1', '1', '1', '1', '1', '1', '1', '0', '1', '1', '0', '1', '0', '0', '0', '0')
 
What I'm shooting for is a view which has columns like the following:
 
sampling_id,
sample_id,
document_id,
CC_RESULT1,
CC_RESULT2,
CC_RESULT3,
CC_RESULT4,
CC_RESULT5,
CC_RESULT6,
CC_RESULT7,
CC_RESULT8,
CC_RESULT9,
CC_RESULT10,
CC_RESULT11,
CC_RESULT12,
etc...
 
To do this I have come up with the following query, which kind of works but the VARRAY values seem to be out of order or something, and I can't seem to figure out why. It is very important to be able to identify exactly which value is which in the VARRAY:
 
SELECT sample_id, 
   sampling_id, 
   document_id, 
   MAX(CASE rn WHEN 1 THEN CC_RES ELSE NULL END) CC_RESULT1,
   MAX(CASE rn WHEN 2 THEN CC_RES ELSE NULL END) CC_RESULT2,
   MAX(CASE rn WHEN 3 THEN CC_RES ELSE NULL END) CC_RESULT3,
   MAX(CASE rn WHEN 4 THEN CC_RES ELSE NULL END) CC_RESULT4,
   MAX(CASE rn WHEN 5 THEN CC_RES ELSE NULL END) CC_RESULT5,
   MAX(CASE rn WHEN 6 THEN CC_RES ELSE NULL END) CC_RESULT6,
   MAX(CASE rn WHEN 7 THEN CC_RES ELSE NULL END) CC_RESULT7,
   MAX(CASE rn WHEN 8 THEN CC_RES ELSE NULL END) CC_RESULT8,
   MAX(CASE rn WHEN 9 THEN CC_RES ELSE NULL END) CC_RESULT9,
   MAX(CASE rn WHEN 10 THEN CC_RES ELSE NULL END) CC_RESULT10,
   MAX(CASE rn WHEN 11 THEN CC_RES ELSE NULL END) CC_RESULT11,
   MAX(CASE rn WHEN 12 THEN CC_RES ELSE NULL END) CC_RESULT12,
   MAX(CASE rn WHEN 13 THEN CC_RES ELSE NULL END) CC_RESULT13,
   MAX(CASE rn WHEN 14 THEN CC_RES ELSE NULL END) CC_RESULT14,
   MAX(CASE rn WHEN 15 THEN CC_RES ELSE NULL END) CC_RESULT15,
   MAX(CASE rn WHEN 16 THEN CC_RES ELSE NULL END) CC_RESULT16,
   MAX(CASE rn WHEN 17 THEN CC_RES ELSE NULL END) CC_RESULT17,
   MAX(CASE rn WHEN 18 THEN CC_RES ELSE NULL END) CC_RESULT18,
   MAX(CASE rn WHEN 19 THEN CC_RES ELSE NULL END) CC_RESULT19,
   MAX(CASE rn WHEN 20 THEN CC_RES ELSE NULL END) CC_RESULT20
FROM (
      SELECT sample_id, sampling_id, document_id, column_value AS CC_RES, row_number() over
            (partition by sample_id order by sample_id) rn
      FROM ORIG_JCSD_gh, table(CC_RESULT)
     )
WHERE sample_id = 1 
GROUP BY sample_id;
 
Here is the result, written transposed for readability since only 1 row is returned:
 
SAMPLE_ID = 1
SAMPLING_ID = 1
DOCUMENT_ID = 2			(VALUE CC_RESULT SHOULD BE)
CC_RESULT1 = 1				1
CC_RESULT2 = 0				1	**DISCREPANCY**
CC_RESULT3 = 1				1
CC_RESULT4 = 0				0
CC_RESULT5 = 1				1
CC_RESULT6 = 1				1
CC_RESULT7 = 1				1
CC_RESULT8 = 1				1
CC_RESULT9 = 1				1
CC_RESULT10 = 1				1
CC_RESULT11 = 1				1
CC_RESULT12 = 0				0
CC_RESULT13 = 1				1
CC_RESULT14 = 1				1
CC_RESULT15 = 0				0
CC_RESULT16 = 1				1
CC_RESULT17 = 0				0
CC_RESULT18 = 0				0
CC_RESULT19 = 0				0
CC_RESULT20 = 1				0	**DISCREPANCY**
 
Now then, there are a couple of discrepancies in this statement which uses the WHERE clause of SAMPLE_ID=1. If I take out the WHERE clause things get even stranger. Here's the SQL and output from that:
 
	SQL> SELECT sample_id,
	  2     sampling_id,
	  3     document_id,
	  4     MAX(CASE rn WHEN 1 THEN CC_RES ELSE NULL END) CC_RESULT1,
	  5     MAX(CASE rn WHEN 2 THEN CC_RES ELSE NULL END) CC_RESULT2,
	  6     MAX(CASE rn WHEN 3 THEN CC_RES ELSE NULL END) CC_RESULT3,
	  7     MAX(CASE rn WHEN 4 THEN CC_RES ELSE NULL END) CC_RESULT4,
	  8     MAX(CASE rn WHEN 5 THEN CC_RES ELSE NULL END) CC_RESULT5,
	  9     MAX(CASE rn WHEN 6 THEN CC_RES ELSE NULL END) CC_RESULT6,
	 10     MAX(CASE rn WHEN 7 THEN CC_RES ELSE NULL END) CC_RESULT7,
	 11     MAX(CASE rn WHEN 8 THEN CC_RES ELSE NULL END) CC_RESULT8,
	 12     MAX(CASE rn WHEN 9 THEN CC_RES ELSE NULL END) CC_RESULT9,
	 13     MAX(CASE rn WHEN 10 THEN CC_RES ELSE NULL END) CC_RESULT10,
	 14     MAX(CASE rn WHEN 11 THEN CC_RES ELSE NULL END) CC_RESULT11,
	 15     MAX(CASE rn WHEN 12 THEN CC_RES ELSE NULL END) CC_RESULT12,
	 16     MAX(CASE rn WHEN 13 THEN CC_RES ELSE NULL END) CC_RESULT13,
	 17     MAX(CASE rn WHEN 14 THEN CC_RES ELSE NULL END) CC_RESULT14,
	 18     MAX(CASE rn WHEN 15 THEN CC_RES ELSE NULL END) CC_RESULT15,
	 19     MAX(CASE rn WHEN 16 THEN CC_RES ELSE NULL END) CC_RESULT16,
	 20     MAX(CASE rn WHEN 17 THEN CC_RES ELSE NULL END) CC_RESULT17,
	 21     MAX(CASE rn WHEN 18 THEN CC_RES ELSE NULL END) CC_RESULT18,
	 22     MAX(CASE rn WHEN 19 THEN CC_RES ELSE NULL END) CC_RESULT19,
	 23     MAX(CASE rn WHEN 20 THEN CC_RES ELSE NULL END) CC_RESULT20
	 24  FROM (
	 25        SELECT sample_id, sampling_id, document_id, column_value AS CC_RES, row_number() over
	 26              (partition by sample_id order by sample_id) rn
	 27        FROM ORIG_JCSD_gh, table(CC_RESULT)
	 28       )
	 29  GROUP BY sample_id, sampling_id, document_id;
	
         SAMPLE_ID     SAMPLING_ID      DOCUMENT_ID C C C C C C C C C C C C C C C C C C C C
	----------     -----------      ----------- - - - - - - - - - - - - - - - - - - - -
	         1           1                2     1 0 1 1 1 0 1 1 1 1 1 0 1 1 0 1 0 0 0 1
	         2           1                3     1 1 1 1 1 0 1 1 1 1 0 0 0 0 1 0 1 1 0 1
	         3           1                4     1 0 1 0 1 1 1 1 1 0 0 0 1 0 1 0 1 1 1 1
 
Here's what the values should be for each sample_id. As you can see the VARRAY values are the same for sample_id 1 and 2. The only values which are different in the VARRAY for SAMPLE_ID 3 are the 12th and 13th values. But, as you can see from the results from the query above, the query I want is returning very different results.
 
SAMPLE_ID	CC_RESULT
------------    -------------
    1		CHRARAY20('1', '1', '1', '0', '1', '1', '1', '1', '1', '1', '1', '0', '1', '1', '0', '1', '0', '0', '0', '0')
    2		CHRARAY20('1', '1', '1', '0', '1', '1', '1', '1', '1', '1', '1', '0', '1', '1', '0', '1', '0', '0', '0', '0')
    3		CHRARAY20('1', '1', '1', '0', '1', '1', '1', '1', '1', '1', '1', '1', '0', '1', '0', '1', '0', '0', '0', '0')
 
I so don't get it. HELP!
 
Thank you kindly,
Julie

Open in new window

0
Comment
Question by:jtittler
  • 3
4 Comments
 
LVL 27

Accepted Solution

by:
sujith80 earned 500 total points
ID: 22696494
The usage of table(CC_RESULT) doesnt ensure the order in which the numbers are stored in the VARRAY, that is the reason for the descrepancy.

You may use a simple function to return the individual elements of the varray.
See the following example.

create or replace function test_func(p_arg varray_typ, p_position number)
return number
as
begin
 return (p_arg(p_position));
exception
 when others then
  return null;
end;
/

SQL> select * from tbl1;
 
        ID VAL
---------- ----------------------------------
        10 VARRAY_TYP(11, 22, 33, 44)
        20 VARRAY_TYP(111, 222, 333, 444)
        
SQL> select id, test_func(val, 1) result1, test_func(val, 2) result2, test_func(val, 3) result3, test_func(val,4) result4
  2  from tbl1;
 
        ID    RESULT1    RESULT2    RESULT3    RESULT4
---------- ---------- ---------- ---------- ----------
        10         11         22         33         44
        20        111        222        333        444
 
SQL>        

Open in new window

0
 
LVL 27

Expert Comment

by:sujith80
ID: 22696510
Well, I see that your varray type is of character type.
Make the necessary changes to the sample function above as per your requirements.
Also, if you use a function; your can avoid the self-join and the pivot logic.
0
 

Author Closing Comment

by:jtittler
ID: 31505312
Simple, easy, and to the point. Sometimes you just need to think outside the box you locked yourself into. Thank you very much.

Best Regards,
Julie
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22701925
Glad to help.
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

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

822 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