yechan
asked on
Derived Column Transformations Question
Hi,
I have an ActiveX script that looks something like the following:
if rtrim(mid(record, 129,12)) = "" then
record = mid(record, 1,128) + "000000000000" + mid(record, 141)
end if
if rtrim(mid(record,47,8)) = "" then
record = mid(record, 1,46) + "00000000" + mid(record, 55)
end if
If rtrim(mid(record,85,8)) = "" then
record = mid(record, 1,84) + "00000000" + mid(record, 93)
end if
If rtrim(mid(record,97,8)) = "" then
record = mid(record, 1,96) + "00000000" + mid(record, 105)
end if
Is it possible to replace this type of logic with a derived column transformation task?
thanks.
I have an ActiveX script that looks something like the following:
if rtrim(mid(record, 129,12)) = "" then
record = mid(record, 1,128) + "000000000000" + mid(record, 141)
end if
if rtrim(mid(record,47,8)) = "" then
record = mid(record, 1,46) + "00000000" + mid(record, 55)
end if
If rtrim(mid(record,85,8)) = "" then
record = mid(record, 1,84) + "00000000" + mid(record, 93)
end if
If rtrim(mid(record,97,8)) = "" then
record = mid(record, 1,96) + "00000000" + mid(record, 105)
end if
Is it possible to replace this type of logic with a derived column transformation task?
thanks.
Hi,
As alwayes Mark gives the right answer but i'll add an SSIS expression since you asked about the derived column...
If you'll use it in a derived column, you'll need an expression such as:
that post will help you...
http://www.sqlchick.com/entries/2011/6/8/nested-conditional-operators-in-an-ssis-derived-column.html
As alwayes Mark gives the right answer but i'll add an SSIS expression since you asked about the derived column...
If you'll use it in a derived column, you'll need an expression such as:
TRIM( SUBSTRING( @record,129,12 ) ) == "" ? (SUBSTRING( @record,1,128) + "000000000000" + SUBSTRING( @record, 141,1)) : @record
that post will help you...
http://www.sqlchick.com/entries/2011/6/8/nested-conditional-operators-in-an-ssis-derived-column.html
ASKER
thanks for the input. With the derived column task, I can use the "record" column only once? Seems like once I use it, I can not select it again.
thanks
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DECLARE @record VARCHAR(200) = 'ABCDE FGHIJKLMNOPQRSTUVWXYZ01234
SELECT SUBSTRING(@record, 1, 5)
+ COALESCE(NULLIF(SUBSTRING(
+ SUBSTRING(@record, 11, 36)
+ COALESCE(NULLIF(SUBSTRING(
+ SUBSTRING(@record, 55, 200)
;
- NULLIF() can be used to return NULL if a value equals some value in the second parameter. Therefore, I used NULLIF(record, {8 spaces}), then wrapped in COALESCE() or ISNULL() you can replace that content with {8 0's}. REPLICATE() is a handy function to ensure I did not mistype the number of spaces or 0's. The final 200 is an arbitrary length to ensure we go all the way to the end of the string. 200 worked because my variable was less than 200. Most folks would use LEN(record) here. This works like MID(record, 105) which starts at 105 and goes to the end.
So in your case, the formula might look like:
SUBSTRING(record, 1, 46)
+ COALESCE(NULLIF(SUBSTRING(
+ SUBSTRING(record, 55, 30)
+ COALESCE(NULLIF(SUBSTRING(
+ SUBSTRING(record, 96, 1)
+ COALESCE(NULLIF(SUBSTRING(
+ SUBSTRING(record, 105, 24)
+ COALESCE(NULLIF(SUBSTRING(
+ SUBSTRING(record, 141, LEN(record))
Anyway, that is just a thought. You can always just recreate as you have it.
CASE WHEN RTRIM(SUBSTRING(record, 47, 8)) = '' THEN '00000000' ELSE SUBSTRING(record, 47, 8) END
Hope that helps!