Re: obtain a record count using embedded SQL

[Alan Shore <AlanShore@xxxxxxxx>]

Thanks for your reply Luis
just after I transmitted my initial e-mail, it struck me what I was looking
for
here it is

Exec SQL Select count(distinct(field2)) Into :Reccount
  from FileA
  where Field1 = :HoldField1;



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill


                                                                           
             Luis Rodriguez                                                
             <luisro58@xxxxxxx                                             
             om>                                                        To 
             Sent by:                  Midrange Systems Technical          
             midrange-l-bounce         Discussion                          
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>           
                                                                        cc 
                                                                           
             09/30/2010 12:31                                      Subject 
             PM                        Re: obtain a record count using     
                                       embedded SQL                        
                                                                           
             Please respond to                                             
             Midrange Systems                                              
                 Technical                                                 
                Discussion                                                 
             <midrange-l@midra                                             
                 nge.com>                                                  
                                                                           
                                                                           




Alan,

If I understand correctly your problem, you need something like:

SELECT COUNT(field1), COUNT(Distinct field2)

HTH,

Luis Rodriguez
IBM Certified Systems Expert â eServer i5 iSeries
--



On Thu, Sep 30, 2010 at 11:35 AM, Alan Shore <AlanShore@xxxxxxxx> wrote:

> Morning all
> I am using the following embedded SQL statement to obtain a record count
>
> Exec SQL Select count(*) Into :Reccount
>  from FileA
>  where Field1 = :HoldField1;
>
> Now this gives me the correct number
> But (there's ALWAYS a but)
> I need to obtain the number of records  where Field1 = :HoldField1 AND
> field2 is distinct
>
> for example
>
> Field1                          Field2
> 1                               1
> 1                               1
> 1                               2
> 1                               3
> 1                               4
> 1                               4
>
> The first count (non distinct) would be 6
> the distinct count would be 4
>
> my mind has gone a complete blank
>
> If anyone has any ideas, as always, they would be MUCH appreciated
>
> Thanks in advance
> Alan Shore
> Programmer/Analyst, Direct Response
> E:AShore@xxxxxxxx
> P:(631) 200-5019
> C:(631) 880-8640
> "If you're going through Hell, keep going" - Winston Churchill
> --
> 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.
--
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.