on 10-02-2012 10:26 PM
tengo el siguiente query:
--/*SELECT FROM [dbo].[ORCT] B0*/
--DECLARE @FechaInicio AS DATETIME
--/* WHERE */
--SET @FechaInicio = /* B0.DocDate */ '[%0]'
--/*SELECT FROM [dbo].[ORCT] C0*/
--DECLARE @FechaFin AS DATETIME
--/* WHERE */
--SET @FechaFin = /* C0.DocDate */ '[%1]'
declare @fechaInicio datetime,
@fechaFin datetime
set @fechaInicio = '20121001'
set @fechaFin = '20121002'
select t0.DocNum,
t1.TrgetEntry,
t1.ItemCode,
t0.SlpCode as CodigoVendedor,
t4.slpname as NombreVendedor,
t6.itmsgrpnam as Grupo,
(select t10.[Descr]from UFD1 T10 where t10.FldValue = t5.U_SubGrupo and
t10.FieldID=4 and t10.TableID='OITM' ) as NombreSubGrupo,
isnull(t1.GTotalFC,0)-((isnull(t1.GTotalFC,0)*isnull(t0.DiscPrcnt,0))/100) as 'TotalVendido_Factura',
t1.StockPrice,
--isnull(t1.GTotalFC,0)-((isnull(t1.GTotalFC,0)*isnull(t0.DiscPrcnt,0))/100) as TotalCobrado,
t5.U_SubGrupo--,t10.Descr
from OINV t0
left join INV1 t1 on t1.DocEntry=t0.DocEntry
left join RCT2 t2 on t2.DocEntry=t0.DocEntry
left join ORCT t3 on t3.DocEntry=t2.DocNum
left join OSLP t4 on t4.SlpCode=t0.SlpCode
left join OITM t5 on t5.ItemCode=t1.ItemCode
left join oitb t6 on t6.ItmsGrpCod=t5.ItmsGrpCod
left join ORIN t8 on t8.GroupNum = t0.GroupNum
left join RIN1 t9 on t9.DocEntry =t8.DocEntry
left join UFD1 t10 on t10.FldValue =t5.U_SubGrupo
where t0.DocDate between @fechaInicio and @fechaFin
group by t6.ItmsGrpCod,
t0.DocNum,
t1.TrgetEntry,
t1.ItemCode,
t0.SlpCode,
t4.SlpName,
t6.itmsgrpnam,
t1.GTotalFC,
t0.DiscPrcnt,
t1.StockPrice,
t5.U_SubGrupo
UNION
--declare @fechaInicio datetime,
-- @fechaFin datetime
--set @fechaInicio = '20121001'
--set @fechaFin = '20121002'
select
t0.DocNum,
t1.TrgetEntry,
t1.ItemCode,
t0.SlpCode as CodigoVendedor,
t4.slpname as NombreVendedor,
t6.itmsgrpnam as Grupo,
t1.StockPrice ,
(select t10.[Descr]from UFD1 T10 where t10.FldValue = t5.U_SubGrupo and
t10.FieldID=4 and t10.TableID='OITM' ) as NombreSubGrupo,
(isnull(t1.GTotalFC,0)-((isnull(t1.GTotalFC,0)*isnull(t0.DiscPrcnt,0))/100))*-1 as 'TotalVendido_Factura',
--isnull(t1.GTotalFC,0)-((isnull(t1.GTotalFC,0)*isnull(t0.DiscPrcnt,0))/100) as TotalCobrado,
t5.U_SubGrupo--,t10.Descr
from ORIN t0
left join RIN1 t1 on t1.DocEntry=t0.DocEntry
left join RCT2 t2 on t2.DocEntry=t0.DocEntry
left join ORCT t3 on t3.DocEntry=t2.DocNum
left join OSLP t4 on t4.SlpCode=t0.SlpCode
left join OITM t5 on t5.ItemCode=t1.ItemCode
left join oitb t6 on t6.ItmsGrpCod=t5.ItmsGrpCod
left join ORIN t8 on t8.GroupNum = t0.GroupNum
left join RIN1 t9 on t9.DocEntry =t8.DocEntry
left join UFD1 t10 on t10.FldValue =t5.U_SubGrupo
where t0.DocDate between @fechaInicio and @fechaFin
group by t6.ItmsGrpCod,
t0.DocNum,
t1.TrgetEntry,
t1.ItemCode,
t0.SlpCode,
t4.SlpName,
t6.itmsgrpnam,
t1.GTotalFC,
t0.DiscPrcnt,
t1.StockPrice,
t5.U_SubGrupo
donde u subgrupo es un campo de usuario, toda la consulta me funcionaba de maravilla hasta que me pidieron incluir en la misma el costo del item vendido
t1.stockprice
cuando veo las ventas funciona bien, si veo las notas de credito tambien
el problema es al hacer el union que me dice error
Msg 8114, Level 16, State 5, Line 6
Error converting data type nvarchar to numeric.
alguien que me de una manito
Hola Martín,
Ese mensaje de error se genera debido a que una columna de la primera consulta presenta un formato de campo diferente al mismo número de columna en la segunda consulta.
El comando Union All solo funciona cuando el número de campos es el mismo y su tipo de datos es idéntico.
Sugiero que las ejecutes separadas y te fijes bien en que campo se te cambió la estructura de los datos.
Saludos,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Agrega un campo NULL en el union.
Ejemplo
SELECT T0.TransId, T0.RefDate, T0.Credit, T0.Debit, T0.LineMemo
FROM JDT1 T0
UNION ALL
SELECT T0.TransId, T0.RefDate, NULL, NULL, T0.LineMemo
FROM JDT1 T0
Saludos
Felipe
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.