Two dropdowns give no results on the table

Ignition
1

I have a query that relies on the values of two dropdowns but I'm not getting any rows returned.

Dropdown1 is BU
Dropdown2 is DC

The query my table is attached to is:

SELECT  
	"COL1",
	"COL2",
	"COL3",
	"COL4",
	CONCAT("COL5",'-',"COL6") as "56"
FROM Inventory
WHERE "Active" = 1
    AND "XXX" = :BU
    AND CONCAT("COL5",'-',"COL6") = :DC

I feel like it has something to do with the concat selection in Dropdown2. Can WHERE match on a CONCAT column?

How could I make this work?

Thanks!

2

If there was some type of syntax error, I would expect that you would get a SQL error back, rather than it just not returning rows.

If you are not getting any rows, this suggests that the values of BU and DC are not what you expect them to be.

How are you setting the parameters in the (I am assuming from parameter syntax) named query? Is this in a binding, or a script? If so can you show that?

2 Likes
3

Here is the parameters in the bindings

image
image731×106 9.89 KB

And the preview gives 5 columns returned, 0 rows.

image

I know the data should be correct because when make another table dependent on dropdown1 only I get the rows (including the CONCAT columns)

edit:

Additionally, I made another query using just the DC variable and it works fine, also the query for both variables works fine when I test it in the query editor. Somehow the :DC value isn't getting passed from Dropdown2

4

A few things that I don't understand.

  1. When you say that you test these in the query editor what does that mean? Are you removing a parameter and providing a constant value, or are you removing the condition from the where clause?
  2. Have you tried running this from the script console and providing static values? If so, did that return any rows?

I see you are using double quotes as a delimiters, what flavor of DB are you running this against? Not all common DB flavors support double quotes by default.

5

  1. Yes, in Ignition Designer under Named Queries and then Authoring for the query, there is a testing tab. If I manually enter a value for DC, I'll get good data returned.

  2. As far as the script console, I don't know how to use it and I get an error if I paste the query directly into it.

This is on a MSSQL DB, I don't know what flavor it is beyond that. Sorry.

6

That's good enough. MSSQL doesn't support the double quote delimeter unless QUOTED_IDENTIFIER is ON. There is no need to delimit column names unless you are using reserved words as column names. That's neither here nor there as, your query is running without error so obviously that's not being seen as an issue. Just trying to rule things out.

The script console can be used as a "play ground" for jython scripts that are used throughout Ignition. There are some caveat's to this.

In this case you should be able to run a script similar to this, obviously replacing the place holder values with real ones. Strings should be quoted, Jython accepts either double or single quotes.

system.db.runNamedQuery('Path/to/Named/Query',{'BU':'Expected Value of BU','DC':'Expected Value of DC'})

Can you show how you have Dropdown_0 configured? Also, is Dropdown_0 in the same container as Dropdown and is that container the same container as the component this query binding is on?

1 Like
7

Dropdown_0:

image
image323×519 23.6 KB

I just noticed the value should be the concatenated value that's selected, it's only showing the last two digits. It should be in the format of XXX-YY and it's only showing YY

8

I would say that is your issue then. Let us know if you're still having issues after correcting the value of this drop down.

1 Like
9

Thank you. Honestly, I'm not sure how to fix it.

10

Well, you will probably need to do a transform on the Options Dataset.

Can you show what the options dataset looks like? And what two values are intended to be concatenated together to yield the actual value you are looking for?