This question is related to an earlier one I wrote yesterday... I thought my problem was with the parameters but even without the parameters, I seem to have a problem selecting records from two tables with an "or" statement...
Attached is the SQL from the report I am trying to build.
As you can see, there are several joins, and the relationship between the Parcel table and the Parcel_Modified table is basically this, a record related to the acquisition of land is inserted into the Parcel table. When that land gets modified (subdivided), that original record gets copied into the Parcel_Modified table, and the new land records (say the two new parcels that were created through the subdivision of land) gets input into the Parcel table. The original and the two new records are all related through a field in the Acquisition_Disposal table called orig_asset.
The report I am trying to create needs to show all parcels of land acquired as they were originally acquired.... Therefore, if a parcel was modified, the record that needs to display is NOT the two new records that are in the Parcel table, but the original record that is now in the Parcel_Modified table. This is why that orig_asset field is so important.
I also need to build in a parameter (or just create two reports, one for each type) that displays land for Business Parks, or all non-business park lands....this is a field called Main Class from either the HRM_Parcel table or the HRM_Parcel_Modified table.
So, even without using parameters (lets just say I was going to build two report, one for Business Parks, and the other for Non, the filter query would be like this would it not??:
" ACQUISITION_DISPOSAL"."TRANS_TYPE"=1 AND NOT ("ACQUISITION_DISPOSAL"."ACQDISTYPE"='EXCHANGE' OR " ACQUISITION_DISPOSAL"."ACQDISTYPE"='SUBDIVISION') AND ("ACQUISITION_DISPOSAL"."ACQDISDATE">=TO_DATE ('01-11-1979 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND " ACQUISITION_DISPOSAL"."ACQDISDATE"<TO_DATE ('31-12-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND ("PARCEL"."MAIN_CLASS"<>'BUSINESS PARK' OR " PARCEL_MODIFIED"."MAIN_CLASS"<>'BUSINESS PARK')
However, it is the end section in that filter that does not seem to work! With this statement above, I get ONLY the records in the Parcel table that do not have a main class of Business Park. I don't get any records at all from the Parcel_Modified table. And if I reverse the order if that part if the statement, I get only records from the Parcel_Modified table, and none from the Parcel table..... If I delete just that part of the filter, everything works fine.... I just get all of the Main Class types of land..... I just want to be able to seperate them out either by a parameter choice selection, or using two reports....
There is probably something really obvious and stupid I am doing, but I can't see it!!
Help??
Attached is the SQL from the report I am trying to build.
As you can see, there are several joins, and the relationship between the Parcel table and the Parcel_Modified table is basically this, a record related to the acquisition of land is inserted into the Parcel table. When that land gets modified (subdivided), that original record gets copied into the Parcel_Modified table, and the new land records (say the two new parcels that were created through the subdivision of land) gets input into the Parcel table. The original and the two new records are all related through a field in the Acquisition_Disposal table called orig_asset.
The report I am trying to create needs to show all parcels of land acquired as they were originally acquired.... Therefore, if a parcel was modified, the record that needs to display is NOT the two new records that are in the Parcel table, but the original record that is now in the Parcel_Modified table. This is why that orig_asset field is so important.
I also need to build in a parameter (or just create two reports, one for each type) that displays land for Business Parks, or all non-business park lands....this is a field called Main Class from either the HRM_Parcel table or the HRM_Parcel_Modified table.
So, even without using parameters (lets just say I was going to build two report, one for Business Parks, and the other for Non, the filter query would be like this would it not??:
" ACQUISITION_DISPOSAL"."TRANS_TYPE"=1 AND NOT ("ACQUISITION_DISPOSAL"."ACQDISTYPE"='EXCHANGE' OR " ACQUISITION_DISPOSAL"."ACQDISTYPE"='SUBDIVISION') AND ("ACQUISITION_DISPOSAL"."ACQDISDATE">=TO_DATE ('01-11-1979 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND " ACQUISITION_DISPOSAL"."ACQDISDATE"<TO_DATE ('31-12-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND ("PARCEL"."MAIN_CLASS"<>'BUSINESS PARK' OR " PARCEL_MODIFIED"."MAIN_CLASS"<>'BUSINESS PARK')
However, it is the end section in that filter that does not seem to work! With this statement above, I get ONLY the records in the Parcel table that do not have a main class of Business Park. I don't get any records at all from the Parcel_Modified table. And if I reverse the order if that part if the statement, I get only records from the Parcel_Modified table, and none from the Parcel table..... If I delete just that part of the filter, everything works fine.... I just get all of the Main Class types of land..... I just want to be able to seperate them out either by a parameter choice selection, or using two reports....
There is probably something really obvious and stupid I am doing, but I can't see it!!
Help??