Re: SQL question with subselect -- MIDRANGE-L

If I may, let me say that I think this approach will be slower than what is now available with table expressions, whether common or nested. Someone else had something similar to this, which is the way it had to be done before the table expressions were available (around V4R4). The reason this is slower is, grouping is not done until the result set is built, and that result set is a product of the records in all the files - 3 in this case. With a nested table expression, as others have offered, grouping can be done early in the process, esp. as in the original question's statement.

These table expressions are like temporary views. Too many nested views can perform very badly, because the deeper ones will not be helped by existing indexes, usually. But 1 level, as in this case, probably are fine.

YMMV
Vern

At 04:34 PM 2/13/2004 -0700, you wrote:

Dennis,

If I understand you correctly, my answer would be to query the two
files in your outer query and do your count of the object at that
outside query level.  Here is some sample code that, I think, will give
you an idea of what you can do:

 SELECT c.utcsid, c.utlcid, b.utfrcd, a.utcycn,
 count(b.utlcid)
                             FROM
     "CXLIB"/"UT100AP" a,
     "CXLIB"/"UT145AP" b,
     "CXLIB"/"UT220AP" c
       WHERE
       (a.utlcid = b.utlcid) and (B.utlcid = c.utlcid)
       and
       (C.utAjur = 'TU' and  b.utbljd = 'TU')
         and
       (C.utAIo = 'I' and b.utblio = 'I')
       and
       (C.utsvc = 'MS' AND  B.utsvc = 'MS')
       and
          (c.UTSsts = 'A' AND  B.UTCSTS = 'A')
         AND
         (C. utacls in ('R','R2') AND B.UTCLAS IN ('R','R2'))
        and
        (B.utfRcd like 'TCP%' and c.utafcd like 'TCP%')
     and
C.UTLCID NOT IN (SELECT C.UTLCID FROM CXLIB/UT220AP WHERE c.UTSVC <>
'MS'
    AND
    B.UTLCID NOT IN
   (SELECT B.UTLCID FROM CXLIB/UT145AP WHERE B.UTSVC <> 'MS'))
     group by b.utlcid,c.utcsid, c.utlcid, b.utfrcd, a.utcycn
  -- order by c.utlcid, c.utcsid

Hope this helps and is not "clear as mud".

Sincerely,

Dave Odom
Tucson Water
Tucson, AZ

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.