Home » SQL & PL/SQL » SQL & PL/SQL » Update Nested Table (Oracle 10g)
Update Nested Table [message #322120] Thu, 22 May 2008 09:50 Go to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member
Hi,

I am having a situation here in which I am having some firms(client) and firms(clients) are having their individual price list for some task.

Client can change the price form application.
So i created a nested table as below:

CREATE OR REPLACE TYPE PRICE_DETAIL_DEMO AS OBJECT
(TASK_TYPE VARCHAR2(20),
REPORT VARCHAR2(200),
TASK_CODE VARCHAR2(200),
PRICE NUMBER(5,2),
DATE_OF_CHANGE DATE)
/

CREATE OR REPLACE TYPE PRICE_DETAIL_DEMO_TYPE AS TABLE OF PRICE_DETAIL_DEMO;
/

/*This is the main table which is going to store the firms and their prices*/
CREATE TABLE PRICE_LIST_DEMO
(PRICE_ID NUMBER,
FIRM_ID VARCHAR2(40),
USER_ID VARCHAR2(40),
PRICE PRICE_DETAIL_DEMO_TYPE)
NESTED TABLE PRICE STORE AS price_store_tab;
/

/*Sequence to generate priceid*/
CREATE SEQUENCE price_id_seq
INCREMENT BY 1
START WITH 1
MINVALUE 1

/*First I am inserting the total firms for which I have to do the pricing*/

DECLARE
CURSOR C IS
select firm_id,user_id from users u
group by firm_id,user_id;
BEGIN
FOR R IN C
LOOP
INSERT INTO price_list_demo(price_id,firm_id,user_id)
VALUES (price_id_seq.nextval,R.firm_id,R.user_id);
END LOOP;
COMMIT;
END;

Now once i have inserted the firm details I have to update the price column in price list demo table on the basis of firm id and user id with price details by using below query.

SELECT DISTINCT c.NAME TASK_type,
pricingitemid.NAME report,
pricingitemid.code TASK_code,
plm.VALUE
FROM items pricingitemid,
contents c,
price_mapping plm,
lists pl,
users u
WHERE c.ID = content_id
AND pl.user_id = u.ID
AND plm.pricing_id = pricingitemid.ID
AND u.firm_id = v_firm_id
AND u.user_id = v_user_id;



I tried creating a procedure as below but it is not helping me out:

CURSOR D (v_firm_id VARCHAR2,v_user_id VARCHAR2) IS
SELECT DISTINCT c.NAME TASK_type,
pricingitemid.NAME report,
pricingitemid.code TASK_code,
plm.VALUE
FROM items pricingitemid,
contents c,
price_mapping plm,
lists pl,
users u
WHERE c.ID = content_id
AND pl.user_id = u.ID
AND plm.pricing_id = pricingitemid.ID
AND u.firm_id = v_firm_id
AND u.user_id = v_user_id;
v_firm_id VARCHAR2(40);
v_user_id VARCHAR2(40);
BEGIN
FOR R IN C
LOOP
v_firm_id := R.FIRM_ID;
v_user_id := R.USER_ID;
FOR S IN D(v_firm_id,v_user_id)
LOOP

UPDATE TABLE(SELECT h.price FROM PRICE_LIST_DEMO h) p
SET p.TASK_TYPE = S.TASK_TYPE,
p.REPORT=S.REPORT,
p.TASK_CODE=S.TASK_CODE,
p.PRICE=S.VALUE,
p.DATE_OF_CHANGE=TRUNC(SYSDATE);
--WHERE h.FIRM_ID = v_firm_id
--AND h.USER_ID = v_user_id;
-- WHERE FIRM_ID = v_firm_id
-- AND USER_ID = v_user_id;
END LOOP;
END LOOP;
END;

This procedure is giving me error as

ORA-01427: single-row subquery returns more than one row

Since the update loop is returning multiple records.

Can anyone please help me in this?

Thanks in advance.

Regards,
Amit Verma

Report message to a moderator

Re: Update Nested Table [message #322124 is a reply to message #322120] Thu, 22 May 2008 09:59 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
You have no where-clause on the price-query in the update, so it will return more than a single price

Report message to a moderator

Re: Update Nested Table [message #322138 is a reply to message #322120] Thu, 22 May 2008 10:26 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member
I changed the update statement with where clause

UPDATE TABLE(SELECT h.price FROM PRICE_LIST_DEMO h
WHERE h.FIRM_ID = v_firm_id
AND h.USER_ID = v_user_id) p
SET p.TASK_TYPE = S.TASK_TYPE,
p.REPORT=S.REPORT,
p.TASK_CODE=S.TASK_CODE,
p.PRICE=S.VALUE,
p.DATE_OF_CHANGE=TRUNC(SYSDATE);


But still getting error as

ORA-22908: reference to NULL table value.

Thanks and Regards,
Amit Verma

Report message to a moderator

Re: Update Nested Table [message #322140 is a reply to message #322120] Thu, 22 May 2008 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
22908, 00000, "reference to NULL table value"
//  *Cause:  The evaluation of the THE subquery or nested table column 
//           resulted in a NULL value implying a NULL table instance. 
//           The THE subquery or nested table column must identify a 
//           single non-NULL table instance.
//  *Action: Ensure that the evaluation of the THE subquery or nested table
//           column results in a single non-null table instance. If happening
//           in the context of an insert statement where the THE subquery is 
//           the target of an insert, then ensure that an empty nested table 
//           instance is created by updating the nested table column of the 
//           parent table's row specifying an empty nested table constructor.

Report message to a moderator

Re: Update Nested Table [message #322143 is a reply to message #322120] Thu, 22 May 2008 10:45 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member
I have already checked the error message can you please let me know what is the solution for this.

Thanks and Regards,
Amit

Report message to a moderator

Re: Update Nested Table [message #322144 is a reply to message #322120] Thu, 22 May 2008 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
In My Opinion, the UPDATE statement make NO sense.
Which table do you desire to UPDATE?

SQL> DESC <table_to_be_updated>
-- post results back here nicely formatted.

Report message to a moderator

Re: Update Nested Table [message #322148 is a reply to message #322120] Thu, 22 May 2008 11:00 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member
Below is the table description

CREATE OR REPLACE TYPE PRICE_DETAIL_DEMO AS OBJECT
(TASK_TYPE VARCHAR2(20),
REPORT VARCHAR2(200),
TASK_CODE VARCHAR2(200),
PRICE NUMBER(5,2),
DATE_OF_CHANGE DATE)
/

CREATE OR REPLACE TYPE PRICE_DETAIL_DEMO_TYPE AS TABLE OF PRICE_DETAIL_DEMO;
/

/*This is the main table which is going to store the firms and their prices*/
CREATE TABLE PRICE_LIST_DEMO
(PRICE_ID NUMBER,
FIRM_ID VARCHAR2(40),
USER_ID VARCHAR2(40),
PRICE PRICE_DETAIL_DEMO_TYPE)
NESTED TABLE PRICE STORE AS price_store_tab;
/


I want to update PRICE column in PRICE_LIST_DEMO on the basis of firmid and userid.

Regards,
Amit Verma

Report message to a moderator

Re: Update Nested Table [message #322214 is a reply to message #322148] Thu, 22 May 2008 15:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
The problem is that the nested table that constitutes your price column is empty, so when you try to "SELECT h.price FROM price_list_demo h", the resulting TABLE is empty, so there is nothing to update. If you first initialize the values, then you can update them, as demonstrated below. Your where clause was also misplaced. In the future, please post insert statements and desired results and format your post. Please read the forum guidelines for what we expect.

-- test environment:
SCOTT@orcl_11g> CREATE OR REPLACE TYPE PRICE_DETAIL_DEMO AS OBJECT
  2    (TASK_TYPE      VARCHAR2(20),
  3  	REPORT	       VARCHAR2(200),
  4  	TASK_CODE      VARCHAR2(200),
  5  	PRICE	       NUMBER(5,2),
  6  	DATE_OF_CHANGE DATE)
  7  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE PRICE_DETAIL_DEMO_TYPE AS TABLE OF PRICE_DETAIL_DEMO;
  2  /

Type created.

SCOTT@orcl_11g> CREATE TABLE PRICE_LIST_DEMO
  2    (PRICE_ID       NUMBER,
  3  	FIRM_ID        VARCHAR2(40),
  4  	USER_ID        VARCHAR2(40),
  5  	PRICE	       PRICE_DETAIL_DEMO_TYPE)
  6    NESTED TABLE PRICE STORE AS price_store_tab
  7  /

Table created.

SCOTT@orcl_11g> INSERT INTO price_list_demo (price_id, firm_id, user_id)
  2  VALUES (1, 'firm1', 'user1')
  3  /

1 row created.

SCOTT@orcl_11g> INSERT INTO price_list_demo (price_id, firm_id, user_id)
  2  VALUES (2, 'firm2', 'user2')
  3  /

1 row created.


-- you cannot update an empty table:
SCOTT@orcl_11g> UPDATE TABLE
  2  	      (SELECT h.price
  3  	       FROM   price_list_demo h
  4  	       WHERE  firm_id = 'firm1'
  5  	       AND    user_id = 'user1') p
  6  SET    p.task_type      = 'task_type1',
  7  	    p.report	     = 'report1',
  8  	    p.task_code      = 'task_code1',
  9  	    p.price	     = 10,
 10  	    p.date_of_change = TRUNC (SYSDATE)
 11  /
UPDATE TABLE
*
ERROR at line 1:
ORA-22908: reference to NULL table value


-- if you initialize your collection with null values,
-- then you can update them:
SCOTT@orcl_11g> UPDATE price_list_demo
  2  SET    price = price_detail_demo_type (price_detail_demo (NULL, NULL, NULL, NULL, NULL))
  3  /

2 rows updated.

SCOTT@orcl_11g> UPDATE TABLE
  2  	      (SELECT h.price
  3  	       FROM   price_list_demo h
  4  	       WHERE  firm_id = 'firm1'
  5  	       AND    user_id = 'user1') p
  6  SET    p.task_type      = 'task_type1',
  7  	    p.report	     = 'report1',
  8  	    p.task_code      = 'task_code1',
  9  	    p.price	     = 10,
 10  	    p.date_of_change = TRUNC (SYSDATE)
 11  /

1 row updated.

SCOTT@orcl_11g> UPDATE TABLE
  2  	      (SELECT h.price
  3  	       FROM   price_list_demo h
  4  	       WHERE  firm_id = 'firm2'
  5  	       AND    user_id = 'user2') p
  6  SET    p.task_type      = 'task_type2',
  7  	    p.report	     = 'report2',
  8  	    p.task_code      = 'task_code2',
  9  	    p.price	     = 20,
 10  	    p.date_of_change = TRUNC (SYSDATE)
 11  /

1 row updated.

SCOTT@orcl_11g> SELECT * FROM price_list_demo
  2  /

  PRICE_ID FIRM_ID USER_ID
---------- ------- -------
PRICE(TASK_TYPE, REPORT, TASK_CODE, PRICE, DATE_OF_CHANGE)
----------------------------------------------------------------------------------------------------
         1 firm1   user1
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type1', 'report1', 'task_code1', 10, '22-MAY-08'))

         2 firm2   user2
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type2', 'report2', 'task_code2', 20, '22-MAY-08'))


SCOTT@orcl_11g>

Report message to a moderator

Re: Update Nested Table [message #322473 is a reply to message #322214] Fri, 23 May 2008 12:15 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member
Thanks a lot Barbara

This worked fine. Since this is the first time I am using Nested Tables so want to clarify one thing. Can we have multiple rows entry in nested column for one record in main table.

For example The records you posted earlier I am adding sample records to it:

PRICE_ID FIRM_ID USER_ID
---------- ------- -------
PRICE(TASK_TYPE, REPORT, TASK_CODE, PRICE, DATE_OF_CHANGE)
----------------------------------------------------------------------------------------------------
1 firm1 user1
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type1', 'report1', 'task_code1', 10, '22-MAY-08'))

1 firm1 user1
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type11', 'report11', 'task_code11', 10, '22-MAY-08'))

1 firm1 user1
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type12', 'report12', 'task_code12', 5, '22-MAY-08'))

2 firm2 user2
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type2', 'report2', 'task_code2', 20, '22-MAY-08'))

2 firm2 user2
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type21', 'report21', 'task_code21', 33, '22-MAY-08'))


Can we do that in nested table if yes can you please send me a sample insert script?

I shall be very thankful to you.

Regards,
Amit

Report message to a moderator

Re: Update Nested Table [message #322477 is a reply to message #322473] Fri, 23 May 2008 12:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
 
SCOTT@orcl_11g> CREATE OR REPLACE TYPE PRICE_DETAIL_DEMO AS OBJECT
  2    (TASK_TYPE      VARCHAR2(20),
  3  	REPORT	       VARCHAR2(200),
  4  	TASK_CODE      VARCHAR2(200),
  5  	PRICE	       NUMBER(5,2),
  6  	DATE_OF_CHANGE DATE)
  7  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE PRICE_DETAIL_DEMO_TYPE AS TABLE OF PRICE_DETAIL_DEMO;
  2  /

Type created.

SCOTT@orcl_11g> CREATE TABLE PRICE_LIST_DEMO
  2    (PRICE_ID       NUMBER,
  3  	FIRM_ID        VARCHAR2(40),
  4  	USER_ID        VARCHAR2(40),
  5  	PRICE	       PRICE_DETAIL_DEMO_TYPE)
  6    NESTED TABLE PRICE STORE AS price_store_tab
  7  /

Table created.

SCOTT@orcl_11g> INSERT INTO price_list_demo (price_id, firm_id, user_id, price)
  2  VALUES (1, 'firm1', 'user1',
  3  	     price_detail_demo_type
  4  	       (price_detail_demo ('task_type1', 'report1', 'task_code1', 10, TRUNC (SYSDATE) - 1),
  5  		price_detail_demo ('task_type11', 'report11', 'task_code11', 10, TRUNC (SYSDATE) - 1),
  6  		price_detail_demo ('task_type12', 'report12', 'task_code12', 5, TRUNC (SYSDATE) - 1)))
  7  /

1 row created.

SCOTT@orcl_11g> INSERT INTO price_list_demo (price_id, firm_id, user_id, price)
  2  VALUES (2, 'firm2', 'user2',
  3  	     price_detail_demo_type
  4  	       (price_detail_demo ('task_type2', 'report2', 'task_code2', 20, TRUNC (SYSDATE) - 1),
  5  		price_detail_demo ('task_type21', 'report21', 'task_code21', 33, TRUNC (SYSDATE) - 1)))
  6  /

1 row created.

SCOTT@orcl_11g> COLUMN firm_id FORMAT A7
SCOTT@orcl_11g> COLUMN user_id FORMAT A7
SCOTT@orcl_11g> SELECT * FROM price_list_demo
  2  /

  PRICE_ID FIRM_ID USER_ID
---------- ------- -------
PRICE(TASK_TYPE, REPORT, TASK_CODE, PRICE, DATE_OF_CHANGE)
--------------------------------------------------------------------------------
         1 firm1   user1
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type1', 'report1', 'task_code1',
10, '22-MAY-08'), PRICE_DETAIL_DEMO('task_type11', 'report11', 'task_code11', 10
, '22-MAY-08'), PRICE_DETAIL_DEMO('task_type12', 'report12', 'task_code12', 5, '
22-MAY-08'))

         2 firm2   user2
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type2', 'report2', 'task_code2',
20, '22-MAY-08'), PRICE_DETAIL_DEMO('task_type21', 'report21', 'task_code21', 33
, '22-MAY-08'))


SCOTT@orcl_11g> COLUMN task_type FORMAT A11
SCOTT@orcl_11g> COLUMN report	 FORMAT A8
SCOTT@orcl_11g> COLUMN task_code FORMAT A11
SCOTT@orcl_11g> SELECT t.price_id, t.firm_id, t.user_id,
  2  	    p.task_type, p.report, p.task_code, p.price
  3  FROM   price_list_demo t,
  4  	    TABLE (price) p
  5  /

  PRICE_ID FIRM_ID USER_ID TASK_TYPE   REPORT   TASK_CODE        PRICE
---------- ------- ------- ----------- -------- ----------- ----------
         1 firm1   user1   task_type1  report1  task_code1          10
         1 firm1   user1   task_type11 report11 task_code11         10
         1 firm1   user1   task_type12 report12 task_code12          5
         2 firm2   user2   task_type2  report2  task_code2          20
         2 firm2   user2   task_type21 report21 task_code21         33

SCOTT@orcl_11g>

Report message to a moderator

Re: Update Nested Table [message #322490 is a reply to message #322477] Fri, 23 May 2008 13:31 Go to previous messageGo to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member
Barbara

Thank you very much. I really appreciate your help.

Thanks once again.

Regards,
Amit

Report message to a moderator

Re: Update Nested Table [message #322492 is a reply to message #322473] Fri, 23 May 2008 13:33 Go to previous message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
Here is another example that inserts using a select from other tables, instead of a values clause. I used a price_data table to simulate the results that you would get from a select from the items, contents, price_mapping, and lists tables, using proper join conditions, since you have not posted the structure of those tables. I used cast and multiset to populate the collection. I also used a trigger to populate the price_id from the sequence. I think this is closer to what you were trying to do with your procedure, but a lot simpler and more efficient. The results are the same as the previous inserts with values clauses.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE PRICE_DETAIL_DEMO AS OBJECT
  2    (TASK_TYPE      VARCHAR2(20),
  3  	REPORT	       VARCHAR2(200),
  4  	TASK_CODE      VARCHAR2(200),
  5  	PRICE	       NUMBER(5,2),
  6  	DATE_OF_CHANGE DATE)
  7  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE PRICE_DETAIL_DEMO_TYPE AS TABLE OF PRICE_DETAIL_DEMO;
  2  /

Type created.

SCOTT@orcl_11g> CREATE TABLE PRICE_LIST_DEMO
  2    (PRICE_ID       NUMBER,
  3  	FIRM_ID        VARCHAR2(40),
  4  	USER_ID        VARCHAR2(40),
  5  	PRICE	       PRICE_DETAIL_DEMO_TYPE)
  6    NESTED TABLE PRICE STORE AS price_store_tab
  7  /

Table created.

SCOTT@orcl_11g> CREATE TABLE users
  2    (id	       NUMBER,
  3  	firm_id        VARCHAR2(40),
  4  	user_id        VARCHAR2(40))
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO users VALUES (1, 'firm1', 'user1')
  3  INTO users VALUES (2, 'firm2', 'user2')
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> CREATE TABLE price_data -- from other tables (items, contents, price_mapping, lists)
  2    (user_id        NUMBER,
  3  	TASK_TYPE      VARCHAR2(20),
  4  	REPORT	       VARCHAR2(200),
  5  	TASK_CODE      VARCHAR2(200),
  6  	PRICE	       NUMBER(5,2),
  7  	DATE_OF_CHANGE DATE)
  8  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO price_data VALUES (1, 'task_type1', 'report1', 'task_code1', 10, TRUNC (SYSDATE) - 1)
  3  INTO price_data VALUES (1, 'task_type11', 'report11', 'task_code11', 10, TRUNC (SYSDATE) - 1)
  4  INTO price_data VALUES (1, 'task_type12', 'report12', 'task_code12', 5, TRUNC (SYSDATE) - 1)
  5  INTO price_data VALUES (2, 'task_type2', 'report2', 'task_code2', 20, TRUNC (SYSDATE) - 1)
  6  INTO price_data VALUES (2, 'task_type21', 'report21', 'task_code21', 33, TRUNC (SYSDATE) - 1)
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11g> CREATE SEQUENCE price_id_seq
  2  INCREMENT BY 1
  3  START WITH 1
  4  MINVALUE 1
  5  /

Sequence created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER price_list_demo_bir
  2    BEFORE INSERT ON price_list_demo
  3    FOR EACH ROW
  4  BEGIN
  5    SELECT price_id_seq.NEXTVAL
  6    INTO   :NEW.price_id
  7    FROM   DUAL;
  8  END price_list_demo_bir;
  9  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO price_list_demo (firm_id, user_id, price)
  2  SELECT u.firm_id, u.user_id,
  3  	    CAST (MULTISET (SELECT p.task_type, p.report, p.task_code, p.price, p.date_of_change
  4  			    FROM   price_data p
  5  			    WHERE  p.user_id = u.id) AS price_detail_demo_type)
  6  FROM   users u
  7  /

2 rows created.

SCOTT@orcl_11g> COLUMN firm_id FORMAT A7
SCOTT@orcl_11g> COLUMN user_id FORMAT A7
SCOTT@orcl_11g> SELECT * FROM price_list_demo
  2  /

  PRICE_ID FIRM_ID USER_ID
---------- ------- -------
PRICE(TASK_TYPE, REPORT, TASK_CODE, PRICE, DATE_OF_CHANGE)
--------------------------------------------------------------------------------
         1 firm1   user1
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type1', 'report1', 'task_code1',
10, '22-MAY-08'), PRICE_DETAIL_DEMO('task_type11', 'report11', 'task_code11', 10
, '22-MAY-08'), PRICE_DETAIL_DEMO('task_type12', 'report12', 'task_code12', 5, '
22-MAY-08'))

         2 firm2   user2
PRICE_DETAIL_DEMO_TYPE(PRICE_DETAIL_DEMO('task_type2', 'report2', 'task_code2',
20, '22-MAY-08'), PRICE_DETAIL_DEMO('task_type21', 'report21', 'task_code21', 33
, '22-MAY-08'))


SCOTT@orcl_11g> COLUMN task_type FORMAT A11
SCOTT@orcl_11g> COLUMN report	 FORMAT A8
SCOTT@orcl_11g> COLUMN task_code FORMAT A11
SCOTT@orcl_11g> SELECT t.price_id, t.firm_id, t.user_id,
  2  	    p.task_type, p.report, p.task_code, p.price
  3  FROM   price_list_demo t,
  4  	    TABLE (price) p
  5  /

  PRICE_ID FIRM_ID USER_ID TASK_TYPE   REPORT   TASK_CODE        PRICE
---------- ------- ------- ----------- -------- ----------- ----------
         1 firm1   user1   task_type1  report1  task_code1          10
         1 firm1   user1   task_type11 report11 task_code11         10
         1 firm1   user1   task_type12 report12 task_code12          5
         2 firm2   user2   task_type2  report2  task_code2          20
         2 firm2   user2   task_type21 report21 task_code21         33

SCOTT@orcl_11g>

Report message to a moderator

Previous Topic: Remote Pipeline (split)
Next Topic: Optimize Delete statement
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Aug 25 04:31:01 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.