[Solved] ORDER BY (multiple conditions) with CASE WHEN
[Solved] ORDER BY (multiple conditions) with CASE WHEN
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
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]
Reason: Tagged ✓ [Solved]
Ooo v4.1.9, Windows 10
Re: Order by with multiple conditions combined with Case Whe
Normalize your tables in order to eliminate all Null values.
https://docs.microsoft.com/en-us/office ... escription
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Order by with multiple conditions combined with Case Whe
Thanks, but not sure what the problem is. Why should Order by Case When (doesn't work) be different than just Order by (works).
Ooo v4.1.9, Windows 10
Re: Order by with multiple conditions combined with Case Whe
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.
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
Re: Order by with multiple conditions combined with Case Whe
THANKS again Chrisb!!
It explains the problem and I will try you proposal!!
It explains the problem and I will try you proposal!!
Ooo v4.1.9, Windows 10
Re: [Solved]Order by with multiple conditions combined Case
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..."
"Unresolved parameter type as output of Case when operand types are SMALLINT and LONGVARCHAR in statement..."
Ooo v4.1.9, Windows 10
Re: Order by with multiple conditions combined Case When
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.
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
Re: Order by with multiple conditions combined Case When
OK and thanks.
Hope this is what you're asking for.
Hope this is what you're asking for.
Ooo v4.1.9, Windows 10
Re: Order by with multiple conditions combined Case When
Edit: oh no!! wrong field, too hasty. try this: |
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
Re: Order by with multiple conditions combined Case When
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?
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
Re: Order by with multiple conditions combined Case When
00001
000012
0000123
00001234
000012345
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Order by with multiple conditions combined Case When
Thanks Villeroy!
I first thought || was OR, but I understand now that it's Concatenate
I first thought || was OR, but I understand now that it's Concatenate
Ooo v4.1.9, Windows 10
Re: [Solved] ORDER BY (multiple conditions) with CASE WHEN
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.
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
Re: [Solved] ORDER BY (multiple conditions) with CASE WHEN
THANKS again Crisb!!
I'm familiar with AscII but I've never known that data is order by them. Great to know!!
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