[Solved] Subtraction in a query

Creating tables and queries
Post Reply
StormWolf
Posts: 12
Joined: Sun Jul 08, 2018 12:46 pm

[Solved] Subtraction in a query

Post by StormWolf »

Hi All,
Using Windows 10 and Open Office Base,

Couldn't find anything similar to my request, so . . .

I have a table with Position from 1 to x. In a query I would like to subtract the posn number from a specific number (that can be put into query and not changed), eg 11 - posn number = result. Posn 1=10; Pos 2=9 etc Negative results need to be 0. I might have 20 positions, but numbers 11-20 will return a 0 result.

Thank you
Open Office 4.1.3 Using Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Subtraction in a query

Post by Villeroy »

SELECT CASE WHEN "POS">11 THEN "POS"-11 ELSE 0 END AS "Diff" FROM "Table"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
StormWolf
Posts: 12
Joined: Sun Jul 08, 2018 12:46 pm

Re: Subtraction in a query

Post by StormWolf »

Thanks Villeroy

Thanks for your reply but not quite what I needed, my fault. I would like to query result as follows:
Pos Diff
1 10
2 9
3 8
etc
9 2
10 1
11 0
12 0
etc

Appreciate your help.
Open Office 4.1.3 Using Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Subtraction in a query

Post by FJCC »

Code: Select all

SELECT CASE WHEN "Pos">11 THEN 0 ELSE 11-"Pos" END AS "Diff" FROM "Table"
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
StormWolf
Posts: 12
Joined: Sun Jul 08, 2018 12:46 pm

Re: Subtraction in a query

Post by StormWolf »

This is very frustrating.

I have stripped the query down to as basic a query i can.

SELECT "Pos" case when ("Pos">11) then 0 else 11-"Pos" end as "Diff" FROM "TblChart" Syntax Error somewhere

SELECT "Pos" case when "Pos">11 then 0 else 11-"Pos" end as "Diff" FROM "TblChart"
syntax error somewhere

Wow I just found the error. Simple stupid error. A COMMA after SELECT "Pos",

It now works fine, tried with numbers other than 11. Works a treat. Thank you for your help in getting the right phrase guys, appreciate it.

:D
Open Office 4.1.3 Using Windows 10
Post Reply