• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

printf with "AS" using same column name order by is alpha

Started by cj, February 14, 2022, 10:54:17 AM

Previous topic - Next topic

cj

This example shows 3-ways to get price column to order by numerically
if printf with "AS" using the "price" column name is used.
1. Use no AS and ignore the long column name
2. Specify parts.price in the order by
3. Use a different name with "AS" than the price column

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN AS LONG
 slOpen "junk.db3"
 slexe  "drop table if exists parts"
 slexe  "create table if not exists parts(price integer)"
 slexe  "insert into parts values(1995),(1300),(4995),(101),(703)"

 'if AS is used with same name as column with printf the order by will be alpha
 rs "select printf('%.2f',price*.01) AS price from parts order by price"

 'these order numerically
 rs "select printf('%.2f',price*.01) from parts order by price"                'no AS
 rs "select printf('%.2f',price*.01) AS price from parts order by parts.price" 'use table.column
 rs "select printf('%.2f',price*.01) AS XYZ from parts order by price"         'AS different
 ? gs
END FUNCTION

FUNCTION rs(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 IF ISFALSE(slSelAry(sql,sArray(),"Q9 E2")) THEN
  gs+= JOIN$(sArray(),$CR) + $CR + $CR
 ELSE
  gs = "error:" + $CR + sql + $CR + $CR
 END IF
END FUNCTION