Lexical Variables & Performance in Reports

From: Ed Jennings <jenningse_at_mindspring.com>
Date: Sat, 05 Sep 1998 12:47:03 -0400
Message-ID: <35F16B06.DC506776_at_mindspring.com>

I have a Report in Reports 3.0. The where clause only contains the appropriate JOIN condittions. When I call this report from FORMS, I want to pass a supplement to the where clause that will restrict the result set. I am doing this in the form of a parameter list and a lexical variable in the report. Trouble is, it appears that the lexical variable must be the entire WHERE clause, or none at all. For example:

       select col1, col2, col3
        from table1, table2
        where table1.col1 = table2.col1;
If I modify it to the following:
       select col1, col2, col3
        from table1, table2
        where &where_supplement AND table1.col1 = table2.col1;

Then I get an error when saving the query. However, if I modify it this way:

       select col1, col2, col3
        from table1, table2
        where &where_supplement;

Then it is accepted. The end result is a run away query performing a cartesian join, since the join condition isn't specified. I was able to duplicate the entire where clause in FORMS and pass the entire clause to REPORTS, but there is a very long delay (1 min) before the first page of the report is generated. There is no delay at all when I don't pass the where clause. I assume that there is some significant overhead when passing in a lengthy where clause. I'm hoping for significantly improved performance if/when I get the mechanics of the supplemental where clause worked out.
With that said, can anyone provide insight into this problem. I find it hard to believe that a lexical variable has to be all inclusive. In PL/SQL and SQL, a lexical variable can be anywhere and be all or any part of any statement. Certainly this must be true in REPORTS???

TIA
Ed Jennings

--
~~~~~~~~~~~~~~~~~~~~~~~~~
jenningse_at_mindspring.com

Received on Sat Sep 05 1998 - 18:47:03 CEST