| |
| Publicado el 04,noviembre 2015 - 06:00 |
I have a query in which there are multiple parameters.
Depending on user selection I want to set some parameters with a value and some without any value so that they do not have any effect on the output of the query.
Here is the query:
SELECT student_master.sm_id AS sm_id, student_master.sm_student_name AS sm_student_name, student_master.sm_gr_no AS sm_gr_no, enrollment.enrollment_roll_no AS enrollment_roll_no, student_master.sm_gender AS sm_gender, student_master.sm_performance_benchmark AS sm_performance_benchmark FROM enrollment, student_master WHERE enrollment.enrollment_sm_id = student_master.sm_id AND ( enrollment.enrollment_ay_id = {ParamAcademicYear} AND enrollment.enrollment_std_id = {ParamStdID} AND enrollment.enrollment_div_id = {ParamDivID} ) Here for example if a user selects the Standard then I just want to set the {ParamStdID} to a value and make others null.
How to achieve this?
If I try to set the other parameters to null it get error at run time.
TIA
Yogi Yang |
| |
| |
| | | |
|
| | |
| |
| Publicado el 04,noviembre 2015 - 08:53 |
Yogi,
then just don't fill in those parameters.
Suppose your query is named Query1 and you records with stdid=3 then do this:
Query1.ParamAcademicYear = 2015
Query1.ParamStdID = 3
HExecuteQuery(Query1)
If you just need DivID
Query1.ParamAcademicYear = 2015
Query1.ParamDivID = 77
HExecuteQuery(Query1)
If you need both
Query1.ParamAcademicYear = 2015
Query1.ParamStdID = 3
Query1.ParamDivID = 77
HExecuteQuery(Query1) |
| |
| |
| | | |
|
| | |
| |
| Publicado el 04,noviembre 2015 - 11:15 |
...further to Arie's response, if you need to re-execute the query, I tend to issue a HFreeQuery to free up system resources but the additional effect of doing this is that the parameters are reset to NULLs as well.
Sometimes depending on requirements, I might also set the paramter = Null in my code.
HTH |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,noviembre 2015 - 05:49 |
Arie and Danner,
Thanks for your inputs but I have to use the same query again and again with different parameters.
Let me explain this a bit more about the UI that I have built:
I have a window in which the user has to select the criteria according to which they want to see the data and when they click on a button another window is opened with the list of data.
Now the user can close the list window and again change the criteria and click on the button to get the list as per different criteria.
Actually this should work but unfortunately this seems to work for the first time and after that the result has unexpected data in the list.
TIA
Yogi Yang |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,noviembre 2015 - 08:00 |
Hi yogi,
This is I guess normal behavior. You cannot use a querie over and over again. You can but moast likely get unexpected results!.
For the same reson you should never make one query and use it at different parts in your application. As a roule I use one query only ones !.
Iam not an expert. But I took this advice form a verry experianced programmer and have not had unexpected results since.
Maybe arie can expalin why this is?
Ok for what you want I would temperary save the first query in a file. On that you can do filtering or even run a new query.
regards
Allard
Regards
Allard |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,noviembre 2015 - 09:08 |
Yogi,
there are several ways to present the query data, can you give us more details?
In my apps I use queries this way almost everywhere. So it can work as expected.
Do you mean you execute the query in one window and display the result in another.Maybe it has something to do with the "independnat hyperfile context" of your window? Afaik query are global so that should work.
Or do you pass the parameters to the window and run it there. Both would work, but the latter is more common practise if you ask me.
How do you display the results? In a table "bound" to the query? Or do you fill the table by programming.
I use the latter. I never use tables bound to datafiles or queries. Just for one reason: to keep things under control myself.
Back in WD14 and earlier I used TableAddLine "for each" record.
Later I started filling an array of structures with the query data (FileToMemory) and add computed values and so on on the fly. And bound the table to this array. Works much faster! And I am still in control of the data presented. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,noviembre 2015 - 09:33 |
Yogi,
I have a pretty complex query and a set of fields and drop-downs where the user selects and enters various values and here's how I prepare the query and the parameters:
// Reset the query HFreeQuery(QRY_ProductsAndCategoriesAllForMaint) // Decide if we search on the code or the other filter fields IF WEDIT_CodeSearch = "" THEN // Set up query params IF WEDIT_TitleSearch <> "" THEN QRY_ProductsAndCategoriesAllForMaint.SearchTitle = WEDIT_TitleSearch END IF WCBX_Categories <> 0 THEN QRY_ProductsAndCategoriesAllForMaint.SearchCategory = WCBX_Categories..DisplayedValue END IF WCBX_ModelSearch <> 0 THEN QRY_ProductsAndCategoriesAllForMaint.ProdModelID = WCBX_ModelSearch END // Set the active mode // 1 = Active // 2 = In-Active // 3 = Both SWITCH glocaliTempActiveMode CASE 1: QRY_ProductsAndCategoriesAllForMaint.Active = 1 CASE 2: QRY_ProductsAndCategoriesAllForMaint.Active = 0 CASE 3: QRY_ProductsAndCategoriesAllForMaint.Active = Null END ELSE // Use code search QRY_ProductsAndCategoriesAllForMaint.SearchCode = WEDIT_CodeSearch // Clear the contents of the CodeSearch field WEDIT_CodeSearch = "" END // Execute the query glocalbQryres = HExecuteQuery(QRY_ProductsAndCategoriesAllForMaint)
There was a learning curve in getting to this point, but it DOES work and it seems to be rock solid. The window where this query is used is the most heavily used window of my app. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,noviembre 2015 - 15:35 |
Darren,
I think I have found my answer from your reply.
But just for clarifications. Will this code work?
IF AY > 0 THEN QRY_Students.ParamAcademicYear = AY ELSE QRY_Students.ParamAcademicYear = Null END //IF AY > 0 THEN IF Std > 0 THEN QRY_Students.ParamStdID = Std ELSE QRY_Students.ParamStdID = Null END IF Div > 0 THEN QRY_Students.ParamDivID = Div ELSE QRY_Students.ParamDivID = Null END
I am trying this code in WM17 but am not getting expected results.
TIA
Yogi Yang |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,noviembre 2015 - 15:50 |
Hey Yogi,
If you issue a HFreeQuery beforehand, you shouldn't need the "= Null" lines of code. HFreeQuery is the easiest/safest/quickest way (delete as applicable :xcool: ) to reset everything about a WX query.
For example, because I've issued the HFreeQuery(...), if WEDIT_TitleSearch = "" then I don't need to set it to NULL because HFreeQuery has set all the parameters to NULL:
IF WEDIT_TitleSearch <> "" THEN QRY_ProductsAndCategoriesAllForMaint.SearchTitle = WEDIT_TitleSearch END
So your code becomes:
HFreeQuery(QRY_Students) IF AY > 0 THEN QRY_Students.ParamAcademicYear = AY END //IF AY > 0 THEN IF Std > 0 THEN QRY_Students.ParamStdID = Std END IF Div > 0 THEN QRY_Students.ParamDivID = Div END
...and revisiting my code, I probably don't need the CASE 3: that sets the Null value either :cool:
As for whether this will work on WM, I believe it will, as I'm using parameters in WM queries but then again, I'm using WM20 and can't comment on earlier versions as v20 is the first WM version I've used. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,noviembre 2015 - 15:52 |
Arie,
I have two windows.
In one the user selects the criteria which are displayed as combo boxes. Here is the screen shot
[attachment 1760 2015-11-05_202025.jpg]
In the other window I am passing the values of combo boxes as parameters and show the required information in a Looper.
TIA
Yogi Yang |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,noviembre 2015 - 15:57 |
Darren,
I tried the the HFreeQuery route but in case of WM17 it gives error in the final Android at run time.
TIA |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,noviembre 2015 - 16:05 |
Looking at the Help, it was introduced at V16, but that was probably into WD?
If it's not avaiable in your version of WM, I would have thought you'd get a syntax error. Is your error message stating it's the HFreeQuery command causing the problem?
If so, I wonder if HCancelDeclaration might help?
http://help.windev.com/en-US/… |
| |
| |
| | | |
|
| | |