June 2, 2009 at 7:55 am
I'm getting multiple rows from a select with group by that should return only one row. There is one field in our ACCOUNT table that I need to check -- is_client_conversion so I do a left outer join to that just before the where clause below. Adding this to the where clause n.is_client_conversion is not null gets the one expected row, but with a count that is smaller. The count(*) is 3260 if I include the "is not null" item. Without that I get two rows totalling 3290.
declare @rundate datetime
set @rundate=CONVERT(datetime, FLOOR(CONVERT(float(24), GETDATE())) )
SELECT o.short_name as tbl_i_client_id, a.OWNERSHIP_DOC_ISSUING_STATE_ABBR as tbl_i_state,
@rundate as tbl_i_inv_date,DOC.MEDIA_TYPE AS tbl_i_Title_Type,
case n.is_client_conversion WHEN 1 then 'Y' else ' ' end as tbl_i_conversion,COUNT(*) AS tbl_i_Inv_Quantity
FROM
ACCOUNT_OWNERSHIP_DOC_SUMMARY a
inner JOIN organization o ON a.client_id = o.organization_id
inner JOIN ownership_doc DOC ON a.ownership_doc_id = DOC.ownership_doc_id LEFT OUTER JOIN
account n on n.account_id=a.account_id
WHERE n.is_client_conversion is not null and o.short_name in ('1STGAIN') and o.concrete_type='Fdi.Po.client'
AND DOC.media_type in('ELECTRONIC','PAPER') AND DOC.document_custodian = 'FDI' AND
DOC.business_object_status = 'OPEN'
and ( (a.status<>'CLOSED_DE' and doc.media_type='PAPER') OR (a.status in('NO_ACCOUNT_PL','PERFECTED_PT') AND doc.media_type='ELECTRONIC'))
GROUP BY o.short_name,a.OWNERSHIP_DOC_ISSUING_STATE_ABBR,doc.media_type,n.is_client_conversion
order by o.short_name, doc.media_type,a.OWNERSHIP_DOC_ISSUING_STATE_ABBR
June 2, 2009 at 8:18 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 10:09 am
It will definitely be easier to provide a correct solution if you read the article Gail provided, but I do have one recommendation.
Try changing your group by to use:
case n.is_client_conversion
WHEN 1 then 'Y'
else ' '
end
instead of:
n.is_client_conversion
I think you'll get the correct results then.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 2, 2009 at 11:03 am
Yes Jack, that worked. Thanks. The table definitions etc would be huge so I'm glad we didn't need that much detail.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply