SQL Concatenate |
Author |
Message |
|
Posted : 2009-01-12 21:30:41 |
Does anyone know how to concatenate 2 fields from a table in a SQL Select statement e.g. SELECT FirstName&" "&LastName FROM Customer (this example doesn't seem to work)? I have version 9 of the QODBC driver running XP. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-01-13 15:38:30 |
|
|
|
|
Posted : 2009-01-15 23:21:35 |
Thanks Tom I can now get the concatenation working however I am also trying to use this in a UNION statement to create a single field output that lists LastName concatenated with FirstName and Unioned with ComanyName.
QODBC simply reports wrong no. of columns or wrong data type. I have checked the data types are the same (varchar?) and the no. of cols are the same (I have added the n2.FullName for to ensure col no. is same, so not sure whats wrong?
SELECT {fn CONCAT(n1.LastName,{fn CONCAT(' ',n1.FirstName)})} AS CoName FROM Customer n1
UNION
SELECT {fn CONCAT(n2.CompanyName,{fn CONCAT(' ',n2.FullName)})} FROM Customer n2
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-01-16 09:57:31 |
Sorry but your UNION statement makes no sense. Simply do:
SELECT {fn CONCAT(n1.LastName,{fn CONCAT(' ',n1.FirstName)})} AS ContactName, {fn CONCAT(n1.CompanyName,{fn CONCAT(' ',n1.FullName)})} as CompanyName FROM Customer n1 |
|
|
|
|
Posted : 2009-01-22 21:03:03 |
Hi Tom, thanks for you reply. I apologise I got a bit mixed up with other stuff but your reply doesn't quite solve our problem.
To explain further, we have both private & company based customers and need to create a single column list of customer names based on both i.e. LastName & CompanyName. However, the list also needs to include FirstName after LastName so we cannot use FullName (hence the contatenation i.e. {fn CONCAT(LastName,{fn CONCAT(' ',FirstName)})} and the UNION clause is then used to include CompanyName.
The following statement works as required using MS Query against an ODBC Access database SELECT n1.LastName&' '&n1.FirstName AS ContactName FROM Customer n1 UNION SELECT n2.CompanyName AS ContactName FROM Customer n2
Giving Contact Name ACME Ltd Bryant Mark Fixtures Unlimited Smith John Wentworth Jill
However the QODBC equivalent? seems to fail reporting columns/data type errors? SELECT {fn CONCAT(n1.LastName,{fn CONCAT(' ',n1.FirstName)})} AS ContactName FROM Customer n1 UNION SELECT n2.CompanyName AS ContactName FROM Customer n2
Am I doing anything wrong or is there a different solution? (In my previous message the FullName in the UNION clause was left over from testing whether it was complaining about not having the same number of columns)
Also is it possible to include a 3rd concatenation to include MiddleName in the first SELECT statement as I seem to be having trouble with this too but again it works in MS Query?
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-01-23 10:14:18 |
Ok with MS Access the best approach is to import the QODBC Customer table into a MS Access table and then join the two MS Access tables. |
|
|
|
|
Posted : 2009-01-31 02:32:29 |
Hi Tom Thanks for all your help. I guess your'e saying that the driver can't do what we need so we will have to think of another solution. It would be useful if that capability could possibly be incorporated as a driver mod for the future. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-02-01 10:58:39 |
Sorry the CompanyName is already in the table, there's no need to do a UNION with the table again in the first place. Just do:
SELECT {fn CONCAT(n1.LastName,{fn CONCAT(' ',n1.FirstName)})} AS ContactName, {fn CONCAT(n1.CompanyName,{fn CONCAT(' ',n1.FullName)})} as CompanyName FROM Customer n1 |
|
|
|
|
Posted : 2009-02-05 02:15:16 |
Hi Tom
Sorry if Iam misunderstanding something, but as in the MS query example
I gave before we need the result in a single column as the recordset
needs to be passed to other functions that also need to take data from
other data sources in a single column recordset format.
The concatenation solution you kindly suggested creates a 2 column
recordset. However, we need to concatenate the LastName & FirstName fields
(for lexigraphical listing) and 'merge' the CompanyName field to cater
for customers that may be entered as private individuals (i.e. not
companies) AND customers that are entered only as company names. |
|
|
|