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

Discuss the database features

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

Postby AfTech54 » Fri Mar 19, 2021 10:30 am

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
AfTech54
 
Posts: 50
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by with multiple conditions combined with Case Whe

Postby Villeroy » Fri Mar 19, 2021 10:57 am

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
User avatar
Villeroy
Volunteer
 
Posts: 29713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Order by with multiple conditions combined with Case Whe

Postby AfTech54 » Fri Mar 19, 2021 11:29 am

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
AfTech54
 
Posts: 50
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by with multiple conditions combined with Case Whe

Postby chrisb » Fri Mar 19, 2021 2:49 pm

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   Expand viewCollapse view
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.8 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.2 (Split) on Windows 10
chrisb
 
Posts: 262
Joined: Mon Jun 07, 2010 4:16 pm

Re: Order by with multiple conditions combined with Case Whe

Postby AfTech54 » Fri Mar 19, 2021 3:05 pm

THANKS again Chrisb!!

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

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

Postby AfTech54 » Fri Mar 19, 2021 3:28 pm

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
AfTech54
 
Posts: 50
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by with multiple conditions combined Case When

Postby chrisb » Fri Mar 19, 2021 3:49 pm

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.8 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.2 (Split) on Windows 10
chrisb
 
Posts: 262
Joined: Mon Jun 07, 2010 4:16 pm

Re: Order by with multiple conditions combined Case When

Postby AfTech54 » Fri Mar 19, 2021 4:08 pm

OK and thanks.

Hope this is what you're asking for.

Photo_ Table Design.jpg
Ooo v4.1.9, Windows 10
AfTech54
 
Posts: 50
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by with multiple conditions combined Case When

Postby chrisb » Fri Mar 19, 2021 4:24 pm

 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.8 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.2 (Split) on Windows 10
chrisb
 
Posts: 262
Joined: Mon Jun 07, 2010 4:16 pm

Re: Order by with multiple conditions combined Case When

Postby AfTech54 » Fri Mar 19, 2021 5:10 pm

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
AfTech54
 
Posts: 50
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by with multiple conditions combined Case When

Postby Villeroy » Fri Mar 19, 2021 5:36 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 29713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Order by with multiple conditions combined Case When

Postby AfTech54 » Fri Mar 19, 2021 6:42 pm

Thanks Villeroy!

I first thought || was OR, but I understand now that it's Concatenate :-)
Ooo v4.1.9, Windows 10
AfTech54
 
Posts: 50
Joined: Tue Dec 31, 2013 10:08 am

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

Postby chrisb » Fri Mar 19, 2021 7:21 pm

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.8 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.2 (Split) on Windows 10
chrisb
 
Posts: 262
Joined: Mon Jun 07, 2010 4:16 pm

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

Postby AfTech54 » Fri Mar 19, 2021 8:54 pm

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
AfTech54
 
Posts: 50
Joined: Tue Dec 31, 2013 10:08 am


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests