How to Concatenate String variable in to WHERE clause?

0

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?

saquib.waheed's picture
Joined: Jul 22 2013 - 9:26pm
Last seen: 5 years 2 months ago

3 Answers:

0

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

Ankur Gupta's picture
Joined: Jan 21 2013 - 10:36pm
Last seen: 11 months 3 days ago

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

saquib.waheed - 5 years 9 months ago

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

Ankur Gupta - 5 years 9 months ago

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

saquib.waheed - 5 years 9 months ago
0

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

 
Ankur Gupta's picture
Joined: Jan 21 2013 - 10:36pm
Last seen: 11 months 3 days ago

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.

saquib.waheed - 5 years 9 months ago
0

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

pssp25's picture
-3
Joined: Feb 28 2013 - 6:58am
Last seen: 5 years 8 months ago
Feedback
randomness