Re: SQL Error message on Line 2.

I'm sure there are several ways.
One way I'm thinking is using a cross join.
The way I like to do it is using CTEs.
I would use the Explain feature in Run SQL Scripts to determine which is a
better performer:

with CVC_orders as (
select otord#
from astccdta.oetra99
where ottrnc = 'CVC'),

GRE_orders as (
select otord#
from astccdta.oetra99
where ottrnc = 'GRE')

select otord# from
CVC_orders
join GRE_orders using (otord#) ;


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144


On 1 February 2016 at 21:00, Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
wrote:

HI I Am getting error here it says line 2. SQL0199 - Keyword SELECT not
expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH ORDER UNION EXCEPT
OPTIMIZE.

I am not sure how to get that message file. All I am trying to do is select
those order
rows which have both CVC and GRE

Otord# Ottrnc more fields
123 AAA
123 CVC
123 GRE

so we want here the second and third rows only.


SELECT *
FROM astccdta.oetra99
WHERE (otord#) IN (
SELECT (otord#)
FROM astccdta.oetra99
WHERE ottrnc = 'CVC'
)
AND (otord#) IN (
SELECT (otord#)
FROM astccdta.oetra99
WHERE ottrnc = 'GRE'
)
SELECT otord#
from astccdta.oetra99
where ottrnc in ('CVC', 'GRE')
group by otord#
having count(distinct ottrnc) = 2
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.