At 08:21 AM 31/05/2007, you wrote:

>Helen
>This is a fuller version of the SP with a lot of detail omitted for
>clarity. I'm now worried based on what you are saying, but I can
>confirm that it does compile with no errors. However, the data
>returned is not in the correct order. When Code order is selected - an
>integer field - the data is returned as follows, even if I
>cast(Supp_Code as integer):
> not
>1 1
>10 2
>11 3
>2 4
>20 5
>So Firebird is converting this to a character field because - as you
>are saying - this is what is expected when its compiled. So it looks
>like I can't improve the readability and am stuck with if... else...
>Even though I had hoped to be able to do what I wanted, at least I
>have a reasoned answer and will reatin the existing SPs, so thanks for
>that.
>
>I run this SP via a select from within Delphi and others also use Php,
>which is why I want the "order by" clause inside the SP, not in the
>applications.
> qry3.Sql.Clear;
> qry3.Sql.Add('Select *
> GET_HAULIER_ADDRESS(:locCode,:DisplayBy,:IndexBy)');
> qry3.Params.ParamValues['locCode']:=locCode;
> qry3.Params.ParamValues['DisplayBy']:=DisplayBy;
> qry3.Params.ParamValues['IndexBy']:=IndexBy;
> qry3.Open;
>
>
>CREATE PROCEDURE GET_HAULIER_ADDRESS (
> loccode integer,
> displayby integer,
> indexby integer)
>returns (
> code integer,
> name char(40)
>as
>begin
> if (:DISPLAYBY=0) then /* Display Individual */
> begin
> for select Haul_Code,Haul_Name
> from Haulier
> where Haul_Code=:locCode
> order by case when (:indexby)=0 then Haul_Name
> else Haul_Code
> end
> into :Code,:Name
> do
> suspend;
> end
> else if (:DISPLAYBY=1) then /* Display All */
> begin
> for select Haul_Code,Haul_Name
> from Haulier
> order by case when (:indexby)=0 then Haul_Name
> else Haul_Code
> end
> into :Code,:Name
> do
> suspend;
> end
>end

OK, ORDER BY [expression] is valid SQL so, as used here, it won't
cause a problem for the compiler. Logically the second block is
being coerced to an alphanumeric ordering because that is determined
by the first block. With the data you are using, you can't switch
that - it's not possible to coerce the haulier name into any kind of
numeric ordering! ;-)

Why don't you write a view that delivers a set that can work either
way for your SP? You would need to define a derived field in the
view that massages the value of the haulier code into a string with
left zero-padding, so that the alphanumeric order would mimic the
numeric order. The IB_UDF function LPAD() can do this for you. Then
your second block can specify ordering on this derived value. (Or,
if you don't like the idea of creating this view, you could try to do
this massaging in the SELECT statement directly).

./heLen