locked
Fetch the Name on the basis of ID in oracle RRS feed

  • Question

  • User-1506965535 posted

    In my first table I am saving the ID's of PROPERTY_NAME and BUILDING_NAME

    and in another table [table name:- xxcus.XXACL_PN_PROJBUILD_V] I am saving the Name of the property and building.

    My first data script is

    CREATE TABLE XXCUS.XXACL_PN_FLAT_STATUS_HIS(
    MKEY NUMBER,
    PROJECT_ID NUMBER,
    FLAT_ID NUMBER,
    BUILDING_ID NUMBER,
    COMMENTS VARCHAR2(225 BYTE),
    FLAT_STATUS VARCHAR2(225 BYTE),
    CREATED_BY VARCHAR2(225 BYTE),
    CREATION_DATE DATE,
    LAST_UPDATE_DATE DATE,
    LAST_UPDATED_BY VARCHAR2(225 BYTE))
    

    and my second table script is:-

    CREATE OR REPLACE FORCE VIEW xxcus.xxacl_pn_projbuild_v(org_id,
    project_id,
    property_name,
    building_id,
    building,
    project_owner,
    owner_id,
    portfolio,
    tenure,
    pa_project_id) AS SELECT proj.org_id, proj.property_id project_id,
    property_name || ', ' || proj.attribute4 property_name,
    location_id building_id, pl.building,
    rsm.relation_mgr_name project_owner, rsm.relation_mgr_id owner_id,
    proj.portfolio, pt.meaning tenure, pl.attribute1 pa_project_id FROM apps.pn_properties_all proj, (SELECT * FROM apps.pn_locations_all bldg WHERE location_type_lookup_code = 'BUILDING'
        AND SYSDATE BETWEEN bldg.active_start_date AND bldg.active_end_date) pl,
    apps.xxacl_pn_build_add_det rsm,
    xxcus.xxacl_pn_plat_type_v pt WHERE proj.property_id = pl.property_id(+) AND pl.location_id = rsm.building_id(+) AND proj.tenure = pt.lookup_code(+) AND proj.attribute3 = 'Y' ORDER BY property_name, pl.building;

    Now what I want is

    Project_Name, Building_Name to be displayed in the gridview

    Saturday, January 9, 2016 11:42 AM

Answers

  • User269602965 posted

    A join to this table is missing
    xxcus.XXACL_PN_PROJBUILD_V

    and it contains the values for property_name

    CREATE OR REPLACE FORCE VIEW xxcus.xxacl_pn_projbuild_v
    (
    org_id,
    project_id,
    property_name,
    building_id,
    building,
    project_owner,
    owner_id,
    portfolio,
    tenure,
    pa_project_id
    ) 
    AS 
    SELECT 
      proj.org_id                              AS org_id, 
      proj.property_id                         AS project_id,
      property_name || ', ' || proj.attribute4 AS property_name,
      location_id                              AS building_id, 
      pl.building                              AS building,
      rsm.relation_mgr_name                    AS project_owner, 
      rsm.relation_mgr_id                      AS owner_id,
      proj.portfolio                           AS portfolio, 
      pt.meaning                               AS tenure, 
      pl.attribute1                            AS pa_project_id 
    FROM 
      apps.pn_properties_all proj,
      (SELECT * FROM apps.pn_locations_all bldg WHERE location_type_lookup_code = 'BUILDING' AND SYSDATE BETWEEN bldg.active_start_date AND bldg.active_end_date) pl,
      apps.xxacl_pn_build_add_det rsm,
      xxcus.xxacl_pn_plat_type_v pt 
    WHERE 
      proj.property_id = pl.property_id(+) 
      AND 
      pl.location_id = rsm.building_id(+) 
      AND 
      proj.tenure = pt.lookup_code(+) 
      AND 
      proj.attribute3 = 'Y' 
    ORDER BY property_name, pl.building;
    /
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2016 10:11 PM