October 22, 2011 at 5:35 am
Any suggestions on modifying the query to return results?
Drop Table #storage
Create Table #storage(
acct_num numeric(18,0) Not null,
comments nvarchar(255) Null)
-- insert acct_num and comments into temp table
Insert into #storage (acct_num,comments)
Select A.acct_num,
Case when Convert(nvarchar(255),Left(A.Comments,Charindex('|', A.Comments,1)-1)) < 0
then ''
Else Convert(nvarchar(255),Left(A.Comments,Charindex('|', A.Comments,1)-1))
End as Comments
From datasource A
Select Top 20 *
from #storage
October 22, 2011 at 8:09 am
This statement:
when Convert(nvarchar(255),Left(A.Comments,Charindex('|', A.Comments,1)-1)) < 0
is what is giving your error.
You are doing a less than comparison between nvarchar data and an integer here.
I assume you mean to return '' if the result of that convert statement is null, in that case then replace the statement above with this:
when Convert(nvarchar(255),Left(A.Comments,Charindex('|', A.Comments,1)-1)) IS NULL
October 22, 2011 at 11:38 am
This one is tricky. I tried the "Is Null" at the end of the statement and the error returns...
Invalid length parameter passed to the LEFT or SUBSTRING function.
October 22, 2011 at 12:32 pm
October 23, 2011 at 5:37 am
Sample data from the field I am using charindex on is listed below:
File Name:BTPN_02042011 BFTPN_02042011| Vendor: Urban | Product: Trial Offers_SD10-01 | Letter Type: Trial Offers_SD10-01 | Letter Number: LMO-0206 | Initial Volume:944 | Final Volume:786 | QCC: QC name | Approved to Mail Date: 2/4/2011
baaltpn_08162011_(44206)samples baaltpn_f_08162011_(44208)samples | Vendor:Urban|C3_536 HMPTAPBAU0E |Initial 3| Final 3| QCC: Customer name| Trial Offers Appeals_SD10-01| Approved to Mail Date:8/16/2011
File Name: BACGENTPN_08312010 | Vendor: Stewart | Product: Trial Offers_SD10-01 | Letter Type: Trial Offers_SD10-01 | Letter Number: LMO-0206 | Initial Volume: 5365 | Final Volume: 1777| Approved to Mail Date: 09/01/2010
Regarding DDL, what specific information do you want to see? I have restrictions on things I can see on database tables. Most of my access is read only.
October 23, 2011 at 6:12 am
Another point. The field does contain Null values. Therefore, the real error to deal with is:
Invalid length parameter passed to the LEFT or SUBSTRING function.
Any suggestions based on my previous responses is appreciated.
October 23, 2011 at 9:50 am
Split the values with this, then only get the value you need.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply