SQL question with subselect

["Dave Odom" <DOdom1@xxxxxxxxxxxxxxx>]

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