Subquery in Informix Bank

Asked

Viewed 52 times

0

I have an appointment that seeks sales of 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’m trying to make a subquery but it’s 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
  • What is the name of the error that is returning?

  • How the "cost" relates to "sale" ?

  • Hello Danilo. You are returning Syntax error (201: A syntax error has occured

  • Motta, thanks for the reply. Cost and sale are related by chassis

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.