Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
View to query differently depending on value of form pro
Message
 
 
To
12/12/2000 06:09:45
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00452108
Message ID:
00452131
Views:
21
>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.
Mark McCasland
Midlothian, TX USA
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform