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
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!