European Experts Exchange (EEE.org) :: Free Experts PHP MySql Oracle Microsoft Web SQL Delphi


Databases :: MySql :: Building Informix SQL Statements
By: JerryScheer	Date: 16/07/2003 00:00:00	Points: 90	Status: Answered Quality : Excellent
I have a little problem transfering the SQL Statements which ran under MySQL to the Informix SQL I already asked a question in <A HREF="http://www.experts-exchange.com/Databases/Mysql/Q_20662870.html">http://www.experts-exchange.com/Databases/Mysql/Q_20662870.html</A> this worked fine for MySQL however as we do a Benchmark for Databases the Statements are not compatible In fact when I do the following SELECT LENGTH(column) AS len, COUNT(*) AS nbr FROM table WHERE LENGTH(column) BETWEEN lower AND upper GROUP BY len ORDER BY len the len is not accessible in the Where block or in the Group by , Order by it says it can't fin the "len"Column How should I change this to be accessible. Second Problem: This statement does not execute, it says Syntax Error: SELECT Count DISTINCT LENGTH(column) FROM table However for numeric values this works fine with Select Count Distinct(column) from table But for Strings i want to do it over the length of the string not over the contents.
By: ikework	Date: 16/07/2003 16:44:00		Type : Comment
i don't really know informix, but in the first statement try to replace in the group by and order by clause len with LENGTH(column): SELECT LENGTH(column) AS len, COUNT(*) AS nbr FROM table WHERE LENGTH(column) BETWEEN lower AND upper GROUP BY LENGTH(column) << ORDER BY LENGTH(column) << by the way, maybe its better to ask this question in the databases area, not in the mysql area, cauz that's actually no mysql-question hope this helps a little bit :) maik

By: VGR	Date: 17/07/2003 22:19:00		Type : Comment
1) perhaps informix (:vomit:) doesn't recognize "AS". Some RDBMS just set aliases without the "as". So try : SELECT LENGTH(column) len, COUNT(*) nbr FROM table WHERE len BETWEEN lower AND upper GROUP BY len ORDER BY len DESC; 2) SELECT Count (DISTINCT LENGTH(column)) FROM table;

By: JerryScheer	Date: 17/07/2003 22:33:00		Type : Comment
1) the AS is recognized, the problem is it is not visible for the inner part of the Where. I found the solution for that, i have to write GROUP BY 1 This does the grouping over the first table, which is LENGTH (column) 2) still no solution. The Length(column) is not accepted,after the Distinct Informix wants a table not a resultset. It does not meet the SQL Standard, however we have to support it.

By: VGR	Date: 17/07/2003 22:42:00		Type : Answer
yes, distinct on columns is also an extension of MySql little question : WHY are you porting to such a pityful restricted-limited system as Informix ?

By: JerryScheer	Date: 17/07/2003 23:07:00		Type : Comment
Because we do a benchmark for SQL Servers. And this Unit of the benchmark should ensure that the randomly created data in the Database fullfills the TPCW Standards for Database Benchmarks.

By: VGR	Date: 17/07/2003 23:10:00		Type : Comment
heh if you benchmark servers, *please* use standard SQL :D

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Contact

page generated in 309.128050 milliseconds

Why Google AdSense ads ?

New Improved Search!