Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Amount - Integer
Now i want to create a View & split the Amount column into 2 basd on
split this Amount into 2 columns as below:
Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Debit Amount - Integer (where D_C is D)
Credit Amount - Integer (where D_C is C)
Hi, how are you? I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:
We are looking data in a a sql database.
We are writting the SQL result in a flat file.
We need to transform data in one of the columns.
For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step? Thanks for any help you can give me. Regards,
I need to find a way to query a value stored inside a table column. I have a piece of sample script that looks like this:
select convert(xml,[VALUE]).value('(/ROOT/app/@S_YEAR)[1]','numeric(4,0)') as S_Year from myTble where [KEY] = 'StringA'
The myTble has 2 columns, Key, Value, and the Value is of data type text. The data stored in there is a large xml node or doc. The table has only three rows for StringA, StringB, and StringC.
I feel bad that I cannot post the xml here because it contains a lot of sensitive data. It will be very hard for me to replace those sensitive data without breaking the syntax integrity.I used to know XPath, but couldn't make out above Select.
I have a script component and under input columns, I checked one of the fields I want to use inside the script. It's under the Input 0 in the hierarchical tree inside the inputs and outputs.
I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"): 1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId 2 3 if @TimestampCheck is null 4 begin 5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist. 6 return -1 7 end 8 else if @TimestampCheck <> @LastChanged 9 begin 10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1) 11 return -1 12 end 13 14 15 Begin Tran Address 16 17 Update ADD_Address 18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate 19 where AddressId= @AddressId 20 21 select @error_code = @@ERROR, @AddressId= scope_identity() 22 23 if @error_code = 0 24 begin 25 commit tran Address 26 27 select @LastChanged = LastChanged 28 from ADD_Address 29 where AddressId = @AddressId 30 31 if @LastChanged is null 32 begin 33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1) 34 return -1 35 end 36 if @LastChanged = @TimestampCheck 37 begin 38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1) 39 return -1 40 end 41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin
I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing =IIF (Parameters!Param1.Value = "String1", False, True) but I would like it to work if the Param1.Value contains "String1" ... I tried =IIF (Parameters!Param1.Value like "%String1%", False, True) but it doesn't work. Any suggestions?
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 have a table in my report which shows sales values for each month by looking at the month number
so for Jan i use
=IIf(Fields!period_.Value=1,Fields!nett_.Value,0)
for Feb
=IIf(Fields!period_.Value=2,Fields!nett_.Value,0)
this is all good and I get a series of rows per customer with the correct value in the correct column
JAN FEB Mar
Customer A 250
Customer A 350
Customer A 5000
.
However I want to create a summary a line
JAN FEB Mar
Customer A 250 350 5000
I tried using SUM but this doesn't give all of the values in the summary line. It might just give the first and the rest are zeros. Or if the customer didn't have a value in Jan, but did in Feb and March, Feb's value is shown, but March is at 0 etc.
Have spent far too long on this today, so if anyone as any suggestion (come back Crystal all is forgiven ) on any approach I could take to this I'd appreciate it.
I am having a question on derived column expression. The expression I am trying to use for the derived column is as below (column1 is a numeric data type column):
case
when column1<0 then 'yes'
else 'no'
end
But I got the error message though, would please any experts here give me any advices on the expression I used? What is wrong with the expression I used above?
Thank you very much and I am looking forward to hearing from you shortly.
The following simple expression is not working for in a derived column transform. I have tried several things back and forth with lots of frustration.
DAY((DT_DATE)mydate)
In fact any of the date/time functions are not working for me and I keep getting the following error
[Derived Column [380]] Error: The "component "Derived Column" (380)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "Derived Column 1" (824)" specifies failure on error. An error occurred on the specified object of the specified component.
mydate happens to be imported as string[DT_STR] column from a flat file source.
I am in the "Check Constraints" window trying to add a constraint that during inserts only allow distinct Column2 insertions. What is the Expression for that? I tried UNIQUE(Column2) it give me an error.
Here is my table definition: Column Name Data Type Allow Nulls -------------------------------------------------------------------------------------------- Column1(PrimaryKey) int No Column2 nvarchar(MAX) No
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:
I'm trying to import from Excel, using (Assignment == 0 ? "In Use" : Assignment == 1 ? "In Stock" : "Retired") to convert 3 values to text and I' getting the following error: An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.
Could someone please tell me what I am doing wrong?
Thanks Dean
ps. just fixed it myself by changing the data type of the input column
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.
I am getting an error from the case part of the select statement below which reads 'Incorrect Column Expression' then it quotes the case statement. All I am trying to do is convert and return the weight value to kilos if it was entered in pounds.
I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.
I am not using CAST in the design grid. Is this even possible?
I am modifying the underlying SQL code. Here is the line that is giving me trouble:
CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequenceâ€
I am importing data from a CSV to a DB with a SSIS package. Among some things things it already does, it has to decide if the relation between one row and the following is acceptable. If it is not, the 2nd row is discarded, the next one is taken and the relation value is calculated again to decide whether to keep this one or not, and so on.
To calculate this value, I need to apply a formula that includes sin(), cos() and acos() functions. I have already written this formula as a scalar-valued function in my SQL Server.
So, my question is: - Is there a way to call a function (a UDF) within the Expression in a Derived Column dataflow item? and if not, - Hoy can I use trigonometric functions within the Expression in a Derived Column dataflow item?
I hope someona can tell me something about this... I'm falling into despair! :-s
I am reading an excel file with a field that consists of lastname, firstname. I am using the Derived Column transformation to separate the two fields. The firstname expression works fine: SUBSTRING(F1,FINDSTRING(F1,",",1) + 1,LEN(F1) - FINDSTRING(F1,",",1))
However, the lastname field keeps giving me an error when I use SUBSTRING(F1,1,FINDSTRING(F1,",",1) - 1). I'm sure I've used this substring before in visual studio. Could this be a bug? The error is below.
[Add Columns [2886]] Error: The "component "Add Columns" (2886)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "LastName" (3100)" specifies failure on error. An error occurred on the specified object of the specified component.
one of my SSIS packages use this expression to put todays date in if it is NULL:
(ISNULL(datejs)) ? GETDATE() : datejs
however, what I really want to do though is put a default date in like '2007-01-01' but I get syntax error because SSIS thinks it's a string, which it is I suppose.
I gotta use a Matrix since the number of months is dynamic.
In my test, I have 12 month columns. Under each Month column is a subordinate Count and a Difference column. In the Difference column, I want to calculate the difference in the Count columns relative to the previous month (if the Count for Jan is 15 and the Count for Feb is 27, then Feb's Difference column should be 12).
So, I am stuck trying to generate an expression for the Difference column. This expression needs to refer to the Count of the previous month.
RS's Previous() function cannot be used in a Matrix (because that's the compiler error that I get when I try to use it).
Does anybody know how to do this???
Do I have to use custom code to do this???
If I can't do this calculation in a matrix, then what good is it???
i am facing problem to dervie one column value from another column using either if and case statements in expression window of dervived columns transformation.
let me give the exapmle. i get 1 column from source system name as "col a" and i want to insert 2 columns into my destination as col A and col B. based on the values of col A i want to derive the values of col B,like if col A value is 0 then col B value is Good else BAD.
Can any one asssit in this regard how to achive it?? and is it possible to use IF and CASE statement in this dervived column tranformation???
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value OR to do System.DateTime.Now in the application and then insert it in the table? I figure even small differences would be magnified if there is moderate traffic, so every little bit helps. Thanks.
