Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to get most recent consecutive records with the same value

725150Nov 22 2010 — edited Dec 3 2010
Hi,

Can someone please help me to solve my problem?

Below is my table

Prod_Code-----Purchase_date---Inv_number-------Inv_amt
MS_AV16850------18/01/2005-----------6575947----------------7.93
MS_AV16850------22/07/2005-----------7034012----------------51.82
MS_AV16850------04/01/2006-----------8719618----------------51.82
MS_AV16850------20/06/2006-----------9515864----------------104.69
MS_AV16850------16/04/2007-----------10353759----------------189.29
MS_AV16850------30/05/2007-----------10689899----------------189.29
MS_AV16850------06/01/2008-----------1653821----------------65.49
MS_AV16850------22/02/2009-----------10866311----------------189.29

I want my query to show the rows that has most recent purchase dates with same amount in consecutive purchase date.

So from the table above, the query should display:

Prod_Code-----Purchase_date---Inv_number-------Inv_amt
MS_AV16850------16/04/2007-----------10353759----------------189.29
MS_AV16850------30/05/2007-----------10689899----------------189.29


It should not get

MS_AV16850------16/04/2007-----------10353759----------------189.29
MS_AV16850------30/05/2007-----------10689899----------------189.29
MS_AV16850------22/02/2009-----------10866311----------------189.29

because inv_number 10866311 has a prvevious inv_amount of 65.49.

and not get this

MS_AV16850------22/07/2005-----------7034012----------------51.82
MS_AV16850------04/01/2006-----------8719618----------------51.82

because they are not the most recent purchase date even if they have the same inv_amount.


Thanks in advance.

Comments

Solomon Yakobson
Answer

1. Typo in MISSING FILED VALUES ARE NULL

2. RECORDS DELIMITED BY NEWLINE must preceed FIELDS TERMINATED BY '|'

3. There is no such SQL*Loader/External table field type VARCHAR2 (in this case you don't even need to specify it)

SQL> CREATE TABLE emp_load
  2       (employee_number      VARCHAR2(50),
  3        employee_last_name   VARCHAR2(50),
  4         employee_first_name  VARCHAR2(50),
  5        employee_middle_name VARCHAR2(50),
  6         employee_hire_date   VARCHAR2(50))
  7      ORGANIZATION EXTERNAL
  8        (TYPE ORACLE_LOADER
  9        DEFAULT DIRECTORY TEMP
10        ACCESS PARAMETERS (
11          RECORDS DELIMITED BY NEWLINE
12          FIELDS TERMINATED BY '|'
13          MISSING FIELD VALUES ARE NULL
14           (employee_number,
15        employee_last_name,
16         employee_first_name,
17        employee_middle_name,
18         employee_hire_date)
19         )
20        LOCATION ('info.dat')
21       ) REJECT LIMIT UNLIMITED
22  /

Table created.

SQL> SELECT  *
  2    FROM  emp_load
  3  /

EMPLOYEE_N EMPLOYEE_L EMPLOYEE_F EMPLOYEE_M EMPLOYEE_HI
---------- ---------- ---------- ---------- -----------
010        ABC        DEF        XYZ        03-DEC-2011

SQL>

SY

Marked as Answer by user12121820 · Sep 27 2020
user12121820

Thanks that worked for me.

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 31 2010
Added on Nov 22 2010
13 comments
8,327 views