Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Jim
Thank you for responding. Testing your idea indicates the only successful way to do this is to use EMPTY().
SELE ...fields... ;
FROM customers LEFT [OUTER] JOIN invoices;
ON customers.customer_id = invoices.customer_id;
WHERE invoices.order_dt BETWEEN.... ;
OR EMPTY(Invoices.Order_dt) ...
Using ISNULL() (and ISBLANK() - I'll try anything !) both fail to return all customers, the query simply reverts to an inner join.
Using EMPTY() the query continues to work as required.
BUT ... I am worried about using this in production code, I have never seen this documented anywhere, and secondly this messes up the logic of the query horribly if the field in question can sometimes be EMPTY()!
e.g. Suppose I wanted to filter the inner table on a logical flag field, my query would have read
... WHERE invoices.lPaid = .T. ...
modifying this to read
... WHERE (invoices.lPaid = .T. OR EMPTY(invoices.lPaid) ...
to make the outer join work screws up the filtering as I now get all invoices both paid and unpaid.
I'm still looking at having to use UNION's to overcome this issue unless
someone can point out a better way.
David
Précédent
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