select x,y,z from 3,4,5 tables

Author: dammie@seznam.cz (dammie)

3 Comments

  1. I use a view.


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  2. Betreff:   Re: [Uniface-L] select x,y,z from 3,4,5 tables
    Von:   Ulrich Merkel <UlrichMerkel@web.de>
    An:   Uniface User Group Discussion Forum <uniface-l@uug.org>
    Datum:   14.07.09 10:29:22


    Hi David,

    your WHERE clause contains
    relationship infos (c.id = d.id,b.some_id = c.some_id,a.other_id = b.other_id)
    some hardcoded (b.x in (1,2,3) and b.y > 0)
    as well as single SEARCH( a.id = pn_id)

    in uniface, you can use some kind of filter mechanism.
    We paint all the entities A,B,C.D next to each other
    and a dummy entity where we can store the results with the fields col_a, col_b and col_d

    We start with the select on entity A set
    id.a = pn_id
    and do a retrieve/e "A"

    for each occurence in A we select in B:
    clear/e "B"
    other_id.b = other_id.a
    y.b = "> 0" ; <GOLD>>
    x.b = "1|2|3" ; <GOLD>|
    retrieve/e "B"

    for each occurence in B, we select in C
    clear/e "C"
    some_id.c = some_id.b
    retrieve/e "C"

    for each occurence in C, we select in D
    clear/e "D"
    id.d = id.c
    retrieve/e "D"

    for each hit, in D, we fill our dummy entity
    col_a.dummy = col.a
    col_b.dummy = col.b
    col_d.dummy = col.d


    But there is an easier way to do in uniface:
    **********************************
    If you have defined relationships between A, B, C and D in the model
    And you paint entity B inside of A, C inside of B and D inside of C
    Then uniface will take care fetching the related many entities.

    To solve the hardcoded "b.x in (1,2,3) and b.y > 0"
    we put in the <READ> trigger of entity B a U_WHERE clause

    read u_where (y > 0 & (x = 1 | X = 2 | X=3) )

    Sucecss, Uli

    P.S for the u_where the help-info reads as
    The SelectionCriteria consists of one or more relational phrases with the operands linked by relational operators (<, !=, and so on) connected by logical operators (&, |, and so on).

    Remember that possible profile characters are always stripped from the SelectionCriteria, with the exception of "*" and "?", which are treated as literals. For example:

    read u_where (NAME = "A|B")

    results in the following SelectionCriteria:

    NAME = "AB"




    > -----Ursprüngliche Nachricht-----
    > Von: "dammie" <MusilDavid@seznam.cz>
    > Gesendet: 13.07.09 23:39:56
    > An: uniface-l@uug.org
    > Betreff: [Uniface-L] select x,y,z from 3,4,5 tables


    > Hello,
    >
    > do you have any muster that you use if you have rewrite select in uniface:
    >
    > e.g.
    >
    > select a.col, b.col, d.col from a t1, b t2, c t3, d t4
    > where c.id = d.id
    > b.some_id = c.some_id
    > b.x in (1,2,3) and b.y > 0
    > a.other_id = b.other_id
    > a.id = pn_id
    >
    > do you use nested loops or do you use read where clausule or do you simply use stored procedure and return entity to uniface?
    >
    > Thanks for your views on topic
    >
    > David
    > _______________________________________________
    > Uniface User Group Discussion Forum
    > For more information: http://lists.umanitoba.ca/mailman/listinfo/uniface-l
    > To unsubscribe/set options: http://lists.umanitoba.ca/mailman/options/uniface-l
    >

    -- Ulrich Merkel --
    ulrichmerkel@web.de
    +49(69)317881
    _______________________________________________
    Uniface User Group Discussion Forum
    For more information: http://lists.umanitoba.ca/mailman/listinfo/uniface-l
    To unsubscribe/set options: http://lists.umanitoba.ca/mailman/options/uniface-l

     


    Author: ulrich-merkel (ulrichmerkel@web.de)
  3. Many thanks for your hints...

     

    David


    Author: dammie (dammie@seznam.cz)