'Like' in a where clause

Archived Topics
1

Hi Guys,
Here is a basic question.
The code below works just fine without line 2 & 3 (the if statement with ‘LIKE’ in it.)
As soon as I select the ‘Alarm Name’ checkbox, the alarm table becomes empty regardless of what I type into the ‘text Field’.

"WHERE 1=1 "+ if({Root Container.Alarms.Options.Alarm Name.selected}, "AND device||'.'||alarm LIKE '%{Root Container.Alarms.Options.Text Field.text}%'", "" ) + if({Root Container.Alarms.Options.HideActive.selected}, "AND clr_time IS NULL ", "") + if({Root Container.Alarms.Options.DateRange.selected}, "AND alm_time >= '"+ dateFormat({Root Container.Alarms.Options.StartDate.date}, 'yyyy-MM-dd 00:00:00')+ "' AND alm_time <= '"+ dateFormat({Root Container.Alarms.Options.EndDate.date}, 'yyyy-MM-dd 23:59:59')+ "'","")

2

Qurban,
I’d be careful with this particular usage of the term code. Usually that refers to Jython scripting. In this case we’re dealing with an expression that a component property is bound to, which is a very different concept from scripting.

Your expression will be difficult for others to follow, including me, because you’re plugging in several FPMI component values. That knarly expression is creating a string that you are probably using somewhere else as the ‘WHERE clause’ in an SQL query. To troubleshoot it, do the following:

  1. Drop a Text Area component on the screen.
  2. Bind the Text property on your Text Area to whatever string property is driving that expression.
  3. You will see the generated WHERE clause. Go into runtime mode (F5), This will update as you check things and move the date range.
  4. Copy this WHERE clause into your SQL frontend. Keep in mind that it’s missing the “SELECT columns FROM table” part. (You could concatenate the WHERE clause to the first part of your query from #2).

You’ll need to dissect one piece at a time to determine why the query isn’t returning any rows. Do this by copying one part of the WHERE clause into your query at a time. It should be pretty easy to figure out once you get visibility on the the query itself instead of the expression that generates it.

3

Nathan,
My apologies for overwhelming you guys with more code(info) then you really need. The so called ‘code’ is part of the ‘where’ clause of a select statement. Everything works except the part below which I just added.

if({Root Container.Alarms.Options.Alarm Name.selected},
“AND device||’.’||alarm LIKE ‘%{Root Container.Alarms.Options.Text Field.text}%’”, “” ) +

I have not tried your seggestion because the database I am working with is not available today. When I select the check box ‘Alarm Name’, the table becomes empty and stays empty even after I enter text into the ‘Text Field’. Does the expression look right to you.
Thanks for your help.

4

OK, I take it back, the code isn’t that bad. Could you explain the following?

device||'.'||alarm

You can go through the first 3 steps that I mentioned without being connected to your SQL database. That will tell you the WHERE clause that you’re generating.

To me it looks like you’ll eventually generate a query that looks something like this (I omitted your ANDs and things to make it play well with the other parts, which looked fine):

SELECT column FROM table WHERE device||'.'||alarm LIKE '%my_input%'
5

device||’.’||alarm represents two columns called device and alarm concatenated with a period.

6

If I recall correctly, you're using PostgreSQL? I'm not very familiar with PGSQL functions. Are you sure you don't do it like this:

SELECT device || ' ' || alarm AS devicealarm FROM ...

Again, you'll have to troubleshoot it against your SQL database. I'll be most helpful with generating the correct strings in FactoryPMI.

7

This is just an off the wall shot, but referring to the following:

if({Root Container.Alarms.Options.Alarm Name.selected}, "AND device||'.'||alarm LIKE '%{Root Container.Alarms.Options.Text Field.text}%'", "" )

Could the problem be that you’re trying to reference a property, but inside a quoted string, so it’s actually taking that reference text literally, as if you were trying to search the table for something LIKE ‘%{Root C’… etc?

I’d try splitting that up into a concatenated string, like:

if({Root Container.Alarms.Options.Alarm Name.selected}, "AND device||'.'||alarm LIKE '%" + {Root Container.Alarms.Options.Text Field.text} + "%'", "" )

Can’t test this myself right now, but I think there’s a good chance that that’s what’s going on.

Regards,

8

Good eye Colby and thanks, that was it.
Now When I Check the check box ‘Alarm Name’, and enter text into the text field, the data in the alarm table does not change untill I uncheck and then again check the check box. Is there a way around this?
Another question: SQL Querys in my project are being executed against ‘Datasource: [project Default]’. I want to change the database for the project, so on ‘Gateway Configuration’ page, under ‘projects’, under ‘Manage’, I clicked ‘edit’ for the project and changed the ‘Primary Datasource’ to the new database. But my project is still looking at the old database. What else do I need to do?

9

[quote="qurban"]
Now When I Check the check box 'Alarm Name', and enter text into the text field, the data in the alarm table does not change untill I uncheck and then again check the check box. Is there a way around this?[/quote]

Your text field is probably set to "defer updates". This means it will not fire property chang events on every keystroke - only on "Enter" and focus lost. This is why clicking the checkbox updates the query - the focus is lost from the text box. Simply uncheck the "Defer Updates" property.

FYI - qurban and I resolved this over the phone - it was a subtle bug in FactoryPMI that had to do with project archive snapshots, and will be fixed in FactoryPMI 3.1