CI3 error with query SQLSERVER |
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
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?
@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?
Whatever you are trying to do, I do not think this is the most efficient way to do it.
(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. |
Welcome Guest, Not a member yet? Register Sign In |