TRIM + Dynamic Expression
Nov 7, 2007
This is a very weird issue, and I can't seem to pinpoint the actual cause. I have a dynamic expression for Excel files that gets evaluated at run-time. One of the parameters for creating this path is pulled from the database - the column is nvarchar(10) and most of the values are only 2-characters long. I have tried LTRIM(RTRIM()) inside SQL Server as well as TRIM() in SSIS's Expression Editor, but nothing seems to work. At run-time the package fails, and the error I receive is because the value that gets pulled from the DB has trailing spaces. What's even annoying is the package runs fine on my XP machine, but fails on a W2K3 box - both setups are using the same back-end database.
Is this is a bug, or am I just screwing up something unknowingly? Thanks.
View 5 Replies
ADVERTISEMENT
Sep 18, 2007
I have a report which contains a parameter called SuppressZero which depending on its value I want to filter out certain data. This parameter can have 3 different values and for each value I need to have a different filter expression. What I would like to do is implement the following:
If SuppressZero = 1
Filter where Quantity <> 0
If SuppressZero = 2
No Filter
If SuppressZero = 3
Filter where Quantity <> 0 Or InStockFamily = "Y"
How can I do this in my report?
View 3 Replies
View Related
Jan 27, 2005
Hi I am trying to do something like the following:
DECLARE @Operator varchar(1)
DECLARE @Rate float
DECLARE @Quantity float
DECLARE @Converted float
SET @Quantity = 6
SET @Operator = '/'
SET @Rate = 2
SET @Converted = 0
@Converted = (@Quantity substituteTheValueOfThis(@Operator) @Rate)
PRINT @Converted
so that the output would be 3
The reason I need to do it like this is that @Operator will change at runtime...
Any suggestions appreciated, I have looked at EXEC sp_execsql but somehow can't get the syntax right.
View 5 Replies
View Related
Nov 13, 2006
I would like to run following T-SQL statement in the Derived Column:
declare @strData varchar(1000), @strSQL varchar(3000)
select @strdata = 'LANEX ~11/13/06 10:10:08 ~112233 9th ST S. Arlington, 22204, VA ~ 093300~XYZ '
select @strSQL ='select datalength('''+replace(@strData,'~','''),datalength(''') +''')'
--print @strSQL
exec (@strSQL)
Thank you!
View 4 Replies
View Related
Feb 8, 2007
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
View 4 Replies
View Related
Oct 28, 2015
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
View 7 Replies
View Related
May 17, 2004
is there a function trimming string such as Trim in vb?
View 2 Replies
View Related
Apr 3, 2008
IN ACCESS
UPDATE 14_Together_final_ICNs INNER JOIN 14_Together_UniqueSKUs ON [14_Together_final_ICNs].SKU = [14_Together_UniqueSKUs].SKU SET [14_Together_final_ICNs].sku_desc = [14_Together_UniqueSKUs].NewDesc
WHERE (((Trim(Nz([NewDesc])))<>""));
THIS IS WHAT I TRY IN SQL SEVER BUT GET A DIFFERNET RESULT
UPDATE [14_Together_final_ICNs]
SET sku_desc = [14_Together_UniqueSKUs].NewDesc
FROM [14_Together_final_ICNs] INNER JOIN
[14_Together_UniqueSKUs] ON [14_Together_final_ICNs].SKU = [14_Together_UniqueSKUs].SKU
WHERE (RTRIM(LTRIM([14_Together_final_ICNs].sku_desc))<> N'')
View 1 Replies
View Related
Jan 29, 2007
Guys/Girls
Have a small problem I have received a load of data in a flat file format and each column has dividend up with a comma. I have stuck the whole row in to its own column.
What I want to do is take the first 12 characters of the row and put them in there own column.
I think I need to use the Len function or the trim function, so can some body point me in the right direction on how to do this.
Regards Lee
View 9 Replies
View Related
Mar 16, 2007
Okay basic question from the village idiot. How do I return only the date and not the time from a datetime column and also can I return only a set number of digits in a float column?
Thanks in advance peter
The Yak Village Idiot
View 3 Replies
View Related
Aug 9, 2007
Hi I have the below paragraph in my datacolum called 'Description'
Ansmann Powerline 5 Super Fast Charger, Microprocessor controlled desktop charger with discharge function for up to 5 NiCd/NiMH cells, For all NiCd and NiMH rechargable batteries including AA/AAA/C/D/9V, Negative Delta V control, faulty cell detection, defined predischarge, trickle charge, separate status indicator for every charging slot, ultra rapid charging, 230V AC. For UK use. Price for 1 Charger.
How do I take only the sentence upto the first comma in my STORED procedure
(ex:Ansmann Powerline 5 Super Fast Charger)
Advance thanks
Vaishu
View 16 Replies
View Related
Apr 16, 2002
we need to cut the aprostrophe out of a name (ie...o'brien, o'leary) to create userid's... can this be done....thanks!
View 1 Replies
View Related
Jun 28, 2000
What is the syntax of left triming characters seperated by space.
e.g elsie reed
and i want elsie to go to one field and reed in another field i do not want to use numbers because they don't all have the same numbers but are all seperated by spaces
View 2 Replies
View Related
Nov 9, 2005
What is the best way to trim a string to the space in a select statement? An example string would be:
'asdf and hjkl'
The result would be:
'asdf'
View 1 Replies
View Related
Jan 3, 2007
hi guys,
i need to update a column by trimming the right end. i tried many things but its not working. can anyone tell me?
Regards,
David
View 12 Replies
View Related
Jul 5, 2007
OK, the problem is like this; say the table is sumthing like this
i have a table 't3' has 3 fields
id(number,2)
date(date/time)
text(varchar,200)
eg. id date text
-----------------------
01 05-jul-07 abcdefghijkl (i want to get rid of the first 6 characters i.e. abcdef)
i want to display 'text' field from t3 table but with the first 6 characters trimmed/removed (i want to get rid of the 6 first characters)
so i did:
select LTRIM(text,6) from t3
but the above code didn't work because LTRIM only trims spaces NOT characters... Is there any way that i can trim off the first 6 characters from every record in the 'text' field?
View 12 Replies
View Related
Dec 14, 2007
In my report, i have a TextBox which retrieve the result from the database.I am using this code :
=Fields!Category.Value.ToString().Trim(" ").TrimEnd(",").TrimStart(",")
But i am encountering a problem,wherein,when the value contains '&' (ex. Jog & Hop)
the result became Jog&Hop.
What could be wrong with my trim???
Thanks!!
View 1 Replies
View Related
Mar 14, 2006
"select RTrim(columName) from tableName"
it can change the stirng " abc" to "abc". But I want change the string "a bc" to "abc".
Why the TRIM cannot work ?
How do I do ?Thanks.
View 3 Replies
View Related
Jan 4, 2008
Whan using the TRIM() function in a SELECT we are getting an invalid function error. What gives? I thought TRIM() was T-SQL & not one of Microsoft's SQL "extras".
Thanks!
View 8 Replies
View Related
Mar 29, 2007
can any one explain how to trim a value on bothside in rss
for eg:
'asd '
' asd'
i wanna this output 'asd'............
can i give this command
=Format(Fields!company.Value.Trim)
View 1 Replies
View Related
Aug 19, 2015
I need the values as below using select query of my column.
I need to find the text after the first space ' ' and before the first '('
Source Column
==================
0636 KAVITHI (LOC)
0638 SRI KRISHNA (NAT)
0639 SELVAM
0643 GOOD SERVICE (LOC)
0644 FINA CARE EVENT (LOC)
Expected Result
==================
KAVITHI
SRI KRISHNA
SELVAM
GOOD SERVICE
FINA CARE EVENT
View 14 Replies
View Related
Nov 21, 2001
I’m trying to optimize the following view so that it runs faster and to trim the code where possible. It runs for 1:57mins. Is that good or it can run much faster than that? Where do l start? It’s a view for a report. Please help l’m running on SQL 2000
CREATE View SalesTest AS
SELECT dbo.Loan.loan_No AS [Loan No], dbo.Customer.customer_No AS [Customer No], dbo.Customer.first_Name + ' ' + dbo.Customer.surname AS Customer,
dbo.Employer.employer_Name AS [Employer Name], dbo.Loan.store AS [Store No], dbo.Store.store_Name AS Store,
dbo.Region.region_Description AS Region, dbo.Financier.financier_Short + dbo.Term.term_Description AS Product,
dbo.Loan.date_Issued AS [Transaction Date], dbo.Loan.capital_Amount AS [Capital Amount], dbo.Loan.interest_Amount AS [Interest Amount],
dbo.Loan.interim_Interest_Amount AS [Interim Interest Amount], dbo.Loan.interest_Amount + dbo.Loan.interim_Interest_Amount AS [Interest2 Amount],
dbo.Loan.insurance_Amount AS [Insurance Amount], dbo.Loan.admin_Fee AS [Admin Fee], dbo.Loan.total_Amount AS [Total Amount],
dbo.Loan_Type.loan_Type_Description AS [Loan Type Description], dbo.Loan.user_Changed AS [User], dbo.Loan.first_Payment AS [First Payment],
dbo.Loan.monthly_Payment AS [Monthly Payment], dbo.Loan.repayment_Period AS [Repayment Period],
dbo.Loan.outstanding_Amount AS [Outstanding Amount], dbo.Loan.last_Payment_Date AS [Last Payment Date],
dbo.Status.status_Description AS Status, CONVERT(Char(3), dbo.Loan.loan_No, 1) AS Company, dbo.Customer.physical_Address1 AS Mine1,
dbo.Customer.physical_Address2 AS Mine2, dbo.Customer.physical_Address3 AS Mine3, dbo.Loan.maturity_Date AS [Maturity Date],
dbo.Agent.agent_Short AS Agent, dbo.Financier.financier_Short AS Financier, dbo.Loan.product AS [Loan Product],
dbo.Deduction_Detail.teba_Account_No AS [Teba Account No]
FROM dbo.Loan INNER JOIN
dbo.Customer ON dbo.Customer.customer_No = dbo.Loan.customer_No INNER JOIN
dbo.Status ON dbo.Status.status = dbo.Loan.status INNER JOIN
dbo.Store ON dbo.Store.store = dbo.Loan.store INNER JOIN
dbo.Product ON dbo.Product.product = dbo.Loan.product INNER JOIN
dbo.Product_Type ON dbo.Product_Type.product_Type = dbo.Product.product_Type INNER JOIN
dbo.Financier ON dbo.Financier.financier = dbo.Product_Type.financier INNER JOIN
dbo.Term ON dbo.Term.term = dbo.Product.term INNER JOIN
dbo.Employer ON dbo.Employer.employer = dbo.Customer.employer INNER JOIN
dbo.Region ON dbo.Region.region = dbo.Store.region INNER JOIN
dbo.Loan_Type ON dbo.Loan_Type.loan_Type = dbo.Product_Type.loan_Type INNER JOIN
dbo.Agent ON dbo.Agent.agent = dbo.Product_Type.agent INNER JOIN
dbo.Deduction_Detail ON dbo.Loan.customer_No = dbo.Deduction_Detail.customer_No AND
dbo.Loan.deduction_No = dbo.Deduction_Detail.deduction_No
WHERE (dbo.Loan.outstanding_Amount <> 0)
View 1 Replies
View Related
Dec 12, 2001
Is it possible to use a TRIM(){or similar} function when JOINing 2 tables via a NVARCHAR field in T-SQL?
I need to trim the field first because some of the data contains trailing spaces (GRRR) and so might future data...
View 2 Replies
View Related
Jan 2, 2002
I have a SQL table which has field data like below
field a field b
123 KB 246 kb
How can I strip out just the numbers(there will always be a space between the number and KB?
Thanks
View 3 Replies
View Related
Sep 17, 2001
hi I have this field
invoice#
007500
190000
000080
I need to run a query to trim the left zeros out and have the following output
invoice#
7500
190000
80
how can I do that in sql select ... or with update table set invoice#=..??
Thanks for your help
Al
View 3 Replies
View Related
Mar 26, 2004
I have a table with a keyword field. In this keyword field some of the entries have a space before the word and some don't.
How can I remove all of the spaces from the fields that have them and not trim from the ones that don't.
View 1 Replies
View Related
Jun 20, 2006
In a table I have a coulmn of postal-codes where there is a space in the middle of the postcode. How do I do to trim them away?
View 6 Replies
View Related
Feb 22, 2007
str1 has some space in front of it and after it.
trim(str1) does not seem to work.
what is the right syntax for that?
Thanks
View 2 Replies
View Related
Sep 25, 2007
For a bulk insert is there a way to trim the data that comes in with a fixed length of say 45 characters but only 14 used and the rest is all spaces?
View 1 Replies
View Related
Mar 28, 2008
Guys,
I need to cleanup a query on Item Numbers.
I don't want to edit the data... just display it clean.
I want to show ItemNumbers without any 'S' on the left, and without any '501' or '601' on the right.
What do you think is my best bet fot getting this?
I was thinking about using TRIM, but maybe that's just for whitespace.
View 3 Replies
View Related
Feb 28, 2008
My records are like this.
Col1
-----
00001
03456
00577
05011
00099
01090
I want to remove the zeros on the left and the answer should be like this.
Col1
-----
1
3456
577
5011
99
1090
How to trim the leading zeros.
Thanks.
View 11 Replies
View Related
Nov 10, 2005
I cannot believe that there is yet another version of SQL Server without a TRIM function. So for SQL 2005 (like I've done in 7.0 and 2000) my T-SQL will look like dog poo because I will have to write LTRIM(RTRIM(MyValue)) anytime I want to remove whitespace from both ends of character values instead of just writing TRIM(MyValue).
View 15 Replies
View Related
Jan 25, 2007
I have values in a matrix takin too much space. Since they are just used as the matrix column header, cant i trim it or something. To display just the first 3 letters of the the attribute, and not the whole string? Whats the code for that?
=Fields!Collision_Type.Value thats my field right now.
Please help! thanks!
View 8 Replies
View Related
