Re: SQL Error message on Line 2.

[Glenn Gundermann <glenn.gundermann@xxxxxxxxx>]

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.