Home » SQL & PL/SQL » SQL & PL/SQL » Pls solve the query (Oracle 9i/Oracle apps)
Pls solve the query [message #305178] Mon, 10 March 2008 00:15 Go to next message
kishorebabu
Messages: 4
Registered: March 2008
Location: chennai
Junior Member
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 having only one value how to match the exact data in both columns

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;
For example in my situation
In the last line of the query XE.ENGORDERID is having no.of values suprated by commas
Ex:ENGORDERID :77754916,77755622,77756404,77757356

need to match any one value should match with attribute16 of oe_order_headers_all

Please help me above the query....

Regards,
Kishore.
Re: Pls solve the query [message #305180 is a reply to message #305178] Mon, 10 March 2008 00:29 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
@kishorebabu this being your first post i would like to tell you that there are some guidelines to be followed while posting a question.please read this ORAFAQ GUIDELINES

regards,
Re: Pls solve the query [message #305187 is a reply to message #305178] Mon, 10 March 2008 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why did you post this in expert forum and newbie one?

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Follow them including what is said about formatting.

Regards
Michel
Re: Pls solve the query [message #312892 is a reply to message #305178] Thu, 10 April 2008 01:16 Go to previous messageGo to next message
lotus14
Messages: 5
Registered: February 2005
Junior Member
Is your problem resolved?

If not, try to use like this

oeh.ATTRIBUTE16 like '%' || XE.ENGORDERID || '%'
Re: Pls solve the query [message #329617 is a reply to message #312892] Thu, 26 June 2008 00:51 Go to previous message
kishorebabu
Messages: 4
Registered: March 2008
Location: chennai
Junior Member
Thanks for reply.
Now this requirement is changed.

wish you a good day
Previous Topic: 2 subquery for INSERT INTO
Next Topic: NVL() degrades the performance
Goto Forum:
  


Current Time: Tue Dec 06 04:10:16 CST 2016

Total time taken to generate the page: 0.09484 seconds