Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    10

    Unanswered: How to set a condition in the WHERE clause

    I have a report where users pass a school id to find errors for their school. What I want to be able to do is, if no school id is passed or they enter a 0 (zero), the report runs for the entire district. This is effectively omitting the last line in the WHERE clause:

    and (schoolid = 425 or enrollment_schoolid = 425)

    Any ideas would be appreciated.

    ****************************************

    Code:
    select
        enrollment_schoolid,
        schoolid,
        student_number,
        state_studentnumber,
        lastfirst,
        to_char(entrydate, 'yyyy-mm-dd'),
        Case
            when entrydate >=to_date('01 AUG 08') and enroll_status > -1 then 'Invalid Pre-Enroll status enroll_status be -1 Currently '|| to_char(enroll_status)
            when state_studentnumber is null and enroll_status > -1 then 'No SSID should be a new student'
            when entrydate = exitdate then 'No Show change entryand exit to 8/1/2008 - Comments ' || to_char(transfercomment) || ' - ' || to_char(exitcomment)
            when lastfirst like '%.%' then 'Period in Name'
            when ethnicity is null then 'No ethnicity Code'
            Else 'Other Error'
        End as Error 
    
    from
        students
    
    where
        entrydate > to_date('01 AUG 08')  -- Don't check previous years
        and (
        entrydate = exitdate -- No Time in School
        or lastfirst like '%.%' -- Period in Name
        or (state_studentnumber is null and enroll_status > -1) -- SSID Check
        or ethnicity is null -- No Ethnicity
            )
        and (schoolid = 425 or enrollment_schoolid = 425)

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    One way would be to use CASE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    select
        enrollment_schoolid,
        schoolid,
        student_number,
        state_studentnumber,
        lastfirst,
        to_char(entrydate, 'yyyy-mm-dd'),
        Case
            when entrydate >=to_date('01 AUG 08') and enroll_status > -1 then 'Invalid Pre-Enroll status enroll_status be -1 Currently '|| to_char(enroll_status)
            when state_studentnumber is null and enroll_status > -1 then 'No SSID should be a new student'
            when entrydate = exitdate then 'No Show change entryand exit to 8/1/2008 - Comments ' || to_char(transfercomment) || ' - ' || to_char(exitcomment)
            when lastfirst like '%.%' then 'Period in Name'
            when ethnicity is null then 'No ethnicity Code'
            Else 'Other Error'
        End as Error 
    
    from
        students
    
    where
        entrydate > to_date('01 AUG 08')  -- Don't check previous years
        and (
        entrydate = exitdate -- No Time in School
        or lastfirst like '%.%' -- Period in Name
        or (state_studentnumber is null and enroll_status > -1) -- SSID Check
        or ethnicity is null -- No Ethnicity
            )
        and (nvl(schoolid,0) = 0 or schoolid = 425 or enrollment_schoolid = 425)
    also always use a mask with to_date
    Last edited by beilstwh; 04-30-09 at 16:12.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •