Hi Guys,
I have requirement like :select * from table where date=?dateparameter? and (name='SAM%' or name='PAUL%' or name='RAM%').
I am not able to append that "%" at end of parameter value after passing in sql through macro prompt . Please help!
I tried as below:
select * from table where date=#prompt('dateparam','string') #promptmany('namepara','string',' ','and name in (','%',')')#-->did not work
Syntax issues are part of the problem... Let's say dateparam is '2016-07-01' and namepara is 'JOE'. I believe your expression would resolve to:
select * from table where date='2016-07-01' 'JOE' and name in (','%',')'
You don't need to specify string datatypes, that is the default. So my initial pass says something more like this:
select * from table where date=#prompt('dateparam')# and name in #promptmany('namepara')#
Obviously that does not have the wildcards inserted. promptmany really only works with in, and does not support wildcards, so promptmany is not going to solve this all by itself. I found an approach from CognosPaul here on cognoise at http://www.cognoise.com/index.php?topic=22355.0 (http://www.cognoise.com/index.php?topic=22355.0). Seems reasonable, and he's the master of #prompt macros among many other things. So something like...
select * from table where date=#prompt('dateparam')#
and name in #join('%'' and [name] like ''%' + split(';',promptmany('namepara','token','','''%','','%'''))#
I am not somewhere I can test this but that should give you the general idea.