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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…