Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), so that the people who want to help you can re-create the problem and test their ideas.
Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results will be the contents of the changed table when everything is finished.
Always say which version of Oracle you're using (for example, 220.127.116.11.0).
See the forum FAQ: Re: 2. How do I ask a question on the forums?
Currently I'm testing a partcular col which is loaded with the help of etl and I need to compare that my sql query.
now my issue is the logic whihc I should test is that:
cola - Set to the colb of the previous row in this table for the given id. This would be date - 1.
My question is how to incorporate this in my select query:
select query to assign a cola's prevoius month last date value to colb
Your question is confusing. A 'select query' can NOT 'assign' a value - it can only query a value.
If you want to query a previous rows value you can use the LAG function. See this Oracle-base example
LAGfunction is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the
ORDER BYof the
LAGfunction is used to order the data by salary.
That example orders the data by salary - you would order yours by date.
The SQL Language doc has the full syntax.
Frank is an expert on LAG and the other analytical functions so if you provide the info he ask for he will likely write a sample query for you.
You can search the forum for his user id and analytical function and you will find hundreds, if not thousands, of queries he has provided.