|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Odd behavierThe situation is I have two select situations one with a count and the other with a SUM, The COUNT instruct goes well, the SUM instruct is a little picky I had to make a date convertion to char to solve the 156 error that I had. The weird stuff is that with the COUNT instrut I hadn't to do this date convertion. The other weird stuff is that in Query Analyser I had the error but the update was always done, after the disapearence of error 156 the update don't get any result. declare @periodo_inicio as datetime declare @periodo_fim as datetime declare @periodo_temp as int declare @periodo_temp2 as decimal(23,3) declare @periodo_temp3 as int declare @periodo_colA as varchar(20) declare @periodo_colB as varchar(20) declare @periodo_x1 as char(10) declare @periodo_x2 as char(10) set @periodo_temp = 1 print 'PERIODO' DECLARE periodo CURSOR FOR select distinct * from #PERIODOS order by inicio --SELECT distinct inicio, fim FROM #PERIODOS order by inicio OPEN periodo --abrir cursor FETCH NEXT FROM periodo INTO @periodo_inicio, @periodo_fim WHILE @@FETCH_STATUS = 0 BEGIN set @periodo_colA = 'periodo' + CONVERT(varchar,@periodo_temp) set @periodo_colB = 'dias' + CONVERT(varchar,@periodo_temp) set @periodo_x1 = CONVERT(char(10),@periodo_inicio, 23) set @periodo_x2 = CONVERT(char(10),@periodo_fim,23) set @periodo_temp2 = (select sum(nValorJuro4) from #TAB2 where dteDiaJuro between @periodo_x1 and @periodo_x2) select @periodo_temp3 = count(facturas_id) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim exec('update ##CONTENC set ' + @periodo_colA + ' = (select SUM(nValorJuro4) from #TAB2 where dteDiaJuro between '+@periodo_x1+' and '+@periodo_x2+') where factura_id =' + @facturas_id2) exec('update ##CONTENC set ' + @periodo_colB + ' = ' + @periodo_temp3+' where factura_id =' + @facturas_id2) --exec('update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp2+' where factura_id =' + @facturas_id2) set @periodo_temp = @periodo_temp + 1 FETCH NEXT FROM periodo INTO @periodo_inicio, @periodo_fim END CLOSE periodo DEALLOCATE periodo I'm puzzle and haven't a clue for what can I do to solve this.... plan9 wrote:
> I'm puzzle and haven't a clue for what can I do to solve this.... Try some basic debugging techniques, such as isolation the statement(s)causing the error and then posting those. Also it wouldn't hurt to do a little error checking after your updates. Chris plan9,
One reason you haven't a clue is that you aren't writing or looking at the queries that you're running on your data. Not a good idea! To begin, run this procedure with SELECT or PRINT instead of EXEC to see what you are executing. You can then run and debug the queries that are returned in Query Analyzer. Other problems: There is no format 23 for converting datetime to varchar. Do you know why you got the "156 error"? If dteDiaJuro is of type datetime, there should be no need to convert, and if it is, a second, implicit conversion will take place in the comparison in the query. That implicit conversion will use whatever language and dateformat settings are in effect. If your SQL Server instance language is Portuguese, your date format is likely dmy, in which case the string '2006-07-04' means April 7, 2006, unfortunately. set language [Português (Brasil)] go declare @d datetime set @d = '20060704' select @d set @d = convert(char(10),@d,23) select @d go It's best to use the dateformat 'YYYYMMDD' when you specify dates with a literal string. It is the only date-only format that will be interpreted the same way regardless of settings. When you get all this worked out, you can think about redesigning your database so it you don't have separate table columns for each period and day. You can obtain this pivoted format when you produce reports, but if your database is normalized, you will find that many things will become easier. You certainly won't have to use EXEC() to do things like this. -- Steve Kass -- Drew University -- http://www.stevekass.com plan9 wrote: Show quote >Hello > >The situation is I have two select situations one with a count and the other >with a SUM, The COUNT instruct goes well, the SUM instruct is a little picky >I had to make a date convertion to char to solve the 156 error that I had. >The weird stuff is that with the COUNT instrut I hadn't to do this date >convertion. > >The other weird stuff is that in Query Analyser I had the error but the >update was always done, after the disapearence of error 156 the update don't >get any result. > > >declare @periodo_inicio as datetime >declare @periodo_fim as datetime >declare @periodo_temp as int >declare @periodo_temp2 as decimal(23,3) >declare @periodo_temp3 as int >declare @periodo_colA as varchar(20) >declare @periodo_colB as varchar(20) >declare @periodo_x1 as char(10) >declare @periodo_x2 as char(10) > > > >set @periodo_temp = 1 > >print 'PERIODO' >DECLARE periodo CURSOR FOR > > select distinct * from #PERIODOS order by inicio > > --SELECT distinct inicio, fim FROM #PERIODOS order by inicio > > OPEN periodo --abrir cursor > > FETCH NEXT FROM periodo INTO @periodo_inicio, @periodo_fim > > > WHILE @@FETCH_STATUS = 0 > BEGIN > > > > set @periodo_colA = 'periodo' + CONVERT(varchar,@periodo_temp) > set @periodo_colB = 'dias' + CONVERT(varchar,@periodo_temp) > > set @periodo_x1 = CONVERT(char(10),@periodo_inicio, 23) > set @periodo_x2 = CONVERT(char(10),@periodo_fim,23) > > > set @periodo_temp2 = (select sum(nValorJuro4) from #TAB2 where dteDiaJuro >between @periodo_x1 and @periodo_x2) > select @periodo_temp3 = count(facturas_id) from #TAB2 where dteDiaJuro >between @periodo_inicio and @periodo_fim > > > > exec('update ##CONTENC set ' + @periodo_colA + ' = (select >SUM(nValorJuro4) from #TAB2 where dteDiaJuro between '+@periodo_x1+' and >'+@periodo_x2+') where factura_id =' + @facturas_id2) > > exec('update ##CONTENC set ' + @periodo_colB + ' = ' + @periodo_temp3+' >where factura_id =' + @facturas_id2) > --exec('update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp2+' >where factura_id =' + @facturas_id2) > > > set @periodo_temp = @periodo_temp + 1 > > FETCH NEXT FROM periodo INTO @periodo_inicio, @periodo_fim > END > > >CLOSE periodo >DEALLOCATE periodo > > > >I'm puzzle and haven't a clue for what can I do to solve this.... > > > plan9 (pl***@discussions.microsoft.com) writes:
Show quote > set @periodo_temp2 = (select sum(nValorJuro4) from #TAB2 That's what you get for entangling yourself in a web of dynamic SQL.> where dteDiaJuro between @periodo_x1 and @periodo_x2) > select @periodo_temp3 = count(facturas_id) from #TAB2 > where dteDiaJuro between @periodo_inicio and @periodo_fim > > > exec('update ##CONTENC set ' + @periodo_colA + ' = (select > SUM(nValorJuro4) from #TAB2 where dteDiaJuro between '+@periodo_x1+' and > '+@periodo_x2+') where factura_id =' + @facturas_id2) > > exec('update ##CONTENC set ' + @periodo_colB + ' = ' + > @periodo_temp3+' where factura_id =' + @facturas_id2) > --exec('update ##CONTENC set ' + @periodo_colA + ' = ' + > @periodo_temp2+' > where factura_id =' + @facturas_id2) >... > I'm puzzle and haven't a clue for what can I do to solve this.... Having looked at your code, and made a guess of what is going on, it appears that you could do the above in this UPDATE statement: UPDATE ##CONTENC SET periodo1 = CASE x.rownum WHEN 1 THEN x.summa ELSE c.period1 END, periodo2 = CASE x.rownum WHEN 2 THEN x.summa ELSE c.period2 END, ... dias1 = CASE x.rownum WHEN 1 THEN x.cnt ELSE c.dias1 END, ... FROM ##CONTENC c CROSS JOIN (SELECT (SELECT COUNT(*) FROM #PERIODOS p2 WHERE p2.inicio <= p.inicio) AS rownum, SUM(t.nValorJuro4) AS summa, COUNT(t.facturas_id) AS cnt FROM #TAB2 t JOIN #PERIODOS p ON t.dteDiaJuro BETWEEN p.inico AND p.fim GROUP BY p.inicio) AS x WHERE c.facturas_id = @facturas_id I've ignored the date conversion business you had, because I could not make out what that was supposed to be. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hello Erland
I can try to explain what i wont is I have to create a table for a report. But the number of columns is dynamic depends of the rates entered by a user, thats the reason why I'm using dynamic sql, that's because I use the cursor that has two variables that are the begining and end of each rate period. in this situation the case doesn't work (I think...)...do you have any suggestion? Show quote "Erland Sommarskog" wrote: > plan9 (pl***@discussions.microsoft.com) writes: > > set @periodo_temp2 = (select sum(nValorJuro4) from #TAB2 > > where dteDiaJuro between @periodo_x1 and @periodo_x2) > > select @periodo_temp3 = count(facturas_id) from #TAB2 > > where dteDiaJuro between @periodo_inicio and @periodo_fim > > > > > > exec('update ##CONTENC set ' + @periodo_colA + ' = (select > > SUM(nValorJuro4) from #TAB2 where dteDiaJuro between '+@periodo_x1+' and > > '+@periodo_x2+') where factura_id =' + @facturas_id2) > > > > exec('update ##CONTENC set ' + @periodo_colB + ' = ' + > > @periodo_temp3+' where factura_id =' + @facturas_id2) > > --exec('update ##CONTENC set ' + @periodo_colA + ' = ' + > > @periodo_temp2+' > > where factura_id =' + @facturas_id2) > >... > > I'm puzzle and haven't a clue for what can I do to solve this.... > > That's what you get for entangling yourself in a web of dynamic SQL. > > Having looked at your code, and made a guess of what is going on, it > appears that you could do the above in this UPDATE statement: > > UPDATE ##CONTENC > SET periodo1 = CASE x.rownum WHEN 1 THEN x.summa ELSE c.period1 END, > periodo2 = CASE x.rownum WHEN 2 THEN x.summa ELSE c.period2 END, > ... > dias1 = CASE x.rownum WHEN 1 THEN x.cnt ELSE c.dias1 END, > ... > FROM ##CONTENC c > CROSS JOIN (SELECT (SELECT COUNT(*) > FROM #PERIODOS p2 > WHERE p2.inicio <= p.inicio) AS rownum, > SUM(t.nValorJuro4) AS summa, > COUNT(t.facturas_id) AS cnt > FROM #TAB2 t > JOIN #PERIODOS p > ON t.dteDiaJuro BETWEEN p.inico AND p.fim > GROUP BY p.inicio) AS x > WHERE c.facturas_id = @facturas_id > > I've ignored the date conversion business you had, because I could not > make out what that was supposed to be. > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > plan9 (pl***@discussions.microsoft.com) writes:
> I can try to explain what i wont is I have to create a table for a Dynamic number of columns? Only do that if you truly like to hurt yourself.> report. But the number of columns is dynamic depends of the rates > entered by a user, thats the reason why I'm using dynamic sql, that's > because I use the cursor that has two variables that are the begining > and end of each rate period. in this situation the case doesn't work (I > think...)...do you have any suggestion? Make the columns rows and work with them that way. That is what SQL is designed for. Once the result is available, do one of: 1) Present the data as-is. 2) Transpose the data in the client. 3) Use dynamic SQL at the end to transpose. 4) Look at RAC, a third-party product which does the dirty work for you. http://www.rac4sql.net. Again, dynamic columns is extremely painful to work with. And if you really insist on doing it, don't do with dynamic SQL from T-SQL, do it from a client language which have far better capabilities for string manipulation than T-SQL. (Of course, if you are on SQL 2005, you can use a CLR procedure to build the dynamic SQL.) And even if you work with dynamic SQL, you can still generate an UPDATE statement according to the lines I posted. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics