Results 1 to 3 of 3

Thread: view

  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Unanswered: view

    what we are trying to do is update the data from FYI tables and view that I wrote need to be changed coz fyi table are using varchar text formate for date and my table has date formate and we need the date to be in date formate.
    The first query doesn't have date formate but when it ran it worked fine but since we have to chang to be able to get date formate .
    This query runs fine but it took 2 and half hr to send the text file into Excel. We are talking about only3800 records. We are using TOAD .

    CREATE OR REPLACE FORCE VIEW HMIS.VW_HYDRO
    (SYS_FKEY, SYS_DKEY, IDNO, COMPANY, FIRST_NAME,
    LAST_NAME, JOB_TITLE, ADDRESS1, ADDRESS2, CITY,
    STATE, ZIP_CODE, PHONE, HYDROSTATIC_FLAG, ACETYLENE_FLAG,
    ULTRASONIC_FLAG, ACOUSTIC_FLAG, OTHER_REQUEST, SPEC, CODE_TYPE,
    CLTST, COMMENTS, RECEIVED_DATE, APPROVAL_DATE, INSPECTOR_NAME,
    INSPECTION_DATE, VIDEO_DATE)
    AS
    select SYS_FKEY, SYS_DKEY, IDNO,
    COMPANY, FIRST_NAME, LAST_NAME, JOB_TITLE,
    ADDRESS1, ADDRESS2, CITY, STATE, ZIP_CODE, PHONE,
    HYDROSTATIC_FLAG, ACETYLENE_FLAG, ULTRASONIC_FLAG, ACOUSTIC_FLAG, OTHER_REQUEST,
    SPEC, CODE_TYPE,
    CLTST, COMMENTS,
    TO_DATE(RECEIVED_DATE, 'YYYY-mm-dd') RDATE,
    Decode(substr(APPROVAL_DATE, 1, 2), '19', To_Date(Approval_date, 'YYYY-mm-dd'),
    '20', To_Date(Approval_date, 'YYYY-mm-dd'),
    To_Date(Approval_date, 'mm-dd-YYYY')) APDAT, INSPECTOR_NAME,
    TO_DATE(INSPECTION_DATE, 'YYYY-mm-dd') IDATE, TO_DATE(VIDEO_DATE, 'YYYY-mm-dd') Video
    from vwDataset_Hydro A
    where sys_fkey in
    (select max(sys_fkey) from vwDataset_Hydro B where A.idno=B.idno);

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: view

    1) What is the SQL for view vwDataset_Hydro?
    2) Is there an index on the idno column used there?

  3. #3
    Join Date
    Oct 2003
    Posts
    15
    no there is no index.


    CREATE OR REPLACE FORCE VIEW HMIS.VWDATASET_HYDRO
    (SYS_FKEY, SYS_DKEY, IDNO, COMPANY, FIRST_NAME,
    LAST_NAME, JOB_TITLE, ADDRESS1, ADDRESS2, CITY,
    STATE, ZIP_CODE, PHONE, HYDROSTATIC_FLAG, ACETYLENE_FLAG,
    ULTRASONIC_FLAG, ACOUSTIC_FLAG, OTHER_REQUEST, SPEC, CODE_TYPE,
    CLTST, COMMENTS, RECEIVED_DATE, APPROVAL_DATE, INSPECTOR_NAME,
    INSPECTION_DATE, VIDEO_DATE)
    AS
    select
    A.sys_fkey, A.sys_dkey, C.fyi_reference_id,
    B.sys_author, C.fyi_first_name, C.fyi_last_name, C.fyi_position,
    C.fyi_address1, C.fyi_address2, C.fyi_city, C.fyi_state, C.fyi_postal_code, C.fyi_phone,
    A.fyi_hydrostatic_flag, A.fyi_acetylene_flag, A.fyi_ultrasonic_flag, A.fyi_acoustic_flag, A.fyi_other_request,
    A.fyi_specification, A.fyi_code_type,
    B.sys_subject, B.sys_comments,
    C.fyi_received_date,
    (select max(fyi_approval_date)
    from fyi_ohma_doc
    where sys_fkey=A.sys_fkey and fyi_reference_id=A.fyi_reference_id ) AP_date,
    (select max(fyi_inspector_name)
    from fyi_ohma_doc
    where sys_fkey=A.sys_fkey and fyi_reference_id=A.fyi_reference_id ) Insp_name,
    (select max(fyi_inspection_date)
    from fyi_ohma_doc
    where sys_fkey=A.sys_fkey and fyi_reference_id=A.fyi_reference_id ) Insp_date,
    (select max(fyi_video_date)
    from fyi_ohma_doc
    where sys_fkey=A.sys_fkey and fyi_reference_id=A.fyi_reference_id ) Video_date
    from fyi_ohma_doc A
    inner join fyiadm.fyi_sysdata B
    on A.SYS_FKEY=B.sys_fkey and
    A.sys_dkey=B.sys_dkey and
    A.sys_ver_major=B.sys_ver_major and
    A.sys_ver_minor=B.sys_ver_minor and
    B.sys_title='Request' AND
    B.SYS_DELETE_DATE is Null
    inner join fyiadm.Fyi_ohma_wf C
    on C.sys_fkey=B.sys_fkey
    where
    ((substr(A.fyi_reference_id,1,1) in ('A','B','C','D','E','F','G','H','I','J','V')) AND
    (substr(A.fyi_reference_id,1,2) NOT IN ('AN','CA','CO','CT','EX','IN'))) AND
    A.fyi_disposition='Approve';

Posting Permissions

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