× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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?



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.