Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
SQL SELECT fails with DateTime Joins
It appears to us that SQL Selects involving DateTimes in Join conditions produce inconsistent answers depending upon the data. We are attempting to extract the latest transaction DateTime for a given transaction type in a table. The following simplified data table and code will reproduce the problem.
Please run the following code repeatedly and observe the results in crWrongAnswer. It should always have three rows, but you may observe anything from zero to three rows.
** Begin Code
CREATE TABLE DT (DTID I, FKID I, DateTime T)
SELECT DT
INSERT INTO DT (DTID, FKID, DateTime) VALUES (1, 4, DATETIME()-INT(RAND()*5555555))
INSERT INTO DT (DTID, FKID, DateTime) VALUES (2, 5, DATETIME()-INT(RAND()*5555555))
INSERT INTO DT (DTID, FKID, DateTime) VALUES (3, 6, DATETIME()-INT(RAND()*5555555))
BROWSE LAST NOWAIT
SELECT FKID, MAX(DateTime) AS DateTime FROM DT GROUP BY 1 INTO CURSOR crMaxDT NOFILTER
BROWSE LAST NOWAIT
SELECT DT.* FROM DT INNER JOIN crMaxDT ON (DT.FKID = crMaxDT.FKID and DT.DateTime = crMaxDT.DateTime) INTO CURSOR crWrongAnswer NOFILTER
BROWSE LAST NOWAIT
** End Code
This is driving us crazy in Virginia. Please help!
Thank you,
John L. Malinowski
Wicks Broadcast Solutions
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement