Home » SQL & PL/SQL » SQL & PL/SQL » strange results set (oracle 10.1.2)
strange results set [message #396074] Fri, 03 April 2009 13:21 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi all
When i use the UNION i'm getting strange results:
SELECT OPERATION,SPEED,VOC,CO,NOX from STG_ITS_EMISSIONFACTOR
union
select RTDIRNTYMEAS,NULL,NULL,NULL,NULL from STG_ITS_SEGMENTCATEGORY_LUT


RESULTS:
OPERATION	SPEED	VOC	CO	NOX

31 n m 126.3				
31 n m 127.3				
31 n m 131.1				
31 n m 132.3				
31 n m 134.7				
31 s m 125.5				
31 s m 129.5				


Indianapolis	40	0.63	8.935	1.191
Indianapolis	43	0.6186	8.548	1.2162
Indianapolis	44	0.6148	8.419	1.2246
Indianapolis	47	0.6042	8.8396	1.2578
Indianapolis	48	0.6008	9.1144	1.2702
Indianapolis	49	0.5974	9.3892	1.2826


Please any help is greatly appreciated.

Thanks
Re: strange results set [message #396075 is a reply to message #396074] Fri, 03 April 2009 13:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The result looks perfectly OK to me.
Re: strange results set [message #396076 is a reply to message #396074] Fri, 03 April 2009 13:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>strange results set
Nothing strange here.
Expected results.
Re: strange results set [message #396077 is a reply to message #396076] Fri, 03 April 2009 13:28 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Was it not supposed to give the following set
OPERATION,SPEED,VOC,CO,NOX,RDIRNTYMEAS ?
BlackSwan wrote on Fri, 03 April 2009 13:26
>strange results set
Nothing strange here.
Expected results.

Re: strange results set [message #396078 is a reply to message #396074] Fri, 03 April 2009 13:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/


Re: strange results set [message #396079 is a reply to message #396077] Fri, 03 April 2009 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Was it not supposed to give the following set
OPERATION,SPEED,VOC,CO,NOX,RDIRNTYMEAS ?

No.

Regards
Michel
Re: strange results set [message #396081 is a reply to message #396078] Fri, 03 April 2009 13:39 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Sir what i meant was when i execute the following query :

SELECT OPERATION,SPEED,VOC,CO,NOX from STG_ITS_EMISSIONFACTOR
UNION 
select RTDIRNTYMEAS,NULL,NULL,NULL,NULL from STG_ITS_SEGMENTCATEGORY_LUT


Shouldnt my result set contain all the fields in the following order
OPERATION SPEED VOC CO NOX RTDIRNTYMEAS


Thanks
Re: strange results set [message #396082 is a reply to message #396081] Fri, 03 April 2009 13:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
aarti81 wrote on Fri, 03 April 2009 11:39
Shouldnt my result set contain all the fields in the following order
OPERATION SPEED VOC CO NOX RTDIRNTYMEAS


NO!
Re: strange results set [message #396086 is a reply to message #396081] Fri, 03 April 2009 15:02 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
aarti81 wrote on Fri, 03 April 2009 14:39

Shouldnt my result set contain all the fields in the following order
OPERATION SPEED VOC CO NOX RTDIRNTYMEAS



I think you need to read up on what the UNION command does.
Re: strange results set [message #396126 is a reply to message #396086] Sat, 04 April 2009 01:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello aarti81,

As other members stated, you first go through concepts of UNION. You have misunderstood it.
By the way you can get desired output as follows.

SELECT t1.OPERATION, t1.SPEED, t1.VOC, t1. CO, t1.NOX,  
       t2.RTDIRNTYMEAS
from STG_ITS_EMISSIONFACTOR t1,
     STG_ITS_SEGMENTCATEGORY_LUT t2
where ...


regards,
Delna
Re: strange results set [message #396133 is a reply to message #396126] Sat, 04 April 2009 02:35 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think a cartesian product is NOT what OP is searching for; but it is hard to say without any requirement.

Regards
Michel
Previous Topic: Oracle Analytical functions question
Next Topic: to different column depending on a value
Goto Forum:
  


Current Time: Fri Dec 09 17:41:38 CST 2016

Total time taken to generate the page: 0.37432 seconds