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

English
#1

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

#2

Edited for clarity.

#3

Hello,

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).

#4

Hello,

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

Edit:

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.

#5

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?

#6

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.

#7

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

#8

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

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.