openedge - How can I check multiple substrings for the same element in a FOR EACH query?

Question

I'm trying to read parts of a string but the compilator is only reading the first substring. My code is as follows:

FOR EACH estrutura NO-LOCK,
EACH item
WHERE estrutura.it-codigo = item.it-codigo
AND IF pc-ge <> 0           THEN item.ge-codigo = pc-ge ELSE item.ge-codigo > 0
AND IF pc-familia <> ""     THEN SUBSTRING(estrutura.it-codigo, 1, 2, "CHARACTER") = pc-familia ELSE SUBSTRING(estrutura.it-codigo, 1, 2, "CHARACTER") BEGINS pc-familia
AND IF pc-norma <> ""       THEN SUBSTRING(estrutura.it-codigo, 3, 1, "CHARACTER") = pc-norma ELSE SUBSTRING(estrutura.it-codigo, 3, 1, "CHARACTER") BEGINS pc-norma
AND IF pc-classe <> ""      THEN SUBSTRING(estrutura.it-codigo, 4, 2, "CHARACTER") = pc-classe ELSE SUBSTRING(estrutura.it-codigo, 4, 2, "CHARACTER") BEGINS pc-classe
AND IF pc-bitola <> ""      THEN SUBSTRING(estrutura.it-codigo, 6, 2, "CHARACTER") = pc-bitola ELSE SUBSTRING(estrutura.it-codigo, 6, 2, "CHARACTER") BEGINS pc-bitola
AND IF pc-comprim <> ""     THEN SUBSTRING(estrutura.it-codigo, 8, 3, "CHARACTER") = pc-comprim ELSE SUBSTRING(estrutura.it-codigo, 8, 3, "CHARACTER") BEGINS pc-comprim
AND IF pc-rosca <> ""       THEN SUBSTRING(estrutura.it-codigo, 11, 2, "CHARACTER") = pc-rosca ELSE SUBSTRING(estrutura.it-codigo, 11, 2, "CHARACTER") BEGINS pc-rosca
AND IF pc-acab <> ""        THEN SUBSTRING(estrutura.it-codigo, 13, 2, "CHARACTER") = pc-acab ELSE SUBSTRING(estrutura.it-codigo, 13, 2, "CHARACTER") BEGINS pc-acab
AND IF pc-seq <> ""         THEN SUBSTRING(estrutura.it-codigo, 15, 2, "CHARACTER") = pc-seq ELSE SUBSTRING(estrutura.it-codigo, 15, 2, "CHARACTER") BEGINS pc-seq NO-LOCK
BY estrutura.it-codigo:

    CREATE tt-estrutura.
    ASSIGN tt-estrutura.nivel-00 = estrutura.it-codigo
    .


END.

The table and variables were created previously. Thanks for reading and sorry if I haven't made myself clear.

Answer 1

You should not attempt to do this with one big static WHERE clause. Instead create a dynamic query specific to the data which is available at run time. Like this:

define variable wc as character no-undo.
define variable b  as handle    no-undo.
define variable q  as handle    no-undo.

wc = "for each customer no-lock where discount > 0".  /* this is the magic part */

create buffer b for table "customer".
create query  q.

q:set-buffers( b ).
q:query-prepare( wc ).
q:query-open.

do while q:get-next( no-lock ):

  display b:buffer-field( "name" ):buffer-value format "x(30)" with 10 down.
  down.

end.

q:query-close.

(I don't have your database so I'm making an example with the "sports" database.)

The point of this is that instead of trying to build a WHERE clause that handles every imaginable possibility at compile time, defer that to run time and use the "wc" string variable to ONLY contain the selection criteria that you actually need and have available at runtime.

So instead of those ANDed IF statements you would say something more like:

wc = "for each item no-lock where ".

if pc-ge <> 0 then
  wc = wc + "item.ge-codigo = pc-ge ".
 else
  wc = wc + "item.ge-codigo = pc-ge ".

and so on until you have built just the right where clause for the circumstances with nothing extra.

(Dynamic queries can contain joins - but in the case you have shown it is probably simpler to just use nested queries leaving "FOR EACH estrutura" as the outer query.