| UNION [message #319983] |
Tue, 13 May 2008 10:42  |
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  |
Michel Cadot
Messages: 17716
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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 |
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 |
Michel Cadot
Messages: 17716
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
"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 |
joy_division
Messages: 2120
Registered: February 2005
Location: NY |
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]
|
|
|
| Re: UNION [message #320039 is a reply to message #320030 ] |
Tue, 13 May 2008 16:43 |
andrew again
Messages: 2181
Registered: September 2004 |
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 |
Frank
Messages: 5761
Registered: April 2002
Location: NL |
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]
|
|
|
]
Members
Search
Help
Register
Login
Home
