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 - 641 days ago
Last viewed on: 19 Feb 2018 (1176 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)

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 9 months 5 days 7 hours 20 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 9 months 5 days 3 hours 14 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 9 months 3 days 5 hours 6 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 9 months 3 days 1 hours 45 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.