Hello Everybody!,
I’d like to setup a Named Query that selects rows based on WHERE conditions. In some use cases, I’d like to pass a wildcard (*) for a parameter. So far, it seems like the only solution is to create a new Named Query with an omitted WHERE condition.
What do you guys think?
Sounds like you could utilize the SQL ‘LIKE’ operator.
What is your query? and where do you want this wildcard?
Query looks like this:
"SELECT * FROM table
WHERE
Column1 = :String1
AND Column2 = :Int1
AND Column3 = :Int2
"
Query works great if I run system.db.runNamedQuery, but in some cases I’d like to grab rows that only match the first two arguments and pass a wildcard integer for :Int2 .
How would I put a wildcard in the python dictionary or should I add ‘LIKE’ operator like @bmusson said?
Thanks guys
You can’t use LIKE in your instance.
You can use this :
SELECT * FROM table
WHERE
Column1 = :String1
AND Column2 = :Int1
AND (CAST(Column3 as nvarchar) = :Int2 OR :Int2 = '*' )
Note: your “int2” would actually need to be a string type parameter, that, or add a new parameter called Int2Enable
SELECT * FROM table
WHERE
Column1 = :String1
AND Column2 = :Int1
AND (Column3 = :Int2 OR :Int2Enable <> 1 )
You could use a query parameter instead, but then you would rely on people supplying the right string (mentioned this just for other ideas, definitely not the best option though) .
LIKE doesn’t work for integer columns, only for string columns. I would make another named query. IA isn’t charging you for extra ones, after all. 
Something that I do quite frequently is to add a 1 = Case statement like the following, allows you to just pass a null if you want to “ignore” the check for a certain column
SELECT * FROM table WHERE
1 = (CASE WHEN :Int1 IS NULL THEN 1 WHEN :Int1 = Column1 THEN 1 ELSE 0 END) AND
1 = (CASE WHEN :Int2 IS NULL THEN 1 WHEN :Int2 = Column2 THEN 1 ELSE 0 END) AND
1 = (CASE WHEN :String1 IS NULL THEN 1 WHEN :String1 = Column3 THEN 1 ELSE 0 END)
In this case you might pass it the following to ignore the Column2 check
Int1 = 1, Int2 = NULL, String1 = “Test”
You can change the WHEN :Int1 IS NULL THEN 1 To whatever you like to ignore that parameter, If null is a valid parameter in this case then maybe you use WHEN :Int1 = -1 THEN 1
Note, I also typically put these in stored procedures, as I find keeping as much SQL code on the SQL server as possible is easier to manage, so I am really just executing the named query as
EXEC dbo.StoredProcedure :Int1, :Int2, :String1
Note: you won’t be able to use NULL in a named query, unless maybe you’re using the script function and can actually pass in nulls in (not sure if possible). You can’t pass nulls into a binding though
Good point @nminchin
I forgot to mention this, I typically use scripting for named query calls, however to use the property bindings you would need to use something like a -1 or empty string "" to accomplish this.
I read through twice.
I don’t see the solution for the binding to passing a wildcard into a named query other than just having two different queries, which is a fine option for me.
I made a named query. I want to check for either a specific stopCode or else to get all of them.
So in my where clause I have
and stopCode = :thisCode
I tried to pass a star, and that did not work.
There is no wild card for an Integer. An integer is either an exact match or it isn’t.
However, you can easily write a query that has multiple execution paths depending on if a particular stopCode is present or not using the EXISTS operator.
IF EXISTS(SELECT 1 FROM yourTable WHERE stopCode = :thisCode)
BEGIN
SELECT * FROM yourTable WHERE stopCode = :thisCode
END
ELSE
BEGIN
SELECT * FROM yourTable
END
Bleh. So a legitimate code that just hasn't happened since the last DB partition pruning blows up your result set.....
I recommend an OR in the where clause:
and (:thisCode = -1 OR stopCode = :thisCode)
Smart DBs might even factor out that comparison when given the -1.
+1 for this, I use this a lot in my named queries, its the "cleanest" way to do conditional where clauses in a Named Query.
huh. never thought of that.
Hi,
Is there a way you can use the LIKE function and % wildcard with also a parameter to filter a string colum?
I tried many things but did not manage to get it working.
My code:
SELECT
DATETIME(ROUND(ae.event_timestamp/ 1000), 'unixepoch') AS Time,
ae.actor AS User,
case when ae.action='tag write' then
(select oae.action_value from AUDIT_EVENTS oae where oae.action=ae.action and oae.action_target=ae.action_target and oae.EVENT_TIMESTAMP<ae.EVENT_TIMESTAMP order by oae.EVENT_TIMESTAMP desc LIMIT 1)
else '' end as oldValue,
ae.action_value AS NewValue,
ae.action_target AS Target
FROM AUDIT_EVENTS ae
WHERE ae.action_target = :TargetFilter LIKE '%' and ae.event_timestamp > :Startdate and ae.event_timestamp < :Enddate and ae.action = 'tag write'
I want to filter action_target with a string parameter. I have a text field in my window where I can type a string and what I want is that the wildcard is placed at the end of the string.
The target colum has to show the data that starts with the text that is in the parameter and all the wildcard fills all that comes after that.
If I type "[Test SCADA Ignition]" in my text field where the parameter is bind to the wildcard has to fill the rest.
anyone that knows what I'm doing wrong or if this is even possible
if you want to use a parameter in some fashion with the LIKE operator, you concatenate it.
select * from table
where col like concat(:param,'%')
It doesn't recognise the function CONCAT
use the function that is available in your DB flavor
This, itself, is not valid SQL syntax. When using LIKE, you don't use an equals sign.
ah Okay I will try.
Edit:
I got it working!
I searched for my DB flavor and got it working with the following:
ae.action_target LIKE :TargetFilter || '%'