Query tags sometimes fails during startup

Ignition
1

Hi
I am scratching my head over problem with several query tags which are bound to the bottom banner label of the starting screen. Occasionally they keep failing which makes whole banner red and tags have an error - java.lang.nullpointexception

image

Original code behind query tag (in properties selected as event driven ) looks like (actual names changed):

SELECT Level FROM Level_Table

i made some changes to protect from the following:
1 Null value if record is return but it is null by using Coalesce - if null then 0 value is default
2 SELECT EXISTS to protect from query failing and no rows returned - if no records than 0 value is default

When i put the code below behind the tag and try to restart it it stays red all the time
which make me feel i overcomplicated this :slight_smile: Any thoughts how to solve this?

if(SELECT EXISTS(SELECT Level FROM Level_Table),SELECT COALESCE (Level,0) FROM Level_Table,0)

2

Coalesce doesn’t work if there is no row result at all. For such cases you need to use a fallback outside the query.

1 Like
3

Can you help me please bit more to understand what would fallback outside query mean in my case? My query tag is being fed to the label. Sometimes during startup after licence expiry this tag becomes red with the message described above.

In my modified query i use coalesce only if select exists is successful so there must be at least valid dataset
Any thoughts?

4

Show a screenshot of your binding for the banner label text.

5

thanks for your help again screenshot below. Just to make sure its clear - sometimes during startup both query tags and banner gets red so if I improve error handling there will still be issue with red query tags i guess?

image
image868×205 18 KB

6

So that binding will simply propagate any bad quality from ../CurrentTotalCapacity. Please show the SQL (screenshot) for the query tag definition.

1 Like
7

image
image1054×714 47.2 KB

image

8

Are you sure that your asrs_vwStorageCapacity view will always return a row? If it doesn’t, coalesce won’t be applicable. Try this:

SELECT coalesce((SELECT L1_Level_Capacity FROM asrs_vwStorageCapacity), 0)
1 Like
9

Hi Phil
Yes you are correct -i am not sure if a row is always returned. I have just applied your solution for testing. I thought that to check if there are no rows i should use if(SELECT EXISTS(…),0) instead of coalesce but i did not get the syntax right for the last statement.
Thanks for help

10

i had this failure again and noticed that some query tags did not have datasource configured and failed. As silly as that :frowning:
Not sure why default database connection sometimes was used and sometimes not but i need to remember not to leave datasource as blank

1 Like
11

Tags don’t belong to a project, so there’s no default to pick. Query tags always needs the datasource setting.

1 Like