Conditional ANDS!

Hi all,
I have the following query:
The two and clauses in the subquery need to be conditional.(if possible)
Only included in the select if @.current_hazards = True Or @.areas IS NOT NULL
If @.current_hazards = False AND @.areas IS NULL then both add statements are
to be ignored?
If this is possible i would appreciate any insight on how to achieve this...
Cheers,
Adam
SELECT
oce_rep_areas.oce_rep_area_id, oce_rep_areas.name
FROM
oce_rep_areas
WHERE
oce_rep_areas.oce_rep_area_id IN (
SELECT
parent
FROM
oce_rep_areas
INNER JOIN
oce_rep_items
ON
oce_rep_areas.oce_rep_area_id = oce_rep_items.oce_rep_area_id
WHERE
oce_rep_items.oce_report_id = @.oceid
AND ///to bo conditional : ie add only if @.hazards = True
oce_rep_items.current_hazard = True
AND //to be conditional : ie only include when @.areas is not null
oce_rep_areas.oce_rep_area_id IN(@.areas))SELECT
oce_rep_areas.oce_rep_area_id, oce_rep_areas.name
FROM
oce_rep_areas
WHERE
oce_rep_areas.oce_rep_area_id IN (
SELECT
parent
FROM
oce_rep_areas
INNER JOIN
oce_rep_items
ON
oce_rep_areas.oce_rep_area_id = oce_rep_items.oce_rep_area_id
WHERE
oce_rep_items.oce_report_id = @.oceid
AND (oce_rep_items.current_hazard = True OR -- this eliminates false
@.areas is not null -- this eliminates null and false, not null and true
null is ok for
--you) AND
oce_rep_areas.oce_rep_area_id IN(@.areas))
Regards
R.D
--Knowledge gets doubled when shared
"Adam Knight" wrote:

> Hi all,
> I have the following query:
> The two and clauses in the subquery need to be conditional.(if possible)
> Only included in the select if @.current_hazards = True Or @.areas IS NOT NU
LL
> If @.current_hazards = False AND @.areas IS NULL then both add statements ar
e
> to be ignored?
> If this is possible i would appreciate any insight on how to achieve this.
.
> Cheers,
> Adam
> SELECT
> oce_rep_areas.oce_rep_area_id, oce_rep_areas.name
> FROM
> oce_rep_areas
> WHERE
> oce_rep_areas.oce_rep_area_id IN (
> SELECT
> parent
> FROM
> oce_rep_areas
> INNER JOIN
> oce_rep_items
> ON
> oce_rep_areas.oce_rep_area_id = oce_rep_items.oce_rep_area_id
> WHERE
> oce_rep_items.oce_report_id = @.oceid
> AND ///to bo conditional : ie add only if @.hazards = True
> oce_rep_items.current_hazard = True
> AND //to be conditional : ie only include when @.areas is not null
> oce_rep_areas.oce_rep_area_id IN(@.areas))
>
>|||Thanks RD,
I appreciated your response.
When i run the query as follows:
SELECT
oce_rep_areas.oce_rep_area_id, oce_rep_areas.name
FROM
oce_rep_areas
WHERE
oce_rep_areas.oce_rep_area_id IN (
SELECT
parent
FROM
oce_rep_areas
INNER JOIN
oce_rep_items
ON
oce_rep_areas.oce_rep_area_id = oce_rep_items.oce_rep_area_id
WHERE
oce_rep_items.oce_report_id = @.oceid
AND (oce_rep_items.current_hazard = True OR @.areas IS NOT NULL)
AND oce_rep_areas.oce_rep_area_id IN(@.areas))
I get the following error:
Invalid column name 'True'
Any thoughts?
Adam|||use
1 for true
0 for false
--
Regards
R.D
--Knowledge gets doubled when shared
"Adam Knight" wrote:

> Thanks RD,
> I appreciated your response.
> When i run the query as follows:
> SELECT
> oce_rep_areas.oce_rep_area_id, oce_rep_areas.name
> FROM
> oce_rep_areas
> WHERE
> oce_rep_areas.oce_rep_area_id IN (
> SELECT
> parent
> FROM
> oce_rep_areas
> INNER JOIN
> oce_rep_items
> ON
> oce_rep_areas.oce_rep_area_id = oce_rep_items.oce_rep_area_id
> WHERE
> oce_rep_items.oce_report_id = @.oceid
> AND (oce_rep_items.current_hazard = True OR @.areas IS NOT NULL)
> AND oce_rep_areas.oce_rep_area_id IN(@.areas))
> I get the following error:
> Invalid column name 'True'
> Any thoughts?
> Adam
>
>|||and
check ( also, I think one is missing
--
Regards
R.D
--Knowledge gets doubled when shared
"Adam Knight" wrote:

> Thanks RD,
> I appreciated your response.
> When i run the query as follows:
> SELECT
> oce_rep_areas.oce_rep_area_id, oce_rep_areas.name
> FROM
> oce_rep_areas
> WHERE
> oce_rep_areas.oce_rep_area_id IN (
> SELECT
> parent
> FROM
> oce_rep_areas
> INNER JOIN
> oce_rep_items
> ON
> oce_rep_areas.oce_rep_area_id = oce_rep_items.oce_rep_area_id
> WHERE
> oce_rep_items.oce_report_id = @.oceid
> AND (oce_rep_items.current_hazard = True OR @.areas IS NOT NULL)
> AND oce_rep_areas.oce_rep_area_id IN(@.areas))
> I get the following error:
> Invalid column name 'True'
> Any thoughts?
> Adam
>
>