[Solved] Negative parameter for query problem

Creating tables and queries

[Solved] Negative parameter for query problem

Postby cwdavi1 » Thu Feb 13, 2020 10:40 pm

I have a simple query:
Code: Select all   Expand viewCollapse view
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
cwdavi1
 
Posts: 29
Joined: Fri Sep 13, 2019 9:47 pm

Re: Negative parameter for query problem

Postby Sliderule » Fri Feb 14, 2020 5:01 pm

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   Expand viewCollapse view
    SELECT * FROM "MediaInfo" WHERE "aid" = :P1 * 1

  2. This uses the IN clause with the Parameter

    Code: Select all   Expand viewCollapse view
    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.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: [SOLVED] Negative parameter for query problem

Postby cwdavi1 » Fri Feb 14, 2020 6:16 pm

: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
cwdavi1
 
Posts: 29
Joined: Fri Sep 13, 2019 9:47 pm

Re: [SOLVED] Negative parameter for query problem

Postby Sliderule » Fri Feb 14, 2020 6:24 pm

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
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Negative parameter for query problem

Postby Russ Ullman » Sat Feb 15, 2020 7:35 pm

Having trouble just posting to site.
OpenOffice 4.1.7 Windows 7
Russ Ullman
 
Posts: 6
Joined: Sat Feb 15, 2020 8:13 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 7 guests