This forum is currently locked. You can't register or post questions at this time. (read more)

Answer for this COMPLEX Query

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

sreenupatt@gmail.com
Member
Posts: 2
Joined: Sun Jul 07, 2013 12:44 am

Answer for this COMPLEX Query

Postby sreenupatt@gmail.com » Sun Jul 07, 2013 12:46 am

ID, Name

101 Mr.
Null Neil
Null Adams
102 Mrs.
Null Julie
Null Roberts


Output should be:-

ID, Name

101 Mr. Neil Adams
102 Mrs. Julie Roberts

Please how can i achieve this , this is my last interview question, How can i achieve this

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Answer for this COMPLEX Query

Postby Tim... » Sun Jul 07, 2013 4:41 am

Hi.

If these are the only columns there is no way to guarantee the result you are being asked for. Oracle does not guarantee the order of a result set without an order by clause. The result you want is only possible if the data is always presented in the same order. Since we can't guarantee the order of the results, the output is not possible in a consistent manner.

So either:

1) This is a trick question, designed to see if you can spot the flaw in the question.
2) The person asking the question has not realized the flaw in the question.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

sreenupatt@gmail.com
Member
Posts: 2
Joined: Sun Jul 07, 2013 12:44 am

Re: Answer for this COMPLEX Query

Postby sreenupatt@gmail.com » Sun Jul 07, 2013 5:39 am

Hi Tim,

Say I have the data as below

ID, Name

101 Mr.
101 Neil
101 Adams
102 Mrs.
102 Julie
102 Roberts

By Executing below SQL, I can get the Output
SELECT ID,
MAX(SYS_CONNECT_BY_PATH(NAME, ',')) FULL_NAME FROM
(
SELECT ID, NAME,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ROWNUM ) RN
FROM <table_name>
)
CONNECT BY PRIOR ID=ID AND PRIOR RN = RN-1
START WITH RN=1
GROUP BY ID
ORDER BY ID;


Output :-

ID, Name

101 Mr. Neil Adams
102 Mrs. Julie Roberts

You please try the above, if we can get this, then y not the NULL SQL

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Answer for this COMPLEX Query

Postby Tim... » Sun Jul 07, 2013 7:55 am

Hi.

Once again, you are ignoring the fact that Oracle does not guarantee order of a result set without an explicit ORDER BY. In your example you are using an ORDER BY ROWNUM, but the ROWNUM pseudocolumn is applied after any rows are retrieved and ORDER BY ordering has taken place.

Your example is relying on the fact that Oracle will always retrieve the data in a specific order, so the ROWNUM will always be correct. That is simply not the case. In your simple example it appears to be, because all the data is stored in a single block, so Oracle *appears* to maintain the insert order. This is a fluke, not a rule.

Let me show you. I'll create a table with the same data you have, but I will insert the data in a different order.

Code: Select all

CREATE TABLE my_tab (
  id NUMBER,
  name VARCHAR2(50)
);

INSERT INTO my_tab VALUES (102, 'Roberts');
INSERT INTO my_tab VALUES (102, 'Julie');
INSERT INTO my_tab VALUES (102, 'Mrs.');
INSERT INTO my_tab VALUES (101, 'Adams');
INSERT INTO my_tab VALUES (101, 'Neil');
INSERT INTO my_tab VALUES (101, 'Mr.');


Now let's use your solution.

Code: Select all

COLUMN full_name FORMAT A40

SELECT ID,
       MAX(SYS_CONNECT_BY_PATH(NAME, ',')) FULL_NAME
FROM   (
         SELECT ID, NAME,
                ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ROWNUM ) RN
         FROM my_tab
       )
CONNECT BY PRIOR ID=ID AND PRIOR RN = RN-1
START WITH RN=1
GROUP BY ID
ORDER BY ID;

        ID FULL_NAME
---------- ----------------------------------------
       101 ,Adams,Neil,Mr.
       102 ,Roberts,Julie,Mrs.

2 rows selected.

SQL>


Oh dear!

Unless you have a specific way to order the data, which is *guaranteed* to always return the data in the same order, doing any processing that relies on the data being ordered is impossible.

Replying on insert order is flawed. It may appear to work for small tests, but Oracle can access blocks in any order it wants. It can also migrate rows to different blocks. This is why the Oracle manuals always state:

"Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order."

http://docs.oracle.com/cd/E16655_01/ser ... SQLRF20039

So we go back to your original question. Either it is a trick question, or the person asking the question doesn't understand the flaw in the question.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests