November 24, 2020 at 2:32 pm
I have an SSRS paginated report where users need the ability to pick and choose which variables they want the report run with. For example, the query looks something like:
SELECT *
FROM CUSTOMER
WHERE 1=1
AND CUSTOMER_NUMBER = ?
AND CUSTOMER_STATE = ?
The users need the ability to plug in a CUSTOMER_NUMBER parameter OR a CUSTOMER_STATE parameter. So for instance if they decide to provide a customer number, "x", the query would read:
SELECT *
FROM CUSTOMER
WHERE 1=1
AND CUSTOMER_NUMBER = x
leaving the "AND CUSTOMER_STATE = ?" line blank. In SQL server I've been able to achieve this by using something like the query below.
DECLARE @cust_t as varchar(MAX)
IF @cust <> ''
SET @cust_t = 'AND CUSTOMER_NUMBER IN (' + '''''' + REPLACE(@cust,',',''''',''''') + '''''' + ')';
ELSE
SET @cust_t = ' ';
DECLARE @state_t as varchar(MAX)
IF @state <> ''
SET @state_t = 'AND CUSTOMER_STATE IN (' + '''''' + REPLACE(@state,',',''''',''''') + '''''' + ')';
ELSE
SET @state_t = ' ';
DECLARE @SQL as varchar(MAX)
SET @SQL = 'SELECT *
FROM CUSTOMER
WHERE 1=1
' + @cust_t + '
' + @state_t ;
EXEC(@SQL)
Where the user provides @cust and/or @state. I just can't figure out how to do this with a native Oracle connection.
November 25, 2020 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply