Re: Real puzzler !!??!!

Eric,
Here's goes:

ASSUMPTION:
1) If grade is set to N then school is not excluded...just not selected.

Select *
FROM schools_table

WHERE    decode(kindergarten_flag,'Y','Y','X') = :p_k_flag
OR             decode(elementary_flag, 'Y','Y','X') = :p_e_flag
OR             decode(junior_hig_flag,'Y','Y','X') = :p_j_flag
OR             decode(high_school_flag,'Y','Y','X') = :p_h_flag;

• Or you could just translate the 'N's to someother character (in the database or in your variable) and totally avoid the use of decode...this would make it much faster.

Regards,
Jack Richter

Eric C. Janzen <ejanzen_at_telusplanet.net> wrote in message news:g6Dt4.8875$pn1.312871_at_news0.telusplanet.net...
> I am writing a report for a client. The report has to provide a list of
> schools. The schools table has four flags as follows:
>
> KINDERGARTEN_FLAG
> ELEMENTARY_FLAG
> JUNIOR_HIGH_FLAG
> HIGH_SCHOOL_FLAG
>
> If the user wants to get a list of schools that teach kindergarten, they
> would check a forms checkbox that says "Include kindergarten". There will
> be checkboxes for all the other grades as well. So I then pass the values
> of these checkboxes to a report.
>
> Lets say I have the following rows:
>
> K E J H
> 1.Y N N N
> 2. Y N N Y
> 3. N N N Y
> 4. Y Y N N
> 5. N Y Y N
> 6. N N Y N
> 7. Y N Y N
>
> If they want Kindergarten and High school, they would get rows 1,2,3,4,7
> If they want junior high, they would get 5,6,7
> If they want elementary and junior high, they would get 4,5,6,7
>
> I can easily write a query for each scenario above using OR, but how do I
> write one query to cover all scenarios?
>
> This won't work:
>
> where kindergarten_flag = :p_k_flag
> and elementary_flag = :p_e_flag
> and junior_high_flag = :p_j_flag
> and high_school_flag = :p_h_flag
>
> because in the first scenario, it would only return row 2.
>
> This also won't work:
>
> where kindergarten_flag = :p_k_flag
> or elementary_flag = :p_e_flag
> or junior_high_flag = :p_j_flag
> or high_school_flag = :p_h_flag
>
> because is the first scenario, :p_k_flag and :p_h_flag are 'Y' and the
other
> 2 are 'N', giving
>
> where kindergarten_flag = 'Y'
> or elementary_flag = 'N'
> or junior_high_flag = 'N'
> or high_school_flag = 'Y'
>
> which would return row 6 as well as the ones we want.
>
> I have racked my brain trying to solve this. I am willing to use unions,
> views, inner selects - anything!!!!
>
> Someone must have solved this before.
>
> I eagerly await any answer.
>
> Eric Janzen
>
>
>
>
>
>
Received on Sat Feb 26 2000 - 00:02:54 CET