Problem with a select Msq. 156

Question

Post reply

Problem with a select Msq. 156

Mário Almeida

Old Hand

Points: 344
More actions
July 26, 2006 at 8:28 pm

#173642

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

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 1

What will happen if you replace your code with this instead?

SELECT @periodo_temp2 = SUM(nValorJuro4)

FROM #TAB2

WHERE dteDiaJuro BETWEEN @periodo_inicio AND @periodo_fim

Also, which datatypes are nValorJuro4 and @period_temp2?

N 56°04'39.16"
E 12°55'05.25"

Mário Almeida Old Hand Points: 344 More actions · Answer 2

The error is the same

Server: Msg 156, Level 15, State 1, Line 1

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'where'.

the variables that you mentioned are both decimal(23,3)

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 3

What datatype is dteDiaJuro? What datatypes are @periodo_inicio and @periodo_fim?

Give the whole picture, please. Are you building a dynamic SQL statement?

N 56°04'39.16"
E 12°55'05.25"

Mário Almeida Old Hand Points: 344 More actions · Answer 4

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

Mário Almeida Old Hand Points: 344 More actions · Answer 5

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

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 6

Yes.

Put a space before the WHERE ...

N 56°04'39.16"
E 12°55'05.25"

Mário Almeida Old Hand Points: 344 More actions · Answer 7

yes I spotted that error a while, and solved it, but I still have an error, the thing that is puzzling me is that the variable @periodo_temp3 its a int and it runs without any trouble, but @periodo_temp2 it's a decimal and it's the error.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

It's almost the same problem as this...

SELECT 'A'+1

You must convert the numeric variables to strings in order to concatenate them with other strings.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mário Almeida Old Hand Points: 344 More actions · Answer 9

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....

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 10

So, what your telling me is that @facturas_id2 is a VARCHAR like it needs to be? Make sure because I didn't see it defined in any of the code you posted, so far.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)