UNION in select query 2

[sql99]

Hello everyone,

I have a sql script with the following content:

SPOOL C:\temp\union.lst
--
((SELECT pers_id
FROM person
WHERE pers_id IN (SELECT pers.pers_id
FROM person pers, employee
emp
WHERE pers.job_type in ('REC', 'SALE')
AND emp.status = 'Full Time'
AND emp.active = 'Active'
AND pers.pers_id = emp.emp_id))

--
-- UNION

(SELECT pers_id
FROM person
WHERE pers_id IN (SELECT pers.pers_id
FROM person pers, employee
emp
WHERE pers.job_type in ('SEC', 'ACCT')
AND emp.status = 'Part Time'
AND emp.active = 'Active'
AND pers.pers_id = emp.emp_id)));
SPOOL OFF


When I execute this script, I get - Unknown command "UNION" - rest of line ignored

When I run the select alone in sql plus, it works fine but when I execute it in a script, I get the above error.

Please help...

Thanks in advance,
sql99

 

[LKBrwnDBA]

What's with all those ((('s and )))'s?? -- remove the outer most pairs of () (as many as you can).



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sql99]

Sorry, this is the actual error message:

SQL> @C:\temp\union.sql;
Unknown command "UNION" - rest of line ignored.
AND pers.pers_id = emp.emp_id)))
*
ERROR at line 9:
ORA-00933: SQL command not properly ended

 

[LKBrwnDBA]

As I suggested, remove the excess ()'s:

SELECT pers_id
  FROM person
 WHERE pers_id IN (SELECT pers.pers_id 
                     FROM person pers, employee  emp                   
                    WHERE pers.job_type in ('REC', 'SALE')
                      AND emp.status = 'Full Time' 
                      AND emp.active = 'Active'
                      AND pers.pers_id = emp.emp_id)
UNION

SELECT pers_id
  FROM person
 WHERE pers_id IN (SELECT pers.pers_id 
                     FROM person pers, employee emp                   
                    WHERE pers.job_type in ('SEC', 'ACCT')
                      AND emp.status = 'Part Time' 
                      AND emp.active = 'Active'
                      AND pers.pers_id = emp.emp_id);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sql99]

Thank you LKBrwnDBA. I could of sworn that I tried it w/o the brackets and got some error message. I tried it again just now and it works fine.

Thanks for your help,
sql99

 

[SantaMufasa]

SQL,

In your case, (if you are running from SQL*Plus) the problem was a blank line prior to the "UNION" clause. SQL*Plus interprets a blank line as a signal of a new SQL statement. Once SQL*Plus believes it is processing a new statement, it flags the word "UNION" as an invalid method of beginning a new SQL statement.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 01:31 (19Jan05) UTC (aka "GMT" and "Zulu"),
@ 18:31 (18Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[sql99]

Thanks for your reply, Dave...Iwould of never guessed that if u hadn't told me...

Thanks again,
sql99