Select problem Msg. 156

27 Jul 2006 2:31 AM

plan9

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

27 Jul 2006 2:40 AM

Stu

error 156 means that your T-SQL code is incorrect; without more code,
there's little advice anyone can offer you.

Stu

plan9 wrote:
Show quote

> 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

27 Jul 2006 8:48 AM

plan9

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;

        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

Show quote

"Stu" wrote:

> error 156 means that your T-SQL code is incorrect; without more code,
> there's little advice anyone can offer you.
>
> Stu
>
> plan9 wrote:
> > 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
>
>

27 Jul 2006 12:36 PM

Stu

Thanks for the code; where do you create the temporary table #TAB2?

Stu

plan9 wrote:
Show quote

> 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;
>
>
>
>         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
>
>
> "Stu" wrote:
>
> > error 156 means that your T-SQL code is incorrect; without more code,
> > there's little advice anyone can offer you.
> >
> > Stu
> >
> > plan9 wrote:
> > > 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
> >
> >

27 Jul 2006 12:54 PM

plan9

--criar tabela temporaria
!--------------------------------------------------------------------
CREATE TABLE #TAB2 (
facturas_id int,
tipo_doc varchar(10) null,
txtDescricao varchar(50) null,
dteDataEmissao smalldatetime null,
dteDataVencimento smalldatetime null,
dteDiaJuro smalldatetime null,
valor_fact decimal(12,2) null,
valor_pag decimal(12,2) null,
nTaxaJuro decimal (5,3)null,
nValorJuro4 decimal(23,13) null
)

Show quote

"Stu" wrote:

> Thanks for the code; where do you create the temporary table #TAB2?
>
> Stu
>
> plan9 wrote:
> > 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;
> >
> >
> >
> >         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
> >
> >
> > "Stu" wrote:
> >
> > > error 156 means that your T-SQL code is incorrect; without more code,
> > > there's little advice anyone can offer you.
> > >
> > > Stu
> > >
> > > plan9 wrote:
> > > > 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
> > >
> > >
>
>

27 Jul 2006 2:06 PM

Stu

I think your problem is here:

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

@periodo_temp3 is a decimal, which means that the above statement will
not parse correctly. Try:

--exec('update ##CONTENC set ' + @periodo_colA + ' = '
+ CONVERT(varchar(20), @periodo_temp3)
+'where factura_id =' + @facturas_id2)

plan9 wrote:
Show quote

> --criar tabela temporaria
> !--------------------------------------------------------------------
> CREATE TABLE #TAB2 (
> facturas_id int,
> tipo_doc varchar(10) null,
> txtDescricao varchar(50) null,
> dteDataEmissao smalldatetime null,
> dteDataVencimento smalldatetime null,
> dteDiaJuro smalldatetime null,
> valor_fact decimal(12,2) null,
> valor_pag decimal(12,2) null,
> nTaxaJuro decimal (5,3)null,
> nValorJuro4 decimal(23,13) null
> )
>
>
> "Stu" wrote:
>
> > Thanks for the code; where do you create the temporary table #TAB2?
> >
> > Stu
> >
> > plan9 wrote:
> > > 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;
> > >
> > >
> > >
> > >         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
> > >
> > >
> > > "Stu" wrote:
> > >
> > > > error 156 means that your T-SQL code is incorrect; without more code,
> > > > there's little advice anyone can offer you.
> > > >
> > > > Stu
> > > >
> > > > plan9 wrote:
> > > > > 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
> > > >
> > > >
> >
> >

27 Jul 2006 3:32 PM

plan9

Sorry Stu, but the command that you suggest, gives a Msg. 156.
From what I was able to figure out of the code the problem is with the
select with the SUM command...can you help me there?

Show quote

"Stu" wrote:

> I think your problem is here:
>
>                 --exec('update ##CONTENC set ' + @periodo_colA + ' = '
> + @periodo_temp3
> +'where factura_id =' + @facturas_id2)
>
> @periodo_temp3 is a decimal, which means that the above statement will
> not parse correctly. Try:
>
>                 --exec('update ##CONTENC set ' + @periodo_colA + ' = '
> + CONVERT(varchar(20), @periodo_temp3)
> +'where factura_id =' + @facturas_id2)
>
> plan9 wrote:
> > --criar tabela temporaria
> > !--------------------------------------------------------------------
> > CREATE TABLE #TAB2 (
> > facturas_id int,
> > tipo_doc varchar(10) null,
> > txtDescricao varchar(50) null,
> > dteDataEmissao smalldatetime null,
> > dteDataVencimento smalldatetime null,
> > dteDiaJuro smalldatetime null,
> > valor_fact decimal(12,2) null,
> > valor_pag decimal(12,2) null,
> > nTaxaJuro decimal (5,3)null,
> > nValorJuro4 decimal(23,13) null
> > )
> >
> >
> > "Stu" wrote:
> >
> > > Thanks for the code; where do you create the temporary table #TAB2?
> > >
> > > Stu
> > >
> > > plan9 wrote:
> > > > 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;
> > > >
> > > >
> > > >
> > > >         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
> > > >
> > > >
> > > > "Stu" wrote:
> > > >
> > > > > error 156 means that your T-SQL code is incorrect; without more code,
> > > > > there's little advice anyone can offer you.
> > > > >
> > > > > Stu
> > > > >
> > > > > plan9 wrote:
> > > > > > 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
> > > > >
> > > > >
> > >
> > >
>
>

27 Jul 2006 4:04 PM

plan9

Sorry Stu, you are right, 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)

but the instrut that you suggest isn't working.

Show quote

"Stu" wrote:

> I think your problem is here:
>
>                 --exec('update ##CONTENC set ' + @periodo_colA + ' = '
> + @periodo_temp3
> +'where factura_id =' + @facturas_id2)
>
> @periodo_temp3 is a decimal, which means that the above statement will
> not parse correctly. Try:
>
>                 --exec('update ##CONTENC set ' + @periodo_colA + ' = '
> + CONVERT(varchar(20), @periodo_temp3)
> +'where factura_id =' + @facturas_id2)
>
> plan9 wrote:
> > --criar tabela temporaria
> > !--------------------------------------------------------------------
> > CREATE TABLE #TAB2 (
> > facturas_id int,
> > tipo_doc varchar(10) null,
> > txtDescricao varchar(50) null,
> > dteDataEmissao smalldatetime null,
> > dteDataVencimento smalldatetime null,
> > dteDiaJuro smalldatetime null,
> > valor_fact decimal(12,2) null,
> > valor_pag decimal(12,2) null,
> > nTaxaJuro decimal (5,3)null,
> > nValorJuro4 decimal(23,13) null
> > )
> >
> >
> > "Stu" wrote:
> >
> > > Thanks for the code; where do you create the temporary table #TAB2?
> > >
> > > Stu
> > >
> > > plan9 wrote:
> > > > 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;
> > > >
> > > >
> > > >
> > > >         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
> > > >
> > > >
> > > > "Stu" wrote:
> > > >
> > > > > error 156 means that your T-SQL code is incorrect; without more code,
> > > > > there's little advice anyone can offer you.
> > > > >
> > > > > Stu
> > > > >
> > > > > plan9 wrote:
> > > > > > 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
> > > > >
> > > > >
> > >
> > >
>
>

27 Jul 2006 4:34 PM

Stu

comment out the exec statements, and use PRINT statements to see what
the finaly dynamic SQL statement is saying, eg.:

PRINT 'update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp3
+ 'where factura_id =' + @facturas_id2

You need to convert to varchar all of the variables that are outside
the SQL string (eg, @periodo_temp3 and @facturas_id2). You may also
want to combine your two update statements into one.

I haven't even given you the standard lecture on don't use cursors and
avoid dynamic SQL unless its absolutely necessary because I assumed
that your design called for it; however, you may want to step back from
this problem and try to rethink it a bit.

Stu

plan9 wrote:
Show quote

> Sorry Stu, you are right, 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)
>
>
> but the instrut that you suggest isn't working.
>
> "Stu" wrote:
>
> > I think your problem is here:
> >
> >                 --exec('update ##CONTENC set ' + @periodo_colA + ' = '
> > + @periodo_temp3
> > +'where factura_id =' + @facturas_id2)
> >
> > @periodo_temp3 is a decimal, which means that the above statement will
> > not parse correctly. Try:
> >
> >                 --exec('update ##CONTENC set ' + @periodo_colA + ' = '
> > + CONVERT(varchar(20), @periodo_temp3)
> > +'where factura_id =' + @facturas_id2)
> >
> > plan9 wrote:
> > > --criar tabela temporaria
> > > !--------------------------------------------------------------------
> > > CREATE TABLE #TAB2 (
> > > facturas_id int,
> > > tipo_doc varchar(10) null,
> > > txtDescricao varchar(50) null,
> > > dteDataEmissao smalldatetime null,
> > > dteDataVencimento smalldatetime null,
> > > dteDiaJuro smalldatetime null,
> > > valor_fact decimal(12,2) null,
> > > valor_pag decimal(12,2) null,
> > > nTaxaJuro decimal (5,3)null,
> > > nValorJuro4 decimal(23,13) null
> > > )
> > >
> > >
> > > "Stu" wrote:
> > >
> > > > Thanks for the code; where do you create the temporary table #TAB2?
> > > >
> > > > Stu
> > > >
> > > > plan9 wrote:
> > > > > 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;
> > > > >
> > > > >
> > > > >
> > > > >         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
> > > > >
> > > > >
> > > > > "Stu" wrote:
> > > > >
> > > > > > error 156 means that your T-SQL code is incorrect; without more code,
> > > > > > there's little advice anyone can offer you.
> > > > > >
> > > > > > Stu
> > > > > >
> > > > > > plan9 wrote:
> > > > > > > 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
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >

28 Jul 2006 10:28 PM

plan9

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

Show quote

"Stu" wrote:

> comment out the exec statements, and use PRINT statements to see what
> the finaly dynamic SQL statement is saying, eg.:
>
> PRINT 'update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp3
> + 'where factura_id =' + @facturas_id2
>
> You need to convert to varchar all of the variables that are outside
> the SQL string (eg, @periodo_temp3 and @facturas_id2). You may also
> want to combine your two update statements into one.
>
> I haven't even given you the standard lecture on don't use cursors and
> avoid dynamic SQL unless its absolutely necessary because I assumed
> that your design called for it; however, you may want to step back from
> this problem and try to rethink it a bit.
>
> Stu
>
> plan9 wrote:
> > Sorry Stu, you are right, 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)
> >
> >
> > but the instrut that you suggest isn't working.
> >
> > "Stu" wrote:
> >
> > > I think your problem is here:
> > >
> > >                 --exec('update ##CONTENC set ' + @periodo_colA + ' = '
> > > + @periodo_temp3
> > > +'where factura_id =' + @facturas_id2)
> > >
> > > @periodo_temp3 is a decimal, which means that the above statement will
> > > not parse correctly. Try:
> > >
> > >                 --exec('update ##CONTENC set ' + @periodo_colA + ' = '
> > > + CONVERT(varchar(20), @periodo_temp3)
> > > +'where factura_id =' + @facturas_id2)
> > >
> > > plan9 wrote:
> > > > --criar tabela temporaria
> > > > !--------------------------------------------------------------------
> > > > CREATE TABLE #TAB2 (
> > > > facturas_id int,
> > > > tipo_doc varchar(10) null,
> > > > txtDescricao varchar(50) null,
> > > > dteDataEmissao smalldatetime null,
> > > > dteDataVencimento smalldatetime null,
> > > > dteDiaJuro smalldatetime null,
> > > > valor_fact decimal(12,2) null,
> > > > valor_pag decimal(12,2) null,
> > > > nTaxaJuro decimal (5,3)null,
> > > > nValorJuro4 decimal(23,13) null
> > > > )
> > > >
> > > >
> > > > "Stu" wrote:
> > > >
> > > > > Thanks for the code; where do you create the temporary table #TAB2?
> > > > >
> > > > > Stu
> > > > >
> > > > > plan9 wrote:
> > > > > > 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;
> > > > > >
> > > > > >
> > > > > >
> > > > > >         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
> > > > > >
> > > > > >
> > > > > > "Stu" wrote:
> > > > > >
> > > > > > > error 156 means that your T-SQL code is incorrect; without more code,
> > > > > > > there's little advice anyone can offer you.
> > > > > > >
> > > > > > > Stu
> > > > > > >
> > > > > > > plan9 wrote:
> > > > > > > > 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
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>
>

29 Jul 2006 12:55 AM

Steve Kass

plan9,

Did you print the query like Stu suggested? You will almost certainly
see the error once you look at the query that is causing the error.

SK

plan9 wrote:

Show quote

>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....
>
>
>"Stu" wrote:
>
>
>
>>comment out the exec statements, and use PRINT statements to see what
>>the finaly dynamic SQL statement is saying, eg.:
>>
>>PRINT 'update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp3
>>+ 'where factura_id =' + @facturas_id2
>>
>>You need to convert to varchar all of the variables that are outside
>>the SQL string (eg, @periodo_temp3 and @facturas_id2). You may also
>>want to combine your two update statements into one.
>>
>>I haven't even given you the standard lecture on don't use cursors and
>>avoid dynamic SQL unless its absolutely necessary because I assumed
>>that your design called for it; however, you may want to step back from
>>this problem and try to rethink it a bit.
>>
>>Stu
>>
>>plan9 wrote:
>>
>>
>>>Sorry Stu, you are right, 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)
>>>
>>>
>>>but the instrut that you suggest isn't working.
>>>
>>>"Stu" wrote:
>>>
>>>
>>>
>>>>I think your problem is here:
>>>>
>>>>                --exec('update ##CONTENC set ' + @periodo_colA + ' = '
>>>>+ @periodo_temp3
>>>>+'where factura_id =' + @facturas_id2)
>>>>
>>>>@periodo_temp3 is a decimal, which means that the above statement will
>>>>not parse correctly. Try:
>>>>
>>>>                --exec('update ##CONTENC set ' + @periodo_colA + ' = '
>>>>+ CONVERT(varchar(20), @periodo_temp3)
>>>>+'where factura_id =' + @facturas_id2)
>>>>
>>>>plan9 wrote:
>>>>
>>>>
>>>>>--criar tabela temporaria
>>>>>!--------------------------------------------------------------------
>>>>>CREATE TABLE #TAB2 (
>>>>>facturas_id int,
>>>>>tipo_doc varchar(10) null,
>>>>>txtDescricao varchar(50) null,
>>>>>dteDataEmissao smalldatetime null,
>>>>>dteDataVencimento smalldatetime null,
>>>>>dteDiaJuro smalldatetime null,
>>>>>valor_fact decimal(12,2) null,
>>>>>valor_pag decimal(12,2) null,
>>>>>nTaxaJuro decimal (5,3)null,
>>>>>nValorJuro4 decimal(23,13) null
>>>>>)
>>>>>
>>>>>
>>>>>"Stu" wrote:
>>>>>
>>>>>
>>>>>
>>>>>>Thanks for the code; where do you create the temporary table #TAB2?
>>>>>>
>>>>>>Stu
>>>>>>
>>>>>>plan9 wrote:
>>>>>>
>>>>>>
>>>>>>>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;
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>        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
>>>>>>>
>>>>>>>
>>>>>>>"Stu" wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>error 156 means that your T-SQL code is incorrect; without more code,
>>>>>>>>there's little advice anyone can offer you.
>>>>>>>>
>>>>>>>>Stu
>>>>>>>>
>>>>>>>>plan9 wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>>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
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>

2 Aug 2006 3:14 PM

plan9

Yes I did, and the query was with the intended result, is it a bug or
something, i runout of explanations....

Show quote

"Steve Kass" wrote:

> plan9,
>
> Did you print the query like Stu suggested? You will almost certainly
> see the error once you look at the query that is causing the error.
>
> SK
>
> plan9 wrote:
>
> >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....
> >
> >
> >"Stu" wrote:
> >
> >
> >
> >>comment out the exec statements, and use PRINT statements to see what
> >>the finaly dynamic SQL statement is saying, eg.:
> >>
> >>PRINT 'update ##CONTENC set ' + @periodo_colA + ' = ' + @periodo_temp3
> >>+ 'where factura_id =' + @facturas_id2
> >>
> >>You need to convert to varchar all of the variables that are outside
> >>the SQL string (eg, @periodo_temp3 and @facturas_id2). You may also
> >>want to combine your two update statements into one.
> >>
> >>I haven't even given you the standard lecture on don't use cursors and
> >>avoid dynamic SQL unless its absolutely necessary because I assumed
> >>that your design called for it; however, you may want to step back from
> >>this problem and try to rethink it a bit.
> >>
> >>Stu
> >>
> >>plan9 wrote:
> >>
> >>
> >>>Sorry Stu, you are right, 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)
> >>>
> >>>
> >>>but the instrut that you suggest isn't working.
> >>>
> >>>"Stu" wrote:
> >>>
> >>>
> >>>
> >>>>I think your problem is here:
> >>>>
> >>>>                --exec('update ##CONTENC set ' + @periodo_colA + ' = '
> >>>>+ @periodo_temp3
> >>>>+'where factura_id =' + @facturas_id2)
> >>>>
> >>>>@periodo_temp3 is a decimal, which means that the above statement will
> >>>>not parse correctly. Try:
> >>>>
> >>>>                --exec('update ##CONTENC set ' + @periodo_colA + ' = '
> >>>>+ CONVERT(varchar(20), @periodo_temp3)
> >>>>+'where factura_id =' + @facturas_id2)
> >>>>
> >>>>plan9 wrote:
> >>>>
> >>>>
> >>>>>--criar tabela temporaria
> >>>>>!--------------------------------------------------------------------
> >>>>>CREATE TABLE #TAB2 (
> >>>>>facturas_id int,
> >>>>>tipo_doc varchar(10) null,
> >>>>>txtDescricao varchar(50) null,
> >>>>>dteDataEmissao smalldatetime null,
> >>>>>dteDataVencimento smalldatetime null,
> >>>>>dteDiaJuro smalldatetime null,
> >>>>>valor_fact decimal(12,2) null,
> >>>>>valor_pag decimal(12,2) null,
> >>>>>nTaxaJuro decimal (5,3)null,
> >>>>>nValorJuro4 decimal(23,13) null
> >>>>>)
> >>>>>
> >>>>>
> >>>>>"Stu" wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Thanks for the code; where do you create the temporary table #TAB2?
> >>>>>>
> >>>>>>Stu
> >>>>>>
> >>>>>>plan9 wrote:
> >>>>>>
> >>>>>>
> >>>>>>>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;
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>        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
> >>>>>>>
> >>>>>>>
> >>>>>>>"Stu" wrote:
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>>error 156 means that your T-SQL code is incorrect; without more code,
> >>>>>>>>there's little advice anyone can offer you.
> >>>>>>>>
> >>>>>>>>Stu
> >>>>>>>>
> >>>>>>>>plan9 wrote:
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>>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
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>
> >>>>>>
> >>>>
> >>>>
> >>
> >>
>

27 Jul 2006 4:03 AM

Vern Rabe

Try this:

SELECT @periodo_temp2 = SUM(nValorJuro4)
FROM #TAB2
WHERE dteDiaJuro BETWEEN @periodo_inicio AND @periodo_fim;

HTH
Vern Rabe

Show quote

"plan9" wrote:

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

27 Jul 2006 8:47 AM

plan9

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

Show quote

"Vern Rabe" wrote:

> Try this:
>
> SELECT @periodo_temp2 = SUM(nValorJuro4)
> FROM #TAB2
> WHERE dteDiaJuro BETWEEN @periodo_inicio AND @periodo_fim;
>
> HTH
> Vern Rabe
>
> "plan9" wrote:
>
> > 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
> >

28 Jul 2006 10:39 PM

Chris Lim

plan9 wrote:

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

What is the dynamic SQL you are trying to executing at the time?

2 Aug 2006 3:18 PM

plan9

select @periodo_temp2 = sum(nValorJuro4) from #TAB2 where dteDiaJuro
between '+@x1+' and '+@x2

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

Show quote

"Chris Lim" wrote:

> plan9 wrote:
> > 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'.
>
> What is the dynamic SQL you are trying to executing at the time?
>
>