Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Furthering Mark's point, the property references make the query much harder to debug, too.
>>I have a view that queries on 3 different fields depending on a property on the form.
>>
>>Here is what I use:
>>
>>SELECT Careplan.*,
>>Ccmslook.lk_desc
>>FROM careplan
>>LEFT OUTER JOIN ccmslook
>>ON Careplan.lk_code = Ccmslook.lk_code
>>AND Ccmslook.lk_type = "AB"
>>WHERE Careplan.cl_ref = ?thisForm.lcCl_Ref
>>AND Careplan.ia_ref = IIF(?thisForm.cAssessment="I",?thisForm.lcIa_Ref,"")
>>AND Careplan.co_ref = IIF(?thisForm.cAssessment="C",?thisForm.lcCo_Ref,"") AND Careplan.ci_ref = IIF(?thisForm.cAssessment="P",?thisForm.lcCi_Ref,"") ORDER BY Careplan.cp_type
>>
>>
>>Can anyone tell me if the above is visable, and if not, what is the way I should be doing this.
>>
>>Thanks
>>Kev
>
>Should AND Ccmslook.lk_type = "AB" really be part of the JOIN condition? This looks more like it belongs in the filter [WHERE] clause. Personally, I do not like the use of object property references in SQL. I first store these values to variables and use the variables in the SQL. If you used variables, you could then remove all the IIFs from the SQL which would go a long way to optimization.
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