-1

I'm trying to do a query with a sub-query that joins the results of many lines in one, in SQL Server.

When I run it on the Microsoft SQL Server Management Studio it works great!

Query running on the Microsoft SQL Server Management Studio

But doing the same query with PHP, I receive this warning:

Warning: odbc_fetch_array(): SQL error: [Microsoft][ODBC Cursor Library] the instruction SELECT has a clause GROUP BY, SQL state SL005 in SQLGetData

The SQL: 

SELECT CONVERT(VARCHAR(7),[Ficha_Tecnica].DataEntrega,111) as DataEntrega,  
[Ficha_Tecnica].CodigoCliente,  
[Ficha_Tecnica].CodTipoServico,  
count(*) as Contagem,  
[Ficha_Tecnica].NumeroOs,  
(SELECT CAST([Cartoes].Produto + ',' AS VARCHAR(400))
           FROM [Cartoes]
          WHERE [Cartoes].NumeroOS = [Ficha_Tecnica].NumeroOs
          ORDER BY [Cartoes].Produto
            FOR XML PATH('') ) AS Produto2 FROM Ficha_Tecnica  WHERE 1=1  and  Ficha_Tecnica.DataEntrega   >=  '2016-01-19'  
 and  Ficha_Tecnica.DataEntrega   <=  '2016-09-27'  
 GROUP BY CONVERT(VARCHAR(7),[Ficha_Tecnica].DataEntrega,111) 
, [Ficha_Tecnica].CodigoCliente 
, [Ficha_Tecnica].CodTipoServico 
, [Ficha_Tecnica].NumeroOs 
 ORDER BY CONVERT(VARCHAR(7),[Ficha_Tecnica].DataEntrega,111) asc

My PHP Version is 5.6.19

SQL Server 2012

Somebody have any clue of what can be happening? Thanks!

LeonardoAD
  • 1

1 Answers1

0

I've finally found the problem and the solution! After four hours of trying!

I solved the problem making a CAST on the column I was making the sub-query:

CAST(
          (SELECT CAST([Cartoes].Produto + ',' AS VARCHAR(400))
           FROM [Cartoes]
          WHERE [Cartoes].NumeroOS = [Ficha_Tecnica].NumeroOs
          ORDER BY [Cartoes].Produto
            FOR XML PATH('') )

    AS VARCHAR(1000)) AS Produto2

What happens is that something wrong happens when we get a column too big from the SQL Server with PHP as the columns of the type "Text" or "VARCHAR (MAX)", for example!

So, that's it!

Tnx!

LeonardoAD
  • 1