on 05-07-2010 12:42 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
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.