Why not use a view for the joined data and pass your WHERE and ORDER BY
clauses to it?

Helen


At 09:41 PM 23-10-00 -0400, you wrote:

>Hi:
>
> Let me go into a little more detail here:
>
> NT4 sp6 CR7 MR1 Interbase 6 EasySoft ODBC Driver
>
> Tables are linked from Tenants.ID to Members.ID
>
> @DateEntry = CurrentDate - 1827. <---
> @CutDateIn = CurrentDate - 1095. <--- These are set by user
> @CutDateUp = CurrentDate - 365. <---
> @SelectedID = A selected Id. <---
>
>---------------------------------------------------------------------------
>------------
>This is the Crystal record selection formula:
>
>(({TENANTS.ID} = {@SelectedID} AND {TENANTS.MGRID} = -1) OR
>({TENANTS.MGRID} = {@SelectedID})) AND
>({TENANTS.ARCHIVE} = 'NO') AND
>(({TENANTS.DATE_ENTRY} > {@DateEntry}) OR ({TENANTS.DATE_UP} >
>{@DateEntry})) AND
>(IsNull({TENANTS.SSN_TEN}) OR ({TENANTS.ADRESS_TEN} < 1 AND
>{TENANTS.POBOX_TEN} <1) OR
> (IsNull({TENANTS.DATE_IN})) OR (IsNull({TENANTS.DATE_OUT}) AND
>({TENANTS.DATE_IN} < {@CutDateIn}) AND
> ({TENANTS.DATE_UP} < {@CutDateUp})))
>---------------------------------------------------------------------------
>---------
>This is SQL from Crystal | Database | Show SQL Query
>
>SELECT
> TENANTS."ARCHIVE", TENANTS."REF_REV", TENANTS."DATE_ENTRY",
>TENANTS."DATE_UP", TENANTS."ID", TENANTS."MGRID",
>TENANTS."NAME2_TEN", TENANTS."MI_TEN", TENANTS."NAME1_TEN",
>TENANTS."SSN_TEN", TENANTS."BIRTH_TEN", TENANTS."EMPLOY_TEN",
>TENANTS."POBOX_TEN", TENANTS."ADRESS_TEN", TENANTS."SUBADD_TEN",
>TENANTS."STNAME_TEN", TENANTS."APT_TEN", TENANTS."CITY_TEN",
>TENANTS."STATE_TEN", TENANTS."ZIP_TEN", TENANTS."NAME2_COT",
>TENANTS."MI_COT", TENANTS."NAME1_COT", TENANTS."SSN_COT",
>TENANTS."BIRTH_COT", TENANTS."EMPLOY_COT", TENANTS."DATE_IN",
>TENANTS."DATE_OUT", TENANTS."COURT_INIT", TENANTS."COURT_ACT",
>TENANTS."ATTORNEY", TENANTS."COURT_DISP", TENANTS."COURT_REST",
>TENANTS."CORT_SORCE", TENANTS."PAY_RENT", TENANTS."CLEAN",
>TENANTS."PRBLMS", TENANTS."COMMENTS", TENANTS."URBANIZATION",
> MEMBERS."ID", MEMBERS."NAMELAST", MEMBERS."MI",
>MEMBERS."NAMEFIRST", MEMBERS."DBA", MEMBERS."PHONE",
> MEMBERS1."NAMELAST", MEMBERS1."MI", MEMBERS1."NAMEFIRST",
>MEMBERS1."DBA", MEMBERS1."PHONE"
>FROM
> { oj ("TENANTS" TENANTS LEFT OUTER JOIN "MEMBERS" MEMBERS1 ON
> TENANTS."MGRID" = MEMBERS1."ID")
> LEFT OUTER JOIN "MEMBERS" MEMBERS ON
> TENANTS."ID" = MEMBERS."ID"}
>WHERE
> TENANTS."ARCHIVE" = 'NO'
>ORDER BY
> TENANTS."ID" ASC,
> TENANTS."NAME2_TEN" ASC,
> TENANTS."NAME1_TEN" ASC
>---------------------------------------------------------------------------
>------------------------
>
> The problem is that Crystal returns all the records without
>consideration of the parameters I pass (conclusion based on SQL)it
>but instead applies a filter to the returned dataset based on
>those parameters.
>
> I figured if I used a stored proc to return just the actual
>dataset I wanted and eliminated the filtering, it would be much
>faster.
>
> Or I can manually edit the SQL generated by Crystal to include
>the parameters and then supply them through the Crystal vcl
>component property SQL | Parameters.
>
> I am now inclined to try the latter since they would probably
>be comparable in speed and to use an SP would require re-creating
>the report since it is currently defined based on fields in
>dataset and not variables returned from SP.
>
> Any thoughts appreciated.
>
> Best
>regards
>
>Community email addresses:
> Post message: IBDI@onelist.com
> Subscribe: IBDI-subscribe@onelist.com
> Unsubscribe: IBDI-unsubscribe@onelist.com
> List owner: IBDI-owner@onelist.com
>
>Shortcut URL to this page:
> http://www.onelist.com/community/IBDI

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
___________________________________________________