#19045 [Opn->Ana]: SELECT DISTINCT with just ONE table doesn't work

ID: 19045 Updated by: kalowsky@php.net Reported By: christophe.bidaux@netcourrier.com -Status: Open +Status: Analyzed Bug Type: ODBC related Operating System: IIS4 on NT4 PHP Version: 4.2.2 New Comment: Interestingly enough some digging into this results in the following data: The ODBC standard exec only supports the following options for a SELECT statement: FROM, WHERE, GROUP BY, HAVING, UNION, and ORDER BY. So technically doing a SELECT DISTINCT shouldn't ever work. Although your example seems to disprove this theory. I'm going to have to do more looking into this as I get time... as I don't think this should be an issue. It should be a SQL Driver issue, not an ODBC Driver issue. Previous Comments: ------------------------------------------------------------------------ [2002-08-30 03:10:23] christophe.bidaux@netcourrier.com it doesn't work with : $requete="select distinct POSTE from TEMPSCUMULES"; and it works with : $requete="select POSTE from TEMPSCUMULES"; ------------------------------------------------------------------------ [2002-08-29 16:52:00] kalowsky@php.net $stmt1 ="select distinct POSTE, LBLPOSTE from TEMPSCUMULES where SOCIETE='001' and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by POSTE", it doesn't work. $stmt2 ="select POSTE, LBLPOSTE from TEMPSCUMULES where SOCIETE='001'and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by POSTE" $stmt3 ="select distinct GROUPE, POSTE, LBLPOSTE from TEMPSCUMULES, GROUPE_MACHINE where POSTE=MACHINE and SOCIETE='001' and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by GROUPE, POSTE" so $stmt2, and $stmt3 work fine. Have you tried with a simplier select query? ------------------------------------------------------------------------ [2002-08-29 02:45:09] christophe.bidaux@netcourrier.com For understanding my examples, you have to replace (°) in '$requete="(°)";' by the query string given below. The first query string is a select distinct with one table (TEMPSCUMULES), and it doesn't work. The second one is a select (no distinct) with one table, and it works fine . And the last one is a select distinct with two tables (TEMPSCUMULES and GROUPE_MACHINE), and it works fine too. ------------------------------------------------------------------------ [2002-08-28 22:52:24] kalowsky@php.net i don't see a select distinct happening on one table here, are you sure the proper code is documented below? ------------------------------------------------------------------------ [2002-08-22 09:23:19] christophe.bidaux@netcourrier.com My problem is the same described in the 13167 and 7789 bug reports, but I don't know how to change a bug report status... I have exactly the same problem with the currently last PHP version/CGI (4.2.2) : I have no result (IIS "stops" the script, and the page is never sended to the client) with an ODBC/Oracle query with a "select distinct". But, I have something to add : the problem appears with a query with just ONE table in the "select distinct", but not, in the same conditions with a query with two tables linked in the "select distinct". ---------- $gp=odbc_pconnect("INTRANET gp","user","password"); $requete="(°)"; $db_query=odbc_exec($gp,$requete); odbc_fetch_into($db_query,$db_array); with (°)="select distinct POSTE, LBLPOSTE from TEMPSCUMULES where SOCIETE='001' and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by POSTE", it doesn't work. with (°)="select POSTE, LBLPOSTE from TEMPSCUMULES where SOCIETE='001' and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by POSTE" or (°)="select distinct GROUPE, POSTE, LBLPOSTE from TEMPSCUMULES, GROUPE_MACHINE where POSTE=MACHINE and SOCIETE='001' and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by GROUPE, POSTE", it works fine. The driver is "Oracle ODBC Driver 8.00.06.00" Merci. Christophe ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=19045&edit=1