Hi,
i have a table with some text fields filled with a data like100,23235,12500200the same fields somethimes contains the
valueslike100x100x25125x125x50200x80x90and so on.
the client requires that rows are sorted in ascending order
for the case there are a float values, i do:SELECT ... ORDER BY float4(field)
for the case there a text values, i do:SELECT ... ORDER BY int2(substring(field from 1 for position('x' in
field)-1));soi can sort them ascendingly at least by the first integer (before'x' char). otherwise (simply "ORDER BY
field")they were sorted as textvalues - 100x100x30, 10x10x10, 400x400x30, 40x40x20, ...now it sorted as i need -
10x10x10,40x40x20, 100x100x30, 400x400x30 ...
it's almost fine, but...now i need to combine that 2 cases, so i try (the field called m1):SELECT ... ORDER BY (CASE
WHENposition('x' in m1)>1 THEN int2(substring(m1 from 1 for position('x' in m1)-1)) ELSE float4(m1) END)
i never used CASE WHEN ... THEN ... ELSE ... END construct before,& assume the above is errorneus by default.
--:)--
Best regards, Sandis