Home » SQL & PL/SQL » SQL & PL/SQL » Please frame a query for this
Please frame a query for this [message #322311] Fri, 23 May 2008 01:37 Go to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
I am not able to get anything into my head and am not able to frame a query for this.
Requirement is:

Table 1

Table Name: FND_LOOKUP_VALUES
Column Name: Lookup_Type, Lookup_Code etc

Table 2

Table Name: XXC_TEMP_LOOKUPS
Column Name: Lookup_Type, Old_Lookup_Code. Old_Tag etc

What is want is that i need to select all the Lookup_codes from XXC_TEMP_LOOKUPS which are NOT THERE in FND_LOOKUP_VALUES for a particular Lookup_Type

So effectively if Table 1 (FND_LOOKUP_VALUES) has foll data for a particular Lookup_Type

Lookup_Type Lookup_Code

TYPE1 CODE1
TYPE1 CODE2
TYPE1 CODE3


And if Table 2 (XXC_TEMP_LOOKUPS) has foll data for a particular Lookup_Type

Lookup_Type Lookup_Code

TYPE1 CODE3
TYPE1 CODE_JUNK


then CODE_JUNK should be retrieved.

Re: Please frame a query for this [message #322328 is a reply to message #322311] Fri, 23 May 2008 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what did you try?

Also, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Please frame a query for this [message #322434 is a reply to message #322311] Fri, 23 May 2008 09:17 Go to previous messageGo to next message
shivaram9
Messages: 35
Registered: August 2006
Member
SELECT *
  FROM XXC_TEMP_LOOKUPS XXTL
 WHERE NOT EXISTS (SELECT 1
                     FROM FND_LOOKUP_VALUES FNDLV
                    WHERE FNDLV.lookup_type = XXTL.lookup_type
                      AND FNDLV.lookup_code = XXTL.old_lookup_code)
Re: Please frame a query for this [message #322435 is a reply to message #322311] Fri, 23 May 2008 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I would use MINUS operator
Re: Please frame a query for this [message #322442 is a reply to message #322311] Fri, 23 May 2008 09:43 Go to previous messageGo to next message
shivaram9
Messages: 35
Registered: August 2006
Member
Hi anacedent,

Is Exists costly when compared to Minus?

Thanks,
Shiva

[Updated on: Fri, 23 May 2008 09:44]

Report message to a moderator

Re: Please frame a query for this [message #322443 is a reply to message #322311] Fri, 23 May 2008 09:47 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Is Exists costly when compared to Minus?
What do your benchmark comparison runs indicate?
Previous Topic: Unreachable Code Error
Next Topic: DBMS_PIPE
Goto Forum:
  


Current Time: Sat Dec 10 10:35:52 CST 2016

Total time taken to generate the page: 0.08805 seconds