Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Unanswered: Oracle stored procedure

    I am new to oracle and need to create a stored procedure for this sql statement. I also need to pass two parameters to table1 in the create table statement and table2 in the from statement.

    create table table1 as
    select distinct
    f.clm_cntl_num,
    d.car_dme_line_key,
    f.claim_key,
    f.bene_clm_acnt_num,
    f.bene_ident_cd,
    d.line_dgns_cd,
    d.line_1st_expns_dt,
    d.line_last_expns_dt,
    d.line_hcpcs_cd,
    f.ORG_NPI_NUM,
    d.CARR_LINE_PRFRMG_NPI_NUM,
    d.DMERC_LINE_SUPLR_NPI_NUM,
    d.LINE_PRVDR_TAX_NUM,
    d.LINE_PLC_SRVC_CD,
    d.LINE_PRVDR_PMT_AMT,
    e.bene_hicn_num
    from table2 m
    join carclm.clm_fact f on m.bene_clm_acnt_num=f.bene_clm_acnt_num
    join carclm.car_dme_line_grp d on d.claim_key=f.claim_key
    join dcs1.car_clm_fact_ext e on d.claim_key=e.claim_key
    left join HCPCS_EXCLU3 x on x.hcpcs=d.line_hcpcs_cd
    where m.bene_ident_cd=f.bene_ident_cd
    and e.net_flag='Y'
    and d.line_1st_expns_dt>=m.clm_from_dt
    and d.LINE_LAST_EXPNS_DT<=m.CLM_THRU_DT
    and x.FY = to_char(m.clm_from_dt+92,'YYYY')

    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am new to oracle and need to create a stored procedure for this sql statement.
    No, you do NOT need any stored procedure.

    CREATE TABLE statements should be static & stored in code repository
    Code:
    CREATE TABLE table1 AS
      SELECT DISTINCT f.clm_cntl_num,
                      d.car_dme_line_key,
                      f.claim_key,
                      f.bene_clm_acnt_num,
                      f.bene_ident_cd,
                      d.line_dgns_cd,
                      d.line_1st_expns_dt,
                      d.line_last_expns_dt,
                      d.line_hcpcs_cd,
                      f.org_npi_num,
                      d.carr_line_prfrmg_npi_num,
                      d.dmerc_line_suplr_npi_num,
                      d.line_prvdr_tax_num,
                      d.line_plc_srvc_cd,
                      d.line_prvdr_pmt_amt,
                      e.bene_hicn_num
      FROM   table2 m
             join carclm.clm_fact f
               ON m.bene_clm_acnt_num = f.bene_clm_acnt_num
             join carclm.car_dme_line_grp d
               ON d.claim_key = f.claim_key
             join dcs1.car_clm_fact_ext e
               ON d.claim_key = e.claim_key
             left join hcpcs_exclu3 x
               ON x.hcpcs = d.line_hcpcs_cd
      WHERE  m.bene_ident_cd = f.bene_ident_cd
             AND e.net_flag = 'Y'
             AND d.line_1st_expns_dt >= m.clm_from_dt
             AND d.line_last_expns_dt <= m.clm_thru_dt
             AND x.fy = To_char(m.clm_from_dt + 92, 'YYYY')
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2012
    Posts
    3
    How do I pass parameters to table1 and table2 they change consistanly.
    thanks
    liz

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How do I pass parameters to table1 and table2 they change consistanly.
    Then you have design FLAW since data is obviously NOT Normalized.
    Multiple tables with exact same column names should NOT exist!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2012
    Posts
    3
    The tables that are dynamic are temp tables. The data in the temp tables are then tranformed to a production table to create a report.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I did not see such answer in my search. I do not want to use email to SMS.
    much more often than NOT, "temp" tables are NOT necessary with Oracle.

    If you choose to (ab)use "temporary" tables, then make Global Temporary Table ONCE,
    & individual session can utilize it as necessary.

    Let me google that for you
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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