|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
[2002-06-16 08:25 UTC] sander@php.net
|
|||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Fri Apr 19 21:01:30 2024 UTC |
I'm using PHP 3.0.16 with iODBC(Openlink), and when I use the clause "group by" in an SQL instruction I receive an error message. I noted that when I use all the fields of the table in the "group by", I don't receive the message, but the result isn't what I want. I've tested the SQL instruction directely in the program odbctest of iODBC and it works perfectely. DBMS: DB2/NT UDB 5.0.0 running with NT 4.0 My Table: pcaear01 field type cod_filial integer cod_item integer cod_pro integer cod_maq integer some data from the table: cod_filial cod_item cod_pro cod_maq 1 515510242 1 6 1 515510242 2 15 1 515510242 2 29 1 515510242 1 3 1) Select without group by: $result = odbc_do($conn,"select cod_pro from pcaear01 where cod_item = 515510242"); Result: COD_PRO 1 1 2 2 2) Select with group by: $result = odbc_do($conn,"select cod_pro from pcaear01 where cod_item = 515510242 group by cod_pro"); result: Warning: SQL error: [OpenLink][ODBC][IBM][CLI Driver][DB2/NT] SQL0119N An expression starting with "COD_MAQ" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY cla, SQL state S1000 in SQLExecDirect in /home/httpd/html/apontamento/monta_processo.php3 on line 18 expected: COD_PRO 1 2 #Note that I did't use the field cod_maq in the select 3) Select with group by of all fields: $result = odbc_do($conn,"select cod_pro from pcaear01 where cod_item = 515510242 group by cod_pro, cod_maq, cod_item, cod_filial"); Result: COD_PRO 1 1 2 2 ------------------My PHP script <HTML> <HEAD> <TITLE>Lista Agenda Pessoal</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#000080" ALINK="#FF0000"> <? putenv("LD_LIBRARY_PATH=/usr/lib"); putenv("ODBCINSTINI=/usr/local/openlink/bin/odbcinst.ini"); putenv("ODBCINI=/usr/local/openlink/bin/odbc.ini"); $conn = odbc_connect("ZARATEST","db2admin","db2admin"); if(!$conn){ print "<BR>Nao foi possivel conectar-se com o Banco de dados\n"; exit; } $result = odbc_do($conn,"select cod_pro from pcaear01 where cod_item = 515510242 group by cod_pro"); if(!$result){ print "<BR>Nao foi possivel pesquisar o Banco de dados.\n"; exit; } $num_linhas=odbc_num_rows($result); $num_cols=odbc_num_fields($result); echo '<form method="POST" action="monta_maquina.php3" >'; echo '<input type="hidden" name="cod_produto" value="'.$cod_produto.'">'; echo '<p><select name="cod_processo" size="1">\n'; for($i=1;$i<=$num_linhas;$i++){ odbc_fetch_row($result,$i); echo "<option value=".odbc_result($result,1).">".odbc_result($result,2)."</option>\n"; } echo '</select><input type="submit" value="Ok" name="enviar"></p>'; echo '</form>'; odbc_free_result($result); odbc_close($result); ?> </BODY> </HTML>