Error en SQL (Divide by zero error encountered)

[Former Member]

Ayuda este es mi query he intentado resolverlo pero no encuentro la solución, gracias....

Select

T10.[# O/V],

T10.[Estado],

T10.[Fecha O/C],

T10.[SEMANA O/C],

T10.[Mes O/C],

T10.[Año O/C],

T10.[Bimestre O/C.],

T10.[Codigo],

T10.[Cliente],

T10.[Grupo Cliente],

T10.[Vendedor],

T10.[# O/C],

T10.[ESTADO COTIZ.],

ISNULL(SUM(ISNULL(T10.[Total Sin Dscto],0.00)),0.00) as 'Total Sin Dscto',

ISNULL(SUM(ISNULL(T10.[Dscto. Total],0.00)),0.00) as 'Dscto. Total',

ISNULL(SUM(ISNULL(T10.[Total],0.00)),0.00) as 'Total',

ISNULL(SUM(ISNULL(T10.[WAC Total],0.00)),0.00) as 'WAC Total'

from

(SELECT   

'# O/V' = v.docnum,

'Estado' = ([SBO_DYNAFLUX_PRODUCCION].[dbo].[FN_ESTADO_VENTA](v.DocEntry, lv.LineNum)),

'Fecha O/C' = v.U_INT_FECOC,

'SEMANA O/C'=DATEPART(wk, v.U_INT_FECOC),

'Mes O/C' = Month(v.u_int_fecoc),

'Año O/C' = Year(v.u_int_fecoc),

'Bimestre O/C.' = floor(((month(v.U_INT_FECOC)) - 1) / 2) + 1,

'Codigo' = c.CardCode,

'Cliente' = c.CardName,

'Grupo Cliente' = d.GroupName,

'Tipo Cliente' = ISNULL(c.U_HAK_TIPCLI,''),

'Vendedor' = vend.slpname,

'% Comision Vendedor' =

CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN 100.00 - ISNULL(v.U_HAK_PORVEN,0.00) ELSE 100.00 END,

'Cant. Vendedores' = CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN 2 ELSE 1 END,

--'Moneda Origen' = CASE v.cursource WHEN 'L' THEN 'SOL' ELSE v.DocCur END,

'ESTADO COTIZ.'=

CASE v.U_INT_ESTCOT

WHEN 'C' THEN 'CALIENTE'

WHEN 'F' THEN 'FRIO'

WHEN 'T' THEN 'TIBIO'

END,

'Total Sin Dscto' =

Round(

CASE lv.Currency

WHEN 'USD' THEN lv.PriceBefDi

WHEN 'EUR' THEN (lv.PriceBefDi*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/v.sysrate

ELSE lv.PriceBefDi / v.sysrate END * lv.Quantity /

(CASE WHEN len(isnull(v.U_INT_SEGVEN,

                       '')) > 0 THEN (100/(100 - isnull(v.u_HAK_PORVEN,0.00))) ELSE 1 END), 2),

'Dscto. Total' =

ROUND(lv.DiscPrcnt / 100 *

(

CASE lv.Currency

WHEN 'USD' THEN lv.PriceBefDi

WHEN 'EUR' THEN (lv.PriceBefDi*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/v.sysrate

ELSE lv.PriceBefDi / v.sysrate END)

                      * lv.Quantity / (CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN (100/(100 - isnull(v.u_HAK_PORVEN,0.00))) ELSE 1 END), 2),

'Total' =

round(

CASE v.DocCur

WHEN 'USD' THEN lv.TotalFrgn

WHEN 'EUR' THEN (lv.TotalFrgn*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/v.sysrate

ELSE lv.LineTotal / v.SysRate END / (CASE WHEN len(isnull(v.U_INT_SEGVEN, ''))

                      > 0 THEN (100/(100 - isnull(v.u_HAK_PORVEN,0.00))) ELSE 1 END), 2),

'# O/C' = v.U_INT_ORDCOM, v.U_INT_ESTCOT,

'Estado OV' =

                          (SELECT     descr

                            FROM          UFD1

                            WHERE      TableID = 'ORDR' AND FieldID = 65 AND FldValue = v.U_INT_ESTCOT), v.U_INT_FECOC, 'Fec. Despacho' =

                          (SELECT     TOP 1 DocDate

                            FROM          ODLN

                            WHERE      DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType), 'Mes Despacho' = Month

                          ((SELECT     TOP 1 DocDate

                              FROM         ODLN

                              WHERE     DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType)), 'Año Despacho' = YEAR

                          ((SELECT     TOP 1 DocDate

                              FROM         ODLN

                              WHERE     DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType)), 'Fec. Conta' = v.DocDate, 'Mes Fec. Conta' = Month(v.DocDate),

                      'Año Fec. Conta' = YEAR(v.DocDate), 'Fec. Doc.' = v.taxdate, 'Mes Fec' = MONTH(v.taxdate), 'Año. Doc.' = Year(v.taxdate), 'Bimestre Doc.' = floor(((month(v.taxdate)) - 1)

                       / 2) + 1, 'Marca' =

                          (SELECT     m.FirmName

                            FROM          omrc m

                            WHERE      i.FirmCode = m.FirmCode), 'Grupo ' = g.ItmsGrpNam, 'Sub Grupo' =

                          (SELECT     sg.U_INT_DESGRU

                            FROM          [@INT_SUBGRU] sg

                            WHERE      sg.Code = i.U_INT_SUBGRU), 'Unidad Estrategica' =

                          (SELECT     name

                            FROM          [@UNEST]

                            WHERE      code = g.u_unest), 'Modelo ' = U_INT_MODART, 'Código Art.' = lv.ItemCode, 'Desc. Art.' = lv.Dscription,

                      'Cantidad Solicitada' = lv.Quantity,

                      'WAC Total' = ROUND((i.AvgPrice /

                          (SELECT     Rate

                            FROM          ORTT

                            WHERE      currency = 'USD' AND RateDate = CONVERT(date, GETDATE())))*lv.Quantity, 2)

/* el valor se convierte a dolares con el t.c del di de hoy / NO APLICA AL 2DO VENDEDOR */

   FROM ORDR v INNER JOIN

                      RDR1 lv ON (v.DocEntry = lv.DocEntry) INNER JOIN

                      OSLP vend ON (vend.slpcode = v.slpcode) INNER JOIN

                      OITM i ON (lv.ItemCode = i.ItemCode) INNER JOIN

                      OITB g ON (i.ItmsGrpCod = g.ItmsGrpCod) INNER JOIN

                      OCRD c ON (v.CardCode = c.CardCode) INNER JOIN

                      OCRG d ON (c.GroupCode = d.GroupCode)

UNION ALL

/* union para el segundo vendedor*/

SELECT v.docnum,

'Estado' = ([SBO_DYNAFLUX_PRODUCCION].[dbo].[FN_ESTADO_VENTA](v.DocEntry, lv.LineNum)),

'Fecha O/C' = v.U_INT_FECOC,

'SEMANA O/C'=DATEPART(wk, v.U_INT_FECOC),

'Mes O/C' = Month(v.u_int_fecoc),

'Año O/C' = Year(v.u_int_fecoc),

'Bimestre O/C.' = floor(((month(v.U_INT_FECOC)) - 1) / 2) + 1,

'Codigo' = c.CardCode,

'Cliente' = c.CardName,

'Grupo Cliente' = d.GroupName,

'Tipo Cliente' = ISNULL(c.U_HAK_TIPCLI,''),

vend.slpname,

'% Comision Vendedor' =

CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN ISNULL(v.U_HAK_PORVEN,0.00) ELSE 0.00 END,

'NroVendedores' = CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN 2 ELSE 1 END,

--'Moneda' = CASE v.cursource WHEN 'L' THEN 'SOL' ELSE v.DocCur END,

'ESTADO COTIZ.'=

CASE v.U_INT_ESTCOT

WHEN 'C' THEN 'CALIENTE'

WHEN 'F' THEN 'FRIO'

WHEN 'T' THEN 'TIBIO'

END,

'TotalSinDscto' =

Round(

CASE lv.Currency

WHEN 'USD' THEN lv.PriceBefDi

WHEN 'EUR' THEN (lv.PriceBefDi*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/v.sysrate

ELSE lv.PriceBefDi / v.sysrate END * lv.Quantity / (CASE WHEN len(isnull(v.U_INT_SEGVEN,

                       '')) > 0 THEN (100/(CASE WHEN ISNULL(v.u_HAK_PORVEN,0.00) = 0.00 THEN 100 ELSE ISNULL(v.u_HAK_PORVEN,0.00) END)) ELSE 1 END) , 2),

'DsctTotal' =

ROUND(lv.DiscPrcnt / 100 *

(

CASE lv.Currency

WHEN 'USD' THEN lv.PriceBefDi

WHEN 'EUR' THEN (lv.PriceBefDi*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/v.sysrate

ELSE lv.PriceBefDi / v.sysrate END)

                      * lv.Quantity / (CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN (100/(CASE WHEN ISNULL(v.u_HAK_PORVEN,0.00) = 0.00 THEN 100 ELSE ISNULL(v.u_HAK_PORVEN,0.00) END)) ELSE 1 END) , 2),

'Total' =

round(

CASE v.DocCur

WHEN 'USD' THEN lv.TotalFrgn

WHEN 'EUR' THEN (lv.TotalFrgn*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/v.sysrate

ELSE lv.LineTotal / v.SysRate END / (CASE WHEN len(isnull(v.U_INT_SEGVEN, ''))

                      > 0 THEN (100/(CASE WHEN ISNULL(v.u_HAK_PORVEN,0.00) = 0.00 THEN 100 ELSE ISNULL(v.u_HAK_PORVEN,0.00) END))

                      ELSE 1 END) , 2),

                      v.U_INT_ORDCOM, v.U_INT_ESTCOT,

'Estado' =

                          (SELECT     descr

                            FROM          UFD1

                            WHERE      TableID = 'ORDR' AND FieldID = 65 AND FldValue = v.U_INT_ESTCOT), v.U_INT_FECOC, 'Fec. Despacho' =

                          (SELECT     TOP 1 DocDate

                            FROM          ODLN

                            WHERE      DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType), 'Mes Despacho' = Month

                          ((SELECT     TOP 1 DocDate

                              FROM         ODLN

                              WHERE     DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType)), 'Año Despacho' = YEAR

                          ((SELECT     TOP 1 DocDate

                              FROM         ODLN

                              WHERE     DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType)), 'Fec. Conta' = v.DocDate, 'Mes Fec. Conta' = Month(v.DocDate),

                      'Año Fec. Conta' = YEAR(v.DocDate), 'Fec. Doc.' = v.taxdate, 'Mes Fec' = MONTH(v.taxdate), 'Año. Doc.' = Year(v.taxdate), 'Bimestre Doc.' = floor(((month(v.taxdate)) - 1)

                       / 2) + 1, 'Marca' =

                          (SELECT     m.FirmName

                            FROM          omrc m

                            WHERE      i.FirmCode = m.FirmCode), g.ItmsGrpNam, 'SubGrupo' =

                          (SELECT     sg.U_INT_DESGRU

                            FROM          [@INT_SUBGRU] sg

                            WHERE      sg.Code = i.U_INT_SUBGRU), 'UnidadEstrategica' =

                          (SELECT     name

                            FROM          [@UNEST]

                            WHERE      code = g.u_unest), U_INT_MODART, lv.ItemCode, lv.Dscription,

                            'CantidadSolicitada' = 0,

                      'WAC Total' = 0

                      FROM ORDR v INNER JOIN

                      RDR1 lv ON (v.DocEntry = lv.DocEntry) INNER JOIN

                      OSLP vend ON vend.SlpCode = CONVERT(int, LEFT(v.U_INT_SEGVEN, CHARINDEX('-', v.U_INT_SEGVEN, 0) - 1)) INNER JOIN

                      OITM i ON (lv.ItemCode = i.ItemCode) INNER JOIN

                      OITB g ON (i.ItmsGrpCod = g.ItmsGrpCod) INNER JOIN

                      OCRD c ON (v.CardCode = c.CardCode) inner join

                      OCRG d ON (c.GroupCode = d.GroupCode)

                      ) T10

                      GROUP BY

                      T10.[# O/V],T10.[Estado],T10.[Fecha O/C],T10.[SEMANA O/C],T10.[Mes O/C],T10.[Año O/C],

T10.[Bimestre O/C.],T10.[Codigo],T10.[Cliente],T10.[Grupo Cliente],T10.[Vendedor],T10.[# O/C],T10.[ESTADO COTIZ.]

HAVING T10.[Estado] NOT IN ('Anulada con Devolucion','Anulada con N.C','Cancelada en O/V')

[Former Member]

Buenos días Alenxander,

el error que tienes es debido a que el divisor de alguna de la divisiones que haces es 0. la forma de resolverlo son 2, en un caso evaluamos que si el divisor es 0, nos devuelva valor nulo, y el otro formato es el uso del nullif

select case when DiscPrcnt=0 then null else DocTotal/DiscPrcnt end from oinv

o

select DocTotal/nullif(DiscPrcnt,0) from oinv

Espero que te ayude, un cordial saludo

[Former Member]

Hola Antonio

el nullif si lo he usado en la parte de los divisores pero aun me sigue saliendo error, también quisiera comentar que tengo dos bd una para pruebas donde ahí no tengo ningún error con ese query pero en la otra bd real que uso sin tengo error sabes a que se deba....

[Former Member]

Buenas tardes, podrias postear de nuevo, la select completa con los nullif incluidos

saludos

[Former Member]

Select

T10.[# O/V],

T10.[Estado],

T10.[Fecha O/C],

T10.[SEMANA O/C],

T10.[Mes O/C],

T10.[Año O/C],

T10.[Bimestre O/C.],

T10.[Codigo],

T10.[Cliente],

T10.[Grupo Cliente],

T10.[Vendedor],

T10.[# O/C],

T10.[ESTADO COTIZ.],

ISNULL(SUM(ISNULL(T10.[Total Sin Dscto],0.00)),0.00) as 'Total Sin Dscto',

ISNULL(SUM(ISNULL(T10.[Dscto. Total],0.00)),0.00) as 'Dscto. Total',

ISNULL(SUM(ISNULL(T10.[Total],0.00)),0.00) as 'Total',

ISNULL(SUM(ISNULL(T10.[WAC Total],0.00)),0.00) as 'WAC Total'

from

(SELECT   

'# O/V' = v.docnum,

'Estado' = ([SBO_DYNAFLUX_PRODUCCION].[dbo].[FN_ESTADO_VENTA](v.DocEntry, lv.LineNum)),

'Fecha O/C' = v.U_INT_FECOC,

'SEMANA O/C'=DATEPART(wk, v.U_INT_FECOC),

'Mes O/C' = Month(v.u_int_fecoc),

'Año O/C' = Year(v.u_int_fecoc),

'Bimestre O/C.' = floor(((month(v.U_INT_FECOC)) - 1) / 2) + 1,

'Codigo' = c.CardCode,

'Cliente' = c.CardName,

'Grupo Cliente' = d.GroupName,

'Tipo Cliente' = ISNULL(c.U_HAK_TIPCLI,''),

'Vendedor' = vend.slpname,

'% Comision Vendedor' =

CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN 100.00 - ISNULL(v.U_HAK_PORVEN,0.00) ELSE 100.00 END,

'Cant. Vendedores' = CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN 2 ELSE 1 END,

--'Moneda Origen' = CASE v.cursource WHEN 'L' THEN 'SOL' ELSE v.DocCur END,

'ESTADO COTIZ.'=

CASE v.U_INT_ESTCOT

WHEN 'C' THEN 'CALIENTE'

WHEN 'F' THEN 'FRIO'

WHEN 'T' THEN 'TIBIO'

END,

'Total Sin Dscto' =

Round(

CASE lv.Currency

WHEN 'USD' THEN lv.PriceBefDi

WHEN 'EUR' THEN (lv.PriceBefDi*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/nullif(v.sysrate,0.00)

ELSE lv.PriceBefDi / nullif(v.sysrate,0.00) END * lv.Quantity /

(CASE WHEN len(isnull(v.U_INT_SEGVEN,

                       '')) > 0 THEN (100/(100 - nullif(v.u_HAK_PORVEN,0.00))) ELSE 1 END), 2),

'Dscto. Total' =

ROUND(lv.DiscPrcnt / 100 *

(

CASE lv.Currency

WHEN 'USD' THEN lv.PriceBefDi

WHEN 'EUR' THEN (lv.PriceBefDi*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/nullif(v.sysrate,0.00)

ELSE lv.PriceBefDi / nullif(v.sysrate,0.00) END)

                      * lv.Quantity / (CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN (100/(100 - nullif(v.u_HAK_PORVEN,0.00))) ELSE 1 END), 2),

'Total' =

round(

CASE v.DocCur

WHEN 'USD' THEN lv.TotalFrgn

WHEN 'EUR' THEN (lv.TotalFrgn*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/nullif(v.sysrate,0.00)

ELSE lv.LineTotal / nullif(v.SysRate,0.00) END / (CASE WHEN len(isnull(v.U_INT_SEGVEN, ''))

                      > 0 THEN (100/(100 - nullif(v.u_HAK_PORVEN,0.00))) ELSE 1 END), 2),

'# O/C' = v.U_INT_ORDCOM, v.U_INT_ESTCOT,

'Estado OV' =

                          (SELECT     descr

                            FROM          UFD1

                            WHERE      TableID = 'ORDR' AND FieldID = 65 AND FldValue = v.U_INT_ESTCOT), v.U_INT_FECOC, 'Fec. Despacho' =

                          (SELECT     TOP 1 DocDate

                            FROM          ODLN

                            WHERE      DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType), 'Mes Despacho' = Month

                          ((SELECT     TOP 1 DocDate

                              FROM         ODLN

                              WHERE     DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType)), 'Año Despacho' = YEAR

                          ((SELECT     TOP 1 DocDate

                              FROM         ODLN

                              WHERE     DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType)), 'Fec. Conta' = v.DocDate, 'Mes Fec. Conta' = Month(v.DocDate),

                      'Año Fec. Conta' = YEAR(v.DocDate), 'Fec. Doc.' = v.taxdate, 'Mes Fec' = MONTH(v.taxdate), 'Año. Doc.' = Year(v.taxdate), 'Bimestre Doc.' = floor(((month(v.taxdate)) - 1)

                       / 2) + 1, 'Marca' =

                          (SELECT     m.FirmName

                            FROM          omrc m

                            WHERE      i.FirmCode = m.FirmCode), 'Grupo ' = g.ItmsGrpNam, 'Sub Grupo' =

                          (SELECT     sg.U_INT_DESGRU

                            FROM          [@INT_SUBGRU] sg

                            WHERE      sg.Code = i.U_INT_SUBGRU), 'Unidad Estrategica' =

                          (SELECT     name

                            FROM          [@UNEST]

                            WHERE      code = g.u_unest), 'Modelo ' = U_INT_MODART, 'Código Art.' = lv.ItemCode, 'Desc. Art.' = lv.Dscription,

                      'Cantidad Solicitada' = lv.Quantity,

                      'WAC Total' = ROUND((i.AvgPrice /

                          (SELECT     Rate

                            FROM          ORTT

                            WHERE      currency = 'USD' AND RateDate = CONVERT(date, GETDATE())))*lv.Quantity, 2)FROM ORDR v INNER JOIN

                      RDR1 lv ON (v.DocEntry = lv.DocEntry) INNER JOIN

                      OSLP vend ON (vend.slpcode = v.slpcode) INNER JOIN

                      OITM i ON (lv.ItemCode = i.ItemCode) INNER JOIN

                      OITB g ON (i.ItmsGrpCod = g.ItmsGrpCod) INNER JOIN

                      OCRD c ON (v.CardCode = c.CardCode) INNER JOIN

                      OCRG d ON (c.GroupCode = d.GroupCode)

UNION ALL

/* union para el segundo vendedor*/

SELECT v.docnum,

'Estado' = ([SBO_DYNAFLUX_PRODUCCION].[dbo].[FN_ESTADO_VENTA](v.DocEntry, lv.LineNum)),

'Fecha O/C' = v.U_INT_FECOC,

'SEMANA O/C'=DATEPART(wk, v.U_INT_FECOC),

'Mes O/C' = Month(v.u_int_fecoc),

'Año O/C' = Year(v.u_int_fecoc),

'Bimestre O/C.' = floor(((month(v.U_INT_FECOC)) - 1) / 2) + 1,

'Codigo' = c.CardCode,

'Cliente' = c.CardName,

'Grupo Cliente' = d.GroupName,

'Tipo Cliente' = ISNULL(c.U_HAK_TIPCLI,''),

vend.slpname,

'% Comision Vendedor' =

CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN ISNULL(v.U_HAK_PORVEN,0.00) ELSE 0.00 END,

'NroVendedores' = CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN 2 ELSE 1 END,

--'Moneda' = CASE v.cursource WHEN 'L' THEN 'SOL' ELSE v.DocCur END,

'ESTADO COTIZ.'=

CASE v.U_INT_ESTCOT

WHEN 'C' THEN 'CALIENTE'

WHEN 'F' THEN 'FRIO'

WHEN 'T' THEN 'TIBIO'

END,

'TotalSinDscto' =

Round(

CASE lv.Currency

WHEN 'USD' THEN lv.PriceBefDi

WHEN 'EUR' THEN (lv.PriceBefDi*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/nullif(v.sysrate,0.00)

ELSE lv.PriceBefDi / nullif(v.sysrate,0.00) END * lv.Quantity / (CASE WHEN len(isnull(v.U_INT_SEGVEN,

                       '')) > 0 THEN (100/(CASE WHEN nullif(v.u_HAK_PORVEN,0.00) = 0.00 THEN 100 ELSE nullif(v.u_HAK_PORVEN,0.00) END)) ELSE 1 END) , 2),

'DsctTotal' =

ROUND(lv.DiscPrcnt / 100 *

(

CASE lv.Currency

WHEN 'USD' THEN lv.PriceBefDi

WHEN 'EUR' THEN (lv.PriceBefDi*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/nullif(v.sysrate,0.00)

ELSE lv.PriceBefDi / nullif(v.sysrate,0.00) END)

                      * lv.Quantity / (CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN (100/(CASE WHEN nullif(v.u_HAK_PORVEN,0.00) = 0.00 THEN 100 ELSE nullif(v.u_HAK_PORVEN,0.00) END)) ELSE 1 END) , 2),

'Total' =

round(

CASE v.DocCur

WHEN 'USD' THEN lv.TotalFrgn

WHEN 'EUR' THEN (lv.TotalFrgn*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/nullif(v.sysrate,0.00)

ELSE lv.LineTotal / nullif(v.SysRate,0.00) END / (CASE WHEN len(isnull(v.U_INT_SEGVEN, ''))

                      > 0 THEN (100/(CASE WHEN nullif(v.u_HAK_PORVEN,0.00) = 0.00 THEN 100 ELSE nullif(v.u_HAK_PORVEN,0.00) END))

                      ELSE 1 END) , 2),

                      v.U_INT_ORDCOM, v.U_INT_ESTCOT,

'Estado' =

                          (SELECT     descr

                            FROM          UFD1

                            WHERE      TableID = 'ORDR' AND FieldID = 65 AND FldValue = v.U_INT_ESTCOT), v.U_INT_FECOC, 'Fec. Despacho' =

                          (SELECT     TOP 1 DocDate

                            FROM          ODLN

                            WHERE      DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType), 'Mes Despacho' = Month

                          ((SELECT     TOP 1 DocDate

                              FROM         ODLN

                              WHERE     DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType)), 'Año Despacho' = YEAR

                          ((SELECT     TOP 1 DocDate

                              FROM         ODLN

                              WHERE     DocEntry = lv.TrgetEntry AND ObjType = lv.TargetType)), 'Fec. Conta' = v.DocDate, 'Mes Fec. Conta' = Month(v.DocDate),

                      'Año Fec. Conta' = YEAR(v.DocDate), 'Fec. Doc.' = v.taxdate, 'Mes Fec' = MONTH(v.taxdate), 'Año. Doc.' = Year(v.taxdate), 'Bimestre Doc.' = floor(((month(v.taxdate)) - 1)

                       / 2) + 1, 'Marca' =

                          (SELECT     m.FirmName

                            FROM          omrc m

                            WHERE      i.FirmCode = m.FirmCode), g.ItmsGrpNam, 'SubGrupo' =

                          (SELECT     sg.U_INT_DESGRU

                            FROM          [@INT_SUBGRU] sg

                            WHERE      sg.Code = i.U_INT_SUBGRU), 'UnidadEstrategica' =

                          (SELECT     name

                            FROM          [@UNEST]

                            WHERE      code = g.u_unest), U_INT_MODART, lv.ItemCode, lv.Dscription,

                            'CantidadSolicitada' = 0,

                      'WAC Total' = 0

   FROM ORDR v INNER JOIN

                      RDR1 lv ON (v.DocEntry = lv.DocEntry) INNER JOIN

                      OSLP vend ON vend.SlpCode = CONVERT(int, LEFT(v.U_INT_SEGVEN, CHARINDEX('-', v.U_INT_SEGVEN, 0) - 1)) INNER JOIN

                      OITM i ON (lv.ItemCode = i.ItemCode) INNER JOIN

                      OITB g ON (i.ItmsGrpCod = g.ItmsGrpCod) INNER JOIN

                      OCRD c ON (v.CardCode = c.CardCode) inner join

                      OCRG d ON (c.GroupCode = d.GroupCode)

                      ) T10

                      GROUP BY

                      T10.[# O/V],T10.[Estado],T10.[Fecha O/C],T10.[SEMANA O/C],T10.[Mes O/C],T10.[Año O/C],

T10.[Bimestre O/C.],T10.[Codigo],T10.[Cliente],T10.[Grupo Cliente],T10.[Vendedor],T10.[# O/C],T10.[ESTADO COTIZ.]

HAVING T10.[Estado] NOT IN ('Anulada con Devolucion','Anulada con N.C','Cancelada en O/V')

[Former Member]

Buenas tardes,

dentro de la primera parte de la select veo que hay un punto donde no tienes informado el nullif

ROUND((i.AvgPrice /

nullif(                           (SELECT     Rate                             FROM          ORTT                             WHERE      currency = 'USD' AND RateDate = CONVERT(date, GETDATE())))*lv.Quantity, 2),0) FROM ORDR

saludos

[Former Member]

no me percate de esa división pero igual ya lo probé y me sigue saliendo error..

[Former Member]

Buenas tardes Alexander, creo que deberias de ir punteando las diferentes zonas del codigo para detectar donde tienes ese problema.

un saludo

[Former Member]

hola antonio el error viene de aqui..

'TotalSinDscto' =

Round(

CASE lv.Currency

WHEN 'USD' THEN lv.PriceBefDi

WHEN 'EUR' THEN (lv.PriceBefDi*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/v.sysrate

ELSE lv.PriceBefDi / v.sysrate END * lv.Quantity / (CASE WHEN len(isnull(v.U_INT_SEGVEN,

                       '')) > 0 THEN (100/(CASE WHEN ISNULL(v.u_HAK_PORVEN,0.00) = 0.00 THEN 100 ELSE ISNULL(v.u_HAK_PORVEN,0.00) END)) ELSE 1 END) , 2),

'DsctTotal' =

ROUND(lv.DiscPrcnt / 100 *

(

CASE lv.Currency

WHEN 'USD' THEN lv.PriceBefDi

WHEN 'EUR' THEN (lv.PriceBefDi*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/v.sysrate

ELSE lv.PriceBefDi / v.sysrate END)

                      * lv.Quantity / (CASE WHEN len(isnull(v.U_INT_SEGVEN, '')) > 0 THEN (100/(CASE WHEN ISNULL(v.u_HAK_PORVEN,0.00) = 0.00 THEN 100 ELSE ISNULL(v.u_HAK_PORVEN,0.00) END)) ELSE 1 END) , 2),

'Total' =

round(

CASE v.DocCur

WHEN 'USD' THEN lv.TotalFrgn

WHEN 'EUR' THEN (lv.TotalFrgn*(Select Rate from ORTT where RateDate = v.CreateDate and Currency = 'EUR'))/v.sysrate

ELSE lv.LineTotal / v.SysRate END / (CASE WHEN len(isnull(v.U_INT_SEGVEN, ''))

                      > 0 THEN (100/(CASE WHEN ISNULL(v.u_HAK_PORVEN,0.00) = 0.00 THEN 100 ELSE ISNULL(v.u_HAK_PORVEN,0.00) END))

                      ELSE 1 END) , 2),

[Former Member]

perfecto, espero haberte ayudado...

si ya esta cierra este post

gracias