Select Expert

[vishalsethia]

I am using CR bundles with VS 2005 and using Oracle as the backend.

I have a situation where my record selection depends on what the user selects.Earlier I had to select if the user wanted all records from a set of departments or all departments, and so I retrieved the records using the following formula

if {?parDepartmentName} = "ALL" then
{PROBATION_NAME_NEW.CAMPUS_CODE} = {?parCampusCode}
and
{PROBATION_NAME_NEW.ACADEMIC_STATUS} = {?parAcademic_Status} and
{PROBATION_NAME_NEW.CURRENT_STANDING} = {?parClassStanding}

else

{PROBATION_NAME_NEW.CAMPUS_CODE} = {?parCampusCode} and
{PROBATION_NAME_NEW.ACADEMIC_STATUS} = {?parAcademic_Status} and
{PROBATION_NAME_NEW.CURRENT_STANDING} = {?parClassStanding} and
{PERSONAL_INFO.DEPARTMENT_CODE} in {?parDepartmentName}

This worked well, but now I have one more requirement, if one of the current standing is U01 (First Yr Freshman), then add 2 more constraints to the above select expert, liek
ISNULL{PROBATION_NAME_NEW.ADMIT_TRSF_FLAG} and
{PROBATION_NAME_NEW.FIRSTYEAR} = 2006

Assumin the user selects all class ranks, U01,U02,U03,U04 then a simple sql query would be like

select * from probation_name_new where
(class_rank = 'U01' and ADMIT_TRSF_FLAG is NULL and
FIRSTYEAR = 2006) or
class_rank in ('U02','U03','U04)


I not sure how to proceed for this requirement. I hope I am making sense .....

Any help will be greatly appreciated..

--Vishal

 

[vishalsethia]

Wanted to bump the thread ??? Sorry abt that

Any help will ge greatly appreciated.

Thanks

--Vishal

 

[synapsevampire]

Try:

if {?parDepartmentName} = "ALL" then
{PROBATION_NAME_NEW.CAMPUS_CODE} = {?parCampusCode}
and
{PROBATION_NAME_NEW.ACADEMIC_STATUS} = {parAcademic_Status}
and
(
if "U01" in {PROBATION_NAME_NEW.CURRENT_STANDING}
then
ISNULL{PROBATION_NAME_NEW.ADMIT_TRSF_FLAG}
and
{PROBATION_NAME_NEW.FIRSTYEAR} = 2006
)
and
{PROBATION_NAME_NEW.CURRENT_STANDING} = {?parClassStanding}
else
{PROBATION_NAME_NEW.CAMPUS_CODE} = {?parCampusCode} and
{PROBATION_NAME_NEW.ACADEMIC_STATUS} = {?parAcademic_Status} and
{PROBATION_NAME_NEW.CURRENT_STANDING} = {?parClassStanding} and
{PERSONAL_INFO.DEPARTMENT_CODE} in {?parDepartmentName}
and
(
if "U01" in {PROBATION_NAME_NEW.CURRENT_STANDING}
then
ISNULL{PROBATION_NAME_NEW.ADMIT_TRSF_FLAG}
and
{PROBATION_NAME_NEW.FIRSTYEAR} = 2006
)

Should get you close.

-k

 

[vishalsethia]

Thanks synapsevampire,

I used some of your ideas to get my report working. Thanks again

--Vishal