October 1, 2015 at 11:27 am
Is there a way I can remove the distinct statement from the Select statement and bring it down the where clause? as I don't want to display application_id in my results, if I just remove the DISTINCT clause it messes up the result set.
Current Select statement
SELECT
DISTINCT AP.application_id
,CAST('6' as varchar(20)) AS [DocumentCode]
,CAST('M' as varchar(20)) AS [CompanyName]
,AP.first_name + ' ' + AP.last_name AS [MailingName]
,CA.house_num + ' ' + CA.street_name + ' ' + CA.apt_num AS [MailingStreet]
Current FROM statement
FROM applicant AS AP
JOIN credit_application AS CAP
ON AP.application_id = CAP.application_id
AND CAP.decision_status = 'D'
October 1, 2015 at 11:36 am
DISTINCT applies to the whole result set not just for that one column. Just remove 'application_id' from the select statement and see what you get. That being said if we had any examples, it would go a long way to help.
October 1, 2015 at 11:42 am
Thank you
October 1, 2015 at 11:56 am
No problem, I also noticed a potential problem with your string concatenation.
CA.house_num + ' ' + CA.street_name + ' ' + CA.apt_num AS [MailingStreet]
If there is any chance that apt_num could end up being NULL and seeing as not everyone lives in an apartment it's a pretty safe to say. You might end up with a completely NULL result. By default CONCAT strings that are NULL will result in the whole thing being NULL. You might want to use something like
ISNULL(CA.apt_num, '')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply