RE: SQL help with EXISTS -- MIDRANGE-L

OK I see your point now.

The reason I am using EXISTS:

The SQL I showed was for a few test cases.

For prod, I need to extract all rows in the file that had any user changes today.

I will identify any user chgs with journal records.

So, the EXIST clause will be looking at all journal records for this PF when this thing is in prod.

Is there a better way to do this?

Thanks!

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Thursday, December 06, 2012 3:12 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL help with EXISTS

Hi Joel

Charles explained some of this. I'll put my responses inline.

On 12/6/2012 2:20 PM, Stone, Joel wrote:

Vern:

OK please explain:

The following returns the 6 rows that I want and works perfectly.

select * from miscchg A
where (a.AMSCHGC in ('S','B') or a.AMSCHGA in ('S','B'))
and
exists
(select ASHPNBR, ABLDNGD, AMSCHRG from MISCCHG b
where a.ASHPNBR = b.ashpnbr and a.ABLDNGD = b.ABLDNGD
and
ashPNBR = 'BN460619' and ABLDNGD = '20120817'
group by ASHPNBR,ABLDNGD, AMSCHRG )
order by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG

This inner SELECT is different from the other - it has references to the
outer table - the a.ASHPNBR = b.ASHPNBR is an example. What happens
here, conceptually, is that the row from A is read, then the values of
its columns that you refer to in the INNER SELECT are put there, and
that WHERE clause is evaluated and you get a result from it - if there
is at least 1 row in THAT result, the EXISTS is true. If no rows there,
EXISTS is false.

The results of that INNER SELECT are tossed away.

I removed the GROUP BY, you are correct, same result.

If I remove the entire EXISTS clause, millions of rows are returned.

I would expect that - you are getting everything in the MISCCHG that
matches the WHERE clause of the OUTER SELECT.

How can what you and others are stating be accurate if the result set with the EXISTS returns 6 rows, and without EXISTS returns millions?

Are you suggesting that the EXISTS "does nothing to change the final result" ? But yet it does change the final result.

Your other posts have a different SELECT statement under the EXISTS
predicate - THAT SELECT has no reference to the OUTER MISCCHG (A) table,
so it does not limit anything - hence, you get everything - the same as
if you had no EXISTS at all.

Apples to oranges!!

What am I missing?

This is not about missing anything, but I don't think you even need the
EXISTS - if you put this condition into the A-table WHERE clause and
omit the EXISTS completely,do you get the same results? The 6 records?

ashPNBR = 'BN460619' and ABLDNGD = '20120817

See, you have those comparison of A.? = B.? - that is again a trivial
TRUE, because you are comparing items in the same table - there MIGHT be
a use for this, but I don't think you need it here.

Thanks

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Thursday, December 06, 2012 2:01 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL help with EXISTS

To add to Charles' reply - your SELECT inside the parentheses - after
the EXISTS - has records. Therefore, records EXIST in the result set of
THAT query. So the EXISTS predicate is unconditionally TRUE in this case
- you get the result that is the outer SELECT with no further conditioning.

Your EXISTS does nothing to change the final result, because it is like
testing for 1 = 1; it is always true.

You need to refer, inside the EXISTS, to a column in the outer, or main,
SELECT. And, as I said earlier, it is basically nonsense to have an
EXISTS test over the same table you are querying.

And, because the result of the internal query are discarded, you have no
need for grouping, without the HAVING clause with a condition on some
aggregate function such as COUNT(*).

HTH
Vern

On 12/6/2012 11:54 AM, Charles Wilt wrote:

No, he's saying it's not functioning the way you think it is.

Charles

On Thu, Dec 6, 2012 at 12:52 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

So - you are stating that the SQL I presented CANNOT function? But it
does. It returns a set based on the EXISTS .

I don't understand.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, December 06, 2012 11:50 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL help with EXISTS

On 06 Dec 2012 11:06, Stone, Joel wrote:

I don't think that is correct. It seems to do the subselect (return
a set of rows) and THEN join the result to the outer select. A few
responses have stated what you said - that EXISTS simply returns TRUE
or FALSE. But I think that it must do this for each row in the outer
select in the case where a join is occurring?

Maybe I am wrong, I don't know SQL and it is elusive and powerful and
complex so I don't understand all the subtleties.

Dan Kimmel on Thursday, December 06, 2012 11:01 AM wrote:

The EXISTS says "are there any". You get the same results in the
outer select whether there's one or many results from the
subselect.

The documentation states explicitly that the "values returned by the
fullselect [of the EXISTS predicate] are ignored" and states clearly
that the only possible results are True and False. No type of matching
join can be done without some values against which to compare; that
would leave only a cartesian product join, and that certainly would be
of no value to the query.

IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Language elements -> Predicates
_i EXISTS predicate i_

http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzexists.htm
"The EXISTS predicate tests for the existence of certain rows.

>>-EXISTS--( fullselect )---------------------------------><

The fullselect may specify any number of columns, and

* The result is true only if the number of rows specified by the
fullselect is not zero.
* The result is false only if the number of rows specified by the
fullselect is zero.
* The result cannot be unknown.

The values returned by the fullselect are ignored.
..."

--
Regards, Chuck
--
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.

________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
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.