Question on SQL SELECT QUERY using an index

42 views
Skip to first unread message

Melissa Laurel

unread,
Nov 29, 2018, 12:24:52 PM11/29/18
to IRIS, Caché, Ensemble
In normal SQL using Oracle and other databases you can do the following:
SELECT *
FROM Table WITH(INDEX(Index_Name))

I was wondering if CACHE can do the same thing.

So I went to the Management Portal,(we are on version 2010.2.7); Data Management/SQL. Selected my namespace, then under SQL Operations, selected Execute SQL Statement.
I typed in the following statement:
SELECT * 
FROM tUser.tEpisodeTests
WITH INDEX(IndxEpisodeNo)

I get the following error:


ERROR #5540: SQLCODE: -25 Message: Input (WITH) encountered after end of query^ SELECT * FROM tUser . tEpisodeTests WITH


I also tried by calling the column of the table itself:
SELECT EpisodeNo
FROM tUser.tEpisodeTests
WITH (INDEX(IndxEpisodeNo))

I get the same error:
ERROR #5540: SQLCODE: -25 Message: Input (WITH) encountered after end of query^ SELECT EpisodeNo FROM tUser . tEpisodeTests WITH

What am I missing? Can this functionality be done in Management Portal?

Thank You,
Melissa

Brendan Bannon

unread,
Nov 29, 2018, 1:02:36 PM11/29/18
to intersystems...@googlegroups.com

Hi Melissa

 

Cache does not support this syntax. We do the opposite letting you tell us where we should NOT use an index.

 

For example

 

SELECT *
FROM Table

WHERE Age > 0

 

Will return all the people in the data base so if there is an index on Age it would not be helpful here.  You can tell us to avoid that index by adding the hint %NOINDEX before the condition

 

SELECT *
FROM Table

WHERE %NOINDEX Age > 0

 

 

 

Hope this helps

 

Brendan

--
--
IRIS, Caché, Ensemble
---
You received this message because you are subscribed to the Google Groups "IRIS, Caché, Ensemble" group.
To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-publi...@googlegroups.com.
To post to this group, send email to intersystems...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Andrew Makinson

unread,
Dec 1, 2018, 11:42:47 AM12/1/18
to intersystems...@googlegroups.com
Is this the functionality you require to create and INDEX in a cacheSQL table? Or am I not fully understanding the issue ? 



Sent from my iPhone

shrinivas rao

unread,
Dec 1, 2018, 11:42:48 AM12/1/18
to intersystems...@googlegroups.com
Not sure if it can be used.

--
Reply all
Reply to author
Forward
0 new messages