Complex queries

Author: gianni.sandigliano@unifacesolutions.com (gianni)

Hi Unifacers, [probably I am missing something here... :-) ] having the need to implement on an entity a complex query mixing ANDs and ORs like: WHERE (field1 LIKE value1 AND field2 LIKE value2 AND field3 = value3 OR field4 = value11)         OR (field1 LIKE value4 AND field2 LIKE value5 AND field3 = value6 OR field4 = value12)         OR (field1 LIKE value7 AND field2 LIKE value8 AND field3 = value9 OR field4 = value13) ORDER BY field4, field5, field6 As of Uniface 9.7.05 the best way I know to implement such functionality is splitting into different retrieve and ordering in the external form/service structure, like: clear field1 = value1 field2 = value2 field3 = value3 field4 = value11 retrieve creocc "myEntity", -1 field1 = value4 field2 = value5 field3 = value6 field4 = value12 retrieve/a creocc "myEntity", -1 field1 = value7 field2 = value8 field3 = value9 field4 = value13 retrieve/a sort "myEntity", "field4, field5, field6" Is there a way using only standard Uniface profiles to implement such complex query in a single shot to enable using ORDER BY capabilities from underlying RDBMS? Thanks for any hint / suggestion. Gianni P.S. I know a parser could be written to generate a complex WHERE string but AFAIK it is NOT a simple task expecially to maintain full RDBMS portability.

8 Comments

  1. gianni said Hi Unifacers, [probably I am missing something here... :-) ] having the need to implement on an entity a complex query mixing ANDs and ORs like: WHERE (field1 LIKE value1 AND field2 LIKE value2 AND field3 = value3 OR field4 = value11)         OR (field1 LIKE value4 AND field2 LIKE value5 AND field3 = value6 OR field4 = value12)         OR (field1 LIKE value7 AND field2 LIKE value8 AND field3 = value9 OR field4 = value13) ORDER BY field4, field5, field6

    Hi Gianni First:      Are you sure, that the OR inside the brackets are corret?               The clear, set, retrievie sequence is a different profile Second: What about U_CONDITION?               With U_CONDITION you can submit any query you want. Gruß Ingo


    Author: istiller (i2stiller@gmx.de)
  2. Hi Gianni,   I think you need a u_where and use & (AND) and | (OR)   Regards Norbert


    Author: Lauterbach (norbert.lauterbach@infraserv.com)
  3. HI Ingo & Norbert, @Ingo - OR inside brackets: YES is correct...it is part of business rule. @Ingo - using U_CONDITION: it was my first pick but then I did not follow up on it, gotfeeling a Uniface list should not be enough! Do you have an example implementing complex query with multiple ANDs and ORs like the one I've written? @Norbert - u_where is solved at compilation time while u_condition is solved at runtime. Do you probably mean u_condition (like Ingo proposed)? Thanks anyhow for these valuable inputs. Gianni


    Author: gianni (gianni.sandigliano@unifacesolutions.com)
  4. gianni said @Ingo - using U_CONDITION: it was my first pick but then I did not follow up on it, gotfeeling a Uniface list should not be enough! Do you have an example implementing complex query with multiple ANDs and ORs like the one I've written?  

    Buongiorno Gianni Build an u_condition is straight foreward ;-) Okay, some time it's not easy to read for us humans, but there is no magic in it. I don't know, weather value_n in your example is a litteral, a procedure local variable, another field or component/global variables Assuming they are string constants, I gave you an example how to implement If value_n are fields or component/global variables, one don't need quotation marks, just do "field_n = $var_n$" procedure local vraibles will also work but caution: If you prepare the condition (lets say $COND$) in one procedure and use $COND$ in READ-trigger, UnifAce is unable to resolve the variables Remarks One can use $concat instead of "in string replacements". If it looks better, I don'r know I'm using v_COND_i with i the level of the expression. So you cann add mor leveles in the same maner The strane v_COND_i[4] is the cut off the first boolean expression. Do so, you can order the conditions and add more without thinking about it. 'hope that I could help you a little bit :) Grüße aus Mainz Ingo ;Prepare conditon v_COND = "" v_COND1 = "" v_COND1 = "%%v_COND1%% & FIELD1=%%"%%value1%%" ; value 1 do have wildcards/GOLD-xyz v_COND1 = "%%v_COND1%% & FIELD2=%%"%%value2%%" ; value 2 do have wildcards/GOLD-xyz v_COND1 = "%%v_COND1%% & FIELD3=%%"%%value3%%" v_COND1 = "%%v_COND1%% | FIELD4=%%"%%value11%%" ; Are you sure about mix up two levels of a boolean expression? v_COND = "%%v_COND%% | (%%v_COND1[4]%%%)" v_COND1 = "" v_COND1 = "%%v_COND1%% & FIELD1=%%"%%value4%%" ; value 4 do have wildcards/GOLD-xyz v_COND1 = "%%v_COND1%% & FIELD2=%%"%%value5%%" ; value 5 do have wildcards/GOLD-xyz v_COND1 = "%%v_COND1%% & FIELD3=%%"%%value6%%" v_COND1 = "%%v_COND1%% | FIELD4=%%"%%value12%%" v_COND = "%%v_COND%% | (%%v_COND1[4]%%%)" v_COND1 = "" v_COND1 = "%%v_COND1%% & FIELD1=%%"%%value7%%" ; value 7 do have wildcards/GOLD-xyz v_COND1 = "%%v_COND1%% & FIELD2=%%"%%value8%%" ; value 8 do have wildcards/GOLD-xyz v_COND1 = "%%v_COND1%% & FIELD3=%%"%%value9%%" v_COND1 = "%%v_COND1%% | FIELD4=%%"%%value13%%" v_COND = "%%v_COND%% | (%%v_COND1[4]%%%)" ... v_COND = v_COND[4] ;Prepare order v_ORDER = "field4, field5, field6" ;Do retrieve read u_CONDITION(v_COND) order by v_ORDER


    Author: istiller (i2stiller@gmx.de)
  5. Thanks Ingo, SOLVED! My mistake: I have intepreted in a wrong way description of u_condition available into Uniface Library; more examples would be nice. Side note: U_Condition string must always be a component variable or a global variable because is normally generated in one trigger/module (EXEC or DETAIL or operation) and used in another (READ). Because wishlist is still not available, it would be nice if these wishes would be considered: 1) A function to generate a u_condition string from one occurrences containing profiles would be available Syntax: $UconditionFromProfile(Entity [, Occurrence(s)]) Occurrences is a Uniface list with one or more positions Example: clear field1.entity = "UU*"              ; is a * field2.entity = "<20100101"   ; is a < $MYUCOND$ = $UconditionFromProfile("Entity") Result: $MYUCOND$ = "(FIELD1='UU*' & FIELD2<'20100101') 2) When second parameter is an Uniface list with more than one position same function should be able to work on listed occurrences concatenating with OR profiles originated from different occurrences. Example: clear field1.entity = "UU*"              ; is a * field2.entity = "<20100101"   ; is a < putitem myProfileOccurrences, -1, $curocc(entity) creocc "entity", -1 field1.entity = "UU*"              ; is a * putitem myProfileOccurrences, -1, $curocc(entity) $MYUCOND$ = $UconditionFromProfile("Entity", myProfileOccurrences) Result: $MYUCOND$ = "((FIELD1='UU*' & FIELD2<'20100101') | (FIELD1='UH*')" 3) A new param for retrieve instruction would be able to query using more occurrences as retrieve profiles Syntax: retrieve "Entity" [, Occurrence(s)]) Functionality is like a normal retrieve but using more occurrences to build the complete query profile, using OR between different occurrences; if list is not defined or single value default to current behaviour. 4) A new param for retrieve/a instruction would be able to query using more occurrences as retrieve profiles without discarding current hitlist Syntax: retrieve/a "Entity" [, Occurrence(s)]) Functionality is like a normal retrieve/a but using more occurrences to build the complete query profile, using OR between different occurrences; if list is not defined or single value default to current behaviour. Regards, Gianni


    Author: gianni (gianni.sandigliano@unifacesolutions.com)
  6. gianni said Side note: U_Condition string must always be a component variable or a global variable because is normally generated in one trigger/module (EXEC or DETAIL or operation) and used in another (READ).

    A field works too

    Because wishlist is still not available, it would be nice if these wishes would be considered: 1) A function to generate a u_condition string from one occurrences containing profiles would be available Syntax: $UconditionFromProfile(Entity [, Occurrence(s)]) Occurrences is a Uniface list with one or more positions Example: clear field1.entity = "UU*"              ; is a * field2.entity = "<20100101"   ; is a < $MYUCOND$ = $UconditionFromProfile("Entity") Result: $MYUCOND$ = "(FIELD1='UU*' & FIELD2<'20100101')  

    This wich is allready in wish list :-) https://unifaceinfo.com/convert-profile-to-sql-statement/ Ingo


    Author: istiller (i2stiller@gmx.de)
  7. ...and the u_where example:
    
    
    read %\
    u_where (((field1=value1) & (field2=value2) & (field3=value3) | (field4=value11)) %\
                        |((field1=value4) & (field2=value5) & (field3=value6) | (field4=value12)) %\
                        |((field1=value7) & (field2=value8) & (field3=value9) | (field4=value13)) %\
                        ) %\
    order by "field4, field5, field6"

      Like Ingo is pointing out already, the order by part can also be a substitution:

    $1 = "field4, field5, field6"
    read order by $1

    Author: Arjen van Vliet (arjen.van.vliet@uniface.com)
  8. istiller said This which is already in wish list :-) https://unifaceinfo.com/convert-profile-to-sql-statement/ Ingo  

    Hi Ingo, converting a profile to a full SQL statement it is a lot more complex task than converting a profile to a u_condition string. Thanks to all! Gianni P.S. Anyone interested in a Uniface function profile2ucondition() could drop me an email at gianni.sandiglianoATSIGNunifacesolutions.com


    Author: gianni (gianni.sandigliano@unifacesolutions.com)