Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql anonymous block
pl/sql anonymous block [message #282117] Tue, 20 November 2007 23:15 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi I have cols as follows

I/P :

item loc order qtyrcv
------------------------------------
1002 210 0 230
1002 200 230 null
2001 102 0 908
2001 101 900 null

Required O/P is:

1002 210 0 0
1002 200 230 230
2001 102 0 0
2001 101 900 908

I need to update this records like this.
I wote a query like : select item,loc,order,qtyrcv,nvl(lag....) over (partition by ...)new_rcv
from( my Join conditon)

then I got o/p as
item loc order qtyrcv New_rcv
------------------------------------
1002 210 0 230 0
1002 200 230 null 230
2001 102 0 908 0
2001 101 900 null 908.
Now i need to update this result into the table.
Can any one pls help me for this issue.now I need to set the O/P as
Required O/P is:

1002 210 0 0
1002 200 230 230
2001 102 0 0
2001 101 900 908
Just shifting no any new col....

Thank you.

[Updated on: Tue, 20 November 2007 23:17]

Report message to a moderator

Re: pl/sql anonymous block [message #282157 is a reply to message #282117] Wed, 21 November 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why why people don't read OraFAQ Forum Guide, especially "How to format your post?" section BEFORE posting?
Why they don't post your Oracle version (4 decimals)?

Regards
Michel
Re: pl/sql anonymous block [message #282165 is a reply to message #282117] Wed, 21 November 2007 01:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi ,

Do you want us also to reply as follows ? Very Happy

Update new_tab
Set    new_col = (select new_rcv
                  From old_table .........)
Where Exists (Select Null From ..... )


Post more detail if you need better answers .

Thumbs Up
Rajuvan.

[Updated on: Wed, 21 November 2007 01:08]

Report message to a moderator

Re: pl/sql anonymous block [message #282201 is a reply to message #282157] Wed, 21 November 2007 03:01 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi Michel I have already mentioned it is Oracle 9i.

I wrote a query as

select item,loc,order,qtyrcv,nvl(lag(qtyrcv)
over (partition by item order by loc),0) new_rcv
from
( select a.item,......,a.qtyrcv,b.stat from tab1 a,tab2 b
where a.item=b.item and a.item in
(select item from tab1 where order=0 and order<>0)
and b.stat<>'C')

then the O/P is

item loc order qtyrcv newrcv
---------------------------------------

1002 201 0 230 0
1002 200 230 null 230
2001 101 0 908 0
2001 100 900 null 908

but required O/P is
item loc order qtyrcv
-----------------------------
1002 201 0 0
1002 200 230 230
2001 101 0 0
2001 100 900 908.

for this i need to write PL/SQL anonymous block.Can you Pls help me to write this.

Thank you.

[Updated on: Wed, 21 November 2007 03:23]

Report message to a moderator

Re: pl/sql anonymous block [message #282220 is a reply to message #282201] Wed, 21 November 2007 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
9i is not a version with 4 decimals.

And your post is still not formated.

Regards
Michel
Re: pl/sql anonymous block [message #282225 is a reply to message #282220] Wed, 21 November 2007 03:48 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi it is
9.2.0.4



Thank you.
Re: pl/sql anonymous block [message #282231 is a reply to message #282220] Wed, 21 November 2007 03:57 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

<code>

SQL> SELEct item,loc,qtyord,qtyrcv,
2 nvl(lag(qtyrcv) over (partition by item order by
3 loc),0) NEWRCV
4 FROM
5 (
6 SELECT A.item,a.loc,a.qtyord,a.qtyrcv,
7 b.date,b.status FROM odd a ,odds b WHERE
8 a.item=b.item and a.item in
9 (SELECT item from odd where qtyord=0 and qtyrcv<>0)
10 and b.stat<>'C'

<\code>

Thank you.

Re: pl/sql anonymous block [message #282235 is a reply to message #282117] Wed, 21 November 2007 04:02 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Please format your code some thing like this:

/* Formatted on 2007/11/21 15:31 (Formatter Plus v4.8.Cool * /
SELECT item, loc, qtyord, qtyrcv,
NVL (LAG (qtyrcv) OVER (PARTITION BY item ORDER BY loc), 0) newrcv
FROM (SELECT a.item, a.loc, a.qtyord, a.qtyrcv, b.DATE, b.status
FROM odd a, odds b
WHERE a.item = b.item
AND a.item IN (SELECT item
FROM odd
WHERE qtyord = 0 AND qtyrcv <> 0)
AND b.stat <> 'C')

[Updated on: Wed, 21 November 2007 04:10]

Report message to a moderator

Re: pl/sql anonymous block [message #282236 is a reply to message #282225] Wed, 21 November 2007 04:02 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

may be something like this :

select item,loc,qty_order,rev_qty_rcv from
(select item,loc,qty_order,
lag(qty_rcv) over (partition by item order by loc desc)rev_qty_rcv
from tab1)


PS:i have changed the name of your columns.

regards,
Re: pl/sql anonymous block [message #282237 is a reply to message #282117] Wed, 21 November 2007 04:03 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi ,

Why are you again trying to provoke Michel without reading the Forum Guide and without formatting your post ?

You didn't tell me which table you are trying to update .
Why are you using subquery only for applying NVL anf LAG Functionos ?
You didn't specifyu whether you are referring the Tab2 aywhere in the select Clause.


Following query may help you , assuming that you need to update on the Tab1
UPDATE TAB1 X1
SET    qtyrcv =  select V.new_qty
                 FROM TAB1 x ,
                     (SELECT lag(qtyrcv)over (partition by item order by loc) new_qty
                      FROM   TAB1 a,TAB2 b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C') v
                  WHERE X.item = V.item
                  AND   X.item =X1.item)
WHERE NOT EXISTS (select NULL
                 FROM TAB1 x ,
                     (SELECT lag(qtyrcv)over (partition by item order by loc) new_qty
                      FROM   TAB1 a,TAB2 b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C') v
                  WHERE X.item = V.item
                  AND   X.item =X1.item)


Thumbs Up
Rajuvan

[Updated on: Wed, 21 November 2007 04:04]

Report message to a moderator

Re: pl/sql anonymous block [message #282243 is a reply to message #282237] Wed, 21 November 2007 04:17 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

hi raju!
I want to update this result in Pl/sql .

code is:
DECLARE


CURSOR C1 IS

SELEct item,loc,qtyord,qtyrcv,
nvl(lag(qtyrcv) over (partition by item order by
loc),0) NEW_RECEIVED
FROM
(
SELECT A.item,a.loc,a.qtyord,a.qtyrcv,
b.date,b.status FROM odd a ,odds b WHERE
a.item=b.item and a.item in
(SELECT item from odd where qtyord=0 and qtyrcv<>0)
and b.status<>'C'
);
BEGIN

DBMS_OUTPUT.PUT_LINE('item'||' '||'LOC'||' '||'QTYORD'||
' '||'QTYRCV'||' '||'NEW_RECEIVED');

DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------');

FOR j IN C1

LOOP

DBMS_OUTPUT.PUT_LINE(j.item||' '||j.loc||' '||j.qtyord||
' '||j.qtyrcv||' '||j.new_received);
END LOOP;
END;


O/P is:

order_no location qty_ordered qty_received new_received
--------------------------------------------------------
1234 121 0 21 0
1234 120 21 null 21
3214 234 0 232 0
3214 232 210 null 210


Required O/P is: ( update the output in table ODD)

order_no location qty_ordered qty_received
-------------------------------------------------
1234 121 0 0
1234 120 21 21
3214 234 0 0
3214 232 210 232

[Updated on: Wed, 21 November 2007 04:17]

Report message to a moderator

Re: pl/sql anonymous block [message #282244 is a reply to message #282243] Wed, 21 November 2007 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: pl/sql anonymous block [message #282253 is a reply to message #282117] Wed, 21 November 2007 04:45 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


HI user71408,

I dont have any data provided by you.
I am just using some assumption as you are not telling clearly.
I haven't tested out code

Even now the question is not clear whether You need to just display the output or need to update the table ODD.

For display Just avoid j.qtyrcv fro dbms_output line.

For Update table , Try something like
UPDATE ODD X1
SET    qtyrcv =  select V.new_qty
                 FROM ODD x ,
                     (SELECT item,loc
                             lag(qtyrcv)over (partition by item order by loc) new_qty
                      FROM   ODD a,ODDS b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C') v
                  WHERE X.item = V.item
                  AND   X.loc = V.loc
                  AND   X.item =X1.item
                  AND   X.loc = X1.loc)
WHERE NOT EXISTS (select NULL
                 FROM ODD x ,
                     (SELECT item,loc
                             lag(qtyrcv)over (partition by item order by loc) new_qty
                      FROM   ODD a,ODDS b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C') v
                  WHERE X.item = V.item
                  AND   X.loc = V.loc
                  AND   X.item =X1.item
                  AND   X.loc = X1.loc)


This is with asumption that Item and loc are key idendifying the each row and this wond update the rows whih dont meet the Condition specified.

Thumbs Up
Rajuvan.
Re: pl/sql anonymous block [message #282255 is a reply to message #282253] Wed, 21 November 2007 04:49 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi New_Rcv is new COL . there is no such col in ODD table. But I want to update the result which one i got from this column should be updated in the col qty_rcv. So I need to update this col. It is in ODD table. means I need to update the table only.

and one more : NEW_RCV is not in ODD Table.( here u mention V.new_rcv from odd X .....)

Thank u.

[Updated on: Wed, 21 November 2007 04:52]

Report message to a moderator

Re: pl/sql anonymous block [message #282260 is a reply to message #282255] Wed, 21 November 2007 05:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

user71408 wrote on Wed, 21 November 2007 16:19

Hi New_Rcv is new COL . there is no such col in ODD table. But I want to update the result which one i got from this column should be updated in the col qty_rcv. So I need to update this col. It is in ODD table. means I need to update the table only.
Thank u.



Now TELL me what else my query is doing ? Have you tried to analyse/execute query?

user71408 wrote on Wed, 21 November 2007 16:19


and one more : NEW_RCV is not in ODD Table.( here u mention V.new_rcv from odd X .....)


I didn't take the NEW_RCV from ODD table ;rather it is taken from Inline view V.

Thumbs Down
Rajuvan

[Updated on: Wed, 21 November 2007 05:21]

Report message to a moderator

Re: pl/sql anonymous block [message #282274 is a reply to message #282260] Wed, 21 November 2007 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Now TELL me what else my query is doing ? Have you tried to analyse/execute query?

Are you asking someone to think? ./fa/1600/0/

Regards
Michel
Re: pl/sql anonymous block [message #282279 is a reply to message #282253] Wed, 21 November 2007 06:35 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi Raju,
Here i am getting the error at 2nd line as "Missing expression"

Thank u
Re: pl/sql anonymous block [message #282281 is a reply to message #282117] Wed, 21 November 2007 06:38 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Really I am Laughing


Thumbs Up
Rajuvan

Re: pl/sql anonymous block [message #282288 is a reply to message #282117] Wed, 21 November 2007 06:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Oops.. Missed a Bracket

UPDATE ODD X1
SET    qtyrcv =  (select V.new_qty
                 FROM ODD x ,
                     (SELECT item,loc
                             lag(qtyrcv)over (partition by item order by loc) new_qty
                      FROM   ODD a,ODDS b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C') v
                  WHERE X.item = V.item
                  AND   X.loc = V.loc
                  AND   X.item =X1.item
                  AND   X.loc = X1.loc)
WHERE NOT EXISTS (select NULL
                 FROM ODD x ,
                     (SELECT item,loc
                             lag(qtyrcv)over (partition by item order by loc) new_qty
                      FROM   ODD a,ODDS b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C') v
                  WHERE X.item = V.item
                  AND   X.loc = V.loc
                  AND   X.item =X1.item
                  AND   X.loc = X1.loc)


Thumbs Up
Rajuvan
Re: pl/sql anonymous block [message #282456 is a reply to message #282288] Thu, 22 November 2007 00:59 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi Raju,
It shows Column ambig.. defined. Mnay Times I do some
modifications enen though I am getting same error.I observe
paranthesis also.

Thank u.
Re: pl/sql anonymous block [message #282460 is a reply to message #282117] Thu, 22 November 2007 01:07 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Hi Rajuvan,

SELECT item,loc
lag(qtyrcv)over (partition by item order by loc) new_qty
FROM ODD a,ODDS b
WHERE a.item=b.item
AND a.item in


Here you should use a.item or b.item while selecting as both ODD and ODDS have column item.

Regards,
Kiran

[Updated on: Thu, 22 November 2007 01:08]

Report message to a moderator

Re: pl/sql anonymous block [message #282461 is a reply to message #282117] Thu, 22 November 2007 01:09 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Just try to check where exactly the Ambuguity by spotting the line number . You have to do it yourself as we dont have any data or tables .

Thumbs Up
Rajuvan.
Re: pl/sql anonymous block [message #282479 is a reply to message #282461] Thu, 22 November 2007 02:13 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

it shows the ambig... in (lag(qtyrcv)... item order by loc...)

at item position.

Thank you.
Re: pl/sql anonymous block [message #282482 is a reply to message #282117] Thu, 22 November 2007 02:23 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Thanx Kiran ,

Try to referece for a and b for the Inner subquery as below .



UPDATE ODD X1
SET    qtyrcv =  (select V.new_qty
                 FROM ODD x ,
                     (SELECT a.item,a.loc
                             lag(a.qtyrcv)over (partition by a.item order by a.loc) new_qty
                      FROM   ODD a,ODDS b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C') v
                  WHERE X.item = V.item
                  AND   X.loc = V.loc
                  AND   X.item =X1.item
                  AND   X.loc = X1.loc)
WHERE NOT EXISTS (select NULL
                 FROM ODD x ,
                     (SELECT a.item,a.loc
                             lag(a.qtyrcv)over (partition by a.item order by a.loc) new_qty
                      FROM   ODD a,ODDS b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C') v
                  WHERE X.item = V.item
                  AND   X.loc = V.loc
                  AND   X.item =X1.item
                  AND   X.loc = X1.loc)


Thumbs Up
Rajuvan.
Re: pl/sql anonymous block [message #282483 is a reply to message #282461] Thu, 22 November 2007 02:32 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

using old code,
I am getting the following error...

ERROR at line 1:
ORA-20090: U R NOT ALLOWED
ORA-06512: at "USEY01.TRIGGS", line 3
ORA-04088: error during execution of trigger 'USEY01.TRIGGS'

[Updated on: Thu, 22 November 2007 02:33]

Report message to a moderator

Re: pl/sql anonymous block [message #282487 is a reply to message #282483] Thu, 22 November 2007 02:38 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

ORA-20090: U R NOT ALLOWED


Then you obviously have some hacked messenger speak copy of Oracle, you should instead install the genuine version. Razz
Re: pl/sql anonymous block [message #282489 is a reply to message #282117] Thu, 22 November 2007 02:39 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Thats what i also thinking Smile
Re: pl/sql anonymous block [message #282490 is a reply to message #282489] Thu, 22 November 2007 02:40 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I want that version too! That will surely improve my knowledge of IM speak and thus enable me to understand more of the cryptic messages on this forum Laughing
Re: pl/sql anonymous block [message #282493 is a reply to message #282482] Thu, 22 November 2007 02:46 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Raju,

When referencing the variable with a.qtyrcv,a.item.. in lag function I am getting error like


ORA-00904: "OL"."LOC": invalid identifier for all those referenced cols.
Re: pl/sql anonymous block [message #282494 is a reply to message #282117] Thu, 22 November 2007 02:48 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Then you should read about this also.

http://tkyte.blogspot.com/2006/01/im-speak.html

Kiran.
Re: pl/sql anonymous block [message #282495 is a reply to message #282117] Thu, 22 November 2007 02:51 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Then you might not having the column LOC in your table. Check it.


Kiran
Re: pl/sql anonymous block [message #282496 is a reply to message #282117] Thu, 22 November 2007 03:00 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Alas...

Atlast it tries to hit the DB.

Crosscheck what the trigger is doing on that table .
Also try to excute V inline view to ensure that it is working fine

SELECT a.item,a.loc
                             lag(a.qtyrcv)over (partition by a.item order by a.loc) new_qty
                      FROM   ODD a,ODDS b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C'


Thumbs Up
Rajuvan
Re: pl/sql anonymous block [message #282498 is a reply to message #282117] Thu, 22 November 2007 03:04 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And I am not sure whether ODDS is having column LOC as you are not ready to disclose the table structure.

If not just remove LOC fiel from yuor query that would probably result in Query returning more thant One row

Thumbs Up
Rajuvan
Re: pl/sql anonymous block [message #282529 is a reply to message #282498] Thu, 22 November 2007 04:39 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

it's working . Then How to put this one in pl/sql block?

Re: pl/sql anonymous block [message #282553 is a reply to message #282498] Thu, 22 November 2007 05:56 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,
After executing this query i am getting O/P like this:

ITEM LOC ORDERS QTYRCV
---------- ---------- ---------- ----------
1002 201 0
1002 200 230
2001 101 0
2001 100 900

but previously the table is like this

ITEM LOC ORDERS QTYRCV
---------- ---------- ---------- ----------
1002 201 0 230
1002 200 230
2001 101 0 908
2001 100 900


But Required is

ITEM LOC ORDERS QTYRCV
---------- ---------- ---------- ----------
1002 201 0 o
1002 200 230 230
2001 101 0 0
2001 100 900 908

Thank u.
Re: pl/sql anonymous block [message #282557 is a reply to message #282117] Thu, 22 November 2007 06:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

That simply means that your subquery conditionsa re not satisfied . so it is Updated to NULL.

How did u update it ? ATLEAST Show the result for

SELECT a.item,a.loc
                             lag(a.qtyrcv)over (partition by a.item order by a.loc) new_qty
                      FROM   ODD a,ODDS b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C'


Thumbs Up
Rajuvan.
Re: pl/sql anonymous block [message #282656 is a reply to message #282557] Thu, 22 November 2007 22:19 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi The O/P of this query is



ITEM LOC NEW_QTY

1002 200
1002 200
1002 201
1002 201 230
2001 100
2001 100
2001 101
2001 101 908


Thank you.
Re: pl/sql anonymous block [message #282661 is a reply to message #282117] Thu, 22 November 2007 22:43 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


What are the o/p for

SELEct item,loc,qtyord,qtyrcv,
nvl(lag(qtyrcv) over (partition by item order by 
loc),0) NEW_RECEIVED
FROM
(
SELECT A.item,a.loc,a.qtyord,a.qtyrcv,
b.date,b.status FROM odd a ,odds b WHERE 
a.item=b.item and a.item in
(SELECT item from odd where qtyord=0 and qtyrcv<>0)
and b.status<>'C'
)


AND

SELECT a.item,a.loc,a.qtyrcv,
                      NVL(lag(a.qtyrcv)over (partition by a.item order by a.loc),0) new_qty
                      FROM   ODD a,ODDS b
                      WHERE  a.item=b.item 
                      AND    a.item in
                     (select item from tab1 where order=0 and order<>0)
                      and b.stat<>'C'


Plus Create and Insert scripts for ODD and ODDS tables also .

Thumbs Up
Rajuvan
Re: pl/sql anonymous block [message #282666 is a reply to message #282661] Thu, 22 November 2007 23:17 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi these r the O/P :

1. ITEM LOC ORDERS QTYRCV NEW_RECEIVED

1002 200 230 0
1002 200 230 0
1002 201 0 230 0
1002 201 0 230 230
2001 100 900 0
2001 100 900 0
2001 101 0 908 0
2001 101 0 908 908

2. ITEM LOC QTYRCV ORDERS NEW_QTY

1002 200 230 0
1002 200 230 0
1002 201 230 0 0
1002 201 230 0 230
2001 100 900 0
2001 100 900 0
2001 101 908 0 0
2001 101 908 0 908

Select * from odd
O/P
ITEM LOC ORDERS QTYRCV

1002 201 0 230
1002 200 230
2001 101 0 908
2001 100 900

Select * from odds

NOT_AFTER STAT LOC ITEM

11/06/83 A 201 1002
23/08/83 A 200 1002
09/04/83 A 101 2001
12/12/82 A 100 2001


Re: pl/sql anonymous block [message #282682 is a reply to message #282117] Fri, 23 November 2007 00:17 Go to previous messageGo to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try this ...

SQL> select * from odd;

      ITEM        LOC     ORDERS     QTYRCV
---------- ---------- ---------- ----------
      2001        101          0        908
      1002        201          0        230
      1002        200        230
      2001        100        900

SQL> select * from odds;

NOT_AFTER S        LOC       ITEM
--------- - ---------- ----------
23-AUG-83 A        200       1002
11-JUN-83 A        201       1002
09-APR-83 A        101       2001
12-DEC-82 A        100       2001

SQL> UPDATE ODD X1
  2  SET    qtyrcv =  (select V.new_qty
  3                   FROM ODD x ,
  4                       (SELECT a.item,a.loc,a.qtyrcv,
  5                        NVL(lead(a.qtyrcv)over (partition by a.item order
a.loc),0) new_qty
  6                        FROM   ODD a,ODDS b
  7                        WHERE  a.item=b.item
  8                                       AND A.LOC=B.LOC
  9                        AND    a.item in
 10                       (select item from odd where orders=0 and qtyrcv  <
 11                        and b.stat<>'C') v
 12                    WHERE X.item = V.item
 13                    AND   X.loc = V.loc
 14                    AND   X.item =X1.item
 15                    AND   X.loc = X1.loc)
 16  WHERE  EXISTS (select NULL
 17                   FROM ODD x ,
 18                       (SELECT a.item,a.loc,a.qtyrcv,
 19                        NVL(lead(a.qtyrcv)over (partition by a.item order
a.loc),0) new_qty
 20                        FROM   ODD a,ODDS b
 21                        WHERE  a.item=b.item
 22                                       AND A.LOC=B.LOC
 23                        AND    a.item in
 24                       (select item from odd where orders=0 and qtyrcv  <
 25                        and b.stat<>'C') v
 26                    WHERE X.item = V.item
 27                    AND   X.loc = V.loc
 28                    AND   X.item =X1.item
 29                    AND   X.loc = X1.loc)
 30  /

4 rows updated.

SQL> commit;

Commit complete.

SQL> select * from odd;

      ITEM        LOC     ORDERS     QTYRCV
---------- ---------- ---------- ----------
      2001        101          0          0
      1002        201          0          0
      1002        200        230        230
      2001        100        900        908

SQL>


This could have been achieved long way back if you provided the create and insert scripts and obeyed the Forum rulse as suggested by Michel.

Give some sort of respect to seniors like Michel .

Thumbs Up
Rajuvan
Previous Topic: Date Calculation Excluding Weekends
Next Topic: How to convert row col to row ?
Goto Forum:
  


Current Time: Tue Dec 06 10:44:45 CST 2016

Total time taken to generate the page: 0.14328 seconds