u_where clause not hardcoded

Edited 100305: This is not working in the context described above

Hi,

u_where is governed by Uniface, so the following solution may help:

You can use string substitution:

var_u_wheretext = "fieldB=%%"1%%" | fieldB=%%"3%%" | fieldB=%%"5%%" | fieldB=%%"7%%" | fieldB=%%"9%%""
selectdb sum(fieldA) from "TABLE" u_where ("%%var_u_wheretext%%%")

Instead of a couple of "|" OR-statements, we may be able to use a list (generated by a putlistitems statement):

var_u_wherelist = "1;3;5;7;9"
selectdb sum(fieldA) from "TABLE" u_where (fieldB="%%var_u_wherelist%%%")

Success, Uli

Two other approaches

One is retrieve profile (rather than u_where

fieldb="1.|2.|3.|5.|7" ( .| = Gold-| )
retrieve/e "ENT"

The other is u_condition

v_condition = "fieldb=1 | fieldb=2 | fieldb=3 | fieldb=5 | fieldb=7"
read uconditon (v_conditon) (might be read ucondition ("%%v_condition%%%") )

Hi

Thanks for your time.

I've tried your solutions and it works.

But I tried to find a solution without the 'read' for performance issue.
As 'read' fetch data, I would prefer to perform the select of the 'sum' directly in the database.

Thanks to your both help, I found the solution : 'u_condition' can also be used for 'selectdb'

So,
v_condition = "fieldb=1 | fieldb=2 | fieldb=3 | fieldb=5 | fieldb=7"
selectdb sum(fielda) from TABLE where uconditon (v_conditon)

works well

Many thanks to all of you
Nicolas

Hi Ulrich

Thanks for your time.

I've tried the 2 solutions but without success.

SOLUTION 1 :
-------------
X_WHERE = "FieldB=%%"6%%" | FieldB=%%"I%%" | FieldB=%%"A%%""
selectdb count(FieldA) from "TABLE" u_where ("%%X_WHERE%%%") to $1

I obtained the following error
I/O function: S, mode: 4, on file/table: TABLE index: 1 >=
Where 'FieldB="6" | FieldB="I" | FieldB="A"' Is Not '0' Select(Count(FieldA))
select count(FieldA) from TABLE where (?<>?)
Restricted data type attribute violation

In the Sql script generated, the content of the X_WHERE variable is considered as a fieldname and not as a search condition

SOLUTION 2 : List of values
-------------
X_WHERE="6;I;A" ; (';' is a Gold;)
selectdb count(FieldA) from "TABLE" u_where (FieldB="%%X_WHERE%%%" ) to $1

I obtained the following Sql script
I/O function: S, mode: 4, on file/table: TABLE index: 1 >=
Where (FieldB = '6') Select(Count(FieldA))
select count(FieldA) from TABLE where (FieldB=?)
-> Hits selected: 1 .

In the Sql script generated, only the first value of the X_WHERE list is taken into account.

We are currently working in 8.2.03. But we plan to migrate soon in 9.4.
I've not yet tried with this version. Do you think it works with the most recent version ?

Thanks
Nicolas

Hi Nicolas,

I don't think the uniface version makes a difference.

I scanned my notes in the "dusty archives" and found we used the WHERE clause.
But teh where-clause is not available for selectdb.

So, as Iain stated, u_condition is the way to go.

I apologize for the inconvenience.
Success, Uli