[Solved] Order by and multiple conditions

Discuss the database features

[Solved] Order by and multiple conditions

Postby AfTech54 » Sun Feb 28, 2021 4:29 pm

OpenOffice > Base and I assume HSQL.

--I have one table "tbl_Filter" with one raw which contains search conditions; one column "Source" and one "AddSign"
--I also have one view "vCatalogue" with a bunch of data that I want to order by either its "Fil" or "Foto" column depending on the content in "Source" and "AddSign".
This is what i want to do
-- If "Source" = is null and "AddSign" = is not null then Order by "Foto" Asc
-- All other (3) cases Order by "Fil" ASC


SELECT * From "vCatalogue"
WHERE "Kalla" = 'RESS 1990'
Order by CASE WHEN Select "AddSign" from "tbl_Filter" is not null THEN "Foto" END ASC
this is working as long as I don't add one more condition
I´ve tried
Order by CASE WHEN Select "Source" from "tbl_Filter" is null THEN CASE WHEN Select "AddSign" from "tbl_Filter" is not null THEN "Foto" END ASC
But I can't get it working

Is it doable with multiple conditions in context to Order By? If it is, how to do it?
Last edited by AfTech54 on Sun Feb 28, 2021 9:12 pm, edited 1 time in total.
Ooo v4.1.9, Windows 10
AfTech54
 
Posts: 50
Joined: Tue Dec 31, 2013 10:08 am

Re: Order by and multiple conditions

Postby Mountaineer » Sun Feb 28, 2021 8:11 pm

I'd try
Code: Select all   Expand viewCollapse view
... ORDER BY COALESCE("AddSign", "tbl_Filter", "Foto")


First and if that fails
Code: Select all   Expand viewCollapse view
SELECT ... ,  COALESCE("AddSign", "tbl_Filter", "Foto") AS "Sort" ORDER BY "Sort"


J.
OpenOffice 3.1 on Windows Vista
Mountaineer
 
Posts: 67
Joined: Sun Sep 06, 2020 8:27 am

Re: Order by and multiple conditions

Postby chrisb » Sun Feb 28, 2021 9:08 pm

hello AfTech54,

try this:
Code: Select all   Expand viewCollapse view
SELECT * From "vCatalogue"
WHERE "Kalla" = 'RESS 1990'
order by
   case
      when
         (select "Source" from "tbl_Filter") is null
         and
         (select "AddSign" from "tbl_Filter") is not null
         then "Foto"
      else
         "Fil"
   end
--DESC
--if choosing to sort in descending order then insert the keyword DESC immediately after the case structure
--if sorting in ascending order (ASC) then no action is required as ASC is the default
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 and multiple conditions

Postby AfTech54 » Sun Feb 28, 2021 9:14 pm

Thanks! chrisb!

I did try with AND but I didn't use Else. Anyway it seems to be work fine now - thanks again.
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 6 guests