Subquery at Banco Informix

0

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
    
asked by anonymous 19.10.2017 / 11:14

0 answers