Home » SQL & PL/SQL » SQL & PL/SQL » LAG error (10g, 8i, win 8.1)
LAG error [message #654011] Mon, 25 July 2016 06:13 Go to next message
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 Go to previous messageGo to next message
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:

nvl (lag (bal),1,0)

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 Go to previous messageGo to next message
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 #654015 is a reply to message #654014] Mon, 25 July 2016 06:50 Go to previous messageGo to next message
rzkhan
Messages: 370
Registered: March 2005
Senior Member
Actually I need to access previous value from BAL column for the ITEMID.
Re: LAG error [message #654016 is a reply to message #654014] Mon, 25 July 2016 06:51 Go to previous messageGo to next message
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
What's wrong with the above?
Re: LAG error [message #654017 is a reply to message #654015] Mon, 25 July 2016 06:52 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
rzkhan wrote on Mon, 25 July 2016 12:50
Actually 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3079
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 #654022 is a reply to message #654019] Mon, 25 July 2016 07:23 Go to previous messageGo to next message
rzkhan
Messages: 370
Registered: March 2005
Senior Member
Thanks a Lot...
Re: LAG error [message #654023 is a reply to message #654022] Mon, 25 July 2016 07:35 Go to previous message
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

Previous Topic: Break a > 300k CLOB string into 32k chunks
Next Topic: How can I load 300k of data into a CLOB
Goto Forum:
  


Current Time: Fri Sep 17 10:21:48 CDT 2021