UNION ALL
Publicado por reki12 (3 intervenciones) el 05/09/2014 17:08:06
estoy haciendo un union en la misma tabla es decir, quiero duplicar la consulta del select de la misma tabla pero me esta salio un error en management tool de sql que dice asi, lo quiero asi porque estoy haciendo un formato de factura por medio de crystal, y quiero que una hoja salgan dos frecibos media carta, ya el union all lo hice y se encuentra dentro del codigo, que aspecto estoy obviando?
Msg 104, Level 16, State 1, Procedure RepReciboPagoDATATECHT2, Line 206
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
Msg 104, Level 16, State 1, Procedure RepReciboPagoDATATECHT2, Line 214
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
-----
Basicamente la consulta seria algo asi:
Msg 104, Level 16, State 1, Procedure RepReciboPagoDATATECHT2, Line 206
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
Msg 104, Level 16, State 1, Procedure RepReciboPagoDATATECHT2, Line 214
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
-----
Basicamente la consulta seria algo asi:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
SELECT
e.cod_emp AS codemp1 ,
e.nombre_completo,
(casewhen e.nac = 2 then 'E-'
else 'V-' end +e.ci)as ci,
n.co_novedad_dia,n.co_novedad_hora,n.num_pres,n.num_contpres,n.num_vac,
[dbo].[ObtenerFechaCadena] (r.fec_emis)as fec_emis,
r.reci_num,
n.comentario,
isnull(n.monto,0)as monto,
n.auxi_cha,
n.auxi_num,
n.tipo,
isnull(dbo.GetValorCampo(dbo.GetCampo('A001'),e.cod_emp),0)as SueldoMensVar,
c.co_conce,
c.des_conce,
b.co_ban,
b.des_ban,
e.cta_banc1,
e.fecha_ing,
d.des_depart,
d.co_depart,
co.co_cont,
co.des_cont,
gn.fec_ini,
gn.fec_fin,
ca.co_cargo,
ca.des_cargo,
dbo.GetMontoNetoPagarRecibo(r.reci_num)
FROM
dbo.snrecibo as rinner join dbo.snnomi as non(r.reci_num = n.reci_num)
inner join dbo.snconcep as con(c.co_conce = n.co_conce)
inner join dbo.snemple as eon(n.cod_emp = e.cod_emp)
inner join dbo.sncont as coon(co.co_cont = r.co_cont)
left join dbo.snbanco as bon(e.co_ban1 = b.co_ban)
inner join dbo.sndepart as don(d.co_depart = r.co_depart)
inner join dbo.sngennomi as gnon(gn.co_cont = r.co_cont)AND(gn.fec_emis = r.fec_emis)
inner join dbo.sncargo as caon(ca.co_cargo = e.co_cargo)
WHERE
(co.tip_cont in (1,2,3))AND
(n.tipo in (1,2,3))AND
((@iReci_Num_d is null OR r.reci_num >= @iReci_Num_d) AND (@iReci_Num_h is null OR (@iReci_Num_d is null AND r.reci_num is null) OR r.reci_num <= @iReci_Num_h))AND
((@sCod_Emp_d is null OR r.cod_emp >= @sCod_Emp_d) AND (@sCod_Emp_h is null OR (@sCod_Emp_d is null AND r.cod_emp is null) OR r.cod_emp <= @sCod_Emp_h))AND
((@sdFec_Emis_d IS NULL OR r.fec_emis >= @sdFec_Emis_d) AND (@sdFec_Emis_h IS NULL OR (@sdFec_Emis_d IS NULL AND r.fec_emis IS NULL) OR r.fec_emis <= @sdFec_Emis_h)) AND
(@sCo_Cont is null OR e.co_cont = @sCo_Cont) AND
((@sCo_ContGen_d is null OR r.co_cont >= @sCo_ContGen_d) AND (@sCo_ContGen_h is null OR (@sCo_ContGen_d is null AND r.co_cont is null) OR r.co_cont <= @sCo_ContGen_h))AND
(@sCo_Depart is null OR e.co_depart = @sCo_Depart) AND
((@sCo_DepartGen_d is null OR r.co_depart >= @sCo_DepartGen_d) AND (@sCo_DepartGen_h is null OR (@sCo_DepartGen_d is null AND r.co_depart is null) OR r.co_depart <= @sCo_DepartGen_h)) AND
((@sCo_Ubicacion_d is null OR e.co_ubicacion >= @sCo_Ubicacion_d) AND (@sCo_Ubicacion_h is null OR (@sCo_Ubicacion_d is null AND e.co_ubicacion is null) OR e.co_ubicacion <= @sCo_Ubicacion_h))
UNION ALL
SELECT
e.cod_emp as codemp2,
e.nombre_completo,
(casewhen e.nac = 2 then 'E-'
else 'V-' end +e.ci)as ci,
n.co_novedad_dia,n.co_novedad_hora,n.num_pres,n.num_contpres,n.num_vac,
[dbo].[ObtenerFechaCadena] (r.fec_emis)as fec_emis,
r.reci_num,
n.comentario,
isnull(n.monto,0)as monto,
n.auxi_cha,
n.auxi_num,
n.tipo,
isnull(dbo.GetValorCampo(dbo.GetCampo('A001'),e.cod_emp),0)as SueldoMensVar,
c.co_conce,
c.des_conce,
b.co_ban,
b.des_ban,
e.cta_banc1,
e.fecha_ing,
d.des_depart,
d.co_depart,
co.co_cont,
co.des_cont,
gn.fec_ini,
gn.fec_fin,
ca.co_cargo,
ca.des_cargo,
dbo.GetMontoNetoPagarRecibo(r.reci_num)
FROM
dbo.snrecibo as rinner join dbo.snnomi as non(r.reci_num = n.reci_num)
inner join dbo.snconcep as con(c.co_conce = n.co_conce)
inner join dbo.snemple as eon(n.cod_emp = e.cod_emp)
inner join dbo.sncont as coon(co.co_cont = r.co_cont)
left join dbo.snbanco as bon(e.co_ban1 = b.co_ban)
inner join dbo.sndepart as don(d.co_depart = r.co_depart)
inner join dbo.sngennomi as gnon(gn.co_cont = r.co_cont)AND(gn.fec_emis = r.fec_emis)
inner join dbo.sncargo as caon(ca.co_cargo = e.co_cargo)
WHERE
(co.tip_cont in (1,2,3))AND
(n.tipo in (1,2,3))AND
((@iReci_Num_d is null OR r.reci_num >= @iReci_Num_d) AND (@iReci_Num_h is null OR (@iReci_Num_d is null AND r.reci_num is null) OR r.reci_num <= @iReci_Num_h))AND
((@sCod_Emp_d is null OR r.cod_emp >= @sCod_Emp_d) AND (@sCod_Emp_h is null OR (@sCod_Emp_d is null AND r.cod_emp is null) OR r.cod_emp <= @sCod_Emp_h))AND
((@sdFec_Emis_d IS NULL OR r.fec_emis >= @sdFec_Emis_d) AND (@sdFec_Emis_h IS NULL OR (@sdFec_Emis_d IS NULL AND r.fec_emis IS NULL) OR r.fec_emis <= @sdFec_Emis_h)) AND
(@sCo_Cont is null OR e.co_cont = @sCo_Cont) AND
((@sCo_ContGen_d is null OR r.co_cont >= @sCo_ContGen_d) AND (@sCo_ContGen_h is null OR (@sCo_ContGen_d is null AND r.co_cont is null) OR r.co_cont <= @sCo_ContGen_h))AND
(@sCo_Depart is null OR e.co_depart = @sCo_Depart) AND
((@sCo_DepartGen_d is null OR r.co_depart >= @sCo_DepartGen_d) AND (@sCo_DepartGen_h is null OR (@sCo_DepartGen_d is null AND r.co_depart is null) OR r.co_depart <= @sCo_DepartGen_h)) AND
((@sCo_Ubicacion_d is null OR e.co_ubicacion >= @sCo_Ubicacion_d) AND (@sCo_Ubicacion_h is null OR (@sCo_Ubicacion_d is null AND e.co_ubicacion is null) OR e.co_ubicacion <= @sCo_Ubicacion_h))
GROUP BY e.cod_emp, c.co_conce, e.nombre_completo, e.nac, e.ci, n.co_novedad_dia,n.co_novedad_hora,n.num_pres,n.num_contpres,n.num_vac,
r.fec_emis, r.reci_num, n.comentario, n.monto, n.auxi_cha, n.auxi_num, n.tipo,e.cod_emp,
c.des_conce, b.co_ban, b.des_ban, e.cta_banc1, e.fecha_ing,
d.des_depart, d.co_depart, co.co_cont, co.des_cont, gn.fec_ini,
gn.fec_fin, ca.co_cargo, ca.des_cargo, r.reci_num
ORDER BY
CASE @sDir
WHEN 'DESC' THENCASE @sCampOrderBy
WHEN 'nombre_completo' THEN e.nombre_completo
else e.cod_empEND
END
DESC,CASE @sDir
WHEN 'ASC' THENCASE @sCampOrderBy
WHEN 'nombre_completo' THEN e.nombre_completo
else e.cod_empEND
END
ASC
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Valora esta pregunta
0
