Concatenation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Concatenation

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-22 : 11:46:46

Hi,

I'm trying to create a unique clientid by concatenating the current clientid with the branchid. My sql is shown below, but it returns the following error when it is ran:

multiple rows in sigleton select.

I think it is because it is returning more than one value when it is expecting only one value.

The query is as follows:

update client_import
set clientid=
(select client_import.branch_id ||"_"|| client_import.clientid
from client_import)

Is there another way I can do this?

Cheers

Paul

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-01-22 : 11:58:33

Hi Paul.

Concatenation is done using the + operator. Also, you normally use ' to identify strings.

Try this:

update client_import
set clientid=
(select client_import.branch_id +'_'+ client_import.clientid
from client_import)

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-22 : 12:03:29

Hi Andraax,
Thanks for your post, unfortunatley when I use your SQL i get the same error message:

"multiple rows in singleton select"

Any other ideas?

Cheers

Paul

P.S I am using Interbase if this makes a difference!

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-22 : 12:10:50

UPDATE client_import
SET clientid = branchid + '_' + clientid

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-01-22 : 12:13:20

Aha... Interbase... That explains why I didn't recognize the error message.

The error is obviously that the select statement returns too many rows. If Onamuji's code doesn't work, try limiting your select statement to only include one row.

PS. This is an MS SQL server forum and T-SQL is a little different from other versions of SQL. Which is why I'm fubmling in the dark here :)

Edited by - andraax on 01/22/2003 12:14:55

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-22 : 14:42:47

Hi,
Onamuji's method didn't work either although it did give a different error:

conversion error from string "_"

Any other ideas?

Cheers

Paul

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-22 : 14:45:19

POST the DDL for the table and we could give you a more exact solution ...

try

UPDATE client_import
SET clientid = clientid + branch_id

why the need for the joining of the ids anyway... they are unique aren't they?

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-22 : 15:20:09

Hi Onamaju,

The DDL is as follows:

CREATE TABLE CLIENT (
CLIENTID INTEGER NOT NULL,
TITLE DTITLE,
FIRST DNAME,
MIDDLE DNAME,
LAST DNAME,
LASTSOUNDEX DSOUNDEX,
DOB DATE,
ADDRESS1 DADDRESSLINE,
ADDRESS2 DADDRESSLINE,
ADDRESS3 DADDRESSLINE,
ADDRESS4 DADDRESSLINE,
ADDRESS5 DADDRESSLINE,
ADDRESS6 DADDRESSLINE,
POSTCODE DPOSTCODE,
HOMETELEPHONE DTELEPHONE,
WORKTELEPHONE DTELEPHONE,
MOBILETELEPHONE DTELEPHONE,
EMAIL DURL,
GENDER DGENDER,
DOCTORNAME DDOCTORNAME,
DOCTORTELEPHONE DTELEPHONE,
SESSIONDISCOUNT DPERCENTAGE default 0,
COURSEDISCOUNT DPERCENTAGE default 0,
PRODUCTDISCOUNT DPERCENTAGE default 0,
CREDITLIMIT DCURRENCY default 0,
PHOTO BLOB sub_type 0 segment size 80,
NOTES BLOB sub_type 0 segment size 80,
URGENTNOTES BLOB sub_type 0 segment size 80,
EXCLUDEFROMMAILSHOTS DBOOLEAN default "F",
DATEENROLLED DATE default "now",
BRANCHID INTEGER,
SKINTYPE INTEGER,
SOURCEID INTEGER);

This would explain why the "_" doesn't work, but if you take it out it still doesn't work. The reason why I have to concatenate these two fields is because the clientid's for each branch are the same therefore to store all clients in one table, the clientid needs to be concatenated with the branch id.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-22 : 15:25:29

no you don't, just make a composite key ...

PRIMARY KEY (branchid, clientid)

that is your unique value isn't it?

anyway... you can't add a character value to an integer field...

or just add a calculated field

CAST(BranchID AS VARCHAR) + '_' + CAST(ClientID AS VARCHAR) AS ClientPK

I'm not sure if you can create an index on a computed column or not though...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-22 : 15:28:14

Well, as mentioned, we focus on SQL Server on this forum so it will be hard for us to come up with a solution that works with your product.

As far as the error message, the subquery will return more than one row because it is not correlated to the UPDATE statement. Nor do you need a subquery as far as I can see, how about this:

update client_import
set clientid= branch_id ||"_"|| clientid

That SHOULD work, it fits the ANSI standard at least. Failing that, I think you'll need to add another column, do the concatenation there, and then UPDATE the clientid column to the new column's value (or use the new column as primary key) Updating a primary key is kinda tricky anyway.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-23 : 11:44:52

Thanks robvolk, it worked.
Thanks to all who posted!!

Best Regards

Paul Rowling

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources