Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Unexpected token in a select statement

626 views
Skip to first unread message

Carsten Nitschke

unread,
Sep 5, 2002, 3:49:58 AM9/5/02
to
The following select statement is meant to select distinct entries
from a table and then count those distinct entries.

SELECT count(DISTINCT DPV_ID) FROM
(
(
SELECT DISTINCT DPR_ID, DPV_ID, DPV_EFFECTIVEDATE
FROM DISTANCEPACKAGEVRS
WHERE DPV_STATUS ='A'
)
MINUS
(
SELECT DISTINCT NEWDATE.DPR_ID,NEWDATE.DPV_ID,NEWDATE.DPV_EFFECTIVEDATE
FROM
(
SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
FROM DISTANCEPACKAGEVRS
WHERE DPV_STATUS = 'A'
)
NEWDATE,
(
SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
FROM DISTANCEPACKAGEVRS
WHERE DPV_EFFECTIVEDATE <= ( CURRENT DATE - 300 DAYS )
AND DPV_STATUS = 'A'
)
OLDDATE
WHERE
NEWDATE.DPR_ID = OLDDATE.DPR_ID AND
NEWDATE.DPV_EFFECTIVEDATE < OLDDATE.DPV_EFFECTIVEDATE)
) VALID_DPV

The SQL Error message is as follows:
SQL0104N An unexpected token "count(DISTINCT DPV_ID) FROM (
( " was found following " SELECT ".Expected tokens may
include: "<space>". SQLSTATE=42601

The Statement worked fine while it was used in an Oracle 8i
environment.

PM (pm3iinc-nospam)

unread,
Sep 5, 2002, 10:26:49 AM9/5/02
to
Wonder if it could come from

...
FROM
(
select ...
) newdate,
(
select
)


Doug Doole

unread,
Sep 5, 2002, 9:44:42 AM9/5/02
to
The "MINUS" keyword is not part of DB2's SQL (I don't think it's in the
SQL standard either). Do you perhaps mean EXCEPT?
--
_____________________________________________________________________
Doug Doole
DB2 Universal Database Development
IBM Toronto Labs

DB2 UDB v8.1 Beta is available for AIX, HP, Linux, Solaris and Windows
http://www.ibm.com/db2/v8beta

Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support

Carsten Nitschke

unread,
Sep 6, 2002, 4:23:23 AM9/6/02
to
Doug Doole <do...@removeme.ca.ibm.com> wrote in message news:<3D775FCA...@removeme.ca.ibm.com>...

The EXCEPT helped indeed somewhat, thank you. However, now I stand at
a different problem. Apparently it seems to have to do something with
the setting of the brackets, or at least I think so. The revised
select statement looks as follows now:
SELECT dzl.DOR_ID, dpl.dzr_id, dzl.DDR_ID,
ddr.DDR_Destination,
dpv.DPR_ID, dpv.DPV_Version
FROM TRU_DistanceZoneLink dzl,
TRU_DestinationRef ddr,
TRU_DistancePackageVrs dpv,
TRU_DistancePackageLink dpl,
(
SELECT DPV_ID FROM


(

SELECT DISTINCT DPR_ID, DPV_ID, DPV_EFFECTIVEDATE

FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_STATUS ='A'


EXCEPT



(
SELECT DISTINCT
NEWDATE.DPR_ID,NEWDATE.DPV_ID,NEWDATE.DPV_EFFECTIVEDATE
FROM

SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_STATUS = 'A'

NEWDATE,

SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_EFFECTIVEDATE <= CURRENT TIMESTAMP - 300 DAYS
AND DPV_STATUS = 'A'

OLDDATE

WHERE
NEWDATE.DPR_ID = OLDDATE.DPR_ID AND
NEWDATE.DPV_EFFECTIVEDATE < OLDDATE.DPV_EFFECTIVEDATE
)
)

) VALID_DPV
WHERE dzl.DDR_ID = ddr.DDR_ID
AND dzl.DPL_ID = dpl.DPL_ID
AND dpv.DPV_ID = dpl.DPV_ID
AND dpl.DPV_ID = VALID_DPV.DPV_ID

This statement now gives me the following error message:

SQL0104N An unexpected token
"NEWDATE.DPR_ID,NEWDATE.DPV_ID,NEWDATE.DPV_EFFE"was found following
"SELECT DISTINCT". Expected tokens may include: "<space>".
SQLSTATE=42601

Doug Doole

unread,
Sep 6, 2002, 4:03:43 PM9/6/02
to
Looks like you've got a couple nested selects without brackets around
them. Try adding the brackets and see where that takes you.

SELECT dzl.DOR_ID, dpl.dzr_id, dzl.DDR_ID,
ddr.DDR_Destination,
dpv.DPR_ID, dpv.DPV_Version
FROM TRU_DistanceZoneLink dzl,
TRU_DestinationRef ddr,
TRU_DistancePackageVrs dpv,
TRU_DistancePackageLink dpl,
(
SELECT DPV_ID FROM
(

SELECT DISTINCT DPR_ID, DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_STATUS ='A'


EXCEPT

(
SELECT DISTINCT
NEWDATE.DPR_ID,NEWDATE.DPV_ID,NEWDATE.DPV_EFFECTIVEDATE
FROM

* SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
* FROM TRU_DISTANCEPACKAGEVRS
* WHERE DPV_STATUS = 'A'

NEWDATE,

* SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
* FROM TRU_DISTANCEPACKAGEVRS
* WHERE DPV_EFFECTIVEDATE <= CURRENT TIMESTAMP - 300
DAYS
* AND DPV_STATUS = 'A'

OLDDATE

WHERE
NEWDATE.DPR_ID = OLDDATE.DPR_ID AND
NEWDATE.DPV_EFFECTIVEDATE < OLDDATE.DPV_EFFECTIVEDATE
)
)
) VALID_DPV
WHERE dzl.DDR_ID = ddr.DDR_ID
AND dzl.DPL_ID = dpl.DPL_ID
AND dpv.DPV_ID = dpl.DPV_ID
AND dpl.DPV_ID = VALID_DPV.DPV_ID

Carsten Nitschke

unread,
Sep 9, 2002, 5:40:01 AM9/9/02
to
Doug Doole <do...@removeme.ca.ibm.com> wrote in message news:<3D790A1F...@removeme.ca.ibm.com>...

> Looks like you've got a couple nested selects without brackets around
> them. Try adding the brackets and see where that takes you.
> _____________________________________________________________________
> Doug Doole
> DB2 Universal Database Development
> IBM Toronto Labs
>
> DB2 UDB v8.1 Beta is available for AIX, HP, Linux, Solaris and Windows
> http://www.ibm.com/db2/v8beta
>
> Visit the DB2 UDB and DB2 Connect Online Support site at:
> http://www.ibm.com/software/data/db2/udb/winos2unix/support

Added the brackets, but still get the unexpected token error:


SELECT dzl.DOR_ID, dpl.dzr_id, dzl.DDR_ID,
ddr.DDR_Destination,
dpv.DPR_ID, dpv.DPV_Version
FROM TRU_DistanceZoneLink dzl,
TRU_DestinationRef ddr,
TRU_DistancePackageVrs dpv,
TRU_DistancePackageLink dpl,
(
SELECT DPV_ID FROM
(
SELECT DISTINCT DPR_ID, DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_STATUS ='A'

EXCEPT

(
SELECT DISTINCT
NEWDATE.DPR_ID,NEWDATE.DPV_ID,NEWDATE.DPV_EFFECTIVEDATE
FROM

(
SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE


FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_STATUS = 'A'

)
NEWDATE,

(


SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS

WHERE DPV_EFFECTIVEDATE <= CURRENT TIMESTAMP - 300 DAYS

AND DPV_STATUS = 'A'
)

Doug Doole

unread,
Sep 9, 2002, 9:00:37 AM9/9/02
to
I can't see what's missing or incorrect. What I'd suggest is starting
with the inner-most subselect, try each subselect on its own until you
find the smallest statement that fails.
--

Tokunaga T.

unread,
Sep 10, 2002, 2:12:10 AM9/10/02
to
Carsten....@gmx.net (Carsten Nitschke) wrote in message news:<d4f1994a.02090...@posting.google.com>...
How about adding correlation name at here? Like this:
) VALID_DPR_DPV

Carsten Nitschke

unread,
Sep 10, 2002, 3:28:05 AM9/10/02
to
Doug Doole <do...@removeme.ca.ibm.com> wrote in message news:<3D7C9B75...@removeme.ca.ibm.com>...

That idea really helped. Seems there was a "container" missing for one
of the selects. For your information, and those interested in the
problem, I am posting the working select statement here:

SELECT dzl.DOR_ID, dpl.dzr_id, dzl.DDR_ID,
ddr.DDR_Destination,
dpv.DPR_ID, dpv.DPV_Version
FROM TRU_DistanceZoneLink dzl,
TRU_DestinationRef ddr,
TRU_DistancePackageVrs dpv,
TRU_DistancePackageLink dpl,
(
SELECT DPV_ID FROM

(
SELECT DISTINCT DPR_ID, DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_STATUS ='A'


EXCEPT


SELECT DISTINCT
NEWDATE.DPR_ID,NEWDATE.DPV_ID,NEWDATE.DPV_EFFECTIVEDATE
FROM
(
SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_STATUS = 'A'
)
NEWDATE,

(
SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_EFFECTIVEDATE <= CURRENT TIMESTAMP - 300 DAYS
AND DPV_STATUS = 'A'
)
OLDDATE
WHERE
NEWDATE.DPR_ID = OLDDATE.DPR_ID AND
NEWDATE.DPV_EFFECTIVEDATE < OLDDATE.DPV_EFFECTIVEDATE
)

VALID_DPV
)VALID_DPV


WHERE dzl.DDR_ID = ddr.DDR_ID
AND dzl.DPL_ID = dpl.DPL_ID
AND dpv.DPV_ID = dpl.DPV_ID
AND dpl.DPV_ID = VALID_DPV.DPV_ID

Thanks a lot for your help.

Carsten Nitschke

unread,
Sep 10, 2002, 3:56:45 AM9/10/02
to
Doug Doole <do...@removeme.ca.ibm.com> wrote in message news:<3D7C9B75...@removeme.ca.ibm.com>...

Hmm, seems I said hurray a bit too quick. While the select statement
works really fine on the CLI, the same select statement being invoked
from a C++ program results yet again in the same old token error
message.

SELECT dzl.DOR_ID, dpl.dzr_id, dzl.DDR_ID,
ddr.DDR_Destination,
dpv.DPR_ID, dpv.DPV_Version
FROM TRU_DistanceZoneLink dzl,
TRU_DestinationRef ddr,
TRU_DistancePackageVrs dpv,
TRU_DistancePackageLink dpl,
(
SELECT DPV_ID FROM

(
SELECT DISTINCT DPR_ID, DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_STATUS ='A'


EXCEPT


SELECT DISTINCT
NEWDATE.DPR_ID,NEWDATE.DPV_ID,NEWDATE.DPV_EFFECTIVEDATE
FROM
(
SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_STATUS = 'A'
)
NEWDATE,

(
SELECT DISTINCT DPR_ID,DPV_ID, DPV_EFFECTIVEDATE
FROM TRU_DISTANCEPACKAGEVRS
WHERE DPV_EFFECTIVEDATE <= CURRENT TIMESTAMP - 300 DAYS
AND DPV_STATUS = 'A'
)
OLDDATE
WHERE
NEWDATE.DPR_ID = OLDDATE.DPR_ID AND
NEWDATE.DPV_EFFECTIVEDATE < OLDDATE.DPV_EFFECTIVEDATE

) VALID_DPV


) VALID_DPV
WHERE dzl.DDR_ID = ddr.DDR_ID
AND dzl.DPL_ID = dpl.DPL_ID
AND dpv.DPV_ID = dpl.DPV_ID
AND dpl.DPV_ID = VALID_DPV.DPV_ID

SQL0104N An unexpected token "VALID_DPV" was found following
"FECTIVEDATE )". Expected tokens may include: "<space>".
SQLSTATE=42601

Doug Doole

unread,
Sep 10, 2002, 10:17:30 AM9/10/02
to
That's odd - if it works in CLI it should work in C++. The only things I
can think of are:
- make sure you copied the statement cleanly.
- make sure you added the ";" to end the statement in C++.
0 new messages