Midrange News for the IBM i Community


Posted by: Viking
Information Systems
CA
Help with SQL statement please?
has no ratings.
Published: 16 May 2016
Revised: 19 May 2016 - 516 days ago
Last viewed on: 16 Oct 2017 (989 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Help with SQL statement please? Published by: Viking on 16 May 2016 view comments(4)

Hi, hoping for some quick guidance on what should be a very easy SQL statement...

I would like to find all accounts in a table where there is more than one record in the table for that account where the end date is 0.  Each account should only have one record in the table without an end date, so I'm trying to find those accounts that have more than one record with no end date.

Something like:

select account
from myfile
where enddate = 0
group by account
having count(*) > 1
order by account

But this will also find accounts with more than one record as long as one of them has an enddate = 0, and I only want to see those where multiple records for that account have enddate = 0.

Any help would be appreciated... thanks!

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Help with SQL statement please?
Posted: 1 years 5 months 1 days 16 hours 8 minutes ago
select account                          
from myfile                             
                                        
group by account, enddate               
having count(*) > 1 and enddate = 0     
order by account 
Posted by: Viking
Premium member *
CA
Comment on: Help with SQL statement please?
Posted: 1 years 5 months 1 days 12 hours 2 minutes ago

Yes, that was it... thanks Bob!

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Help with SQL statement please?
Posted: 1 years 4 months 30 days 13 hours 54 minutes ago

WHERE condidtions are applied before GROUPing. The query as you originally presented it should work as is. Is there more that you're not showing us? Some JOINs maybe?

Posted by: Viking
Premium member *
CA
Comment on: Help with SQL statement please?
Posted: 1 years 4 months 30 days 10 hours 33 minutes ago

Dale, no this is the whole statement.  Mine also showed accounts with just one open record (no end date) as long as there were multiple records for the account, but Bob's only shows accounts with multiple OPEN records.  Bob's produces the result I needed.