如何在for EACH查询中检查同一元素的多个子字符串

Question

我试图读取字符串的一部分，但编译器只读取第一个子字符串。我的代码如下：

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.

表和变量是以前创建的。谢谢你的阅读，如果我没有说清楚，我很抱歉。

Answer 1

您不应该试图用一个大的静态WHERE子句来实现这一点。相反，创建一个特定于运行时可用数据的动态查询。像这样：

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.

(我没有你的数据库，所以我以"体育"数据库为例。(

这样做的目的是，与其试图在编译时构建一个处理所有可以想象的可能性的WHERE子句，不如将其推迟到运行时，并使用"；wc"；字符串变量仅包含您实际需要并在运行时可用的选择条件。

因此，与其说那些ANDed IF语句，不如说更像：

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 ".

等等，直到你为这种情况建立了正确的位置条款，没有任何额外的内容。

(动态查询可以包含联接，但在您已经展示的情况下，只使用嵌套查询可能更简单，将"FOR EACH estrutura"作为外部查询