![]() |
CI3 error with query SQLSERVER - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6) +--- Forum: CodeIgniter 3.x (https://forum.codeigniter.com/forumdisplay.php?fid=17) +--- Thread: CI3 error with query SQLSERVER (/showthread.php?tid=78475) |
CI3 error with query SQLSERVER - Y6700B - 01-26-2021 Hi everyone, I have this query if I run it in sqlstudio it works The query PHP Code: SELECT * INTO #TempTable FROM ( SELECT VDN,YEAR(FECHA) AS AÑO,MONTH(FECHA) AS MES,FORMAT(FECHA,'MMMM') AS MES_NOMBRE, SUM(INCALLS - ABNCALLS) AS CALLS FROM [dbo].[CCOD_TRAFICO_INTERVALO] WHERE VDN IN (71072,50001,50002,50003,50004,50005,50006,50007,50039,50040) AND FECHA >= '2020-10-01' GROUP BY VDN,YEAR(FECHA),MONTH(FECHA),FORMAT(FECHA,'MMMM') ) T; DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); SET @cols = (SELECT STRING_AGG(MES_NOMBRE,',') FROM (SELECT DISTINCT AÑO,MES,MES_NOMBRE FROM #TempTable)t); SET @query = 'SELECT VDN, ' + @cols + ' FROM ( SELECT VDN,MES_NOMBRE,CALLS FROM #TempTable ) X PIVOT ( SUM(CALLS) FOR MES_NOMBRE IN (' + @cols + ')) P '; execute(@query); drop table #TempTable as a result I got a table Code: VDN October November December January but when I put it funcion in the model PHP Code: The model. and print into controller, the result is Array() and I dont know why. Any idea? Regards RE: CI3 error with query SQLSERVER - php_rocs - 01-26-2021 @Y6700B, You have a lot going on there. Can you do me a favor and format it. This will allow us to read it better. RE: CI3 error with query SQLSERVER - Y6700B - 01-27-2021 Hi I think CI has a problem when I use pivot function in sqlserver, I made another test usign the next query PHP Code: SELECT * FROM ( It´s simple but when I print the result in the controller only show me. PHP Code: ( When If I run it in SQlServerStudio gave a table that I want Code: DOMINIO 4 5 Anyone could fix this or have an example? RE: CI3 error with query SQLSERVER - php_rocs - 01-27-2021 @Y6700B, Thx for the reformat. I have one suggestion and 1 question. Suggestion: If you are not expecting a dynamic variable to be a part of the query then why not make your query a database VIEW within MSSQL Server and then call the view. Question: what error message are you seeing when you run it in CI? RE: CI3 error with query SQLSERVER - ojmichael - 01-27-2021 Whatever you are trying to do, I do not think this is the most efficient way to do it. RE: CI3 error with query SQLSERVER - Y6700B - 01-28-2021 (01-27-2021, 07:49 PM)php_rocs Wrote: @Y6700B, Suggestion: The function is dinamyc only for the example I didnot set variables. Question: There´s no error message the only think is the function from de model doesnt return all info and return an empty array. RE: CI3 error with query SQLSERVER - php_rocs - 01-28-2021 @Y6700B, If the query does not need to have variables added then why not make it a MSSQL query then all you have to do in CI4 is call the query. |