Help with join syntax

[morechocolate]

The following is a view that is used in Crystal reports.

I original only had one union. It contained the first and third set of SQL statements. In each of those I had property.prop_code *= extprop.prop_code. However, because of our ODBC driver Crystal defaults to ANSI standard. Since the outer join uses SQL standard an error was returned in the report.

I am aware that I can just use ANSI standard in the view, but I do not want to do that because most people here, including myself, are more familiar with the SQL Standard.

Keeping all that I have said above in mind, is there a different way I could have handled the outer join without adding the additional union clauses?

Thanks

create view nyl_property_appr
(LOAN,
PROP_CODE,
PROPERTY,
PROP_TYPE,
LOC_TYPE,
REGION,
ADDRESS1,
ADDRESS2,
CITY,
STATE,
ZIPCODE,
COUNTRY,
COUNTY,
TOTAL_ACRES,
PR_PRTY_MGR,
SYSTEM_TYPE,
METRO_AREA,
ACLI_REGION,
PR_PRTY_ZONE,
APPR_PROP_CODE,
APPRAISAL_DATE,
APPRAISER1,
APPR_FINAL_LAND,
APPR_FINAL_BLDG,
APPR_FINAL_OTHR,
APPR_FINAL_VAL,
NYL_PART,
REPLACEMENT_VALUE,
AP_INTR_EST_VAL,
AP_APSL_FEE,
NEXT_APPR_DATE,
APPR_REASON,
APPRAISAL_TYPE,
AP_INTR_VAL_DT,
AP_INTR_APSR )
as SELECT LOANCOLL.LOAN,
PROPERTY.PROP_CODE,
PROPERTY.PROPERTY,
PROPERTY.PROP_TYPE,
EXTPROP.LOC_TYPE,
PROPERTY.REGION,
PROPERTY.ADDRESS1,
PROPERTY.ADDRESS2,
PROPERTY.CITY,
PROPERTY.STATE,
PROPERTY.ZIPCODE,
PROPERTY.COUNTRY,
PROPERTY.COUNTY,
PROPERTY.TOTAL_ACRES,
NULL PROP_MANAGER,
PROPERTY.SYSTEM_TYPE,
PROPERTY.METRO_AREA,
PROPERTY.ACLI_REGION,
NULL PROP_ZONE,
PROPAPPR.PROP_CODE,
PROPAPPR.APPRAISAL_DATE,
PROPAPPR.APPRAISER1,
PROPAPPR.APPR_FINAL_LAND,
PROPAPPR.APPR_FINAL_BLDG,
PROPAPPR.APPR_FINAL_OTHR,
PROPAPPR.APPR_FINAL_VAL,
EXTGENERAL.NYL_PARTICIPATI,
PROPAPPR.REPLACEMENT_VALUE,
NULL APPR_ESTIMATED_VAL,
NULL APPR_FEE,
EXTAPPR.NEXT_APPR_DATE,
EXTAPPR.APPR_REASON,
PROPAPPR.APPRAISAL_TYPE,
NULL AP_INTR_VAL_DT,
NULL AP_INTR_APSR
FROM PROPAPPR PROPAPPR,PROPERTY PROPERTY, EXTPROP EXTPROP, EXTAPPRAISAL EXTAPPR, LOANCOLL LOANCOLL, LOANMAST LOANMAST, EXTGENERAL EXTGENERAL
WHERE PROPAPPR.APPRAISAL_DATE = (select MAX(APPRAISAL_DATE) from PROPAPPR
where PROP_CODE = PROPERTY.PROP_CODE) and
PROPAPPR.PROP_CODE = PROPERTY.PROP_CODE AND
PROPERTY.PROP_CODE = EXTPROP.PROP_CODE AND
PROPERTY.PROP_CODE = EXTAPPR.PROP_CODE AND
PROPAPPR.APPRAISAL_DATE = EXTAPPR.APPRAISAL_DATE AND
PROPERTY.PROP_CODE = LOANCOLL.CODE AND
LOANMAST.LOAN = LOANCOLL.LOAN AND
LOANMAST.LOAN = EXTGENERAL.LOAN

union

SELECT LOANCOLL.LOAN,
PROPERTY.PROP_CODE,
PROPERTY.PROPERTY,
PROPERTY.PROP_TYPE,
NULL LOC_TYPE,
PROPERTY.REGION,
PROPERTY.ADDRESS1,
PROPERTY.ADDRESS2,
PROPERTY.CITY,
PROPERTY.STATE,
PROPERTY.ZIPCODE,
PROPERTY.COUNTRY,
PROPERTY.COUNTY,
PROPERTY.TOTAL_ACRES,
NULL PROP_MANAGER,
PROPERTY.SYSTEM_TYPE,
PROPERTY.METRO_AREA,
PROPERTY.ACLI_REGION,
NULL PROP_ZONE,
PROPAPPR.PROP_CODE,
PROPAPPR.APPRAISAL_DATE,
PROPAPPR.APPRAISER1,
PROPAPPR.APPR_FINAL_LAND,
PROPAPPR.APPR_FINAL_BLDG,
PROPAPPR.APPR_FINAL_OTHR,
PROPAPPR.APPR_FINAL_VAL,
EXTGENERAL.NYL_PARTICIPATI,
PROPAPPR.REPLACEMENT_VALUE,
NULL APPR_ESTIMATED_VAL,
NULL APPR_FEE,
EXTAPPR.NEXT_APPR_DATE,
EXTAPPR.APPR_REASON,
PROPAPPR.APPRAISAL_TYPE,
NULL AP_INTR_VAL_DT,
NULL AP_INTR_APSR
FROM PROPAPPR PROPAPPR,PROPERTY PROPERTY, EXTPROP EXTPROP, EXTAPPRAISAL EXTAPPR, LOANCOLL LOANCOLL, LOANMAST LOANMAST, EXTGENERAL EXTGENERAL
WHERE PROPAPPR.APPRAISAL_DATE = (select MAX(APPRAISAL_DATE) from PROPAPPR
where PROP_CODE = PROPERTY.PROP_CODE) and
PROPAPPR.PROP_CODE = PROPERTY.PROP_CODE AND
PROPERTY.PROP_CODE not in (select EXTPROP.PROP_CODE
from EXTPROP EXTPROP) AND
PROPERTY.PROP_CODE = EXTAPPR.PROP_CODE AND
PROPAPPR.APPRAISAL_DATE = EXTAPPR.APPRAISAL_DATE AND
PROPERTY.PROP_CODE = LOANCOLL.CODE AND
LOANMAST.LOAN = LOANCOLL.LOAN AND
LOANMAST.LOAN = EXTGENERAL.LOAN

union

SELECT LOANCOLL.LOAN,
PROPERTY.PROP_CODE,
PROPERTY.PROPERTY,
PROPERTY.PROP_TYPE,
EXTPROP.LOC_TYPE,
PROPERTY.REGION,
PROPERTY.ADDRESS1,
PROPERTY.ADDRESS2,
PROPERTY.CITY,
PROPERTY.STATE,
PROPERTY.ZIPCODE,
PROPERTY.COUNTRY,
PROPERTY.COUNTY,
PROPERTY.TOTAL_ACRES,
NULL PROP_MANAGER,
PROPERTY.SYSTEM_TYPE,
PROPERTY.METRO_AREA,
PROPERTY.ACLI_REGION,
NULL PROP_ZONE,
null PROP_CODE,
null APPRAISAL_DATE,
null APPRAISER1,
null APPR_FINAL_LAND,
null APPR_FINAL_BLDG,
null APPR_FINAL_OTHR,
null APPR_FINAL_VAL,
EXTGENERAL.NYL_PARTICIPATI,
null REPLACEMENT_VALUE,
null APPR_ESTIMATED_VAL,
null APPR_FEE,
null NEXT_APPR_DATE,
null APPR_REASON,
null APPRAISAL_TYPE,
null AP_INTR_VAL_DT,
null AP_INTR_APSR
FROM PROPERTY PROPERTY, EXTPROP EXTPROP, LOANCOLL LOANCOLL, LOANMAST LOANMAST, EXTGENERAL EXTGENERAL
WHERE PROPERTY.PROP_CODE not in
(select PROP_CODE FROM PROPAPPR) and
PROPERTY.PROP_CODE = EXTPROP.PROP_CODE and
PROPERTY.PROP_CODE = LOANCOLL.CODE and
LOANMAST.LOAN = LOANCOLL.LOAN and
LOANMAST.LOAN = EXTGENERAL.LOAN

union

SELECT LOANCOLL.LOAN,
PROPERTY.PROP_CODE,
PROPERTY.PROPERTY,
PROPERTY.PROP_TYPE,
NULL LOC_TYPE,
PROPERTY.REGION,
PROPERTY.ADDRESS1,
PROPERTY.ADDRESS2,
PROPERTY.CITY,
PROPERTY.STATE,
PROPERTY.ZIPCODE,
PROPERTY.COUNTRY,
PROPERTY.COUNTY,
PROPERTY.TOTAL_ACRES,
NULL PROP_MANAGER,
PROPERTY.SYSTEM_TYPE,
PROPERTY.METRO_AREA,
PROPERTY.ACLI_REGION,
NULL PROP_ZONE,
null PROP_CODE,
null APPRAISAL_DATE,
null APPRAISER1,
null APPR_FINAL_LAND,
null APPR_FINAL_BLDG,
null APPR_FINAL_OTHR,
null APPR_FINAL_VAL,
EXTGENERAL.NYL_PARTICIPATI,
null REPLACEMENT_VALUE,
null APPR_ESTIMATED_VAL,
null APPR_FEE,
null NEXT_APPR_DATE,
null APPR_REASON,
null APPRAISAL_TYPE,
null AP_INTR_VAL_DT,
null AP_INTR_APSR
FROM PROPERTY PROPERTY, EXTPROP EXTPROP, LOANCOLL LOANCOLL, LOANMAST LOANMAST, EXTGENERAL EXTGENERAL
WHERE PROPERTY.PROP_CODE not in
(select PROP_CODE FROM PROPAPPR) and
PROPERTY.PROP_CODE not in (select EXTPROP.PROP_CODE
from EXTPROP EXTPROP) and
PROPERTY.PROP_CODE = LOANCOLL.CODE and
LOANMAST.LOAN = LOANCOLL.LOAN and
LOANMAST.LOAN = EXTGENERAL.LOAN

 

[synapsevampire]

What database are you using?

And you might as well just bite the bullet and move towards the ANSI standard as everyone else is...

Consider posting database questions in the proper database forum, many Crystal developers are unfamiliar with SQL.

Generally Unions are frowned upon because they are slow, and you can usually do the same thing by using an OR, or a nested query or a case or a decode, etc., and get better performance.

Something like:
Where
(PROPAPPR.APPRAISAL_DATE = (select MAX(APPRAISAL_DATE) from PROPAPPR
where PROP_CODE = PROPERTY.PROP_CODE) and
PROPAPPR.PROP_CODE = PROPERTY.PROP_CODE AND
PROPERTY.PROP_CODE not in (select EXTPROP.PROP_CODE
from EXTPROP EXTPROP) AND
PROPERTY.PROP_CODE = EXTAPPR.PROP_CODE AND
PROPAPPR.APPRAISAL_DATE = EXTAPPR.APPRAISAL_DATE AND
PROPERTY.PROP_CODE = LOANCOLL.CODE AND
LOANMAST.LOAN = LOANCOLL.LOAN AND
LOANMAST.LOAN = EXTGENERAL.LOAN
)
or
(PROPAPPR.APPRAISAL_DATE = (select MAX(APPRAISAL_DATE) from PROPAPPR
where PROP_CODE = PROPERTY.PROP_CODE) and
PROPAPPR.PROP_CODE = PROPERTY.PROP_CODE AND
PROPERTY.PROP_CODE not in (select EXTPROP.PROP_CODE
from EXTPROP EXTPROP) AND
PROPERTY.PROP_CODE = EXTAPPR.PROP_CODE AND
PROPAPPR.APPRAISAL_DATE = EXTAPPR.APPRAISAL_DATE AND
PROPERTY.PROP_CODE = LOANCOLL.CODE AND
LOANMAST.LOAN = LOANCOLL.LOAN AND
LOANMAST.LOAN = EXTGENERAL.LOAN
)

To be fair, it was more than I'd bargained for to carefully analyze, but hopefullky this will point you towards
a more optimized version.

-k