RE: SQL question with subselect -- MIDRANGE-L

Back from dry walling & oh so stiff & sore.

I need to clarify part of my message.

AVM is the vendor master file & ABR is the bank reconciliation file.  
So the idea was the total the number of records in the ABR file by vendor to
get my count of checks written by vendor & the AVM would supply all the
master info for the vendor.
<SNIP>
select abvnd, count(*) from bpcsf/abr where absts <> 'W' group by abvnd
<SNIP>

I am out of the office most of today so I will go over the replies later
today & thanks to all that answered.

Thanks - Dennis.

Dennis Munro

"I love deadlines. I especially like the whooshing sound they make as they
go flying by."
Dilbert's Words Of Wisdom

Badger Mining Corporation
www.badgerminingcorp.com
dmunro@xxxxxxxxxxxxxxxxxxxx
(920) 361-2388   Ext.141

-----Original Message-----
From: Dennis Munro [mailto:DMunro@xxxxxxxxxxxxxxxxxxxx]
Sent: Friday, February 13, 2004 2:43 PM
To: Midrange List (E-mail)
Subject: SQL question with subselect

V5R2M0 - latest cume

I'm able to do "simple" things with SQL & I am trying to use it for normal
database queries so hence the following question.

>From a user they want the following:

List all active suppliers (name & address), date last paid, & number of
checks issued to that vendor.

The following is a SQL statement that I am trying to make work.

select vendor, vndnam, vndad1, vndad2, vndad3, vstate, vpost, vdtlpd from
bpcsf/avm where vendor in (select abvnd, count(*) from bpcsf/abr where absts
<> 'W' group by abvnd) order by vendor

<Snip - text from error message>
For all predicates, except the EXISTS predicate, the subselect of a
predicate can have only one result column specified in its SELECT list
<SNIP>

In a group by situation in a sub query, it seems that I can have only one
column selected.  Obviously without the count by vendor, the report is
useless so how do I get that count & vendor number?

When I replace the in with an exists, the error message is: "The keyword
EXISTS was not expected here."

The sub select works just fine as a standalone SQL statement but put in a
subselect, it does not work.

So the question is, how do I make the two work asz one?  All the examples
I've found in the two text books I've got do not cover this type of
situation (what's new) so where would I look to find the answer? 

I could have created an ABR summary file via QUERY/400 but that's "cheating"
when I'm trying to learn & use SQL & just joined the ABR & my summary
tables.  Over & done with an hour ago.  I looked at views but there was not
much info on them, just some very basic info.

I am out of here for the weekend.  Time to go help a kid finish dry walling
his basement.  With take this up Monday morning.

Thanks - Dennis.

Dennis Munro

"I love deadlines. I especially like the whooshing sound they make as they
go flying by."
Dilbert's Words Of Wisdom

Badger Mining Corporation
www.badgerminingcorp.com
dmunro@xxxxxxxxxxxxxxxxxxxx
(920) 361-2388   Ext.141

The information contained in this e-mail message is confidential information
intended only for the use of the individual or entity named above. If the
reader of this message is not the intended recipient, or the employee or
agent responsible to deliver it to the intended recipient, you are hereby
notified that any dissemination, distribution or copying of this
communication is strictly prohibited. If you have received this
communication in error, please immediately notify us by telephone, and
return the original message to us at the below address via the U.S. Postal
Service, and delete it from your computer.  Badger Mining Corporation  P.O.
Box 328  Berlin, WI 54923  920-361-2388
_______________________________________________
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.
The information contained in this e-mail message is confidential information
intended only for the use of the individual or entity named above. If the
reader of this message is not the intended recipient, or the employee or
agent responsible to deliver it to the intended recipient, you are hereby
notified that any dissemination, distribution or copying of this
communication is strictly prohibited. If you have received this
communication in error, please immediately notify us by telephone, and
return the original message to us at the below address via the U.S. Postal
Service, and delete it from your computer.  Badger Mining Corporation  P.O.
Box 328  Berlin, WI 54923  920-361-2388