Qualifying tables

[Chemi]

Hello all.

I am wondering if it is possible to qualify tables in a OpenEdge query using FOR EACH.

For example, I have this code in SQL:

SELECT COUNT(*)
FROM table a, table b
WHERE a.field1 = 1 AND
      b.field1 = 2 AND
      a.field2 = b.field2.

Is it possible to write it in OpenEdge qualifying tables?. If not, how should it be done?

Thanks in advance.

Regards.
- José Miguel Giménez

 

[TomBascom]

Do you mean something like:

define variable i as integer no-undo.

for each table a no-lock, each table b no-lock
  WHERE a.field1 = 1 AND
      b.field1 = 2 AND
      a.field2 = b.field2:

  i = i + 1.

end.

display i.

 

[KnutHandsome]

If you mean JOIN tables, then the above post answers your question.

If you mean QUALIFY as in use the table name even if column names are unambiguous, the above post answers that question also; you can however not qualify if you wish (when column names are unique across the tables), but this is bad practice, as I suspect you know.



This bit I suspect is unrelated to your request, but on the subject of table names...

You can also use DEFINE BUFFER as a kind of ALIAS/synonym (although its not the same as a synonym) if thats what you're after, though I tend only to use it when I want to scope the record to the local procedure or I need multiple records from the same table in memory at the same time (buffers!).

eg. DEFINE BUFFER bAccount FOR Account.

<find Account record>
CREATE bAccount.
BUFFER-COPY Account to bAccount.
<etc.>

 

[Chemi]

Hello.

I think my explanation was a bit confusing, when I wrote "table" I was meaning a table name, not the reserved word table.

I'll rewrite it to make more clear:

SELECT COUNT(*)
FROM table_name a, table_name b
WHERE a.field1 = 1 AND
      b.field1 = 2 AND
      a.field2 = b.field2.

I think BUFFER is what I was searching for. I have tried this code and looks like it works:

DEFINE BUFFER table_buffer FOR table_name.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
 
FOR EACH table_name NO-LOCK,
    EACH table_buffer NO-LOCK
    WHERE table_name.field1= 1 AND
          table_buffer.field1 = 2 AND
          table_name.field2= table_buffer.field2:
    i = i + 1.
END.
 
DISPLAY i.

Thank you everyone.

Regards.
- José Miguel Giménez