Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Array of Strings to string. Expand / Collapse
Author
Message
Posted Friday, March 14, 2014 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:04 PM
Points: 5, Visits: 37
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.







Post #1551327
Posted Friday, March 14, 2014 1:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 13,291, Visits: 12,140
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1551331
Posted Friday, March 14, 2014 1:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:04 PM
Points: 5, Visits: 37
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.


Post #1551342
Posted Friday, March 14, 2014 2:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 13,291, Visits: 12,140
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1551351
Posted Friday, March 14, 2014 3:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:04 PM
Points: 5, Visits: 37
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.






Post #1551387
Posted Friday, March 14, 2014 3:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 13,291, Visits: 12,140
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1551392
Posted Friday, March 14, 2014 3:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:04 PM
Points: 5, Visits: 37
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)


Post #1551395
Posted Friday, March 14, 2014 3:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 13,291, Visits: 12,140
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1551396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse