Complex queries | Bugs, Issues and Errors | Forum

Avatar

Please consider registering
guest

sp_LogInOut Log In sp_Registration Register

Register | Lost password?
Advanced Search

—  Results per page  —








— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters
For a group of consecutive words like 'end of support' use Match phrase

sp_Feed Topic RSS sp_TopicIcon
Complex queries
11 Sep 2018
11:06 am
Avatar
gianni
Member
Forum Posts: 331
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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.

11 Sep 2018
11:25 am
Avatar
istiller
Member
Forum Posts: 285
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

11 Sep 2018
12:40 pm
Avatar
Lauterbach
Member
Forum Posts: 46
Member Since:
06 Dec 2012
sp_UserOfflineSmall Offline

Hi Gianni,

 

I think you need a u_where and use & (AND) and | (OR)

 

Regards

Norbert

11 Sep 2018
4:29 pm
Avatar
gianni
Member
Forum Posts: 331
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

12 Sep 2018
9:32 am
Avatar
istiller
Member
Forum Posts: 285
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

12 Sep 2018
11:06 am
Avatar
gianni
Member
Forum Posts: 331
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

12 Sep 2018
11:28 am
Avatar
istiller
Member
Forum Posts: 285
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

12 Sep 2018
12:03 pm
Avatar
Arjen van Vliet
Member
Forum Posts: 182
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline
...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
12 Sep 2018
2:40 pm
Avatar
gianni
Member
Forum Posts: 331
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

Forum Timezone: Europe/Amsterdam

Most Users Ever Online: 131

Currently Online:
27 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

ulrich-merkel: 1840

Iain Sharp: 663

Theo Neeskens: 366

gianni: 331

istiller: 285

rogerw: 268

Knut: 219

lalitpct: 197

Arjen van Vliet: 182

sochaz: 173

Member Stats:

Guest Posters: 3

Members: 9740

Moderators: 0

Admins: 8

Forum Stats:

Groups: 1

Forums: 62

Topics: 2270

Posts: 9824

Newest Members:

agrohimujg, Ramhaturiff, best_auto, kings1, skorp0883, oraha, Anitastync, marina1993m, CharlesRip, Angiefoxlome

Administrators: admin: 23, Adrian Gosbell: 318, diseli: 1017, Bob Maier: 3, Nico Peereboom: 80, Michael Rabone: 4, richiet: 406, JanCees: 34