Problem with a select Msg. 156

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with a select Msg. 156

Author

Topic

plan9
Starting Member

15 Posts

Posted - 2006-07-26 : 22:28:06

Hi

I'm trying to do this select but I'm having a error 156, i'm stuck with this. Can anyone help?

set @periodo_temp2 = (select SUM(nValorJuro4) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim)

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-27 : 03:39:31

Do this....

select @periodo_temp2 = SUM(nValorJuro4) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim

you are trying to assign a resultset to a scalar variable, so you are getting the error.

Harsh Athalye
India.
"Nothing is Impossible"

plan9
Starting Member

15 Posts

Posted - 2006-07-27 : 05:04:12

I still have this error

Server: Msg 156, Level 15, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'where'.

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-27 : 05:35:31

where dteDiaJuro between @periodo_inicio and @periodo_fim

Are they all the same type?

plan9
Starting Member

15 Posts

Posted - 2006-07-27 : 09:50:45

declare @periodo_inicio as datetime
declare @periodo_fim as datetime
declare @periodo_temp as int
declare @periodo_temp2 as decimal(23,13)
declare @periodo_temp3 as int
declare @periodo_colA as varchar(20)
declare @periodo_colB as varchar(20)

set @periodo_temp = 1

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_temp2 = (select SUM(nValorJuro4) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim)

--SELECT @periodo_temp2 = SUM(nValorJuro4)
--FROM #TAB2
--WHERE dteDiaJuro BETWEEN @periodo_inicio AND @periodo_fim;
select @periodo_temp2 = SUM(nValorJuro4) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim

set @periodo_temp3 =(select count(facturas_id) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim)

--debug *****************************
print 'juros do periodo '
print @periodo_temp2
print @periodo_temp3
--*************************************

set @periodo_colA = 'periodo' + CONVERT(varchar,@periodo_temp)
set @periodo_colB = 'dias' + CONVERT(varchar,@periodo_temp)

--exec('update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp3 +'where factura_id =' + @facturas_id2)
exec('update ##CONTENC set ' + @periodo_colB + ' = ' + @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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-27 : 10:23:15

I still find

set @periodo_temp3 =(select count(facturas_id) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim)

try changing this to...

select @periodo_temp3  =  count(facturas_id) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim

and check whether error occurs !

Harsh Athalye
India.
"Nothing is Impossible"

plan9
Starting Member

15 Posts

Posted - 2006-07-27 : 11:07:59

Hello

Thank you for your help, the problem is with the SUM select, the COUNT works fine, still I made the change and continue to have the same error, I'm posting the code rectifide by your instructions, I'm becoming a little desperate, because I'm not finding the error.....

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_temp2 = (select SUM(nValorJuro4) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim)

--SELECT @periodo_temp2 = SUM(nValorJuro4)
--FROM #TAB2
--WHERE dteDiaJuro BETWEEN @periodo_inicio AND @periodo_fim;
select @periodo_temp2 = SUM(nValorJuro4) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim

select @periodo_temp3 = count(facturas_id) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim

--set @periodo_temp3 =(select count(facturas_id) from #TAB2 where dteDiaJuro between @periodo_inicio and @periodo_fim)

--debug *****************************
print 'juros do periodo '
print @periodo_temp2
print @periodo_temp3
--*************************************

set @periodo_colA = 'periodo' + CONVERT(varchar,@periodo_temp)
set @periodo_colB = 'dias' + CONVERT(varchar,@periodo_temp)

--exec('update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp3 +'where factura_id =' + @facturas_id2)
exec('update ##CONTENC set ' + @periodo_colB + ' = ' + @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

plan9
Starting Member

15 Posts

Posted - 2006-07-27 : 12:08:17

I already isolated the problem are those two instructions...

exec('update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp3 +'where factura_id =' + @facturas_id2)

exec('update ##CONTENC set ' + @periodo_colB + ' = ' + @periodo_temp2 +'where factura_id =' + @facturas_id2)

Can anyone help me

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-27 : 12:16:55

quote:
Originally posted by plan9

I already isolated the problem are those two instructions...

exec('update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp3 +'where factura_id =' + @facturas_id2)

exec('update ##CONTENC set ' + @periodo_colB + ' = ' + @periodo_temp2 +'where factura_id =' + @facturas_id2)

Can anyone help me

It's very simple problem...there is not space between table name & where clause...Try this

exec('update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp3 +' where factura_id =' + @facturas_id2)

exec('update ##CONTENC set ' + @periodo_colB + ' = ' + @periodo_temp2 +' where factura_id =' + @facturas_id2)

Harsh Athalye
India.
"Nothing is Impossible"

plan9
Starting Member

15 Posts

Posted - 2006-07-27 : 15:53:23

No it's not working I get this error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'where'.

plan9
Starting Member

15 Posts

Posted - 2006-07-28 : 18:15:07

if it was that problem I would be safe, but unfortunatly is not. the situation is I have two select situations one with a count and the other with a SUM, The COUNT instrut goes well, the SUM instrut 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.

I'm puzzle and haven't a clue for what can I do to solve this....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources