Page 1 of 1

[Solved] Order by and multiple conditions

PostPosted: Sun Feb 28, 2021 4:29 pm
by AfTech54
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?

Re: Order by and multiple conditions

PostPosted: Sun Feb 28, 2021 8:11 pm
by Mountaineer
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.

Re: Order by and multiple conditions

PostPosted: Sun Feb 28, 2021 9:08 pm
by chrisb
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

Re: [Solved] Order by and multiple conditions

PostPosted: Sun Feb 28, 2021 9:14 pm
by AfTech54
Thanks! chrisb!

I did try with AND but I didn't use Else. Anyway it seems to be work fine now - thanks again.