Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Left Outer Join Help needed
Hi All:
Trying to get someone else's code to run and having a problem during testing that needs resolution before placing this into production.
I have two tables -- operators and operator permissions. The idea is to print a report with all permissions for each operator on it. There is a filter dialog where one can enter a specific operator's name and it will just print for that one, or if blank it will print for all operators in the operator table. The earlier programmer made a SQL select to combine the info from the two tables, something like this (simplified -- many more fields included in the actual code):
SELECT DISTINCT ;
Op_Info.cOp_ID, ;
cOp_Lname, ;
cOp_Fname, ;
FROM Op_Info ;
LEFT OUTER JOIN Op_Modul ;
ON Op_Info.cOp_ID = Op_Modul.cOp_ID ;
WHERE .T. ;
INTO CURSOR OperTemp1 NOFILTER
The "WHERE .T." is the evaluated where clause if all operators are desired, otherwise it is something like 'WHERE operator id = "xxxx"'...
Problem is: If I choose a specific existing operator, it works fine, but if I have operators that do not currently have any access rights, (ie: operators that do not exist in the permissions table) the SQL fails with 'datatype mismatch'.
I isolated the problem to the LEFT OUTER JOIN clause. Before that point, the sql works fine. As soon as I try to add in the LEFT OUTER JOIN clause it fails if I have any operators without at least one record in the permissions table.
When I tracked down the problem, I kludged together a quick fix to just scan the permissions table an put in a dummy record if a given operator is not found, but I really want to do this right -- and I really don't understand the LEFT OUTER JOIN problem.
Any help would be appreciated.
TIA
Rob
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