PromptMany SQL Macro not Working

[Ricardo Julio "Ricky" Villa]

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

[Andrei I]

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

[Ricardo Julio "Ricky" Villa]

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]

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.

[Ricardo Julio "Ricky" Villa]

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.

[CognosPaul]

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".

[Ricardo Julio "Ricky" Villa]

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?

[bus_pass_man]

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' )

[CognosPaul]

Wait, I think I got it. It's this part:

('100000396317', '100000385203') in ('ZZZZZZ')

I strongly suspect this will do exactly what you need:

Code Select Expand


#promptmany(
   'param_customer_number'
  ,'string'
  ,'1=1'
  ,' hca.account_number in ('
  , ''
  , ')'
)#



so the query would look something like:

Code Select Expand

select *
from
  table hca
where
#promptmany('param_customer_number','string','1=1',' hca.account_number in (', '', ')')#


[Andrei I]

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)

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.

[Ricardo Julio "Ricky" Villa]

@CognosPaul and Stork - Both your solutions work.

Many thanks for your time and effort on this, much appreciated.

Cheers,

Toby