cancel
Showing results for 
Search instead for 
Did you mean: 

PROBLEMA AL HACER UNION CON QUERYS.

Former Member
0 Kudos

10 PUNTOS A QUIEN ME RESPONDA.

QUE TAL HICE TRES QUERYS LAS CUELES CADA UNA POR SEPARADO ESTAN FUCNCIONANDO CORRESTAMENTE PERO QUIERO HACER UNION CON ELLAS , HAGO UNION CON LAS DOS PRIMERAS NO TENGO NINGUN PROBLEMA PERO AL HACER EL UNION CON MI TERCERA QUERY SI ME MARCA UN ERROR. NO SE QUE ESTOY HACIENDO MAL YA QUE MI EXPERIENCIA EN SQL ES MUY POBRE.

(SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[NumAtCard], T0.[FolioNum], T0.[CardCode], T0.[CardName],

T0.[ObjType], T0.[DocTotal] as DocTot, T0.[PaidToDate] as PaidtoD,

(T0.[DocTotal] - T0.[PaidToDate]) as Vigente, T0.[CtlAccount]as cuenta,

T0.[CntctCode], T1.[CreditLine], T1.[Phone1], T1.[Phone2], T1.[E_Mail], T0.[DocSubType],T2.[Name] FROM OPCH T0 LEFT JOIN OCRD T1 ON T0.CardCode = T1.CardCode

LEFT JOIN OCPR T2 ON T0.CntctCode = T2.CntctCode

WHERE

(T0.[CtlAccount]='20101010001'or T0.[CtlAccount]='20101020001' OR T0.[CtlAccount]='20101030001' )

AND T0.[DocStatus] = 'O')

UNION

(SELECT T3.[DocNum], T3.[DocDate], T3.[DocDueDate], T3.[NumAtCard], T3.[FolioNum], T3.[CardCode], T3.[CardName],

T3.[ObjType], (T3.[DocTotal] * -1) as DocTot, (T3.[PaidToDate] * -1) as PaidtoD,

((T3.[DocTotal] * -1) - (T3.[PaidToDate] * -1)) as Vigente, T3.[CtlAccount]as cuenta,

T3.[CntctCode], T4.[CreditLine], T4.[Phone1], T4.[Phone2], T4.[E_Mail], T3.[DocSubType], T5.[Name] FROM ORPC T3 LEFT JOIN OCRD T4 ON T3.CardCode = T4.CardCode

LEFT JOIN OCPR T5 ON T3.CntctCode = T5.CntctCode

WHERE

(T3.[CtlAccount]='20101010001'or T3.[CtlAccount]='20101020001' or T3.[CtlAccount]='20101030001' )

AND T3.[DocStatus] = 'O')

UNION

(SELECT T6.[DocNum], T6.[DocDate], T6.[DocDueDate], T6.[CounterRef], T6.[DocNum], T6.[CardCode], T6.[CardName],

T6.[ObjType], (T6.[OpenBal] * -1) as DocTot, (T6.[OpenBal] * -1) as PaidtoD,

(T6.[OpenBal] * -1) as Vigente,

T6.[BpAct], T7.[CreditLine], T7.[Phone1], T7.[Phone2], T7.[E_Mail], T6.[DocType], T8.[Name] FROM OVPM T6 LEFT JOIN OCRD T7 ON T6.CardCode = T7.CardCode

LEFT JOIN OCPR T8 ON T6.CntctCode = T8.CntctCode

WHERE

(T6.[BpAct]='20101010001'or T6.[BpAct]='20101020001' or T6.[BpAct]='20101030001' )

AND T6.[Canceled] = 'N' )

order by t6.cardcode desc

GRACIAS

Accepted Solutions (1)

Accepted Solutions (1)

former_member210784
Active Contributor
0 Kudos

Hola.

Estoy de acuerdo con lo comentado por Paul.

Intenta con este código, adicione una columna vacía en el tercer query para completar la cantidad de columnas:


(SELECT T0.DocNum, T0.DocDate, T0.DocDueDate, T0.NumAtCard, T0.FolioNum, T0.CardCode, T0.CardName,
T0.ObjType, T0.DocTotal as DocTot, T0.PaidToDate as PaidtoD, 
(T0.DocTotal - T0.PaidToDate) as Vigente, T0.CtlAccount as cuenta,
T0.CntctCode, T1.CreditLine, T1.Phone1, T1.Phone2, T1.E_Mail, T0.DocSubType,T2.Name 
FROM OPCH T0 LEFT JOIN OCRD T1 ON T0.CardCode = T1.CardCode
LEFT JOIN OCPR T2 ON T0.CntctCode = T2.CntctCode
WHERE
(T0.CtlAccount='20101010001'or T0.CtlAccount='20101020001' OR T0.CtlAccount='20101030001' )
AND T0.DocStatus = 'O')

UNION

(SELECT T3.DocNum, T3.DocDate, T3.DocDueDate, T3.NumAtCard, T3.FolioNum, T3.CardCode, T3.CardName, 
T3.ObjType, (T3.DocTotal * -1) as DocTot, (T3.PaidToDate * -1) as PaidtoD, 
((T3.DocTotal * -1) - (T3.PaidToDate * -1)) as Vigente, T3.CtlAccount as cuenta,
T3.CntctCode, T4.CreditLine, T4.Phone1, T4.Phone2, T4.E_Mail, T3.DocSubType, T5.Name 
FROM ORPC T3 LEFT JOIN OCRD T4 ON T3.CardCode = T4.CardCode 
LEFT JOIN OCPR T5 ON T3.CntctCode = T5.CntctCode
WHERE 
(T3.CtlAccount='20101010001'or T3.CtlAccount='20101020001' or T3.CtlAccount='20101030001' )
AND T3.DocStatus = 'O')

UNION

(SELECT T6.DocNum, T6.DocDate, T6.DocDueDate, T6.CounterRef, T6.DocNum, T6.CardCode, T6.CardName, 
T6.ObjType, (T6.OpenBal * -1) as DocTot, (T6.OpenBal * -1) as PaidtoD,
(T6.OpenBal * -1) as Vigente, T6.BpAct, 
'', T7.CreditLine, T7.Phone1, T7.Phone2, T7.E_Mail, T6.DocType, T8.Name 
FROM OVPM T6 LEFT JOIN OCRD T7 ON T6.CardCode = T7.CardCode 
LEFT JOIN OCPR T8 ON T6.CntctCode = T8.CntctCode
WHERE 
(T6.BpAct='20101010001'or T6.BpAct='20101020001' or T6.BpAct='20101030001' )
AND T6.Canceled = 'N' )
order by CardCode desc

Saludos.

Former Member
0 Kudos

MUCHAS GRACIAS FUNCIONO PERFECTAMENTE.

Answers (1)

Answers (1)

Former Member
0 Kudos

Hola...

2 Cosas, la primera es que si haces un union, los querys deben tener la misma cantidad de datos a mostrar (me parece que el tercer query tiene menos registros que los 2 primeros). La segunda observación es que los tipos de datos deben ser similares, por ejemplo, si tu primer query tuviera 3 datos del tipo numerico, numerico y fecha; el segundo query debe respetar ese mismo orden, es decir, si un segundo query para el union tuviera el orden numerico, fecha y numerico...entonces no funcionaría.

Espero te sea de ayuda

Saludos Cordiales,

Former Member
0 Kudos

MUCHAS GRACIAS POR TU ASESORIA GRACIAS A USTEDES ME AYUDO BASTANTE