# Compare single value with a query dataset **URL:** \ **Category:** Ignition\ **Tags:** vision, ignition79\ **Created:** [August 11, 2023, 6:42pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773 "2023-08-11T18:42:18Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![Mithun\_Reddy\_Basvana](https://avatars.discourse-cdn.com/v4/letter/m/9f8e36/32.png) [@Mithun\_Reddy\_Basvana](https://forum.inductiveautomation.com/u/Mithun_Reddy_Basvana)\ **Post date:** [August 11, 2023, 6:42pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/1 "2023-08-11T18:42:19Z")
Hi, I am running a simple query to return one column of data. I want to compare one value which is inputted into the ignition project by a operator to these column of data to see if it matches . i am getting the query values to print but the if statement is not running .How can i do these? ```auto #define a query query = """ SELECT xx FROM xx where xx = (?) """ #call the query and return the results in a variable called dataset dataset = system.db.runPrepQuery(query,['xxxxx'],'yyyy') print dataset #iterate through the results to print the values at each row,column for row in range(dataset.rowCount): print "row: " + str(row) for col in range(dataset.columnCount): print dataset.getValueAt(row,col) if scannedpart == dataset.getValueAt(row,col): message = "equal" else: message = "not equal" ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 11, 2023, 6:54pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/2 "2023-08-11T18:54:08Z")
Please edit your post (with the pencil icon) so it is formatted for readability: - Remove any white space or punctuation from in front of "I am running", as it triggers a one-line display. - Highlight all of your pasted code then click the \ button (preformatted text). That will mark the code as code, and it will display with proper indentation and with color coding. ---
**Author:** ![Mithun\_Reddy\_Basvana](https://avatars.discourse-cdn.com/v4/letter/m/9f8e36/32.png) [@Mithun\_Reddy\_Basvana](https://forum.inductiveautomation.com/u/Mithun_Reddy_Basvana)\ **Post date:** [August 11, 2023, 7:01pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/3 "2023-08-11T19:01:31Z")
Thank you edited ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 11, 2023, 7:06pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/4 "2023-08-11T19:06:30Z")
You are checking for equality **after** the loops have run and row & col are off the end. You must check within the loop, where you are currently printing the column value. I recommend returning `True` as soon as you find a match, and returning `False` if you get all the way through the loops. You probably should not be using `print` -- it doesn't behave well in many cases. ---
**Author:** ![Mithun\_Reddy\_Basvana](https://avatars.discourse-cdn.com/v4/letter/m/9f8e36/32.png) [@Mithun\_Reddy\_Basvana](https://forum.inductiveautomation.com/u/Mithun_Reddy_Basvana)\ **Post date:** [August 11, 2023, 7:53pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/5 "2023-08-11T19:53:48Z")
That helped thank you ---
**Author:** ![Mithun\_Reddy\_Basvana](https://avatars.discourse-cdn.com/v4/letter/m/9f8e36/32.png) [@Mithun\_Reddy\_Basvana](https://forum.inductiveautomation.com/u/Mithun_Reddy_Basvana)\ **Post date:** [August 11, 2023, 9:45pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/6 "2023-08-11T21:45:42Z")
But, after moving the IF statement into the loop it only performs the equal operation on the last row of the dataset how can i get it to perform on all the rows? ```auto for row in range(dataset.rowCount): #print "row: " + str(row) for col in range(dataset.columnCount): #print dataset.getValueAt(row,col) if scannedPart == dataset.getValueAt(row,col): message = "True" else: message = "False" ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 11, 2023, 9:56pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/7 "2023-08-11T21:56:15Z")
Start with your result preset to false. When the comparison is true, set the result to true and **break out of the loop**. A function return is simplest, but the `break` statement is an option. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [August 11, 2023, 9:57pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/8 "2023-08-11T21:57:39Z")
Is this what you want? ```auto message = "False" for row in range(dataset.rowCount): for col in range(dataset.columnCount): if scannedPart == dataset.getValueAt(row,col): message = "True" break # Break out of inner loop. if message = "True": break # Break out of outer loop. ``` Python doesn't have a neat way of breaking out of nested loops. You could create a function and break out using a `return` statement and this might be neater. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [August 11, 2023, 10:33pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/9 "2023-08-11T22:33:18Z")
No need for nested loops at all, it's a single column. ```auto return any(val for val in dataset.getColumnAsList(dataset.getColumnIndex(0) if val == selectedPart) ``` This will return true if there is a match and false otherwise, and `any()` is short circuiting so it will return at the first match. ---
**Author:** ![Mithun\_Reddy\_Basvana](https://avatars.discourse-cdn.com/v4/letter/m/9f8e36/32.png) [@Mithun\_Reddy\_Basvana](https://forum.inductiveautomation.com/u/Mithun_Reddy_Basvana)\ **Post date:** [August 11, 2023, 10:34pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/10 "2023-08-11T22:34:15Z")
Thank you. I had to change a couple more things I had to move col to the first for loop and row to the second one I don't completely understand why these works do you? ```auto result = "false" for col in range(dataset.columnCount): #print "row: " + str(row) for row in range(dataset.rowCount): #print dataset.getValueAt(row,col) if scannedPart == dataset.getValueAt(row,col): result = "true" message = "equal" break else: message = "Not equal" ``` ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [August 11, 2023, 10:37pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/11 "2023-08-11T22:37:01Z")
It would not matter, in this instance if the outer loop was rows or columns, there must have been another error. ---
**Author:** ![Mithun\_Reddy\_Basvana](https://avatars.discourse-cdn.com/v4/letter/m/9f8e36/32.png) [@Mithun\_Reddy\_Basvana](https://forum.inductiveautomation.com/u/Mithun_Reddy_Basvana)\ **Post date:** [August 15, 2023, 12:32am UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/12 "2023-08-15T00:32:21Z")
I have a problem with the above after running the below code. Seems like after comparing 1 row in the dataset and if it is not equal its performing the "else" statement i only want it to perform the else statement after comparing all the rows in the dataset and only if the overall result is not equal ```auto result = "false" for col in range(dataset.columnCount): #print "row: " + str(row) for row in range(dataset.rowCount): #print dataset.getValueAt(row,col) if scannedPart == dataset.getValueAt(row,col): result = "true" message = "Equal" break else: perform a API call ``` ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [August 15, 2023, 1:24am UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/13 "2023-08-15T01:24:31Z")
> [@Mithun\_Reddy\_Basvana](#): > > Seems like after comparing 1 row in the dataset and if it is not equal its performing the "else" statement Because that **is** what it is doing. That is _exactly_ what you have programmed it to do. You stated in your original post that this is only a single column. From this last post I take that to mean that if there is no match, then you want to do something. This script is all you need. ```auto if all(val != selectedPart for val in dataset.getColumnAsList(0)): #perform API call ``` Lets talk about whats happening here: 1. `dataset.getColumnAsList(0)` This function returns the entirety of a column as a list. Since you only have a single column, there is no need to loop through the columnCount, the code will only execute once anyway. The added syntax for a for loop confuses what the script is actually accomplishing. 2. `val != selectedPart for val in dataset.getColumnAsList(0)` This is a special way of writing a for loop in python. It is roughly equivalent to: 3. `all()` This function takes an iterator of boolean items if any of them is false (i.e. val == selectedPart) then it will immediately return false. You can almost read this as: if all values in column 0 are not equal to selectedPart then perform API call. As a complete aside, the formatting in your last code blocks while better, still hurts my eyes a bit to look at. ---
**Author:** ![Mithun\_Reddy\_Basvana](https://avatars.discourse-cdn.com/v4/letter/m/9f8e36/32.png) [@Mithun\_Reddy\_Basvana](https://forum.inductiveautomation.com/u/Mithun_Reddy_Basvana)\ **Post date:** [August 15, 2023, 10:19pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/14 "2023-08-15T22:19:17Z")
Thank you for the detailed info. it seems converting a column into a List is not working I am not able to print a List ```auto print dataset print dataset.getColumnIndex(value) print dataset.getColumnName(0) print dataset.getColumnAsList(0) ``` below is the output for the above 4 commands. I am not seeing a list ```auto 0 value ``` my ignition platform version is 7.9.17 does these have anything to do with these? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 15, 2023, 10:30pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/15 "2023-08-15T22:30:13Z")
> [@Mithun\_Reddy\_Basvana](#): > > ignition platform version is 7.9.17 That definitely makes a difference. In old versions, the `.getColumnAsList()` method was on selected implementations of `Dataset`. And `PyDataset` was quite distinct from `Dataset`. This has changed over time, such that `Dataset` implements `.getColumnAsList()` in the interface now, and `PyDataset` implements `Dataset`. You're kind of [expletived] with that old version. ---
**Author:** ![Mithun\_Reddy\_Basvana](https://avatars.discourse-cdn.com/v4/letter/m/9f8e36/32.png) [@Mithun\_Reddy\_Basvana](https://forum.inductiveautomation.com/u/Mithun_Reddy_Basvana)\ **Post date:** [August 15, 2023, 10:33pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/16 "2023-08-15T22:33:31Z")
Thank you. Any other way to create a List in these version so the below command works? `if all(val != selectedPart for val in dataset.getColumnAsList(0)):` ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [August 15, 2023, 10:41pm UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/17 "2023-08-15T22:41:24Z")
Not simply, that I’m aware of, but that’s okay. We can achieve the same type of thing like this: ```auto if all(dataset.getValueAt(row,0) != selectedPart for row in range(dataset.getRowCount()): #perform API call ``` Would be better to use `xrange()` but I don’t remember if you had to import that in 2.3 or not. ---
**Author:** ![Mithun\_Reddy\_Basvana](https://avatars.discourse-cdn.com/v4/letter/m/9f8e36/32.png) [@Mithun\_Reddy\_Basvana](https://forum.inductiveautomation.com/u/Mithun_Reddy_Basvana)\ **Post date:** [August 16, 2023, 12:14am UTC](https://forum.inductiveautomation.com/t/compare-single-value-with-a-query-dataset/77773/18 "2023-08-16T00:14:19Z")
Thank you that works