[Solved] ORDER BY (multiple conditions) with CASE WHEN

Discuss the database features
Post Reply
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

[Solved] ORDER BY (multiple conditions) with CASE WHEN

Post by AfTech54 »

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
Last edited by MrProgrammer on Fri Mar 19, 2021 7:13 pm, edited 4 times in total.
Reason: Tagged ✓ [Solved]
Ooo v4.1.9, Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Order by with multiple conditions combined with Case Whe

Post by Villeroy »

Normalize your tables in order to eliminate all Null values.
https://docs.microsoft.com/en-us/office ... escription
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by with multiple conditions combined with Case Whe

Post by AfTech54 »

Thanks, but not sure what the problem is. Why should Order by Case When (doesn't work) be different than just Order by (works).
Relations.jpg
Ooo v4.1.9, Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Order by with multiple conditions combined with Case Whe

Post by chrisb »

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' 

Code: Select all

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"
END
,
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."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
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by with multiple conditions combined with Case Whe

Post by AfTech54 »

THANKS again Chrisb!!

It explains the problem and I will try you proposal!!
Ooo v4.1.9, Windows 10
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: [Solved]Order by with multiple conditions combined Case

Post by AfTech54 »

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..."
Ooo v4.1.9, Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Order by with multiple conditions combined Case When

Post by chrisb »

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.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by with multiple conditions combined Case When

Post by AfTech54 »

OK and thanks.

Hope this is what you're asking for.
Photo_ Table Design.jpg
Ooo v4.1.9, Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Order by with multiple conditions combined Case When

Post by chrisb »

 Edit: oh no!! wrong field, too hasty.
try this: 
replace
THEN P."TimeOrdNo"
with
THEN cast(right('0000' || P."TimeOrdNo", 5) as varchar(5))
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by with multiple conditions combined Case When

Post by AfTech54 »

Thanks and perfect Crisb!! :super:
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?
Ooo v4.1.9, Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Order by with multiple conditions combined Case When

Post by Villeroy »

00001
000012
0000123
00001234
000012345
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by with multiple conditions combined Case When

Post by AfTech54 »

Thanks Villeroy!

I first thought || was OR, but I understand now that it's Concatenate :-)
Ooo v4.1.9, Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] ORDER BY (multiple conditions) with CASE WHEN

Post by chrisb »

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.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: [Solved] ORDER BY (multiple conditions) with CASE WHEN

Post by AfTech54 »

THANKS again Crisb!!
I'm familiar with AscII but I've never known that data is order by them. Great to know!!
Ooo v4.1.9, Windows 10
Post Reply