Hi,
I have the following Oracle SQL Query in Report Studio:
select account_number from EDW_OWNER.DIM_CUST_ACCOUNT hca
where
hca.account_number in ( #promptmany('param_customer_number','string',"'ZZZZZZ'")# )
The report runs OK when one value is entered into the prompt, but errors when more than one value is entered.
The following query runs but returns no data:
select account_number from EDW_OWNER.DIM_CUST_ACCOUNT hca
where
hca.account_number in ( #sq(promptmany('param_customer_number','string',"'ZZZZZZ'"))# )
The expression #sq(promptmany('param_customer_number','string',"'ZZZZZZ'"))# when output on the report page returns the correct format for the where clause, i.e. 'parameter value1','parameter value2'.
Database is Oracle.
Any suggestions?
Thanks,
Toby
Try this:
select account_number from EDW_OWNER.DIM_CUST_ACCOUNT hca
where
hca.account_number in ( #promptmany('param_customer_number','string', sq('ZZZZZZ') )# )
If it does not work then please let us not the error message you are getting
Thanks that works.
However, when I expand this so that it returns all records when no parameter is passed it errors:
hca.account_number in ( #promptmany('param_customer_number','string',sq('ZZZZZZ'))# ) or (#promptmany('param_customer_number','string',sq('ZZZZZZ'))#) in ( 'ZZZZZZ' )
XQE-DAT-0001
Data source adapter error: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
Suggestions?
Many thanks.
Can you create a new query with this data item:
#sq(promptmany('param_customer_number','string',sq('ZZZZZZ')))#
Run that query and populate the parameter with the values you're passing when you get the error.
Hi,
Yes I can, it returns this:
'100000396317', '100000385203'
I modified the where clause on the original query to the following and it runs OK but returns no data:
hca.account_number in ( #sq(promptmany('param_customer_number','string',sq('ZZZZZZ')))# ) or (#sq(promptmany('param_customer_number','string',sq('ZZZZZZ')))#) in ( 'ZZZZZZ' )
Suggestions?
Many thanks for your help.
What is the exact error you're getting?
using sq() in the macro won't help you, because you'd end up with a where clause like
hca.account_number in ( '\'100000396317\', \'100000385203\'' )
can you test if hca.account_number in ( '100000396317', '100000385203') returns an error?
if account_number is an actual number, not a string, try replacing "string" with "integer".
Following your suggested change it runs without error.
The #sq(promptmany('param_customer_number','string',sq('ZZZZZZ')))# returns this in the new query:
'100000396317', '100000385203'
I modified the where clause on the original query to the following and it runs OK but returns no data:
hca.account_number in ( #sq(promptmany('param_customer_number','string',sq('ZZZZZZ')))# ) or (#sq(promptmany('param_customer_number','string',sq('ZZZZZZ')))#) in ( 'ZZZZZZ' )
hca.account_number in ( '100000396317', '100000385203') returns data as expected.
It's a string not an integer.
Using this (which was before your suggested change):
hca.account_number in ( #promptmany('param_customer_number','string',sq('ZZZZZZ'))# ) or (#promptmany('param_customer_number','string',sq('ZZZZZZ'))#) in ( 'ZZZZZZ' )
Returns this error:
XQE-DAT-0001
Data source adapter error: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
Suggestions?
The fact that Oracle is complaining about the invalid relational operator might be a clue about approaching the problem. Try simplifying the prompt so that only one of the filter expressions on either side of the OR operator is being used and see what happens.
Also try this:
hca.account_number in ( #sq(promptmany('param_customer_number','string',sq('ZZZZZZ')))# ) or
hca.account_number in
(#sq(promptmany('param_customer_number','string',sq('ZZZZZZ')))#) in ( 'ZZZZZZ' )
Wait, I think I got it. It's this part:
('100000396317', '100000385203') in ('ZZZZZZ')
I strongly suspect this will do exactly what you need:
#promptmany(
'param_customer_number'
,'string'
,'1=1'
,' hca.account_number in ('
, ''
, ')'
)#
so the query would look something like:
select *
from
table hca
where
#promptmany('param_customer_number','string','1=1',' hca.account_number in (', '', ')')#
Use this:
select account_number from EDW_OWNER.DIM_CUST_ACCOUNT hca
where
hca.account_number in ( #promptmany('param_customer_number','string',
'hca.account_number' )# )
If you do not supply any parameter values then the SQL should be generated as:
select account_number from EDW_OWNER.DIM_CUST_ACCOUNT hca
where hca.account_number in ( hca.account_number)
Quote from: Ricardo Julio "Ricky" Villa on 19 Dec 2018 05:14:57 AM
Thanks that works.
However, when I expand this so that it returns all records when no parameter is passed it errors:
hca.account_number in ( #promptmany('param_customer_number','string',sq('ZZZZZZ'))# ) or (#promptmany('param_customer_number','string',sq('ZZZZZZ'))#) in ( 'ZZZZZZ' )
XQE-DAT-0001
Data source adapter error: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
Suggestions?
Many thanks.
@CognosPaul and Stork - Both your solutions work.
Many thanks for your time and effort on this, much appreciated.
Cheers,
Toby