July 13, 2018 at 6:44 am
Hi all
I'm currently having a couple of problems with the script below. What I'm trying to achieve is to concatenate the converted DOB column with the FIRSTNAME and KEYNAME columns to form FULLNAME and then use the FULLNAME column in a WHERE clause.
However, if I run the script as it's shown below then I get an 'Invalid column name FULLNAME' error. Also, if I try to concatenate the DOB column into the FULLNAME column then I get a similar error saying that DOB doesn't exist. I appreciate that DOB and FULLNAME are both not real column names, but I'm unsure how to get around this.
SELECT SERIALNUMBER, CONVERT(VARCHAR(10), [DATEOFBIRTH], 103) AS 'DOB', FIRSTNAME + ' ' + KEYNAME AS 'FULLNAME'
FROM CONTACT
WHERE (CONTACTTYPE = 'INDIVIDUAL') AND FULLNAME IN ('Sarah Smith' ,'Bob Smith')
ORDER BY FULLNAME
Any help would be much appreciated, thank you.
Best wishes
Jon
July 13, 2018 at 7:01 am
j.clay 47557 - Friday, July 13, 2018 6:44 AMHi all
I'm currently having a couple of problems with the script below. What I'm trying to achieve is to concatenate the converted DOB column with the FIRSTNAME and KEYNAME columns to form FULLNAME and then use the FULLNAME column in a WHERE clause.However, if I run the script as it's shown below then I get an 'Invalid column name FULLNAME' error. Also, if I try to concatenate the DOB column into the FULLNAME column then I get a similar error saying that DOB doesn't exist. I appreciate that DOB and FULLNAME are both not real column names, but I'm unsure how to get around this.
SELECT SERIALNUMBER, CONVERT(VARCHAR(10), [DATEOFBIRTH], 103) AS 'DOB', FIRSTNAME + ' ' + KEYNAME AS 'FULLNAME'
FROM CONTACT
WHERE (CONTACTTYPE = 'INDIVIDUAL') AND FULLNAME IN ('Sarah Smith' ,'Bob Smith')
ORDER BY FULLNAMEAny help would be much appreciated, thank you.
Best wishes
Jon
This has to do with the logic processing order. The WHERE clause is evaluated before the SELECT clause, so it's evaluated before you define the alias. There are a couple of ways to get around this. I prefer using a CROSS APPLY to define the alias, but if you are using a CTE for some other reason, you can also define it in the CTE.SELECT SERIALNUMBER, CONVERT(VARCHAR(10), [DATEOFBIRTH], 103) AS [DOB] , [FULLNAME]
FROM CONTACT
CROSS APPLY( VALUES(FIRSTNAME + ' ' + KEYNAME) ) fn([FULLNAME])
WHERE (CONTACTTYPE = 'INDIVIDUAL') AND FULLNAME IN ('Sarah Smith' ,'Bob Smith')
ORDER BY FULLNAME
Also, I think it's a bad idea to use single quotes for column names, because it's too easily confused with strings. I use square brackets to delimit my column names when necessary/desired.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 13, 2018 at 7:43 am
Hi Drew
Perfect thanks!
The only issue that I have now is trying to also add DOB into the CROSS APPLY( VALUES(FIRSTNAME + ' ' + KEYNAME ) ) fn([FULLNAME]) part of the statement.
If I try to add it after the + KEYNAME section I get an error saying that DOB doesn't exist. If I instead try to use DATEOFBIRTH then I get an error saying Conversion failed when converting date and/or time from character string.
Best wishes
Jon
July 13, 2018 at 8:47 am
SELECT SERIALNUMBER, [DOB] , [FULLNAME]
FROM CONTACT
CROSS APPLY( VALUES(CONVERT(VARCHAR(10), [DATEOFBIRTH], 103), FIRSTNAME + ' ' + KEYNAME) ) fn([DOB],[FULLNAME])
WHERE (CONTACTTYPE = 'INDIVIDUAL') AND FULLNAME IN ('Sarah Smith' ,'Bob Smith')
ORDER BY FULLNAME
Does this work?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 13, 2018 at 8:55 am
Hi Drew
No, unfortunately it produces three columns called SERIALNUMBER, DOB AND FULLNAME.
So, it doesn't include DOB or DATEOFBIRTH in the FULLNAME concatenation.
Many thanks
Jon
July 13, 2018 at 9:03 am
How is the data stored? Based on what you asked, you want to concatenate DOB to Fullname and then search by it. Something like:
where FullName in ('01/01/2000 Sarah Smith' ,'1/1/1990 Bob Smith')??
do you have any sample data we can work with?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 13, 2018 at 9:18 am
Looking at the original code posted by the OP, DOB is not being concatenated with FirstName and LastName.
July 13, 2018 at 9:24 am
Thank you, yes, I want to make the columns FIRSTNAME, KEYNAME and DATEOFBIRTH into another column called FULLNAME. I want to do this only in the SELECT statement and not actually write it to the database of course.
I then want to create a WHERE clause that is e.g. WHERE (CONTACTTYPE = 'INDIVIDUAL') AND FULLNAME IN ('Sarah Smith 01/01/1971' ,'Bob Smith 02/02/1984')
The FIRSTNAME, KEYNAME and DATEOFBIRTH columns are all in the CONTACT table. FIRSTNAME and KEYNAME are Varchar, whilst DATEOFBIRTH is datetime.
Best wishes
Jon
July 13, 2018 at 9:44 am
why do you want to concatenate these 3 columns in the where clause anyway? Are you joining against a table where it concatenated together? Otherwise, why not just put in temp table and join to it?
Create table #T (FirstName nvarchar(30), KeyName nvarchar(30), DOB date)
insert into #T
values
('Sarah','Smith','1/1/1971'),
('Bob','Smith','2/2/1984')
select a.*
from TableA a
join #T t
on t.FirstName = a.FirstName
and t.KeyName = a.KeyName
and t.DOB = a.DOB
where a.Contacttype = 'Individual'
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 13, 2018 at 10:00 am
Thanks Mike.
I'm trying to concatenate them together so that I can compare them to a spreadsheet that is concatenated in the same way. I can then using the EXACT clause in Excel to ensure that they're the same people.
If I can't get it to work the way I want it then I'll use your query.
Best wishes
Jon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply