CODEWITHSUNDEEP
sqloracle-database
Markus1980Wien
Markus1980Wien

Reputation: 529

Oracle normalize multiple rows into new view

I need to find a solution for the following problem.

Out internal postman has to scan a QR-barcode on a mailbox first and a datamatrix-barcode on each (internal) letter he puts into the mailbox.

The data from his scanner-device is stored into a Oracle 11g database-table in the following format

|----|---------------------|--------------|---------------|
| ID |      SCAN_DATE      | BAROCDE_TYPE | BARCODE_VALUE |
----------------------------------------------------------|
| 1  | 2016/02/01 08:10:30 |      QR      | Dept_HR       |
| 2  | 2016/02/01 08:10:35 |      DM      | Lett_1        |
| 3  | 2016/02/01 08:10:38 |      DM      | Lett_3        |
| 4  | 2016/02/01 08:10:41 |      DM      | Lett_6        |
| 5  | 2016/02/01 08:16:37 |      QR      | Dept_FI       |
| 6  | 2016/02/01 08:16:38 |      DM      | Lett_2        |
| 7  | 2016/02/01 08:16:40 |      DM      | Lett_4        |
|----|---------------------|--------------|---------------|

I want to "normalize?" the data into a database-view in the following format (where it easy to see which letter was delivered to which mailbox)

|---------------------|------------|---------------------|----------|
|   ScanDate Postbox  | Department |   ScanDate Letter   | LetterID |
|---------------------|------------|---------------------|----------|
| 2016/02/01 08:10:30 | Dept_HR    | 2016/02/01 08:10:35 | Lett_1   |
| 2016/02/01 08:10:30 | Dept_HR    | 2016/02/01 08:10:38 | Lett_3   |
| 2016/02/01 08:10:30 | Dept_HR    | 2016/02/01 08:10:41 | Lett_6   |
| 2016/02/01 08:16:37 | Dept_FI    | 2016/02/01 08:16:38 | Lett_2   |
| 2016/02/01 08:16:37 | Dept_FI    | 2016/02/01 08:16:40 | Lett_4   |
|---------------------|------------|---------------------|----------|

Any ideas how I can create an oracle database-view showing the data as described above?

Upvotes: 0

Views: 75

Answers (1)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

I guess the Postbox record is the previous record to the letter records. This is a bad because unsafe association.

the following select should do the job:

-- Your testdata   
with data(id,
scan_date,
barcode_type,
barcode_value) as
 (select 1,
         to_date('2016/02/01 08:10:30', 'YYYY/MM/DD HH24:MI:SS'),
         'QR',
         'Dept_HR'
    from dual
  union all
  select 2,
         to_date('2016/02/01 08:10:35', 'YYYY/MM/DD HH24:MI:SS'),
         'DM',
         'Lett_1'
    from dual
  union all
  select 3,
         to_date('2016/02/01 08:10:38', 'YYYY/MM/DD HH24:MI:SS'),
         'DM',
         'Lett_3'
    from dual
  union all
  select 4,
         to_date('2016/02/01 08:10:41', 'YYYY/MM/DD HH24:MI:SS'),
         'DM',
         'Lett_6'
    from dual
  union all
  select 5,
         to_date('2016/02/01 08:16:37', 'YYYY/MM/DD HH24:MI:SS'),
         'QR',
         'Dept_FI'
    from dual
  union all
  select 6,
         to_date('2016/02/01 08:16:38', 'YYYY/MM/DD HH24:MI:SS'),
         'DM',
         'Lett_2'
    from dual
  union all
  select 7,
         to_date('2016/02/01 08:16:40', 'YYYY/MM/DD HH24:MI:SS'),
         'DM',
         'Lett_4'
    from dual)

-- Select
select dp.scan_date     as "ScanDate Postbox",
       dp.barcode_value as "Departement",
       d.scan_date      as "ScanDate Letter",
       d.barcode_value  as "LetterId"
  from data dp, data d
 where d.barcode_type = 'DM'
   and dp.barcode_type = 'QR'
   and dp.scan_date =
       (select max(dpp.scan_date)
          from data dpp
         where dpp.barcode_type = dp.barcode_type
           and dpp.scan_date <= d.scan_date);

Upvotes: 1

Related Questions