compare one row with the next row

Hi,

I have a query like this:

SELECT LINE1,LINE2,LINE3,DATE_START,DATE_CLOSE,RESULT FROM TABLE1 order BY LINE1,DATE_START;

Open in new window


i need to add an additional column that have too posible results: valid, invalid.

The results depends the on a comparison between the actual line and the next line. For example: suppose that I have a row that the LINE2 value is CAR and the next row the LINE1 value is CAR. Then this additional column value will be valid, else is invalid. The comparison that i need is for each line compare any column value from this line to any column value on the next line, then evaluate and set the value of the additional column
LVL 1
joyacv2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
One option is Lead/TSQL or Lead/PSQL

My response is not complete because your question is incomplete also.

- Provide TSQL or PSQL code to create a test table. Identify version whether it is Oracle ot SQL Server you are using.
joyacv2Author Commented:
hi

I am using Oracle 10
Mike EghtebasDatabase and Application DeveloperCommented:
Sorry, I do not have Oracle to test it. And, I am not that good to post solution without testing it.
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

joyacv2Author Commented:
can you test in your database?
Mike EghtebasDatabase and Application DeveloperCommented:
I have sql server. And Lead is available in version 12 and up.  I can do with cte fto work with any other versins (without Lead) but still cte is for SQL Server only I guess.

I could try derived table (inline view). But you need to give tsql for test table with some sample data.

Mike
slightwv (䄆 Netminder) Commented:
LEAD and CTE should work the same in Oracle as it does with SQL Server.  At least it will be very close.

joyacv2,
Can you post some sample data and expected results so we can provide tested SQL?
joyacv2Author Commented:
sorry, i dont have any test data
Mike EghtebasDatabase and Application DeveloperCommented:
@slightwv,

Thank you for the info.

Mike
D_VanteCommented:
Read the first row into variables
Then step through the database one row at a time, updating the variables after each comparison

Select all records into temp table

Loop
  load values of row into variables
  goto next row Select statement
   comparison
   if like
        set value
   else
       do not set
end of Loop
Mike EghtebasDatabase and Application DeveloperCommented:
re:> sorry, i dont have any test data

Make some.
slightwv (䄆 Netminder) Commented:
>>Then step through the database one row at a time, updating the variables after each comparison

This likely isn't necessary.

>>sorry, i dont have any test data

Agreed.  Just make up a test case that has raw sample data and the expected results.

Given you sample SQL, here is what I threw together.  I doubt it is correct so please add data to it to show what it is you need.

drop table tab1 purge;
create table tab1( line1 varchar2(5), line2 varchar2(5), date_start date);

insert into tab1 values('BOB','CAR', to_date('01/01/2015','MM/DD/YYYY'));
insert into tab1 values('CAR','BOB', to_date('01/02/2015','MM/DD/YYYY'));
insert into tab1 values('BOB','CAR', to_date('01/03/2015','MM/DD/YYYY'));
commit;

select line1, line2, next_line1, case when line2=next_line1 then 'valid' else 'invalid' end check_it
from (
	select line1, line2, lead(line1) over(order by date_start) next_line1
	from tab1
)
/

Open in new window


The results from the test:
LINE1 LINE2 NEXT_ CHECK_I
----- ----- ----- -------
BOB   CAR   CAR   valid
CAR   BOB   BOB   valid
BOB   CAR         invalid

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joyacv2Author Commented:
perfect!!!! works excellent!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.