u_where clause not hardcoded

Author: ncolmart@medinfo.fr (ncolmart)

Hi all
I would like to use a selectdb with a u_where clause on a field containing more or less values.
The problem is that values to evaluate are only known at runtime.

Example
selectdb sum(fieldA) from "TABLE" u_where (fieldB="1" | fieldB="2")
or
selectdb sum(fieldA) from "TABLE" u_where (fieldB="1" | fieldB="3" | fieldB="5" | fieldB="7" | fieldB="9")
etc

So, I don't know in advance the number of values to evaluate and of course the values themselves.

A solution could be to initialize the fieldB with the list of values, then a retrieve, then a loop, and doing the sum for each record.
But this will fetch to much data and slow down the process.
I would prefer the selectdb as the 'sum' will be made by the DBMS.

Any Idea ?

Thanks

5 Comments

  1. 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


    Author: ulrich-merkel (ulrichmerkel@web.de)
  2. 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%%%") )


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  3. 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


    Author: ncolmart (ncolmart@medinfo.fr)
  4. 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


    Author: ncolmart (ncolmart@medinfo.fr)
  5. 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


    Author: ulrich-merkel (ulrichmerkel@web.de)