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?
 Table
LVL 1
adamssapAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

knightEknightCommented:
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
adamssapAuthor Commented:
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.

SharathData EngineerCommented:
>> 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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

adamssapAuthor Commented:
Yes, that will be good.

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

check that.
Christoffer SwanströmPartnerCommented:
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

adamssapAuthor Commented:
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ömPartnerCommented:
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

adamssapAuthor Commented:
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. Data
It has html tags and other characters too.
Christoffer SwanströmPartnerCommented:
Can you post the actual data you are querying? The original screenshot you gave above does not look like the string you just posted.
adamssapAuthor Commented:
Data:

"<html><body>When a misspelling &quot;defect&quot; 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.
Christoffer SwanströmPartnerCommented:
How about this:
SET DEFINE OFF;

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

INSERT INTO swc_tst VALUES (209, 1, '<html><BODY>WHEN A misspelling &quot;defect&quot; 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>');
--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,'<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
    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

adamssapAuthor Commented:
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ömPartnerCommented:
If you run exactly the code I have you what do you get?
Christoffer SwanströmPartnerCommented:
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
adamssapAuthor Commented:
I got the return = '55', I don't know what that is.
Christoffer SwanströmPartnerCommented:
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

Christoffer SwanströmPartnerCommented:
Also, please try the following and let me know what you get:
SET DEFINE OFF;

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

INSERT INTO swc_tst VALUES (209, 1, '<html><BODY>WHEN A misspelling &quot;defect&quot; 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>');
--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,'<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
    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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adamssapAuthor Commented:
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ömPartnerCommented:
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.
adamssapAuthor Commented:
Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.