Supprimer doublon

nimano69 Messages postés 3 Statut Membre -  
 nimano69 -
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

1 réponse

  1.
    funnycat Messages postés 164 Statut Membre 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,
    0
    1. 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
      0

Discussions similaires

Supprimer l'historique de commandes et d'achats sur Amazon
Matthew5 -
Adraen -

25 réponses