Hi Folks:
We are in the process of trying to convert from using an old program (Brio from the early 2000's) to Toad Data Point.
I'm having problems converting from the old style SQL (with (+) ) to Ansi.
Here is the working Code:
SELECT SUBSTR (historical.animal, 3, 6) AS Animals,
CURRENT_LOCATIONS.PEN,
NULL AS WT,
NULL AS HT,
CURRENT_ANIMAL_COMMENTS.COMMENTS AS NOTES,
NULL AS Comments,
CURRENT_ANIMAL_COMMENTS.COMMENT_DATE,
CURRENT_ELECTRONIC_IDS.ELECTRONIC_ID
FROM CATTLE.HISTORICAL HISTORICAL,
CATTLE.CURRENT_LOCATIONS CURRENT_LOCATIONS,
CATTLE.CURRENT_MANAGEMENT_GROUPS CURRENT_MANAGEMENT_GROUPS,
CATTLE.CURRENT_ANIMAL_COMMENTS CURRENT_ANIMAL_COMMENTS,
CATTLE.CURRENT_ELECTRONIC_IDS CURRENT_ELECTRONIC_IDS
WHERE (CURRENT_LOCATIONS.PEN = '108')
AND (CURRENT_ANIMAL_COMMENTS.COMMENT_DATE(+) > '1-aug-2021')
AND (HISTORICAL.SEASON = 202101)
AND (HISTORICAL.WEANING_DATE IS NOT NULL)
AND (HISTORICAL.DISPOSAL_CODE IS NULL)
AND (CURRENT_LOCATIONS.LOCATION = 'AREA 42')
AND (CURRENT_MANAGEMENT_GROUPS.MANAGEMENT_GROUP != 8)
AND (HISTORICAL.ANIMAL = CURRENT_LOCATIONS.ANIMAL)
AND (HISTORICAL.ANIMAL = CURRENT_MANAGEMENT_GROUPS.ANIMAL)
AND (CURRENT_ANIMAL_COMMENTS.ANIMAL(+) = HISTORICAL.ANIMAL)
AND (CURRENT_ELECTRONIC_IDS.ANIMAL(+) = HISTORICAL.ANIMAL)
When I convert it to Ansi, I get the following code:
SELECT SUBSTR (HISTORICAL.ANIMAL, 3, 6) ANIMALS,
CURRENT_LOCATIONS.PEN,
NULL WT,
NULL HT,
CURRENT_ANIMAL_COMMENTS.COMMENTS NOTES,
NULL COMMENTS,
CURRENT_ANIMAL_COMMENTS.COMMENT_DATE,
CURRENT_ELECTRONIC_IDS.ELECTRONIC_ID
FROM CATTLE.HISTORICAL HISTORICAL
INNER JOIN CATTLE.CURRENT_LOCATIONS CURRENT_LOCATIONS
ON (HISTORICAL.ANIMAL = CURRENT_LOCATIONS.ANIMAL)
INNER JOIN CATTLE.CURRENT_MANAGEMENT_GROUPS CURRENT_MANAGEMENT_GROUPS
ON (HISTORICAL.ANIMAL = CURRENT_MANAGEMENT_GROUPS.ANIMAL)
LEFT OUTER JOIN CATTLE.CURRENT_ANIMAL_COMMENTS CURRENT_ANIMAL_COMMENTS
ON (HISTORICAL.ANIMAL = CURRENT_ANIMAL_COMMENTS.ANIMAL)
LEFT OUTER JOIN CATTLE.CURRENT_ELECTRONIC_IDS CURRENT_ELECTRONIC_IDS
ON (HISTORICAL.ANIMAL = CURRENT_ELECTRONIC_IDS.ANIMAL)
WHERE ( ( ( ( ( (CURRENT_LOCATIONS.PEN = '108')
AND ((CURRENT_ANIMAL_COMMENTS.COMMENT_DATE /*(+)*/ ) > '1-aug-2021'))
AND (HISTORICAL.SEASON = 202101))
AND (HISTORICAL.WEANING_DATE IS NOT NULL))
AND (HISTORICAL.DISPOSAL_CODE IS NULL))
AND (CURRENT_LOCATIONS.LOCATION = 'AREA 42'))
AND (CURRENT_MANAGEMENT_GROUPS.MANAGEMENT_GROUP <> 8)
The problem line is this one:
AND ((CURRENT_ANIMAL_COMMENTS.COMMENT_DATE /*(+)*/ ) > '1-aug-2021'))
In the old style, there is a (+) in that line. with that 29 lines rows of data are returned. Without it, only two lines are returned. (limited by to only two rows). Below is an example of the data:
I would appreciate any help with understanding how I should change the ANSI statement with the comment_date (+) to give the same results. Thank you so much for any help...
Matthew