Re: Problem with Sub-Select on IDS-7.30

Informix does not currently support the syntax you are trying to use. I'm also surprised that the temp table approach was faster than your original query since it would appear to be dealing with a larger percentage of the table. I yould try your original query with the following index: create index a_table_idx1 on a_table (valss, ppcode); SELECT DISTINCT SUBSTRING(ppcode FROM 1 FOR 2) AS pp FROM a_table WHERE valss BETWEEN 0 AND 5 ORDER BY pp; Assuming that ppcode is not a varchar, this index should be able to cover the query and provide maximum selectivity. If ppcode is a varchar, the query would still need to go to the base table. You may also want to try switching to the old subscript style instead of substring: SELECT DISTINCT ppcode[1,2] AS pp FROM a_table WHERE valss BETWEEN 0 AND 5 ORDER BY pp; I'm not sure if it will make any difference at all, but it's worth a try. How many rows are in a_table? How many rows have valss between 0 and 5? How wide are the rows? What is the datatype of ppcode? Jay Buckler Torsten Rennett <torsten@rennett.de> wrote in message news:387F87AB.C80045A5@rennett.de... > Hello, > > I would like to to a Sub-Select on Informix Dynamic Server Version 7.30 > as follows: > SELECT DISTINCT pp > FROM ( SELECT DISTINCT SUBSTRING(ppcode FROM 1 FOR 2) AS pp, > valss AS ss > FROM a_table ) > WHERE ss BETWEEN 0 AND 5 > ORDER BY pp;> > But I always get a syntax error: > SELECT DISTINCT pp > FROM ( SELECT DISTINCT SUBSTRING(ppcode FROM 1 FOR 2) AS pp,> # ^ > # 201: A syntax error has occurred. > # > > When I use a temporary table everything works fine: > SELECT DISTINCT SUBSTRING(ppcode FROM 1 FOR 2) AS pp, valss AS ss > FROM a_table > INTO TEMP tmp; > SELECT DISTINCT pp > FROM tmp > WHERE ss BETWEEN 0 AND 5 > ORDER BY pp;> > Due to the book "A Guide to THE SQL STANDARD" by Chris J. Date the > syntax should be ok. > > So please help -- what I'm doing wrong? What's the right way? > > > P.S.: > The initial intension was to do the following simple query: > SELECT DISTINCT SUBSTRING(ppcode FROM 1 FOR 2) AS pp > FROM a_table > WHERE valss BETWEEN 0 AND 5 > ORDER BY pp;> This works but is MUCH slower! I created any index combination I could > think of -- but nothing helps, until I found the fast solution with the > Sub-Select. > > -- > Ingenieurbuero RENNETT -- innovative Individual-Software -- > Torsten Rennett > Ludwig-Thoma-Weg 14 E-Mail: mailto:torsten@rennett.de > D-85551 Heimstetten Telefon: +49-89-90480538

Thank you Jay for your help! Jay Buckler wrote: > Informix does not currently support the syntax you are trying to use. :-(( > I yould try your original query with the following index: > > create index a_table_idx1 on a_table (valss, ppcode);> > SELECT DISTINCT SUBSTRING(ppcode FROM 1 FOR 2) AS pp > FROM a_table > WHERE valss BETWEEN 0 AND 5 > ORDER BY pp;> > Assuming that ppcode is not a varchar, this index should be able to > cover the query and provide maximum selectivity. If ppcode is a > varchar, the query would still need to go to the base table. I've tried create index a_table_idx1 on a_table (ppcode,valss);which should be the same and it didn't help. 'ppcode' has type 'char(10)'. > You may also want to try switching to the old subscript style instead of > substring: > > SELECT DISTINCT ppcode[1,2] AS pp > FROM a_table > WHERE valss BETWEEN 0 AND 5 > ORDER BY pp; Doesn't help. > How many rows are in a_table? Number of Rows 26107 > How many rows have valss between 0 and 5? 15014 > How wide are the rows? Row Size 306 > What is the datatype of ppcode? 'ppcode' has type 'char(10)'. > I'm also surprised that the temp table approach was faster than your > original query since it would appear to be dealing with a larger > percentage of the table. I think generally you are right, but in this special case the distribution of data is very irregular. In order to complete the above information: SELECT DISTINCT SUBSTRING(ppcode FROM 1 FOR 2) AS pp, valss AS ss FROM a_table INTO TEMP tmp; 191 row(s) retrieved into temp table. So the the second query SELECT DISTINCT pp FROM tmp WHERE ss BETWEEN 0 AND 5 ORDER BY pp;is on a very small table of 191 rows. Conclusion: ----------- |'SUBSTRING(ppcode FROM 1 FOR 2)' 'WHERE ss BETWEEN 0 AND 5' ------------------------------------------------------------------------ query | 26107 rows 191 rows w/ temp | (but fast because of a single (191 is no problem, table | index on ppcode) with index or not) | | one | 15014 rows 26107 rows simple | (This seems the problem to me! (fast because of a single query | Even the combined index does index on ss) | not speed it up. Why?) -- Ingenieurbuero RENNETT -- innovative Individual-Software -- Torsten Rennett Ludwig-Thoma-Weg 14 E-Mail: mailto:torsten@rennett.de D-85551 Heimstetten Telefon: +49-89-90480538