Обсуждение: A Query that works standalone but not in a f(x)
Within the function I have:
for darec in select * from purchbt where addr not like ''%STE%STE%'' and addr not like ''%STE%STE%STE%'' and (addr similar to ''%STE[A-Z]*[0-9]+'' or addr similar to ''%STE[A-Z]*[0-9]+[A-Z]'' or addr similar to ''%STE[A-Z]*[0-9]+[A-Z][A-Z]'') loop
and it finds no records.
Outside the function, replace '' with ', I DO get the results I expect.
Back and forth, it's reliable.
I tried putting the COUNT(*) into a var and it finds 0 w/in the function and 59K recs as a standalone query.
Any ideas?
Thanks!
for darec in select * from purchbt where addr not like ''%STE%STE%'' and addr not like ''%STE%STE%STE%'' and (addr similar to ''%STE[A-Z]*[0-9]+'' or addr similar to ''%STE[A-Z]*[0-9]+[A-Z]'' or addr similar to ''%STE[A-Z]*[0-9]+[A-Z][A-Z]'') loop
and it finds no records.
Outside the function, replace '' with ', I DO get the results I expect.
Back and forth, it's reliable.
I tried putting the COUNT(*) into a var and it finds 0 w/in the function and 59K recs as a standalone query.
Any ideas?
Thanks!
-- Ralph _________________________
Ralph Smith <rsmith@10kinfo.com> writes:
> Within the function I have:
> for darec in select * from purchbt where addr not like ''%STE%STE%'' and
> addr not like ''%STE%STE%STE%'' and (addr similar to
> ''%STE[A-Z]*[0-9]+'' or addr similar to ''%STE[A-Z]*[0-9]+[A-Z]'' or
> addr similar to ''%STE[A-Z]*[0-9]+[A-Z][A-Z]'') loop
> and it finds no records.
> Outside the function, replace '' with ', I DO get the results I expect.
Maybe "addr" is the name of a local variable inside the function?
Pre-9.0, that sort of ambiguity can bite you pretty easily.
regards, tom lane
Once again, youreka, or EUREKA Tom!
There was no indication that the problem was inside the loop, just the query wouldn't work.
In fact it ran perfectly fine yesterday!...? (Though I am going to go back and check it now.)
Thanks. I know better but... You can bet I won't make that one again.
Ralph
========================================
Tom Lane wrote:
There was no indication that the problem was inside the loop, just the query wouldn't work.
In fact it ran perfectly fine yesterday!...? (Though I am going to go back and check it now.)
Thanks. I know better but... You can bet I won't make that one again.
Ralph
========================================
Tom Lane wrote:
Ralph Smith <rsmith@10kinfo.com> writes:Within the function I have:for darec in select * from purchbt where addr not like ''%STE%STE%'' and addr not like ''%STE%STE%STE%'' and (addr similar to ''%STE[A-Z]*[0-9]+'' or addr similar to ''%STE[A-Z]*[0-9]+[A-Z]'' or addr similar to ''%STE[A-Z]*[0-9]+[A-Z][A-Z]'') loopand it finds no records. Outside the function, replace '' with ', I DO get the results I expect.Maybe "addr" is the name of a local variable inside the function? Pre-9.0, that sort of ambiguity can bite you pretty easily. regards, tom lane
-- Ralph _________________________