General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only