1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

please guide me why i am unable to create view

Discussion in 'SQL PL/SQL' started by RIAZ, May 28, 2009.

  1. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Code (SQL):

    CREATE VIEW cust_inv_variance_v
    AS
       SELECT msi.inventory_item_id AS inv_item_id,
                 msi.segment1
              || '-'
              || msi.segment2
              || '-'
              || msi.segment3
              || '-'
              || msi.segment4 AS item_code,
                 mc.segment1
              || '-'
              || mc.segment2
              || '-'
              || mc.segment3
              || '-'
              || mc.segment4 AS category_id,
              mc.description AS category_description,
              msi.description item_description, msi.primary_uom_code uom,
              DECODE (cpic.pac_period_id,
                      :p_period_id, ROUND (NVL (cpic.total_layer_quantity, 0), 2),
                      0
                     ) qty_1,
              DECODE (cpic.pac_period_id,
                      :p_period_id, ROUND (NVL (cpic.item_cost, 0), 2),
                      0
                     ) unit_cost_1,
              DECODE (cpic.pac_period_id,
                      :p_period_id, ROUND ((  NVL (cpic.item_cost, 0)
                                            * NVL (cpic.total_layer_quantity, 0)
                                           ),
                                           2
                                          ),
                      0
                     ) total_cost_1,
              DECODE (cpic.pac_period_id,
                      :p_period_id - 1, ROUND (NVL (cpic.total_layer_quantity, 0),
                                               2
                                              ),
                      0
                     ) qty_2,
              DECODE (cpic.pac_period_id,
                      :p_period_id - 1, ROUND (NVL (cpic.item_cost, 0), 2),
                      0
                     ) unit_cost_2,
              DECODE (cpic.pac_period_id,
                      :p_period_id - 1, ROUND ((  NVL (cpic.item_cost, 0)
                                                * NVL (cpic.total_layer_quantity,
                                                       0
                                                      )
                                               ),
                                               2
                                              ),
                      0
                     ) total_cost_2
         FROM mtl_item_categories mic,
              mtl_categories mc,
              mtl_system_items msi,
              cst_pac_item_costs cpic
        WHERE cpic.pac_period_id IN (:p_period_id, :p_period_id - 1)
          AND cpic.total_layer_quantity <> 0
          AND cpic.cost_group_id = 1004
          AND cpic.inventory_item_id = mic.inventory_item_id
          AND mic.organization_id = 105
          AND mic.category_set_id = 1
          AND mc.category_id = mic.category_id
          AND msi.inventory_item_id = mic.inventory_item_id
          AND msi.organization_id = mic.organization_id
     
  2. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
    hi RIAZ you have the ORA-01027: bind variables not allowed for data definition operations error. You cannot use the prompt p_period_id in a create view statement.
     
  3. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    thanks, got it.

    But I want to create or replace the view before running the report [developing in oracle developer 6i] I got a place called Report Trigger [Before Report] here i want to fill this view by user's input values.

    Please guide me how to do ??
     
  4. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
    well I am actually not familiar with oracle reports but shouldn't assigning the parameter value to a variable and using the variable in the view work? Just guessing... try it.