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.