How to simulate Subqueries in the ON Clause in Vertica

Moderator: NorbertKrupa

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

How to simulate Subqueries in the ON Clause in Vertica

Post by adrian.oprea » Tue May 28, 2013 12:12 am

Hy everbody , i am having some trouble in transforming a query that has a set of subqueries in the ON clause. The initial code was created in SQL Server and i have changed to work on Vertica.

This is the code :

Code: Select all

 SELECT
    DISTINCT DocFinanceiro.AutoId AS AutoId,
    DocFinanceiro.Classe AS ClasseDoc,
    EspecPrestador.Seq AS Sequencia,
    PrestadorServico.Codigo AS CodigoPrest,
    PessoaPrest.Nome AS NomePrestador,
    DocFinanceiro.CompFinanceira AS CompFinanceira,
    EspecialidadeServico.Nome AS Especialidade,
    ClassePrestador.Nome AS ClsPrestador,
    RegistroPessoa.Numero AS RegistroINSS,
    RegistroPessoa.Tipo AS TipoRegPessoa,
    DocFinanceiro.DataVencimento AS DataVencimentoDocFin
    FROM DocFinanceiro 
    INNER JOIN ClasseDocFinanceiro
    ON DocFinanceiro.Classe = ClasseDocFinanceiro.Codigo
    INNER JOIN ItemFinanceiro  ON DocFinanceiro.AutoId = ItemFinanceiro.Documento
    INNER JOIN ClasseApropriacaoFinan
    ON ItemFinanceiro.ClasseApro = ClasseApropriacaoFinan.Codigo
    INNER JOIN ContratoFinanceiro
    ON DocFinanceiro.ContratoFinanceiro = ContratoFinanceiro.AutoId
    INNER JOIN Pessoa  ON ContratoFinanceiro.Pessoa = Pessoa.AutoId
    INNER JOIN GeradorItemFinan
    ON ItemFinanceiro.AutoId = GeradorItemFinan.ItemFinan
    INNER JOIN PrestadorServico
    ON ContratoFinanceiro.AutoId = PrestadorServico.ContratoFinanceiro
    INNER JOIN Pessoa PessoaPrest  ON PrestadorServico.Pessoa = PessoaPrest.AutoId
    INNER JOIN TipoValorFinan
    ON ClasseApropriacaoFinan.TipoValor = TipoValorFinan.Codigo
    INNER JOIN ClasseContratoFinanceiro
    ON ContratoFinanceiro.Classe = ClasseContratoFinanceiro.AutoId
    INNER JOIN ClassePrestador
    ON PrestadorServico.Classe = ClassePrestador.Codigo
    LEFT JOIN EspecPrestador
    ON EspecPrestador.Prestador = PrestadorServico.AutoId
    AND EspecPrestador.Seq = 1
    LEFT JOIN EspecialidadeServico
    ON EspecialidadeServico.AutoId = EspecPrestador.Especialidade
    LEFT JOIN RegistroPessoa ON PessoaPrest.AutoId = RegistroPessoa.Pessoa
--This is the part where i getr stuck    
AND
    (
       RegistroPessoa.AutoId =
       (
          SELECT
          RegistroPessoa.AutoId
          FROM RegistroPessoa
          WHERE RegistroPessoa.Pessoa = PessoaPrest.Autoid
          AND (RegistroPessoa.Tipo = 1 OR RegistroPessoa.Tipo = 1) order by 1  limit 1
       )
    )
--until here
    WHERE (GeradorItemFinan.TipoGerador = 1)
    AND (DocFinanceiro.CompFinanceira = 1)
    AND (PrestadorServico.AutoId = 1)
    AND DocFinanceiro.CompSeq = 1
    AND (DocFinanceiro.Numero IS NOT NULL)
    AND (DocFinanceiro.Classe <> 1)
    ORDER BY AutoId, Sequencia
    ;
If anybody has any ideas it would be great !!
trying so hard !!!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to simulate Subqueries in the ON Clause in Vertica

Post by JimKnicely » Tue May 28, 2013 7:14 pm

Hi!

Are you getting an error?

What is the meaning of this code in your syntax?

(RegistroPessoa.Tipo = 1 OR RegistroPessoa.Tipo = 1)

Is one of the RegistroPessoa.Tipo columns supposed to be from the RegistroPessoa table from the LEFT join? If so, try aliasing each table reference. Something like this:

Code: Select all

LEFT JOIN RegistroPessoa rp1 ON PessoaPrest.AutoId = rp1.Pessoa
    AND
        (
           RegistroPessoa.AutoId =
           (
              SELECT
              RegistroPessoa.AutoId
              FROM RegistroPessoa rp2
              WHERE rp1.Pessoa = PessoaPrest.Autoid
              AND (rp1.Tipo = 1 OR rp2.RegistroPessoa.Tipo = 1) order by 1  limit 1
           )
        )
You'll also need to change the table reference in your SELECT columns:

Code: Select all

rp1.Numero AS RegistroINSS,
rp1.Tipo AS TipoRegPessoa,
Hope this helps a little!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Re: How to simulate Subqueries in the ON Clause in Vertica

Post by adrian.oprea » Tue May 28, 2013 8:16 pm

Hy Jim,
Thank you for answering so fast.
The actul code is the one here :

Code: Select all

SELECT
DISTINCT DocFinanceiro.AutoId AS AutoId,
DocFinanceiro.Classe AS ClasseDoc,
EspecPrestador.Seq AS Sequencia,
pAux.Codigo AS CodigoPrest,
PessoaPrest.Nome AS NomePrestador,
DocFinanceiro.CompFinanceira AS CompFinanceira,
EspecialidadeServico.Nome AS Especialidade,
ClassePrestador.Nome AS ClsPrestador,
RegistroPessoa.Numero AS RegistroINSS,
RegistroPessoa.Tipo AS TipoRegPessoa,
DocFinanceiro.DataVencimento AS DataVencimentoDocFin
FROM DocFinanceiro 
INNER JOIN ClasseDocFinanceiro
ON DocFinanceiro.Classe = ClasseDocFinanceiro.Codigo
INNER JOIN ItemFinanceiro  ON DocFinanceiro.AutoId = ItemFinanceiro.Documento
INNER JOIN ClasseApropriacaoFinan
ON ItemFinanceiro.ClasseApro = ClasseApropriacaoFinan.Codigo
INNER JOIN ContratoFinanceiro
ON DocFinanceiro.ContratoFinanceiro = ContratoFinanceiro.AutoId
INNER JOIN Pessoa  ON ContratoFinanceiro.Pessoa = Pessoa.AutoId
INNER JOIN GeradorItemFinan
ON ItemFinanceiro.AutoId = GeradorItemFinan.ItemFinan
INNER JOIN PrestadorServico
ON ContratoFinanceiro.AutoId = PrestadorServico.ContratoFinanceiro
INNER JOIN TipoValorFinan
ON ClasseApropriacaoFinan.TipoValor = TipoValorFinan.Codigo
INNER JOIN ClasseContratoFinanceiro
ON ContratoFinanceiro.Classe = ClasseContratoFinanceiro.AutoId
INNER JOIN ClassePrestador
ON PrestadorServico.Classe = ClassePrestador.Codigo
LEFT JOIN EspecPrestador
ON EspecPrestador.Prestador = PrestadorServico.AutoId
AND EspecPrestador.Seq = 1
LEFT JOIN EspecialidadeServico
ON EspecialidadeServico.AutoId = EspecPrestador.Especialidade
INNER JOIN CompVlItemEvento
ON GeradorItemFinan.IdGerador = CompVlItemEvento.AutoId
INNER JOIN PrestadorServico pAux  ON pAux.AutoId = CompVlItemEvento.Prestador
INNER JOIN Pessoa PessoaPrest  ON pAux.Pessoa = PessoaPrest.AutoId
INNER JOIN ItemEvento  ON CompVlItemEvento.ItemEvento = ItemEvento.AutoId
INNER JOIN Evento  ON ItemEvento.Evento = Evento.AutoId
INNER JOIN Beneficiario  ON Evento.Beneficiario = Beneficiario.AutoId
INNER JOIN Pessoa PessoaBenef  ON Beneficiario.Pessoa = PessoaBenef.AutoId
INNER JOIN GrupoApropriacaoUtil
ON Evento.GrupoApro = GrupoApropriacaoUtil.Codigo
INNER JOIN LoteDocServico  ON Evento.Lote = LoteDocServico.AutoId
INNER JOIN PrestadorServico PrestServLocAtend
ON Evento.LcAt = PrestServLocAtend.AutoId
LEFT JOIN IndiceFinanceiro
ON CompVlItemEvento.IndiceUtil = IndiceFinanceiro.Codigo
AND CompVlItemEvento.IndiceCobr = IndiceFinanceiro.Codigo
LEFT JOIN ClasseDocServico
ON ItemEvento.ClasseDocumento = ClasseDocServico.Codigo
LEFT JOIN ServicoOperadora  ON ItemEvento.Servico = ServicoOperadora.AutoId
LEFT JOIN RegistroPessoa  ON PessoaPrest.AutoId = RegistroPessoa.Pessoa
AND
(
   RegistroPessoa.AutoId =
   (
      SELECT
       RegistroPessoa.AutoId
      FROM RegistroPessoa 
      WHERE RegistroPessoa.Pessoa = PessoaPrest.Autoid
      AND (RegistroPessoa.Tipo = 11 OR RegistroPessoa.Tipo = 26)order by 1   limit 1
   )
)
WHERE
(
   GeradorItemFinan.Localizador = 'TotalPago' OR GeradorItemFinan.Localizador = 'SubTotalPago' OR GeradorItemFinan.Localizador = 'TotalCusteioPago'
)
AND (GeradorItemFinan.TipoGerador = 2)
AND (DocFinanceiro.CompFinanceira = 201304)
AND (pAux.AutoId = 643)
AND DocFinanceiro.CompSeq = 99
AND (DocFinanceiro.Numero IS NOT NULL)
AND (DocFinanceiro.Classe <> 90)
UNION
SELECT
DISTINCT DocFinanceiro.AutoId AS AutoId,
DocFinanceiro.Classe AS ClasseDoc,
EspecPrestador.Seq AS Sequencia,
PrestadorServico.Codigo AS CodigoPrest,
PessoaPrest.Nome AS NomePrestador,
DocFinanceiro.CompFinanceira AS CompFinanceira,
EspecialidadeServico.Nome AS Especialidade,
ClassePrestador.Nome AS ClsPrestador,
RegistroPessoa.Numero AS RegistroINSS,
RegistroPessoa.Tipo AS TipoRegPessoa,
DocFinanceiro.DataVencimento AS DataVencimentoDocFin
FROM DocFinanceiro 
INNER JOIN ClasseDocFinanceiro
ON DocFinanceiro.Classe = ClasseDocFinanceiro.Codigo
INNER JOIN ItemFinanceiro  ON DocFinanceiro.AutoId = ItemFinanceiro.Documento
INNER JOIN ClasseApropriacaoFinan
ON ItemFinanceiro.ClasseApro = ClasseApropriacaoFinan.Codigo
INNER JOIN ContratoFinanceiro
ON DocFinanceiro.ContratoFinanceiro = ContratoFinanceiro.AutoId
INNER JOIN Pessoa  ON ContratoFinanceiro.Pessoa = Pessoa.AutoId
INNER JOIN GeradorItemFinan
ON ItemFinanceiro.AutoId = GeradorItemFinan.ItemFinan
INNER JOIN PrestadorServico
ON ContratoFinanceiro.AutoId = PrestadorServico.ContratoFinanceiro
INNER JOIN Pessoa PessoaPrest  ON PrestadorServico.Pessoa = PessoaPrest.AutoId
INNER JOIN TipoValorFinan
ON ClasseApropriacaoFinan.TipoValor = TipoValorFinan.Codigo
INNER JOIN ClasseContratoFinanceiro
ON ContratoFinanceiro.Classe = ClasseContratoFinanceiro.AutoId
INNER JOIN ClassePrestador
ON PrestadorServico.Classe = ClassePrestador.Codigo
LEFT JOIN EspecPrestador
ON EspecPrestador.Prestador = PrestadorServico.AutoId
AND EspecPrestador.Seq = 1
LEFT JOIN EspecialidadeServico
ON EspecialidadeServico.AutoId = EspecPrestador.Especialidade
INNER JOIN AjustePagamentoComp
ON GeradorItemFinan.IdGerador = AjustePagamentoComp.AutoId
LEFT JOIN RegistroPessoa  ON PessoaPrest.AutoId = RegistroPessoa.Pessoa
AND
(
   RegistroPessoa.AutoId =
   (
      SELECT
       RegistroPessoa.AutoId
      FROM RegistroPessoa 
      WHERE RegistroPessoa.Pessoa = PessoaPrest.Autoid
      AND (RegistroPessoa.Tipo = 11 OR RegistroPessoa.Tipo = 26) order by 1  limit 1
   )
)
WHERE (GeradorItemFinan.TipoGerador = 5)
AND (DocFinanceiro.CompFinanceira = 201304)
AND (PrestadorServico.AutoId = 643)
AND DocFinanceiro.CompSeq = 99
AND (DocFinanceiro.Numero IS NOT NULL)
AND (DocFinanceiro.Classe <> 90)
UNION
SELECT
DISTINCT DocFinanceiro.AutoId AS AutoId,
DocFinanceiro.Classe AS ClasseDoc,
EspecPrestador.Seq AS Sequencia,
PrestadorServico.Codigo AS CodigoPrest,
PessoaPrest.Nome AS NomePrestador,
DocFinanceiro.CompFinanceira AS CompFinanceira,
EspecialidadeServico.Nome AS Especialidade,
ClassePrestador.Nome AS ClsPrestador,
RegistroPessoa.Numero AS RegistroINSS,
RegistroPessoa.Tipo AS TipoRegPessoa,
DocFinanceiro.DataVencimento AS DataVencimentoDocFin
FROM DocFinanceiro 
INNER JOIN ClasseDocFinanceiro
ON DocFinanceiro.Classe = ClasseDocFinanceiro.Codigo
INNER JOIN ItemFinanceiro  ON DocFinanceiro.AutoId = ItemFinanceiro.Documento
INNER JOIN ClasseApropriacaoFinan
ON ItemFinanceiro.ClasseApro = ClasseApropriacaoFinan.Codigo
INNER JOIN ContratoFinanceiro
ON DocFinanceiro.ContratoFinanceiro = ContratoFinanceiro.AutoId
INNER JOIN Pessoa  ON ContratoFinanceiro.Pessoa = Pessoa.AutoId
INNER JOIN GeradorItemFinan
ON ItemFinanceiro.AutoId = GeradorItemFinan.ItemFinan
INNER JOIN PrestadorServico
ON ContratoFinanceiro.AutoId = PrestadorServico.ContratoFinanceiro
INNER JOIN Pessoa PessoaPrest  ON PrestadorServico.Pessoa = PessoaPrest.AutoId
INNER JOIN TipoValorFinan
ON ClasseApropriacaoFinan.TipoValor = TipoValorFinan.Codigo
INNER JOIN ClasseContratoFinanceiro
ON ContratoFinanceiro.Classe = ClasseContratoFinanceiro.AutoId
INNER JOIN ClassePrestador
ON PrestadorServico.Classe = ClassePrestador.Codigo
LEFT JOIN EspecPrestador
ON EspecPrestador.Prestador = PrestadorServico.AutoId
AND EspecPrestador.Seq = 1
LEFT JOIN EspecialidadeServico
ON EspecialidadeServico.AutoId = EspecPrestador.Especialidade
LEFT JOIN RegistroPessoa ON PessoaPrest.AutoId = RegistroPessoa.Pessoa
AND
(
   RegistroPessoa.AutoId =
   (
      SELECT
      RegistroPessoa.AutoId
      FROM RegistroPessoa
      WHERE RegistroPessoa.Pessoa = PessoaPrest.Autoid
      AND (RegistroPessoa.Tipo = 11 OR RegistroPessoa.Tipo = 26) order by 1  limit 1
   )
)
WHERE (GeradorItemFinan.TipoGerador = 6)
AND (DocFinanceiro.CompFinanceira = 201304)
AND (PrestadorServico.AutoId = 643)
AND DocFinanceiro.CompSeq = 99
AND (DocFinanceiro.Numero IS NOT NULL)
AND (DocFinanceiro.Classe <> 90)
ORDER BY AutoId, Sequencia
;
trying so hard !!!

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Re: How to simulate Subqueries in the ON Clause in Vertica

Post by adrian.oprea » Tue May 28, 2013 8:17 pm

The error is :

Code: Select all

Error: [Vertica][VJDBC](4816) ERROR: Subqueries in the ON clause are not supported SQLState: 0A000 ErrorCode: 4816 
trying so hard !!!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to simulate Subqueries in the ON Clause in Vertica

Post by JimKnicely » Tue May 28, 2013 8:31 pm

Duh, I knew that. Hold on, let me re-think this :oops:

How about this attempt?

Code: Select all

   LEFT JOIN RegistroPessoa rp1 ON PessoaPrest.AutoId = RegistroPessoa.Pessoa
  WHERE (GeradorItemFinan.TipoGerador = 1)
        AND (DocFinanceiro.CompFinanceira = 1)
        AND (PrestadorServico.AutoId = 1)
        AND DocFinanceiro.CompSeq = 1
        AND (DocFinanceiro.Numero IS NOT NULL)
        AND (DocFinanceiro.Classe <> 1)
        AND (rp1.Tipo = 1
         OR  rp1.AutoId = (SELECT MAX(rp2.AutoId)
                             FROM RegistroPessoa rp2
                            WHERE rp2.Pessoa = PessoaPrest.Autoid
                              AND rp2.Tipo = 1))
        ;
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Re: How to simulate Subqueries in the ON Clause in Vertica

Post by adrian.oprea » Tue May 28, 2013 8:59 pm

It seems to run without error !!! :) !!
Can you please please explain a bit what have you done !!
Or is any materials in the vertica doc that show this ?
trying so hard !!!

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Re: How to simulate Subqueries in the ON Clause in Vertica

Post by adrian.oprea » Tue May 28, 2013 9:04 pm

I am saiing this becouse i have like 60 + like this !! So i need to learn and understand this as well !! copy+paste will not work:)

P.S.: - i took the certificate as well and passed but i am not even close to you skills !!
Thank you for the great forum!
trying so hard !!!

Post Reply

Return to “Vertica SQL”