Prompt Macro for optional SQL like wildcard filter

[Ricardo Julio "Ricky" Villa]

Hi,

I have a text prompt and SQL query in Report Studio.

The requirement is to apply a like where clause with wildcards to the SQL query as follows:

Use case: text prompt = John
select hca.account_name
from DIM_CUST_ACCOUNT hca
where upper(hca.account_name) like '%JOHN%'

Use case: text prompt = null
select hca.account_name
from DIM_CUST_ACCOUNT hca

My starting point is:
upper(hca.account_name) like #prompt('param_customer_name','string')#

Any suggestions on how to expand the macro to accommodate the optional filter and wildcards would be very much appreciated.

Many thanks...

[Lynn]

Hi,

I have a text prompt and SQL query in Report Studio.

The requirement is to apply a like where clause with wildcards to the SQL query as follows:

Use case: text prompt = John
select hca.account_name
from DIM_CUST_ACCOUNT hca
where upper(hca.account_name) like '%JOHN%'

Use case: text prompt = null
select hca.account_name
from DIM_CUST_ACCOUNT hca

My starting point is:
upper(hca.account_name) like #prompt('param_customer_name','string')#

Any suggestions on how to expand the macro to accommodate the optional filter and wildcards would be very much appreciated.

Many thanks...

Does this work? If optional it would resolve to name like '%%' which would return everything.

Code Select Expand


upper ( hca.account_name ) like '%' || #toupper(prompt ( 'param_customer_name', 'string' ))# || '%'


[MFGF]

Hi,

I have a text prompt and SQL query in Report Studio.

The requirement is to apply a like where clause with wildcards to the SQL query as follows:

Use case: text prompt = John
select hca.account_name
from DIM_CUST_ACCOUNT hca
where upper(hca.account_name) like '%JOHN%'

Use case: text prompt = null
select hca.account_name
from DIM_CUST_ACCOUNT hca

My starting point is:
upper(hca.account_name) like #prompt('param_customer_name','string')#

Any suggestions on how to expand the macro to accommodate the optional filter and wildcards would be very much appreciated.

Many thanks...

Hi,

You could also use the default parameter and the preceding text parameter of the prompt macro to your advantage:

eg

select hca.account_name
from DIM_CUST_ACCOUNT hca +
#prompt('param_customer_name','string','where 1=1','where upper(hca.account_name) like ')#

Cheers!

MF.

[Ricardo Julio "Ricky" Villa]

@Lynn - Almost ;)  It is still expecting a prompt value on execution so not working as optional prompt.

@Mark - SQL does not execute :( Also, no wildcards in solution...?

Many thanks for your help on this... Any other suggestions...?

Cheers

[Ricardo Julio "Ricky" Villa]

Got it...

select hca.account_name
from DIM_CUST_ACCOUNT hca
where
upper ( hca.account_name ) like '%' || #toupper(prompt ( 'param_customer_name', 'string',"'ZZZZZZ'" ))# || '%'
or
#prompt ( 'param_customer_name', 'string',"'ZZZZZZ'" )# = 'ZZZZZZ'

Thanks for your time on this :)

Cheers,

Toby

[Ricardo Julio "Ricky" Villa]

Except it runs like treacle as the query is always applying a like condition in the where clause...

Any ideas how to set it to be 1=1 or something when no value is entered in the prompt?

[Lynn]

Except it runs like treacle as the query is always applying a like condition in the where clause...

Any ideas how to set it to be 1=1 or something when no value is entered in the prompt?

If only there were a way to create a meta data layer that would generate predictable SQL instead of manually coding SQL for each and every report  ::)

Code Select Expand


select hca.account_name
from DIM_CUST_ACCOUNT hca
#toupper ( prompt('param_customer_name','string','where 1=1','where upper(hca.account_name) like' ) )#


Mark's suggestion works for me. The user would need to type in the wildcard characters.

[Ricardo Julio "Ricky" Villa]

Agreed.

This puppy is SQL powered as it's a tactical solution before the datawarehouse has reached a sufficient level of maturity.

Not my decision, I just work here, I'm not paid to think just follow orders ;)

[Ricardo Julio "Ricky" Villa]

Think I might go with 2 separate queries populating 2 lists.

One will have Lynn's where condition, the other not.

The lists to be rendered will be determined by a variable that checks if the customer name parameter is null or not null.