It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Search matching with Pocket Query

Eugene Domingo Oct 28, 2014

I have this inventory database used to track where items are.  The query below doesn't seem to work how I expect it.  I would like to be able to input something like "Dell" and it should come up with everything in the description box with the words "Dell" in it (not case sensitive).  But, it only comes up when using exact matches (inputting "Dell Monitor").  I thought using "like '%:Value%'" would do it but that don't work.  neither does " = :Value" (Value is my dynamic variable).  I thought that by default, searches are done as wildcards.  I can't seem to find any documentation on the syntax either.

select a.asset_tag AS 'Asset Number', 
a.asset_description AS 'Asset', 
i.item_number AS 'Item Description', 
a.model AS 'Model Number', 
a.serial_number AS 'Serial Number', 
l.code AS 'Location', 
c.description AS 'Category', 
d.contract_id AS 'Contract ID'
from asset a, location l, item i, category c, contracts d
where (a.asset_description = :Value or 
             i.item_number = :Value or 
             l.code = :Value or 
             a.model = :Value or 
             a.serial_number = :Value or 
             a.asset_tag = :Value or 
             c.description = :Value) and 
                    a.location_id = l.location_id and 
                    a.item_id = i.item_id and 
                    c.category_id = i.category_id and 
                    a.record_status = '1'
order by l.code

3 answers

1 accepted

0 votes
Answer accepted
Eugene Domingo Oct 31, 2014

I discovered that if you place % within the query field, it will glob the search, provided I use LIKE instead of =.

But, if there is a way to default glob search without having to place the % sign in it, that would be great.

0 votes
Felix Grund Nov 03, 2014

Hi Eugene!

Thank you for your interest in PocketQuery. We once had an issue regarding a problem with colons inside single quotes. So in version 1.13.1, we implemented that the pattern :xxx is not interpreted as parameter when it is inside single quotes. Now in your case with the LIKE statement, of course, you want your parameter interpreted. This is legitimate and I see that it's no good that it doesn't work with the current implementation. I'll think about how we tackle this problem and in the meantime please use your workaround wink.

Regards, Felix

Eugene Domingo Nov 04, 2014

Thanks.

Eugene Domingo Nov 04, 2014

I checked out PocketQuery, PlaySQL and SQL macro (legacy) and found that PocketQuery does best for what I need. I like how you can customize the dynamic fields on the screen, the use of modifying links to display tables differently by using get in the link, and how easy it is to implement. I was able to utilize the documentation on the website but some questions I have are not answered in the documentation, which is why I've already asked several questions on this forum so far.

Eugene Domingo Nov 04, 2014

Is there a way to add the '%' sign to the value of the dynamic field without it appearing on the screen?

Felix Grund Nov 05, 2014

Thanks for your feedback! At the moment, unfortunately no. I will fix this in the next version and let you know!

0 votes
Felix Grund Nov 12, 2014

Hi Eugene!

I actually found a good solution for this! You can use a concatenation function in your SQL dialect that prevents the parameters from being inside quotes. The following example is MySQL-specific.

Instead of this:

SELECT * 
FROM Country 
WHERE Name LIKE '%:param%'

You can write this:

SELECT * 
FROM Country 
WHERE Name LIKE CONCAT("%",:param,"%")

Let me know if this helps!

Regards, Felix [Scandio]

 

Eugene Domingo Nov 13, 2014

It doesn't seem to work. I get an error. I'll look into it more.

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Confluence

Confluence CVEs and common questions

Two vulnerabilities have been published for Confluence Server and Data Center recently: March 20, 2019 CVE-2019-3395 / CVE-2019-3396 April 17, 2019 CVE-2019-3398 The goal of this article is...

969 views 0 19
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you