Ask Your Question
0

Query with user parameter

asked 2019-08-20 20:22:59 +0200

jodybingo gravatar image

Hello, Using Design View, I have a query with LIKE '%' || :Search || '%' as a user parameter (used to do a keyword search). This worked well when I used LO Base with HSQLDB, but since I am using LO (6.2.5) as a front end and MySQL server this user parameter is returning all my records.
How do I set up this query to allow a user parameter search for text (partial or full)?
Thanks,
Jody

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-08-20 22:32:17 +0200

Ratslinger gravatar image

Hello,

Try:

Like concat('%', :Search, '%')
edit flag offensive delete link more

Comments

User parameter window isn't appearing
-jody

jodybingo gravatar imagejodybingo ( 2019-08-21 16:17:28 +0200 )edit

@jodybingo,

Sorry but don't understand your comment. You seem to have accepted the answer but contradict this with the comment. Have tested on Mint 18.3 without problem using LO v6.3.0.4. Is this working for you? If not, exactly what is happening?

Ratslinger gravatar imageRatslinger ( 2019-08-21 16:34:53 +0200 )edit

Sorry if I hit the tick by mistake. The object is to have a user enter a string of text and have the query return matches. Example: A person could enter ardi into a user parameter window and it would return the records containing Bombardier. I'm on W10 with MySQL Server 8.0 btw

jodybingo gravatar imagejodybingo ( 2019-08-21 16:51:47 +0200 )edit

Yes, that is what is done in my answer. Prompt is displayed, enter condition and result is displayed where entered data is somewhere within specified field. What exactly is your SQL and what specific problem are you having?

Have just re-tested but I am using Linux Mint with local MySQL Serrver and LO v6.3.0.4.

Ratslinger gravatar imageRatslinger ( 2019-08-21 17:26:02 +0200 )edit

SELECT Company FROM reservations.corporate infocorporate info WHERE Company = 'Like concat(''%'', :Search, ''%'')'
LO 6.2.5.2, W10 with MySQL Server 8.0. The user parameter window isn't appearing

jodybingo gravatar imagejodybingo ( 2019-08-21 17:38:47 +0200 )edit
1

@jodybingo,

Don't know why you chose to surround the LIKE section with apostrophes. That actually signifies what it is equated to. Also you have = and LIKE together. Also problem in concat section. Statement should be:

SELECT `Company` FROM `reservations`.`corporate info` `corporate info` WHERE   `Company` LIKE concat('%', :Search, '%')
Ratslinger gravatar imageRatslinger ( 2019-08-21 19:31:33 +0200 )edit

I copy/pasted your original response into design view and it put in the apostrophes. When I pasted into SQL view it works.
Thanks for your patience.

jodybingo gravatar imagejodybingo ( 2019-08-21 20:42:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-08-20 20:22:59 +0200

Seen: 33 times

Last updated: Aug 20