RE: SQL question with subselect -- MIDRANGE-L

Dennis,

How about:
with VndSlt (Vendor, Trn_count) as
(       select  abvnd, count(*) 
        from            bpcsf/abr 
        where   absts<> 'W' 
        group by abvnd
)

select  a.vendor, a.vndnam, 
                a.vndad1, a.vndad2, a.vndad3, 
                a.vstate, a.vpost, a.vdtlpd,
                b.Trn_count 
from            bpcsf/avm       a inner join
                VndSlt  b on (a.vendor = b.Vendor)      
order by        a.vendor


Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



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