Oracle SQL query that will remove duplicates using a substring, and link the list of deduped records to another table

Question

Example table data

|TAB    | MODDATE    |ORDERID  |
|-------| ---------  |---------|
|TableOH| 01/11/2021 |811911|
|TableOH| 01/11/2021 |811913|
|TableOH| 01/11/2021 |260854|
|TableOL| 01/11/2021 |260854#10|
|TableOH| 01/11/2021 |379452|
|TableOL| 01/11/2021 |379452#10|
|TableOL| 01/11/2021 |379452#20|
|TableOL| 01/11/2021 |379452#10|
|TableOL| 01/11/2021 |379452#20|
|TableOH| 01/11/2021 |379452|
|TableOH| 01/11/2021 |379453|
|TableOL| 01/11/2021 |379453#10|
|TableOL| 01/11/2021 |379453#10|
|TableOH| 01/11/2021 |379453|

Desired result.

|TAB    | MODDATE    |ORDERID  |
|-------| ---------  |---------|
|TableOH| 01/11/2021 |  811911|
|TableOH| 01/11/2021 |811913|
|TableOL| 01/11/2021 |260854|
|TableOH|  01/11/2021 |379452|
|TableOH|  01/11/2021 |379453|

Example Query (finds all records and links the tables)

SELECT 
"DATABASE"."DUPTABLE"."TAB",
"DATABASE"."DUPTABLE"."MODDATE",
SUBSTR( “DATABASE”.”DUPTABLE”."ORDERID",1,6),
" DATABASE"."ORDERHEADER"."ORNO",
" DATABASE"."ORDERHEADER"."CUSTNO",

FROM "DATABASE"."DUPTABLE", "DATABASE"."ORDERHEADER"


WHERE 

“DATABASE”.”DUPTABLE”."MODDATE " > DATE ' 2021-01-11' AND  “DATABASE”.”DUPTABLE”." MODDATE " < DATE ' 2021-01-11'
 AND (“DATABASE”.”DUPTABLE”." TAB" = 'TableOH' OR “DATABASE”.”DUPTABLE”."TAB" = 'TableOL')  
AND SUBSTR(“DATABASE”.”DUPTABLE”."ORDERID",1,6) = "DATABASE"."ORDERHEADER"."ORNO"

I am trying to figure out a way to select the records in the "desired result" table, and link these records to the "ORDERHEADER" table. Any help with this would be greatly appreciated.

Thanks MS

question from:https://stackoverflow.com/questions/66050683/oracle-sql-query-that-will-remove-duplicates-using-a-substring-and-link-the-lis

Answer 1

Waitting for answers