Solved

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

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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.
This video shows how to recover a database from a user managed backup

706 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

18 Experts available now in Live!

Get 1:1 Help Now