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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Probably more like
WHERE
Case When {?p_student} = ‘All Students’ then TRUE
ELSE student.studentname in ({?p_student})
END
mlmcc