resultado em cada linha
Galera estou com uma consulta que ainda não consegui entender o porque. gostaria que ela trouxesse em apenas uma linha mas não vem de forma alguma.
SELECT BO.LAGER,
BO.ID_OS,
K.ID_KLIENT,
K.SUCHBEGRIFF CNPJ_CPF,
K.NAME,
BI.DIV_1 LOTE,
BI.DIV_10 DI_DDE,
(SELECT DISTINCT DIS.ID_DISPATCHER
FROM FISCAL.DOCHD DC, WMS_EADI.DISPATCHER DIS
WHERE DC.DOCHD_DOC_ID = BO.NR_NF
AND DC.DOCHD_RPS_DESP = DIS.SUCHBEGRIFF) COD_COMISSARIA,
(SELECT DISTINCT DIS.BEZ
FROM FISCAL.DOCHD DC, WMS_EADI.DISPATCHER DIS
WHERE DC.DOCHD_DOC_ID = BO.NR_NF
AND DC.DOCHD_RPS_DESP = DIS.SUCHBEGRIFF) COMISSARIA,
BO.NR_NF,
BO.DATE_BILL,
BO.DATE_EMISS,
--------ABAIXO OS TIPOS DE SERVIÇOS ONDE ESTÃO OCORRENDO CADA RESULTADO ELE CRIA UMA LINHA----------------
(SELECT SUM(ROUND(OSI.PREIS, 2))
FROM BILL_OS_ITEM OSI
WHERE OSI.LAGER = BO.LAGER
AND OSI.ID_KLIENT = K.ID_KLIENT
AND OSI.ID_OS = BO.ID_OS
AND OSI.BILLITE = BI.BILLITE
AND OSI.DIV_1 = BI.DIV_1
--AND NVL(OSI.DIV_10, 'x') = NVL(BI.DIV_10, 'x')
AND OSI.BILLITE IN
(SELECT BLL.BILLITE
FROM BILL_ITEM BLL, WERTE WER
WHERE BLL.TIPO_FAT = WER.WERT
AND WER.WERTE_BER = 'ITENSF'
AND WER.WERT = 'ARMAZ')) VLR_ARMAZ,
(SELECT SUM(ROUND(OSI.PREIS, 2))
FROM BILL_OS_ITEM OSI
WHERE OSI.LAGER = BO.LAGER
AND OSI.ID_KLIENT = K.ID_KLIENT
AND OSI.ID_OS = BO.ID_OS
AND OSI.BILLITE = BI.BILLITE
AND OSI.DIV_1 = BI.DIV_1
--AND NVL(OSI.DIV_10, 'x') = NVL(BI.DIV_10, 'x')
AND OSI.BILLITE IN
(SELECT BLL.BILLITE
FROM BILL_ITEM BLL, WERTE WER
WHERE BLL.TIPO_FAT = WER.WERT
AND WER.WERTE_BER = 'ITENSF'
AND WER.WERT = 'GER.RISCO')) VLR_GER_RISCO,
(SELECT SUM(ROUND(OSI.PREIS, 2))
FROM BILL_OS_ITEM OSI
WHERE OSI.LAGER = BO.LAGER
AND OSI.ID_KLIENT = K.ID_KLIENT
AND OSI.ID_OS = BO.ID_OS
AND OSI.BILLITE = BI.BILLITE
AND OSI.DIV_1 = BI.DIV_1
--AND NVL(OSI.DIV_10, 'x') = NVL(BI.DIV_10, 'x')
AND OSI.BILLITE IN
(SELECT BLL.BILLITE
FROM BILL_ITEM BLL, WERTE WER
WHERE BLL.TIPO_FAT = WER.WERT
AND WER.WERTE_BER = 'ITENSF'
AND WER.WERT = 'MOVIMENT')) VLR_MOVIMENTACAO,
(SELECT SUM(ROUND(OSI.PREIS, 2))
FROM BILL_OS_ITEM OSI
WHERE OSI.LAGER = BO.LAGER
AND OSI.ID_KLIENT = K.ID_KLIENT
AND OSI.ID_OS = BO.ID_OS
AND OSI.BILLITE = BI.BILLITE
AND OSI.DIV_1 = BI.DIV_1
--AND NVL(OSI.DIV_10, 'x') = NVL(BI.DIV_10, 'x')
AND OSI.BILLITE IN
(SELECT BLL.BILLITE
FROM BILL_ITEM BLL, WERTE WER
WHERE BLL.TIPO_FAT = WER.WERT
AND WER.WERTE_BER = 'ITENSF'
AND WER.WERT = 'DESUNIT')) VLR_DESUNIT,
(SELECT SUM(ROUND(OSI.PREIS, 2))
FROM BILL_OS_ITEM OSI
WHERE OSI.LAGER = BO.LAGER
AND OSI.ID_KLIENT = K.ID_KLIENT
AND OSI.ID_OS = BO.ID_OS
AND OSI.BILLITE = BI.BILLITE
AND OSI.DIV_1 = BI.DIV_1
--AND NVL(OSI.DIV_10, 'x') = NVL(BI.DIV_10, 'x')
AND OSI.BILLITE IN
(SELECT BLL.BILLITE
FROM BILL_ITEM BLL, WERTE WER
WHERE BLL.TIPO_FAT = WER.WERT
AND WER.WERTE_BER = 'ITENSF'
AND WER.WERT = 'PESAGEM')) VLR_PESAGEM,
(SELECT SUM(ROUND(OSI.PREIS, 2))
FROM BILL_OS_ITEM OSI
WHERE OSI.LAGER = BO.LAGER
AND OSI.ID_KLIENT = K.ID_KLIENT
AND OSI.ID_OS = BO.ID_OS
AND OSI.BILLITE = BI.BILLITE
AND OSI.DIV_1 = BI.DIV_1
--AND NVL(OSI.DIV_10, 'x') = NVL(BI.DIV_10, 'x')
AND OSI.BILLITE IN
(SELECT BLL.BILLITE
FROM BILL_ITEM BLL, WERTE WER
WHERE BLL.TIPO_FAT = WER.WERT
AND WER.WERTE_BER = 'ITENSF'
AND WER.WERT LIKE 'AVERBA%')) VLR_AVERBACAO,
(SELECT SUM(ROUND(OSI.PREIS, 2))
FROM BILL_OS_ITEM OSI
WHERE OSI.LAGER = BO.LAGER
AND OSI.ID_KLIENT = K.ID_KLIENT
AND OSI.ID_OS = BO.ID_OS
AND OSI.BILLITE = BI.BILLITE
AND OSI.BILLITE IN
(SELECT BLL.BILLITE
FROM BILL_ITEM BLL, WERTE WER
WHERE BLL.TIPO_FAT = WER.WERT
AND WER.WERTE_BER = 'ITENSF'
AND WER.WERT = 'FAT.MINIMO.')) VLR_FAT_MINIMO,
(SELECT SUM(ROUND(OSI.PREIS, 2))
FROM BILL_OS_ITEM OSI
WHERE OSI.LAGER = BO.LAGER
AND OSI.ID_KLIENT = K.ID_KLIENT
AND OSI.ID_OS = BO.ID_OS
AND OSI.BILLITE = BI.BILLITE
AND OSI.DIV_1 = BI.DIV_1
--AND NVL(OSI.DIV_10, 'x') = NVL(BI.DIV_10, 'x')
AND OSI.BILLITE IN
(SELECT BLL.BILLITE
FROM BILL_ITEM BLL, WERTE WER
WHERE BLL.TIPO_FAT = WER.WERT
AND WER.WERTE_BER = 'ITENSF'
AND BLL.BILLITE NOT IN ('21.02', '21.03')
AND WER.WERT = 'OUT.SERV.')) VLR_OUTROS_SERV,
(SELECT SUM(ROUND(OSI.PREIS, 2))
FROM BILL_OS_ITEM OSI
WHERE OSI.LAGER = BO.LAGER
AND OSI.ID_KLIENT = K.ID_KLIENT
AND OSI.ID_OS = BO.ID_OS
AND OSI.BILLITE = BI.BILLITE
AND OSI.DIV_1 = BI.DIV_1
--AND NVL(OSI.DIV_10, 'x') = NVL(BI.DIV_10, 'x')
AND OSI.BILLITE IN
(SELECT BLL.BILLITE
FROM BILL_ITEM BLL, WERTE WER
WHERE BLL.TIPO_FAT = WER.WERT
AND WER.WERTE_BER = 'ITENSF'
AND BLL.BILLITE = '21.02'
AND WER.WERT = 'OUT.SERV.')) TRANSF_IN,
(SELECT SUM(ROUND(OSI.PREIS, 2))
FROM BILL_OS_ITEM OSI
WHERE OSI.LAGER = BO.LAGER
AND OSI.ID_KLIENT = K.ID_KLIENT
AND OSI.ID_OS = BO.ID_OS
AND OSI.BILLITE = BI.BILLITE
AND OSI.DIV_1 = BI.DIV_1
--AND NVL(OSI.DIV_10, 'x') = NVL(BI.DIV_10, 'x')
AND OSI.BILLITE IN
(SELECT BLL.BILLITE
FROM BILL_ITEM BLL, WERTE WER
WHERE BLL.TIPO_FAT = WER.WERT
AND WER.WERTE_BER = 'ITENSF'
AND BLL.BILLITE = '21.03'
AND WER.WERT = 'OUT.SERV.')) TRANSF_OUT
FROM BILL_OS BO, BILL_OS_ITEM BI, KLIENTEN K
WHERE BO.LAGER = BI.LAGER
AND BO.ID_KLIENT = BI.ID_KLIENT
AND BO.ID_OS = BI.ID_OS
AND BO.LAGER = K.LAGER
AND BO.ID_KLIENT = K.ID_KLIENT
and k.id_klient = '3099'
and BO.NR_NF = '121304'
AND BO.STATUS = '90'
AND TRUNC(BO.DATE_BILL) BETWEEN '28/08/2019' AND '28/08/2019'
GROUP BY BO.LAGER,
BO.ID_OS,
K.ID_KLIENT,
K.SUCHBEGRIFF,
K.NAME,
BO.DATE_EMISS,
BO.NR_NF,
BO.DATE_BILL,
BI.BILLITE,
BI.DIV_1,
BI.DIV_10

Alguem consegue identificar neste select porque ele vem assim?Discussão (2)
Carregando comentários...