Ask Your Question

Hello, I am trying to sort the output of a query on the results of 2 fields.

asked 2019-10-14 00:44:03 +0100

chemistry337 gravatar image

updated 2019-10-14 00:49:53 +0100

Ratslinger gravatar image

Hello, I am trying to sort the output of a query on the results of 2 fields. The first field is Money owed, i've sorted this with > '0' in the criterion section of the field.
The second field is a calculation based on 2 fields in my table. The 2 fields are both yes/no fields. I am subtracting one field from the other. Depending on the state of the fields 3 outcomes are possible: 1, 0 & -1. If I leave the criterion empty in the second field all Monies owed are returned. If I put =’0’ in the criterion of the second field only Monies owed with a ‘0’ are returned. However if I put in the criterion field = ‘0’ OR = ‘1’ all of the Monies owed are returned that have a value of ‘0’ and ‘1’ irrespect of whether the value of the Monies owed is greater than 0. I want returned all Monies owed that have a value greater than ‘0’ in the first field and equal to 0 or 1 in the second and not show the -1 Monies owed. Can someone help please?

Below is an example of the 2 fields. I need the monies owed field greater than 0 and the indate field to show all 0 and 1's that correspond to Monies >0

Monies owed indate
£98.67             -1
£98.67             -1
£98.67              1
£98.67             0
£98.67             0
£98.67             1
£98.67             1
£98.67             1
£98.67             1

Thanks in advance Chem

edit retag flag offensive close merge delete


Edited for clarity.

Ratslinger gravatar imageRatslinger ( 2019-10-14 00:50:10 +0100 )edit


Please, when dealing with Base, always mention database used as items such as SQL can vary with database. It is usually helpful to have OS and specific LO version (may have bug fixed is a different release).

Ratslinger gravatar imageRatslinger ( 2019-10-14 01:13:18 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-10-14 01:08:09 +0100

Ratslinger gravatar image

updated 2019-10-14 01:29:14 +0100


Depending upon the database used, you would not (and possibly cannot) use bool fields to do math. Using a simple integer field:

select * from "MoniesOwed" where "amount" > 0 And "field1" - "field2" > -1


After a brief thought, if you stick with bool fields you can use:

Select * from "MoniesOwed" where "amount" > 0 AND NOT("field1" is False AND "field2" is True)

This eliminates only possible negative result.

edit flag offensive delete link more


Have read yet again your question. Am not certain if my "field1" and "field2" are in correct sequence for what you need. If not, it is simply switching the two field names. Hopefully this is clear.

Also, the main question uses the term "sort" but I see no mention in the question about a specific sequence wanted. Can you clarify?

Ratslinger gravatar imageRatslinger ( 2019-10-14 02:17:08 +0100 )edit

Ratslinger, thanks for the answers above, I'm very new to this - has you can probably guess. So, with that in mind:
My operating system is Windows 10, the version of Base in LibreOffice is 6.3. I think that all this is correct. I'll try your suggestions and get back to you. Thanks for your help! Much appreciated.

chemistry337 gravatar imagechemistry337 ( 2019-10-14 10:03:39 +0100 )edit

Thank you for the information but this is not all of it. As was stated database used was noted and you have not mentioned. This can make a difference on SQL presented. Note that Base is not a database but rather a front end to a database. The database can be any of many types - Firebird embedded, HSQLDB embedded, MySQL, PostgreSQL, etc.

And as a note, all this information should be posted with each question asked.

Being new to this, please read (it is short) this post -> Ask/Getting Started

Ratslinger gravatar imageRatslinger ( 2019-10-14 10:20:01 +0100 )edit

Dear Ratslinger,
I've just realized that I know absolutely nothing about SQL ( I mean nothing, first exposure!) I tried your suggestions but they didn't work - operator error I suspect. However, it did something that made me think. I tweaked it and it seems to work.What I did is this:

SELECT "First name", "Surname", "Total_fee", "Commission", "Deposit_paid", "Balance_paid", "Total_fee" - "Commission" - "Deposit_paid" - "Balance_paid" AS "Monies owed", "Guests_have_left" - "Booking_cancel" FROM "Bookingstbl" WHERE ( "Total_fee" - "Commission" - "Deposit_paid" - "Balance_paid" > '0' AND ( "Guests_have_left" - "Booking_cancel" = '0' OR "Guests_have_left" - "Booking_cancel" = '1' ) )

Going forward, because I will probably need to get help again (no probably involved!), I'll always use the code to try and describe my problems.

What a brilliant site this is!

Thanks again Chem

chemistry337 gravatar imagechemistry337 ( 2019-10-14 10:27:30 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-14 00:44:03 +0100

Seen: 20 times

Last updated: Oct 14