asked on

SQL help: Nested Select statement

Hi:

I need help with the SQL statements here:
I have a table as shown in the picture.
Step 1: I wanted all the records with ds_test_id 209. I did this
SELECT * FROM DESSTEPS where DS_TEST_ID = '209'

Step 2: Now, I want to store the values of DS_DESCRIPTION for DS_TEST_ID='209' in an array in a sequence based on DS_STEP_ORDER.

How can I accomplish the steps 1 and 2 in a single SQL statement?

knightEknight

Are you looking to sort the output of the query? use "order by":

SELECT * FROM DESSTEPS where DS_TEST_ID = '209' ORDER BY DS_STEP_ORDER

adamssap

ASKER

Thanks for your reply. But I am not looking to sort, I am looking to get the value for each row of DS_DESCRIPTION field in an array. And that needs to be in a single query if possible.

Sharath S

>> I am looking to get the value for each row of DS_DESCRIPTION field in an array

Do you want a comma separated list of DS_DESCRIPTION in a single column?

adamssap

ASKER

Yes, that will be good.

Thanks,.

Sharath S

There are some techniques in this link. http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

check that.

Christoffer Swanström

Here's an example how to do it:

DROP TABLE swc_tst;
CREATE TABLE swc_tst (
  ds_test_id NUMBER
  ,ds_step_order NUMBER
  ,ds_description VARCHAR2(64)
);

INSERT INTO swc_tst VALUES (209, 1, 'Step 1');
INSERT INTO swc_tst VALUES (209, 2, 'Step 2');
INSERT INTO swc_tst VALUES (209, 3, 'Step 3');

INSERT INTO swc_tst VALUES (210, 1, 'Step 1');
INSERT INTO swc_tst VALUES (210, 2, 'Step 2');
INSERT INTO swc_tst VALUES (210, 3, 'Step 3');

COMMIT;

SELECT
  ds_test_id
  ,ltrim(sys_connect_by_path(ds_description,','),',') AS description
FROM
  (SELECT
    ds_test_id
    ,ds_step_order
    ,ds_description
    ,RANK() OVER(PARTITION BY ds_test_id ORDER BY ds_step_order) AS rnk
    ,COUNT(1) OVER(PARTITION BY ds_test_id) AS cnt
  FROM
    swc_tst
  ) asd
WHERE
  level = cnt
START WITH
  rnk = 1
CONNECT BY
  PRIOR ds_test_id = ds_test_id
AND
  PRIOR rnk = rnk - 1

Open in new window

adamssap

ASKER

Thanks Tosse:

I am getting the following error:

Messages:
Failed to Run Query;
Failed to execute the query;
Failed SQL: /* ~~QC */ SELECT DS_TEST_ID ,ltrim(sys_connect_by_path(DS_DESCRIPTION,','),',') AS description FROM (SELECT DS_TEST_ID, DS_STEP_ORDER, DS_DESCRIPTION, RANK() OVER(PARTITION BY DS_TEST_ID ORDER BY DS_STEP_ORDER) AS rnk,COUNT(1) OVER(PARTITION BY DS_TEST_ID) AS cnt FROM DESSTEPS) asd WHERE level = cnt START WITH rnk = 1 CONNECT BY PRIOR DS_TEST_ID = '209' AND PRIOR rnk = rnk - 1
[Server][Oracle JDBC Driver][Oracle]ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value
;

Christoffer Swanström

You seem to have some values of ds_description that contain commas, which is also the separator in my example. Try to find another character (e.g. a pipe, |) that does not occur in the field. E.g.

...SYS_CONNET_BY_PATH(DS_DESCRIPTION, '|')...

Open in new window

Besides, you probably want to put test_id = 209 in the WHERE clause instead of the CONNET BY if you want to select just one test_id:

... 
WHERE 
  level = cnt 
AND 
  DS_TEST_ID = '209' 
START WITH 
  rnk = 1 
CONNECT BY PRIOR 
  rnk = rnk - 1

Open in new window

adamssap

ASKER

Thanks, this time there is no error but no data is retrieved.

Actual data from the column DS_DESCRIPTION looks as shown below in the image.

It has html tags and other characters too.

Christoffer Swanström

Can you post the actual data you are querying? The original screenshot you gave above does not look like the string you just posted.

adamssap

ASKER

Data:

"<html><body>When a misspelling "defect" is first identified, the notification is most often by an end-user, since the most serious misspelling defects are in user-visible objects, and in BW these objects are either in menus (report names) or in reports titles or column headings). The remainder of this test case will assume the misspelling is in a role menu for illustrative purposes.</body></html>"

I used the query "SELECT DS_DESCRIPTION FROM DESSTEPS where DS_TEST_ID='209' order by DS_STEP_ORDER"

But DS_TEST_ID 209 has 6 records, I want to get all the 6 record data from column DS_DESCRIPTION.

SOLUTION

Christoffer Swanström

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adamssap

ASKER

I used this query:
SELECT DS_TEST_ID,ltrim(sys_connect_by_path(DS_DESCRIPTION,'<my_separator>'),',') AS description FROM (SELECT DS_TEST_ID,DS_STEP_ORDER,DS_DESCRIPTION,RANK() OVER(PARTITION BY DS_TEST_ID ORDER BY DS_STEP_ORDER) AS rnk,COUNT(1) OVER(PARTITION BY DS_TEST_ID) AS cnt FROM DESSTEPS) asd WHERE level = cnt START WITH rnk = 1 CONNECT BY PRIOR DS_TEST_ID = '209' AND PRIOR rnk = rnk - 1

and got the return = '55', I don't know what that is.

Christoffer Swanström

If you run exactly the code I have you what do you get?

Christoffer Swanström

Plus, as I mentioned above, you should do
... asd WHERE level = cnt AND DS_TEST_ID = 209 START WITH rnk = 1 CONNECT BY PRIOR DS_TEST_ID = PRIOR DS_TEST_ID AND PRIOR rnk = rnk - 1

instead of
... asd WHERE level = cnt START WITH rnk = 1 CONNECT BY PRIOR DS_TEST_ID = '209' AND PRIOR rnk = rnk - 1

adamssap

ASKER

I got the return = '55', I don't know what that is.

Christoffer Swanström

Try this and let me know what you get:

SELECT DS_TEST_ID,ltrim(sys_connect_by_path(DS_DESCRIPTION,'<my_separator>'),',') AS description FROM (SELECT DS_TEST_ID,DS_STEP_ORDER,DS_DESCRIPTION,RANK() OVER(PARTITION BY DS_TEST_ID ORDER BY DS_STEP_ORDER) AS rnk,COUNT(1) OVER(PARTITION BY DS_TEST_ID) AS cnt FROM DESSTEPS WHERE DS_TEST_ID = 209) asd WHERE level = cnt START WITH rnk = 1 CONNECT BY PRIOR DS_TEST_ID = DS_TEST_ID AND PRIOR rnk = rnk - 1

Open in new window

ASKER CERTIFIED SOLUTION

Christoffer Swanström

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adamssap

ASKER

By executing this:
"SELECT DS_TEST_ID,ltrim(sys_connect_by_path(DS_DESCRIPTION,'<my_separator>'),',') AS description FROM (SELECT DS_TEST_ID,DS_STEP_ORDER,DS_DESCRIPTION,RANK() OVER(PARTITION BY DS_TEST_ID ORDER BY DS_STEP_ORDER) AS rnk,COUNT(1) OVER(PARTITION BY DS_TEST_ID) AS cnt FROM DESSTEPS WHERE DS_TEST_ID = 209) asd WHERE level = cnt START WITH rnk = 1 CONNECT BY PRIOR DS_TEST_ID = DS_TEST_ID AND PRIOR rnk = rnk - 1"

Result is '209'

By executing this:
"SELECT DS_TEST_ID,ltrim(sys_connect_by_path(DS_DESCRIPTION,'<my_separator>'),',') AS description FROM (SELECT DS_TEST_ID,DS_STEP_ORDER,DS_DESCRIPTION,RANK() OVER(PARTITION BY DS_TEST_ID ORDER BY DS_STEP_ORDER) AS rnk,COUNT(1) OVER(PARTITION BY DS_TEST_ID) AS cnt FROM DESSTEPS) asd WHERE level = cnt START WITH rnk = 1 CONNECT BY PRIOR DS_TEST_ID = '209' AND PRIOR rnk = rnk - 1"

Result is '55'

Christoffer Swanström

Can you please also give the output you get with the test data I provided? Run the whole set of statements exactly like I provided them.

adamssap

ASKER

Thanks.