Hello, I have declared two string variables in iReport qurey designer
DECLARE @str nvarchar(max), @str1 nvarchar (max);
Select @str = ' AND (BondSales_Confirmed <> -1) ', @str1 = ' AND (BondSales_IssueType = ''REGULAR'') ';
Now, If I try to execute a query like following:
SELECT * from t_BondSales WHERE (BondSales_cType <> 'Institute') +@str + @str1
It shows me following error:
Error: SQL Problems: Incorrect Syntax near '+'
Can anyone please suggest me what is the proper syntax to concatenate string variables in WHERE clause? I can do this in SQL Server 2008 R2, but how to do it in Jasper?
In SQL Server, I've done something like this:
DECLARE @sql NVARCHAR(MAX)
Select @sql = 'SELECT * from t_BondSales WHERE (BondSales_cType <> 'Institute')' +@str + @str1
EXEC sys.sp_executesql @sql //**This line executes the query
But I guess, I can not do that in iReport... What should I do?
3 Answers:
Hi,
Instead of using variables what you can do is that you can create two parameters and define the same two values as the default values of the two parameters
$P{param1} = "AND (BondSales_Confirmed <> -1)", $P{param2}= " AND (BondSales_IssueType = 'REGULAR') "
remember don't check the option use as prompt for the parameters as we have to use the defualt values defined above only
and in the query you can simply put this
SELECT * from t_BondSales WHERE (BondSales_cType <> 'Institute')
AND P!{param1}
AND P!{param2}
This will work.Please check the same
Hope that above explanation helps you.
Thanks,
Ankur Gupta
Hi Saquib,
The Concatenate function depends on the SQL engine that you are using.
Which Database are you using for your reports?
I think that condition that your are trying to test can be solve with :
WHERE NOT IN ('val1','val2','val3')
Hope it helps,
Kind regards,
Paulo
HI Ankur, I've tried several times to update my question, but the server isn't allowing me to do this. It shows that, the document is locked and I can't edit that :(
Whenever it allows me, I'll update my question. Thanks
You can add right here in a comment or can add again question with the description
Hi Ankur, I edited my question. If you can, please check and suggest me if you have any solution to that. Thanks