Hi
I am using Oracle Database 11g Release 2: 11.2.0.1.
I am having two different table and the requirement is to find the match for a value stored in a column named 'Pur_Order_Id',of a PUR_ORDR_ITEM table.
Structure of the two tables is listed below.
1) PUR_ORDR_ITEM
Create Table VIN.PUR_ORDR_ITEM (
"Pur_Order_Id" VARCHAR(64) NOT NULL,
"PUR_ORDER_ITEM_ID" VARCHAR(64) NOT NULL,
"MTRL_ID" VARCHAR(64),
"SITE_ID" VARCHAR(64),
"PROJ_ID" VARCHAR(64),
"QTY" NUMBER(17 , 2)
)
2) TRANSPT_INVOICE_ITEM
CREATE TABLE "VIN"."TRANSPT_INVOICE_ITEM" (
"TRANSPT_INVOICE_ID" VARCHAR(64) NOT NULL,
"TRANSPT_INVOICE_ITM_ID" VARCHAR(64) NOT NULL,
"CNSGNMNT_NOTE_ID" VARCHAR(64),
"INVC_ITEM_REF_1" VARCHAR(255),
"INVC_ITEM_REF_2" VARCHAR(255),
"TRANS_DT" Date,
"QTY" NUMBER(12 , 2),
"TOT_AMT" NUMBERL(12 , 2)
)
Problem is that i need to find out the records where PUR_ORDR_ITEM.Pur_Order_Id is either equal to TRANSPT_INVOICE_ITEM.INVC_ITEM_REF_1 or TRANSPT_INVOICE_ITEM.INVC_ITEM_REF_2,this sound pretty simple but there is twist to it.And i am struggling to resolve that twist.
The twist is,column named INVC_ITEM_REF_1 and INVC_ITEM_REF_2 contains value which might be exactly equal to the Pur_Order_id and again these columns also contains values with additional characters either prefixed or suffixed to the Pur_Order_Id.
For example the PUR_ORDR_ITEM table is having below rows.
| PRCHS_ORDR_ID | PRCHS_ORDR_ITM_ID | MATRL_ID | SITE_ID | PROJ_ID | QTY |
|---|
| 50588296 | 1 | | Z2 | NULL | 1 |
| 50762691 | 1 | 9600138 | DC13 | NULL | 150 |
| 50821735 | 1 | 91101537 | Z2 | NULL | 1 |
| 50773559 | 1 | 90400047 | Z2 | A46029557-0A | 26091 |
| 50821735 | 3 | 91101540 | Z2 | NULL | 1 |
| 50775801 | 1 | 90400009 | Z2 | A46029557-0A | 4850 |
| 50844294 | 1 | | Z2 | 410006738 | 9500 |
| 50810561 | 1 | 55000484 | VOIV | NULL | 13000 |
| 50821735 | 2 | 91101539 | Z2 | NULL | 1 |
| 50846780 | 1 | 18790255 | Z2 | 410006740 | 1 |
TRANSPT_INVOICE_ITEM table is having following rows.
| TRANSPT_INVOICE_ID | TRANSPT_INVOICE_ITM_ID | CNSGNMNT_NOTE_ID | INVC_ITEM_REF_1 | INVC_ITEM_REF_2 | TRANS_DT | QTY | TOT_AMT |
|---|
| CEVA_TRANSPT_20141201 | 134 | WEB015220420 | CCUWCM/973000 | /1487801 | 2014-12-01 | 2 | 178.81 |
| CEVA_TRANSPT_20160708 | 2578 | WEB019560090 | CCA27M/861001 | 1488256/98150588296 | 2016-07-08 | 1 | 34.50 |
| CEVA_TRANSPT_20170305 | 1139 | WEB017061090 | OMAG04/J73000 | 9050821735/1594661 | 2017-03-05 | 3 | 18.75 |
| CEVA_TRANSPT_20170308 | 7546 | ENP012504542 | CCQSME/5757009 | 50762691 | 2017-03-08 | 4 | 410.00 |
| CEVA_TRANSPT_20170310 | 3039 | NLP665479871 | CQ50821737/CDX561 | 50821735 | 2017-03-10 | 2 | 165.75 |
| CEVA_TRANSPT_20170412 | 8546 | VXZ34598WZ89 | COP631/858108 | 50773559 | 2017-04-12 | 6 | 205.10 |
| CEVA_TRANSPT_20170416 | 7677 | BAC749432409 | NEPVC1/858109 | 50821736 | 2017-04-16 | 1 | 17.80 |
| CEVA_TRANSPT_20170512 | 7878 | RIC015606163 | GCOAD1/858110 | 50775801 | 2017-05-12 | 3 | 55.50 |
| CEVA_TRANSPT_20170618 | 4004 | CRB764505222 | ESG010/858111 | 50844294 | 2017-06-18 | 2 | 51.10 |
| CEVA_TRANSPT_20170810 | 2189 | GEO856344576 | CBP716/858112 | 50810561 | 2017-08-10 | 4 | 210.35 |
| CEVA_TRANSPT_20170911 | 24 | RIC161610635 | ISAP10/858113 | 00141380028MF9F | 2017-09-11 | 6 | 760.80 |
| CEVA_TRANSPT_20171010 | 7788 | EPN549016610 | VXW778/858114 | 00141380028SJ0D | 2017-10-10 | 2 | 16.70 |
Expected/Desired output.
| TRANSPT_INVOICE_ID | TRANSPT_INVOICE_ITM_ID | CNSGNMNT_NOTE_ID | INVC_ITEM_REF_1 | INVC_ITEM_REF_2 | Trans_Dt | QTY | Tot_AMT |
|---|
| CEVA_TRANSPT_20160708 | 2578 | WEB019560090 | Null | 50588296 | 2016-07-08 | 1 | 34.50 |
| CEVA_TRANSPT_20170308 | 7546 | ENP012504542 | Null | 50762691 | 2017-03-18 | 4 | 410.00 |
| CEVA_TRANSPT_20170310 | 3039 | NLP665479871 | 50821737 | 50821735 | 2017-03-10 | 2 | 165.75 |
| CEVA_TRANSPT_20170412 | 8546 | VXZ34598WZ89 | Null | 50773559 | 2017-04-12 | 6 | 205.10 |
| CEVA_TRANSPT_20170416 | 7677 | BAC749432409 | Null | 50821736 | 2017-04-16 | 1 | 17.80 |
| CEVA_TRANSPT_20170512 | 7878 | RIC015606163 | Null | 50775801 | 2017-05-12 | 3 | 55.50 |
| CEVA_TRANSPT_20170618 | 4004 | CRB764505222 | Null | 50844294 | 2017-06-18 | 2 | 51.10 |
| CEVA_TRANSPT_20170810 | 2189 | GEO856344576 | Null | 50810561 | 2017-08-10 | 4 | 210.35 |
Seek your guidance to get this desired output.
Ideally with the LIKE function we can address this issue but it will require me to write the statement N number of times,i am looking for a feasible solution either in the form of a sql statement or Stored Procedure.
Any sort of help is highly appreciated.
Cheers
Vinny