CODEWITHSUNDEEP
openedgeprogress-4gl
Diego
Diego

Reputation: 57

Is there a way of using an IF statement inside a FOR EACH in PROGRESS-4GL?

I'm transforming some SQL codes into Progress 4GL. The specific code I'm writing right now has a lot of possible variables insertions, for example, there are 3 checkboxes that can be selected to be or not, and each selection adds a "AND ind_sit_doc IN", etc.

What I'd like to do is something like this:

FOR EACH doc-fiscal USE-INDEX dctfsc-09
        WHERE doc-fiscal.dt-docto >= pd-data-1
        AND doc-fiscal.dt-docto <= pd-data-2
        AND doc-fiscal.cod-observa <> 4
        AND doc-fiscal.tipo-nat <> 3
        AND doc-fiscal.cd-situacao <> 06
        AND doc-fiscal.cd-situacao <> 22
        AND (IF pc-ind-sit-doc-1 = 'on' THEN: doc-fiscal.ind-sit-doc=1) NO-LOCK,
            EACH natur-oper USE-INDEX natureza
            WHERE doc-fiscal.nat-operacao = natur-oper.nat-operacao NO-LOCK:

The if part would only be read if the variable was in a certain way. Is it possible?

Upvotes: 0

Views: 1386

Answers (2)

Tom Bascom
Tom Bascom

Reputation: 14020

Yes, you can do that (more or less as nwahmaet showed).

But it is a very, very bad idea in a non-trivial query. You are very likely going to force a table-scan to occur and you may very well send all of the data to the client for selection. That's going to be really painful. You would be much better off moving the IF THEN ELSE outside of the WHERE clause and implementing two distinct FOR EACH statements.

If your concern with that is that you would be duplicating the body of the FOR EACH then you could use queries. Something like this:

define query q for customer.

if someCondition = true then
  open query q for each customer no-lock where state = "ma".
 else
  open query q for each customer no-lock where state = "nh".

get first q.

do while available customer:

  display custNum state.
  get next q.

end.

This is going to be much more efficient for anything other than a tiny little table.

You can also go fully dynamic and just build the needed WHERE clause as a string - but that involves using handles and is more complicated. But if that sounds attractive lookup QUERY-PREPARE in the documentation.

Upvotes: 2

nwahmaet
nwahmaet

Reputation: 3909

You can add an IF statement in a FOR EACH. You must have the complete IF ... THEN ... ELSE though.

For example:

FOR EACH customer WHERE (IF discount > 50 THEN state = 'ma' ELSE state = ''):
  DISPL name state discount.
END.

That said, that condition will not be used for index selection and will only be applied on the client (if you're using networked db connections this is bad).

Upvotes: 2

Related Questions