Home » SQL & PL/SQL » SQL & PL/SQL » Querying objects
Querying objects [message #398444] Thu, 16 April 2009 05:18 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi,
Below is the script -

CREATE  TABLE nav_test
(
ID        NUMBER,
status_1 VARCHAR(51),
status_2 VARCHAR(51)
);

INSERT INTO nav_test
     VALUES (1, 'PASSED', 'PASSED');
INSERT INTO nav_test
     VALUES (1, 'FAILED', 'PASSED');
INSERT INTO nav_test
     VALUES (1, 'FAILED', 'FAILED');
INSERT INTO nav_test
     VALUES (1, 'PASSED', 'FAILED');
INSERT INTO nav_test
     VALUES (2, 'FAILED', 'PASSED');

CREATE OR REPLACE TYPE status_ot AS OBJECT (
   status_1   VARCHAR2 (51),
   status_2   VARCHAR2 (51)
);

CREATE OR REPLACE TYPE status_nt AS TABLE OF status_ot;



When I query the sql below, i get "ORA-01427: single-row subquery returns more than one row"


SELECT b.ID, 
           (SELECT status_nt(status_ot (a.status_1, a.status_2))
              FROM nav_test a
             WHERE a.ID = b.ID) set_val
  FROM nav_test b;



I need the output in below format
id       set_val
1	(DATASET 1) 
2	(DATASET 2) 


DATASET 1 will have the value
	PASSED		PASSED
 	FAILED		PASSED
 	FAILED		FAILED
 	PASSED		FAILED
DATASET 2 will have the value
	FAILED		PASSED


Thanks
Navkrish

[Updated on: Thu, 16 April 2009 05:27] by Moderator

Report message to a moderator

Re: Querying objects [message #398451 is a reply to message #398444] Thu, 16 April 2009 05:33 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I do not have not access to Oracle right now so could not test the below code.



SELECT b.ID, 
           cast(multiset(SELECT status_nt(status_ot (a.status_1, a.status_2)
              FROM nav_test a
             WHERE a.ID = b.ID) as status_nt) set_val
  FROM nav_test b;


Re: Querying objects [message #398454 is a reply to message #398444] Thu, 16 April 2009 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know what you really want to do but here are 2 ways to achieve something I think close:
SQL> SELECT b.ID, 
  2         cursor(select status_ot (a.status_1, a.status_2) 
  3                FROM nav_test a
  4                WHERE a.ID = b.ID) set_val
  5  FROM nav_test b
  6  group by b.id
  7  /
        ID SET_VAL
---------- ----------------------------------------------------------------
         1 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2
STATUS_OT(A.STATUS_1,A.STATU(STATUS_1, STATUS_2)
---------------------------------------------------------------------------
STATUS_OT('PASSED', 'PASSED')
STATUS_OT('FAILED', 'PASSED')
STATUS_OT('FAILED', 'FAILED')
STATUS_OT('PASSED', 'FAILED')

         2 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2
STATUS_OT(A.STATUS_1,A.STATU(STATUS_1, STATUS_2)
---------------------------------------------------------------------------
STATUS_OT('FAILED', 'PASSED')

SQL> SELECT b.ID, 
  2         cursor(select a.status_1, a.status_2
  3                FROM nav_test a
  4                WHERE a.ID = b.ID) set_val
  5  FROM nav_test b
  6  group by b.id
  7  /
        ID SET_VAL
---------- ----------------------------------------------------------------
         1 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2
STATUS_1                                            STATUS_2
--------------------------------------------------- -----------------------
PASSED                                              PASSED
FAILED                                              PASSED
FAILED                                              FAILED
PASSED                                              FAILED
         2 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2
STATUS_1                                            STATUS_2
--------------------------------------------------- -----------------------
FAILED                                              PASSE

Regards
Michel

[Updated on: Thu, 16 April 2009 05:49]

Report message to a moderator

Re: Querying objects [message #398456 is a reply to message #398451] Thu, 16 April 2009 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you meant:
SQL> SELECT b.ID, 
  2             cast(multiset(SELECT status_ot (a.status_1, a.status_2)
  3                FROM nav_test a
  4               WHERE a.ID = b.ID) as status_nt) set_val
  5    FROM nav_test b
  6  group by b.ID
  7  /
        ID SET_VAL(STATUS_1, STATUS_2)
---------- ----------------------------------------------------------------------
         1 STATUS_NT(STATUS_OT('PASSED', 'PASSED'), STATUS_OT('FAILED', 'PASSED')
           , STATUS_OT('FAILED', 'FAILED'), STATUS_OT('PASSED', 'FAILED'))
         2 STATUS_NT(STATUS_OT('FAILED', 'PASSED'))

(fixing syntax and adding group by to remove duplicates.)

Regards
Michel
Re: Querying objects [message #398462 is a reply to message #398454] Thu, 16 April 2009 06:04 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Thanks for the prompt reply. With your help I modified the sql and got the proper result which I was looking for-

SELECT   b.ID,
         CAST (MULTISET (SELECT status_ot (a.status_1, a.status_2)
                           FROM nav_test a
                          WHERE a.ID = b.ID) AS status_nt
              ) set_val
    FROM nav_test b
GROUP BY b.ID



Navkrish
Re: Querying objects [message #398463 is a reply to message #398444] Thu, 16 April 2009 06:08 Go to previous message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Thanks Michel....
Previous Topic: Oracle10g
Next Topic: How to know the constraints hostory. [merged]
Goto Forum:
  


Current Time: Sat Dec 10 09:19:02 CST 2016

Total time taken to generate the page: 0.09330 seconds