1 Reply Latest reply: Jan 5, 2014 5:36 AM by jariola RSS

    ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

    Ahmed Alsaied

      Dears,

      i have this problem after i create tabular from depend on view

      ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

      
      

      this a query that i use

       

      select 
      "INVOICE_DET",
      "INVOICE_DET" INVOICE_DET_DISPLAY,
      "INVOICE_ID",
      "STORAGE_CODE",
      "ITEM_QNTY",
      "ITEM_PRICE",
      "BONS_QNTY",
      "DISC_VALUE",
      "TOT_VAL",
      "LOCATION_CODE",
      "BATCH_DET",
      "ITEM_CODE",
      "ITEM_NAME",
      "UOM_CODE",
      "UOM_NAME",
      "SIZE_CODE",
      "SIZE_NAME",
      "COLOR_CODE",
      "COLOR_NAME",
      "STOREG_BRCHAS_BATCHN",
      "EXPR_DATE",
      "PROD_DATE",
      "ITEM_DET_ID"
      from "#OWNER#"."BRCHAS_INVOICE_DET_VIEW"
      

       

      this the view i created

       

       

       

        CREATE OR REPLACE FORCE VIEW "RETAIL"."BRCHAS_INVOICE_DET_VIEW" ("INVOICE_DET", "INVOICE_ID", "STORAGE_CODE", "ITEM_QNTY", "ITEM_PRICE", "BONS_QNTY", "DISC_VALUE", "TOT_VAL", "LOCATION_CODE", "BATCH_DET", "ITEM_CODE", "ITEM_NAME", "UOM_CODE", "UOM_NAME", "SIZE_CODE", "SIZE_NAME", "COLOR_CODE", "COLOR_NAME", "STOREG_BRCHAS_BATCHN", "EXPR_DATE", "PROD_DATE", "ITEM_DET_ID") AS 
        SELECT invoice_det, invoice_id, storage_code, item_qnty, item_price,
                bons_qnty, disc_value, tot_val, location_code, batch_det, item_code,
                item_name, uom_code, uom_name, size_code, size_name, color_code,
                color_name, storeg_brchas_batchn, expr_date, prod_date, item_det_id
           FROM (SELECT d.invoice_det, d.invoice_id, d.storage_code, d.item_qnty,
                        d.item_price, d.bons_qnty, d.disc_value, d.tot_val,
                        d.location_code, d.batch_det, i.item_code,
                        get_item_name (i.item_code) item_name, i.uom_code,
                        get_uom_desc (i.uom_code) uom_name, i.size_code,
                        get_size_name (i.size_code) size_name, i.color_code,
                        get_color_name (i.color_code) color_name,
                        dd.storeg_brchas_batchn, b.expr_date, b.prod_date,
                        i.item_det_id
                   FROM brchas_invoice_det d,
                        brchas_batch dd,
                        brchas_batch_det b,
                        item_uom_size_color i
                  WHERE dd.storeg_brchas_batchinternn = b.storeg_brchas_batchinternn
                    AND d.batch_det = b.batch_det
                    AND b.item_det_id = i.item_det_id
                    AND d.batch_det IS NOT NULL 
                   
                 UNION ALL
                 SELECT d.invoice_det, d.invoice_id, d.storage_code, d.item_qnty,
                        d.item_price, d.bons_qnty, d.disc_value, d.tot_val,
                        d.location_code, d.batch_det, i.item_code,
                        get_item_name (i.item_code) item_name, i.uom_code,
                        get_uom_desc (i.uom_code) uom_name, i.size_code,
                        get_size_name (i.size_code) size_name, i.color_code,
                        get_color_name (i.color_code) color_name,
                        NULL storeg_brchas_batchn, NULL expr_date, NULL prod_date,
                        i.item_det_id
                   FROM brchas_invoice_det d, item_uom_size_color i
                  WHERE d.item_det_id = i.item_det_id 
                  AND d.batch_det IS NULL) ;
      
      
        CREATE OR REPLACE TRIGGER "RETAIL"."BRCHAS_INVOICE_DET_VIEW_TRG" 
      INSTEAD OF DELETE OR INSERT OR UPDATE
      ON RETAIL.BRCHAS_INVOICE_DET_VIEW 
      REFERENCING NEW AS NEW OLD AS OLD
      FOR EACH ROW
      DECLARE
      V_PRICE NUMBER;
      V_BONUS_QNTY NUMBER;
      V_DISC_VAL NUMBER;
      BEGIN
        IF INSERTING THEN 
            INSERT INTO BRCHAS_INVOICE_DET
            (INVOICE_DET,
            INVOICE_ID ,
            STORAGE_CODE,
            ITEM_QNTY,
            ITEM_PRICE,
            BATCH_DET,
            BONS_QNTY,
            TOT_VAL,
            LOCATION_CODE,
            DISC_VALUE,
            CREATED_USER,
            CREATED_DATE,
            ITEM_DET_ID
            )
            VALUES
            ("BRCHAS_INVOICE_DET_SEQ".nextval,
            :NEW.INVOICE_ID ,
            :NEW.STORAGE_CODE,
            :NEW.ITEM_QNTY,
            :NEW.ITEM_PRICE,
            :NEW.BATCH_DET,
            :NEW.BONS_QNTY,
            (:NEW.ITEM_QNTY * NVL(:NEW.ITEM_PRICE,0)) - NVL(:NEW.DISC_VALUE,0),
            :NEW.LOCATION_CODE,
            :NEW.DISC_VALUE,
            nvl(v('APP_USER'),user),
            sysdate,
            :NEW.ITEM_DET_ID);
        ELSIF UPDATING THEN
              UPDATE BRCHAS_INVOICE_DET
              SET INVOICE_ID    =:NEW.INVOICE_ID,
                  STORAGE_CODE  =:NEW.STORAGE_CODE,
                  ITEM_QNTY     =:NEW.ITEM_QNTY,
                  ITEM_PRICE    = :NEW.ITEM_PRICE,
                  BATCH_DET     =:NEW.BATCH_DET,
                  BONS_QNTY     = :NEW.BONS_QNTY,
                  TOT_VAL       =(:NEW.ITEM_QNTY * NVL(:NEW.ITEM_PRICE,0)) - NVL(:NEW.DISC_VALUE,0),
                  LOCATION_CODE =:NEW.LOCATION_CODE,
                  DISC_VALUE    = :NEW.DISC_VALUE,
                  ITEM_DET_ID =:NEW.ITEM_DET_ID
              WHERE INVOICE_DET =:NEW.INVOICE_DET;
        ELSE
              DELETE FROM BRCHAS_INVOICE_DET
               WHERE INVOICE_DET =:OLD.INVOICE_DET;
        END IF;
           
         EXCEPTION
           WHEN OTHERS THEN
             -- Consider logging the error and then re-raise
             RAISE;
      END BRCHAS_INVOICE_DET_VIEW_TRG; 
      /
      ALTER TRIGGER "RETAIL"."BRCHAS_INVOICE_DET_VIEW_TRG" ENABLE;
      

       

       

       

      please i need help

       

      Thanks

      Ahmed