This discussion is archived
1 Reply Latest reply: May 1, 2013 7:38 AM by fac586 RSS

Apex 4.2: Is there a limit on the length of SQL conditions?

emma-apex Newbie
Currently Being Moderated
Hello

I have a set of page items used as filters for the data behind a chart, via the chart's where clause.

I have repeated the where clause as a condition of the chart region, to make sure the query returns at least 1 row and does not throw an error.

This seems to be working perfectly, except I've noticed the final four fields do not trigger the condition. They DO trigger changes to the chart, i.e. the where condition is working properly. The SQL where clause has been copied and pasted between the two so they are identical.

The only thing I can think is that there is a limit to the length of where clause for conditions (?). Does anyone know if this is the case? My code is below.
select null from aa_wl_activities3
where (activities_key=:P0_KEYPROJECT or :P0_KEYPROJECT is null)
and (activities_parent=:P0_PARENTCHILD or :P0_PARENTCHILD is null)
and (activities_projectflag=:P0_PROJECT or :P0_PROJECT is null)
and (activities_status=:P0_STATUS or :P0_STATUS is null)
and (activities_parent_obj=:P0_OBJECTIVE or :P0_OBJECTIVE is null)
and (activities_driver=:P0_DRIVER or :P0_DRIVER is null)
and (activities_team=:P0_TEAM or :P0_TEAM is null)
and (activities_team in (select team_id from aa_wl_teams2 where team_dept=:P0_DEPT) or :P0_DEPT is null)
and (activities_team in (select team_id from aa_wl_teams2 where team_division=:P0_DIVISION) or :P0_DIVISION is null)
and (activities_policyflag=:P0_POLICY or :P0_POLICY is null)
and (activities_obj_op=:P0_POSEDTO or :P0_POSEDTO is null)
and (activities_int_ext=:P0_POSEDBY or :P0_POSEDBY is null)
and (activities_source=:P0_SOURCE or :P0_SOURCE is null)
and (activities_activity=:P0_ACTIVITY or :P0_ACTIVITY is null)
and (activities_phase=:P0_ORGPROCESSPHASE or :P0_ORGPROCESSPHASE is null)
and (activities_layer=:P0_ORGLAYER or :P0_ORGLAYER is null)
and (activities_stage=:P0_ORGMETHOD or :P0_ORGMETHOD is null)
and (activities_lod=:P0_ORGLINEOFDEFENCE or :P0_ORGLINEOFDEFENCE is null)
Thanks
Emma
  • 1. Re: Apex 4.2: Is there a limit on the length of SQL conditions?
    fac586 Guru
    Currently Being Moderated
    emma-apex wrote:
    Hello

    I have a set of page items used as filters for the data behind a chart, via the chart's where clause.

    I have repeated the where clause as a condition of the chart region, to make sure the query returns at least 1 row and does not throw an error.

    This seems to be working perfectly, except I've noticed the final four fields do not trigger the condition. They DO trigger changes to the chart, i.e. the where condition is working properly. The SQL where clause has been copied and pasted between the two so they are identical.

    The only thing I can think is that there is a limit to the length of where clause for conditions (?). Does anyone know if this is the case? My code is below.
    select null from aa_wl_activities3
    where (activities_key=:P0_KEYPROJECT or :P0_KEYPROJECT is null)
    and (activities_parent=:P0_PARENTCHILD or :P0_PARENTCHILD is null)
    and (activities_projectflag=:P0_PROJECT or :P0_PROJECT is null)
    and (activities_status=:P0_STATUS or :P0_STATUS is null)
    and (activities_parent_obj=:P0_OBJECTIVE or :P0_OBJECTIVE is null)
    and (activities_driver=:P0_DRIVER or :P0_DRIVER is null)
    and (activities_team=:P0_TEAM or :P0_TEAM is null)
    and (activities_team in (select team_id from aa_wl_teams2 where team_dept=:P0_DEPT) or :P0_DEPT is null)
    and (activities_team in (select team_id from aa_wl_teams2 where team_division=:P0_DIVISION) or :P0_DIVISION is null)
    and (activities_policyflag=:P0_POLICY or :P0_POLICY is null)
    and (activities_obj_op=:P0_POSEDTO or :P0_POSEDTO is null)
    and (activities_int_ext=:P0_POSEDBY or :P0_POSEDBY is null)
    and (activities_source=:P0_SOURCE or :P0_SOURCE is null)
    and (activities_activity=:P0_ACTIVITY or :P0_ACTIVITY is null)
    and (activities_phase=:P0_ORGPROCESSPHASE or :P0_ORGPROCESSPHASE is null)
    and (activities_layer=:P0_ORGLAYER or :P0_ORGLAYER is null)
    and (activities_stage=:P0_ORGMETHOD or :P0_ORGMETHOD is null)
    and (activities_lod=:P0_ORGLINEOFDEFENCE or :P0_ORGLINEOFDEFENCE is null)
    Always post code using <tt>\
    ...\
    </tt> tags as described in the FAQ.

    Is this all of the code from the region source?

    Condition expressions are defined as VARCHAR2(4000 BYTE) and this query isn't anywhere close to that (unless your database is using some really exotic character set). This is also a hard, physical limit: the code will obviously either fit or not.

    Do all of the referenced items have the values you are expecting in session state when the condition is evaluated?

    What does a Debug trace show with respect to evaluation of the condition?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points