I need help below this Query Urgent Please help me..

[veerunrt]

Hi All,

one column is having no of values separated by comma in the table that any one value should match with some other column of another table one value how to join in oracle

I need result for this query:

 
SELECT  DISTINCT oeh.ORDER_NUMBER AS sales_order_number,
   oeh.REQUEST_DATE AS CUST_REQ_DATE,
          oeh.ATTRIBUTE10 AS FOC_FRC_DATE,
          rac.CUSTOMER_NAME AS CUSTOMER_NAME,
          oeh.ATTRIBUTE13 AS expedite_indicator,
          oeh.ATTRIBUTE16 AS ENGINEER_ORDER,
          oeh.ATTRIBUTE7 AS OES_ORDER,
          oeh.HEADER_ID,
    XE.EMURL AS QOA_ORDER_SUMMARY_LINK
FROM     oe_order_lines_all oel,
  oe_order_headers_all oeh,
  ra_customers rac,
  oe_transaction_types_tl ott,
  XXQST_EOI_EM_ORACLEE2E XE
WHERE oeh.HEADER_ID=oel.HEADER_ID
AND oeh.SOLD_TO_ORG_ID=rac.CUSTOMER_ID
AND oeh.ORG_ID=(SELECT HOU.ORGANIZATION_ID FROM HR_OPERATING_UNITS HOU where hou.NAME like'%NWX%')
AND oel.FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
AND oel.CANCELLED_FLAG != 'Y'
AND oel.LINE_TYPE_ID= ott.TRANSACTION_TYPE_ID
AND oeh.ORDER_NUMBER not in (SELECT SALES_ORDER_NUMBER FROM XXQST_CUST_NOTIFICATION_TAB)
AND (ott.NAME='NWX Equip' OR ott.NAME='NWX Labor')
AND oeh.ATTRIBUTE10 IS NULL
AND XE.OESORDERID= oeh.ATTRIBUTE7
--AND XE.ENGORDERID= oeh.ATTRIBUTE16;
 

I that last line of the query XE.ENGORDERID is having no.of values suprated by commas
Ex:ENGORDERID :77754916,77755 622,77756404,77 757356

need to join with any one value should match with attribute16 of oe_order_header s_all

Please help me above the query....

Regards,
Veera.

[amitpatel66]

Try like this:

 
EXEC EXECUTE IMMEDIATE 'SELECT  DISTINCT oeh.ORDER_NUMBER AS sales_order_number,
   oeh.REQUEST_DATE AS CUST_REQ_DATE,
          oeh.ATTRIBUTE10 AS FOC_FRC_DATE,
          rac.CUSTOMER_NAME AS CUSTOMER_NAME,
          oeh.ATTRIBUTE13 AS expedite_indicator,
          oeh.ATTRIBUTE16 AS ENGINEER_ORDER,
          oeh.ATTRIBUTE7 AS OES_ORDER,
          oeh.HEADER_ID,
    XE.EMURL AS QOA_ORDER_SUMMARY_LINK
FROM     oe_order_lines_all oel,
  oe_order_headers_all oeh,
  ra_customers rac,
  oe_transaction_types_tl ott,
  XXQST_EOI_EM_ORACLEE2E XE
WHERE oeh.HEADER_ID=oel.HEADER_ID
AND oeh.SOLD_TO_ORG_ID=rac.CUSTOMER_ID
AND oeh.ORG_ID=(SELECT HOU.ORGANIZATION_ID FROM HR_OPERATING_UNITS HOU WHERE hou.NAME LIKE'%NWX%')
AND oel.FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
AND oel.CANCELLED_FLAG != 'Y'
AND oel.LINE_TYPE_ID= ott.TRANSACTION_TYPE_ID
AND oeh.ORDER_NUMBER NOT IN (SELECT SALES_ORDER_NUMBER FROM XXQST_CUST_NOTIFICATION_TAB)
AND (ott.NAME='NWX Equip' OR ott.NAME='NWX Labor')
AND oeh.ATTRIBUTE10 IS NULL
AND XE.OESORDERID= oeh.ATTRIBUTE7
AND oeh.ATTRIBUTE16 IN (XE.ENGORDERID)';