New post
Question
Scott Beeson · Feb 12, 2016

Help with SQL error

#SQL #Caché

 Error:

    [SQLCODE: <-400>:<Fatal error occurred>]
    [Cache Error: <<SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]
    [Location: <ServerLoop - Query Fetch>]
    [%msg: <Unexpected error occurred:  <SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]

 

Here is an example of a query that gives the error:

    SELECT COUNT(DISTINCT Criteria) as Relevance FROM HS_IHE_ATNA_Repository.Aggregation
    WHERE EventType = 'CROSS GATEWAY QUERY'

The content of the actual field is XML, but the query simply returns a number.  Here is one that works:

    SELECT COUNT(DISTINCT DestinationURI) FROM HS_IHE_ATNA_Repository.Aggregation
    WHERE EventType = 'CROSS GATEWAY QUERY'

The result here is "8"

Anyone have any ideas?

0
0 3,902
Discussion (10)0
Log in or sign up to continue
Scott Beeson · Feb 25, 2016

I don't understand so I'll just leave it at 490 for now :)

And yes, there are still long values (this is a log so they won't go away).  I found other fields besides Criteria too.

0 0
Eduard Lebedyuk · Feb 15, 2016

What is the Criteria datatype?

Can you show the property definition?

If for example you have Criteria defined like this:

Property Criteria As %String(MAXLEN = 2000); // MAXLEN>512

Try to define the property like this:

Property Criteria As %String(COLLATION = "TRUNCATE(490)", MAXLEN = 2000);

 

How to debug SQL queries:

1. In SMP > System > Configuration > General SQL Settings  enable the following flags (don't forget to press Save):

  • Retains SQL Statement as Comments in .INT Code
  • Cached Query - Save Source  

2. Purge all Cached Queries - execute in terminal (namespace with query):

  • Do $SYSTEM.SQL.Purge()

3. Execute the query again to get an error

4. Open in studio %sqlcq.HSREGISTRY.cls966.1.int routine (it would be %sqlcq.HSREGISTRY.cls1.1.int after purge)

5. Go to %0AmEdun+4 in this routine and see what this is about. Set try/catch, and debug.

 

In this case the most likely scenario is that the code tries to set global subscript longer than the maximum of 511 characters. Since the only change between queries is the column (there is also "as Relevance" part in your error query, but I think it's irrelevant) then it's probably something with that.

2 0
Scott Beeson · Feb 16, 2016

Knowing what's in the Criteria field, I assume you are right that it's a length issue.  I don't have the ability to change this on the Cache side, so do you know how I can improve my query to get around these errors?

0 0
Eduard Lebedyuk · Feb 16, 2016

Modify your query like this:

SELECT COUNT(DISTINCT $EXTRACT(Criteria, 1, 490)) AS Relevance 
FROM HS_IHE_ATNA_Repository.Aggregation 
WHERE EventType = 'CROSS GATEWAY QUERY'

Only symbols 1 to 490 would be used for comparison in DISTINCT condition, also see $EXTRACT docs.

1 0
Scott Beeson · Feb 25, 2016

Sorry I'm just getting back to this.  Your $EXTRACT solution works great, but why did you choose 490?  I did a quick test with 600 and it still seems to work.  What is the maximum?  or is it dependent upon the processor?

0 0
Brendan Bannon · Feb 25, 2016

OK one more try.

When you use DISTINCT or GROUP BY we are going to build a temp global using the Field values as subscripts.  So say we have a query with GROUP BY Name, Title

the table has data that looks like this:

Scott     Customer

Brendan Support

Eduard Customer

Brendan Trouble Maker

 

To figure out the GROUP BY we will build a global that looks like this:

^||sql.temp(1,"Brendan","Support")=""

^||sql.temp(1,"Brendan","Trouble Maker")=""

^||sql.temp(1,"Eduard","Customer")=""

^sql.temp(1,"Scott","Customer")=""

 

With this data the longest global node is for that Trouble Maker, 39 total charaters.  This is well under 511 so no subscript error.  Now if I have a Title that is 500 characters long then the total length of the global would be over 511 and we would get a <SUBSCRIPT> error.

The more fields you put into a GROUP BY the easier it will be to get over the 511 charater limit as each field will be a different subscript in a global node.

1 0
Brendan Bannon · Nov 16, 2016

Scott

Do you still have long values for Criteria in your data?  The documented limit for a global name plus subscripts is 511

http://docs.intersystems.com/cache20152/csp/docbook/DocBook.UI.Page.cls?...

Depending on the version of Cache the global used internally might change,  For my example the global is ^||%sql.temp so 12 characters+490+(a subscript we are using 2 characters)+ 1 commas +() and we are up to 507

0 0
Manikandasubramani S · Nov 30, 2016

SQLCODE: :]

[%msg: %0AmBk1+6^%sqlcq.datakit.cls30.1>]

Please help me with this..

0 0
Brendan Bannon · Nov 30, 2016

can you please start a new question for this error.  Include the SQL and the full error message.  -400 should be reported for an COS error like <UNDEFINED>,  <SUBSCRIPT>, <FILEFULL>....

1 0