Merging two fields together

Question

Post reply

Merging two fields together

Jonah-356166

SSC Enthusiast

Points: 112
More actions
August 7, 2006 at 9:19 am

#172052

I have two fields from an AS400 which i want to merge when i extract the data using Data Transformation. The first field contains a Customer name and the second field contains a Form Type.
Example:
CUST FORM
Walmart INV
Inv meaning Invoice....but you knew that....
What i need to do is join these two fields together like this
Example:
NEWFIELD
Walmart -INV
How would i do this in SQL. i know how to do it in Access but in SQL it baffles me.
Any Suggestions would be much appreciated.
Thank You

Viewing 7 posts - 1 through 6 (of 6 total)

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

Bhushan Default port Points: 1427 More actions · Answer 1

Bhushan

Default port

Points: 1427

August 7, 2006 at 9:37 am

#653659

Cust + Form

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 2

select CUST + ' - ' + FORM as NEWFIELD from dbo.DataBase

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 3

sorry for misprint:

select CUST + ' - ' + FORM as NEWFIELD from dbo.TableName

Jonah-356166 SSC Enthusiast Points: 112 More actions · Answer 4

Sorry, but 1 more question. As you can tell im very new to this. Im getting an error message.

SELECT HQCUST + ' -' + HQDADD AS HQCFLD,

AIMPDTA.HQANALLA.*

FROM AIMPDTA.HQANALLA

This is my code above.

The error message i get is: "Query Designer encountered a MS Design Tools Error: ADO error [IBM][Client Access Express ODBC Driver (32-Bit)][DB2/400 SQL] SQL0402 - + use not valid.

Also from the code above you can probably see that i am incorporating all fields from the table HQANALLA. How would i incorporate all fields plus add the concatenated field as well

HELP!!

Cory E. SSCertifiable Points: 7076 More actions · Answer 5

It has been a long time since I had to do any DB2 work, but the solution you may need to use here is to get the records into some temp table on SQL, then, place the records in your final table doing the Field1 + ' - ' + Field2

-- Cory

Dan Murray Right there with Babe Points: 779 More actions · Answer 6

This is just a thought but since you're getting an MS ADO error that the concatenation operator '+' is invalid have you tried the standard MS concatenation operator '&' in it's place?

Yes I know it's not Sql but it is MicroSoft and everybody got to love Microsoft.