SQL statement never gets executed

[barry.marcus]

The following code looks fine to me, and yet the second SQL loop never executes (i.e., the <SQL ROW $query> within the LOOP). It just skips right over it. Needless to say, the innermost SQL (i.e., the UPDATE statement) never runs either. Any help would be appreciated.

Thanks

---------------------------

<$tabName="PATN_TEST">
<$exprToFind="&apos;" ">" "<" "&" "&#xf7;" """>
<$replacement="'" ">" "<" "&" "/" '"'>
<strfmt "SELECT DISTINCT PATN_ISD FROM %s WHERE PATN_ISD > '20051231' ORDER BY PATN_ISD" $tabName>
<$outerQuery=$ret>
<$ISD=>
<SQL ROW $outerQuery>
$PATN_ISD<br>
<$ISD=$ISD $PATN_ISD>
</SQL>
<LOOP $ISD>
<strfmt "SELECT PATN_WKU, ABST_PAx FROM %s WHERE PATN_ISD LIKE %s" $tabName $ISD>
<$query=$ret>
<SQL ROW $query>
<sandr $exprToFind $replacement $ABST_PAx>
<strfmt "UPDATE %s SET ABST_PAx = %s WHERE PATN_WKU = %s" $tabName $ret $PATN_WKU>
<$uQuery=$ret>
<SQL NOVARS $uQuery></SQL>
</SQL>
</LOOP>

[jason112]

I'm guessing the first sql statement isn't filling in $ISD like you expect. Add some output inside of the first sql loop to see if it's actually looping at all.

[John]

You should not in general be using the LIKE %s construct, as it may be missing the quotes, instead try:

<strfmt "SELECT PATN_WKU, ABST_PAx FROM %s WHERE PATN_ISD LIKE $$ISD" $tabName>

and

<strfmt "UPDATE %s SET ABST_PAx = $$ret WHERE PATN_WKU = $$PATN_WKU" $tabName>

or:

<SQL ROW "SELECT PATN_WKU, ABST_PAx FROM " $tabName " WHERE PATN_ISD LIKE $ISD">

and then check vortex.log for any errors.

[barry.marcus]

There *is* output inside the first loop (i.e., the line: $PATN_ISD<br>). Yes, it is looping, and I see the expected five values for PATN_ISD displayed.

Made the changes suggested by John. Same thing. The $ISD loop is running, but the SQL loop inside is not.

[mark]

Try tracesql and/or output in the second loop and sql.

[barry.marcus]

Putting output inside the inner loop (i.e., inside the <SQL ROW $query> loop) displays nothing at all. The code never drops into the inner loop.

[barry.marcus]

OK. I figured it out thanks to everyone's help! I looked in the log and saw the message "Query <blah blah> would require linear search." (Or something like that.) Since I am testing the code on a very small test table (<100 rows) I never thought to build an index. Apparently that's what Texis was complaining about. I added an <apicp allinear on> line to the code before the SQL call and voila! It seems to work.

Our production table, which is *MUCH* bigger (> 10e6 rows) is certainly indexed.

Questions: Will this script still require the <apicp allinera on> in that case? Does it *force* a linear scan even if an index is available?

Thanks to everyone for their help.

[mark]

No, linear is not forced.

[John]

The script should not require allinear on if there is an index. It would make sense to create the index on the test table to verify it still works as intended. Allinear on will allow it to go linear, but does not force it.