Home » SQL & PL/SQL » SQL & PL/SQL » issue in union condition (10g)
issue in union condition [message #564372] Thu, 23 August 2012 03:52 Go to next message
db_learner
Messages: 5
Registered: September 2011
Junior Member
Hi,

I am facing an issue in union clause

Below is my query
Issue faced is that based on union I want to limit the result and does not want second query to give duplicate result as both queries deal with same table
please suggest

SELECT  TAB1.ID, TAB1.CRNCY_CODE, TAB2.SCHEME_CODE, DECODE( 'INFENG',CAST( ''  AS VARCHAR(20)),
NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC),  
TAB1.SUB_HEAD_CODE,  DECODE( 'INFENG' ,CAST( ''  AS VARCHAR(20)),NVL(TAB1.ALT1_SUB_HEAD_DESC ,
TAB1.SUB_HEAD_DESC),TAB1.SUB_HEAD_DESC) 
FROM TAB1, TAB4, TAB2, TAB3  
WHERE TAB2.SCHEME_TYPE = 'SCHEME1'  AND TAB1.BANK_ID= '01'  AND  TAB4.BANK_ID= '01' 
AND  TAB2.BANK_ID= '01'  AND  TAB3.BANK_ID= '01'  AND  TAB3.DEL_FLG != 'Y'  AND TAB4.DEL_FLG != 'Y'
AND TAB1.DEL_FLG != 'Y'  AND TAB2.DEL_FLG != 'Y'  
AND TAB3.SUB_HEAD_CODE = TAB1.SUB_HEAD_CODE
AND TAB3.SCHEME_CODE = TAB4.SCHEME_CODE 
AND TAB2.SCHEME_CODE = TAB3.SCHEME_CODE 
AND TAB1.CRNCY_CODE = TAB4.CRNCY_CODE 
AND TAB1.ID = '105'   AND TAB4.CRNCY_CODE =  'INR'    
union

select null ID,null crncy_code,TAB2.SCHEME_CODE  ,
NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC,null SUB_HEAD_CODE from TAB2 where TAB2.SCHEME_TYPE ='SCHEME1'; 

[\CODE]
Re: issue in union condition [message #564378 is a reply to message #564372] Thu, 23 August 2012 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use UNION you can't have duplicates as UNION removes the duplicates... or you have your definition of duplicates that is different from a real duplicate.

If you want to limit the result, then;
SELECT * FROM (your query) WHERE ROWNUM < limit

Regards
Michel
Re: issue in union condition [message #564386 is a reply to message #564378] Thu, 23 August 2012 05:18 Go to previous messageGo to next message
db_learner
Messages: 5
Registered: September 2011
Junior Member
bonjour michel
merci beaucoup de votre reponse...
actually i want that the rows returned by query one for TAB2.SCHEME_CODE should not be returned by query 2 which is happening
Re: issue in union condition [message #564390 is a reply to message #564386] Thu, 23 August 2012 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wirh any SQL or PL/SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: issue in union condition [message #564396 is a reply to message #564372] Thu, 23 August 2012 06:15 Go to previous message
flyboy
Messages: 1776
Registered: November 2006
Senior Member
It sounds that you want to outer join some table(s) instead of inner joining them.

It is a good motive for reformatting that code (re-group join/filter conditions based on tables involved) - then this task may be done more comfortably.

Converting the code to ANSI join syntax could improve its readability too.

Good luck.
Previous Topic: Previous record for multiple combination
Next Topic: Selecting latest date
Goto Forum:
  


Current Time: Sat Oct 25 11:46:14 CDT 2014

Total time taken to generate the page: 0.09826 seconds