Issues with Displaying data with a UNION query

[piovesan]

Hi;
I have a report that uses a union query. The report displays a list of properties and indicates the vendors code. The properties are one table (Table1), the vendors are another one to many related table (Client). This works fine. However, if the property was consolidated or subdivided, the "new" parcel with a new unique AssetID goes into Table 1 and so that new AssetID does not directly relate to anything in the Client table. The "old" parcel goes into another table (Table2) and there is a relationship back to Table1 through the AssetIDs. The ModassetID in this Table2 related to the Client AssetID table. but I cannot seem to get the Vendor code to display in the report when the client goes through Table2. My command code is here below.... any help to get the Client (Vendor) code to display for these properties in Table2 would be appreciated!!
Thanks!

SELECT DISTINCT
'Parcel' "Type",
"LND_ACQUISITION_DISPOSAL"."ACQDISTYPE", "LND_ACQUISITION_DISPOSAL"."ACQDISDATE", "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET", "LND_ACQUISITION_DISPOSAL"."ACQDISP_ID",
"LND_HRM_PARCEL"."ASSET_TYPE" "TABLE1.assettype",
"LND_ACQUISITION_DISPOSAL"."TRANS_TYPE",
"LND_CIVIC_ADDRESS"."CIV_ID",
"LND_HRM_PARCEL"."PID" "TABLE1.PID",
"LND_PR_CLIENT"."CLIENTCODE" "CLIENT.NonModClientCode",
"LND_PR_CLIENT"."CLIENTNAME",
"LND_HRM_PARCEL"."ASSET_ID" "TABLE1.assetid",
"LND_CIVIC_ADDRESS"."CIV_NUM",
"LND_HRM_PARCEL"."MOD_TYPE" "TABLE1.modtype",
"LND_ASSET_XREF"."MODASSETID",
"LND_ASSET_XREF"."HRMASSETID",
"LND_PARCEL_POLYGON"."PID"

FROM ((((("SDEADM"."LND_ACQUISITION_DISPOSAL" "LND_ACQUISITION_DISPOSAL" LEFT OUTER JOIN "SDEADM"."LND_HRM_PARCEL" "LND_HRM_PARCEL" ON "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET"="LND_HRM_PARCEL"."ASSET_ID")

LEFT OUTER JOIN "SDEADM"."LND_CIVIC_ADDRESS" "LND_CIVIC_ADDRESS" ON "LND_HRM_PARCEL"."PID"="LND_CIVIC_ADDRESS"."PID")

LEFT OUTER JOIN "SDEADM"."LND_ASSET_XREF" "LND_ASSET_XREF" ON "LND_HRM_PARCEL"."ASSET_ID"="LND_ASSET_XREF"."HRMASSETID")

LEFT OUTER JOIN "SDEADM"."LND_PR_CLIENT" "LND_PR_CLIENT" ON ("LND_HRM_PARCEL"."ASSET_ID"="LND_PR_CLIENT"."ASSET_ID") OR ("LND_HRM_PARCEL"."PID"="LND_PR_CLIENT"."PID"))

LEFT OUTER JOIN "SDEADM"."LND_PR_REALESTATE_COMMENTS" "LND_PR_REALESTATE_COMMENTS" ON ("LND_HRM_PARCEL"."ASSET_ID"="LND_PR_REALESTATE_COMMENTS"."ASSET_ID") OR ("LND_HRM_PARCEL"."PID"="LND_PR_REALESTATE_COMMENTS"."PID"))

LEFT OUTER JOIN "SDEADM"."LND_PARCEL_POLYGON" "LND_PARCEL_POLYGON" ON "LND_HRM_PARCEL"."PID"="LND_PARCEL_POLYGON"."PID"

WHERE (("LND_ACQUISITION_DISPOSAL"."TRANS_TYPE" = 1)
AND NOT ("LND_HRM_PARCEL"."MAIN_CLASS"='BUSINESS PARK')
AND ("LND_ACQUISITION_DISPOSAL"."HRM_DEPT"='Real Estate'))

UNION

SELECT DISTINCT
'Parcel_Modified' "Type",
"LND_ACQUISITION_DISPOSAL"."ACQDISTYPE", "LND_ACQUISITION_DISPOSAL"."ACQDISDATE", "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET", "LND_ACQUISITION_DISPOSAL"."ACQDISP_ID",
"LND_HRM_PARCEL_MODIFIED"."ASSET_TYPE" "TABLE2.Assettype",
"LND_ACQUISITION_DISPOSAL"."TRANS_TYPE",
"LND_CIVIC_ADDRESS"."CIV_ID",
"LND_HRM_PARCEL_MODIFIED"."PID" "TABLE2.PID",
"LND_PR_CLIENT"."CLIENTCODE" "CLIENT.ModClientCode",
"LND_PR_CLIENT"."CLIENTNAME",
"LND_HRM_PARCEL_MODIFIED"."ASSET_ID" "TABLE2.Modassetid",
"LND_CIVIC_ADDRESS"."CIV_NUM",
"LND_HRM_PARCEL_MODIFIED"."MODIFY_TYPE" "TABLE2.modtype",
"LND_ASSET_XREF"."MODASSETID",
"LND_ASSET_XREF"."HRMASSETID",
"LND_PARCEL_POLYGON"."PID"

FROM ((((("SDEADM"."LND_ACQUISITION_DISPOSAL" "LND_ACQUISITION_DISPOSAL" LEFT OUTER JOIN "SDEADM"."LND_HRM_PARCEL_MODIFIED" "LND_HRM_PARCEL_MODIFIED" ON "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET"="LND_HRM_PARCEL_MODIFIED"."ASSET_ID")

LEFT OUTER JOIN "SDEADM"."LND_CIVIC_ADDRESS" "LND_CIVIC_ADDRESS" ON "LND_HRM_PARCEL_MODIFIED"."PID"="LND_CIVIC_ADDRESS"."PID")

LEFT OUTER JOIN "SDEADM"."LND_ASSET_XREF" "LND_ASSET_XREF" ON "LND_HRM_PARCEL_MODIFIED"."ASSET_ID"="LND_ASSET_XREF"."MODASSETID")

LEFT OUTER JOIN "SDEADM"."LND_PR_CLIENT" "LND_PR_CLIENT" ON ("LND_HRM_PARCEL_MODIFIED"."ASSET_ID"="LND_PR_CLIENT"."ASSET_ID") OR ("LND_HRM_PARCEL_MODIFIED"."PID"="LND_PR_CLIENT"."PID"))

LEFT OUTER JOIN "SDEADM"."LND_PR_REALESTATE_COMMENTS" "LND_PR_REALESTATE_COMMENTS" ON ("LND_HRM_PARCEL_MODIFIED"."ASSET_ID"="LND_PR_REALESTATE_COMMENTS"."ASSET_ID") OR ("LND_HRM_PARCEL_MODIFIED"."PID"="LND_PR_REALESTATE_COMMENTS"."PID"))

LEFT OUTER JOIN "SDEADM"."LND_PARCEL_POLYGON" "LND_PARCEL_POLYGON" ON "LND_HRM_PARCEL_MODIFIED"."PID"="LND_PARCEL_POLYGON"."PID"

WHERE (("LND_ACQUISITION_DISPOSAL"."TRANS_TYPE" = 1)
AND NOT ("LND_HRM_PARCEL_MODIFIED"."MAIN_CLASS"='BUSINESS PARK')
AND ("LND_ACQUISITION_DISPOSAL"."HRM_DEPT"='Real Estate'))

 

[hilfy]

This is really a SQL question and not a Crystal question. Are you using a specific software package? If so, can their support help you with this query?

I would work in a db query tool other than Crystal - Toad, SQL Server Management Studio, PL/SQL Developer, etc. - and start with just the part of the query that works with the modified parcel data. Work with that until it's bringing back the data you expect - I usually do this by taking out the where clause and all of the joins and adding the joins back one at a time, and then add the where clause back one line at a time until you determine which line is causing no data to be returned. This is then the line that needs to be changed in order to get the data you want.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[kenhamady]

Before the UNION can work you have to have 2 queries that work correctly independently of each other. So run each half of the union separately and figure out the one that isn't working. Once you determine the one that is the problem, rebuild it starting with the first join only. Then add the next join, etc. When you add a join and start to get bad data you know which join is the problem, and then you have a clue on how to solve it.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/