Boa tarde!
a todos
Tenho a consulta abaixo que no SQL DO Microsiga retorna o resultado em colunas e preciso fazer a mesma consulta no Access.
O mesmo esta com erro na função COALESCE
SELECT PRODUTO, B1_ZZDCSAP AS DESCR,
COALESCE( SUM(CASE WHEN TIPO ='STK' THEN QTY END),0) AS 'STK',
COALESCE( SUM(CASE WHEN TIPO ='PO' THEN QTY END),0) AS 'PO',
COALESCE( SUM(CASE WHEN TIPO ='PR' THEN QTY END),0) AS 'PR',
COALESCE( SUM(CASE WHEN TIPO ='P1' THEN QTY END),0) AS '14AUG',
COALESCE( SUM(CASE WHEN TIPO ='P2' THEN QTY END),0) AS '14SEP',
COALESCE( SUM(CASE WHEN TIPO ='P3' THEN QTY END),0) AS '14OCT',
COALESCE( SUM(CASE WHEN TIPO ='P4' THEN QTY END),0) AS '14NOV',
COALESCE( SUM(CASE WHEN TIPO ='P5' THEN QTY END),0) AS '14DEC',
COALESCE( SUM(CASE WHEN TIPO ='P6' THEN QTY END),0) AS '15JAN',
COALESCE( SUM(CASE WHEN TIPO ='P7' THEN QTY END),0) AS '15FEB',
COALESCE( SUM(CASE WHEN TIPO ='P8' THEN QTY END),0) AS '15MAR',
COALESCE( SUM(CASE WHEN TIPO ='P9' THEN QTY END),0) AS '15APR',
COALESCE( SUM(CASE WHEN TIPO ='P10' THEN QTY END),0) AS '15MAY',
COALESCE( SUM(CASE WHEN TIPO ='P11' THEN QTY END),0) AS '15JUN',
COALESCE( SUM(CASE WHEN TIPO ='P12' THEN QTY END),0) AS '15JUL',
COALESCE( SUM(CASE WHEN TIPO ='P13' THEN QTY END),0) AS '15AUG',
COALESCE( SUM(CASE WHEN TIPO ='P14' THEN QTY END),0) AS '15SEP',
COALESCE( SUM(CASE WHEN TIPO ='P15' THEN QTY END),0) AS '15OCT',
COALESCE( SUM(CASE WHEN TIPO ='P16' THEN QTY END),0) AS '15NOV',
COALESCE( SUM(CASE WHEN TIPO ='P17' THEN QTY END),0) AS '15DEC'
FROM
(SELECT 'STK' AS TIPO, B2_COD AS PRODUTO, SUM(B2_QATU+B2_QNPT) AS QTY FROM SB2010 WHERE D_E_L_E_T_ = '' AND B2_LOCAL IN (
SELECT ZZA_LOCAL FROM ZZA010 WHERE D_E_L_E_T_ = '' AND ZZA_MRP = 'S')
GROUP BY B2_COD
UNION ALL
SELECT 'PO' AS TIPO, C7_PRODUTO AS PRODUTO, SUM(C7_QUANT-C7_QUJE) AS QTY FROM SC7010 WHERE D_E_L_E_T_ = '' AND C7_RESIDUO = ''
GROUP BY C7_PRODUTO
UNION ALL
SELECT 'PR' AS TIPO, C1_PRODUTO AS PRODUTO, SUM(C1_QUANT-C1_QUJE) AS QTY FROM SC1010 WHERE D_E_L_E_T_ = '' AND C1_RESIDUO = ''
GROUP BY C1_PRODUTO
UNION ALL
SELECT 'P1' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201408%'
GROUP BY D4_COD
UNION ALL
SELECT 'P2' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201409%'
GROUP BY D4_COD
UNION ALL
SELECT 'P3' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201410%'
GROUP BY D4_COD
UNION ALL
SELECT 'P4' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201411%'
GROUP BY D4_COD
UNION ALL
SELECT 'P5' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201412%'
GROUP BY D4_COD
UNION ALL
SELECT 'P6' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201501%'
GROUP BY D4_COD
UNION ALL
SELECT 'P7' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201502%'
GROUP BY D4_COD
UNION ALL
SELECT 'P8' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201503%'
GROUP BY D4_COD
UNION ALL
SELECT 'P9' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201504%'
GROUP BY D4_COD
UNION ALL
SELECT 'P10' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201505%'
GROUP BY D4_COD
UNION ALL
SELECT 'P11' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201506%'
GROUP BY D4_COD
UNION ALL
SELECT 'P12' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201507%'
GROUP BY D4_COD
UNION ALL
SELECT 'P13' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201508%'
GROUP BY D4_COD
UNION ALL
SELECT 'P14' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201509%'
GROUP BY D4_COD
UNION ALL
SELECT 'P15' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201510%'
GROUP BY D4_COD
UNION ALL
SELECT 'P16' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201511%'
GROUP BY D4_COD
UNION ALL
SELECT 'P17' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201512%'
GROUP BY D4_COD
) AS TAB1
INNER JOIN SB1010 SB1 ON PRODUTO = B1_COD AND D_E_L_E_T_ = ''
WHERE SUBSTRING(PRODUTO,1,3) NOT IN ('MAT','SVC','SER','C16','CBR','100','ZZZ','MOD','','','')
GROUP BY PRODUTO, B1_ZZDCSAP
o que eu preciso e colocar o resultado de cada consulta acima em uma coluna por exemplo a primeira consulta na coluna SKT, A segunda na PO e assim por diante.
Desde já agradeço a todos
a todos
Tenho a consulta abaixo que no SQL DO Microsiga retorna o resultado em colunas e preciso fazer a mesma consulta no Access.
O mesmo esta com erro na função COALESCE
SELECT PRODUTO, B1_ZZDCSAP AS DESCR,
COALESCE( SUM(CASE WHEN TIPO ='STK' THEN QTY END),0) AS 'STK',
COALESCE( SUM(CASE WHEN TIPO ='PO' THEN QTY END),0) AS 'PO',
COALESCE( SUM(CASE WHEN TIPO ='PR' THEN QTY END),0) AS 'PR',
COALESCE( SUM(CASE WHEN TIPO ='P1' THEN QTY END),0) AS '14AUG',
COALESCE( SUM(CASE WHEN TIPO ='P2' THEN QTY END),0) AS '14SEP',
COALESCE( SUM(CASE WHEN TIPO ='P3' THEN QTY END),0) AS '14OCT',
COALESCE( SUM(CASE WHEN TIPO ='P4' THEN QTY END),0) AS '14NOV',
COALESCE( SUM(CASE WHEN TIPO ='P5' THEN QTY END),0) AS '14DEC',
COALESCE( SUM(CASE WHEN TIPO ='P6' THEN QTY END),0) AS '15JAN',
COALESCE( SUM(CASE WHEN TIPO ='P7' THEN QTY END),0) AS '15FEB',
COALESCE( SUM(CASE WHEN TIPO ='P8' THEN QTY END),0) AS '15MAR',
COALESCE( SUM(CASE WHEN TIPO ='P9' THEN QTY END),0) AS '15APR',
COALESCE( SUM(CASE WHEN TIPO ='P10' THEN QTY END),0) AS '15MAY',
COALESCE( SUM(CASE WHEN TIPO ='P11' THEN QTY END),0) AS '15JUN',
COALESCE( SUM(CASE WHEN TIPO ='P12' THEN QTY END),0) AS '15JUL',
COALESCE( SUM(CASE WHEN TIPO ='P13' THEN QTY END),0) AS '15AUG',
COALESCE( SUM(CASE WHEN TIPO ='P14' THEN QTY END),0) AS '15SEP',
COALESCE( SUM(CASE WHEN TIPO ='P15' THEN QTY END),0) AS '15OCT',
COALESCE( SUM(CASE WHEN TIPO ='P16' THEN QTY END),0) AS '15NOV',
COALESCE( SUM(CASE WHEN TIPO ='P17' THEN QTY END),0) AS '15DEC'
FROM
(SELECT 'STK' AS TIPO, B2_COD AS PRODUTO, SUM(B2_QATU+B2_QNPT) AS QTY FROM SB2010 WHERE D_E_L_E_T_ = '' AND B2_LOCAL IN (
SELECT ZZA_LOCAL FROM ZZA010 WHERE D_E_L_E_T_ = '' AND ZZA_MRP = 'S')
GROUP BY B2_COD
UNION ALL
SELECT 'PO' AS TIPO, C7_PRODUTO AS PRODUTO, SUM(C7_QUANT-C7_QUJE) AS QTY FROM SC7010 WHERE D_E_L_E_T_ = '' AND C7_RESIDUO = ''
GROUP BY C7_PRODUTO
UNION ALL
SELECT 'PR' AS TIPO, C1_PRODUTO AS PRODUTO, SUM(C1_QUANT-C1_QUJE) AS QTY FROM SC1010 WHERE D_E_L_E_T_ = '' AND C1_RESIDUO = ''
GROUP BY C1_PRODUTO
UNION ALL
SELECT 'P1' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201408%'
GROUP BY D4_COD
UNION ALL
SELECT 'P2' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201409%'
GROUP BY D4_COD
UNION ALL
SELECT 'P3' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201410%'
GROUP BY D4_COD
UNION ALL
SELECT 'P4' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201411%'
GROUP BY D4_COD
UNION ALL
SELECT 'P5' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201412%'
GROUP BY D4_COD
UNION ALL
SELECT 'P6' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201501%'
GROUP BY D4_COD
UNION ALL
SELECT 'P7' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201502%'
GROUP BY D4_COD
UNION ALL
SELECT 'P8' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201503%'
GROUP BY D4_COD
UNION ALL
SELECT 'P9' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201504%'
GROUP BY D4_COD
UNION ALL
SELECT 'P10' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201505%'
GROUP BY D4_COD
UNION ALL
SELECT 'P11' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201506%'
GROUP BY D4_COD
UNION ALL
SELECT 'P12' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201507%'
GROUP BY D4_COD
UNION ALL
SELECT 'P13' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201508%'
GROUP BY D4_COD
UNION ALL
SELECT 'P14' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201509%'
GROUP BY D4_COD
UNION ALL
SELECT 'P15' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201510%'
GROUP BY D4_COD
UNION ALL
SELECT 'P16' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201511%'
GROUP BY D4_COD
UNION ALL
SELECT 'P17' AS TIPO, D4_COD AS PRODUTO, SUM(D4_QUANT) AS QTY FROM SD4010 WHERE D_E_L_E_T_ = '' AND D4_QUANT > 0 AND D4_DATA LIKE '201512%'
GROUP BY D4_COD
) AS TAB1
INNER JOIN SB1010 SB1 ON PRODUTO = B1_COD AND D_E_L_E_T_ = ''
WHERE SUBSTRING(PRODUTO,1,3) NOT IN ('MAT','SVC','SER','C16','CBR','100','ZZZ','MOD','','','')
GROUP BY PRODUTO, B1_ZZDCSAP
o que eu preciso e colocar o resultado de cada consulta acima em uma coluna por exemplo a primeira consulta na coluna SKT, A segunda na PO e assim por diante.
Desde já agradeço a todos
Última edição por cla3200pc em 1/12/2014, 17:10, editado 1 vez(es)