if ambig workaround

[Sebustone]

Hello! As a progress newbie I dont have a solution for this question: Is there a workaround for IF AMBIG if there is a join on multiple tables? Any help would be appreciated Progress OpenEdge 11.7

 

[Stefan]

Try showing some basic code that demonstrates your problem, ProgressAblDojo is great for showing running code.

 

[Sebustone]

DEFINE TEMP-TABLE foo NO-UNDO
  FIELD pk AS INTEGER
  FIELD t AS CHARACTER
  FIELD fk AS INTEGER
  // should be replaced with field b2 from bar
  FIELD b AS LOGICAL  
  INDEX i t.

DEFINE TEMP-TABLE bar NO-UNDO
  FIELD pk2 AS INTEGER
  FIELD t2 AS CHARACTER
  FIELD b2 AS LOGICAL
  INDEX i2 pk2.

CREATE foo.
pk = 1.
t = "sometext".
fk = 1.
b = FALSE.
RELEASE foo.

CREATE foo.
pk = 2.
t = "sometext2".
fk = 1.
b = FALSE.
RELEASE foo.

CREATE foo.
pk = 3.
t = "sometext3".
fk = 2.
b = TRUE.
RELEASE foo.

CREATE bar.
pk2 = 1.
t2 = "sometext4".
b2 = FALSE.
RELEASE bar.

CREATE bar.
pk2 = 2.
t2 = "sometext5".
b2 = TRUE.
RELEASE bar.

// i have something like this (code commented out)
// FIND foo WHERE foo.t BEGINS "so" AND foo.b = FALSE.
// IF AVAILABLE foo THEN DISPLAY "dosomething".
// ELSE IF AMBIG foo THEN DISPLAY "dosomethingelse".

// and i want to replace it with something like this but here
// i cant ask if ambig but in the end i have to get the first
// record of foo
FOR EACH foo WHERE foo.t BEGINS "so",
EACH bar WHERE bar.pk2 = foo.fk AND bar.b2 = FALSE:
  DISPLAY foo.pk foo.t foo.fk.
END.

// so i decided to preselect
DO PRESELECT EACH bar NO-LOCK WHERE bar.b2 = FALSE,
EACH foo WHERE foo.fk = bar.pk2 AND foo.t BEGINS "so":
  // but I need to use find here
  FIND FIRST foo.
  DISPLAY foo.pk foo.t foo.fk.
END.

 

[Stefan]

Please put [ code ] [ /code ] tags around code.

 

[Stefan]

I'm not seeing any outer-join in your code, so I'm not sure what you are trying to do. With a query you can tell if something is ambiguous when first-of does not equal last-of:

DEFINE TEMP-TABLE foo NO-UNDO
    FIELD pk AS INTEGER
    FIELD t AS CHARACTER
    FIELD fk AS INTEGER
    // should be replaced with field b2 from bar
    FIELD b AS LOGICAL
    INDEX i t
    .

DEFINE TEMP-TABLE bar NO-UNDO
    FIELD pk2 AS INTEGER
    FIELD t2 AS CHARACTER
    FIELD b2 AS LOGICAL
    INDEX i2 pk2
    .

function createFoo returns logical ( pk as int, t as char, fk as int, b as logical ):

    define buffer foo for foo.
    
    create foo.
    assign
        foo.pk    = pk
        foo.t     = t
        foo.fk    = fk
        foo.b     = b
        .

end function.

function createBar returns logical ( pk as int, t as char, b as logical ):

    define buffer bar for bar.

    create bar.
    assign 
        bar.pk2 = pk
        bar.t2 = t
        bar.b2 = b
        .

end function.

createFoo( 1, 'so-one', 1, false ).
createFoo( 2, 'so-two', 1, false ).
createFoo( 3, 'so-three', 2, true ).

createBar( 1, 'so-four', false ).
createBar( 2, 'so-five', true ).

def var hq as handle no-undo.
def var cquery as char no-undo.

create query hq.
hq:add-buffer( buffer foo:handle ).
hq:add-buffer( buffer bar:handle ).

cquery    = 'for each foo where foo.t begins "so"'
        + ', each bar outer-join where bar.pk2    = foo.fk and bar.b2 = false '
        + ' break by foo.fk'.

hq:query-prepare( cquery ).
hq:query-open().

do while hq:get-next():

    message
        ( if hq:first-of( 1 ) = hq:last-of( 1 ) then 'unique' else 'ambiguous' )
        foo.pk 
        foo.t
        foo.fk
        .

end.

See ProgressAblDojo

 

[tamhas]

The other thing that screams to me from your code is transaction scope.

 

[Sebustone]

Hello Stefan! Thank you for your answer, this is a great solution. The thing I am trying to do is if the record is found, I pick it straightaway, if it is ambig or not found I open an other window. I already thought about a query, I’ll see if this will be the solution for me.
Hello tamhas, I will think about transaction scope, too! Did not hear about it yet so I will have to look it up first Thank you!