August 11, 2014 at 7:49 am
hi
i have a tbale and after the table i want to put in a derived column that then pick just the data from the InvestmentType == "Forward Cash"
now i have tried the above code but doesn't work whats the best way to do this
August 11, 2014 at 8:00 am
ronan.healy (8/11/2014)
hii have a tbale and after the table i want to put in a derived column that then pick just the data from the InvestmentType == "Forward Cash"
now i have tried the above code but doesn't work whats the best way to do this
Your question is not very clear, but if you are talking about picking only certain data in a dataflow, just use a SELECT/WHERE query as your data source, rather than 'table or view'.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 11, 2014 at 8:01 am
Phil Parkin (8/11/2014)
ronan.healy (8/11/2014)
hii have a tbale and after the table i want to put in a derived column that then pick just the data from the InvestmentType == "Forward Cash"
now i have tried the above code but doesn't work whats the best way to do this
Your question is not very clear, but if you are talking about picking only certain data in a dataflow, just use a SELECT/WHERE query as your data source, rather than 'table or view'.
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2014 at 8:02 am
ronan.healy (8/11/2014)
hii have a tbale and after the table i want to put in a derived column that then pick just the data from the InvestmentType == "Forward Cash"
now i have tried the above code but doesn't work whats the best way to do this
Do you only want to change the value of the rows containing InvestmentType == "Forward Cash"?
Or do you want values of that type to process in one direction while values of another type process in a different direction?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2014 at 8:06 am
the info is coming from a table.
can you write scripts in a data flow i have never done it before.
the info is in a table but i need to do all the following
For all records where Investment Type = ‘Forward Cash’
I.The records will need to be grouped by the TaxLotID field – consider the logic below prior to grouping the records
II.Identify currency purchased/sold codes
a.Parse out the first 3 characters of the field Tax Lot Description – this is the currency purchased code. [store this as temp variable for populating later]
b.Parse out the 3 characters starting from position 9 from the Tax Lot Description – this is the currency Sold code. [store this as temp variable for populating later]
III.Identify the amounts purchased / amounts sold
a.If the Quantity figure < 0, this is the Currency Purchased Amount
b.If the Quantity figure > 0, this is the Currency Sold Amount
would it be better to do a script or do it in derived columns as once i do all this i need to map it to another table that stores my output
August 11, 2014 at 8:13 am
All of that can be done via TSQL query in your data source.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2014 at 8:16 am
ok
would I still be able to use SUBSTRING(TaxLotDescription,1,3) for pulling the first 3 chars.
how would I be able to do in tsql
III.Identify the amounts purchased / amounts sold
a.If the Quantity figure < 0, this is the Currency Purchased Amount
b.If the Quantity figure > 0, this is the Currency Sold Amount
I need to basically create a new column and put the figures in under the right headings
August 11, 2014 at 8:21 am
ronan.healy (8/11/2014)
okwould I still be able to use SUBSTRING(TaxLotDescription,1,3) for pulling the first 3 chars.
how would I be able to do in tsql
III.Identify the amounts purchased / amounts sold
a.If the Quantity figure < 0, this is the Currency Purchased Amount
b.If the Quantity figure > 0, this is the Currency Sold Amount
I need to basically create a new column and put the figures in under the right headings
Yes on the use of substring.
For the new columns - you can use pivot, case statements, or even subqueries.
Are you pulling this data to create a report?
Or are you trying to move this data from one table to a different table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2014 at 8:24 am
im going to be moving the data from 1 table to another
August 11, 2014 at 8:38 am
so say I use something like this
SELECT *
FROM dbo.BNYForwardsTaxLotsForwards
WHERE Quantity IN (SELECT Quantity as CurrencyPurchasedAmount
FROM dbo.BNYForwardsTaxLotsForwards
WHERE investmentType ='Forward Cash'
and Quantity < 0)
when I want to map CurrencyPurchasedAmount to a field in my table how can i
August 11, 2014 at 8:46 am
ronan.healy (8/11/2014)
so say I use something like this
SELECT *FROM dbo.BNYForwardsTaxLotsForwards
WHERE Quantity IN (SELECT Quantity as CurrencyPurchasedAmount
FROM dbo.BNYForwardsTaxLotsForwards
WHERE investmentType ='Forward Cash'
and Quantity < 0)
when I want to map CurrencyPurchasedAmount to a field in my table how can i
Since you are using a select *, your field name for the source would be Quantity.
What is the field name for the destination? That is your mapping.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2014 at 1:54 am
CurrencyCodePurchased is the name of the column it will be mapped to
August 12, 2014 at 3:40 am
will have to declare a variable for it to work
August 12, 2014 at 8:38 am
ronan.healy (8/12/2014)
will have to declare a variable for it to work
No, you just map the datasource column to the CurrencyCodePurchased column in your destination object.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2014 at 8:51 am
how if im using an sql script
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply