Re: SQL question with subselect

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

I think you need some kind of join with a temporary result set. This should 
work since V4R4:

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

You don't really need to qualify the join field names.

HTH
Vern

At 02:43 PM 2/13/2004 -0600, you wrote:
> 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?