Commands and Multi-value Prompts

[hilfy]

I'm having a problem with a report that I'm working on. The report uses a number of commands that have parameters. The 3 parameters that are causing me problems are string parameters that can have multiple values, with a default value of 'All'.

In the commands, the parameters are used like this:

   and ('All' in ({?param_dept_m})  or team.GS_ASSOC_DEPT in ({?param_dept_m}))
   and ('All' in ({?param_funct_group_m}) or exists (
     Select 'X'
     from GS_FUNC_GROUP_FLAT_V grp
     where team.GS_FUNCT_GRP = grp.CHILD_CODE
       and grp.PARENT_CODE in ({?param_funct_group_m})) )
   and ('All' in ({?param_funct_team_m}) or team.CODE in ({?param_funct_team_m}))

When I leave the default of All or enter a single value in each parameter, everything works fine. When I enter multiple values I get the following error:

Failed to retrieve data from the database. Details: ORA-00907: missing right parenthesis [Database Vendor Code: 907]

Oracle version is 11g. Crystal version is CR 2008 SP3 with no fix packs.

For all of the work I've done in Crystal over the years, this is the first time that I've worked with multi-value prompts in commands and I'm stumped.

Thanks!

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Turkbear]

Hi,
A little hard to follow ( and Oracle has pretty specific rules about IN statements - be sure about the separator and the quotes.
Assuming a correct string of values is passed, maybe a missing ) is causing the issue , so
try

and
 (
'All' in ({?param_dept_m})
  or 
team.GS_ASSOC_DEPT in ({?param_dept_m})

)
   and
 (
'All' in ({?param_funct_group_m})
 or
 exists 
(
     Select 'X'
     from GS_FUNC_GROUP_FLAT_V grp
     where team.GS_FUNCT_GRP = grp.CHILD_CODE
       and grp.PARENT_CODE in ({?param_funct_group_m}))
 )
[COLOR=red] add a ) to close this AND set here and test[/color]  
 and 
(
'All' in ({?param_funct_team_m})
 or
 team.CODE in ({?param_funct_team_m})
)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[hilfy]

Actually, all I had to do was delete the parentheses around the parameters. So the final looks like this:

and ('All' in {?param_dept_m}  or team.GS_ASSOC_DEPT in {?param_dept_m})   
and ('All' in {?param_funct_group_m} 
  or exists (
    Select 'X'
    from GS_FUNC_GROUP_FLAT_V grp
    where team.GS_FUNCT_GRP = grp.CHILD_CODE
      and grp.PARENT_CODE in {?param_funct_group_m}))   
and ('All' in {?param_funct_team_m} or team.CODE in {?param_funct_team_m})

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.