Re: SQL Error message on Line 2.

First, the SQL isn't structured well. The reason his wouldn't compile was because he had two selects.

The first select with a where clause with subselects. After the subselect he started another select without a union clause

Second, the first part of the SQL statement said to select all fields where order number is in a subselect list orders from the same table where the transaction type equaled GRE. That would have been the same as saying transaction code = GRE. Without using a subselect. The he had an and on the where clause. This time doing the same thing. Where order number was in a subselect of the same table where transaction code equal to CVC.

So the first select statement could have been pared down to

Select *
From astccdta.oetra99
Where ottrnc in('CVC','GRE')

So now he has a list of orders. The result set could have duplicate order numbers, for reasons we cannot answer here other than each order can have multiple transaction types.

The second query was trying to produce a unique set of order numbers, but only when an order had both transaction codes CVC and GRE.

I cannot think of any good reason to select records based on a subselect of itself.




Sent from my iPhone

On Feb 2, 2016, at 1:56 PM, Kevin Bucknum <Kevin@xxxxxxxxxxxxxxxxxxx> wrote:

Is ottrnc a unique field? He's looking for sets that have both CVC and
GRE. Could there be two CVC's?




Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Joe Pluta
Sent: Tuesday, February 02, 2016 12:38 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Error message on Line 2.

Well played, sir. I always enjoy when easier is better. :)

You're thinking too hard. All you need is.

SELECT otord#
from astccdta.oetra99
where ottrnc in ('CVC', 'GRE')
group by otord#
having count(distinct ottrnc) = 2

Sent from my iPhone

On Feb 1, 2016, at 9:00 PM, Hoteltravelfundotcom

<hoteltravelfun@xxxxxxxxx> wrote:

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.
--
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.