MVSFORUMS.com

[pzmohanty]

hi all,

I am facing a problem with UNION clause :

i have a following query----

SELECT COMMISSION,EMP_ID FROM EMPLOYEE
WHERE COMMISSION = 0 FETCH FIRST ROW ONLY
UNION
SELECT COMMISSION,EMP_ID FROM EMPLOYEE
WHERE COMMISSION = 100 FETCH FIRST ROW ONLY ;

The error message i got is following:

DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD UNION, TOKEN
<END-OF-STATEMENT> WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 147 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'00000093' X'00000000' SQL DIAGNOSTIC INFORMATION

How else can i get the desired result??

Following is the requirement :

The EMPLOYEE table has various columns , 2 of them are EMP_ID & COMMISSION.
EMP_ID is primary key.
in the table there are many employees have commission of 0 & 100.
there are other employees too had commissions other than 0 & 100.

I have to query EMP_ID & COMMISSION of one employee each for employee getting commission 0 & employee getting commission 100.

Thanx in advnc
P.R.Mohanty

[pzmohanty]

hi all,

one way i found to get around the problem is to use following query :

SELECT EMP_ID , COMMISSION FROM EMPLOYEE
WHERE EMP_ID = ( SELECT MAX(EMP_ID) FROM EMPLOYEE WHERE COMMISSION = 0 )
UNION
SELECT EMP_ID , COMMISSION FROM EMPLOYEE
WHERE EMP_ID = ( SELECT MAX(EMP_ID) FROM EMPLOYEE WHERE COMMISSION = 100 )

but I believe this is CPU hungry quey;

Is there any better query ???

Thanx ,
P.R.Mohanty

[a_seshu]

This would be a CPU friendly SQL