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:

[code=oracle]

SELECT DISTINCT oeh.ORDER_NUMBE R AS sales_order_num ber,
oeh.REQUEST_DAT E AS CUST_REQ_DATE,
oeh.ATTRIBUTE10 AS FOC_FRC_DATE,
rac.CUSTOMER_NA ME AS CUSTOMER_NAME,
oeh.ATTRIBUTE13 AS expedite_indica tor,
oeh.ATTRIBUTE16 AS ENGINEER_ORDER,
oeh.ATTRIBUTE7 AS OES_ORDER,
oeh.HEADER_ID,
XE.EMURL AS QOA_ORDER_SUMMA RY_LINK
FROM oe_order_lines_ all oel,
oe_order_header s_all oeh,
ra_customers rac,
oe_transaction_ types_tl ott,
XXQST_EOI_EM_OR ACLEE2E XE
WHERE oeh.HEADER_ID=o el.HEADER_ID
AND oeh.SOLD_TO_ORG _ID=rac.CUSTOME R_ID
AND oeh.ORG_ID=(SEL ECT HOU.ORGANIZATIO N_ID FROM HR_OPERATING_UN ITS HOU where hou.NAME like'%NWX%')
AND oel.FLOW_STATUS _CODE = 'AWAITING_RECEI PT'
AND oel.CANCELLED_F LAG != 'Y'
AND oel.LINE_TYPE_I D= ott.TRANSACTION _TYPE_ID
AND oeh.ORDER_NUMBE R not in (SELECT SALES_ORDER_NUM BER FROM XXQST_CUST_NOTI FICATION_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 ;
[/code]

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:
[code=oracle]

EXEC EXECUTE IMMEDIATE 'SELECT DISTINCT oeh.ORDER_NUMBE R AS sales_order_num ber,
oeh.REQUEST_DAT E AS CUST_REQ_DATE,
oeh.ATTRIBUTE10 AS FOC_FRC_DATE,
rac.CUSTOMER_NA ME AS CUSTOMER_NAME,
oeh.ATTRIBUTE13 AS expedite_indica tor,
oeh.ATTRIBUTE16 AS ENGINEER_ORDER,
oeh.ATTRIBUTE7 AS OES_ORDER,
oeh.HEADER_ID,
XE.EMURL AS QOA_ORDER_SUMMA RY_LINK
FROM oe_order_lines_ all oel,
oe_order_header s_all oeh,
ra_customers rac,
oe_transaction_ types_tl ott,
XXQST_EOI_EM_OR ACLEE2E XE
WHERE oeh.HEADER_ID=o el.HEADER_ID
AND oeh.SOLD_TO_ORG _ID=rac.CUSTOME R_ID
AND oeh.ORG_ID=(SEL ECT HOU.ORGANIZATIO N_ID FROM HR_OPERATING_UN ITS HOU WHERE hou.NAME LIKE'%NWX%')
AND oel.FLOW_STATUS _CODE = 'AWAITING_RECEI PT'
AND oel.CANCELLED_F LAG != 'Y'
AND oel.LINE_TYPE_I D= ott.TRANSACTION _TYPE_ID
AND oeh.ORDER_NUMBE R NOT IN (SELECT SALES_ORDER_NUM BER FROM XXQST_CUST_NOTI FICATION_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) ';

[/code]