Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros
Download
1 / 50

greg pike piocon technologies - PowerPoint PPT Presentation


  • 326 Views
  • Updated On :

Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros. Greg Pike Piocon Technologies. Greg Pike Managing Principal Piocon Technologies. History: 15+ year Oracle solution provider Home: ChicagoLand

Related searches for greg pike piocon technologies

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'greg pike piocon technologies ' - Gabriel


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Slide1 l.jpg

Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros

Greg PikePiocon Technologies


Greg pike managing principal piocon technologies l.jpg
Greg Pike OTN Forum ProsManaging PrincipalPiocon Technologies

  • History: 15+ year Oracle solution provider

  • Home: ChicagoLand

  • Focus: Business Intelligence, Data Warehousing, Portal, web applications, devious SQL

  • Blog: www.SingleQuery.com

  • Client list includes


The oracle discussion forums l.jpg
The Oracle Discussion Forums OTN Forum Pros

An interactive community for sharing information, questions, and comments about Oracle products and related technologies

Most forums are moderated by product managers, and all of them are frequently visited by knowledgeable users from the community

Anyone can read messages but must be a registered member to post a question or response

Posts to the SQL and PL/SQL Forum are often answered in minutes

Source: http://www.oracle.com/technology/forums/faq.html


Who can learn from the otn sql and pl sql forum l.jpg
Who Can Learn from the OTN SQL and PL/SQL Forum? OTN Forum Pros

Beginners: Got a question…its probably already been answered 100 times. Please search for a solution first, but don’t be shy to ask

IntermediateLearners: The SQL and PL/SQL Forum has a wealth of knowledge on every imaginable topic

Experts: You may not feel like an expert after a visit to this Forum

UniqueProblem: This is your real-time resource for solutions

Contributor: Get involved and share your expertise


Who contributes introducing the experts and recent forum post counts l.jpg
Who Contributes? Introducing the Experts and Recent Forum Post Counts:

  • Justin Cave – 18,700+

  • Billy Verreynne – 5,500+

  • APC – 9,500+

  • BluShadow – 6,200+

  • William Robertson – 4,500+

  • Volder – 950+

  • Yingkuan – 7000+

  • Kamal Kishore – 7,300+

…and 100’s of people worldwide with a passion for problem solving

Warren Tolentino – 4,400+

Devmiral – 2,200+

Nicolas Gasparotto – 14,900+

Marias – 1,300+

The Flying Spontinalli – 700+

Rob van Wijk – 3,900+

Michaels – 3,400+

John Spencer – 3,700+


Q if the advice is free how good can it be a the program enough said l.jpg
Q:”If the advice is Post Counts:free, how good can it be?”A: “The Program. Enough said.”

  • The Oracle ACE program formally recognizes advocates of Oracle technology with strong credentials as evangelists and educators

  • Oracle ACE recipients are chosen based on their significant contributions and activity in the Oracle technical community with candidates nominated by anyone in the Oracle Technology and Applications communities. “

Source: http://www.oracle.com/technology/community/oracle_ace/index.html


The oracle aces l.jpg
The Oracle Aces Post Counts:

“Oracle ACEs are known for their strong credentials as Oracle community enthusiasts and advocates with candidates nominated by anyone in the Oracle Technology and Applications communities.”

  • Technical proficiency

  • Oracle-related blog

  • Oracle discussion forum activity

  • Published white paper(s) and/or article(s)

  • Presentation experience

  • Beta program participant

  • Oracle user group member

  • Oracle certification


Just a few of the 188 oracle aces l.jpg
Just a Few of the 188 Oracle Aces Post Counts:

Cary Milsap (speaking today)

Peter Koletzke (speaking today)

Dan Norris (Piocon)

Paul Dorsey

Steven Feuerstein

Ken Jacobs

Tom Kyte

Mark Rittman

Laurent Schneider


Case study examining a recent post l.jpg
Case Study: Examining a Recent Post Post Counts:

  • Warren Tolentino

  • Devang Bhatt (devmiral)

  • Nicolas Gasparotto

  • marias

  • The Flying Spontinalli

  • Rob van Wijk

  • michaels

The following post is from May 28, 2007 and elicited 33 replies from long-time Forum contributors including Oracle Aces

http://forums.oracle.com/forums/thread.jspa?messageID=1864354

Only a portion of the solutions are presented here and the supporting commentary is omitted. Some queries have been altered to fit on the page but retain their basic logic

Thanks to the following OTN experts who contributed to this thread (OTN handle shown):


The original question l.jpg
The Original Question: Post Counts:

”I need to get the count on how many customers answered for each set of questions. For the above data, this should be the output:”

Questions IDsCustomer Count 1 1 1,2,4 2 3,4 2 2,3 1 2 1

“The Question IDs can have as many as 10 questions.” “Thanks in advance.”

“I need your help to generate a report. I'm using Oracle 9i database. This is our data:”

Customer_IDQuestion_ID 10001 1 10002 1 10002 2 10002 4 10003 3 10003 4 10004 2 10004 3 10005 1 10005 2 10005 4 10006 3 10006 4 10007 2


Examining a few of the posted solutions l.jpg
Examining a Few of the Posted Solutions Post Counts:

  • Method 1: Hierarchical Query

  • Method 2: COLLECT

  • Method 3: XML

  • Method 4: MODEL

  • Method 5: Pipelined Funtions


Method 1 hierarchical query warren l.jpg
Method 1: Hierarchical Query (Warren) Post Counts:

For querying datasets that contain a parent-child relationship between rows

Recursively walks through the tree of relationships from the top-down or bottom-up

Includes tools for determining location in the hierarchy and the nature of individual nodes


Method 1 hierarchical query l.jpg
Method 1: Hierarchical Query Post Counts:

SELECT customer_id,

question_id,

ROW_NUMBER() OVER (PARTITION BY

customer_id ORDER BY

customer_id, question_id) rn,

COUNT(*) OVER (PARTITION BY

customer_id,question_id) cnt

FROM customer_inquiry

) ci1

DTSRT WITH ci1.rn = 1

CONNECT BY ci1.rn = PRIOR ci1.rn + 1

AND PRIOR ci1.customer_id = ci1.customer_id

GROUP BY ci1.customer_id, ci1.cnt

) ci2

GROUP BY questions;

SELECT ci2.questions,

COUNT(ci2.cnt) “Customer Count"

FROM (SELECT ci1.customer_id,

SUBSTR(MAX(SUBSTR(SYS_CONNECT_BY_PATH

(ci1.question_id,','),2)),1,40) questions,

ci1.cnt

FROM (


Step 1 the pseudo parent child relationship l.jpg
Step 1: The pseudo-parent-child relationship Post Counts:

Customer_IDQuestion_ID 10001 1 10002 1 10002 2 10002 4 10003 3 10003 4 10004 2 10004 3 10005 1 10005 2 10005 4 10006 3 10006 4 10007 2

Customer_IDQuestion_IDRNCNT

10001 1 1 1

10002 1 1 1

10002 2 2 1

10002 4 3 1

10003 3 1 1

10003 4 2 1

10004 2 1 1

10004 3 2 1

10005 1 1 1

10005 2 2 1

10005 4 3 1

10006 3 1 1

10006 4 2 1

10007 2 1 1

SELECT customer_id,

question_id,

ROW_NUMBER() OVER (PARTITION BY

customer_id ORDER BY

customer_id, question_id) rn,

COUNT(*) OVER (PARTITION BY

customer_id,question_id) cnt

FROM customer_inquiry


Step 2 employ sys connect by path l.jpg
Step 2: Employ SYS_CONNECT_BY_PATH Post Counts:

SELECT customer_id,

question_id,

ROW_NUMBER() OVER (PARTITION BY

customer_id ORDER BY

customer_id, question_id) rn,

COUNT(*) OVER (PARTITION BY

customer_id,question_id) cnt

FROM customer_inquiry

) ci1

START WITH ci1.rn = 1

CONNECT BY ci1.rn = PRIOR ci1.rn + 1

AND PRIOR ci1.customer_id = ci1.customer_id

GROUP BY ci1.customer_id, ci1.cnt

) ci2

GROUP BY questions;

SYS_CONNECT_BY_PATH returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.

SELECT ci2.questions,

COUNT(ci2.cnt) “Customer Count"

FROM (SELECT ci1.customer_id,

SUBSTR(MAX(SUBSTR(SYS_CONNECT_BY_PATH

(ci1.question_id,','),2)),1,40) questions,

ci1.cnt

FROM (


Step 2 employ sys connect by path16 l.jpg
Step 2: Employ SYS_CONNECT_BY_PATH Post Counts:

Questions IDsCustomer Count 1 1 1,2,4 2 3,4 2 2,3 1 2 1

SYS_CONNECT_BY_PATH returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.

SELECT ci2.questions,

COUNT(ci2.cnt) “Customer Count"

FROM (SELECT ci1.customer_id,

SUBSTR(MAX(SUBSTR(SYS_CONNECT_BY_PATH

(ci1.question_id,','),2)),1,40) questions,

ci1.cnt

FROM (


Method 2 collect greg pike l.jpg
Method 2: COLLECT (Greg Pike) Post Counts:

  • COLLECT takes as its argument a column of any type and creates a nested table of the input type out of the rows selected

  • GROUP BY can determine how the rows are COLLECTED

  • The database creates its own collection object to hold the data

  • For the required output, CAST the results into a more usable form

  • Get the data out of the collection with a function


Method 2 the query l.jpg
Method 2: The Query Post Counts:

COLLECT(question_id)

SELECT SUBSTR(col,1,10) questions,

COUNT(*)

FROM (

SELECT customer_id,

tab_to_string(

CAST(

AS t_number_tab

)

) col

FROM answers

GROUP BY customer_id

)

GROUP BY col;


Step 1 collect l.jpg
Step 1: COLLECT Post Counts:

Customer_IDQuestion_ID 10001 1 10002 1 10002 2 10002 4 10003 3 10003 4 10004 2 10004 3 10005 1 10005 2 10005 4 10006 3 10006 4 10007 2

CUST_ID COLLECT(QUESTION_ID)

------- ---------------------------------

10001 SYSTP3f5GcsxkSvyjQSG5pDbjhA==(1)

10002 SYSTP3f5GcsxkSvyjQSG5pDbjhA==(1, 2, 4)

10003 SYSTP3f5GcsxkSvyjQSG5pDbjhA==(3, 4)

10004 SYSTP3f5GcsxkSvyjQSG5pDbjhA==(2, 3)

10005 SYSTP3f5GcsxkSvyjQSG5pDbjhA==(1, 2, 4)

10006 SYSTP3f5GcsxkSvyjQSG5pDbjhA==(3, 4)

10007 SYSTP3f5GcsxkSvyjQSG5pDbjhA==(2)

COLLECT takes as its argument a column of any type and creates a nested table of the input type out of the rows selected.

SELECT customer_id cust_id,

COLLECT(question_id)

FROM customer_inquiry

GROUP BY customer_id;


Step 2 cast collect l.jpg
Step 2: CAST(COLLECT) Post Counts:

Customer_IDQuestion_ID 10001 1 10002 1 10002 2 10002 4 10003 3 10003 4 10004 2 10004 3 10005 1 10005 2 10005 4 10006 3 10006 4 10007 2

CUST_ID COLLECT(QUESTION_ID)

------- ---------------------

10001 T_NUMBER_TAB(1)

10002 T_NUMBER_TAB(1, 2, 4)

10003 T_NUMBER_TAB(3, 4)

10004 T_NUMBER_TAB(2, 3)

10005 T_NUMBER_TAB(1, 2, 4)

10006 T_NUMBER_TAB(3, 4)

10007 T_NUMBER_TAB(2)

CAST converts one built-in data type or collection-typed value into another built-in data type or collection-typed value.

CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER;

SELECT customer_id,

CAST(COLLECT(question_id) AS t_number_tab) col

FROM customer_inquiry

GROUP BY customer_id;


Step 3 simple tab to string function l.jpg
Step 3: Simple tab_to_string Function Post Counts:

CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER;

CREATE OR REPLACE FUNCTION tab_to_string

(

p_number_tab IN t_number_tab,

p_delimiter IN VARCHAR2 DEFAULT ',‘

) RETURN VARCHAR2 IS

l_string VARCHAR2(32767);

BEGIN

FOR i IN p_number_tab.FIRST .. p_number_tab.LAST LOOP

IF i != p_number_tab.FIRST THEN

l_string := l_string || p_delimiter;

END IF;

l_string := l_string || to_char(p_number_tab(i));

END LOOP;

RETURN l_string;

END tab_to_string;


Step 3 tab to string cast collect l.jpg
Step 3: tab_to_string(CAST(COLLECT)) Post Counts:

Customer_IDQuestion_ID 10001 1 10002 1 10002 2 10002 4 10003 3 10003 4 10004 2 10004 3 10005 1 10005 2 10005 4 10006 3 10006 4 10007 2

CUST_ID COLLECT(QUESTION_ID)

------- ---------------------

10001 1

10002 1,2,4

10003 3,4

10004 2,3

10005 1,2,4

10006 3,4

10007 2

The tab_to_string procedure converts a table of numbers to a comma-separated VARCHAR2.

SELECT customer_id,

tab_to_string(CAST(COLLECT(question_id) AS

t_number_tab)) col

FROM customer_inquiry

GROUP BY customer_id;


Method 2 putting it all together l.jpg
Method 2: Putting it all together Post Counts:

tab_to_string(

CAST(

COLLECT(question_id)

AS t_number_tab

)

) col

Questions IDsCustomer Count 1 1 1,2,4 2 3,4 2 2,3 1 2 1

SELECT SUBSTR(col,1,10) questions,

COUNT(*)

FROM (

SELECT customer_id,

FROM answers

GROUP BY customer_id

)

GROUP BY col;


Method 3 xml the flying spontinalli l.jpg
Method 3: XML (The Flying Spontinalli) Post Counts:

  • XML functions:

    • Convert traditional table data into XML

    • Inquire upon XML data and fragments

    • Operate on XML data and fragments

    • Convert XML data back to character data type

  • CURSOR function: Converts a sub-query into a REF CURSOR


Method 3 the query l.jpg
Method 3: The Query Post Counts:

EXTRACT (

SYS_XMLGEN(seq)

,'/SEQ/XMLTYPE/ROW/Q'

).getstringval()

questions

FROM (

SELECT customer_id,

XMLSEQUENCE

(

CURSOR

(

SELECT question_id Q

FROM TEST t2

WHERE customer_id = t.customer_id

)

) seq

FROM TEST t

)

)

GROUP BY questions;

SELECT REPLACE(REPLACE(REPLACE(

questions,'</Q>'||CHR(10)||'<Q>',','

),'<Q>',NULL

),'</Q>',NULL

) questions,

COUNT(*) the_count

FROM (

SELECT DISTINCT customer_id,


Step 1 cursor and xmlsequence l.jpg
Step 1: CURSOR and XMLSEQUENCE Post Counts:

CUST SEQ

----- ------------------------------

10001 XMLSEQUENCETYPE(XMLTYPE( <ROW>

<Q>1</Q>

</ROW>

))

10002 XMLSEQUENCETYPE(XMLTYPE( <ROW>

<Q>1</Q>

</ROW>

), XMLTYPE( <ROW>

<Q>2</Q>

</ROW>

), XMLTYPE( <ROW>

<Q>4</Q>

</ROW>

The CURSOR function converts a sub-query into a REF CURSOR. In this case, XMLSEQUENCE requires a REF CURSOR.

The XMLSEQUENCE operator is used to split multi-value results from XMLTYPE queries into multiple rows.

SELECT customer_id,

XMLSEQUENCE(

CURSOR(

SELECT question_id Q

FROM TEST t2

WHERE customer_id = t.customer_id

)

) seq

FROM TEST t;


Step 2 sys xmlgen and extract l.jpg
Step 2: SYS_XMLGen and EXTRACT Post Counts:

The SYS_XMLGen function takes an expression that evaluates to a particular row and column of the database, and returns an instance of type XMLType containing an XML document

Applying EXTRACT to an XMLType value extracts the node or a set of nodes from the document identified by the XPath expression. The method getStringVal() retrieves the text from the XMLType instance

SELECT DISTINCT customer_id,

EXTRACT(

SYS_XMLGEN(seq),'/SEQ/XMLTYPE/ROW/Q'

).getstringval () questions

FROM (

SELECT customer_id,

XMLSEQUENCE(

CURSOR(

SELECT question_id Q

FROM TEST t2

WHERE customer_id = t.customer_id

)

) seq

FROM TEST t

)


Step 2 sys xmlgen and extract28 l.jpg
Step 2: SYS_XMLGen and EXTRACT Post Counts:

CUST SEQ

----- ------------------------------

10002 XMLSEQUENCETYPE(XMLTYPE( <ROW>

<Q>1</Q>

</ROW>

), XMLTYPE( <ROW>

<Q>2</Q>

</ROW>

), XMLTYPE( <ROW>

<Q>4</Q>

</ROW>

CUST QUESTIONS

----- -------------------------

10001 <Q>1</Q>

10002 <Q>1</Q><Q>2</Q><Q>4</Q>

10003 <Q>3</Q><Q>4</Q>

10004 <Q>2</Q><Q>3</Q>

10005 <Q>1</Q><Q>2</Q><Q>4</Q>

10006 <Q>3</Q><Q>4</Q>

10007 <Q>2</Q>

The SYS_XMLGen function takes an expression that evaluates to a particular row and column of the database, and returns an instance of type XMLType containing an XML document

Applying EXTRACT to an XMLType value extracts the node or a set of nodes from the document identified by the XPath expression. The method getStringVal() retrieves the text from the XMLType instance

SELECT DISTINCT customer_id,

EXTRACT(

SYS_XMLGEN(seq),'/SEQ/XMLTYPE/ROW/Q'

).getstringval () questions


Method 3 back to the query l.jpg
Method 3: Back to the Query Post Counts:

Questions IDsCustCnt 1 1 1,2,4 2 3,4 2 2,3 1 2 1

EXTRACT (

SYS_XMLGEN(seq)

,'/SEQ/XMLTYPE/ROW/Q'

).getstringval()

questions

FROM (

SELECT customer_id,

XMLSEQUENCE

(

CURSOR

(

SELECT question_id Q

FROM TEST t2

WHERE customer_id = t.customer_id

)

) seq

FROM TEST t

)

)

GROUP BY questions;

SELECT REPLACE(REPLACE(REPLACE(

questions,'</Q>'||CHR(10)||'<Q>',','

),'<Q>',NULL

),'</Q>',NULL

) questions,

COUNT(*) the_count

FROM (

SELECT DISTINCT customer_id,


Method 4 model rob van wijk l.jpg
Method 4: MODEL (Rob van Wijk) Post Counts:

MODEL PARTITION BY (cust_id)

DIMENSION BY (ROW_NUMBER() OVER

(PARTITION BY cust_id ORDER BY quest_id DESC) rn)

MEASURES (CAST(quest_id AS varchar2(20)) q)

RULES (

q[any] ORDER BY rn DESC = q[cv()+1] || ',' || q[cv()]

)

SELECT q "Questions",

COUNT(*) "Customer Count"

FROM (

SELECT SUBSTR(q,2) q,

rn

FROM a

)

WHERE rn = 1

GROUP BY q;


Method 4 the model portion of the query l.jpg
Method 4: The MODEL Portion of the Query Post Counts:

The MODEL clause enables you to create a multidimensional array by mapping the columns of a query into three groups: partitioning, dimension, and measure columns

MODEL PARTITION BY (cust_id)

DIMENSION BY (ROW_NUMBER() OVER

(PARTITION BY cust_id ORDER BY quest_id DESC) rn)

MEASURES (CAST(quest_id AS varchar2(20)) q)

RULES (

q[any] ORDER BY rn DESC = q[cv()+1] || ',' || q[cv()]

)

* All text on this page from Oracle® Database Data Warehousing Guide


Method 4 dissecting the model clause l.jpg
Method 4: Dissecting the MODEL Clause Post Counts:

The MODEL clause enables you to create a multidimensional array by mapping the columns of a query into three groups: partitioning, dimension, and measure columns

PARTITION columns define the logical blocks of the result set in a way similar to the partitions of the analytical functions. Rules in the MODEL clause are applied to each partition independent of other partitions

MODEL PARTITION BY (cust_id)

DIMENSION BY (ROW_NUMBER() OVER

(PARTITION BY cust_id ORDER BY quest_id DESC) rn)

MEASURES (CAST(quest_id AS varchar2(20)) q)

RULES (

q[any] ORDER BY rn DESC = q[cv()+1] || ',' || q[cv()]

)

* All text on this page from Oracle® Database Data Warehousing Guide


Method 4 dissecting the model clause33 l.jpg
Method 4: Dissecting the MODEL Clause Post Counts:

The MODEL clause enables you to create a multidimensional array by mapping the columns of a query into three groups: partitioning, dimension, and measure columns

PARTITION columns define the logical blocks of the result set in a way similar to the partitions of the analytical functions. Rules in the MODEL clause are applied to each partition independent of other partitions

DIMENSION columns define the multi-dimensional array and are used to identify cells within a partition. By default, a full combination of dimensions should identify just one cell in a partition

MODEL PARTITION BY (cust_id)

DIMENSION BY (ROW_NUMBER() OVER

(PARTITION BY cust_id ORDER BY quest_id DESC) rn)

MEASURES (CAST(quest_id AS varchar2(20)) q)

RULES (

q[any] ORDER BY rn DESC = q[cv()+1] || ',' || q[cv()]

)

* All text on this page from Oracle® Database Data Warehousing Guide


Method 4 dissecting the model clause34 l.jpg
Method 4: Dissecting the MODEL Clause Post Counts:

The MODEL clause enables you to create a multidimensional array by mapping the columns of a query into three groups: partitioning, dimension, and measure columns

PARTITION columns define the logical blocks of the result set in a way similar to the partitions of the analytical functions. Rules in the MODEL clause are applied to each partition independent of other partitions

DIMENSION columns define the multi-dimensional array and are used to identify cells within a partition. By default, a full combination of dimensions should identify just one cell in a partition

MEASURES are equivalent to the measures of a fact table in a star schema.

MODEL PARTITION BY (cust_id)

DIMENSION BY (ROW_NUMBER() OVER

(PARTITION BY cust_id ORDER BY quest_id DESC) rn)

MEASURES (CAST(quest_id AS varchar2(20)) q)

RULES (

q[any] ORDER BY rn DESC = q[cv()+1] || ',' || q[cv()]

)

* All text on this page from Oracle® Database Data Warehousing Guide


Method 4 dissecting the model clause35 l.jpg
Method 4: Dissecting the MODEL Clause Post Counts:

The MODEL clause enables you to create a multidimensional array by mapping the columns of a query into three groups: partitioning, dimension, and measure columns

PARTITION columns define the logical blocks of the result set in a way similar to the partitions of the analytical functions. Rules in the MODEL clause are applied to each partition independent of other partitions

DIMENSION columns define the multi-dimensional array and are used to identify cells within a partition. By default, a full combination of dimensions should identify just one cell in a partition

MEASURES are equivalent to the measures of a fact table in a star schema

RULES are used to manipulate the measure values of the cells in the multi-dimensional array defined by partition and dimension columns

MODEL PARTITION BY (cust_id)

DIMENSION BY (ROW_NUMBER() OVER

(PARTITION BY cust_id ORDER BY quest_id DESC) rn)

MEASURES (CAST(quest_id AS varchar2(20)) q)

RULES (

q[any] ORDER BY rn DESC = q[cv()+1] || ',' || q[cv()]

)

* All text on this page from Oracle® Database Data Warehousing Guide


Method 4 dissecting the model clause36 l.jpg
Method 4: Dissecting the MODEL Clause Post Counts:

PARTITION BY (cust_id)

DIMENSION BY (ROW_NUMBER() OVER

(PARTITION BY cust_id

ORDER BY quest_id DESC) rn)

MEASURES (CAST(quest_id

AS varchar2(20)) q)

RULES (

q[any] ORDER BY rn DESC =

q[cv(rn)+1] || ',' ||q[cv(rn)]

)

Example: q[2] = q[3]||’,’||q[2] = NULL,3

q[1] = q[2]||’,’||q[1] = ,3,4

quest_id ROW_NUMBER

Cust ID CV() (rn) Q

------- ---- ------- ------

10001 1 1 ,1

10002 1 3 ,1

10002 2 2 ,1,2

10002 4 1 ,1,2,4

10003 3 2 ,3

10003 4 1 ,3,4

10004 2 2 ,2

10004 3 1 ,2,3

10005 1 3 1

10005 2 2 ,1,2

10005 4 1 ,1,2,4

10006 3 2 ,3

10006 4 1 ,3,4

10007 2 1 ,2


Method 4 back to the query l.jpg
Method 4: Back to the Query Post Counts:

Questions IDsCustomer Count 1 1 1,2,4 2 3,4 2 2,3 1 2 1

MODEL PARTITION BY (cust_id)

DIMENSION BY (ROW_NUMBER() OVER

(PARTITION BY cust_id ORDER BY quest_id DESC) rn)

MEASURES (CAST(quest_id AS varchar2(20)) q)

RULES (

q[any] ORDER BY rn DESC = q[cv()+1] || ',' || q[cv()]

)

SELECT q "Questions",

COUNT(*) "Customer Count"

FROM (

SELECT SUBSTR(q,2) q,

rn

FROM a

)

WHERE rn = 1

GROUP BY q;


Method 5 the pipelined function michaels l.jpg
Method 5: The Pipelined Function (michaels) Post Counts:

  • Oracle Table Functions produce a collection of rows that can be consumed by a query much like a table.

  • Rows from a collection returned by a table function can also be PIPELINED or returned as they are produced instead of in a complete set upon function completion.

  • Two supported approached for Pipelining:

    • Interface method

    • PL/SQL method


Method 5 the query l.jpg
Method 5: The Query Post Counts:

Oracle Table Functions produce a collection of rows that can be consumed by a query much like a table.

SELECT COLUMN_VALUE "Questions",

COUNT (*) "Customer count"

FROM TABLE (f ())

GROUP BY COLUMN_VALUE;


Method 5 the pipelined function l.jpg
Method 5: The Pipelined Function Post Counts:

CREATE OR REPLACE FUNCTION f

RETURN SYS.dbms_debug_vc2coll PIPELINED

AS

l_c1 VARCHAR2 (20);

l_c2 PLS_INTEGER;

BEGIN

FOR c IN

(SELECT a.*,

COUNT (*) OVER (PARTITION BY customer_id) cnt,

ROW_NUMBER () OVER (PARTITION BY customer_id

ORDER BY question_id) rn

FROM a

ORDER BY customer_id, question_id)

LOOP…


Method 5 the pipelined function continued l.jpg
Method 5: The Pipelined Function continued Post Counts:

LOOP

IF l_c2 = c.customer_id OR l_c2 IS NULL THEN

l_c1 := l_c1 || c.question_id || ',';

l_c2 := c.customer_id;

IF c.cnt = c.rn THEN

PIPE ROW (RTRIM (l_c1, ','));

l_c1 := NULL;

l_c2 := NULL;

END IF;

END IF;

END LOOP;

RETURN;

END f;


Method 5 the pipelined function42 l.jpg
Method 5: The Pipelined Function Post Counts:

Questions IDsCustomer Count 1 1 1,2,4 2 3,4 2 2,3 1 2 1

SELECT COLUMN_VALUE "Questions",

COUNT (*) "Customer count"

FROM TABLE (f ())

GROUP BY COLUMN_VALUE;


So many choices so little time l.jpg
So many choices, so little time… Post Counts:

  • If all these queries provide the same results, which one should be chosen?

  • Query cost information was added to this post - yet another topic!

  • The results:

    • Michaels PIPELINED Function

    • Rob's MODEL

    • Nicolas' HIERARCHY 1

    • Nicolas' HIERARCHY 2

    • Greg's COLLECT

    • Warren/devmiral's HIERARCHY

    • Michaels'/Greg‘s XML


Not done yet even more solutions l.jpg
Not done yet! Even More Solutions… Post Counts:

XML combined with COLLECT

Bit Pattern

User-defined Aggregate Functions (ODCIAggregate Interface)

Old-school Oracle 7 solution


Wow all of these topics in a single thread l.jpg
Wow, All of These Topics in a Single Thread! Post Counts:

  • Hierarchical Queries

  • SYS_CONNECT_BY_ROOT

  • Analytic Functions

  • COLLECT

  • CAST

  • CURSOR

  • XML Functions

  • MODEL Clause

  • Pipelined Functions

  • Bit Pattern Techniques

  • User-defined Aggregates

  • Explain Plans/Query Costs

BUT…Never underestimate the value of using widely-understood and accepted Oracle database concepts

The experts here used this thread to demonstrate alternate technologies only and never advocated these rather esoteric solutions


Jumping in the pool forum decorum l.jpg
Jumping in the Pool: “Forum-Decorum” Post Counts:

  • Don’t demand urgent help. In fact, don’t demand anything from this all-volunteer community

  • Search for an answer first

  • Use the proper code notation tags – reformatting queries is a pain

    [pre]This is code text[/pre]

  • Don’t flame the Gurus…you will likely get scorched!

  • If you use information from the Forums on your Blog or anywhere else, please reference your source

  • Don’t be intimidated. If you don’t understand an answer, request clarification

  • Thank your responders


Wrap up l.jpg
Wrap-Up Post Counts:

  • The Oracle SQL and PL/SQL Forum has 64K+ topics with almost 350k posts

  • Other active Forums include:

    • Database-General

    • App Server

    • J-Developer

    • Forms

    • OWB

    • More!

  • Take advantage of the experts including many Oracle Aces!

  • Learn something new

  • Become a regular contributor!


Acknowledgements l.jpg
Acknowledgements Post Counts:

  • Oracle® Database Data Warehousing Guide 10gR2

  • Oracle® Database SQL Reference 10gR2

  • Oracle® XML DB Developer's Guide 11gR1

  • OTN - Getting into SQL/XML - Tim Quinlan

  • http://www.oracle-base.com

  • The OTN SQL and PL/SQL Forum


Thank you for attending l.jpg
Thank You for attending! Post Counts:

Thank You for attending!

Greg Pike

[email protected]

Piocon Technologies

1420 Kensington Rd. Suite 106

Oak Brook, IL 60523

630-579-0800

Blog: www.singlequery.com

Thanks to numerous contributors:

  • The Oracle Forum experts


ad