Home » SQL & PL/SQL » SQL & PL/SQL » UNION (Oracle 10g)
UNION [message #319983] Tue, 13 May 2008 10:42 Go to next message
googenfrog
Messages: 9
Registered: May 2008
Junior Member
I used a UNION but it says not allowed in iSQL Plus error. Does anyone know there is a replacement command. Thanks
Re: UNION [message #319986 is a reply to message #319983] Tue, 13 May 2008 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works if you use it in a proper way.
As you didn't post what you did we can't say more.

Regards
Michel
Re: UNION [message #320014 is a reply to message #319983] Tue, 13 May 2008 12:57 Go to previous messageGo to next message
googenfrog
Messages: 9
Registered: May 2008
Junior Member
SET ECHO ON

SELECT COUNT(*) "NUM OF EMP LOCATION 600",
AVG(salary)"AVERAGE_SALARY"
FROM employees JOIN departments
USING(department_id)
WHERE location_id = 600;


UNION
SP2-0850: Command "UNION" is not available in iSQL*Plus

SELECT COUNT(*) "NUM OF EMP LOCATION NON-600",
AVG(salary)"AVERAGE_SALARY"
FROM employees JOIN departments
USING(department_id)
WHERE location_id <> 600;


Re: UNION [message #320016 is a reply to message #320014] Tue, 13 May 2008 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"WHERE location_id = 600;"
";" ends your statement so the next line "UNION" starts a new and invalid one.

Next time 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).
Use the "Preview Message" button to verify.

Regards
Michel
Re: UNION [message #320030 is a reply to message #320014] Tue, 13 May 2008 15:14 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Unfortunately, even with correct syntax your output won't be too helpful. The heading is going to be "NUM OF EMP LOCATION 600" for your count column.

You'd be better off using a CASE or DECODE statement in a single query rather than using a UNION.

[Updated on: Tue, 13 May 2008 15:15]

Report message to a moderator

Re: UNION [message #320039 is a reply to message #320030] Tue, 13 May 2008 16:43 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Seems to work fine for me...

select 'records in dept' Tag, count(*) Cnt from dept
union -- union all
select 'records in emp', count(*) from emp;

TAG	        CNT
--------------- ----------
records in dept	4
records in emp	14
Re: UNION [message #320070 is a reply to message #320039] Wed, 14 May 2008 00:37 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yeah, but the difference is that you use the description as a separate column, where the original poster uses it as an alias, expecting someway to have the header change mysteriously for the second row.

[Updated on: Wed, 14 May 2008 00:38]

Report message to a moderator

Previous Topic: WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions
Next Topic: ROWNUM ??
Goto Forum:
  


Current Time: Sat Dec 03 00:53:50 CST 2016

Total time taken to generate the page: 0.20189 seconds