Link to home
Start Free TrialLog in
Avatar of tonMachine100
tonMachine100

asked on

Multi value parameter in SQL command - CR2013

Im using CR2013 to query an oracle 11 database.

I want to use a multi value parameter in my sql query (rather than using the parameter in the select expert).
 So far I have:

1. created a parameter in the 'Command Parameter' dialog box called p_student
2. checked the 'Allows multiple values' checkbox in the 'Command Parameter' dialog box
3. added the parameter into the query using syntax:
       where student.studentname in ({?p_student})

4. added the following values into the p_student parameter:
      George
      Paul
      Ringo
      John

This seems to work fine, but now I want to add an additional parameter option called 'All students':

All students
      George
      Paul
      Ringo
      John

Which when selected would pass the following sql into the query:

      Where Student.studentname like ‘%’

So would return all students

The logic would be

Case when
{?p_student} = ‘All Students’ then
where Student.studentname like ‘%’
else
where student.studentname in ({?p_student})
end

Any help with the syntax is appreciated.
Avatar of Mike McCracken
Mike McCracken

Rather than that just use TRUE

Probably more like

WHERE
   Case When {?p_student} = ‘All Students’ then TRUE
   ELSE  student.studentname in ({?p_student})
   END

mlmcc
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tonMachine100

ASKER

that works- thanks
You're welcome.

 FWIW, mlmcc probably deserved some of the points, since he responded first, and I think his basic syntax would work.  You could ask to have the question re-opened, and then split the points between us.

 James