At 01:02 AM 24-10-00 -0400, you wrote:

>Hi:
>
> Thanks for input.
>
> > Why not use a view for the joined data and pass your WHERE and ORDER BY
> > clauses to it?
>
> To be perfectly frank, it never entered my mind!!!
>
>I have it working now with the SQL. I had to build the whole SQL
>statement at runtime with the parameters. ODBC trace below. Works
>fine but no faster (I think) that what was going on before.
>Would there be any advantage in speed or other areas with a view?

You can but try...how long would it take to paste your query into a CREATE
VIEW statement and test it out? Do it and time it !

H.

> Best
>regards
>ODBC Trace
>
>LTDMSA 9e-110 ENTER SQLExecDirect
> HSTMT 022A1B08
> UCHAR * 0x01A403A8 [ -3] "SELECT
>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."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."ID" = 1 AND
>TENANTS."MGRID" = -1) OR (TENANTS."MGRID" = 1 )) AND
>(TENANTS."ARCHIVE" = 'NO') AND ((TENANTS."DATE_ENTRY" >
>'10/24/1995') OR (TENANTS."DATE_UP" > '10/24/1995')) AND
>((SUBSTR(TENANTS."SSN_TEN",1,2) IS NULL ) OR (TENANTS."ADRESS_TEN"
>< 1 AND TENANTS."POBOX_TEN" <1) OR (TENANTS."DATE_IN" IS NULL)
>OR ((TENANTS."DATE_OUT" IS NULL) AND (TENANTS."DATE_IN" <
>'10/25/1997') AND (TENANTS."DATE_UP" < '10/25/1999'))) ORDER
>BY TENANTS."ID" ASC, TENANTS."NAME2_TEN" ASC,
>TENANTS."NAME1_TEN" ASC \ 0"
>
>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
___________________________________________________