PLS-00402: alias erforderlich in der SELECT-Liste des Cursors zu vermeiden, doppelte Spaltennamen

Ich geschrieben habe, eine gespeicherte Prozedur an, ziehen Sie die Daten aus drei verschiedenen Tabelle mit join, aber ich bin nicht in der Lage das Ergebnis zu erhalten.Ich bin auch versucht, zu übergeben, dynamische Tabelle, aber es ist ein Fehler aufgetreten.

CREATE OR REPLACE Procedure DE_DUP_PRO1 (Dy_File_Name in varchar2) 
   --RETURN NUMBER
AS
       v_hol varchar2(300);

  CURSOR De_DUB_CUR IS
  SELECT S.TRANS_GUID AS OLD_TRANS_GUID,
  H.TRANS_GUID    AS NEW_TRANS_GUID,
  CASE
    WHEN H.TRANS_GUID IS NULL
    THEN 0
    ELSE 1
  END as TRN_STAT,
        P.INTR_PHARMACY_ID, S.EXTRNL_PHARMACY_ID,  S.PHARMACY_NM,  S.PHARMACY_ADDR,  S.SUPPLIERS_PSCR_DRUG_CD,  S.PSCR_DRUG_IPU_CD,
        'IPU',  S.PSCR_DRUG_DESC,  S.DSPNSD_DRUG_PACK_SIZE,  S.RX_ID,  S.RX_ITEM_SEQ,  S.RX_REPEAT_STATUS,  S.RX_TYP,  S.EXMT_STATUS,
        S.PSCR_QTY,  S.NRSG_HM_IND,  S.RX_DSPNSD_DT,  S.RX_DSPNSD_TM,  S.SUPPLIERS_DSPNSD_DRUG_CD,  S.DSPNSD_DRUG_IPU_CD, 'IPU',
        S.DSPNSD_DRUG_DESC,  S.GENERIC_USE_MARKER,  S.DSPNSD_UNIT_OF_QTY,  S.DSPNSD_QTY, 'EUR',  S.COST_OF_DSPNSD_QTY,  S.VERBOSE_DOSAGE

  FROM (SELECT stg.*, row_number() over ( partition BY key_clmns_hash ORDER BY 1 ) AS RN FROM  T_MCL_30404_20150317_020 stg ) s


  LEFT JOIN ps_pharmacy p ON s.extrnl_pharmacy_id = p.extrnl_pharmacy_id LEFT JOIN ps_rx_hist H ON h.key_clmns_hash = s.key_clmnS_hash
  AND h.rx_dspnsd_dt = s.rx_dspnsd_dt AND s.supplier_pharmacy_cd = h.SUPPLIER_PHARMACY_CD AND s.detl_clmns_hash <> h.detl_clmns_hash WHERE S.RN = 1;

BEGIN


      FOR De_Dub_rec IN  De_DUB_CUR
      LOOP

        DBMS_OUTPUT.PUT_LINE ( De_Dub_rec.OLD_TRANS_GUID || '|' || De_Dub_rec.NEW_TRANS_GUID || '|' || De_Dub_rec.TRN_STAT || '|' || De_Dub_rec.P.INTR_PHARMACY_ID || '|' || De_Dub_rec.S.EXTRNL_PHARMACY_ID
 || '|' || De_Dub_rec.S.PHARMACY_NM || '|' || De_Dub_rec.S.PHARMACY_ADDR || '|' || De_Dub_rec.S.SUPPLIERS_PSCR_DRUG_CD|| '|' || De_Dub_rec.S.PSCR_DRUG_IPU_CD || '|' || 'IPU' 
 || '|' || De_Dub_rec.S.PSCR_DRUG_DESC || '|' || De_Dub_rec.S.DSPNSD_DRUG_PACK_SIZE || '|' || De_Dub_rec.S.RX_ID || '|' || De_Dub_rec.S.RX_ITEM_SEQ || '|' || De_Dub_rec.S.RX_REPEAT_STATUS 
 || '|' || De_Dub_rec.S.RX_TYP || '|' || De_Dub_rec.S.EXMT_STATUS || '|' || De_Dub_rec.S.PSCR_QTY || '|' || De_Dub_rec.S.NRSG_HM_IND || '|' || De_Dub_rec.S.RX_DSPNSD_DT 
 || '|' || De_Dub_rec.S.RX_DSPNSD_TM || '|' || De_Dub_rec.S.SUPPLIERS_DSPNSD_DRUG_CD || '|' || De_Dub_rec.S.DSPNSD_DRUG_IPU_CD || '|' || 'IPU' || '|' || De_Dub_rec.S.DSPNSD_DRUG_DESC 
 || '|' || De_Dub_rec.S.GENERIC_USE_MARKER || '|' || De_Dub_rec.S.DSPNSD_UNIT_OF_QTY  || '|' || De_Dub_rec.S.DSPNSD_QTY || '|' || 'EUR' || '|' || De_Dub_rec.S.COST_OF_DSPNSD_QTY 
 || '|'|| De_Dub_rec.S.VERBOSE_DOSAGE );

      END LOOP;

--   RETURN 0;

END DE_DUP_PRO1;
/

immer wenn ich ausführen gespeicherte Prozedur bekomme ich folgende Fehlermeldung

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3      PL/SQL: SQL Statement ignored
19/96    PL/SQL: ORA-00942: table or view does not exist
31/9     PL/SQL: Statement ignored
31/32    PLS-00364: loop index variable 'DE_DUB_REC' use is invalid

LINE/COL ERROR(Now it is resolved)
-------- -----------------------------------------------------------------
28/7     PL/SQL: Statement ignored
28/7     PLS-00402: alias required in SELECT list of cursor to avoid
         duplicate column names
InformationsquelleAutor | 2015-08-15
Schreibe einen Kommentar