April 27, 2011 at 12:30 pm
I have the following Access 2007 Query and I am trying to translate it to TSQL:
SELECT tblApplicant.ApplicantID, [LastName] & ", " & ([Suffix]+", ") & [FirstName] & " " & ([MiddleName]+".") AS [Full Name]
FROM tblApplicant
WHERE (((tblApplicant.Inactive)=False))
ORDER BY [LastName] & ", " & ([Suffix]+", ") & [FirstName] & " " & ([MiddleName]+".");
Any ideas?
Art Lorenzini
Sioux Falls SD
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
April 27, 2011 at 12:41 pm
Assuming the the bracketed names are fields:
SELECT tblApplicant.ApplicantID, [LastName] + ', ' + [Suffix] + ', ' + [FirstName] + ' ' + [MiddleName] + '.' AS [Full Name]
FROM tblApplicant
WHERE tblApplicant.Inactive = 1
ORDER BY [LastName] + ', ' + [Suffix] + ', ' + [FirstName] + ' ' + [MiddleName] + '.'
Also, something like ([Suffix]+", ") might be a function, but I'm not sure.
Take a look, let me know.
CEWII
April 27, 2011 at 1:02 pm
I kinda see how its going... But the FullName is returning NULLS.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
April 27, 2011 at 1:05 pm
I see the issue. It's returning a null for every name that does not have a Suffix. Is there a way around that?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
April 27, 2011 at 1:15 pm
Hey look at that, I solved my own issue. Thanks for your help.
SELECT ApplicantID, ([LastName] + ', ' + ISNULL([Suffix],'') + ', ' + [FirstName] + ' ' + ISNULL([MiddleName],'') + '.') AS [Full Name]
FROM dbo.tblApplicant
ORDER BY [LastName] + ', ' + [Suffix] + ', ' + [FirstName] + ' ' + [MiddleName] + '.'
GO
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
April 27, 2011 at 1:28 pm
It is related to something called CONCAT_NULL_YIELDS_NULL, which basically means if you add up values and ANY of them are NULL then the result is NULL.
Here is the query two different ways, you;ll need to to see which performs better:
SELECT tblApplicant.ApplicantID,
'Full Name' = CASE WHEN [LastName] IS NULL THEN 'NLN, ' ELSE [LastName] + ', ' END
+ CASE WHEN [Suffix] IS NULL THEN '' ELSE [Suffix] + ', ' END
+ CASE WHEN [FirstName] IS NULL THEN 'NFN ' ELSE [FirstName] + ' ' END
+ CASE WHEN [MiddleName] IS NULL THEN '' ELSE [MiddleName] + '.' END
FROM tblApplicant
WHERE tblApplicant.Inactive = 1
ORDER BY CASE WHEN [LastName] IS NULL THEN 'NLN, ' ELSE [LastName] + ', ' END
+ CASE WHEN [Suffix] IS NULL THEN '' ELSE [Suffix] + ', ' END
+ CASE WHEN [FirstName] IS NULL THEN 'NFN ' ELSE [FirstName] + ' ' END
+ CASE WHEN [MiddleName] IS NULL THEN '' ELSE [MiddleName] + '.' END
SELECT tblApplicant.ApplicantID,
'Full Name' = COALESCE( [LastName] + ', ', 'NLN' )
+ COALESCE( [Suffix] + ', ', '' )
+ COALESCE( [FirstName] + ' ', 'NFN' )
+ COALESCE( [MiddleName] + '. ', '' )
FROM tblApplicant
WHERE tblApplicant.Inactive = 1
ORDER BY COALESCE( [LastName] + ', ', 'NLN' )
+ COALESCE( [Suffix] + ', ', '' )
+ COALESCE( [FirstName] + ' ', 'NFN' )
+ COALESCE( [MiddleName] + '. ', '' )
NLN means No Last Name
NFN means No first Name
Mileage may vary..
CEWII
April 27, 2011 at 1:50 pm
That was perfect thanks.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply