[Solved] Negative parameter for query problem

Creating tables and queries
Post Reply
cwdavi1
Posts: 29
Joined: Fri Sep 13, 2019 9:47 pm

[Solved] Negative parameter for query problem

Post by cwdavi1 »

I have a simple query:

Code: Select all

SELECT * FROM "MediaInfo" WHERE "aid" = :P1
. The "aid" column is integer and has some negative integers in it. When I run the query and enter a positive integer it works but finds no records if I enter a negative. It does work when I replace :P1 with either '-1030' or just -1030. I've wasted an hour on this and give up but hopefully someone can give me a pointer. I know I could do it with a form and a filter table but this is a trivial function and I don't want to waste any more time on it plus that seems like overkill.

Thanks
Last edited by cwdavi1 on Fri Feb 14, 2020 6:10 pm, edited 1 time in total.
Libreoffice 6.3 on Windows 10 Pro
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Negative parameter for query problem

Post by Sliderule »

Work around ( you should NOT have to do this ) . . . but . . . you can use EITHER of the below Queries to get the results you desire.
  1. This multiplies the Parameter by 1

    Code: Select all

    SELECT * FROM "MediaInfo" WHERE "aid" = :P1 * 1
  2. This uses the IN clause with the Parameter

    Code: Select all

    SELECT * FROM "MediaInfo" WHERE "aid" IN ( :P1 )
Explanation:
  1. Either of the two work-around will provide the results you desire:
    1. The difference ( change ) is to multiply the Parameter by 1 and it should work.
    2. The difference ( change ) the Parameter by coding it within an IN clause.
  2. NOTE: This is NOT an issue with the database back-end ( HSQL 1.8.0.10 ) but rather an issue with Base OpenOffice / LibreOffice database parser.
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
cwdavi1
Posts: 29
Joined: Fri Sep 13, 2019 9:47 pm

Re: [SOLVED] Negative parameter for query problem

Post by cwdavi1 »

:P1*1 works, too lazy to try the IN just to see if it works, I'm sure you did. Thank you for the info and thanks to all the knowledgeable people who provide support on this forum. I could have searched for years and never found this. As an aside, is this something I could fix for the project? I had 27 years experience as a software engineer developing applications and systems before I retired in 2007.
Libreoffice 6.3 on Windows 10 Pro
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [SOLVED] Negative parameter for query problem

Post by Sliderule »

cwdavi1 wrote::P1*1 works, too lazy to try the IN just to see if it works, I'm sure you did. Thank you for the info and thanks to all the knowledgeable people who provide support on this forum. I could have searched for years and never found this. As an aside, is this something I could fix for the project? I had 27 years experience as a software engineer developing applications and systems before I retired in 2007.
While this seems to be an issue with BOTH OpenOffice and LibreOffice Parser ( the module that CHANGES the SQL Query ) . . . for example, to substitute the Parameter to a value, or, an INPUT date from local format to SQL format ( YYYY-MM-DD ) . . . I would encourage you file a BUG report.

Go to the link below, and, click on Create a bug report

https://www.libreoffice.org/get-help/feedback/

I looked quickly, and, did NOT see a filed bug with LibreOffice.

Sliderule
Russ Ullman
Posts: 6
Joined: Sat Feb 15, 2020 8:13 am

Re: [Solved] Negative parameter for query problem

Post by Russ Ullman »

Having trouble just posting to site.
OpenOffice 4.1.7 Windows 7
Post Reply