Re: Real puzzler !!??!!
Date: Sat, 26 Feb 2000 23:56:28 GMT
Message-ID: <899p7b$7ac$1_at_nnrp1.deja.com>
In article <g6Dt4.8875$pn1.312871_at_news0.telusplanet.net>, "Eric C. Janzen" <ejanzen_at_telusplanet.net> wrote:
Hi Eric
Try
...
where (:p_k_flag = 'Y' and kindergarten_flag = 'Y')
or (:p_e_flag = 'Y' and elementary_flag = 'Y') or (:p_j_flag = 'Y' and junior_high_flag = 'Y') or (:p_h_flag = 'Y' and high_school_flag = 'Y')/* matches if one flag of a scool out of the wanted ones
is set, unwanted flags are ignored */ or (:p_k_flag = 'N'
and :p_e_flag = 'N'
and :p_j_flag = 'N'
and :p_h_flag = 'N')
/* accounts for the case the user didn't make any selection. Omit if you
don't want to select any scool in this case */
HTH Pascal Glauser
and elementary_flag = :p_e_flag
and junior_high_flag = :p_j_flag
and high_school_flag = :p_h_flag
> 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
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sun Feb 27 2000 - 00:56:28 CET