a tip on creating a rudimentary dynamic sql in a sp
I love
SP for variety of task, despite its limitations.
But recently I found out, by trial and error, even
in SP it is possible to have some sort of rudimentary
dynamic sql. And I am sharing this with others.
we have one stored procedure which returns a result set
to the client program sorted by price. Recently there was
a change request, where they wanted it to be sorted on
price or airlines. The sort criteria would be sent to the
procedure as a parameter p_sort.
a typical sql in the sp
foreach
select price,airline,depart_date
into w_price,w_airline,w_depart_date
from table_a
order by 1return .... with resume ;
end foreach ;
The problem is that order by clause has to be hardcoded, either
by column number or by name. I can't refer order by clause by
a variable, as in ORDER BY P_SORT. Informix will take P_SORT
as a column name and spew out error that column P_SORT does
not exist.
Then I used this method and much to my surprise it worked.
foreach
select
case when p_sort = 1
then price || '$$'
else
airline || price
end priceairline,
depart_date
into ..
from table_a
order by 1
end foreach ;
In the above case, based on the order criteria in the variable p_sort,
I am dynamically changing the field order in the select statement.
All I had to do is to ensure that the size of the field remains
same in either case. That's why I am adding '$$' in the first case
since airline code is char(2). Works like a charm. I got the result
sorted in correct order.
Isn't it strange that a case statement can refer to a SP variable,
but not order by clause.
Ravi
we are
using that for older applications. Personally I am not much
statisfied with it. Too much of coding to be done. In fact a pain.
----- Original Message -----
From: "Marco Greco" <marco@4glworks.com>
To: "rkusenet" <rkusenet@sympatico.ca>
Cc: <ids@iiug.org>
Sent: Friday, May 16, 2003 11:56
Subject: Re: a tip on creating a rudimentary dynamic sql in a sp [1162]
> hmmmm, you might be interested in the dynamic sql bladelet by Paul Brown.
> If you are using a 9.x engine, that is...
>
> rkusenet wrote:
> > I love SP for variety of task, despite its limitations.
> >
> > But recently I found out, by trial and error, even
> > in SP it is possible to have some sort of rudimentary
> > dynamic sql. And I am sharing this with others.
> >
> > we have one stored procedure which returns a result set
> > to the client program sorted by price. Recently there was
> > a change request, where they wanted it to be sorted on
> > price or airlines. The sort criteria would be sent to the
> > procedure as a parameter p_sort.
> > a typical sql in the sp
> > foreach
> > select price,airline,depart_date
> > into w_price,w_airline,w_depart_date
> > from table_a
> > order by 1> > return .... with resume ;
> > end foreach ;
> >
> > The problem is that order by clause has to be hardcoded, either
> > by column number or by name. I can't refer order by clause by
> > a variable, as in ORDER BY P_SORT. Informix will take P_SORT
> > as a column name and spew out error that column P_SORT does
> > not exist.
> >
> > Then I used this method and much to my surprise it worked.
> >
> > foreach
> > select
> > case when p_sort = 1
> > then price || '$$'
> > else
> > airline || price
> > end priceairline,
> > depart_date
> > into ..
> > from table_a
> > order by 1
> > end foreach ;
> >
> > In the above case, based on the order criteria in the variable p_sort,
> > I am dynamically changing the field order in the select statement.
> > All I had to do is to ensure that the size of the field remains
> > same in either case. That's why I am adding '$$' in the first case
> > since airline code is char(2). Works like a charm. I got the result
> > sorted in correct order.
> >
> > Isn't it strange that a case statement can refer to a SP variable,
> > but not order by clause.
> >
> >
> > Ravi
> >
> >
> >
> > .
> >
>
>
> --
> Ciao,
> Marco
>
______________________________________________________________________________
> Marco Greco /UK /IBM Standard disclaimers apply!
>
> Informix faq http://www.iiug.org/techinfo/faq/informix.htm
> 4glworks http://www.4glworks.com
> Informix on Linux http://www.4glworks.com/ifmxlinux.htm
>
>