March 14, 2014 at 1:22 pm
Hi! I've got the following problem:
I'm designing a report in Business Objects and I would like to do the following:
I'm working with a multi value parameter and I would like to do the opposite of converting a string in an array for an IN( ) clause. I would like to convert an array of strings into a single string with all the values.
Here is an example of a main query of a report:
SELECT Code,
@PromptFromBusinessObjects AS ValuesSelected
FROM Table
WHERE Code IN( @PromptFromBusinessObjects )
If I select one value, there's no error:
SELECT Code,
'Value1' AS ValuesSelected
FROM Table
WHERE Code IN( 'Value1' )
But If I select two or more values I've got a syntax error:
SELECT Code,
'Value1', 'Value2' AS ValuesSelected --It gets me an error.
FROM Table
WHERE Code IN( 'Value1', 'Value2' )
I realize that the only valid place you can use the array of strings is enclosed in an IN statement.
If I want to use the REPLACE function or I want to add single quotes it doesn't work.
I can't use CTE, TEMP Tables or SPs, I have to resolve this in the SELECT query (I can use subqueries).
A valid SQL Query should look like this:
SELECT Code,
'''Value1'', ''Value2''' AS ValuesSelected --This should run fine.
FROM Table
WHERE Code IN( 'Value1', 'Value2' )
Please let me know your thoughts about this issue.
Thanks,
Andrew.
March 14, 2014 at 1:33 pm
First of all I want to confirm that you are actually using sql 2000.
I can't use CTE, TEMP Tables or SPs, I have to resolve this in the SELECT query (I can use subqueries).
This is a rather strange requirement. If you are truly using sql 2000 then I understand the restriction on cte since they didn't exist yet. No temp table or SP sounds like an artificial restriction that is often found in homework assignments.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 1:54 pm
Sean Lange (3/14/2014)
First of all I want to confirm that you are actually using sql 2000.I can't use CTE, TEMP Tables or SPs, I have to resolve this in the SELECT query (I can use subqueries).
This is a rather strange requirement. If you are truly using sql 2000 then I understand the restriction on cte since they didn't exist yet. No temp table or SP sounds like an artificial restriction that is often found in homework assignments.
Hi Sean,
First all all: thanks for your quick reply.
The problem is that I have to place the SQL query in the report interface of Business Object. That's why I can't use SPs, CTE or Temp Tables.
In SSRS, You can work with CTEs and Temp Tables. But in Business Objects you can only run one query. You can use subqueries and the UNION operator, and you can build a huge SQL query that will run, but you can't create multiple queries that will feed one last query.
I'm working with SQL Server 2005. And also, I don't have permissions to modify the DB. I can only run SELECT queries. The DBA can do all the operations.
March 14, 2014 at 2:09 pm
andrexcobar (3/14/2014)
Sean Lange (3/14/2014)
First of all I want to confirm that you are actually using sql 2000.I can't use CTE, TEMP Tables or SPs, I have to resolve this in the SELECT query (I can use subqueries).
This is a rather strange requirement. If you are truly using sql 2000 then I understand the restriction on cte since they didn't exist yet. No temp table or SP sounds like an artificial restriction that is often found in homework assignments.
Hi Sean,
First all all: thanks for your quick reply.
The problem is that I have to place the SQL query in the report interface of Business Object. That's why I can't use SPs, CTE or Temp Tables.
In SSRS, You can work with CTEs and Temp Tables. But in Business Objects you can only run one query. You can use subqueries and the UNION operator, and you can build a huge SQL query that will run, but you can't create multiple queries that will feed one last query.
I'm working with SQL Server 2005. And also, I don't have permissions to modify the DB. I can only run SELECT queries. The DBA can do all the operations.
Ahh gotcha. I wanted to be sure because you posted in the sql 2000 forum. Since you are working in 2005 you can very easily do this. Take a look at my signature about splitting strings.
Your query will end up something like this.
SELECT s.Item as Code,
@PromptFromBusinessObjects AS ValuesSelected
FROM Table
inner join dbo.DelimitedSplit8K(@PromptFromBusinessObjects, ',') s on s.Item = Table.Code
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 3:07 pm
Sean Lange (3/14/2014)
andrexcobar (3/14/2014)
Sean Lange (3/14/2014)
First of all I want to confirm that you are actually using sql 2000.I can't use CTE, TEMP Tables or SPs, I have to resolve this in the SELECT query (I can use subqueries).
This is a rather strange requirement. If you are truly using sql 2000 then I understand the restriction on cte since they didn't exist yet. No temp table or SP sounds like an artificial restriction that is often found in homework assignments.
Hi Sean,
First all all: thanks for your quick reply.
The problem is that I have to place the SQL query in the report interface of Business Object. That's why I can't use SPs, CTE or Temp Tables.
In SSRS, You can work with CTEs and Temp Tables. But in Business Objects you can only run one query. You can use subqueries and the UNION operator, and you can build a huge SQL query that will run, but you can't create multiple queries that will feed one last query.
I'm working with SQL Server 2005. And also, I don't have permissions to modify the DB. I can only run SELECT queries. The DBA can do all the operations.
Ahh gotcha. I wanted to be sure because you posted in the sql 2000 forum. Since you are working in 2005 you can very easily do this. Take a look at my signature about splitting strings.
Your query will end up something like this.
SELECT s.Item as Code,
@PromptFromBusinessObjects AS ValuesSelected
FROM Table
inner join dbo.DelimitedSplit8K(@PromptFromBusinessObjects, ',') s on s.Item = Table.Code
Thank you Sean. I have tested the function, and I have to say that I did a mistake.
The correct example of the main query is as follows:
SELECT Code,
@PromptFromBusinessObjects AS ValuesSelected
FROM Table
WHERE Code IN @PromptFromBusinessObjects --without the parenthesis.
So this will be the examples:
SELECT Code,
('Value1') AS ValuesSelected --with parenthesis
FROM Table
WHERE Code IN( 'Value1' )
SELECT Code,
('Value1', 'Value2') AS ValuesSelected --It gets me an error. With parenthesis.
FROM Table
WHERE Code IN( 'Value1', 'Value2' )
I did a little test of the DelimitedSplit8K function, with the following result:
SELECT s.Item
FROM dbo.DelimitedSplit8K(('Value1','Value2'), ',') s
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
I was thinking in trying to pull the values that are present in the IN statement. Something like:
SELECT
...
FROM ...
WHERE Code IN @PromptWithValues
At runtime it will show the following:
SELECT
...
FROM ...
WHERE Code IN ('Value1', 'Value2', 'Value3')
And I would like the select statement to return the following:
Code
--------------------------
'Value1','Value2','Value3'
(1 row(s) affected)
So, I would like an array of strings to be converted into a single string. Any ideas?
Best,
Andrew.
March 14, 2014 at 3:22 pm
Well you first example was using a parameter. Not sure what you mean by an array of strings. How do you get this into sql?
You seem to be jumping back and forth between string literals and parameters. Honestly your query doesn't make any sense because you have IN with no parenthesis. Are you passing the whole string including the parenthesis? How are you running this? This really isn't that hard but you aren't providing much detail for me to work with here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 3:35 pm
Sean Lange (3/14/2014)
Well you first example was using a parameter. Not sure what you mean by an array of strings. How do you get this into sql?You seem to be jumping back and forth between string literals and parameters. Honestly your query doesn't make any sense because you have IN with no parenthesis. Are you passing the whole string including the parenthesis? How are you running this? This really isn't that hard but you aren't providing much detail for me to work with here.
The query is placed in Business Objects. The parameter is also from Business Objects. It's a Multi value parameter that includes parenthesis.
So the @parameter is equal to: ('Value1', 'Value2', 'Value3') --parenthesis included.
So when you use that parameter in a IN statement, it works perfectly. But it seems that it only works exclusively with the IN statement.
Maybe a better example will be the following:
SELECT ('Value1', 'Value2') --This will get an error.
How can you work that query in order to get the following result:
Code
-----------------
'Value1', 'Value2'
(1 row(s) affected)
March 14, 2014 at 3:39 pm
andrexcobar (3/14/2014)
Sean Lange (3/14/2014)
Well you first example was using a parameter. Not sure what you mean by an array of strings. How do you get this into sql?You seem to be jumping back and forth between string literals and parameters. Honestly your query doesn't make any sense because you have IN with no parenthesis. Are you passing the whole string including the parenthesis? How are you running this? This really isn't that hard but you aren't providing much detail for me to work with here.
The query is placed in Business Objects. The parameter is also from Business Objects. It's a Multi value parameter that includes parenthesis.
So the @parameter is equal to: ('Value1', 'Value2', 'Value3') --parenthesis included.
So when you use that parameter in a IN statement, it works perfectly. But it seems that it only works exclusively with the IN statement.
Maybe a better example will be the following:
SELECT ('Value1', 'Value2') --This will get an error.
How can you work that query in order to get the following result:
Code
-----------------
'Value1', 'Value2'
(1 row(s) affected)
SAP BusinessObjects?
I have no experience at all with that but maybe you can do something like this?
select replace(replace(@parameter, '(', ''), ')', '')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply