I think what you need to do is break up the construction of the SQL to account for the conditions so that you ultimately only create a small number of SQL queries. This may still be cumbersome, but this might work depending on how complicated your SQL code is. And how many conditions you have to account for.
If I understand the issue, your SQL code has a WHERE clause. If all three filters are chosen with a specific values, it looks something like:
WHERE field_1=?, field_2=? AND field_3=?
(and excuses if the SQL isn't right -- my SQL is very rusty)
You are substituting the actual values users are selecting for the various ? marks. But if the filter for field_1 is ALL, then "field_1=?," should not be in the WHERE clause. And so on for the other two.
So, create a String Builder block that has an execute condition. If the filter parameter for field_1 != ALL (or more probably you would use Not Matches), then it should build the string "field_1=" + the value of the filter parameter for field_1 + ", ". When users choose ALL for this filter parameter, the result of this block is an empty string.
Repeat this for the other two filter parameters to build just that portion of your SQL WHERE clause that defines the condition for their respective fields.
Then create a String builder block that concatenates " WHERE " with the String builder blocks for fields 1, 2 and 3. Try running these and testing them with ALL as the input parameters until you are sure that the WHERE clause is correct for any combination of input parameters. Then add that into the mix of the other String Builder blocks that are building the final SQL query(ies).
This should take care of 2 conditions being ALL. If you need to account for all 3 input parameters being ALL, you probably need to put an execute condition on the String Builder that is creating the WHERE clause. Check to make sure that not all of the input parameters match ALL. If they do, the result of the WHERE string builder will be an empty string. And it should still work.
Hope this helps,
Sara, technical writer/jackbe
Sara ,Sorry but ,I did not get the part where you said "create a String Builder block that has an execute condition. If the filter parameter for field_1 != ALL (or more probably you would use Not Matches), then it should build the string "field_1=" + the value of the filter parameter for field_1 + ", ". When users choose ALL for this filter parameter, the result of this block is an empty string."
Could you please post an example for the same..
Thank you..
Well, I was wrong about the solution. While attempting to put a full example together it became clear that my idea required complex logical expressions -- which are absolutely possible in EMML, but not in Wires.
My idea, expressed in pseudo code, was to use String Builder blocks to output the different portions of your WHERE clause in the SQL based on which input parameters were *not* equal to ALL. Something like this:
if input1 <> ALL, job_type=input1
if input2 <> ALL, job_exit_status=input2
if input3 <> ALL, mem_req_status==input3
You put the "if inputn<>ALL" as an execute condition on the appropriate String Builder block that outputs the "fieldn=inputn" string. This works just fine
Unfortunately, the logic needed to control whether to output a comma or "AND" in order to build the full WHERE clause requires complex logical expressions such as:
if input1<>ALL and (input2<>ALL or input3<>ALL), "AND"
You can do this in EMML, but Wires does not support execute conditions with combinations of AND and OR.
I think that this particular solution has hit the limit of what is possible in Wires. You can do this mashup in EMML, however. One approach would be to take what you have done in Wires already and use the View EMML button to copy the EMML code to either the Mashup Editor (in Presto) or the Mashup Studio (in Eclipse) if you have access to those tools. Then continue editing the EMML until the mashup does what you need.
Sara, technical writer/jackbe
Hi
I have below 3 input parameters which allow user to select a value and filter data , i need to have one additional filter value in each of the filters say "ALL" which allows user to select all values in each of the filter , i tried this by creating 2 sql blocks in wires one for All and other for selected value, which works fine when one out of 3 filters are selected , but when i select All from 2 of the filter the sql logic fails , for example if i have logic something like select * from table where job_type = all and job_exit_status = done , then i get values if i want select * from table where job_type= all and Job_exit_status = All , do i need to create another sql block ??, i may be getting some more additional filters , its not practically possible to have a separate sql block for each of the combination of filter values , could anyone please suggest a better appraoch for this , i am not familiar with java scripting , is their any way i could achive this using mashups in wires ?
Attached the emml i have currently in my mashup...
Please Advice!