NOT EXISTS

Estimados.
Utilizo SQL Server 2000 SP4

Tengo la siguiente consulta:

SELECT
c.codcli + '-' + c.dig as ID, left(c.codcli,4) as password, c.mail,
c.nombre,
c.paterno + ' ' + c.materno as apellidos, 'cl', '', '', '', '',
'1-9' as RUT,c.fonoact as Fono, 1, 'ALUMNO', c.comuna as comuna,
c.ciudadact as ciudad, a.codcarpr, 1 centro_educ, 'PAIS', 1
tipo_usuario
FROM
TABLA1 a,
TABLA3 c
WHERE
a.rut=c.codcli
and a.ano_mat = 2008
and a.estacad in ('VIGENTE', 'egresado')
and a.codsede = @p_cod_sede
and a.fec_mat < '20080101'
and mail like '%@%.%'
and a.rut collate Modern_Spanish_CI_AS NOT IN (SELECT substring(nick,
1,len(nick)-2) collate Modern_Spanish_CI_AS rut_alumno FROM TABLA8
T)
Pues bien esta consulta me devuelve N registros.

Si cambio el NOT IN por NOT EXISTS:

SELECT
c.codcli + '-' + c.dig as ID, left(c.codcli,4) as password, c.mail,
c.nombre,
c.paterno + ' ' + c.materno as apellidos, 'cl', '', '', '', '',
'1-9' as RUT,c.fonoact as Fono, 1, 'ALUMNO', c.comuna as comuna,
c.ciudadact as ciudad, a.codcarpr, 1 centro_educ, 'PAIS', 1
tipo_usuario
FROM
TABLA1 a,
TABLA3 c
WHERE
a.rut=c.codcli
and a.ano_mat = 2008
and a.estacad in ('VIGENTE', 'egresado')
and a.codsede = @p_cod_sede
and a.fec_mat < '20080101'
and mail like '%@%.%'
and NOT exists (SELECT * FROM TABLA8 T)

Aca NO me devuelve registros.

Agradeceria sus comentarios.
Atte.
Penta.

Trata:

...
and not exists (
select *
from TABAL8 AS T
where
substring(T.nick, 1,len(T.nick)-2) collate Modern_Spanish_CI_AS =
a.rut collate Modern_Spanish_CI_AS
)


AMB

"Penta" wrote:

> Estimados.
> Utilizo SQL Server 2000 SP4
>
> Tengo la siguiente consulta:
>
> SELECT
> c.codcli + '-' + c.dig as ID, left(c.codcli,4) as password, c.mail,
> c.nombre,
> c.paterno + ' ' + c.materno as apellidos, 'cl', '', '', '', '',
> '1-9' as RUT,c.fonoact as Fono, 1, 'ALUMNO', c.comuna as comuna,
> c.ciudadact as ciudad, a.codcarpr, 1 centro_educ, 'PAIS', 1
> tipo_usuario
> FROM
> TABLA1 a,
> TABLA3 c
> WHERE
> a.rut=c.codcli
> and a.ano_mat = 2008
> and a.estacad in ('VIGENTE', 'egresado')
> and a.codsede = @p_cod_sede
> and a.fec_mat < '20080101'
> and mail like '%@%.%'
> and a.rut collate Modern_Spanish_CI_AS NOT IN (SELECT substring(nick,
> 1,len(nick)-2) collate Modern_Spanish_CI_AS rut_alumno FROM TABLA8
> T)
> Pues bien esta consulta me devuelve N registros.
>
> Si cambio el NOT IN por NOT EXISTS:
>
> SELECT
> c.codcli + '-' + c.dig as ID, left(c.codcli,4) as password, c.mail,
> c.nombre,
> c.paterno + ' ' + c.materno as apellidos, 'cl', '', '', '', '',
> '1-9' as RUT,c.fonoact as Fono, 1, 'ALUMNO', c.comuna as comuna,
> c.ciudadact as ciudad, a.codcarpr, 1 centro_educ, 'PAIS', 1
> tipo_usuario
> FROM
> TABLA1 a,
> TABLA3 c
> WHERE
> a.rut=c.codcli
> and a.ano_mat = 2008
> and a.estacad in ('VIGENTE', 'egresado')
> and a.codsede = @p_cod_sede
> and a.fec_mat < '20080101'
> and mail like '%@%.%'
> and NOT exists (SELECT * FROM TABLA8 T)
>
> Aca NO me devuelve registros.
>
> Agradeceria sus comentarios.
> Atte.
> Penta.
>

Estimados.
Utilizo SQL Server 2000 SP4

Tengo la siguiente consulta:

SELECT
c.codcli + '-' + c.dig as ID, left(c.codcli,4) as password, c.mail,
c.nombre,
c.paterno + ' ' + c.materno as apellidos, 'cl', '', '', '', '',
'1-9' as RUT,c.fonoact as Fono, 1, 'ALUMNO', c.comuna as comuna,
c.ciudadact as ciudad, a.codcarpr, 1 centro_educ, 'PAIS', 1
tipo_usuario
FROM
TABLA1 a,
TABLA3 c
WHERE
a.rut=c.codcli
and a.ano_mat = 2008
and a.estacad in ('VIGENTE', 'egresado')
and a.codsede = @p_cod_sede
and a.fec_mat < '20080101'
and mail like '%@%.%'
and a.rut collate Modern_Spanish_CI_AS NOT IN (SELECT substring(nick,
1,len(nick)-2) collate Modern_Spanish_CI_AS rut_alumno FROM TABLA8
T)
Pues bien esta consulta me devuelve N registros.

Si cambio el NOT IN por NOT EXISTS:

SELECT
c.codcli + '-' + c.dig as ID, left(c.codcli,4) as password, c.mail,
c.nombre,
c.paterno + ' ' + c.materno as apellidos, 'cl', '', '', '', '',
'1-9' as RUT,c.fonoact as Fono, 1, 'ALUMNO', c.comuna as comuna,
c.ciudadact as ciudad, a.codcarpr, 1 centro_educ, 'PAIS', 1
tipo_usuario
FROM
TABLA1 a,
TABLA3 c
WHERE
a.rut=c.codcli
and a.ano_mat = 2008
and a.estacad in ('VIGENTE', 'egresado')
and a.codsede = @p_cod_sede
and a.fec_mat < '20080101'
and mail like '%@%.%'
and NOT exists (SELECT * FROM TABLA8 T)

Aca NO me devuelve registros.

Agradeceria sus comentarios.
Atte.
Penta.