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

...and the u_where example:

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

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

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

Hi Gianni,   I think you need a u_where and use & (AND) and | (OR)   Regards Norbert

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

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

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