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
  • 1916
  • 10
  • 0

Comments

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.

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?

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.

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?

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.

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.

 

 

 

SQLCODE: :]

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

Please help me with this..

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>....