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?
[Solved] Order by and multiple conditions
[Solved] Order by and multiple conditions
Last edited by AfTech54 on Sun Feb 28, 2021 9:12 pm, edited 1 time in total.
Ooo v4.1.9, Windows 10
-
- Posts: 318
- Joined: Sun Sep 06, 2020 8:27 am
Re: Order by and multiple conditions
I'd try
First and if that fails
J.
Code: Select all
... ORDER BY COALESCE("AddSign", "tbl_Filter", "Foto")
Code: Select all
SELECT ... , COALESCE("AddSign", "tbl_Filter", "Foto") AS "Sort" ORDER BY "Sort"
OpenOffice 3.1 on Windows Vista
Re: Order by and multiple conditions
hello AfTech54,
try this:
try this:
Code: Select all
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.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 and multiple conditions
Thanks! chrisb!
I did try with AND but I didn't use Else. Anyway it seems to be work fine now - thanks again.
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