Kyle Lists Common Problems and Their Solutions: 7 Reasons A Query Returns No Data

So I know it's been a while, and I hate to let my adoring fans down... just not enough to actually start writing again.  But the wait is over and I'm back!  Now bask in my beautiful ginger words!

For this series, I am going to look at some common problems we see in the WRC and discuss some common solutions.  Of course, even if you find a solution here, you are always welcome to call in and expression you gratitude, or just hear my voice!

This week's common problem: "My query returns no data."

Now, supposedly you've checked that your query SHOULD return data.  That is, if you do a "SELECT * FROM MyTable" and don't get back any data, I don't expect that your more complicated query with JOINs, a WHERE clause, and a GROUP BY will either.  So, if you've determined that the data lives in your table what else could be going wrong?

1) You're in the wrong namespace.

Laugh if you'd like, but this is by far the most common problem.  Usually people don't call in with this one, but it does occasionally come up.  If you're not getting data, the first reaction you should have is to check your namespace, and while you're at it, make sure you're connecting to the right instance.  Get the simple one out of the way first.

2) You need to build indices.

This is the problem that trips up most people.  If you open your class definition and add an index, it is not automatically built for you.  Therefore, when you add an index, it is available for use by the SQL Compiler, but it doesn't have any data in it.  So when we look, no data, boom, query done, no results!  You need to call ##class(<your class>).%BuildIndices($LB("<New Index>")) in order to get that new index populated with the right data.  Be careful doing this on a live system, though!  For advice on building an index on a live system, please contact the WRC and let us know your version!

3) You are in the wrong SELECTMODE.

When running a query, there are a few modes you can be in: Logical, Display, and ODBC.  The typical way to show the difference is with %Date properties.  For instance today is 64295, 01/12/2017, 2017-01-12 in Logical, Display, and ODBC modes respectively.  If you are in the wrong SELECTMODE then your date queries will sometimes return no data even if they should.  A good way to test this is to run a query in the SQL Shell and set your selectmode - like so:

SAMPLES>d $SYSTEM.SQL.Shell()

SAMPLES>>selectmode = odbc

This way you have absolute (and simple) control over your query's selectmode.  You can also run xDBC (ODBC/JDBC) queries and know that we're in ODBC mode.  

4) Collation problems

If you followed any of Brendan's awesome posts regarding building your own storage, there is an additional problem that can catch you.  You need to make sure that the collation you defined for your field is properly put into your index.  Therefore, if your %String field has SQLUPPER collation, the index should have SQLUPPER collation.  If not, you can find that you have no data. 

5) NLS on CacheTemp

This is pretty rare, but if you are using a different NLS collation, you need to make sure the NLS collation on CACHTEMP matches the NLS collation on your database.  See the official word in the docs here:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

6) InterSystems Bug

Hey, I'm not too proud to admit it.  Occasionally we have a bug and it could be that.  If you think so, contact the WRC!

7) Your Query has a Bug.

What, you thought you were off the hook!?  If our code can have a bug, so can your query!  Double check your query!

Those are the big ones.  Try looking at some of the things mentioned here, and if you have trouble figuring it out, feel free to contact Support and we'll be happy to look at it with you!

-----------------------------------------------------------------------------

YOU NEED A TL;DR for this!?  It was so short!

7 Reasons why your query might not be returning data:

1) You're in the wrong namespace.

2) You need to build indices.

3) You are in the wrong SELECTMODE.

4) Collation problems

5) NLS on CacheTemp

6) InterSystems Bug

7) Your Query has a Bug.

Edit: Almost forgot  - do you have a problem that you ran into where a query returned no data?  Add your experience to the comments!