Currently I'm using this expression in a matrix to provide a running
total of sales by month. Is there a way to reverse this so that it
counts down to zero rather than adding each month together? Here's the
expression as is:
=IIF(InScope("matrix3_RowGroup1"),Sum(Fields!MonthAmount.Value),RunningValue(Fields!MonthAmount.Value,
Sum, "matrix3_RowGroup1"))
Can I reverse that, so that it shows the grand total minus the running
value?
Thanks in advance for any help.You could try the following expression:
=IIF(InScope("matrix3_RowGroup1"), Sum(Fields!MonthAmount.Value),
Sum(Fields!MonthAmount.Value) - RunningValue(Fields!MonthAmount.Value, Sum,
"matrix3_RowGroup1"))
Note: the third argument of the IIF function would get used if your not in
the scope of RowGroup1. Therefore, the Sum() function should calculate the
Sum of all cells of the entire RowGroup1 and you can just subtract the
RunningValue.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"twbanks" <twbanks@.gmail.com> wrote in message
news:1114016571.152109.86760@.f14g2000cwb.googlegroups.com...
> Currently I'm using this expression in a matrix to provide a running
> total of sales by month. Is there a way to reverse this so that it
> counts down to zero rather than adding each month together? Here's the
> expression as is:
> =IIF(InScope("matrix3_RowGroup1"),Sum(Fields!MonthAmount.Value),RunningValue(Fields!MonthAmount.Value,
> Sum, "matrix3_RowGroup1"))
> Can I reverse that, so that it shows the grand total minus the running
> value?
> Thanks in advance for any help.
>|||Thanks for the suggestion, Robert. Unfortunately, that subtracted the
runningvalue from the sum for that month only. Below is an example rdl
with first just the sum, then your suggestion, based on the pubs db.
Is there a way to reference the entire sum rather than just the
monthly?
RDL below:
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix2">
<Corner>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.46875in</Height>
<ZIndex>1</ZIndex>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=IIF(InScope("matrix2_pub_name"),Sum(Fields!ytd_sales.Value),Sum(Fields!ytd_sales.Value)
- RunningValue(Fields!ytd_sales.Value, Sum,
"matrix2_pub_name"))</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.21875in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1.125in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix2_pub_name">
<GroupExpressions>
<GroupExpression>=Fields!pub_name.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!pub_name.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.375in</Width>
<Top>1.25in</Top>
<Left>0.25in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix2_RowGroup1">
<GroupExpressions>
<GroupExpression>=Fields!type.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!type.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1.125in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.46875in</Height>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_type">
<GroupExpressions>
<GroupExpression>=Fields!type.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="type">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>type</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!type.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1.125in</Width>
</RowGrouping>
</RowGroupings>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ytd_sales_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>ytd_sales_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=IIF(InScope("matrix1_pub_name"),Sum(Fields!ytd_sales.Value),RunningValue(Fields!ytd_sales.Value,
Sum, "matrix1_pub_name"))</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.21875in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1.125in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_pub_name">
<GroupExpressions>
<GroupExpression>=Fields!pub_name.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="pub_name">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>pub_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!pub_name.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.375in</Width>
<Top>0.625in</Top>
<Left>0.25in</Left>
</Matrix>
</ReportItems>
<Style />
<Height>2.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="MyReports5">
<rd:DataSourceID>adedf68c-bc44-419e-945a-ceb24c77c2e2</rd:DataSourceID>
<DataSourceReference>MyReports5</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.25in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="title_id">
<DataField>title_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="title">
<DataField>title</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="type">
<DataField>type</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="pub_id">
<DataField>pub_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="price">
<DataField>price</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="advance">
<DataField>advance</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="royalty">
<DataField>royalty</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ytd_sales">
<DataField>ytd_sales</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="notes">
<DataField>notes</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="pubdate">
<DataField>pubdate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="pub_name">
<DataField>pub_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>MyReports5</DataSourceName>
<CommandText>SELECT titles.*, publishers.pub_name
FROM titles INNER JOIN
publishers ON titles.pub_id = publishers.pub_id
WHERE (titles.ytd_sales > 0)</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>6e1b5581-c1e8-493d-a6e5-4ff74ef05171</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||Never mind, I figured it out. I need to use your expression, but
specify the dataset name as the scope when referencing the sum to
subtract from, like so:
=IIF(InScope("matrix3_RowGroup1"), Sum(Fields!MonthAmount.Value),
Sum(Fields!MonthAmount.Value,"DeferredRevenue") -
RunningValue(Fields!MonthAmount.Value, Sum,
"matrix3_RowGroup1"))
Thanks for the help.
Showing posts with label expression. Show all posts
Showing posts with label expression. Show all posts
Thursday, March 29, 2012
Dervied Column - Expression?
Hi:
I have a Dervied Column Component, in which there is a column called RecStatusCode. The criteria to give a value to that column is below in SQL:
(SELECT CASE CertParticipant
WHEN 'Y' THEN 'C'
WHEN NULL THEN 'A'
END
FROM [dbo].[SchoolCertRequest]
WHERE SchoolID = (SELECT SchoolCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @.LoanApplicationID) and SchoolBranch = (SELECT BranchCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @.LoanApplicationID) AND LoanType=(SELECT LoanApplicationTypeCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @.LoanApplicationID))
My question is how can I use this SQL in that expression field, or is it possible to have a user variable, with the above sql being the criteria, and the value could be used in the derived column, by just using the variable in the expression. In either case, I hope you understand what I am trying to accomplish. The reason why its like this is because currently in my Data Flow Task, i Have a OLEDB source which returns 25+ column values, the source connects to this derived column item, and then finally the destination oledb where the mapping takes place. So basically I am trying to avoid MERGE JOIN, because i tried using it , and it complicated the whole thing much more then it really needs to be. Like the RecStatusCode column, i have 5 more columns which are dervied, and the values are coming from a SQL statement similar to above. I really need some help here, and I have not seen anything in reference to this in BOL or any articles online. Thanks in advance.
I think a combination of Replace and ISNull functions will do the trick.
http://msdn2.microsoft.com/en-us/library/ms141196.aspx
http://msdn2.microsoft.com/en-us/library/ms141184.aspx
|||Is there a way to put SQL queries in a user variable? I am just trying to figure out, whats the best way to do this? Can someone please advise. Thanks.|||Try doing that in an OLE DB command... You can't execute a SQL statement in a variable. You can store a SQL statement in a variable as it's just a string. You can even make that statement dynamic using variable expressions.|||Please disregard my first answer; I thought you were asking how to translate the case statement into a SSIS expression.
Anyway; have you consider to include that query with the query in your source component; assuming all tables are in the same DB it would work just fine. In case the data is in different DB/Server; you could use a staging table to load the data on a common place and then have one single query.
Otherwise a second source component with a merge join may be an option.
deriving a new column from another derived column
In a Derived Column data flow transformation, why can't I refer to a derived column (added in that same transformation) in the expression for another derived column? It seems I am forced to chain 2 DC data flows, just to do something as conceptually simple as:
a = x + y; b = a2
On a related note: Can I define a variable that is scoped to each row? Can I bind a variable in an expression to avoid creating a new row, e.g.
let a = x + y; a2
as the expression for new row b?
Kevin Rodgers wrote:
In a Derived Column data flow transformation, why can't I refer to a derived column (added in that same transformation) in the expression for another derived column? It seems I am forced to chain 2 DC data flows, just to do something as conceptually simple as:
a = x + y; b = a2
Kevin,
God yeah. I so wish you could do that. Seems like such simple funcitonality doesn't it?
I have requested it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127010
Please please please click through and vote for it.
On a related note: Can I define a variable that is scoped to each row? Can I bind a variable in an expression to avoid creating a new row, e.g.
let a = x + y; a2
as the expression for new row b?
No, you can't do that!
-Jamie
|||You can't do it in derived column, but it is very easy to do in script component - the component generates the row accessors, so the amount of code you need to write is almost the same as in derived column transform.|||
I haven't used the script component before -- I haven't even written any scripts. Could you show me a simple example of generating row accessors etc. as you've suggested?
Thanks!
|||It's a good idea Jamie.
Do keep in mind that it adds another layer of complexity. Along with some other difficulties, the user would have to specify an order of execution for the expressions, and the UI would need a way too enable and maintain that. Chaining multiplie derived columns, while not as convenient or as pretty to look at, makes it abundantly clear what order the intermediate expressions occur in and eases some usability concerns.
It's something to look at for the future, though, certainly. Keep the suggestions coming!
Thanks
Mark
I haven't used the script component before -- I haven't even written any scripts. Could you show me a simple example of generating row accessors etc. as you've suggested?
Thanks!
Just add a script transform where you would use Derived Column transform, select Transform in the first dialog. Check the columns you want to use in your expressions. Go to Inputs and Outputs tab, add output columns to Output 0.
Now edit the script, and type your code inside Input0_ProcessInputRow function. I quickly setup a "validation" for AdwentureWorks DB (the transform adds two colums CalculatedColumn and TotalOK to each row):
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim t As Decimal
t = Row.OrderQty * Row.UnitPrice * (1 - Row.UnitPriceDiscount)
Row.CalculatedColumn = t
If (t = Row.LineTotal) Then
Row.TotalOK = True
Else
Row.TotalOK = False
End If
End Sub
So the main difference in expressions is that you have to add "Row." prefix to column name :) Of course, the syntax is different, as the script transform uses full VB.NET language, which gives you ability to create temporary variables among other things.
sqlTuesday, March 27, 2012
Derived Shape - Replacing a column with itself.
Hi there,
I have a derived column shape in which I replace a column with an expression.
The expression is an IF statement - a true result sets a value for the column and a false result just uses the existing value of the column (ie it replaces it with itself)
Like this.
ColumnName DerivedColumn Expression
ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : ColumnA
But whenever, the condition is NOT met, ColumnA is set to NULL!!!!
Does this mean that the column value is deleted before the Expression is applied?
If this is how it is meant to work, then does anyone have a way of doing what I want to do without creating extra columns in the dataset?
Cheers.
I'm wondering if there are some data typing issues here. Wha is the type of ColumnA?
-Jamie
|||Hi Jamie,
It's just a unicode string.
Dave.
|||I mean in the pipeline. Is it a DT_WSTR?
if so, ensure you don't have any implicit conversions going on. i.e. explicitly cast "Y" as a DT_WSTR.
Also, first try to get this working as a new column rather than replacing ColumnA. See if you exhibit the same problems in that scenario.
-Jamie
|||Jamie,
in my example setting Column A to the value "y" works fine. It's setting Column A to itself (ie II just want it to retain it's original value) which is the problem.
Do you suggest I explicitly cast the ColumnA in the expression? So with my example....
ColumnName DerivedColumn Expression
ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : CAST(ColumnA...)
|||Dave,
No, that's not quite what i meant.
Try this:
DerivedColumn Expression
Replace 'ColumnA' ColumnB != ColumnC ? (DT_WSTR)"y" : ColumnA
My second suggestion was to see if this worked first:
DerivedColumn Expression
Add as new column ColumnB != ColumnC ? "y" : ColumnA
I'm clutching at straws a little bit but if I were you I would definately try to recreate the problem by adding it as a new column rather than replacing ColumnA.
-Jamie
|||Hi Jamie,
adding a column works fine. It's replacing an exising column that has the "problem".
I should point out as well that the issue only arises when you use an IF condition in the expression.
So this is OK
DerivedColumn Expression
Replace ColumnA ColumnA+"Hello"
This will replace ColumnA with what was in ColumnA + "Hello"
This is NOT OK
DerivedColumn Expression
Replace ColumnA ColumnB != ColumnC ? "y" : ColumnA
So if ColumnB = ColumnC, then ColumnA is set to NULL - NOT what ColumnA was before the expression was applied.
I reckon its a bug.
|||Hi Dave,
Can you post a simple repro that doesn't reply on external data sources (i.e. just create the same data using a script source component) and then post it up here?
To post up here, just copy the contents of your .dtsx file into your reply.
Thanks
Jamie
|||Sure,
I'll do it tomorrow.
Speak then.
|||Have you checked if Column B or C is NULL? If so you will always get NULL out. Example here http://wiki.sqlis.com/default.aspx/SQLISWiki/Expressions.html|||You can quickly test for what Darren is saying by changing your expression like this:
false ? "y" : ColumnA
If ColumnB or ColumnC are NULL, and you want to fail the comparison in that case,you can make your expression like this:
!ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA
If you want the condition to do NULL comparisions (that is, you want ColumnB != ColumnC to return true if one is NULL and the other is not, you might need something more like:
(ISNULL(ColumnB) != ISNULL(ColumnC)) || !ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA
|||Your right Darren - it was the NULL values. aaargh!!!
Thanks for everyone's input.
derived column transformation expression
Hi.
I am using the following expression to check if the first charcter of a string is not the letter "E" and if it is, strip it off by selecting the remainder of the string:
SUBSTRING([Derv.comno],1,1) == "E" ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]
This is ok in 99.9% of cases, but ideally I would like to be able to check, and alter the string if the first charcter is anything but numeric
I had something like this in mind:
SUBSTRING([Derv.comno],1,1) != ("1","2","3") ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]
but the syntax is incorrect.
Could you tell me if what I am attempting is actually possible, and if so, point me in the right direction regarding the syntax!
Thanks
CODEPOINT(@.[User::CCC]) >= 47 && CODEPOINT(@.[User::CCC]) <= 57 ? @.[User::CCC] : SUBSTRING(@.[User::CCC], 2, LEN(@.[User::CCC]) - 1)|||
Excellent, works a treat!
Thanks very much
sqlDerived Column Transformation Editor Question
Help...
I'm having trouble coming up with a valid expression in my derived column transformation editor that tests the input column for NULL and responds something like this:
if[message] isNull then "NA" else [message]
where [message] is the input column.
Thanks!
Try:
ISNULL([message]) ? "NA" : [message]
|||SWEET!! Thanks!
Derived Column Transformation Editor Question
Help...
I'm having trouble coming up with a valid expression in my derived column transformation editor that tests the input column for NULL and responds something like this:
if[message] isNull then "NA" else [message]
where [message] is the input column.
Thanks!
Try:
ISNULL([message]) ? "NA" : [message]
Derived Column Task Replace Quotes
How do you replace quotes in an expression
I mean for example if I needed to replace xx in a string with empty string then the following works: REPLACE(SelectedString, "xx","")
But the example I have needs to actually replace quote marks in a string with an empty string and REPLACE(SelectedString, " " ","") doesn't work. I tried guessing a few option like "E or &QTE or something...
Any ideas ?
Thanks
Richard
Got it !! :
REPLACE([Selected Closing],"\"","")
Derived column problem
I want to implement all this four condition in one derived column expression.
if (Column1== 1)
{
OutputColumn = ColumnA- Column1RATE
}
if (Column2 == 1)
{
OUtputColumn = ColumnA- Column2RATE
}
if (Column3 == 1)
{
OUtputColumn = ColumnA- Column3RATE
}
if (Column4 == 1)
{
OUtputColumn = ColumnA- Column4RATE
}
please suggest
Something like this might work:
(Column1== 1)?ColumnA- Column1RATE?(Column2 == 1)?OUtputColumn = ColumnA- Column2RATE:(Column3 == 1)?ColumnA- Column3RATE:(Column4 == 1)?ColumnA- Column4RATE:0
Frank
|||Earlier post had typos...
Corrected:
(Column1== 1)?ColumnA- Column1RATE:(Column2 == 1)?ColumnA- Column2RATE:(Column3 == 1)?ColumnA- Column3RATE:(Column4 == 1)?ColumnA- Column4RATE:0
Sunday, March 25, 2012
Derived column expression question......
Greetings,
I have an existing 2000 DTS package that uses the following case statement:
Case
When TERMS_PERCENT ='0'
then 0
else cast(TERMS_PERCENT as decimal(6,2))/100
end as TermsPct
to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.
mjanzou wrote:
Greetings,
I have an existing 2000 DTS package that uses the following case statement:
Case
When TERMS_PERCENT ='0'
then 0
else cast(TERMS_PERCENT as decimal(6,2))/100
end as TermsPct
to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.
Something like this perhaps?
[TERMS_PERCENT] == "0" ? (DT_NUMERIC, 5, 2)[TERMS_PERCENT] : (DT_NUMERIC, 5, 2)0
-Jamie
|||
[TERMS_PERCENT] == "0" ? (DT_NUMERIC, 5, 2)[TERMS_PERCENT] : (DT_NUMERIC, 5, 2)0
-Jamie
The TRUE and FALSE values are reversed 
Code Snippet
[TERMS_PERCENT]== "0" ? (DT_NUMERIC,6,2)0 : ((DT_NUMERIC,6,2)[TERMS_PERCENT]) / 100
|||Ahhh goddamnit!!!
Subscribe to:
Posts (Atom)