Usuário com melhor resposta
Inner Join com select top 1

Pergunta
-
Pessoal, boa tarde
Tenho a necessidade de montar um left join com o top 1 de determinada tabela, estava fazendo dessa forma, porém vi que está errado.
select *
from
historicoCliente h (Nolock)left join
(
SELECT TOP 1 jur.* FROM pessoa_log (Nolock)
left join pessoa p on p.cd_pessoa = jur.cd_pessoa
where jur.status = 1 and jur.operacao = 'I'
ORDER BY p.STATUS)
logj ON (h.codempreedimento = logj.cd_pessoa_na and logj.status = 1 and logj.operacao = 'I')Preciso selecionar o 1º registro de acordo com o join, da maneira que está, me retorna o primeiro registro da tabela de acordo com o where, alguém tem ideia de como fazer, estou utilizando o sql 2000.
Respostas
-
Guilherme, experimente fazer um teste com o script abaixo:
declare @Pessoa table (Cd_Pessoa int, Nome_Pessoa varchar(55), Status char(1)); insert into @Pessoa values (1000, 'Fred', 'C'), (2000, 'Neymar', 'A'), (3000, 'Luis', 'B'), (4000, 'Julio', 'B'), (5000, 'Daniel', 'C'), (6000, 'Oscar', 'A'); declare @Pessoa_log table (IdPessoa_log int, Cd_Pessoa int, Cd_Pessoa_NA int); insert into @Pessoa_log values (11, 1000, 123), (12, 2000, 123), (13, 3000, 123), (14, 4000, 456), (15, 5000, 456), (16, 6000, 456); declare @HistoricoCliente table (CodCliente int, Data Datetime); insert into @HistoricoCliente values (123, '20140319'), (456, '20140320'); select * from ( select h.*, (SELECT TOP 1 jur.IdPessoa_log FROM @Pessoa_log jur left join @Pessoa p on p.Cd_Pessoa = jur.Cd_Pessoa where h.CodCliente = jur.Cd_Pessoa_NA order by p.Status) as IdPessoa_log from @HistoricoCliente as h ) as d inner join @Pessoa_log jur on jur.IdPessoa_log = d.IdPessoa_log inner join @Pessoa p on p.Cd_Pessoa = jur.Cd_Pessoa
Espero que seja útil.
Assinatura: http://www.imoveisemexposicao.com.br
- Marcado como Resposta Guilherme da Luz segunda-feira, 24 de março de 2014 15:37
Todas as Respostas
-
Usando CTE talvez resolva seu problema
WITH CTE AS (SELECT jur.* FROM pessoa_log (nolock) left join pessoa p on p.cd_pessoa = jur.cd_pessoa where jur.status = 1 and jur.operacao = 'I') select * from historicoCliente h (nolock) JOIN CTE on h.codempreendimento = CTE.cd_pessoa_na)
Se a sugestão resolver o problema, favor marcar como Resposta.
- Editado Lucas_Santos segunda-feira, 17 de março de 2014 16:34
-
-
@Lucas, não tem CTE no SQL2000, infelizmente.
@Guilherme, eu não consegui enxergar quem é a tabela "jur".
Poderia postar a estrutura correta do que esta fazendo?
[]´s
OMG agora que li que é em SQL 2000.Se a sugestão resolver o problema, favor marcar como Resposta.
-
Advaldo, segue abaixo a query:
select * from historicoCliente h (Nolock) left join ( SELECT TOP 1 jur.* FROM pessoa_log jur (Nolock) left join pessoa p on p.cd_pessoa = jur.cd_pessoa where jur.status = 1 and jur.operacao = 'I' ORDER BY p.STATUS) logj ON (h.codempreedimento = logj.cd_pessoa_na and logj.status = 1 and logj.operacao = 'I')
Obrigado pela atenção.
-
O join que você fala é entre a historicoCliente com a logj correto?
Pq nao coloca este seu where fora, deste modo:
SELECT * FROM historicoCliente h (NOLOCK) LEFT JOIN ( SELECT jur.*, p.STATUS FROM pessoa_log jur (NOLOCK) LEFT join pessoa p (NOLOCK) ON p.cd_pessoa = jur.cd_pessoa -- WHERE jur.status = 1 AND jur.operacao = 'I' ORDER BY p.STATUS )logj ON (h.codempreedimento = logj.cd_pessoa_na AND logj.status = 1 AND logj.operacao = 'I') WHERE logj.status = 1 AND logj.operacao = 'I' order by logj.STATUS
Note que coloquei o where e o order by no final da sintaxe. Deste modo, você tem uma visão geral do histórico do cliente, selecionando o range que precisa.
Ao menos, foi o que entendi nesta sua consulta.
Veja se funcioan.[]´s
-
Advaldo,
É esse join mesmo, o meu problema está em aplicar o TOP 1 no Left Join, o alias logj só pode retornar 1 linha pra cada linha de h.
Utilizando Outer Apply eu resolveria dessa forma:
SELECT * FROM históricoCliente h (Nolock)
outer apply
(
SELECT TOP 1 jur.*
FROM pessoa_log jur
left join pessoa p on p.cd_pessoa = jur.cd_pessoa
where h.codcliente = jur.cd_pessoa_na
and jur.status = 1 and jur.operacao = 'I'
order by p.STATUS
) logj
Mas o meu grande impeditivo é o fato de ser o SQL 2000.
-
-
Bom dia,
Guilherme, não sei se vai rodar no 2000, mas segue uma sugestão que talvez seja uma alternativa para obter o resultado desejado:
SELECT * FROM ( SELECT h.*, (SELECT TOP 1 jur.IdPessoa_log FROM pessoa_log jur left join pessoa p on p.cd_pessoa = jur.cd_pessoa where h.codcliente = jur.cd_pessoa_na and jur.status = 1 and jur.operacao = 'I' order by p.STATUS) as IdPessoa_log FROM históricoCliente h ) as d INNER JOIN pessoa_log jur ON jur.IdPessoa_log = d.IdPessoa_log
Espero que seja útil.
Assinatura: http://www.imoveisemexposicao.com.br
-
José,
Deixa eu explicar o cenário, acho que fica mais fácil.
Eu estou elaborando essa query, porque existem casos de duplicidade em parte do processo de carga que é realizado aqui, enquanto estão procurando a fonte da duplicidade, eu pretendo tratar isso na criação da view que utiliza parte desse select que eu postei, pois está influenciando em alguns relatórios.
O motivo do order by é que na tabela pessoa eu posso ter mais de 1 registro, nesse caso eu preciso pegar sempre o ativo, caso tenha apenas 1, eu seleciono ele mesmo, independente do status.
Ele pode me retornar nulo, pois na verdade na tabela historicoCliente, eu faço uma distinção entre pessoas físicas e jurídicas(um campo para cada), o join é na mesma tabela, alterando somente as chaves, e caso um desses dois campos me retorne nulo, eu trato os mesmos.
Para cada linha da tabela Pessoa, existe uma na tabela Pessoa_log, pois ele registra as ações realizadas naquele registro, o relacionamento é de 1:N
O meu select apenas do que eu passei no join é esse, passei um código no where para demonstrar o resultado:
SELECT * from
pessoa_log pls
left join pessoa p on p.cd_pessoa = pls.cd_pessoa
where cd_pessoa_na = 303515349 and pls.status = 1 and pls.operacao = 'I'
order by p.statusE esse é o resultado que estou tentando obter:
SELECT TOP 1 * from
pessoa_log pls
left join pessoa p on p.cd_pessoa = pls.cd_pessoa
where cd_pessoa_na = 303515349 and pls.status = 1 and pls.operacao = 'I'
order by p.status
-
Pessoal, estou colocando a query completa:
SELECT
Dt_Log = getdate()
,Dt_Envio = getdate()
,Cd_Chave_Negocio = h.CodRealizacao
,Sistema = 'ALI - Cliente Nacional'
,Cd_Pessoa = isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0)
,Operacao = 'I'
,Status = case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) = 0 or isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) = 0 or c.Cd_Acao is null or pl.cd_plano is null then 2 else 1 end
,Cd_Plano_NA = h.CodProjeto
,Cd_Acao_NA = t.CodAcao
,Cd_Acao = c.cd_acao
,Cd_Plano = pl.cd_plano
,Texto_Log = Case when c.Cd_Acao is null or pl.cd_plano is null then 'Plano Orçamentário não encontrado na base do Cliente-SP!!!' ELSE '' end+
Case when isnull(h.codempreedimento,0) = 0 then 'PJ - Lançamento realizado sem informação do código da Pessoa Jurídica' else
Case when isnull(h.codcliente,0) = 0 then 'PF - Lançamento realizado sem informação do código da Pessoa Física' else
case when parcf.codparceiro is null then 'PF - Código não encontrado na Base do Cliente-NA!!!' else
case when parcj.codparceiro is null then 'PJ - Código não encontrado na Base do Cliente-NA!!!' else
case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) = 0 then
case WHEN NOT EXISTS (select 1 from pessoa (nolock) where cgc_cpf = right(REPLICATE('0',11)+rtrim(ltrim(parcf.cgccpf)),11)) then
'PF - Pessoa Física não cadastrada no SAC!!!' else 'PF - Código não referenciado na Base Cliente-SP!!!' end else ''
end+
case when isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) = 0 then
Case WHEN NOT EXISTS (select 1 from pessoa (nolock) where cgc_cpf = right(REPLICATE('0',14)+rtrim(ltrim(parcj.cgccpf)),14)) then
'PJ - Pessoa Jurídica não cadastrada no SAC!!!' else 'PJ - Código não referenciado na Base Cliente-SP!!!' end else ''
end
end
end
end
end
,Cd_Pessoa_NA = h.codcliente
,Cd_Unidade = isnull(unop.cd_unidade,49)
,Mes = month(h.mesanocompetencia)
,Ano = year(h.mesanocompetencia)
,Dt_Evento = h.mesanocompetencia
,Cd_Pessoa_Empr = isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0)
,Cd_Pessoa_Empr_NA = h.codempreedimento
,Nome_Produto = h.NomeRealizacao
,Cd_Meta_Fisica = case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) <> isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) and isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) <> 0 then
-- PJ
case when h.instrumento = 'Orientação Técnica a Distância' then 322 else 102 end
else
--PF
case when h.instrumento = 'Orientação Técnica a Distância' then 321 else 101 end
end
,Tipo_Pessoa = h.TipoPessoa
,Cd_Turma = h.CodRealizacao
,CodEvento = null -- verificar se deve ser o codrealizacao
,HORAS = hatv.CargaHoraria * 60
,Dt_Evento_Inicio = h.DataHoraInicioRealizacao
,IDPRODUTO = 0
,ID_LOCAL_ATEND = 0
,ID_SUBLOCAL_ATEND = 0
,CD_LOCAL = 0
,ID_LOCAL = 0
,CodProdutoPortfolio = 0
,IdAtendimento = h.CodRealizacao
,Acao_Comp_Fechada = ''
,Inf_Orient = ''
,Canal = ''
,Cd_Pessoa_Usuario = null
,Login_Usuario = null
,Nome_Usuario = null
,IdBaseConhecimentoDetalhe = null
,IdCanalAtendimento = null
,CNPJ = right(REPLICATE('0',14)+rtrim(ltrim(parcj.cgccpf)),14)
,CPF = right(REPLICATE('0',11)+rtrim(ltrim(parcf.cgccpf)),11)
from
siacnet..historicorealizacoescliente h (Nolock)
left join
(SELECT TOP 1 jur.* FROM pessoa_log_siacnet jur (Nolock) left join pessoa p on p.cd_pessoa = jur.cd_pessoa ORDER BY p.STATUS)
logj ON (h.codempreedimento = logj.cd_pessoa_na and logj.status = 1 and logj.operacao = 'I')
left join pessoa_log_siacnet_Dupl duplj (Nolock) on duplj.cd_pessoa_na = h.codempreedimento and duplj.status = 1 and duplj.operacao = 'I'
left join siacnet..parceiro parcj (Nolock) on parcj.codparceiro = h.codempreedimento
left join
(SELECT TOP 1 fis.* FROM pessoa_log_siacnet fis (Nolock) left join pessoa p on p.cd_pessoa = fis.cd_pessoa ORDER BY p.STATUS)
logf on (logf.cd_pessoa_na = h.codcliente and logf.status = 1 and logf.operacao = 'I')
left join pessoa_log_siacnet_Dupl duplf (Nolock) on duplf.cd_pessoa_na = h.codcliente and duplf.status = 1 and duplf.operacao = 'I'
left join siacnet..parceiro parcf (Nolock) on parcf.codparceiro = h.codcliente
left join SPSVSQL07.SAPT_SIORC.dbo.Apt_Plano_Ano pl on pl.cd_plano_na = h.CodProjeto and pl.ano = (select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO)
left Join SiacNet.dbo.TbPaiAcao t (Nolock) On (t.CodAcao_seq=h.CodAcao)
Left Join SPSVSQL07.SAPT_SIORC.dbo.Apt_Acao_Ano c On (c.Cd_Acao_NA=t.CodAcao) And (c.Ano=(select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO))
Left Join SPSVSQL07.SAPT_SIORC.dbo.Apt_Acao b On (b.Cd_Acao=c.cd_acao)
left join spsvsql07.sapt_siorc.dbo.APT_PROCESSO pro on pro.CD_PROCESSO_RM = convert(int,RIGHT(ltrim(rtrim(b.centro_custo)),3))
left join spsvsql07.sapt_siorc.dbo.APT_PROCESSO_UNOP unop on unop.CD_PROCESSO = pro.cd_processo and unop.ativo = 'S'
left join siacnet..historicorealizacoescliente hatv (Nolock) on hatv.codcliente = h.codcliente
and hatv.codempreedimento = h.codempreedimento
and hatv.codrealizacao = h.codrealizacao
and year(hatv.mesanocompetencia) = year(h.mesanocompetencia)
and hatv.tiporealizacao = 'ATV'
where
year(h.mesanocompetencia) = (select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO)
and h.tiporealizacao = 'ALI'
and h.codCliente = 30Obrigado.
-
-
Guilherme, você tentou utilizar a forma que sugeri? O que ocorreu? Não funcionou? Por que?
Assinatura: http://www.imoveisemexposicao.com.br
-
Guilherme, experimente fazer um teste com o script abaixo:
declare @Pessoa table (Cd_Pessoa int, Nome_Pessoa varchar(55), Status char(1)); insert into @Pessoa values (1000, 'Fred', 'C'), (2000, 'Neymar', 'A'), (3000, 'Luis', 'B'), (4000, 'Julio', 'B'), (5000, 'Daniel', 'C'), (6000, 'Oscar', 'A'); declare @Pessoa_log table (IdPessoa_log int, Cd_Pessoa int, Cd_Pessoa_NA int); insert into @Pessoa_log values (11, 1000, 123), (12, 2000, 123), (13, 3000, 123), (14, 4000, 456), (15, 5000, 456), (16, 6000, 456); declare @HistoricoCliente table (CodCliente int, Data Datetime); insert into @HistoricoCliente values (123, '20140319'), (456, '20140320'); select * from ( select h.*, (SELECT TOP 1 jur.IdPessoa_log FROM @Pessoa_log jur left join @Pessoa p on p.Cd_Pessoa = jur.Cd_Pessoa where h.CodCliente = jur.Cd_Pessoa_NA order by p.Status) as IdPessoa_log from @HistoricoCliente as h ) as d inner join @Pessoa_log jur on jur.IdPessoa_log = d.IdPessoa_log inner join @Pessoa p on p.Cd_Pessoa = jur.Cd_Pessoa
Espero que seja útil.
Assinatura: http://www.imoveisemexposicao.com.br
- Marcado como Resposta Guilherme da Luz segunda-feira, 24 de março de 2014 15:37
-
-
Pessoal, estou colocando a query completa:
SELECT
Dt_Log = getdate()
,Dt_Envio = getdate()
,Cd_Chave_Negocio = h.CodRealizacao
,Sistema = 'ALI - Cliente Nacional'
,Cd_Pessoa = isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0)
,Operacao = 'I'
,Status = case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) = 0 or isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) = 0 or c.Cd_Acao is null or pl.cd_plano is null then 2 else 1 end
,Cd_Plano_NA = h.CodProjeto
,Cd_Acao_NA = t.CodAcao
,Cd_Acao = c.cd_acao
,Cd_Plano = pl.cd_plano
,Texto_Log = Case when c.Cd_Acao is null or pl.cd_plano is null then 'Plano Orçamentário não encontrado na base do Cliente-SP!!!' ELSE '' end+
Case when isnull(h.codempreedimento,0) = 0 then 'PJ - Lançamento realizado sem informação do código da Pessoa Jurídica' else
Case when isnull(h.codcliente,0) = 0 then 'PF - Lançamento realizado sem informação do código da Pessoa Física' else
case when parcf.codparceiro is null then 'PF - Código não encontrado na Base do Cliente-NA!!!' else
case when parcj.codparceiro is null then 'PJ - Código não encontrado na Base do Cliente-NA!!!' else
case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) = 0 then
case WHEN NOT EXISTS (select 1 from pessoa (nolock) where cgc_cpf = right(REPLICATE('0',11)+rtrim(ltrim(parcf.cgccpf)),11)) then
'PF - Pessoa Física não cadastrada no SAC!!!' else 'PF - Código não referenciado na Base Cliente-SP!!!' end else ''
end+
case when isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) = 0 then
Case WHEN NOT EXISTS (select 1 from pessoa (nolock) where cgc_cpf = right(REPLICATE('0',14)+rtrim(ltrim(parcj.cgccpf)),14)) then
'PJ - Pessoa Jurídica não cadastrada no SAC!!!' else 'PJ - Código não referenciado na Base Cliente-SP!!!' end else ''
end
end
end
end
end
,Cd_Pessoa_NA = h.codcliente
,Cd_Unidade = isnull(unop.cd_unidade,49)
,Mes = month(h.mesanocompetencia)
,Ano = year(h.mesanocompetencia)
,Dt_Evento = h.mesanocompetencia
,Cd_Pessoa_Empr = isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0)
,Cd_Pessoa_Empr_NA = h.codempreedimento
,Nome_Produto = h.NomeRealizacao
,Cd_Meta_Fisica = case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) <> isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) and isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) <> 0 then
-- PJ
case when h.instrumento = 'Orientação Técnica a Distância' then 322 else 102 end
else
--PF
case when h.instrumento = 'Orientação Técnica a Distância' then 321 else 101 end
end
,Tipo_Pessoa = h.TipoPessoa
,Cd_Turma = h.CodRealizacao
,CodEvento = null -- verificar se deve ser o codrealizacao
,HORAS = hatv.CargaHoraria * 60
,Dt_Evento_Inicio = h.DataHoraInicioRealizacao
,IDPRODUTO = 0
,ID_LOCAL_ATEND = 0
,ID_SUBLOCAL_ATEND = 0
,CD_LOCAL = 0
,ID_LOCAL = 0
,CodProdutoPortfolio = 0
,IdAtendimento = h.CodRealizacao
,Acao_Comp_Fechada = ''
,Inf_Orient = ''
,Canal = ''
,Cd_Pessoa_Usuario = null
,Login_Usuario = null
,Nome_Usuario = null
,IdBaseConhecimentoDetalhe = null
,IdCanalAtendimento = null
,CNPJ = right(REPLICATE('0',14)+rtrim(ltrim(parcj.cgccpf)),14)
,CPF = right(REPLICATE('0',11)+rtrim(ltrim(parcf.cgccpf)),11)
from
siacnet..historicorealizacoescliente h (Nolock)
left join
(SELECT TOP 1 jur.* FROM pessoa_log_siacnet jur (Nolock) left join pessoa p on p.cd_pessoa = jur.cd_pessoa ORDER BY p.STATUS)
logj ON (h.codempreedimento = logj.cd_pessoa_na and logj.status = 1 and logj.operacao = 'I')
left join pessoa_log_siacnet_Dupl duplj (Nolock) on duplj.cd_pessoa_na = h.codempreedimento and duplj.status = 1 and duplj.operacao = 'I'
left join siacnet..parceiro parcj (Nolock) on parcj.codparceiro = h.codempreedimento
left join
(SELECT TOP 1 fis.* FROM pessoa_log_siacnet fis (Nolock) left join pessoa p on p.cd_pessoa = fis.cd_pessoa ORDER BY p.STATUS)
logf on (logf.cd_pessoa_na = h.codcliente and logf.status = 1 and logf.operacao = 'I')
left join pessoa_log_siacnet_Dupl duplf (Nolock) on duplf.cd_pessoa_na = h.codcliente and duplf.status = 1 and duplf.operacao = 'I'
left join siacnet..parceiro parcf (Nolock) on parcf.codparceiro = h.codcliente
left join SPSVSQL07.SAPT_SIORC.dbo.Apt_Plano_Ano pl on pl.cd_plano_na = h.CodProjeto and pl.ano = (select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO)
left Join SiacNet.dbo.TbPaiAcao t (Nolock) On (t.CodAcao_seq=h.CodAcao)
Left Join SPSVSQL07.SAPT_SIORC.dbo.Apt_Acao_Ano c On (c.Cd_Acao_NA=t.CodAcao) And (c.Ano=(select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO))
Left Join SPSVSQL07.SAPT_SIORC.dbo.Apt_Acao b On (b.Cd_Acao=c.cd_acao)
left join spsvsql07.sapt_siorc.dbo.APT_PROCESSO pro on pro.CD_PROCESSO_RM = convert(int,RIGHT(ltrim(rtrim(b.centro_custo)),3))
left join spsvsql07.sapt_siorc.dbo.APT_PROCESSO_UNOP unop on unop.CD_PROCESSO = pro.cd_processo and unop.ativo = 'S'
left join siacnet..historicorealizacoescliente hatv (Nolock) on hatv.codcliente = h.codcliente
and hatv.codempreedimento = h.codempreedimento
and hatv.codrealizacao = h.codrealizacao
and year(hatv.mesanocompetencia) = year(h.mesanocompetencia)
and hatv.tiporealizacao = 'ATV'
where
year(h.mesanocompetencia) = (select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO)
and h.tiporealizacao = 'ALI'
and h.codCliente = 30Obrigado.
Guilherme,
Cara na boa, observe o nível de complexidade da sua query, existem diversas condições que o SQL Server tem que analisar, dizer que a culpa é do SQL Server 2000 é algo que não podemos justificar.
O que você tem que analisar é a maneira que os dados estão sendo obtidos.
Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]