cancel
Showing results for 
Search instead for 
Did you mean: 

CONSULTA

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

como convierto de varchar a numerico

former_member203638
Active Contributor
0 Kudos

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)

                           

Former Member
0 Kudos

Ing que pena , mi conocimiento de SQL es poco

donde va exactamente el código de conversión

former_member203638
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

Gracias Alessandro  Esto fue de mucha ayuda

Answers (1)

Answers (1)

former_member203638
Active Contributor
0 Kudos

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.