×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
Hi Rob,
stmt='Select cmpny, cmpnam, cmpost ' +
'from rco ' +
'where cmpny between :lowCmpny and :highCmpny ' +
'and cmpnam between :lowCmpnam and :highCmpnam ' +
'and cmpost between :lowCmpost and :highCmpost ' +
'order by ?';
No, this isn't valid. Since you are building the SQL statement
on-the-fly, you can't use :lowCmpny or any other variable that starts
with a colon. That code is normally handled at compile-time by the SQL
precompiler -- and nothing handled at compile-time will work in this
case, because you're building the SQL statement using string
concatenation at run-time. You could, however, replace those with
parameter markers (question marks) and fill them in that way.
You also can't use a parameter marker (the question mark) for "order
by". Parameter markers can only replace variable/column values. they
can't replace code that's used to develop the query plan.
in any case, your statement is a bad example of dynamic SQL because it
could be done (actually easier) as a static statement. (Notice how I
handle the order by:)
exec SQL declare C1 cursor for
Select cmpny, cmpnam, cmpost
from rco
where cmpny between :lowCmpny and :highCmpny
and cmpnam between :lowCmpnam and :highCmpnam
and cmpost between :lowCmpost and :highCmpost
order by case
when :sortby = '1' then cmpny
when :sortby = '2' then cmpnam
when :sortby = '3' then cmpost
end
midrange.com
