Home » SQL & PL/SQL » SQL & PL/SQL » how to get the column value of previous row to current row? (merged 3)
how to get the column value of previous row to current row? (merged 3) [message #393524] Mon, 23 March 2009 04:04 Go to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
Hi All,
I am facing a critical problem in SQL query (for reporting purpose (COGNOS)). please reply to my query.

REQUIREMENT: i want to retrive value of a column (this is not a table column, this will be calculated based expression) from previous row to current row.

EXAMPLE:
TABLE NAME: i have to join multiple tables. so, i am not mentioning table names here.

DISPLAY COLUMNS in the report: item, loc, sku, beginval (not table column), endval (not table column, this will calculated based on some expressions), etc. Here, first time the value of BEGINVAL will be taken from some x column name of x table. ENDVAL will calculated based on the expression. For the next row onwards, BEGINVAL will become the value of ENDVAL and the value of ENDVAL will be calculated based on the expression and this expression uses the value of BEGINVAL.

my report will look like below.

ITEM LOC SKU BEGINVAL ENDVAL
1 HYD 1-HYD 10 10+1+2 (13)
2 HYD 2-HYD 13 13+1+2 (16)
3 SEC 3-SEC 16 16+1+2 (19)
4 SEC 4-SEC 19 19+1+2 (22)

etc....

in the above output, BEGINVAL and ENDVAL columns are not part of any table. they are alias column names.if you observe intially BEGINVAL will be assigned to some value, but from subsequent rows, BEGINVAL will become the ENDVAL of previous row.

Please help me on how to write this query?
how to get the column value of previous row to current row? [message #393527 is a reply to message #393524] Mon, 23 March 2009 04:10 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
Hi All,
I am facing a critical problem in SQL query (for reporting purpose (COGNOS)). please reply to my query.

REQUIREMENT: i want to retrive value of a column (this is not a table column, this will be calculated based expression) from previous row to current row.

EXAMPLE:
TABLE NAME: i have to join multiple tables. so, i am not mentioning table names here.

DISPLAY COLUMNS in the report: item, loc, sku, beginval (not table column), endval (not table column, this will calculated based on some expressions), etc. Here, first time the value of BEGINVAL will be taken from some x column name of x table. ENDVAL will calculated based on the expression. For the next row onwards, BEGINVAL will become the value of ENDVAL and the value of ENDVAL will be calculated based on the expression and this expression uses the value of BEGINVAL.

my report will look like below. i have also atached report output with this message. please have a look at if allignment is not proper.

ITEM LOC SKU BEGINVAL ENDVAL
1 HYD 1-HYD 10 10+1+2 (13)
2 HYD 2-HYD 13 13+1+2 (16)
3 SEC 3-SEC 16 16+1+2 (19)
4 SEC 4-SEC 19 19+1+2 (22)


etc....

in the above output, BEGINVAL and ENDVAL columns are not part of any table. they are alias column names.if you observe intially BEGINVAL will be assigned to some value, but from subsequent rows, BEGINVAL will become the ENDVAL of previous row.

Please help me on how to write this query?
Re: how to get the column value of previous row to current row? [message #393529 is a reply to message #393527] Mon, 23 March 2009 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Have a look at LAG function.

Regards
Michel
Re: how to get the column value of previous row to current row? [message #393534 is a reply to message #393524] Mon, 23 March 2009 04:21 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@muralimadhavuni,

Please read up on LEAD and LAG in Analytic functions by Example. I have no experience in COGNOS as such. So can't tell if these might help. My understanding/misunderstanding is that reporting tools play up on the cube data that they get from the database. So you have to use either of the above functions before the data gets populated into the cube.

Another useful link might be Getting Cumulative Sum Using Oracle Analytical Functions

Hope these helps,

Regards,
Jo
Re: how to get the column value of previous row to current row? [message #393541 is a reply to message #393534] Mon, 23 March 2009 05:26 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
Thanks for timely response. but, still i didn't get the answer for my query.

while calculating the ENDVAL alias column, i have to use the BEGINVAL aliascolumn. Oracle is not supporting me to use the column alias in expressions.
please suggest me.
Re: how to get the column value of previous row to current row? [message #393543 is a reply to message #393541] Mon, 23 March 2009 05:30 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@muralimadhavuni,

Post your SQL Plus session along with your query . And don't forget the Test Case(DDL and DML statements) which will help us to test your queries.

Regards,
Jo
Re: how to get the column value of previous row to current row? (merged) [message #393554 is a reply to message #393524] Mon, 23 March 2009 06:10 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
Hi Jo,
please find attached the complete details of the problom which you have asked. i am attching a file, because the content is huge.

thaks for your timely response.
Re: how to get the column value of previous row to current row? (merged) [message #393568 is a reply to message #393554] Mon, 23 March 2009 07:15 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@muralimadhavuni,

Sorry. I can't download the file. Thats my network security settings. Many of us can't download any attachments. Anyways by your previous reply I understand you are trying to alias an expression and use the aliased name in the same level of SELECT Statement.

SQL> SELECT     LEVEL col_1,
  2             LAG (col_1, 1, 0) OVER (ORDER BY col_1 ASC NULLS LAST) begval
  3        FROM DUAL
  4  CONNECT BY LEVEL <= 10;
           LAG (col_1, 1, 0) OVER (ORDER BY col_1 ASC NULLS LAST) begval
                                            *
ERROR at line 2:
ORA-00904: "COL_1": invalid identifier


SQL> SELECT LAG (col_1, 1, 0) OVER (ORDER BY col_1 ASC NULLS LAST) begval
  2    FROM (SELECT     LEVEL col_1
  3                FROM DUAL
  4          CONNECT BY LEVEL <= 10);

    BEGVAL
----------
         0
         1
         2
         3
         4
         5
         6
         7
         8
         9

10 rows selected.

SQL> 


Hope this helps.

Regards,
Jo
need help in complex query - previous row to current row [message #393736 is a reply to message #393524] Tue, 24 March 2009 04:10 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
Requirement: i want to display the column value of previous row to the column value of current row.

-- create table script
create table leadlag
(
 item varchar2(10),
 loc varchar2(10),
 Onhand number(10)
);

--DML scripts to insert data into table
insert into leadlag values ('pepsi','HYD',10);

insert into leadlag values ('limca','SEC',20);

insert into leadlag values ('cocacola','AMP',30);

--below is the query i have written
SELECT   LAG (endbal, 1, 0) OVER (ORDER BY endbal) beginbal, endbal
  FROM   (SELECT   beginbal, beginbal - endbal endbal
            FROM   (SELECT   LAG (endbal, 1, 0) OVER (ORDER BY endbal)
                                beginbal,
                             endbal
                      FROM   (SELECT   onhand,
                                       onhand beginbal,
                                       (onhand - 1) endbal
                                FROM   leadlag)));


--below is the output of the above query.
  BEGINBAL     ENDBAL
---------- ----------
         0        -10
       -10        -10
       -10         -9


Problem description: beginbal and endbal are the two alias columns which are derived based on the expression.
In the query output, for the first row, the value of BEGINBAL should be the value of column onhand, which is 10.
Then for the subsequent rows, the value of beginbal should be the value of endval of previous row.
the output which i am looking is something like below.

  BEGINBAL     ENDBAL
---------- ----------
        10        -10
       -10        -10
       -10         -9


please help me on how to get the above output. appreciate for the timely response.

[Added code tags]

[Updated on: Tue, 24 March 2009 04:18] by Moderator

Report message to a moderator

Re: need help in complex query - previous row to current row [message #393739 is a reply to message #393736] Tue, 24 March 2009 04:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You've told us how to calculate BEGINBAL.

you need to tell us how to calculate ENDBAL.
Re: need help in complex query - previous row to current row [message #393740 is a reply to message #393736] Tue, 24 March 2009 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to start a new topic to continue your previous question.

Regards
Michel
Re: need help in complex query - previous row to current row [message #393743 is a reply to message #393739] Tue, 24 March 2009 04:28 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
ENDBAL = BEGINBAL- (onhand-1).

to be more precise on beginbal and endbal,

beginbal = lag(endbal)
note: first row of this value would be onhand column value(in our example, it is 10). And for the subsequent rows, endbal value of previous row.


endbal = beginbal-(some expression here)
Re: need help in complex query - previous row to current row [message #393747 is a reply to message #393743] Tue, 24 March 2009 04:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I've answered you at the Oracle forums.

SELECT onhand
     , NVL( LAG (onhand-1, 2, 0) OVER (ORDER BY onhand) - LAG(onhand-1) OVER (ORDER BY onhand) 
          , onhand
          ) beginbal
     , LAG (onhand-1, 1, 0) OVER (ORDER BY onhand) - (onhand-1) endbal
FROM   leadlag
Does this help?

MHE
Re: how to get the column value of previous row to current row? (merged 3) [message #393757 is a reply to message #393524] Tue, 24 March 2009 05:05 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
i understood the above query. But, my actual requirement is below query. I have given above query just as an example where in we are using onhand every where.
But, in below actual query case, the scenario is different. Please check the below query.
both beginbal and endbal column value should be calculated dynamically. i mean they are the alias column.

SELECT beginbal,endbal
FROM item i,
skuprojstatic sp,
sku s,
VehicleLoad vl,
VehicleLoadLine vll
WHERE sp.item = i.item
AND s.item = i.item
AND sp.item = s.item(+)
AND sp.loc = s.loc(+)
AND vll.item = s.item
AND vll.loadid = vl.loadid
AND TO_CHAR (sp.startdate, 'mm/dd/yyyy') =
TO_CHAR (vl.arrivdate, 'mm/dd/yyyy')
AND sp.loc = vl.dest(+)
ORDER BY sp.startdate;



BeginBal
1. Beginning Balance for the first period is the starting on hand.
Moving forward, it is equal to the ending balance of the previous day.
2. For the first period, BeginBal = SKU.OH
For the following periods, BeginBal = EndBal from previous period.

EndBal
1. Ending Balance only considers customer orders, outcomming commited intransits and incomming total intransits.
2. EndBal = BeginBal - (sp.FCSTCUSTORDERS - sp.CommitIntransitOut + sp.TotIntransitIn)

I Hope you understand my query. please let me if you need more information.
Re: how to get the column value of previous row to current row? (merged 3) [message #393762 is a reply to message #393757] Tue, 24 March 2009 05:22 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 23 March 2009 10:13
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Have a look at LAG function.

Regards
Michel


Previous Topic: find a string in atble
Next Topic: Grant References to Role ?
Goto Forum:
  


Current Time: Sat Dec 10 09:08:11 CST 2016

Total time taken to generate the page: 0.14511 seconds