If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

PromptMany SQL Macro not Working

Started by Ricardo Julio "Ricky" Villa, 18 Dec 2018 05:04:06 AM

Previous topic - Next topic

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:


#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 (', '', ')')#

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)


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.

Ricardo Julio "Ricky" Villa

@CognosPaul and Stork - Both your solutions work.

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

Cheers,

Toby