Markus1980Wien Markus1980Wien - 2 months ago 11
SQL Question

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?

Answer

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);
Comments