BlackTDN :: Exemplo de “Query” para popular uma tabela vazia no Protheus
1: /*
2: Limpa a tabela para o Insert Total baseado na Classificacao
3: */
4: 5: TRUNCATE TABLE SZD030
6: 7: /*
8: Popula a Tabela SZD de acordo com a Linha de Produto e Classificacao do Cliente
9: */
10: INSERT INTO
11: SZD030 12: ( 13: ZD_FILIAL, 14: ZD_CLIENTE, 15: ZD_LOJA, 16: ZD_CODTMRG, 17: ZD_LINHAP, 18: D_E_L_E_T_, 19: R_E_C_N_O_, 20: R_E_C_D_E_L_ 21: )22: SELECT
23: SZD.ZD_FILIAL AS ZD_FILIAL,
24: SZD.ZD_CLIENTE AS ZD_CLIENTE,
25: SZD.ZD_LOJA AS ZD_LOJA,
26: SZD.ZD_CODTMRG AS ZD_CODTMRG,
27: ZD_LINHAP AS ZD_LINHAP,
28: ' ' AS D_E_L_E_T_,
29: ROW_NUMBER() OVER ( ORDER BY SZD.R_E_C_N_O_ ) AS R_E_C_N_O_,
30: 0 AS R_E_C_D_E_L_
31: FROM
32: ( 33: --Linha de Produto 0134: SELECT
35: SA1030.A1_FILIAL AS ZD_FILIAL,
36: SA1030.A1_COD AS ZD_CLIENTE,
37: SA1030.A1_LOJA AS ZD_LOJA,
38: CODTMRG.ZD_CODTMRG AS ZD_CODTMRG,
39: '01' AS ZD_LINHAP,
40: 0 AS R_E_C_N_O_
41: FROM
42: SA1030, 43: (44: SELECT
45: SE1.E1_FILIAL, 46: SE1.E1_CLIENTE, 47: SE1.E1_LOJA, 48: SE1.E1_VALOR, 49: (50: CASE
51: WHEN
52: SE1.E1_VALOR < 15000 53: THEN
54: '001'
55: ELSE
56: CASE
57: WHEN
58: SE1.E1_VALOR BETWEEN 15000 AND 24999.99
59: THEN
60: '002'
61: ELSE
62: CASE
63: WHEN
64: SE1.E1_VALOR >= 2500065: THEN '003'
66: END
67: END
68: END
69: ) AS ZD_CODTMRG
70: FROM
71: (72: SELECT
73: SE1_S.E1_FILIAL, 74: SE1_S.E1_CLIENTE, 75: SE1_S.E1_LOJA,76: SUM(SE1_S.E1_VALOR) E1_VALOR
77: FROM
78: SE1030 SE1_S79: WHERE
80: SE1_S.D_E_L_E_T_ <> '*'
81: AND
82: SE1_S.E1_FILIAL = '01'
83: AND
84: SE1_S.E1_EMISSAO BETWEEN '20110101' AND '20111231'
85: GROUP BY
86: SE1_S.E1_FILIAL, 87: SE1_S.E1_CLIENTE, 88: SE1_S.E1_LOJA 89: ) AS SE1
90: ) AS CODTMRG
91: WHERE
92: SA1030.D_E_L_E_T_ <> '*'
93: AND
94: SA1030.A1_FILIAL = ' '
95: AND
96: SA1030.A1_MSBLQL <> '1'
97: AND
98: CODTMRG.E1_FILIAL = '01'
99: AND
100: CODTMRG.E1_CLIENTE = SA1030.A1_COD101: AND
102: CODTMRG.E1_LOJA = SA1030.A1_LOJA103: AND
104: NOT EXISTS
105: (106: SELECT
107: 1108: FROM
109: SZD030 110: WHERE
111: SZD030.ZD_FILIAL = SA1030.A1_FILIAL112: AND
113: SZD030.ZD_CLIENTE = SA1030.A1_COD114: AND
115: SZD030.ZD_LOJA = SA1030.A1_LOJA116: AND
117: SZD030.ZD_LINHAP = '01'
118: )119: UNION ALL
120: --Linha de Produto 02121: SELECT
122: SA1030.A1_FILIAL AS ZD_FILIAL,
123: SA1030.A1_COD AS ZD_CLIENTE,
124: SA1030.A1_LOJA AS ZD_LOJA,
125: CODTMRG.ZD_CODTMRG AS ZD_CODTMRG,
126: '02' AS ZD_LINHAP,
127: 0 AS R_E_C_N_O_
128: FROM
129: SA1030, 130: (131: SELECT
132: (133: CASE
134: WHEN
135: SE1.E1_VALOR < 15000 136: THEN
137: '001'
138: ELSE
139: CASE
140: WHEN
141: SE1.E1_VALOR BETWEEN 15000 AND 24999.99
142: THEN
143: '002'
144: ELSE
145: CASE
146: WHEN
147: SE1.E1_VALOR >= 25000148: THEN '005'
149: END
150: END
151: END
152: ) AS ZD_CODTMRG,
153: SE1.E1_CLIENTE, 154: SE1.E1_LOJA, 155: SE1.E1_VALOR156: FROM
157: (158: SELECT
159: SE1_S.E1_CLIENTE, 160: SE1_S.E1_LOJA,161: SUM(SE1_S.E1_VALOR) E1_VALOR
162: FROM
163: SE1030 SE1_S164: WHERE
165: SE1_S.D_E_L_E_T_ <> '*'
166: AND
167: SE1_S.E1_EMISSAO BETWEEN '20110101' AND '20111231'
168: GROUP BY
169: SE1_S.E1_CLIENTE, 170: SE1_S.E1_LOJA 171: ) AS SE1
172: ) AS CODTMRG
173: WHERE
174: SA1030.D_E_L_E_T_ <> '*'
175: AND
176: SA1030.A1_MSBLQL <> '1'
177: AND
178: CODTMRG.E1_CLIENTE = SA1030.A1_COD179: AND
180: CODTMRG.E1_LOJA = SA1030.A1_LOJA181: AND
182: NOT EXISTS
183: (184: SELECT
185: 1186: FROM
187: SZD030 188: WHERE
189: SZD030.ZD_FILIAL = SA1030.A1_FILIAL190: AND
191: SZD030.ZD_CLIENTE = SA1030.A1_COD192: AND
193: SZD030.ZD_LOJA = SA1030.A1_LOJA194: AND
195: SZD030.ZD_LINHAP = '02'
196: )197: UNION ALL
198: --Linha de Produto 03199: SELECT
200: SA1030.A1_FILIAL AS ZD_FILIAL,
201: SA1030.A1_COD AS ZD_CLIENTE,
202: SA1030.A1_LOJA AS ZD_LOJA,
203: CODTMRG.ZD_CODTMRG AS ZD_CODTMRG,
204: '03' AS ZD_LINHAP,
205: 0 AS R_E_C_N_O_
206: FROM
207: SA1030, 208: (209: SELECT
210: (211: CASE
212: WHEN
213: SE1.E1_VALOR < 15000 214: THEN
215: '001'
216: ELSE
217: CASE
218: WHEN
219: SE1.E1_VALOR BETWEEN 15000 AND 24999.99
220: THEN
221: '002'
222: ELSE
223: CASE
224: WHEN
225: SE1.E1_VALOR >= 25000226: THEN '003'
227: END
228: END
229: END
230: ) AS ZD_CODTMRG,
231: SE1.E1_CLIENTE, 232: SE1.E1_LOJA, 233: SE1.E1_VALOR234: FROM
235: (236: SELECT
237: SE1_S.E1_CLIENTE, 238: SE1_S.E1_LOJA,239: SUM(SE1_S.E1_VALOR) E1_VALOR
240: FROM
241: SE1030 SE1_S242: WHERE
243: SE1_S.D_E_L_E_T_ <> '*'
244: AND
245: SE1_S.E1_EMISSAO BETWEEN '20110101' AND '20111231'
246: GROUP BY
247: SE1_S.E1_CLIENTE, 248: SE1_S.E1_LOJA 249: ) AS SE1
250: ) AS CODTMRG
251: WHERE
252: SA1030.D_E_L_E_T_ <> '*'
253: AND
254: SA1030.A1_MSBLQL <> '1'
255: AND
256: CODTMRG.E1_CLIENTE = SA1030.A1_COD257: AND
258: CODTMRG.E1_LOJA = SA1030.A1_LOJA259: AND
260: NOT EXISTS
261: (262: SELECT
263: 1264: FROM
265: SZD030 266: WHERE
267: SZD030.ZD_FILIAL = SA1030.A1_FILIAL268: AND
269: SZD030.ZD_CLIENTE = SA1030.A1_COD270: AND
271: SZD030.ZD_LOJA = SA1030.A1_LOJA272: AND
273: SZD030.ZD_LINHAP = '03'
274: )275: ) AS SZD
276: 277: /*
278: Totaliza os Clientes Processados
279: */
280: 281: SELECT
282: ( COUNT(1) / 3 ) AS TOTAL_CLIENTES
283: FROM
284: ( SELECT * FROM SZD030 ) SZD
285: 286: /*
287: Lista os Clientes Processados Classificados por Filial,Codigo, Loja, Linha
288: */
289: SELECT
290: SZD.ZD_FILIAL, 291: SZD.ZD_CLIENTE, 292: SZD.ZD_LOJA, 293: SZD.ZD_LINHAP, 294: SZD.E1_VALOR, 295: SZD030.ZD_CODTMRG296: FROM
297: SZD030, 298: (299: SELECT
300: SZD.ZD_FILIAL, 301: SZD.ZD_CLIENTE, 302: SZD.ZD_LOJA, 303: SZD.ZD_LINHAP,304: SUM(SE1.E1_VALOR) E1_VALOR
305: FROM
306: SZD030 SZD, 307: SE1030 SE1308: WHERE
309: SZD.D_E_L_E_T_ <> '*'
310: AND
311: SZD.ZD_FILIAL = ' '
312: AND
313: SE1.D_E_L_E_T_ <> '*'
314: AND
315: SE1.E1_FILIAL = '01'
316: AND
317: SZD.ZD_CLIENTE = SE1.E1_CLIENTE318: AND
319: SZD.ZD_LOJA = SE1.E1_LOJA320: AND
321: SZD.ZD_LINHAP = '01'
322: AND
323: SE1.E1_EMISSAO BETWEEN '20110101' AND '20111231'
324: GROUP BY
325: SZD.ZD_FILIAL, 326: SZD.ZD_CLIENTE, 327: SZD.ZD_LOJA, 328: SZD.ZD_LINHAP329: UNION ALL
330: SELECT
331: SZD.ZD_FILIAL, 332: SZD.ZD_CLIENTE, 333: SZD.ZD_LOJA, 334: SZD.ZD_LINHAP,335: SUM(SE1.E1_VALOR) E1_VALOR
336: FROM
337: SZD030 SZD, 338: SE1030 SE1339: WHERE
340: SZD.D_E_L_E_T_ <> '*'
341: AND
342: SZD.ZD_FILIAL = ' '
343: AND
344: SE1.D_E_L_E_T_ <> '*'
345: AND
346: SE1.E1_FILIAL = '01'
347: AND
348: SZD.ZD_CLIENTE = SE1.E1_CLIENTE349: AND
350: SZD.ZD_LOJA = SE1.E1_LOJA351: AND
352: SZD.ZD_LINHAP = '02'
353: AND
354: SE1.E1_EMISSAO BETWEEN '20110101' AND '20111231'
355: GROUP BY
356: SZD.ZD_FILIAL, 357: SZD.ZD_CLIENTE, 358: SZD.ZD_LOJA, 359: SZD.ZD_LINHAP360: UNION ALL
361: SELECT
362: SZD.ZD_FILIAL, 363: SZD.ZD_CLIENTE, 364: SZD.ZD_LOJA, 365: SZD.ZD_LINHAP,366: SUM(SE1.E1_VALOR) E1_VALOR
367: FROM
368: SZD030 SZD, 369: SE1030 SE1370: WHERE
371: SZD.D_E_L_E_T_ <> '*'
372: AND
373: SZD.ZD_FILIAL = ' '
374: AND
375: SE1.D_E_L_E_T_ <> '*'
376: AND
377: SE1.E1_FILIAL = '01'
378: AND
379: SZD.ZD_CLIENTE = SE1.E1_CLIENTE380: AND
381: SZD.ZD_LOJA = SE1.E1_LOJA382: AND
383: SZD.ZD_LINHAP = '03'
384: AND
385: SE1.E1_EMISSAO BETWEEN '20110101' AND '20111231'
386: GROUP BY
387: SZD.ZD_FILIAL, 388: SZD.ZD_CLIENTE, 389: SZD.ZD_LOJA, 390: SZD.ZD_LINHAP 391: ) SZD392: WHERE
393: SZD030.D_E_L_E_T_ <> '*'
394: AND
395: SZD030.ZD_FILIAL = SZD.ZD_FILIAL396: AND
397: SZD030.ZD_CLIENTE = SZD.ZD_CLIENTE398: AND
399: SZD030.ZD_LOJA = SZD.ZD_LOJA400: AND
401: SZD030.ZD_LINHAP = SZD.ZD_LINHAP402: ORDER BY
403: SZD.ZD_FILIAL, 404: SZD.ZD_CLIENTE, 405: SZD.ZD_LOJA, 406: SZD.ZD_LINHAP[]s
иαldσ dj
Comentários
Postar um comentário