Create a temp field within a select statement ?

Question

Post reply

Create a temp field within a select statement ?

isuckatsql

SSChampion

Points: 10381
More actions
December 5, 2008 at 9:26 am

#196702

I am trying to create a temp field -
set tempfield=fielda+fieldb
within a select statement to create a unqiue value.
How would i do this ?
Thanks

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

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

You mean something like?

SELECT A, B, A + B, A + ', ' + B

FROM Something

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

isuckatsql SSChampion Points: 10381 More actions · Answer 2

The select a+b worked but not with a +c.

I am trying to create a unique key for some history records, that have a many to one relationship with some account records.

I have tried ondate+ontime which is close but still not unique enough.

I guess i need accountno+ondate+ontime but the format is wrong so i get an error.

How can i define a field format that will allow a temp field to hold

accountno+ondate+ontime

Thanks

Garadin One Orange Chip Points: 29613 More actions · Answer 3

It's a datatype issue. This is why it's best to give all the information you can the first time, as I'd have warned you about this. You need to cast/convert the values you're trying to concatenate to compatible datatypes.

Something like this:

SELECT

Value1,

Value2,

Value3,

CAST(Value1 as varchar(25)) + CAST(Value2 as varchar(25)) + CAST(Value3 as varchar(25))

From YourTable

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

isuckatsql SSChampion Points: 10381 More actions · Answer 4

Thanks, that worked!

Where do you add the distinct function, to make it unique?

It normally comes after the select, but that wont work now.

Garadin One Orange Chip Points: 29613 More actions · Answer 5

Why won't it work?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

isuckatsql SSChampion Points: 10381 More actions · Answer 6

I tried again and it did. duh!

What i need to do is manage the records in unique order based on the temp field which equals 'field1+field2+field3'

Would i need to do a save as, then do a new select statement on the saved file ?

Thanks

Garadin One Orange Chip Points: 29613 More actions · Answer 7

ifila (12/5/2008)
I tried again and it did. duh!
What i need to do is manage the records in unique order based on the temp field which equals 'field1+field2+field3'
Would i need to do a save as, then do a new select statement on the saved file ?
Thanks

Heh, no, that wouldn't do much for you. Do you just mean you want to order them by the concatenated field?

Order by Field1, Field2, Field3

There may be a much better way to do what you're asking than what I'm giving you, but you haven't really given us a description of what you're trying to accomplish.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

isuckatsql SSChampion Points: 10381 More actions · Answer 8

I am trying to migrate my data from Goldmine to CRM 4.0

The Client (account) records have imported fine, based on running a sql query with unique account number.

The history (note) records are giving me the headache!

I have:

accountno, ondate, ontime, noteintextformat

It keep failing to import due to duplicates, so i am trying to find a way to create something unique that will work.

accountno+ondate+ontime.

My thought was to create a unique

temp field=accountno+ondate+ontime

then the file would look like:

tempfield, accountno, ondate ontime, noteintextformat

and i could match to the account records based on accountno.

Will that work ?

Garadin One Orange Chip Points: 29613 More actions · Answer 9

Goldmine... ack. Some nasty memories of that one.

Can you just add an identity column to the table and import that as well for uniqueness?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]