SQL Query - Lag function

[vijayr]

Hi All,

I need to select the previous and next record, when the value of column is changed for that record.

I tried with lag function, but I couldn’t succeed. I’m using oracle 8.1.6 Version.

My table structure is as follows:

Account_No number(10)
Payment_type number(5)
Installment_Type number(5)
Date_chage date

Sample record:

Account_No Payment_Type Installment_Type Date_change
70539 1 2 01-OCT-83
70539 1 2 03-FEB-01
70539 1 2 26-APR-02
70539 1 1 21-JUN-02
70539 1 2 12-JUL-02
185562 1 2 23-APR-02
185562 2 2 10-MAY-02

My output should be like this:

Account_No Payment_Type Installment_Type Date_change
70539 1 2 26-APR-02
70539 1 1 21-JUN-02
70539 1 2 12-JUL-02
185562 1 2 23-APR-02
185562 2 2 10-MAY-02

Can anyone help me to achieve this?

Thanks and regards,
Vijay R.

[stecal]

That would be the previous and next record pertaining to an account? Or simply the previous and next record as they appear in the table? And if there is no previous or next (because you're at the first or last record)? Change in value of which column - installment_type? Or payment_type? Both?

[vijayr]

Yes Stecal. You are right. The previous and next record should be pertaining to an account.

If Either of Installment_type or Payment_type column is changed i want the records.

[stecal]

You'll need some placeholders or temporary storage variables to hold the first record. You can use subscript i and j to reflect current and new record being compared to current record, or i and i+1, or assign record i to record_temp, etc. Read the next record. If payment_type(i) <> payment_type(j) OR installment_type(i) <> installment_type(j) then output record(j) because there has been a change. Assign the current record (j) to the placeholder (you can use i or some other variable name). Read the next record. If last record (which is a test you need to include at the beginning where you read in the first record), then exit. Else, read in next record and start (loop) the comparison again.

This process is exactly like the master record test found in programming logic books (typical examples have to do with banking records - read an account in, were there any debits or credits - if so, output the new balance - read the next transaction - is this the same account/customer - if so, out put the new balance - if not, read in the next account number - etc.). You'll need to use PL/SQL (cursor, loop, declaration of variables). Is this enough to get you started?

[vijayr]

Hi Stecal,

Thanks for your help. We do have PL/SQL program and its working fine.

Actually we are trying to do this by SQL query itself using Rank () over and Lag functions.

Appreciate your help on this regard.