# Named Query Not Working as expected **URL:** \ **Category:** Ignition\ **Tags:** ignition80\ **Created:** [June 17, 2020, 5:13pm UTC](https://forum.inductiveautomation.com/t/named-query-not-working-as-expected/35766 "2020-06-17T17:13:00Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![MikeAllgood](https://avatars.discourse-cdn.com/v4/letter/m/a183cd/32.png) [@MikeAllgood](https://forum.inductiveautomation.com/u/MikeAllgood)\ **Post date:** [June 17, 2020, 5:13pm UTC](https://forum.inductiveautomation.com/t/named-query-not-working-as-expected/35766/1 "2020-06-17T17:13:00Z")
In a PostgreSQL database I have a table that contains seat counts. The rows have values of Current Hour, Available Seats, & Empty Seats Curr\_Hour Available Empty 9 32 3 9 32 4 10 32 8 10 32 0 10 32 4 I am calculating the sum of Filled Seats form the Available & Empty Seat values (sums). My problem is that I need to get two rows of data to populate a Pie Chart. Those rows would consist of two columns: SeatType todayTotal Filled ### Empty ### In pgAdmin I have written a query that returns what I need: ```auto DROP TABLE IF EXISTS util_daily_totals; CREATE TEMP TABLE util_daily_totals ( seatType varchar(7), todayTotal integer ) ON COMMIT DELETE ROWS; INSERT INTO util_daily_totals (seatType, todayTotal) VALUES('Empty', (SELECT e."Empty" FROM (SELECT SUM(seats_empty) as "Empty" FROM util_hourly WHERE source = 'Track 902' and curr_date = DATE('2020-06-16')) as e)); INSERT INTO util_daily_totals (seatType, todayTotal) VALUES('Filled', (SELECT f."Filled" FROM (SELECT SUM(seats_avail) as "Available", SUM(seats_empty) as "Empty", SUM((seats_avail - seats_empty)) as "Filled" FROM util_hourly WHERE source = 'Track 902' and curr_date = DATE('2020-06-16')) as f)); SELECT * FROM util_daily_totals; ``` The results look like this: seatType todayTotal “Empty” 49 “Filled” 79 When I paste this query into my Named Query it dose not return any values. I do get an error dialog with the following error: ```auto com.inductiveautomation.ignition.client.gateway_interface.GatewayException: org.postgresql.util.PSQLException: No results were returned by the query. at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:339) at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:313) at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:266) at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:915) at com.inductiveautomation.ignition.designer.db.namedquery.workspace.NamedQueryTestingPanel$1$1.doInBackground(NamedQueryTestingPanel.java:313) at com.inductiveautomation.ignition.designer.db.namedquery.workspace.NamedQueryTestingPanel$1$1.doInBackground(NamedQueryTestingPanel.java:288) at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source) at java.base/java.util.concurrent.FutureTask.run(Unknown Source) at java.desktop/javax.swing.SwingWorker.run(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.base/java.lang.Thread.run(Unknown Source) Caused by: java.lang.Exception: org.postgresql.util.PSQLException: No results were returned by the query. at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:413) at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:199) at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:117) at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.executeNamedQuery(NamedQueryFunctions.java:67) at jdk.internal.reflect.GeneratedMethodAccessor164.invoke(null) at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null) at java.lang.reflect.Method.invoke(null) at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:225) at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:412) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86) at org.eclipse.jetty.servlet.ServletHolder$NotAsyncServlet.service(ServletHolder.java:1391) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:760) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:547) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143) at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:590) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1607) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1297) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1577) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1212) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322) at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59) at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) at org.eclipse.jetty.server.Server.handle(Server.java:500) at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383) at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270) at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311) at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103) at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129) at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806) at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938) at java.lang.Thread.run(null) Ignition v8.0.12 (b2020042115) Java: Azul Systems, Inc. 11.0.6 ``` Are there differences in how the query is handled on the Ignition side than on the PostgreSQL side? How do I get this to work? ---
**Author:** ![MikeAllgood](https://avatars.discourse-cdn.com/v4/letter/m/a183cd/32.png) [@MikeAllgood](https://forum.inductiveautomation.com/u/MikeAllgood)\ **Post date:** [June 17, 2020, 8:04pm UTC](https://forum.inductiveautomation.com/t/named-query-not-working-as-expected/35766/2 "2020-06-17T20:04:03Z")
Anyone? Bueller? Bueller?.. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [June 17, 2020, 8:07pm UTC](https://forum.inductiveautomation.com/t/named-query-not-working-as-expected/35766/3 "2020-06-17T20:07:11Z")
> [@MikeAllgood](#): > > ```sql > DROP TABLE IF EXISTS util_daily_totals; CREATE TEMP TABLE util_daily_totals ( seatType varchar(7), todayTotal integer ) ON COMMIT DELETE ROWS; INSERT INTO util_daily_totals (seatType, todayTotal) VALUES('Empty', (SELECT e."Empty" FROM (SELECT SUM(seats_empty) as "Empty" FROM util_hourly WHERE source = 'Track 902' and curr_date = DATE('2020-06-16')) as e)); INSERT INTO util_daily_totals (seatType, todayTotal) VALUES('Filled', (SELECT f."Filled" FROM (SELECT SUM(seats_avail) as "Available", SUM(seats_empty) as "Empty", SUM((seats_avail - seats_empty)) as "Filled" FROM util_hourly WHERE source = 'Track 902' and curr_date = DATE('2020-06-16')) as f)); SELECT * FROM util_daily_totals; > > ``` Have you tried this in the Query Browser? ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [June 17, 2020, 8:15pm UTC](https://forum.inductiveautomation.com/t/named-query-not-working-as-expected/35766/4 "2020-06-17T20:15:03Z")
Looks like this would be better off being a stored procedure… Then again, I guess a named query can be seen as ignition’s version of this 🤔 I don’t know about postgre, but in sql server you’d need GO statements between all of your independent queries ---
**Author:** ![MikeAllgood](https://avatars.discourse-cdn.com/v4/letter/m/a183cd/32.png) [@MikeAllgood](https://forum.inductiveautomation.com/u/MikeAllgood)\ **Post date:** [June 17, 2020, 8:39pm UTC](https://forum.inductiveautomation.com/t/named-query-not-working-as-expected/35766/5 "2020-06-17T20:39:45Z")
I tried it at your suggestion, and no, it does not work there either, though I get a different error message. The message tab of the error dialog states the following: ```auto Error running query: "...Echo of Qeury...": A result was returned when none was expected. ``` The Details tab contains the following: ```auto GatewayException: SQL error for "...Echo of Query...": A result was returned when none was expected. caused by PSQLException: A result was returned when none was expected. Ignition v8.0.12 (b2020042115) Java: Azul Systems, Inc. 11.0.6[Query Error in Query Browser.txt|attachment](upload://qHhcSj9WLFctYI8du3ZAww8ATNu.txt) (6.4 KB) ``` I have attached the full error copied from the error dialog. I’m stumped as I am not really familiar with PostgreSQL. The fact that it runs as expected in PostgreSQL pgAdmin query window but not in Ignitions Query Browser or Named Query makes me lean toward it not being a PostgreSQL issue. ---
**Author:** ![MikeAllgood](https://avatars.discourse-cdn.com/v4/letter/m/a183cd/32.png) [@MikeAllgood](https://forum.inductiveautomation.com/u/MikeAllgood)\ **Post date:** [June 17, 2020, 8:40pm UTC](https://forum.inductiveautomation.com/t/named-query-not-working-as-expected/35766/6 "2020-06-17T20:40:55Z")
I’m trying to build a PostgreSQL function, but it is not exactly like building a stored procedure in MSSQL unfortunately.