Developer Forum

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

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

and
check ( also, I think one is missing
--
Regards
R.D
--Knowledge gets doubled when shared