Solved

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

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

724 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