Results 1 to 5 of 5

Thread: View Help

  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: View Help

    I have created a view using the below sql. This issue that I am having is that it does not return data. Can someone see something that I might be doing wrong?

    CREATE OR REPLACE VIEW VW_CC_RPT_HAZWASTE_CT ( WO_DTL_ID,
    SERVICE_DATE, GEN_NAME, GEN_ADDRESS_DISPLAY, DOT_SHIP_DESC,
    GALLONS, TSD_NAME, TSD_ADDRESS_DISPLAY, HANDLE_CODE,
    PRODUCT_ID, QTY, GEN_ID ) AS SELECT /*+first_row*/
    WOD.WO_DTL_ID AS WO_DTL_ID,
    WO.SERVICED_DATE AS SERVICE_DATE,
    G.NAME AS GEN_NAME,
    A.address_1 || ', ' ||
    DECODE(A.address_2, NULL, NULL, A.address_2 || ', ') ||
    A.city || ', ' ||
    SP.ABBREVIATION || ' ' ||
    A.zip_code AS GEN_ADDRESS_DISPLAY,
    DS.SHIPPING_NAME AS DOT_SHIP_DESC,
    ET.CAPACITY_GALS AS GALLONS,
    T.NAME AS TSD_NAME,
    TA.ADDRESS_1 || ', '||
    DECODE(TA.ADDRESS_2, NULL, NULL, TA.ADDRESS_2 || ', ')||
    TA.CITY || ', ' ||
    SP.ABBREVIATION || ' ' ||
    TA.ZIP_CODE AS TSD_ADDRESS_DISPLAY,
    WT.FINAL_HANDLE_CODE AS HANDLE_CODE,
    WOD.PRODUCT_ID AS PRODUCT_ID,
    WOD.QTY - WOD.QTY_ADJUSTMENT AS QTY,
    G.GENERATOR_ID AS GEN_ID

    FROM
    CCMSDBA.WORK_ORDER WO,
    CCMSDBA.WO_DTL WOD,
    CCMSDBA.WASTESTREAM WS,
    CCMSDBA.DOT_SHIPPING DS,
    CCMSDBA.TSD T,
    CCMSDBA.TSD_ADDRESS TA,
    CCMSDBA.GENERATOR G,
    CCMSDBA.GENERATOR_ADDRESS GA,
    CCMSDBA.ADDRESS A,
    CCMSDBA.EQUIPMENT E,
    CCMSDBA.EQUIP_TYPE ET,
    CCMSDBA.STATE_PROVINCE SP,
    CCMSDBA.WS_TSD WT,
    CCMSDBA.EPA_REPORTING_STATUS ERS

    WHERE
    WO.WORK_ORDER_ID = WOD.WORK_ORDER_ID AND
    WS.WASTESTREAM_ID = WOD.WASTESTREAM_ID AND
    WOD.WASTESTREAM_ID = WT.WASTESTREAM_ID AND
    WT.TSD_ID = T.TSD_ID AND
    T.TSD_ID = TA.TSD_ID AND
    TA.STATE_PROVINCE_ID = SP.STATE_PROVINCE_ID AND
    WOD.EQUIPMENT_ID = E.EQUIPMENT_ID AND
    E.EQUIP_TYPE_ID = ET.EQUIP_TYPE_ID AND
    WO.GENERATOR_ID = G.GENERATOR_ID AND
    G.GENERATOR_ID = GA.GENERATOR_ID AND
    GA.ADDRESS_ID = A.ADDRESS_ID AND
    G.EPA_REPORTING_STATUS_ID = ERS.EPA_REPORTING_STATUS_ID AND
    GA.GENERATOR_ADDRESS_TYPE_CODE = 'STREET' AND
    SP.ABBREVIATION = 'CT' AND
    (G.EPA_ID LIKE 'CT%' OR
    ERS.SHORT_CODE = 'CESQG')

    ORDER BY G.GENERATOR_ID





    Thanks,

    Matt

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Interesting...

    What is the behaviour when you just issue the SELECT statement against your database ?

  3. #3
    Join Date
    Oct 2003
    Posts
    7
    Originally posted by cvandemaele
    Interesting...

    What is the behaviour when you just issue the SELECT statement against your database ?
    Same Behaviour.

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    It returns zero rows or you have killed the stmt after waiting too much ?

    If the second is true, maybe it is full scanning the table with the LIKE applied - analyzing the tables and indexes, or putting an index on that column, may help.

  5. #5
    Join Date
    Sep 2003
    Posts
    16
    If a view returns no rows, I normally investigate it by commenting out links (and their referenced columns) until data starts to appear. At that point look at the last link you've just commented out and it will point you in the right direction for where your problem lies.

    For instance, if you link to a table which MAY or MAY NOT contain relevant rows, you may need an outer join on that table, otherwise ORACLE will not return the rows with partial data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •