optimize query

Hello,

How can I optimize this query :
EXPLAIN PLAN FOR update REVERSE_ORDER_LINE set PICKING_QUANTITY = 
	nvl(
		(
			select PICKING_QUANTITY from (
  				select nvl(pllQty,0) + nvl(stockQty,0) as PICKING_QUANTITY, rolid from (
	  				select CASE WHEN total<0 THEN 0 ELSE total END as pllQty, stockQty,   nvl(rolid1, rolid2) as rolid FROM(
						select sum(nvl(pll.Quantity,0)) as total, rol.reverse_order_line_id as rolid1 from picking_list_line pll 
						inner join reverse_order_line rol on pll.reverse_order_line_id = rol.reverse_order_line_id 
						group by rol.reverse_order_line_id
		   			)
	    			full outer join (
     					select nvl(store_stock,0)  + nvl(total_order_line,0)  as stockQty, nvl(rolid2, rolid3) as rolid2  from (
							select sum(sp.stock) as store_stock, rol.reverse_order_line_id as rolid2 from REVERSE_ORDER ro 
							inner join REVERSE_ORDER_LINE rol on ro.reverse_order_id = rol.reverse_order_id
							inner join store_product sp on ro.source_store_id = sp.store_id and sp.product_id = rol.product_id
							inner join picking_list pl on rol.picking_list_id = pl.picking_list_id
							left join picking_list_line pll on pll.picking_list_id = pl.picking_list_id
							where rol.all_quantity ='Y'
							and rol.ro_line_status_id = (select ro_line_status_id from ro_line_status where code = 'PICKING')
							and pll.reverse_order_line_id is null		  
							group by rol.reverse_order_line_id
						) full outer join (
							select  sum(rol.requested_quantity ) as total_order_line, rol.reverse_order_line_id as rolid3  from REVERSE_ORDER ro 
							inner join REVERSE_ORDER_LINE rol on ro.reverse_order_id = rol.reverse_order_id
							inner join picking_list pl on rol.picking_list_id = pl.picking_list_id
							left join picking_list_line pll on pll.picking_list_id = pl.picking_list_id 
							where  rol.ALL_QUANTITY = 'N' and rol.ro_line_status_id = (select ro_line_status_id from ro_line_status where code = 'PICKING')
							and pll.reverse_order_line_id is null
							group by rol.reverse_order_line_id
						) on rolid2 = rolid3 or rolid2 is null or rolid3 is null
					) on rolid1 = rolid2 or rolid2 is null or rolid1 is null order by rolid asc
				)
      		)
			where rolid=REVERSE_ORDER_LINE_ID
		)
	,0);

Open in new window



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 137090316

--------------------------------------------------------------------------------
----------------------------------------------------

| Id  | Operation                                       | Name
   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
----------------------------------------------------

|   0 | UPDATE STATEMENT                                |
   |   537K|  4199K|       |  5014P  (1)|999:59:59 |

|   1 |  UPDATE                                         | REVERSE_ORDER_LINE
   |       |       |       |            |          |

|   2 |   TABLE ACCESS STORAGE FULL                     | REVERSE_ORDER_LINE
   |   537K|  4199K|       |  1042   (1)| 00:00:13 |

|   3 |   VIEW                                          |
   |  5453M|   198G|       |  9328G  (1)|999:59:59 |

|   4 |    SORT ORDER BY                                |
   |  5453M|   264G|   327G|  9328G  (1)|999:59:59 |

|   5 |     VIEW                                        |
   |  5453M|   264G|       |  9328G  (1)|999:59:59 |

|   6 |      UNION-ALL                                  |
   |       |       |       |            |          |

|   7 |       NESTED LOOPS OUTER                        |
   |  5453M|   264G|       |  9328G  (1)|999:59:59 |

|*  8 |        VIEW                                     |
   |   306K|  7773K|       |  3861   (1)| 00:00:47 |

|   9 |         SORT GROUP BY                           |
   |   306K|  4783K|  7216K|  3861   (1)| 00:00:47 |

|* 10 |          HASH JOIN                              |
   |   306K|  4783K|  6648K|  2231   (1)| 00:00:27 |

|  11 |           TABLE ACCESS STORAGE FULL             | PICKING_LIST_LINE
   |   309K|  3021K|       |   406   (1)| 00:00:05 |

|  12 |           TABLE ACCESS STORAGE FULL             | REVERSE_ORDER_LINE
   |   537K|  3149K|       |  1039   (1)| 00:00:13 |

|  13 |        VIEW                                     |
   | 17812 |   452K|       |    30M  (1)|101:33:49 |

|* 14 |         VIEW                                    |
   | 17812 |   904K|       |    30M  (1)|101:33:49 |

|  15 |          UNION-ALL                              |
   |       |       |       |            |          |

|  16 |           NESTED LOOPS OUTER                    |
   |  3533 |   155K|       |  4377K  (1)| 14:35:30 |

|  17 |            VIEW                                 |
   |  3533 | 67127 |       | 30794   (1)| 00:06:10 |

|  18 |             SORT GROUP BY                       |
   |  3533 |   224K|       | 30794   (1)| 00:06:10 |

|  19 |              NESTED LOOPS                       |
   |       |       |       |            |          |

|  20 |               NESTED LOOPS                      |
   |  3533 |   224K|       | 30792   (1)| 00:06:10 |

|* 21 |                HASH JOIN                        |
   | 14279 |   752K|       |  2226   (1)| 00:00:27 |

|* 22 |                 FILTER                          |
   |       |       |       |            |          |

|* 23 |                  HASH JOIN OUTER                |
   | 14279 |   613K|       |  1632   (1)| 00:00:20 |

|* 24 |                   HASH JOIN                     |
   | 19597 |   612K|       |  1225   (1)| 00:00:15 |

|* 25 |                    TABLE ACCESS STORAGE FULL    | REVERSE_ORDER_LINE
   | 19597 |   497K|       |  1044   (1)| 00:00:13 |

|  26 |                     TABLE ACCESS BY INDEX ROWID | RO_LINE_STATUS
   |     1 |    12 |       |     1   (0)| 00:00:01 |

|* 27 |                      INDEX UNIQUE SCAN          | IX_RO_LINE_STATUS_SC
   |     1 |       |       |     0   (0)| 00:00:01 |

|  28 |                    TABLE ACCESS STORAGE FULL    | PICKING_LIST
   | 91638 |   536K|       |   180   (1)| 00:00:03 |

|  29 |                   TABLE ACCESS STORAGE FULL     | PICKING_LIST_LINE
   |   309K|  3626K|       |   406   (1)| 00:00:05 |

|  30 |                 TABLE ACCESS STORAGE FULL       | REVERSE_ORDER
   |   172K|  1683K|       |   593   (1)| 00:00:08 |

|* 31 |                INDEX UNIQUE SCAN                | STORE_PRODUCT_IND_003
   |     1 |       |       |     1   (0)| 00:00:01 |

|  32 |               TABLE ACCESS BY INDEX ROWID       | STORE_PRODUCT
   |     1 |    11 |       |     2   (0)| 00:00:01 |

|  33 |            VIEW                                 |
   |     1 |    26 |       |  1230   (1)| 00:00:15 |

|  34 |             SORT GROUP BY                       |
   |     1 |    49 |       |  1230   (1)| 00:00:15 |

|* 35 |              FILTER                             |
   |       |       |       |            |          |

|  36 |               NESTED LOOPS OUTER                |
   |     1 |    49 |       |  1228   (1)| 00:00:15 |

|  37 |                NESTED LOOPS                     |
   |     1 |    37 |       |  1222   (1)| 00:00:15 |

|* 38 |                 HASH JOIN                       |
   |     1 |    31 |       |  1222   (1)| 00:00:15 |

|* 39 |                  TABLE ACCESS STORAGE FULL      | REVERSE_ORDER_LINE
   |     1 |    25 |       |  1042   (1)| 00:00:13 |

|  40 |                   TABLE ACCESS BY INDEX ROWID   | RO_LINE_STATUS
   |     1 |    12 |       |     1   (0)| 00:00:01 |

|* 41 |                    INDEX UNIQUE SCAN            | IX_RO_LINE_STATUS_SC
   |     1 |       |       |     0   (0)| 00:00:01 |

|  42 |                  TABLE ACCESS STORAGE FULL      | PICKING_LIST
   | 91638 |   536K|       |   180   (1)| 00:00:03 |

|* 43 |                 INDEX UNIQUE SCAN               | SYS_C004412
   |     1 |     6 |       |     0   (0)| 00:00:01 |

|  44 |                TABLE ACCESS BY INDEX ROWID      | PICKING_LIST_LINE
   | 83961 |   983K|       |     6   (0)| 00:00:01 |

|* 45 |                 INDEX RANGE SCAN                | PICKING_LIST_LINE_IND_
02 |    13 |       |       |     2   (0)| 00:00:01 |

|  46 |           SORT GROUP BY                         |
   | 14279 |   683K|       |    26M  (1)| 86:58:19 |

|* 47 |            FILTER                               |
   |       |       |       |            |          |

|  48 |             NESTED LOOPS                        |
   | 14279 |   683K|       |  1633   (1)| 00:00:20 |

|* 49 |              FILTER                             |
   |       |       |       |            |          |

|* 50 |               HASH JOIN OUTER                   |
   | 14279 |   599K|       |  1632   (1)| 00:00:20 |

|* 51 |                HASH JOIN                        |
   | 19597 |   593K|       |  1225   (1)| 00:00:15 |

|* 52 |                 TABLE ACCESS STORAGE FULL       | REVERSE_ORDER_LINE
   | 19597 |   478K|       |  1044   (1)| 00:00:13 |

|  53 |                  TABLE ACCESS BY INDEX ROWID    | RO_LINE_STATUS
   |     1 |    12 |       |     1   (0)| 00:00:01 |

|* 54 |                   INDEX UNIQUE SCAN             | IX_RO_LINE_STATUS_SC
   |     1 |       |       |     0   (0)| 00:00:01 |

|  55 |                 TABLE ACCESS STORAGE FULL       | PICKING_LIST
   | 91638 |   536K|       |   180   (1)| 00:00:03 |

|  56 |                TABLE ACCESS STORAGE FULL        | PICKING_LIST_LINE
   |   309K|  3626K|       |   406   (1)| 00:00:05 |

|* 57 |              INDEX UNIQUE SCAN                  | SYS_C004412
   |     1 |     6 |       |     0   (0)| 00:00:01 |

|  58 |             VIEW                                |
   |   177 |  2301 |       |  3654   (1)| 00:00:44 |

|  59 |              SORT GROUP BY                      |
   |   177 | 11505 |       |  3654   (1)| 00:00:44 |

|  60 |               NESTED LOOPS                      |
   |       |       |       |            |          |

|  61 |                NESTED LOOPS                     |
   |   177 | 11505 |       |  3652   (1)| 00:00:44 |

|* 62 |                 HASH JOIN                       |
   |   714 | 38556 |       |  2224   (1)| 00:00:27 |

|* 63 |                  FILTER                         |
   |       |       |       |            |          |

|* 64 |                   HASH JOIN OUTER               |
   |   714 | 31416 |       |  1630   (1)| 00:00:20 |

|* 65 |                    HASH JOIN                    |
   |   980 | 31360 |       |  1223   (1)| 00:00:15 |

|* 66 |                     TABLE ACCESS STORAGE FULL   | REVERSE_ORDER_LINE
   |   980 | 25480 |       |  1042   (1)| 00:00:13 |

|  67 |                      TABLE ACCESS BY INDEX ROWID| RO_LINE_STATUS
   |     1 |    12 |       |     1   (0)| 00:00:01 |

|* 68 |                       INDEX UNIQUE SCAN         | IX_RO_LINE_STATUS_SC
   |     1 |       |       |     0   (0)| 00:00:01 |

|  69 |                     TABLE ACCESS STORAGE FULL   | PICKING_LIST
   | 91638 |   536K|       |   180   (1)| 00:00:03 |

|  70 |                    TABLE ACCESS STORAGE FULL    | PICKING_LIST_LINE
   |   309K|  3626K|       |   406   (1)| 00:00:05 |

|  71 |                  TABLE ACCESS STORAGE FULL      | REVERSE_ORDER
   |   172K|  1683K|       |   593   (1)| 00:00:08 |

|* 72 |                 INDEX UNIQUE SCAN               | STORE_PRODUCT_IND_003
   |     1 |       |       |     1   (0)| 00:00:01 |

|  73 |                TABLE ACCESS BY INDEX ROWID      | STORE_PRODUCT
   |     1 |    11 |       |     2   (0)| 00:00:01 |

|* 74 |       FILTER                                    |
   |       |       |       |            |          |

|  75 |        VIEW                                     |
   |  3676 |   186K|       |  4640K  (1)| 15:28:01 |

|  76 |         UNION-ALL                               |
   |       |       |       |            |          |

|* 77 |          FILTER                                 |
   |       |       |       |            |          |

|  78 |           NESTED LOOPS OUTER                    |
   |  3533 | 81259 |       |  4377K  (1)| 14:35:30 |

|  79 |            VIEW                                 |
   |  3533 | 67127 |       | 30794   (1)| 00:06:10 |

|  80 |             SORT GROUP BY                       |
   |  3533 |   224K|       | 30794   (1)| 00:06:10 |

|  81 |              NESTED LOOPS                       |
   |       |       |       |            |          |

|  82 |               NESTED LOOPS                      |
   |  3533 |   224K|       | 30792   (1)| 00:06:10 |

|* 83 |                HASH JOIN                        |
   | 14279 |   752K|       |  2226   (1)| 00:00:27 |

|* 84 |                 FILTER                          |
   |       |       |       |            |          |

|* 85 |                  HASH JOIN OUTER                |
   | 14279 |   613K|       |  1632   (1)| 00:00:20 |

|* 86 |                   HASH JOIN                     |
   | 19597 |   612K|       |  1225   (1)| 00:00:15 |

|* 87 |                    TABLE ACCESS STORAGE FULL    | REVERSE_ORDER_LINE
   | 19597 |   497K|       |  1044   (1)| 00:00:13 |

|  88 |                     TABLE ACCESS BY INDEX ROWID | RO_LINE_STATUS
   |     1 |    12 |       |     1   (0)| 00:00:01 |

|* 89 |                      INDEX UNIQUE SCAN          | IX_RO_LINE_STATUS_SC
   |     1 |       |       |     0   (0)| 00:00:01 |

|  90 |                    TABLE ACCESS STORAGE FULL    | PICKING_LIST
   | 91638 |   536K|       |   180   (1)| 00:00:03 |

|  91 |                   TABLE ACCESS STORAGE FULL     | PICKING_LIST_LINE
   |   309K|  3626K|       |   406   (1)| 00:00:05 |

|  92 |                 TABLE ACCESS STORAGE FULL       | REVERSE_ORDER
   |   172K|  1683K|       |   593   (1)| 00:00:08 |

|* 93 |                INDEX UNIQUE SCAN                | STORE_PRODUCT_IND_003
   |     1 |       |       |     1   (0)| 00:00:01 |

|  94 |               TABLE ACCESS BY INDEX ROWID       | STORE_PRODUCT
   |     1 |    11 |       |     2   (0)| 00:00:01 |

|  95 |            VIEW                                 |
   |     1 |     4 |       |  1230   (1)| 00:00:15 |

|  96 |             SORT GROUP BY                       |
   |     1 |    49 |       |  1230   (1)| 00:00:15 |

|* 97 |              FILTER                             |
   |       |       |       |            |          |

|  98 |               NESTED LOOPS OUTER                |
   |     1 |    49 |       |  1228   (1)| 00:00:15 |

|  99 |                NESTED LOOPS                     |
   |     1 |    37 |       |  1222   (1)| 00:00:15 |

|*100 |                 HASH JOIN                       |
   |     1 |    31 |       |  1222   (1)| 00:00:15 |

|*101 |                  TABLE ACCESS STORAGE FULL      | REVERSE_ORDER_LINE
   |     1 |    25 |       |  1042   (1)| 00:00:13 |

| 102 |                   TABLE ACCESS BY INDEX ROWID   | RO_LINE_STATUS
   |     1 |    12 |       |     1   (0)| 00:00:01 |

|*103 |                    INDEX UNIQUE SCAN            | IX_RO_LINE_STATUS_SC
   |     1 |       |       |     0   (0)| 00:00:01 |

| 104 |                  TABLE ACCESS STORAGE FULL      | PICKING_LIST
   | 91638 |   536K|       |   180   (1)| 00:00:03 |

|*105 |                 INDEX UNIQUE SCAN               | SYS_C004412
   |     1 |     6 |       |     0   (0)| 00:00:01 |

| 106 |                TABLE ACCESS BY INDEX ROWID      | PICKING_LIST_LINE
   | 83961 |   983K|       |     6   (0)| 00:00:01 |

|*107 |                 INDEX RANGE SCAN                | PICKING_LIST_LINE_IND_
02 |    13 |       |       |     2   (0)| 00:00:01 |

| 108 |          SORT GROUP BY                          |
   |   143 |  7007 |       |   262K  (1)| 00:52:31 |

|*109 |           FILTER                                |
   |       |       |       |            |          |

| 110 |            NESTED LOOPS                         |
   |   143 |  7007 |       |  1632   (1)| 00:00:20 |

|*111 |             FILTER                              |
   |       |       |       |            |          |

|*112 |              HASH JOIN OUTER                    |
   |   143 |  6149 |       |  1632   (1)| 00:00:20 |

|*113 |               HASH JOIN                         |
   |   196 |  6076 |       |  1225   (1)| 00:00:15 |

|*114 |                TABLE ACCESS STORAGE FULL        | REVERSE_ORDER_LINE
   |   196 |  4900 |       |  1045   (1)| 00:00:13 |

| 115 |                 TABLE ACCESS BY INDEX ROWID     | RO_LINE_STATUS
   |     1 |    12 |       |     1   (0)| 00:00:01 |

|*116 |                  INDEX UNIQUE SCAN              | IX_RO_LINE_STATUS_SC
   |     1 |       |       |     0   (0)| 00:00:01 |

| 117 |                TABLE ACCESS STORAGE FULL        | PICKING_LIST
   | 91638 |   536K|       |   180   (1)| 00:00:03 |

| 118 |               TABLE ACCESS STORAGE FULL         | PICKING_LIST_LINE
   |   309K|  3626K|       |   406   (1)| 00:00:05 |

|*119 |             INDEX UNIQUE SCAN                   | SYS_C004412
   |     1 |     6 |       |     0   (0)| 00:00:01 |

| 120 |            VIEW                                 |
   |   177 |  2301 |       |  3654   (1)| 00:00:44 |

| 121 |             SORT GROUP BY                       |
   |   177 | 11505 |       |  3654   (1)| 00:00:44 |

| 122 |              NESTED LOOPS                       |
   |       |       |       |            |          |

| 123 |               NESTED LOOPS                      |
   |   177 | 11505 |       |  3652   (1)| 00:00:44 |

|*124 |                HASH JOIN                        |
   |   714 | 38556 |       |  2224   (1)| 00:00:27 |

|*125 |                 FILTER                          |
   |       |       |       |            |          |

|*126 |                  HASH JOIN OUTER                |
   |   714 | 31416 |       |  1630   (1)| 00:00:20 |

|*127 |                   HASH JOIN                     |
   |   980 | 31360 |       |  1223   (1)| 00:00:15 |

|*128 |                    TABLE ACCESS STORAGE FULL    | REVERSE_ORDER_LINE
   |   980 | 25480 |       |  1042   (1)| 00:00:13 |

| 129 |                     TABLE ACCESS BY INDEX ROWID | RO_LINE_STATUS
   |     1 |    12 |       |     1   (0)| 00:00:01 |

|*130 |                      INDEX UNIQUE SCAN          | IX_RO_LINE_STATUS_SC
   |     1 |       |       |     0   (0)| 00:00:01 |

| 131 |                    TABLE ACCESS STORAGE FULL    | PICKING_LIST
   | 91638 |   536K|       |   180   (1)| 00:00:03 |

| 132 |                   TABLE ACCESS STORAGE FULL     | PICKING_LIST_LINE
   |   309K|  3626K|       |   406   (1)| 00:00:05 |

| 133 |                 TABLE ACCESS STORAGE FULL       | REVERSE_ORDER
   |   172K|  1683K|       |   593   (1)| 00:00:08 |

|*134 |                INDEX UNIQUE SCAN                | STORE_PRODUCT_IND_003
   |     1 |       |       |     1   (0)| 00:00:01 |

| 135 |               TABLE ACCESS BY INDEX ROWID       | STORE_PRODUCT
   |     1 |    11 |       |     2   (0)| 00:00:01 |

| 136 |        VIEW                                     |
   | 26872 |   341K|       |  1458   (2)| 00:00:18 |

| 137 |         SORT GROUP BY                           |
   | 26872 |   419K|       |  1458   (2)| 00:00:18 |

|*138 |          HASH JOIN                              |
   | 26916 |   420K|       |  1457   (2)| 00:00:18 |

|*139 |           TABLE ACCESS STORAGE FULL             | REVERSE_ORDER_LINE
   | 26880 |   157K|       |  1049   (2)| 00:00:13 |

| 140 |           TABLE ACCESS STORAGE FULL             | PICKING_LIST_LINE
   |   309K|  3021K|       |   406   (1)| 00:00:05 |

--------------------------------------------------------------------------------

Thanks

Regards
bibi92Asked:
Who is Participating?
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.

slightwv (䄆 Netminder) Commented:
First:
The "order by" really serves no purpose so you do a sort for no reason.

It looks like you can combine the two larger queries into one.

I seriously doubt what I'm going to paste below is even close to correct but it should provide the idea of how to combine them.

     					select
     						sum(case all_quantity = 'Y' then nvl(sp_stock,0)  + nvl(total_order_line,0) end) as stockQty,
     						sum(case all_quantity = 'N' then requested_quantity end) as total_order_line,
     						nvl(rolid2, rolid3) as rolid2 
     						reverse_order_line_id as rolid3 
     					from (
							select sp.stock as store_stock, rol.reverse_order_line_id as rolid2,
								rol.requested_quantity as total_order_line,
								rol.reverse_order_line_id as rolid3,
								all_quantity
							from REVERSE_ORDER ro 
							inner join REVERSE_ORDER_LINE rol on ro.reverse_order_id = rol.reverse_order_id
							inner join store_product sp on ro.source_store_id = sp.store_id and sp.product_id = rol.product_id
							inner join picking_list pl on rol.picking_list_id = pl.picking_list_id
							left join picking_list_line pll on pll.picking_list_id = pl.picking_list_id
							where rol.all_quantity in ('Y','N')
							and rol.ro_line_status_id = (select ro_line_status_id from ro_line_status where code = 'PICKING')
							and pll.reverse_order_line_id is null		  
						)
							group by rolid2, rolid3, reverse_order_line_id

Open in new window


Once you get them combined and working, post an updated plan.

and PLEASE put that plan inside a code block so it is readable...
0
slightwv (䄆 Netminder) Commented:
From a little more scanning, I'm thinking the who thing can be re-written so it only accesses the base tables once.

I'm not seeing an actual reason to keep selecting sums of various columns from picking_list_line and reverse_order_line just to join them to sums from pretty much the same two tables.

Using SUM with CASE and windowing functions with OVER, I think you can probably get everything you need.

Then one or two outer queries to possibly finalize the value and you should be good.

Before you ask:
No, I cannot rewrite it all for you.  I don't understand your tables or the actual requirements for the update.

If you can provide a simplified test case (tables, sample data and expected results), I can try to come up with a working example.
0
bibi92Author Commented:
Hello,

I don't see missing right parenthesis :
CASE all_quantity = 'Y' then nvl(sp_stock,0) + nvl(total_order_line,0)
              *
ERROR at line 2:
ORA-00907: missing right parenthesis

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>I don't see missing right parenthesis :

There must be one...  I said I doubted what I posted would work as-is.

Understand the idea behind what I posted and migrate your query.

If you cannot find the missing paran with what you have, post the code and I can try to help you find it.  Not saying I can 'fix' it, just help you find it.
0
bibi92Author Commented:
Following the code :
SELECT   SUM( 
         CASE all_quantity = 'Y' then nvl(sp_stock,0) + nvl(total_order_line,0) 
         END) AS stockqty, 
         SUM( 
         CASE all_quantity = 'N' THEN requested_quantity 
         END)                AS total_order_line, 
         nvl(rolid2, rolid3) AS rolid2 reverse_order_line_id AS rolid3 
FROM     ( 
                    SELECT     sp.stock                  AS store_stock, 
                               rol.reverse_order_line_id AS rolid2, 
                               rol.requested_quantity    AS total_order_line, 
                               rol.reverse_order_line_id AS rolid3, 
                               all_quantity 
                    FROM       reverse_order ro 
                    inner join reverse_order_line rol 
                    ON         ro.reverse_order_id = rol.reverse_order_id 
                    inner join store_product sp 
                    ON         ro.source_store_id = sp.store_id 
                    AND        sp.product_id = rol.product_id 
                    inner join picking_list pl 
                    ON         rol.picking_list_id = pl.picking_list_id 
                    left join  picking_list_line pll 
                    ON         pll.picking_list_id = pl.picking_list_id 
                    WHERE      rol.all_quantity IN ('Y', 
                                                    'N') 
                    AND        rol.ro_line_status_id = 
                               ( 
                                      SELECT ro_line_status_id 
                                      FROM   ro_line_status 
                                      WHERE  code = 'PICKING') 
                    AND        pll.reverse_order_line_id IS NULL ) 
GROUP BY rolid2, 
         rolid3, 
         reverse_order_line_id  

Open in new window

Thanks
0
slightwv (䄆 Netminder) Commented:
I feel silly...  I didn't need all the code:

change:
CASE all_quantity ...

to:
CASE WHEN all_quantity ...


You also have a missing comma.
Change:
nvl(rolid2, rolid3) AS rolid2 reverse_order_line_id AS rolid3

to:
nvl(rolid2, rolid3) AS rolid2, reverse_order_line_id AS rolid3
0
bibi92Author Commented:
Thanks a lot case are resolved but ERROR at line 34:
ORA-00904: "REVERSE_ORDER_LINE_ID": invalid identifier
0
slightwv (䄆 Netminder) Commented:
Line 34 in the last code you posted is the group by.

Make sure reverse_order_line_id is in the select.  That was the one with the missing comma.
0

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
bibi92Author Commented:
UPDATE reverse_order_line
SET    picking_quantity =
select   SUM(
         CASE
                  WHEN all_quantity = 'Y' THEN nvl(store_stock, 0) + nvl(total_order_line, 0)
         END) AS stockqty,
         SUM(
         CASE
                  WHEN all_quantity = 'N' THEN total_order_line
         END)                AS total_order_line,
         nvl(rolid2, rolid3) AS rolid2,
         rolid3
FROM     (
                    SELECT     sp.stock                  AS store_stock,
                               rol.reverse_order_line_id AS rolid2,
                               rol.requested_quantity    AS total_order_line,
                               rol.reverse_order_line_id AS rolid3,
                               all_quantity
                    FROM       reverse_order ro
                    inner join reverse_order_line rol
                    ON         ro.reverse_order_id = rol.reverse_order_id
                    inner join store_product sp
                    ON         ro.source_store_id = sp.store_id
                    AND        sp.product_id = rol.product_id
                    inner join picking_list pl
                    ON         rol.picking_list_id = pl.picking_list_id
                    left join  picking_list_line pll
                    ON         pll.picking_list_id = pl.picking_list_id
                    WHERE      rol.all_quantity IN ( 'Y',
                                                    'N' )
                    AND        rol.ro_line_status_id =
                               (
                                      SELECT ro_line_status_id
                                      FROM   ro_line_status
                                      WHERE  code = 'PICKING')
                    AND        pll.reverse_order_line_id IS NULL)
GROUP BY rolid2,
         rolid3;
0
slightwv (䄆 Netminder) Commented:
That doesn't look correct...

You are updating one column but selecting four?
0
slightwv (䄆 Netminder) Commented:
There also isn't a where clause on the UIPDATE so it is updating every row with what is likely the same value?

I'm thinking this might be better as a MERGE statement?
0
bibi92Author Commented:
ok how can I do it?
Thanks
0
slightwv (䄆 Netminder) Commented:
I would start with the online documentation:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

There is an example there.

It might not work because you are updating the same table that you need to query to get the values but give it a try.
0
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.