Home » SQL & PL/SQL » SQL & PL/SQL » LAG error (10g, 8i, win 8.1)
| LAG error [message #654011] |
Mon, 25 July 2016 06:13  |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Here is my query.
SAM@orcl:>ed
Wrote file afiedt.buf
select itemid
, transaction_date
, nvl(purchased,0) as purchased
, nvl(issued,0) as issued
, sum(nvl(purchased,0)-nvl(issued,0)) over (partition by itemid
order by transaction_date) bal
from (select p.the_date as transaction_date
,p.itemid,purchase_qty as purchased
,NULL as issued
from raw_purchase_qty p
UNION ALL
select s.the_date as transaction_date
,s.itemid
,NULL as purchased
,s.issue_qty as issued
from raw_issue_qty s
)
order by itemid, transaction_date
SAM@orcl:>/
ITEMID TRANSACTI PURCHASED ISSUED BAL
---------- --------- ---------- ---------- ----------
3 22-JUN-16 250 0 250
3 23-JUN-16 5525 0 5775
3 20-JUL-16 500 0 6275
3 22-JUL-16 0 25 6250
8 20-JUL-16 25 0 25
9 20-JUL-16 0 0 0
13 20-JUL-16 100 0 100
14 20-JUL-16 200 0 200
15 20-JUL-16 100 0 100
16 20-JUL-16 400 0 400
16 25-JUL-16 0 400 0
11 rows selected.
I wanted to ADD opening column in this query as below.
ITEMID TRANSACTI openning PURCHASED ISSUED BAL
---------- --------- ----------- ---------- ----------
3 22-JUN-16 0 250 0 250
3 23-JUN-16 250 5525 0 5775
3 20-JUL-16 5775 500 0 6275
3 22-JUL-16 6275 0 25 6250
8 20-JUL-16 0 25 0 25
9 20-JUL-16 0 0 0 0
13 20-JUL-16 0 100 0 100
14 20-JUL-16 0 200 0 200
15 20-JUL-16 0 100 0 100
16 20-JUL-16 0 400 0 400
16 25-JUL-16 400 0 400 0
11 rows selected.
But it throws the following error.
select itemid
, transaction_date
, nvl (lag (bal),1,0) over (partition by itemid
order by transactiondate, 0) opening
, nvl(purchased,0) as purchased
, nvl(issued,0) as issued
, sum(nvl(purchased,0)-nvl(issued,0)) over(partition by itemid
order by transaction_date) bal
from (select p.the_date as transaction_date
, p.itemid
, purchase_qty as purchased
, NULL as issued
from raw_purchase_qty p
UNION ALL
select s.the_date as transaction_date
, s.itemid
, NULL as purchased
, s.issue_qty as issued
from raw_issue_qty s
)
order by itemid, transaction_date
SAM@orcl:>
SAM@orcl:>/
nvl (lag (bal),1,0) over (partition by itemid order by transactiondate, 0) opening,
*
ERROR at line 2:
ORA-30484: missing window specification for this function
Please help in this regards.
RzKhan
[/code]
[Updated on: Mon, 25 July 2016 06:36] by Moderator Report message to a moderator |
|
|
|
| Re: LAG error [message #654013 is a reply to message #654011] |
Mon, 25 July 2016 06:40  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Thanks for trying by using code tags, however, try splitting it up and not including the entire message in code tags. Also, please format your code so that it is more readable (in future, I've done it for you this time)
The issue is here:
1. How many arguments does the NVL function take? What exactly is nvl(x, 1, 0) meant to do?
2. You can't wrap the nvl around the lag 'part' of the function, it has to be around the entire
function
|
|
|
|
| Re: LAG error [message #654014 is a reply to message #654011] |
Mon, 25 July 2016 06:47 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Without NVL I tried but still error...
Wrote file afiedt.buf
1 select itemid, transaction_date,
2 lag ((bal),1,0) over (partition by itemid order by transaction_date) opening,
3 nvl(purchased,0) as purchased ,nvl(issued,0) as issued,
4 sum(nvl(purchased,0)-nvl(issued,0)) over
5 (partition by itemid order by transaction_date) bal
6 from (
7 select p.the_date as transaction_date,p.itemid,purchase_qty as purchased,NULL as issued
8 from raw_purchase_qty p
9 UNION ALL
10 select s.the_date as transaction_date,s.itemid,NULL as purchased,s.issue_qty as issued
11 from raw_issue_qty s
12 )
13* order by itemid, transaction_date
SAM@orcl:>/
lag ((bal),1,0) over (partition by itemid order by transaction_date) opening,
*
ERROR at line 2:
ORA-00904: "BAL": invalid identifier
|
|
|
|
|
|
| Re: LAG error [message #654016 is a reply to message #654014] |
Mon, 25 July 2016 06:51 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Tell you what, as I asked nicely for you to format your code in my previous post, and even formatted your previous code for you, how about you format that code there for us?
Notice that you have a different error this time?
It says that it doesn't recognise 'Bal'
Look at your code and see if you can see why it doesn't.
I'll drop a big hint:
select a
, b
, a+b calc
, calc/100
from tab
|
|
|
|
| Re: LAG error [message #654017 is a reply to message #654015] |
Mon, 25 July 2016 06:52 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
rzkhan wrote on Mon, 25 July 2016 12:50Actually I need to access previous value from BAL column for the ITEMID.
Provide a test case please.
|
|
|
|
| Re: LAG error [message #654019 is a reply to message #654014] |
Mon, 25 July 2016 06:56 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You can't reference select list expression alias in same select list. That's why Oracle complains about bal. Use:
with t1 as (
select p.the_date as transaction_date,
p.itemid,purchase_qty as purchased,
NULL as issued
from raw_purchase_qty p
union all
select s.the_date as transaction_date,
s.itemid,
NULL as purchased,
s.issue_qty as issued
from raw_issue_qty s
),
t2 as (
select itemid,
transaction_date,
nvl(purchased,0) as purchased,
nvl(issued,0) as issued,
sum(nvl(purchased,0)-nvl(issued,0)) over(partition by itemid order by transaction_date) bal
from t1
)
select itemid,
transaction_date,
lag(bal,1,0) over(partition by itemid order by transaction_date) opening,
purchased,
issued,
bal
from t2
order by itemid,
transaction_date
/
SY.
|
|
|
|
|
|
| Re: LAG error [message #654023 is a reply to message #654022] |
Mon, 25 July 2016 07:35 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
I created another view
create view raw_purchase_issue as
select
itemid,
transaction_date,
nvl(purchased,0) as purchased ,nvl(issued,0) as issued,
sum(nvl(purchased,0)-nvl(issued,0)) over
(partition by itemid order by transaction_date) bal
from (
select p.the_date as transaction_date,p.itemid,purchase_qty as purchased,NULL as issued
from raw_purchase_qty p
UNION ALL
select s.the_date as transaction_date,s.itemid,NULL as purchased,s.issue_qty as issued
from raw_issue_qty s
)
order by itemid, transaction_date
/
and based on this view I used the select statement.
select itemid,
transaction_date,
lag(bal,1,0) over(partition by itemID order by transaction_date) as opening,
purchased,
issued,
bal
from raw_purchase_issue
/
so the result is as required.
SAM@orcl:>/
ITEMID TRANSACTI OPENING PURCHASED ISSUED BAL
---------- --------- ---------- ---------- ---------- ----------
3 22-JUN-16 0 250 0 250
3 23-JUN-16 250 5525 0 5775
3 20-JUL-16 5775 500 0 6275
3 22-JUL-16 6275 0 25 6250
8 20-JUL-16 0 25 0 25
9 20-JUL-16 0 0 0 0
13 20-JUL-16 0 100 0 100
14 20-JUL-16 0 200 0 200
15 20-JUL-16 0 100 0 100
16 20-JUL-16 0 400 0 400
16 25-JUL-16 400 0 400 0
11 rows selected.
Thanks for all your support and guidance as always...
rzkhan
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 07:53:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
