Solved

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

Posted on 2008-10-11
4
390 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
[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
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

737 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