Variable Parameter using SQL wildcard

[tmccoy1]

My report is based on an SQL command that sets variables based on a CASE statement based on a parameter select. It then uses the variables to populate part of the where clause to select records. Example:

Set @var =
CASE
WHEN {?param} = 1
THEN '[^SUB]%'

Select…..
From…
Where tbl.field LIKE @var

This returns results in SQL just fine, but will return no results in Crystal. Any tips on how I might get this to work?

 

[PeterDimitrov]

you might try this:
SELECT ... FROM .... WHERE {?param} <> 1 OR tbl.field LIKE '[^SUB]%'

if you want to concatenate the varchars the check the sql send to the database
if {?param} <> 1 Then the query which will be send to the database will look like this
Select….. From… Where tbl.field LIKE //nothing after LIKE
if {?param} = 1 then
Select….. From… Where tbl.field LIKE [^SUB]% //no ' for [^SUB]%

both will return errors

Try to concatenate the varchars and use exec to execute the varchar command. I am not sure about the exact syntax (the number of ' might be different)

declare @SQL varchar(max)
set @SQL = 'select ... FROM ....' + CASE WHEN {?param} = 1 THEN 'WHERE tbl.field LIKE '''[^SUB]%'''
exec @SQL

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.