Re: SQL question with subselect

with

qry1 (abvnd, vcount) as
    (select abvnd, count(*) from bpcsf/abr where absts <> 'W' group by
abvnd)

select  vendor, vcount, vndnam, vndad1, vndad2, vndad3, vstate, vpost,
vdtlpd from
 bpcsf/avm, qry1 where vendor = abvnd

should do the trick

cheers
Colin.W


----- Original Message -----
From: "Dennis Munro" <DMunro@xxxxxxxxxxxxxxxxxxxx>
To: "Midrange List (E-mail)" <MIDRANGE-L@xxxxxxxxxxxx>
Sent: Friday, February 13, 2004 8:43 PM
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.
>
>