Page 1 of 2

How to simulate Subqueries in the ON Clause in Vertica

Posted: Tue May 28, 2013 12:12 am
by adrian.oprea
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 !!

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

Posted: Tue May 28, 2013 7:14 pm
by JimKnicely
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!

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

Posted: Tue May 28, 2013 8:16 pm
by adrian.oprea
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
;

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

Posted: Tue May 28, 2013 8:17 pm
by adrian.oprea
The error is :

Code: Select all

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

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

Posted: Tue May 28, 2013 8:31 pm
by JimKnicely
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))
        ;

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

Posted: Tue May 28, 2013 8:59 pm
by adrian.oprea
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 ?

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

Posted: Tue May 28, 2013 9:04 pm
by adrian.oprea
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!