on 04-01-2013 11:17 PM
Buenas Tardes
al ejecutar la siguiente consulta en SAP o en SQL sale con errores
me podrian ayudar por favor gracias
SELECT T0.[CardCode] as Cliente, T2.[CardName] as 'Nombre cliente', T0.[SlpCode] as Vendedor, T3.[SlpName] as 'Nombre vendedor', T4.[ItmsGrpCod] as Linea, T5.[ItmsGrpNam] as 'Nombre linea', T4.[QryGroup10] as 'Pnuevos', T4.[QryGroup14] as 'Promociones', T1.[ItemCode] as Referencia, T4.[ItemName] as 'Nombre referencia', T4.[SalPackUn] as 'Unidad Venta', T4.[QryGroup11] as 'Pventaeimportados', T4.[QryGroup12] as 'Fumigadorasespalda20/16', case when T4.[QryGroup12]= 'Y ' then 'Fumigadorasespalda20/16' else T4.[ItemName] end as Linea, T1.[Quantity] as Cantidad, T1.[LineTotal] - (T0.[DiscPrcnt] * T1.[LineTotal] / 100) as Total, T1.[Quantity] * T4.[SalPackUn] as 'Cantidad U', YEAR(T0.[DocDate]) as Periodo, DATENAME(month,T0.[DocDate]) as Mes
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode INNER JOIN OITM T4 ON T1.ItemCode = T4.ItemCode INNER JOIN OITB T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod
UNION ALL
SELECT T0.[CardCode], T2.[CardName], T0.[SlpCode], T3.[SlpName], T4.[ItmsGrpCod], case when T4.[QryGroup12]= 'Y ' then 'Fumigadorasespalda20/16' else T4.[ItemName], T5.[ItmsGrpNam], T4.[QryGroup10], T1.[ItemCode], T4.[ItemName], T4.[SalPackUn], T4.[QryGroup11], T4.[QryGroup12], T4.[QryGroup14], T1.[Quantity]*-1, (T1.[LineTotal] - (T0.[DiscPrcnt] * T1.[LineTotal] / 100))*-1, (T1.[Quantity] * T4.[SalPackUn])*-1, YEAR(T0.[DocDate]), DATENAME(month,T0.[DocDate])
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode INNER JOIN OITM T4 ON T1.ItemCode = T4.ItemCode INNER JOIN OITB T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod
Si era eso .. pero ahora al ejecutar sale el error que dice : error al convertir tipo de datos varchar a numérico
Agradezco la ayuda
Gracias.....
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Que raro, aqui funciono bien tanto en SQL como en b1, puedes comentar la parte que menciono Hector de (T4.[SalPackUn]) (que por cierto no veo su post) y probar si es lo que te esta dando el error para que sepas en donde vas a usar la conversion. Intenta con isnull(Convert(Numeric(20,2)Tu Campo),0)
Intenta asi:
SELECT
T0.[CardCode] as Cliente,
T2.[CardName] as 'Nombre cliente',
T0.[SlpCode] as Vendedor,
T3.[SlpName] as 'Nombre vendedor',
T4.[ItmsGrpCod] as 'Linea',
T5.[ItmsGrpNam] as 'Nombre linea',
T4.[QryGroup10] as 'Pnuevos',
T4.[QryGroup14] as 'Promociones',
T1.[ItemCode] as 'Referencia',
T4.[ItemName] as 'Nombre referencia',
T4.[SalPackUn] as 'Unidad Venta',
T4.[QryGroup11] as 'Pventaeimportados',
T4.[QryGroup12] as 'Fumigadorasespalda20/16',
case when T4.[QryGroup12]= 'Y ' then 'Fumigadorasespalda20/16' else T4.[ItemName] end as Linea,
T1.[Quantity] as Cantidad,
T1.[LineTotal] - (T0.[DiscPrcnt] * T1.[LineTotal] / 100) as Total,
T1.[Quantity] * T4.[SalPackUn] as 'Cantidad U',
YEAR(T0.[DocDate]) as Periodo,
DATENAME(month,T0.[DocDate]) as Mes
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode
INNER JOIN OITM T4 ON T1.ItemCode = T4.ItemCode
INNER JOIN OITB T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod
UNION ALL
SELECT
T0.[CardCode],
T2.[CardName],
T0.[SlpCode],
T3.[SlpName],
T4.[ItmsGrpCod],
T5.[ItmsGrpNam],
T4.[QryGroup10],
T4.[QryGroup14],
T1.[ItemCode],
T4.[ItemName],
T4.[SalPackUn],
T4.[QryGroup11],
T4.[QryGroup12],
case when T4.[QryGroup12]= 'Y ' then 'Fumigadorasespalda20/16' else T4.[ItemName] End,
T1.[Quantity]*-1,
(T1.[LineTotal] - (T0.[DiscPrcnt] * T1.[LineTotal] / 100))*-1,
(T1.[Quantity] * T4.[SalPackUn])*-1,
YEAR(T0.[DocDate]),
DATENAME(month,T0.[DocDate])
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode
INNER JOIN OITM T4 ON T1.ItemCode = T4.ItemCode
INNER JOIN OITB T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod
Si te arroja algun error, deberás comentar o quitar cada campo para ver donde te esta arrojando el error de conversion, te repito aqui no me esta arrojando ningun error.
Saludos.
Alessandro.
En el segundo query en esta parte: case when T4.[QryGroup12]= 'Y ' then 'Fumigadorasespalda20/16' else T4.[ItemName] End, te falta poner el "End,"
Saludos.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.