Derived Column - Redirect Row  
Author Message
quenaon





PostPosted: Mon Nov 27 05:36:18 CST 2006 Top

SQL Server >> Derived Column - Redirect Row

Currently have a derived column as part of a dataflow to help combat spaces
and blanks when converting data types.

example:
derived column 1: (DT_I4)[DUEQTY] if the column can't be converted it
redirects the row then another derived column process uses (DT_I4)0

I have this concept on three columns within the table.

if there are only errors on one of the columns it redirects the whole row
and changes all values to zero.

How do I combat this? I think i'm looking for a derived column variable
statment that is similiatr to the if(iserror(statement),"",(statement)) in
Excel

--
Milk Bottle

SQL Server177  
 
 
Uri





PostPosted: Mon Nov 27 05:36:18 CST 2006 Top

SQL Server >> Derived Column - Redirect Row Hi
Is it SQL Server's statement? Can you show us?



>
> Currently have a derived column as part of a dataflow to help combat
> spaces
> and blanks when converting data types.
>
> example:
> derived column 1: (DT_I4)[DUEQTY] if the column can't be converted it
> redirects the row then another derived column process uses (DT_I4)0
>
> I have this concept on three columns within the table.
>
> if there are only errors on one of the columns it redirects the whole row
> and changes all values to zero.
>
> How do I combat this? I think i'm looking for a derived column variable
> statment that is similiatr to the if(iserror(statement),"",(statement)) in
> Excel
>
> --
> Milk Bottle


 
 
Dan





PostPosted: Mon Nov 27 07:07:15 CST 2006 Top

SQL Server >> Derived Column - Redirect Row > if there are only errors on one of the columns it redirects the whole row
> and changes all values to zero.

Rather than set all values to zero, set only the problem column (e.g.
column1) to zero. Connect the outputs of both column1 derrived columns to a
Union All so that both good and fixed records continue down the pipeline.
Repeat this for the remaining columns you want to scrub.


--
Hope this helps.

Dan Guzman
SQL Server MVP



>
> Currently have a derived column as part of a dataflow to help combat
> spaces
> and blanks when converting data types.
>
> example:
> derived column 1: (DT_I4)[DUEQTY] if the column can't be converted it
> redirects the row then another derived column process uses (DT_I4)0
>
> I have this concept on three columns within the table.
>
> if there are only errors on one of the columns it redirects the whole row
> and changes all values to zero.
>
> How do I combat this? I think i'm looking for a derived column variable
> statment that is similiatr to the if(iserror(statement),"",(statement)) in
> Excel
>
> --
> Milk Bottle