How do I make Progress interpret my variable name as a field name?

C

clcoop

New Member
I am trying to change the FIELD NAME during a FOR EACH, and it will not work. When I use a variable for a field name, the field name the variable supplies is NOT looked up. Progress interprets the variable name as a character varable. How do I make Progress interpret my variable name as a field name?

Sample code:

DO i = 1 TO 2:
IF i = 1 THEN cVar = "Int1"
IF i = 2 THEN cVar = "Int2"

FOR EACH acct where
acct.cVar > 0:

DISPLAY Int1 Int2.
END.

END.

This doesn't work. Instead of using the variable, Progress is taking it literally and looking up the following:


FOR EACH acct where
"Int1" > 0:
END.

I tried Pre Processor statements, but they will not work with an IF statement.

Thank you for your help.
 
You cannot use a variable as a field name in "normal" Progress queries (maybe with some includes you could, but I don't go down that road anymore).

You can define a query (lookup DEFINE QUERY in your online help and/or edocs) and depending on your condition open the query with a different query string.

In version 9 and up you can also make it truely dynamic by doing something like this (typed straight into the forum so it may need some tweaking and syntax checking):
Code: 
DEF VAR hQuery AS HANDLE NO-UNDO.
DEF VAR hBuffer AS HANDLE NO-UNDO.
DEF VAR hField1 AS HANDLE NO-UNDO.
DEF VAR hField2 AS HANDLE NO-UNDO.
DEF VAR cVar AS CHAR NO-UNDO.
/* You could change the fields below to input parameters - woohoo */
DEF VAR cTable AS CHAR NO-UNDO INIT "acct":U.
DEF VAR cField1 AS CHAR NO-UNDO INIT "Int1":U.
DEF VAR cField2 AS CHAR NO-UNDO INIT "Int2":U.

CREATE BUFFER hBuffer FOR TABLE cTable.
ASSIGN hField1 = hBuffer:BUFFER-FIELD(cField1)
       hField2 = hBuffer:BUFFER-FIELD(cField2).
CREATE QUERY hQuery.
hQuery:ADD-BUFFER(hBuffer).

DO i = 1 TO 2:
  cVar = IF i = 1 THEN cField1 ELSE cField2.

  hQuery:QUERY-PREPARE("FOR EACH ":U + cTable + " NO-LOCK WHERE ":U +  cVar + " > 0":U). 
  hQuery:QUERY-OPEN.
  hQuery:GET-FIRST(NO-LOCK).
  DO WHILE NOT hQuery:QUERY-OFF-END:
    DISPLAY hField1:BUFFER-VALUE(0) hField2:BUFFER-VALUE(0).
    hQuery:GET-NEXT(NO-LOCK).
  END.
  hQuery:QUERY-CLOSE.
END.
/* Very important to do this, otherwise you create yourself a nice memory leak... */
DELETE OBJECT hQuery.
DELETE OBJECT hBuffer.
A bit of code to do a simple thing, but it should work. Depending on your Progress version (as said, minimum is version 9) you can make the code a little more compact by using multi-level attributes. You could also throw in a couple of functions and/or internal procedures and/or include files if you have to do this more often and save youself some typing.

If you have version 9.1D (or up) you can also do something else; which is using dynamic FIND statements on a dynamic buffer. If you have to do a simple for each that would save you some typing (try looking up the FIND-FIRST method in the online help).

Let me know how you go with this. If you have more questions, feel free to ask here or send me a pm.

HTH
 
Progress wont do it like that, in Progress you need to define the field names at design time, at run time you can select which fields you will alter.
At design time have something like;

DO i = 1 TO 2:

/* */
DO:
IF i = 1 THEN flag = "Int1"
IF i = 2 THEN flag = "Int2"
END.

case flag:
WHEN Int1
DO:
FOR EACH acc WHERE ....
END.

WHEN Int2
DO:
FOR EACH acc WHERE ...
END.



end case.
 
Hi,

Also..

&SCOPED-DEFINE FIELD-1 Int1
&SCOPED-DEFINE FIELD-2 Int2

DO i = 1 TO 2:

FOR EACH acct where
(if i = 1 then {&FIELD=1} else {&FIELD-2}) > 0 :

DISPLAY Int1 Int2.
END.

END.
 
Paul, Thank you very much for your help. The Dynamic Query worked great. However, now once I have retrieved records from the query, I want to modify them. I am having trouble doing that. A for each would be simple:



FOR EACH clsl:

ASSIGN int120 = int120 + 1.

END.



With a dynamic query,

DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.

DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.

DEFINE VARIABLE cTable AS CHARACTER NO-UNDO INITIAL "clsl":U.

DEFINE VARIABLE cInt120 AS CHARACTER NO-UNDO INITIAL "int120":U.

DEFINE VARIABLE hField1 AS HANDLE NO-UNDO.



CREATE BUFFER hBuffer FOR TABLE cTable.

ASSIGN hField1 = hBuffer:BUFFER-FIELD(cint120).

CREATE QUERY hQuery.

hQuery:ADD-BUFFER(hBuffer).



hQuery:QUERY-PREPARE("FOR EACH ":U + cTable + " NO-LOCK").

hQuery:QUERY-OPEN.

hQuery:GET-FIRST(NO-LOCK).

DO WHILE NOT hQuery:QUERY-OFF-END:

ASSIGN hField1:BUFFER-VALUE(0) = hField1:BUFFER-VALUE + 1.

hQuery:GET-NEXT(NO-LOCK).

END.

hQuery:QUERY-CLOSE.

Thanks for your help.
 
If you try and think outside the square (or the box) you will find that you can create some internal procedures and/or functions that can help you out with all that stuff. Try and create some generic procedures and functions that you can re-use and that limit the amount of code you have to type in time and time again. If you can do that you are well on your way...


However, when you wish to update a record you will have to change the NO-LOCK throughout the code into EXCLUSIVE-LOCK. And while we are at it, don't forget to limit your transaction....
 
You must log in or register to reply here.
Back
Top