February 25, 2008 at 3:49 pm
Hi,
I have yet another problem that I am facing with the query.
I am trying to run a rport from my ASP.NET code that uses the query we wrote earlier.
My SQL Query:
SELECTj2821.RepName, j2821.LogEntryID, b.BranchName, r.RepFullName As PortlandSPName, l.LogEntryStatus, j2821.Transaction2821ID, j2821.TransactionPercentOfNetWorth,
j2821.AnnualIncome, j2821.TimeHorizon, j2821.RegistrationType, j2821.DateForwarded, j2821.AccountNumber, j2821.Amount,
j2821.ProductDesc, j2821.TransactionType, j2821.Description, j2821.ClientName, j2821.Provider,
j2821.ProductType, DATEDIFF(day, j2821.DateOfTrust, GETDATE()) As ClientAge,
substring(
case when (@operation = '>' AND j2821.Amount > @Amount) then 'Trade Amount Inquiry, ' else '' end +
case when (@OpClientAge = '>' AND DATEDIFF(day, j2821.DateOfTrust, GETDATE()) > @ClientAge) then 'Client Age Inquiry, ' else '' end +
case when (@OpIncome = '>' AND j2821.AnnualIncome > @Income) then 'Annual Income Inquiry, ' else '' end +
case when (@operation = '<' AND j2821.Amount < @Amount) then 'Trade Amount Inquiry, ' else '' end +
case when (@OpClientAge = '<' AND DATEDIFF(day, j2821.DateOfTrust, GETDATE()) < @ClientAge) then 'Client Age Inquiry, ' else '' end +
case when (@OpIncome = '<' AND j2821.AnnualIncome < @Income) then 'Annual Income Inquiry, ' else '' end +
case when (@operation = '=' AND j2821.Amount = @Amount) then 'Trade Amount Inquiry, ' else '' end +
case when (@OpClientAge = '=' AND DATEDIFF(day, j2821.DateOfTrust, GETDATE()) = @ClientAge) then 'Client Age Inquiry, ' else '' end +
case when (@OpIncome = '=' AND j2821.AnnualIncome = @Income) then 'Annual Income Inquiry, ' else '' end +
case when (j2821.RegistrationType = @RegistrationType) then 'Qualified Account, ' else '' end +
case when (j2821.TimeHorizon IN (@TimeHorizon)) then 'Time Horizon Inquiry, ' else '' end, 1,
len(
case when (@operation = '>' AND j2821.Amount > '') then 'Trade Amount Inquiry, ' else '' end +
case when (@OpClientAge = '>' AND DATEDIFF(day, j2821.DateOfTrust, GETDATE()) > @ClientAge) then 'Client Age Inquiry, ' else '' end +
case when (@OpIncome = '>' AND j2821.AnnualIncome > @Income) then 'Annual Income Inquiry, ' else '' end +
case when (@operation = '<' AND j2821.Amount < '') then 'Trade Amount Inquiry, ' else '' end +
case when (@OpClientAge = '<' AND DATEDIFF(day, j2821.DateOfTrust, GETDATE()) < @ClientAge) then 'Client Age Inquiry, ' else '' end +
case when (@OpIncome = '<' AND j2821.AnnualIncome < @Income) then 'Annual Income Inquiry, ' else '' end +
case when (@operation = '=' AND j2821.Amount = @Amount) then 'Trade Amount Inquiry, ' else '' end +
case when (@OpClientAge = '=' AND DATEDIFF(day, j2821.DateOfTrust, GETDATE()) = @ClientAge) then 'Client Age Inquiry, ' else '' end +
case when (@OpIncome = '=' AND j2821.AnnualIncome = @Income) then 'Annual Income Inquiry, ' else '' end +
case when (j2821.RegistrationType = @RegistrationType) then 'Qualified Account, ' else '' end +
case when (j2821.TimeHorizon IN (@TimeHorizon)) then 'Time Horizon Inquiry, ' else '' end) - 1) as MatchedCriteria
FROM
Transaction2821Journals j2821 INNER JOIN
Journals j ON j2821.LogEntryID=j.LogEntryID WHERE
j.JournalTypeID = 8
AND j2821.DateClientSignature >= '2/2/2008'
AND j2821.DateClientSignature <= '2/25/2008'AND (j2821.ProductType IN ('VA - INDIVIDUAL','GVA - PARTICIPANT (RR Recommended Investment Selection)'))
AND (j2821.IsReplacementExchange = @IsReplacementExchange)
AND
(
CASE
WHEN ((@operation = '>' AND j2821.Amount > @Amount)
OR (@OpTransPercent = '>' AND j2821.TransactionPercentOfNetWorth > @TransPercentLNW)
OR (@OpClientAge = '>' AND DATEDIFF(day, j2821.DateOfTrust, GETDATE()) > @ClientAge)
OR (@OpIncome = '>' AND j2821.AnnualIncome > @Income)
OR (j2821.RegistrationType = @RegistrationType)
OR (j2821.TimeHorizon IN (@TimeHorizon))) THEN 'True'
WHEN ((@operation = '<' AND j2821.Amount < @Amount)
OR (@OpTransPercent = '<' AND j2821.TransactionPercentOfNetWorth < @TransPercentLNW)
OR (@OpClientAge = '<' AND DATEDIFF(day, j2821.DateOfTrust, GETDATE()) < @ClientAge)
OR (@OpIncome = '<' AND j2821.AnnualIncome < @Income)
OR (j2821.RegistrationType = @RegistrationType)
OR (j2821.TimeHorizon IN (@TimeHorizon))) THEN 'True'
WHEN ((@operation = '=' AND j2821.Amount = @Amount)
OR (@OpTransPercent = '=' AND j2821.TransactionPercentOfNetWorth = @TransPercentLNW)
OR (@OpClientAge = '=' AND DATEDIFF(day, j2821.DateOfTrust, GETDATE()) = @ClientAge)
OR (@OpIncome = '=' AND j2821.AnnualIncome = @Income)
OR (j2821.RegistrationType = @RegistrationType)
OR (j2821.TimeHorizon IN (@TimeHorizon))) THEN 'True'
WHEN ((@operation = '-1') OR (@OpTransPercent = '-1') OR (@OpIncome = '-1') OR (@OpClientAge = '-1')
OR (j2821.RegistrationType = @RegistrationType) OR ((j2821.TimeHorizon IN (@TimeHorizon) OR ('-1') IN (@TimeHorizon)))) THEN 'True'
ELSE 'False'
END
) = 'True'
ORDER BY j2821.RepName, j2821.Transaction2821ID
However when i pass blank for the Amount, Income, ClientAge then i get an error from the report saying " An error has occurred during report processing. ---> Cannot read the next data row for the data set dsJournals. ---> Invalid length parameter passed to the SUBSTRING function"
I am unable to find out why that error is occuring.Can you help me with this?
February 25, 2008 at 3:53 pm
I am sorry I didn't realise it was a big chunk of code.
I have now attached the script for my sql statement.
February 25, 2008 at 4:14 pm
The error you are getting is because somehow passing an empty string is causing the len function to return 0 and then you are subtracting 1 and ending up with a Substring(value, 1, -1) and - 1 is an invalid length. You need to pass 0 or higher. The simplest fix is to put a character instead of '' in your else's. The len function returns 0 if there is only spaces in the value so you need something other than a space.
I have to ask, what does your execution plan look like for this query? Could you run with Set Statistics IO On and post the results?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2008 at 10:48 am
Thanks, I got that working.
At some times the len was coming out to be zero that's why the error.
I appreciate your efforts.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply