March 9, 2009 at 8:40 am
hi..........
i have to send 2 values from one database in to the 1 value of another database.......but they are int values.........when i am using derived column and concatenate they just adding up.........if i am using "" symbol in between them it is showing error in derived column.............my destination datatype is int.......can anyone help me ..........
Thanks
March 9, 2009 at 9:08 am
So, if one of the results is 123 and the other is 456, you want to end up with the number 123456?
Can you multiply the first one by the appropriate power of 10 and then concat them? In my example above, you would multiply the first one by 1000, and then add them together, and you'd get the desired result.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2009 at 9:14 am
GSquared (3/9/2009)
So, if one of the results is 123 and the other is 456, you want to end up with the number 123456?Can you multiply the first one by the appropriate power of 10 and then concat them? In my example above, you would multiply the first one by 1000, and then add them together, and you'd get the desired result.
Or, if the values are of varying length, cast((cast(col1 as varchar(10)) + cast(col2 as varchar(10))) as int).
March 9, 2009 at 9:16 am
Hi,,,,,,,,,,
No,i dont want like that result 123+456=123456........ i have id_num and ssn from one database to cust_id in another database........so i have 2 moves 2 values in to 1 column...........
how can i do that???
thanks
March 9, 2009 at 9:29 am
hi.......
i have tried using the expression,but i am getting error in it...... can u give correct expression and what kind of datatype i have to select?? please help me??
THanks
March 9, 2009 at 9:31 am
You know what would really help, show us what you are trying to accomplish with sample data. Right now we are all just guessing at what you are trying to do.
March 9, 2009 at 10:22 am
hi.........
(DT_STR,50,1252) ( (DT_STR,10,1252)[ [LIC-NO]] + (DT_STR,10,1252) [SSN] )
i am using the above expression in the derived column and LIC-NO and SSN are the columns i need to send and datatype i selected DT_STR........but also it is showing in red........
how can i write the expression??
March 9, 2009 at 10:27 am
Okay, you showed us the how you are trying to accomplish your task, but how about showing us what you are trying to accomplish with some sample data, like '123' + '456' = '123456'.
March 9, 2009 at 10:37 am
hi..........
i have LIC-NO and SSN fields for all the customers in the database 1 and i have to send them to database2 into the CUST_ID column..........
for example,for a customer with id 1 have LIC_NO:234523 and SSN:123456,i have to display both the fields
in the CUST_ID field.........may like this 234523,123456.........or if possible display the 2 fields in different columns........
please help me.........
March 9, 2009 at 10:45 am
angel41_23 (3/9/2009)
hi..........i have LIC-NO and SSN fields for all the customers in the database 1 and i have to send them to database2 into the CUST_ID column..........
for example,for a customer with id 1 have LIC_NO:234523 and SSN:123456,i have to display both the fields
in the CUST_ID field.........may like this 234523,123456.........or if possible display the 2 fields in different columns........
please help me.........
Okay, let's change the requirements in the middle of things. Which do you want, to concatenate the values or put them in separate columns in the destination database? If you want them in different columns, the destination database needs separate columns to put the data.
March 9, 2009 at 10:53 am
Try this:
(DT_STR,10,1252)[LIC-NO] + "," + (DT_STR,10,1252)[SSN]
And yes, those are double quotes not two single quotes on either side of the comma.
March 9, 2009 at 10:57 am
Sounds like you're not overly sure *what* you want to do here....
Trying to concat two integers in to a new integer field is not overly a great idea. How will you handle cases when the resultant concatenated value is > 10 numeric characters (ie int data type holds a number that's up to 10 numerics long)? Seeing as SSN is inherently 9 char (ignoring dashes), you're probably already broken.
You might want to throw it in to a string field, but then you'd still want some style of separator, otherwise, how will you determine where the client ID ends and the SSN starts? using string functions coming from the RHS for 9 char? ugh, ugly.
Why not just have two fields, one for the client id and the other for SSN? This would make the transfer of information a lot simpler (which all indications are leaning towards the simpler the better) and would allow you to manage the two distinct data elements individually.
Steve.
March 9, 2009 at 11:03 am
Lynn Pettis (3/9/2009)
Try this:
(DT_STR,10,1252)[LIC-NO] + "," + (DT_STR,10,1252)[SSN]
And yes, those are double quotes not two single quotes on either side of the comma.
And, of course, when doing this you will no longer have an INT value as you now have a comma inserted in the data.
March 9, 2009 at 11:48 am
HI..............
if i want to keep them in different rows.....how can do that??
March 9, 2009 at 12:04 pm
Different rows??
Um, different columns I can understand (ie one col for SSN, another for client ID). Different rows would indicate (to me) different tables, i mean, i certainly wouldn't qant to be the person reporting off a table where row(x):field(1) contains a SSN, and row(x+n):field(1) returns me a different data element. A multi-use column would be mega ugly. Almost as much fun as table data type for a column.
Steve.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply