Supprimer doublon

nimano69 Messages postés 2 Date d'inscription Statut Membre Dernière intervention -
nimano69 - 14 juin 2013 à 00:17

J'ai fait une requête et malgré que j'utilise Distinct, j'obtiens encore quelques doublons. Voici ma requête. Dans ma requête, je dois avoir un seul F242P002.R_IDF. Merci de votre aide.

SELECT distinct
F242P002.R_IDF,
F242P002.R_MATRICUL,
F242P002.R_EXVILLE,
F242P002.R_BASSIN,
F242P002.V_SOUS_BASSIN,
F242P002.R_UVOIS,
F242P002.R_CIVICDE,
F242P002.R_NOMRUE

FROM
S38.F242P002 F242P002
LEFT OUTER JOIN S38.F242PBDM F242PBDM ON F242P002.R_NOENREG = F242PBDM.P044_NO_ENREG
LEFT OUTER JOIN S38.F242P004 F242P004 ON F242P002.R_NOENREG = F242P004.T_NOENREG

LEFT OUTER JOIN S38.LOI67_DEMANDE_REV LOI67_DEMANDE_REV ON F242P002.R_IDF = LOI67_DEMANDE_REV.L67_IDF_ID,

S38.F242P042 F242P042,

S38.F242P251 F242P251

WHERE
F242P002.R_IDF = F242P042.P042_MTF_IDF_ID AND
F242P002.R_IDF = F242P251.P251_MTF_IDF_ID AND
(
F242P002.R_DATEINSP < TO_DATE('20060101', 'YYYYMMDD') AND
F242P002.R_ORIGCAL in ('P','MP') AND F242P002.R_TYPEDOSS in ('B','BS')
)

ORDER BY F242P002.R_IDF asc

Afficher la suite

A voir également:

Supprimer doublon
Supprimer rond bleu whatsapp - Guide
Supprimer page word - Guide
Supprimer pub youtube - Accueil - Streaming
Fichier impossible à supprimer - Guide
Supprimer doublon excel - Guide

1 réponse

Réponse 1 / 1

funnycat Messages postés 153 Date d'inscription Statut Membre Dernière intervention 65

Bonjour.
A tout hasard, est-ce que ça résoud votre problème si vous utilisez un GROUP BY.
A voir si ça fonctionne :

SELECT  
	F242P002.R_IDF, 
	F242P002.R_MATRICUL, 
	F242P002.R_EXVILLE, 
	F242P002.R_BASSIN, 
	F242P002.V_SOUS_BASSIN, 
	F242P002.R_UVOIS, 
	F242P002.R_CIVICDE, 
	F242P002.R_NOMRUE 

FROM            S38.F242P002 F242P002 
LEFT OUTER JOIN S38.F242PBDM F242PBDM                   ON F242P002.R_NOENREG = F242PBDM.P044_NO_ENREG 
LEFT OUTER JOIN S38.F242P004 F242P004                   ON F242P002.R_NOENREG = F242P004.T_NOENREG 
LEFT OUTER JOIN S38.LOI67_DEMANDE_REV LOI67_DEMANDE_REV ON F242P002.R_IDF = LOI67_DEMANDE_REV.L67_IDF_ID, 
														S38.F242P042 F242P042, 
														S38.F242P251 F242P251 
WHERE 1=1
AND   F242P002.R_IDF = F242P042.P042_MTF_IDF_ID 
AND   F242P002.R_IDF = F242P251.P251_MTF_IDF_ID 
AND 
	( 
	F242P002.R_DATEINSP < TO_DATE('20060101', 'YYYYMMDD') 
	AND 
	F242P002.R_ORIGCAL in ('P','MP') AND F242P002.R_TYPEDOSS in ('B','BS') 
	) 
GROUP BY F242P002.R_IDF, 
	F242P002.R_MATRICUL, 
	F242P002.R_EXVILLE, 
	F242P002.R_BASSIN, 
	F242P002.V_SOUS_BASSIN, 
	F242P002.R_UVOIS, 
	F242P002.R_CIVICDE, 
	F242P002.R_NOMRUE 
ORDER BY F242P002.R_IDF asc

Cordialement,

nimano69

Merci cela ne fonctionne pas. J'ai le message suivant:

440-SQL Execution error Ora-00972 n'est pas une expression GROUP BY

J'avais tronqué ma requête. Voici la requête en entier.

SELECT distinct
F242P002.R_IDF,
F242P002.R_MATRICUL,
F242P002.R_EXVILLE,
F242P002.R_BASSIN,
F242P002.V_SOUS_BASSIN,
F242P002.R_UVOIS,
F242P002.R_CIVICDE,
F242P002.R_NOMRUE,
F242P002.R_NOAPT,
F242P002.R_AGEORIG,
F242P002.R_AGEAPP,
F242P002.R_CODECAT,
F242P002.R_TYPEDOSS,
F242P002.R_MODELE,
F242P002.R_CODEUTIL,
F242P002.R_NBLOG,
F242P002.R_28_DT_INSPECTION_CODE_3_6_7,
F242P002.R_DATEINSP,
F242P042.TRF_TYPE_FIC,
(TO_CHAR(F242P002.R_DATEINSP,'yyyy')) as "ANNEEVISITE",
F242P251.R28_A_CODE_1,
F242P251.R28_B_DATE_VISITE_1,
F242P251.R28_A_CODE_2,
F242P251.R28_B_DATE_VISITE_2,
F242P251.R28_A_CODE_3,
F242P251.R28_B_DATE_VISITE_3,
CASE
WHEN F242P002.R_DATEINSP = F242P251.R28_B_DATE_VISITE_1 THEN F242P251.R28_A_CODE_1
WHEN F242P002.R_DATEINSP = F242P251.R28_B_DATE_VISITE_2 THEN F242P251.R28_A_CODE_2
WHEN F242P002.R_DATEINSP = F242P251.R28_B_DATE_VISITE_3 THEN F242P251.R28_A_CODE_3
ELSE 'ERREUR'
END as CODE,
F242P042.F00_CODE_SURTAXE,
F242P002.R_LOCNONRE,
F242P042.F62_CODE_NI_1,
F242P042.F62_CODE_NI_2,
F242P042.F62_CODE_NI_3,
CASE
WHEN LOI67_DEMANDE_REV.L67_DEMANDE_NUMERO>='D201000001' AND
LOI67_DEMANDE_REV.L67_DIV_TRAITANTE='244' AND
LOI67_DEMANDE_REV.L67_REP_NO Is Null THEN LOI67_DEMANDE_REV.L67_DEMANDE_NUMERO
else ''
end as DDR,

F242P042.F751_PROPRIETAIRE_1,
F242P042.F751_PROPRIETAIRE_2,
F242P042.F792_F795_EAE,
case
when F242PBDM.B01_DATE_TRANSACTION>=('2008-01-01') and
F242PBDM.B21C_ANNOT_2 Not In ('C20','C60','U00','J60','H00','M60','F60','H60') AND
F242PBDM.B06_CONSTRUIT='X' AND
F242PBDM.B01_NATURE_ACTE In (101,1600,1800) AND
F242P004.T_CDANNOT1 Not In ('C60','F60','M60','H60','C20','U00','J60','H00') AND
F242P004.T_CDANNOT2 not in ('C60','F60','M60','H60','C20','U00','J60','H00') AND
F242P002.R_SUPERPI>'1' AND
F242P004.T_PRIXVRAJ >= '40000' AND
F242P002.R_AIRHSOL>'1' AND
F242PBDM.B21_NB_MAT='1' AND
F242PBDM.B21_CODE_VALIDITE='1' then F242PBDM.B01_DATE_TRANSACTION
when
(F242PBDM.B21C_ANNOT_2 Is Null) AND
(F242PBDM.B01_DATE_TRANSACTION>=('2008-01-01')) AND
(F242PBDM.B06_CONSTRUIT='X') AND
F242P004.T_PRIXVRAJ >= '40000' AND
(F242PBDM.B01_NATURE_ACTE In (101,1600,1800)) AND
(F242P004.T_CDANNOT1 Not In ('C60','F60','M60','H60','C20','U00','J60','H00')) AND
(F242P004.T_CDANNOT2 is null) AND
(F242P002.R_SUPERPI>1) AND (F242P002.R_AIRHSOL>1) AND
(F242PBDM.B21_NB_MAT=1) AND
(F242PBDM.B21_CODE_VALIDITE=1) then F242PBDM.B01_DATE_TRANSACTION
when
(F242PBDM.B21C_ANNOT_2 Is Null) AND
(F242PBDM.B01_DATE_TRANSACTION>=('2008-01-01')) AND
(F242PBDM.B06_CONSTRUIT='X') AND
F242P004.T_PRIXVRAJ >= '40000' AND
(F242PBDM.B01_NATURE_ACTE In (101,1600,1800)) AND
(F242P004.T_CDANNOT1 ='A70') AND
(F242P002.R_SUPERPI>1) AND (F242P002.R_AIRHSOL>1) AND
(F242PBDM.B21_NB_MAT=1) AND
(F242PBDM.B21_CODE_VALIDITE=1) then F242PBDM.B01_DATE_TRANSACTION
end as DATEVENTE,

case
when F242PBDM.B01_DATE_TRANSACTION>=('2008-01-01') and
F242PBDM.B21C_ANNOT_2 Not In ('C20','C60','U00','J60','H00','M60','F60','H60') AND
F242PBDM.B06_CONSTRUIT='X' AND
F242PBDM.B01_NATURE_ACTE In (101,1600,1800) AND
F242P004.T_CDANNOT1 Not In ('C60','F60','M60','H60','C20','U00','J60','H00') AND
F242P004.T_CDANNOT2 not in ('C60','F60','M60','H60','C20','U00','J60','H00') AND
F242P004.T_PRIXVRAJ >= '40000' AND
F242P002.R_SUPERPI>'1' AND
F242P002.R_AIRHSOL>'1' AND
F242PBDM.B21_NB_MAT='1' AND
F242PBDM.B21_CODE_VALIDITE='1' then F242P004.T_PRIXVRAJ
when
(F242PBDM.B21C_ANNOT_2 Is Null) AND
(F242PBDM.B01_DATE_TRANSACTION>=('2008-01-01')) AND
(F242PBDM.B06_CONSTRUIT='X') AND
(F242PBDM.B01_NATURE_ACTE In (101,1600,1800)) AND
(F242P004.T_CDANNOT1 Not In ('C60','F60','M60','H60','C20','U00','J60','H00')) AND
(F242P004.T_CDANNOT2 is null) AND
F242P004.T_PRIXVRAJ >= '40000' AND
(F242P002.R_SUPERPI>1) AND (F242P002.R_AIRHSOL>1) AND
(F242PBDM.B21_NB_MAT=1) AND
(F242PBDM.B21_CODE_VALIDITE=1) then F242P004.T_PRIXVRAJ
when
(F242PBDM.B21C_ANNOT_2 Is Null) AND
(F242PBDM.B01_DATE_TRANSACTION>=('2008-01-01')) AND
(F242PBDM.B06_CONSTRUIT='X') AND
F242P004.T_PRIXVRAJ >= '40000' AND
(F242PBDM.B01_NATURE_ACTE In (101,1600,1800)) AND
(F242P004.T_CDANNOT1 ='A70') AND
(F242P002.R_SUPERPI>1) AND (F242P002.R_AIRHSOL>1) AND
(F242PBDM.B21_NB_MAT=1) AND
(F242PBDM.B21_CODE_VALIDITE=1) then F242P004.T_PRIXVRAJ
end as PRIXVENTERAJUSTE,

CASE

WHEN F242P042.F792_F795_EAE = 'O' THEN 'INSPECTION MANUELLE - FERME'
WHEN F242P002.R_CODEUTIL BETWEEN '8000' AND '8999' THEN 'INSPECTION MANUELLE - FERME'

WHEN F242P042.F751_PROPRIETAIRE_1 like '%BANQUE%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%BANK%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%FAILLITE%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%SYNDIC%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%HYPO%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%FINAN%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%TRUST%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%ASSURANCE%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%INVESTORS%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%VILLE DE LAVAL' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like 'CAISSE%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%SHQ%' then 'INSPECTION MANUELLE - PROPRIETAIRE'
WHEN F242P042.F751_PROPRIETAIRE_1 like '%GOUVERNEMENT%' then 'INSPECTION MANUELLE - PROPRIETAIRE'

WHEN F242P002.R_CODEUTIL NOT LIKE '1000' then 'INSPECTION MANUELLE - CODE UTILISATION'

WHEN LOI67_DEMANDE_REV.L67_DEMANDE_NUMERO>='D201000001' AND
LOI67_DEMANDE_REV.L67_DIV_TRAITANTE='244' AND
LOI67_DEMANDE_REV.L67_REP_NO Is Null THEN 'INSPECTION MANUELLE - DEMANDE DE RÉVISION'

when F242PBDM.B01_DATE_TRANSACTION>=('2008-01-01') and
F242PBDM.B21C_ANNOT_2 Not In ('C20','C60','U00','J60','H00','M60','F60','H60') AND
F242PBDM.B06_CONSTRUIT='X' AND
F242PBDM.B01_NATURE_ACTE In (101,1600,1800) AND
F242P004.T_CDANNOT1 Not In ('C60','F60','M60','H60','C20','U00','J60','H00') AND
F242P004.T_CDANNOT2 not in ('C60','F60','M60','H60','C20','U00','J60','H00') AND
F242P002.R_SUPERPI>'1' AND
F242P002.R_AIRHSOL>'1' AND
F242PBDM.B21_NB_MAT='1' AND
F242PBDM.B21_CODE_VALIDITE='1' then 'DATE DE VENTE 5 ANS & MOINS'
when
(F242PBDM.B21C_ANNOT_2 Is Null) AND
(F242PBDM.B01_DATE_TRANSACTION>=('2008-01-01')) AND
(F242PBDM.B06_CONSTRUIT='X') AND
(F242PBDM.B01_NATURE_ACTE In (101,1600,1800)) AND
(F242P004.T_CDANNOT1 Not In ('C60','F60','M60','H60','C20','U00','J60','H00')) AND
(F242P004.T_CDANNOT2 is null) AND
(F242P002.R_SUPERPI>1) AND (F242P002.R_AIRHSOL>1) AND
(F242PBDM.B21_NB_MAT=1) AND
F242PBDM.B21_CODE_VALIDITE='1' then 'DATE DE VENTE 5 ANS & MOINS'
when
(F242PBDM.B21C_ANNOT_2 Is Null) AND
(F242PBDM.B01_DATE_TRANSACTION>=('2008-01-01')) AND
(F242PBDM.B06_CONSTRUIT='X') AND
(F242PBDM.B01_NATURE_ACTE In (101,1600,1800)) AND
(F242P004.T_CDANNOT1 ='A70') AND
(F242P002.R_SUPERPI>1) AND (F242P002.R_AIRHSOL>1) AND
(F242PBDM.B21_NB_MAT=1) AND
F242PBDM.B21_CODE_VALIDITE='1' then 'DATE DE VENTE 5 ANS & MOINS'

else 'MAJI'
end EXCLUSION,
MAJI_SUIVI.DSI_DTE_CREATION,
MAJI_SUIVI.F128_4_CODIFICATION

FROM
S38.F242P002 F242P002
LEFT OUTER JOIN S38.F242PBDM F242PBDM ON F242P002.R_NOENREG = F242PBDM.P044_NO_ENREG
LEFT OUTER JOIN S38.F242P004 F242P004 ON F242P002.R_NOENREG = F242P004.T_NOENREG
LEFT OUTER JOIN S38.MAJI_SUIVI MAJI_SUIVI ON F242P002.R_IDF = MAJI_SUIVI.DSI_IDF

LEFT OUTER JOIN S38.LOI67_DEMANDE_REV LOI67_DEMANDE_REV ON F242P002.R_IDF = LOI67_DEMANDE_REV.L67_IDF_ID,

S38.F242P042 F242P042,

S38.F242P251 F242P251

WHERE
F242P002.R_IDF = F242P042.P042_MTF_IDF_ID AND
F242P002.R_IDF = F242P251.P251_MTF_IDF_ID AND
(
F242P002.R_DATEINSP < TO_DATE('20060101', 'YYYYMMDD') AND
F242P002.R_ORIGCAL in ('P','MP') AND F242P002.R_TYPEDOSS in ('B','BS') AND

F242P042.TRF_TYPE_FIC = '251' AND F242P002.R_NBLOG <6

)

ORDER BY F242P002.R_DATEINSP ASC,F242P002.R_IDF asc

Votre réponse