Converting data question

I need to combine two columns and copy them to another. One column is varchar and one is decimal (3,0) and the destination column is int. The column of decimal contains only 1 and 2 digit numbers. I need to perface a 0 to the data with 1 digit before combining the two columns. If someone could guide me here, I'd appreciate it. I've been trying to use cast and convert with no success.Your destination column should be a varchar column if the column from your source varchar is not an integer. You can post a set of sample data and expected result here to help answer your question.|||Source Columns Destination
Varchar Dec int
0660 10 66010
0660 2 66002
0660 3 66003
0660 11 66011|||

Kinny:

I put together a quick mock-up of what I think you are trying to do. Do either of the output columns look like what you are trying to do?

Dave

-- --
-- This mock-up works correctly for "correct values" as discribed in the request.
-- However, it does not know what to do with most "error conditions." If you don't
-- have to worry about "incorrect values" this select should work fine
--
-- Problem areas:
--
-- 1. What do you do with nulls?
-- 2. What do you do with 3-digit numbers?
-- 3. What do you do with negative numbers?
-- --
set nocount on
declare @.mockUp table
( sampleString varchar (20) not null,
sampleDecimal decimal (3,0) null
)

insert into @.mockUp values ('testView', null) -- ? Do you have to worry about null?
-- ? If so, display double-zero for nulls?
insert into @.mockUp values ('testView', 0) -- O0; OK
insert into @.mockUp values ('testView', 4) -- 04; OK
insert into @.mockUp values ('testView', 21) -- 21; OK
insert into @.mockUp values ('nextSample', 99) -- 99; OK
insert into @.mockUp values ('negative', -1) -- -1; Not sure; is this a problem?
insert into @.mockUp values ('negative', -99) -- 99; Wrong; is this a problem?
insert into @.mockUp values ('tooBig', 100) -- 00; Wrong; is this a problem?
--select * from @.mockUp

select sampleString,
sampleDecimal,
sampleString + isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00')
as [String First],
isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00') + sampleString
as [Decimal First]
from @.mockUp

-- --
-- Sample Output:
-- --

-- sampleString sampleDecimal String First Decimal First
-- -- - - -
-- testView NULL testView00 00testView
-- testView 0 testView00 00testView
-- testView 4 testView04 04testView
-- testView 21 testView21 21testView
-- nextSample 99 nextSample99 99nextSample
-- negative -1 negative-1 -1negative
-- negative -99 negative99 99negative
-- tooBig 100 tooBig00 00tooBig

|||

Hi,

Here is the update statement for question:

UPDATE yourTable

SET DesINTColumn= CAST(CAST(CONVERT(int, VarCharColumn1) AS varchar)+ RIGHT('0' + CAST(DecimalColumn1 AS varchar), 2) AS INT)

Convert varchar(12) to binary(12)

I need help converting a varchar field to binary.

I have a Data Control Task that has a OLE DB Source and corresponding OLE DB Destination Data Flow Task. In the referenced source table there exists a field defined as a varchar(12), in the corresponding destination table it is defined as a binary(12). How do I perform this conversion?

I tried inserting a Data Conversion Task and assigning the new data type as byte stream[DT_BYTES] and a Length = 12, but this was a bust. Output test is as follows:

Error: 0xC020901C at DFT - Windows, OLE_SRC - Windows [1]: There was an error with output column "Payload" (40) on output "OLE DB Source Output" (12). The column status returned was: "The value could not be converted because of a potential loss of data.".

Can anyone help me out here?

Regards,

What is the SSIS data type assigned to the source column? Right-click on the data flow error and slect Edit and then go to the Metadata tab of the Data Flow Path Editor dialog.

Is it DT_WSTR or DT_STR?

My guess is that SSIS is treating your input field as a Unicode string (DT_WSTR) so each of the 12 characters is taking up 2 bytes.

Convert If-Else into Case statement

Can I convert this IF-ELSE statement into a CASE statement? Thanks for
your help!
declare @.source varchar(150)
declare @.destination varchar(150)
if (select @.@.servername) = 'A'
begin
set @.source = 'folder1\file.txt'
set @.destination = 'folder2'
end
else
if (select @.@.servername) = 'B'
begin
set @.source = 'folder3\file2.txt'
set @.destination = 'folder4'
end
else
if (select @.@.servername) = 'C'
begin
set @.source = 'folder4\.*'
set @.destination = 'folder4'
end
*** Sent via Developersdex http://www.examnotes.net ***Give this a try...
declare @.source varchar(150)
declare @.destination varchar(150)
select @.source = case @.@.servername
when 'A' then 'folder1\file.txt'
when 'B' then 'folder3\file2.txt'
when 'C' then 'folder4\.*'
end
, @.destination = case @.@.servername
when 'A' then 'folder2'
when 'B' then 'folder4'
when 'C' then 'folder4'
end
--Brian
(Please reply to the newsgroups only.)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OMYWbhTuFHA.460@.TK2MSFTNGP15.phx.gbl...
> Can I convert this IF-ELSE statement into a CASE statement? Thanks for
> your help!
>
> declare @.source varchar(150)
> declare @.destination varchar(150)
> if (select @.@.servername) = 'A'
> begin
> set @.source = 'folder1\file.txt'
> set @.destination = 'folder2'
> end
> else
> if (select @.@.servername) = 'B'
> begin
> set @.source = 'folder3\file2.txt'
> set @.destination = 'folder4'
> end
> else
> if (select @.@.servername) = 'C'
> begin
> set @.source = 'folder4\.*'
> set @.destination = 'folder4'
> end
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||declare @.source varchar(150)
declare @.destination varchar(150)
SELECT @.source =
CASE @.@.servername
WHEN 'A' THEN 'folder1\file.txt'
WHEN 'B' THEN 'folder3\file2.txt'
WHEN 'C' THEN 'folder\4.*'
END,
@.destination =
CASE @.@.servername
WHEN 'A' THEN 'folder2\'
WHEN 'B' THEN 'folder4\'
WHEN 'C' THEN 'folder4'
END|||Heh,
Sorry for the duplication, I guess I was typing my response whilst
Brian was posting his!
Still, we both posted the same thing so I guess that's confirmation it
works!|||THANX A LOT!!!!
*** Sent via Developersdex http://www.examnotes.net ***|||...or we're both just feeling lucky this morning. ;)
--Brian
(Please reply to the newsgroups only.)
"RobJH" <robertjhenderson@.gmail.com> wrote in message
news:1126709760.080074.261130@.g14g2000cwa.googlegroups.com...
> Heh,
> Sorry for the duplication, I guess I was typing my response whilst
> Brian was posting his!
> Still, we both posted the same thing so I guess that's confirmation it
> works!
>