I have a query that searches sales for a period:
SELECT cxpdvend.filial,
cxpdvend.dtpedido,
cxpdvend.pedvenda,
ciendere.nro_endere,
cipessoa.nomepessoa,
ciendere.celular,
cxpdvend.codagente,
cxpdvend.modelo,
cxpdvend.chassis,
(cxpdvend.valor-cxpdvend.valordesc) AS venda,
cxpdparc.formapgto
FROM cipessoa
INNER JOIN ciendere ON cipessoa.cgccpf = ciendere.cgccpf
INNER JOIN cxpdvend ON ciendere.nro_endere = cxpdvend.nro_endere
INNER JOIN cxcadast ON cxpdvend.chassis = cxcadast.chassis
INNER JOIN cxpdparc ON cxpdvend.pedvenda = cxpdparc.pedvenda
And another that seeks the cost of this sale:
SELECT cttransa.mvtotransacao,
cttransa.tipotransacao,
cnnfcapa.dtnota,
cnnfitem.chassis,
cnnfitem.valoritem
FROM cnnfcapa
INNER JOIN cnnfitem ON cnnfcapa.id_nfcapa = cnnfitem.id_nfcapa
INNER JOIN cttransa ON cnnfcapa.transacao = cttransa.transacao
WHERE cttransa.mvtotransacao="E"
AND cttransa.tipotransacao=10
I would like to include in the first query the purchase cost of the chassis that is obtained in the second query.
I am trying to make a subquery but it is returning syntax error.
I'm trying like this:
SELECT cxpdvend.filial,
cxpdvend.dtpedido,
cxpdvend.pedvenda,
ciendere.nro_endere,
cipessoa.nomepessoa,
ciendere.celular,
cxpdvend.codagente,
cxpdvend.modelo,
cxpdvend.chassis,
(cxpdvend.valor-cxpdvend.valordesc) AS venda,
cxpdparc.formapgto
FROM cipessoa
(MULTISET( SELECT cttransa.mvtotransacao,
cttransa.tipotransacao,
cnnfcapa.dtnota,
cnnfitem.chassis,
cnnfitem.valoritem
FROM cnnfcapa
INNER JOIN cnnfitem ON cnnfcapa.id_nfcapa = cnnfitem.id_nfcapa
INNER JOIN cttransa ON cnnfcapa.transacao = cttransa.transacao
WHERE cttransa.mvtotransacao="E"
AND cttransa.tipotransacao=10)
INNER JOIN ciendere ON cipessoa.cgccpf = ciendere.cgccpf
INNER JOIN cxpdvend ON ciendere.nro_endere = cxpdvend.nro_endere
INNER JOIN cxcadast ON cxpdvend.chassis = cxcadast.chassis
INNER JOIN cxpdparc ON cxpdvend.pedvenda = cxpdparc.pedvenda