Apache OpenOffice Community Forum

I'm trying to order by three columns combined with Case When. Below I've just added one (in red) but I get the same error "Unexpected token in statement".
If I remove P."TimeOrdNo" it works.
If I just use Order By P."Date", P."TimeOrdNo" it also works fine.
Is this solvable?

SELECT * FROM "Photos" P WHERE P."ID_Source" = 18
ORDER BY
CASE
WHEN
(SELECT "Source" FROM "tbl_Filter" ) IS NULL
AND (SELECT "AddDesign" FROM "tbl_Filter" ) IS NULL
AND (SELECT "Date" FROM "tbl_Filter" ) IS NOT NULL
THEN
P."Date", P."TimeOrdNo"
WHEN
(SELECT "Source" FROM "tbl_Filter" ) IS NULL
AND (SELECT "AddDesign" FROM "tbl_Filter" ) IS NOT NULL
THEN
P."AddDesign"
ELSE
P."Designation"
END

Normalize your tables in order to eliminate all Null values.
https://docs.microsoft.com/en-us/office ... escription

Thanks, but not sure what the problem is. Why should Order by Case When (doesn't work) be different than just Order by (works).

hello AfTech54,

a CASE structure returns a single value i.e. not more than one value.
obviously cannot test but i think that if you duplicate the initial CASE structure in order to first select P."Date" before attempting to select P."TimeOrdNo" then you should be ok.

Edit: 2021 Mar 19 13:50 oops forgot to include 'order by'

THANKS again Chrisb!!

It explains the problem and I will try you proposal!!

I think it might work, but now it is complaining over the TimeOrdNo (Integer) or any numeric param. type. If I change it to Text it will work, but the order doesn't work correct (1, 10, 11 ... 2, 20, 21...) .
"Unresolved parameter type as output of Case when operand types are SMALLINT and LONGVARCHAR in statement..."

yes values returned by a case structure must be of one type i.e. text, integer, double.
please supply:
field name, field type
of all fields in the order by clause.

OK and thanks.

Hope this is what you're asking for.

Edit: oh no!! wrong field, too hasty. try this:

replace
THEN P."TimeOrdNo"
with
THEN cast(right('0000' || P."TimeOrdNo", 5) as varchar(5))

Thanks and perfect Crisb!!
I've just read about CAST and I think I understand it, But I don' understand how you use the RIGHT command.
Can you explain?
5 is it the length of TinyInt?

00001
000012
0000123
00001234
000012345

Thanks Villeroy!

I first thought || was OR, but I understand now that it's Concatenate

every character has a numeric value referred to as ASCII 'American Standard Code'.

numbers have ASCII codes between 48(0) & 57(9)
upper case letters have ASCII codes between 65(A) & 90(Z)
lower case letters have ASCII codes between 97(a) & 122(z)

A > a = false because ASCII values are compared and 65 is less than 97
A < a = true because 65 is less than 97

when sorting fields the database checks the field type & orders rows accordingly:
if our field type is text then numbers precede upper case letters & upper case letters precede lower case letters (1 precedes Z & Z precedes a)
if our field type is numeric then lessor values precede greater values (5 precedes 12)

if we cast numeric values as text then they get treated as text (12 precedes 5)
you can visualise this as ASCII 12 = (49 & 50), 5 = (53) so 12 will precede 5 because 49 is less than 53

as you yourself discovered after converting numbers to text: order doesn't work correct (1, 10, 11 ... 2, 20, 21...)
the solution is to precede values with zeros which using your example gives us exactly what we are looking for: '01', '02', '10', '11', '20', '21'

5 is it the length of TinyInt?
not true, 5 is the length of smallint, max value(32,767), two bytes.
that is why i precede the value with four zeros ('0000' || smallint).
if our smallint has a value of one we get the text string '00001'
if our smallint has a value of 32,767 we get the text string '000032767'
the function RIGHT is used to extract the rightmost five characters from our concatenated string ('00001' or '32767') & that's how we accurately sort numbers as text.

THANKS again Crisb!!
I'm familiar with AscII but I've never known that data is order by them. Great to know!!