June 26, 2012 at 3:38 am
Hi,
I have this query:
(SELECT
DISTINCT([NIF_ANTIGO]),
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
[BI],
[OLD_NIF],
[PSERVTCO],
[PSERVTCP],
[PSERVA],
[DTACESSACAOTCO],
[DTACESSACAOTCP],
[DTACESSACAOA]
FROM
BULK_CONTRIBUINTES_21 A
WHERE
A.VALIDO_BULK IS NULL AND
A.NIF_ANTIGO NOT IN
(SELECT NIF_ANTIGO FROM SGCTCentral.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL) )a
Shoudn't it return only distinct NIF_ANTIGOs results?
It is returning results like:
NIF_ANTIGO NIF Other Fields....
S4.01-1089595100079747LA0377NULLAPSGL
S4.01-1089595100079747LA0377NULLAPSGL
S4.01-1089595100079747LA0377NULLAPSGL
As you can see the three records have the same NIF_ANTIGO
June 26, 2012 at 3:46 am
I have seens that they have only a diferente value. It's the cod_bairro. It the three records only this field has diferente values....
How can I return a distinct NIF_antigo even if the cod_bairro has diferente values.
I wnat only one record for each NIF_antigo in the table
June 26, 2012 at 4:35 am
river1 (6/26/2012)
I have seens that they have only a diferente value. It's the cod_bairro. It the three records only this field has diferente values....How can I return a distinct NIF_antigo even if the cod_bairro has diferente values.
I wnat only one record for each NIF_antigo in the table
So, you have several rows for each distinct value of NIF_antigo. You want to pick only one of those rows, for each distinct value of NIF_antigo.
How do you decide which row to pick?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 26, 2012 at 4:42 am
"How do you decide which row to pick"
The records are like:
NIF_antigo ; NIF; Olther Fields; Cod_Bairro
00000001 1 1 0
00000001 1 1 1
00000001 1 1 2
00000002 2 2 0
00000003 3 3 7
00000003 3 3 6
I want that the query returns:
00000001 1 1
00000002 2 2
00000003 3 3
As to the value of cod_bairro it can retrieve any of them. I don't mind
For exemple:
IT can return:
00000001 1 1 0
or
00000001 1 1 1
or
00000001 1 1 2
June 26, 2012 at 4:47 am
river1 (6/26/2012)
...As to the value of cod_bairro it can retrieve any of them. I don't mind
Why return it at all, if you don't care about the value?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 26, 2012 at 4:54 am
Because I need a value for the filed.
For example:
the cod_bairro can be 1, 2, 3, .....
I need one of this values.
June 26, 2012 at 5:00 am
It Does not matter what is the value, but it most be a value from the field
June 26, 2012 at 5:44 am
river1 (6/26/2012)
It Does not matter what is the value, but it most be a value from the field
SELECT *
FROM
(SELECT
rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
[BI],
[OLD_NIF],
[PSERVTCO],
[PSERVTCP],
[PSERVA],
[DTACESSACAOTCO],
[DTACESSACAOTCP],
[DTACESSACAOA]
FROM BULK_CONTRIBUINTES_21 A
WHERE A.VALIDO_BULK IS NULL
AND A.NIF_ANTIGO NOT IN
(SELECT NIF_ANTIGO FROM SGCTCentral.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL) )a
WHERE rn = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 26, 2012 at 6:05 am
thank you very very much.
Excellent solution!
June 26, 2012 at 6:51 am
maybe you can help me with this too:
IF I do a select like:
(SELECT
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
[BI],
[OLD_NIF],
[PSERVTCO],
[PSERVTCP],
[PSERVA],
[DTACESSACAOTCO],
[DTACESSACAOTCP],
[DTACESSACAOA]
FROM
(SELECT
rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
[BI],
[OLD_NIF],
[PSERVTCO],
[PSERVTCP],
[PSERVA],
[DTACESSACAOTCO],
[DTACESSACAOTCP],
[DTACESSACAOA]
FROM
BULK_CONTRIBUINTES_21 A
WHERE
A.VALIDO_BULK IS NULL AND
A.NIF_ANTIGO NOT IN
(SELECT NIF_ANTIGO FROM SGCTCentral.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL)) A
WHERE rn = 1
UNION ALL
SELECT
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
NULL AS [COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
NULL AS [OBS],
[DT_BEGIN],
NULL AS BI,
NULL AS [OLD_NIF],
NULL AS [PSERVTCO],
NULL AS [PSERVTCP],
NULL AS [PSERVA],
NULL AS [DTACESSACAOTCO],
NULL AS [DTACESSACAOTCP],
NULL AS [DTACESSACAOA]
FROM
(SELECT rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
NULL AS [COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
NULL AS [OBS],
[DT_BEGIN],
NULL AS BI,
NULL AS [OLD_NIF],
NULL AS [PSERVTCO],
NULL AS [PSERVTCP],
NULL AS [PSERVA],
NULL AS [DTACESSACAOTCO],
NULL AS [DTACESSACAOTCP],
NULL AS [DTACESSACAOA]
FROM
BULK_CONTRIBUINTES_5 B
WHERE
B.VALIDO_BULK IS NULL AND
B.NIF_ANTIGO NOT IN
(SELECT NIF_ANTIGO FROM SGCTCentral.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL))D
WHERE RN=1
UNION ALL
SELECT
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_MET_TRIBUTARIO],
[COD_TP_SINGULARES],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
NULL AS BI,
NULL AS [OLD_NIF],
NULL AS [PSERVTCO],
NULL AS [PSERVTCP],
NULL AS [PSERVA],
NULL AS [DTACESSACAOTCO],
NULL AS [DTACESSACAOTCP],
NULL AS [DTACESSACAOA]
FROM
(
SELECT rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_MET_TRIBUTARIO],
[COD_TP_SINGULARES],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
NULL AS BI,
NULL AS [OLD_NIF],
NULL AS [PSERVTCO],
NULL AS [PSERVTCP],
NULL AS [PSERVA],
NULL AS [DTACESSACAOTCO],
NULL AS [DTACESSACAOTCP],
NULL AS [DTACESSACAOA]
FROM
BULK_CONTRIBUINTES_7 C
WHERE
C.VALIDO_BULK IS NULL AND
C.NIF_ANTIGO NOT IN
(SELECT NIF_ANTIGO FROM SGCTCentral.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL)) H
WHERE RN=1
)
The records returned by the query are 1330
but IF I insert this select inside a function and select the function, then , the records returned are much more.
how is this possible?
Here is my function:
ALTER FUNCTION[dbo].[fcSGCT_RETURN_NOVOS] (@DB varchar(50))
RETURNS TABLE
AS
RETURN
(SELECT
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
[BI],
[OLD_NIF],
[PSERVTCO],
[PSERVTCP],
[PSERVA],
[DTACESSACAOTCO],
[DTACESSACAOTCP],
[DTACESSACAOA]
FROM
(SELECT
rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
[BI],
[OLD_NIF],
[PSERVTCO],
[PSERVTCP],
[PSERVA],
[DTACESSACAOTCO],
[DTACESSACAOTCP],
[DTACESSACAOA]
FROM
BULK_CONTRIBUINTES_21 A
WHERE
A.VALIDO_BULK IS NULL AND
A.NIF_ANTIGO NOT IN
('SELECT NIF_ANTIGO FROM '+@db+'.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL')) A
WHERE rn = 1
UNION ALL
SELECT
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
NULL AS [COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
NULL AS [OBS],
[DT_BEGIN],
NULL AS BI,
NULL AS [OLD_NIF],
NULL AS [PSERVTCO],
NULL AS [PSERVTCP],
NULL AS [PSERVA],
NULL AS [DTACESSACAOTCO],
NULL AS [DTACESSACAOTCP],
NULL AS [DTACESSACAOA]
FROM
(SELECT rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
NULL AS [COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
NULL AS [OBS],
[DT_BEGIN],
NULL AS BI,
NULL AS [OLD_NIF],
NULL AS [PSERVTCO],
NULL AS [PSERVTCP],
NULL AS [PSERVA],
NULL AS [DTACESSACAOTCO],
NULL AS [DTACESSACAOTCP],
NULL AS [DTACESSACAOA]
FROM
BULK_CONTRIBUINTES_5 B
WHERE
B.VALIDO_BULK IS NULL AND
B.NIF_ANTIGO NOT IN
('SELECT NIF_ANTIGO FROM '+@db+'.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL'))D
WHERE RN=1
UNION ALL
SELECT
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_MET_TRIBUTARIO],
[COD_TP_SINGULARES],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
NULL AS BI,
NULL AS [OLD_NIF],
NULL AS [PSERVTCO],
NULL AS [PSERVTCP],
NULL AS [PSERVA],
NULL AS [DTACESSACAOTCO],
NULL AS [DTACESSACAOTCP],
NULL AS [DTACESSACAOA]
FROM
(
SELECT rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
[NIF_ANTIGO],
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_MET_TRIBUTARIO],
[COD_TP_SINGULARES],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
NULL AS BI,
NULL AS [OLD_NIF],
NULL AS [PSERVTCO],
NULL AS [PSERVTCP],
NULL AS [PSERVA],
NULL AS [DTACESSACAOTCO],
NULL AS [DTACESSACAOTCP],
NULL AS [DTACESSACAOA]
FROM
BULK_CONTRIBUINTES_7 C
WHERE
C.VALIDO_BULK IS NULL AND
C.NIF_ANTIGO NOT IN
('SELECT NIF_ANTIGO FROM '+@db+'.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL')) H
WHERE RN=1
)
Here is how I call it:
SELECT * FROM dbo.fcSGCT_RETURN_NOVOS ('SGCTCentral')
Shouldn't both return the same result set?
June 26, 2012 at 6:58 am
You can't use dynamic SQL in a function - if it was valid dynamic SQL, it would return an error. What it's doing is creating a string:
A.NIF_ANTIGO NOT IN
('SELECT NIF_ANTIGO FROM SGCTCentral.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL')) A
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 26, 2012 at 7:56 am
You should consider using CTE's for this query, to make it more readable if nothing else:
;WITH
A AS (SELECT rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
* -- substitute * with actual column list
FROM BULK_CONTRIBUINTES_21 A
WHERE A.VALIDO_BULK IS NULL
AND A.NIF_ANTIGO NOT IN
(SELECT NIF_ANTIGO FROM SGCTCentral.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL)
),
D AS (SELECT rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
* -- substitute * with actual column list
FROM BULK_CONTRIBUINTES_5 B
WHERE B.VALIDO_BULK IS NULL
AND B.NIF_ANTIGO NOT IN
(SELECT NIF_ANTIGO FROM SGCTCentral.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL)
),
H AS (SELECT rn = ROW_NUMBER() OVER(PARTITION BY NIF_ANTIGO ORDER BY Cod_Bairro),
* -- substitute * with actual column list
FROM
BULK_CONTRIBUINTES_7 C
WHERE C.VALIDO_BULK IS NULL
AND C.NIF_ANTIGO NOT IN
(SELECT NIF_ANTIGO FROM SGCTCentral.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL)
)
SELECT * -- DO NOT substitute
FROM A WHERE rn = 1
UNION ALL
SELECT * -- DO NOT substitute
FROM D WHERE rn = 1
UNION ALL
SELECT * -- DO NOT substitute
FROM H WHERE rn = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 26, 2012 at 7:59 am
WHAT ARE CTE's?
June 26, 2012 at 8:13 am
river1 (6/26/2012)
WHAT ARE CTE's?
Can you confirm that your database is SQL Server 2008? If so, have a look in the help system (Books Online) for your client, which is probably SSMS.
A CTE is often described as an inline view - it exists for the duration of the query in which it's defined.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 26, 2012 at 8:54 am
river1 (6/26/2012)
Hi,I have this query:
(SELECT
DISTINCT([NIF_ANTIGO]),
[NIF],
[FILIAL_NUMBER],
[STATUS],
[COD_GRP_CONTRIBUINTE],
[COD_TP_SINGULARES],
[COD_MET_TRIBUTARIO],
[COD_TP_IMPOSTOS],
[GRANDE_CONTRIBUINTE],
[NOME],
[MORADA],
[COD_CIDADE],
[C_POSTAL],
[TELEFONE],
[FAX],
[COD_PROVINCIA],
[COD_MUNICIPIO],
[COD_REP_FISCAL],
[DT_INICIO],
[DT_ALTERACAO],
[DT_CESSACAO],
[COD_MOT_CESSACAO],
[MOT_CESSACAO],
[COD_TP_INSTITUICAO],
[COD_FUNC],
[COD_COMUNA],
[COD_BAIRRO],
[OBS],
[DT_BEGIN],
[BI],
[OLD_NIF],
[PSERVTCO],
[PSERVTCP],
[PSERVA],
[DTACESSACAOTCO],
[DTACESSACAOTCP],
[DTACESSACAOA]
FROM
BULK_CONTRIBUINTES_21 A
WHERE
A.VALIDO_BULK IS NULL AND
A.NIF_ANTIGO NOT IN
(SELECT NIF_ANTIGO FROM SGCTCentral.DBO.CONTRIBUINTES WHERE NIF_ANTIGO IS NOT NULL) )a
Shoudn't it return only distinct NIF_ANTIGOs results?
It is returning results like:
NIF_ANTIGO NIF Other Fields....
S4.01-1089595100079747LA0377NULLAPSGL
S4.01-1089595100079747LA0377NULLAPSGL
S4.01-1089595100079747LA0377NULLAPSGL
As you can see the three records have the same NIF_ANTIGO
The reason the DISTINCT did not return what you were expecting is it doesn't work on a single column, it is looking at ALL of the columns in the SELECT list and returning only those records that are DISTINCT in all the columns.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply