How to Concatenate String variable in to WHERE clause?

[saquib.waheed]

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?

[Ankur Gupta]

Hi,

 

Probably you cannot use a variable directly in the query,you will have to make use of parameter . Please describe as to what you want to do so that I can help you better

 

Thanks,

Ankur Gupta

[saquib.waheed]

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

[Ankur Gupta]

You can add right here in a comment or can add again question with the description

[saquib.waheed]

Hi Ankur, I edited my question. If you can, please check and suggest me if you have any solution to that. Thanks

[Ankur Gupta]

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

 

[saquib.waheed]

Thanks for the reply Ankur. I've tried your way but somehow, couldn't succeed. So, I tried another way through SQL Server and now it is working. I really appreciate your effort.

[pssp25]

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