SQL statement never gets executed

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

SQL statement never gets executed

Post by 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>
User avatar
jason112
Site Admin
Posts: 347
Joined: Tue Oct 26, 2004 5:35 pm

SQL statement never gets executed

Post by 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.
User avatar
John
Site Admin
Posts: 2622
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

SQL statement never gets executed

Post by 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.
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

SQL statement never gets executed

Post by 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.
User avatar
mark
Site Admin
Posts: 5519
Joined: Tue Apr 25, 2000 6:56 pm

SQL statement never gets executed

Post by mark »

Try tracesql and/or output in the second loop and sql.
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

SQL statement never gets executed

Post by 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
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

SQL statement never gets executed

Post by 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.
User avatar
mark
Site Admin
Posts: 5519
Joined: Tue Apr 25, 2000 6:56 pm

SQL statement never gets executed

Post by mark »

No, linear is not forced.
User avatar
John
Site Admin
Posts: 2622
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

SQL statement never gets executed

Post by 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.
John Turnbull
Thunderstone Software
Post Reply