Concatenating fields

Question

Concatenating fields

Haxby

SSC Enthusiast

Points: 141
More actions
November 30, 2006 at 10:12 am

#117826

Hi I am inserting data into a table using the following query
Select ADD_OP, ADD_DATE, EDIT_OP, EDIT_DATE,'n','202', LR_REV,END_DATE, '0', W_SHIFT, LR_REX FROM W_SOURCE INNER JOIN LR ON LR_RES = W_RES
I would like to concatenate LR_REV with one unique value. If I have played around and wondered if I added an inner join say select top 100 percent to the query would that give me the field I could concatenate to LR_REV?.
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I'm not real sure exactly what you are trying to do, but it sounds like a great place for a scalar function. When you say "I would like to concatenate LR_REV with one unique value", what exactly do you mean? What type of unique value? Can you give an example and include some sample data?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Haxby SSC Enthusiast Points: 141 More actions · Answer 2

ADD_OP, ADD_DATE, EDIT_OP, EDIT_DATE,'n','202', LR_REV,END_DATE, '0', W_SHIFT, LR_REX

The basic data would be :-

SYS, 01/12/2006, SYS, 01/12/2006,n,2002,MCHN1,02/12/2006,0,SHIFT1,END

I would like the proposed data to be :-

SYS, 01/12/2006, SYS, 01/12/2006,n,2002,MCHN1 + UNIQUE ID ,02/12/2006,0,SHIFT1,END

Is this possible?

ijaz SSCommitted Points: 1912 More actions · Answer 3

what would you like for the UNIQUE ID. Should it be a numeric in the increasing order or it would be any alpha numeric value.

cheers

Haxby SSC Enthusiast Points: 141 More actions · Answer 4

Haxby

SSC Enthusiast

Points: 141

December 1, 2006 at 4:08 am

#675105

Hi, The additional field should be a numeric.

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Does it need to be sequential or just a random unique numeric?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Haxby SSC Enthusiast Points: 141 More actions · Answer 6

Haxby

SSC Enthusiast

Points: 141

December 1, 2006 at 8:14 am

#675151

Hi John,

I would prefer a sequential numeric field

John Rowan SSC Guru Points: 56440 More actions · Answer 7

I think you’re going to have a problem with this solution. While you may be able to get something that works, it may be best to re-think the design of your table. Can you give us some more information as to what you are trying to accomplish and the business rules surrounding your problem?

You can surely come up with a method to concatenate a unique sequential number onto your LR_REV column (varchar (??) I assume), but you run into a couple of problems in doing this. First, how do you truly enforce uniqueness of the concatenated value? In order to ensure that the value you are concatenating has not yet been used in the table, you would need to check the existing values first (I would use an outer join in my insert). The problem with this is that the value you want to check for is at the end of the column’s data which means even if you index this column, it will be a table scan each time you insert. I hope I’m making sense here. Why can’t you just add an identity column to your table? If you need to display the LR_REV value along with the identity, handle that in a subsequent select or in your application.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Haxby SSC Enthusiast Points: 141 More actions · Answer 8

Hi,

Here's my query, I really would appreciate it if you could help me.

Thanks,

SELECT

WT_ADD_OP, WT_ADD_DATE, WT_EDIT_OP,, WT_EDIT_DATE, 'N', '211', ,

LR_REV, WT_END_DATE, '0', WT_SHIFT, LR_REV, '20061212', DATEADD(Second,

(FLOOR(LR_DAY_END_TIME / 10000) * 3600) +

(FLOOR((LR_DAY_END_TIME - (FLOOR(LR_DAY_END_TIME / 10000) * 10000)) / 100) * 60) +

(LR_DAY_END_TIME - ((FLOOR(LR_DAY_END_TIME / 100) * 100))),

CONVERT(datetime, WT_END_DATE)) AS 'actl_end_dt_tm',

'0' -- [shft_use_end_tm] [smallint] NULL

FROM WT

INNER JOIN LR ON LR_RES = WT_RES

John Rowan SSC Guru Points: 56440 More actions · Answer 9

I suggest you think about re-designing your approach, but if you insist to go down this route, check out these two threads. The first one in particular should be helpfull.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=316818&p=1

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=318870

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

As the two links John posted suggest, add an IDENTITY column to your table and concatenate with the string conversion of that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)