Re: SQL: Inconsistent "order by"

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
IBM clarified this by saying that when you try to use a parameter marker
it tried to sort by the value of the marker, not the column number that it
represented.  They said order by 2 was like saying order by 'M'.  Valid,
but totally meaningless.  Why anyone would want to sort by a hard value is
beyond me.

I like my coworkers work around versus the prepared statement that
everyone seems enamored of:

...
     C+    SELECT
     C+      LPROD, LLOC, LWHS, ICMRP, IITYP, IDESC, WMFAC,
     C+      TotPhyQty, OnHand, TotStdCst, ILIFlag,
     C+      VarQty, OnHandValue, PhyAmt, VarVal,
     C+
     C* SORT1-SORT4 are calculated fields to allow different sort
     C* methods with one statement depending on the host variable RptTyp.
     C*
     C* RptTyp=1 sorts by IITYP, ICMRP, LWHS, LLOC, LPROD
     C* RptTyp=2 sorts by IITYP, ICMRP, VarVal, LPROD
     C* RptTyp=3 sorts by IITYP, ICMRP, VarQty, LPROD
     C* RptTyp=4 sorts by IITYP, ICMRP, LPROD, LWHS, LLOC
     C*
     C+      CASE
     C+        WHEN :RptTyp=1 THEN LWHS
     C+        WHEN :RptTyp=4 THEN LPROD
     C+        ELSE ' '
     C+      END AS SORT1,
     C+      CASE
     C+        WHEN :RptTyp=2 THEN VarVal
     C+        WHEN :RptTyp=3 THEN VarQty
     C+        ELSE 0
     C+      END AS SORT2,
     C+      CASE
     C+        WHEN :RptTyp=1 THEN LLOC
     C+        WHEN :RptTyp=2 THEN LPROD
     C+        WHEN :RptTyp=3 THEN LPROD
     C+        WHEN :RptTyp=4 THEN LWHS
     C+      END AS SORT3,
     C+      CASE
     C+        WHEN :RptTyp=1 THEN LPROD
     C+        WHEN :RptTyp=4 THEN LLOC
     C+        ELSE ' '
     C+      END AS SORT4
     C+    FROM T2
     C+
     C+    WHERE VarQty<>0
     C+      OR PhyAmt<>0
     C+
     C+    ORDER BY IITYP, ICMRP, SORT1, SORT2, SORT3, SORT4

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




"Carel Teijgeler" <coteijgeler@chello.nl>
Sent by: midrange-l-admin@midrange.com
11/20/2002 12:36 PM
Please respond to midrange-l

        To:     midrange-l@midrange.com
        cc:
        Fax to:
        Subject:        Re: SQL:  Inconsistent "order by"


Does this also handle parameter markers, which was the origin of your
problems, IIRC?

Thus ORDER BY ? and passing a field name to the marker.

Just curious.

Regards,
Carel Teijgeler.

*********** REPLY SEPARATOR  ***********

On 19-11-02 at 15:00 rob@dekko.com wrote:

<SNIP>
>After many discussions with development, I think we have a solution to
>the problem you were seeing.  According to development and what is
>documented in the SQL Reference, the order by clause will accept a
>a column name, an integer or a sort-key expression.  I've confirmed with
>development that the integer must be in the form of a number and cannot
>be a host variable.  When the term integer-expression is used,
>then a number or a host variable or another numeric expression(X +Y)
>can be used as a parameter.
>The net is what you are seeing returned by your program is
>working as designed.
<MORE CUTTING>
>Additional Information:
>So what you are saying is that
>Order by :myvar
>is not an error.  It will not create a invalid sql state SQLSTT, nor an
>invalid SQL code SQLCOD.  However it is just ignored and any old sort
>order is used?
</SNIP>