[sqr-users] 6.2 -> 8.3 Upgrade problems

["Joe R. Jah" <jjah@cloud.ccsf.cc.ca.us>]

Hi Folks,

Our DBA has suddenly upgraded our SQR from 6.2 to 8.3.  Many of our old
programs do not run with 8.3.  Is there any "rule of thumb" checklist for
such an upgrade?

Specifically, how can I fix the following error?  I appreciate any
pointers.

Hyperion SQR Server 8.3
Copyright (c) 1994-2004 Hyperion Solutions Corporation.  All Rights Reserved.

Executing SWRMC07 report
(SQR 5528) ORACLE OCIStmtExecute error 904 in cursor 9:
   ORA-00904: "SCRATTR_ATTR_CODE": invalid identifier
SQL:  SELECT ssbsect_crn, ssbsect_subj_code, ssbsect_crse_numb,
      ssbsect_seq_numb, decode (ssbsect_seq_numb, '075', 'STAB', '076',
      'STAB', '111', 'AFAS', '112', 'AFAS', '113', 'AFAS', '114', 'AFAS',
      decode(ssbsect_subj_code, 'JRNY','APPR',
      nvl(scrattr_attr_code,ssbsect_subj_code))), 'C',
      nvl(ssbsect_crse_title,'***************'), nvl(ssbsect_credit_hrs,0),
      nvl(scbcrse_credit_hr_low,0), nvl(scbcrse_title,'***************'),
      nvl(ssrmeet_mon_day,' '), nvl(ssrmeet_tue_day,' '),
      nvl(ssrmeet_wed_day,' '), nvl(ssrmeet_thu_day,' '),
      nvl(ssrmeet_fri_day,' '), nvl(ssrmeet_sat_day,' '),
      nvl(ssrmeet_sun_day,' '), nvl(ssrmeet_begin_time,'0000'),
      nvl(ssrmeet_end_time,'0000'), nvl(ssrmeet_bldg_code,'**** '),
      substr(nvl(ssrmeet_room_code,'****'),1,4), nvl(ssrmeet_catagory,' '),
      ssbsect_max_enrl, nvl(ssbsect_sess_code,'D'),
      nvl(scbcrse_coll_code,'99'), nvl(ssbsect_acct_code,'  '),
      ssbsect_ssts_code, ssbsect_ptrm_code, scrlevl_levl_code, sirasgn_pidm,
      ssbsect_camp_code, ssbsect_schd_code,
      to_char(ssbsect_ptrm_start_date,'MMDDYY'),
      to_char(ssbsect_ptrm_end_date,'MMDDYY'), nvl(ssbovrr_divs_code,'****'),
      nvl(scbcrse_divs_code,'****'), nvl(stvbldg_desc,'******'),
      nvl(stvsubj_desc,'????'), nvl(ssbsect_prnt_ind,'Y'),
      nvl(scbcrse_dept_code,'0000'), ssrmeet.rowid,
      decode((nvl(ssrmeet_bldg_code,ssbsect_camp_code)), 'AIRP',  'A', 'ALA*',
       'O', 'EVRT',  'B', 'JLK',   'B', 'V',     'B', 'LGB*',  'C', 'CHNB', 
      'D', 'C',     'E', 'DEY*',  'E', 'DNTN',  'F', 'D',     'F', 'ELCO', 
      'N', 'EVANS', 'H', 'E',     'H', 'EXPLR', 'I', 'FORT',  'J', 'FAD*', 
      'K', 'HOS*',  'K', 'JAD',   'K', 'PDO*',  'K', 'PRE*',  'K', 'J',    
      'K', 'LHO*',  'L', 'MMS',   'M', 'MIS',   'O', 'M',     'O', 'PRESM',
      'O', 'SEC',   'P', 'S',     'P', 'CLY*',  'Q', 'WAR*',  'R', ' '),
      nvl(ssbsect_census_enrl,0), nvl(ssbsect_enrl,0), sirasgn_fcnt_code,
      decode(to_char(ssbsect_ptrm_start_date,'MON'), 'MAY', '1', 'JUN', '1',
      'JUL', '2', 'AUG', '2', ' '), to_char(ssbsect_ptrm_start_date,'MMDD') 
      from   scbcrse a, stvbldg, stvsubj, ssbovrr, scrattr, ssrmeet, sirasgn,
      scrlevl c, ssbsect where  ssbsect_term_code = :1 and scrlevl_levl_code =
      '01' and  ssbsect_ssts_code <> 'C' and  ssbsect_acct_code <> 'CS' and 
      ssbsect_acct_code <> 'CA' and  ssbsect_crn = ssbovrr_crn(+) and 
      ssbsect_term_code = ssbovrr_term_code(+) and  c.scrlevl_subj_code =
      ssbsect_subj_code and  c.scrlevl_crse_numb = ssbsect_crse_numb and 
      c.scrlevl_eff_term = (select max(d.scrlevl_eff_term) from scrlevl d
      where d.scrlevl_crse_numb = ssbsect_crse_numb and d.scrlevl_subj_code =
      ssbsect_subj_code and d.scrlevl_eff_term <= ssbsect_term_code) and 
      ssbsect_subj_code = scrattr_subj_code(+) and  ssbsect_crse_numb =
      scrattr_crse_numb(+) and  scrattr_attr_code(+) <> 'BSKL' and 
      scrattr_attr_code(+) <> 'PBSK' and  scrattr_attr_code(+) <> 'LIO*' and 
      scrattr_attr_code(+) <> 'INTR' and  scrattr_attr_code(+) <> 'CON*' and 
      scrattr_attr_code(+) <> 'CIS' and  scrattr_attr_code(+) <> 'WEBG' and 
      scrattr_attr_code(+) <> 'NDA' and  scrattr_attr_code(+) <> 'NBK' and 
      scrattr_attr_code(+) not in ('CA1','CA2','CA3','CB1','CB2','CB3') and 
      scrattr_attr_code(+) not in ('CB4','CC1','CC2','CD1','CD2','CD3') and 
      scrattr_attr_code(+) not in ('CD4','CD5','CD6','CD7','CD8','CD9') and 
      scrattr_attr_code(+) not in ('CE1','CE2','I1A','I1B','I1C','I2A') and 
      scrattr_attr_code(+) not in ('I3A','I3B','I4A','I4B','I4C','I4D') and 
      scrattr_attr_code(+) not in ('I4E','I4F','I4G','I4H','I4I','I4J') and 
      scrattr_attr_code(+) not in ('CD0','NDA','NT','%CV0') and 
      scrattr_attr_code(+) not in ('I5A','I5B''I6A','I7A','I7B','I8A','I8B')
      and  ssbsect_term_code >= scrattr_eff_term(+) and  ssbsect_crse_numb =
      a.scbcrse_crse_numb and  ssbsect_subj_code = a.scbcrse_subj_code and 
      a.scbcrse_eff_term = (select max(b.scbcrse_eff_term) from   scbcrse b
      where  b.scbcrse_crse_numb = ssbsect_crse_numb and b.scbcrse_subj_code
      = ssbsect_subj_code and b.scbcrse_eff_term  <= ssbsect_term_code) and 
      ssbsect_subj_code = stvsubj_code and  ssbsect_crn = ssrmeet_crn(+) and 
      ssbsect_term_code = ssrmeet_term_code(+) and  ssrmeet_term_code =
      sirasgn_term_code(+) and  ssrmeet_crn       = sirasgn_crn(+) and 
      ssrmeet_catagory  = sirasgn_category(+) and  ssrmeet_catagory  <> '98'
      and  ssrmeet_bldg_code = stvbldg_code(+) and  ssbsect_crn is not null
      union select ssbsect_crn, ssbsect_subj_code, ssbsect_crse_numb,
      ssbsect_seq_numb, decode (ssbsect_seq_numb, '075', 'STAB', '076',
      'STAB', '111', 'AFAS', '112', 'AFAS', '113', 'AFAS', '114', 'AFAS',
      decode(ssbsect_subj_code, 'JRNY','APPR',
      nvl(scrattr_attr_code,ssbsect_subj_code))), 'S',
      nvl(ssbsect_crse_title,'***************'), nvl(ssbsect_credit_hrs,0),
      nvl(scbcrse_credit_hr_low,0), nvl(scbcrse_title,'***************'),
      nvl(ssrmeet_mon_day,' '), nvl(ssrmeet_tue_day,' '),
      nvl(ssrmeet_wed_day,' '), nvl(ssrmeet_thu_day,' '),
      nvl(ssrmeet_fri_day,' '), nvl(ssrmeet_sat_day,' '),
      nvl(ssrmeet_sun_day,' '), nvl(ssrmeet_begin_time,'0000'),
      nvl(ssrmeet_end_time,'0000'), nvl(ssrmeet_bldg_code,'*****'),
      substr(nvl(ssrmeet_room_code,'****'),1,4), nvl(ssrmeet_catagory,' '),
      ssbsect_max_enrl, nvl(ssbsect_sess_code,'D'),
      nvl(scbcrse_coll_code,'99'), nvl(ssbsect_acct_code,'  '),
      ssbsect_ssts_code, ssbsect_ptrm_code, scrlevl_levl_code, sirasgn_pidm,
      ssbsect_camp_code, ssbsect_schd_code,
      to_char(ssrmeet_start_date,'mmddyy'),
      to_char(ssrmeet_end_date,'mmddyy'), nvl(ssbovrr_divs_code,'****'),
      nvl(scbcrse_divs_code,'****'), nvl(stvbldg_desc,'******'),
      nvl(stvsubj_desc,'??????'), nvl(ssbsect_prnt_ind,'Y'),
      nvl(scbcrse_dept_code,'0000'), ssrmeet.rowid,
      decode((nvl(ssrmeet_bldg_code,'****')), 'AIRP',  'A', 'ALA*',  'N',
      'EVRT',  'B', 'JLK',   'B', 'V',     'B', 'CHNB',  'C', 'C', 'C',
      'DEY*',  'D', 'DNTN',  'E', 'D',     'E', 'ELCO',  'F', 'EVANS', 'G',
      'E',     'G', 'EXPLR', 'H', 'FORT',  'I', 'FAD*',  'J', 'JAD', 'J',
      'PRE*',  'J', 'J',     'J', 'LHO*',  'K', 'MMS',   'L', 'MIS', 'M',
      'M',     'M', 'PRESM', 'N', 'SEC',   'O', 'S',     'O', 'CLY*', 'P',
      'WAR*',  'Q', ' '), nvl(ssbsect_census_enrl,0), nvl(ssbsect_enrl,0),
      sirasgn_fcnt_code, to_char(ssbsect_ptrm_start_date,'MON'),
      to_char(ssbsect_ptrm_start_date,'MMDD') from scbcrse a, stvbldg,
      stvsubj, ssbovrr, ssrattr g, ssrmeet, sirasgn, scrlevl c, ssbsect where 
      ssbsect_term_code = :1 and scrlevl_levl_code = '01' and 
      ssbsect_ssts_code <> 'C' and  ssbsect_acct_code <> 'CS' and 
      ssbsect_acct_code <> 'CA' and  ssbsect_crn = ssbovrr_crn(+) and 
      ssbsect_term_code = ssbovrr_term_code(+) and  scrlevl_subj_code =
      ssbsect_subj_code and  scrlevl_crse_numb = ssbsect_crse_numb and 
      c.scrlevl_eff_term = (select max(d.scrlevl_eff_term) from scrlevl d
      where d.scrlevl_crse_numb = ssbsect_crse_numb and d.scrlevl_subj_code =
      ssbsect_subj_code and d.scrlevl_eff_term <= ssbsect_term_code) and 
      ssbsect_term_code = ssrattr_term_code and  ssbsect_crn       =
      ssrattr_crn and  ssrattr_attr_code <> 'BSKL' and  ssrattr_attr_code <>
      'PBSK' and  ssrattr_attr_code <> 'LIO*' and  ssrattr_attr_code <> 'INTR'
      and  ssrattr_attr_code <> 'CON*' and  ssrattr_attr_code <> 'CIS' and 
      ssrattr_attr_code <> 'WEBG' and  ssrattr_attr_code <> 'NDA' and 
      ssrattr_attr_code not in ('CA1','CA2','CA3','CB1','CB2','CB3') and 
      ssrattr_attr_code not in ('CB4','CC1','CC2','CD1','CD2','CD3') and 
      ssrattr_attr_code not in ('CD4','CD5','CD6','CD7','CD8','CD9') and 
      ssrattr_attr_code not in ('CE1','CE2','I1A','I1B','I1C','I2A') and 
      ssrattr_attr_code not in ('I3A','I3B','I4A','I4B','I4C','I4D') and 
      ssrattr_attr_code not in ('I4E','I4F','I4G','I4H','I4I','I4J') and 
      ssrattr_attr_code not in ('CD0','%CV0') and  ssrattr_attr_code not in
      ('I5A','I5B','I6A','I7A','I7B','I8A','I8B') and  ssbsect_crse_numb =
      a.scbcrse_crse_numb and  ssbsect_subj_code = a.scbcrse_subj_code and 
      a.scbcrse_eff_term = (select max(b.scbcrse_eff_term) from   scbcrse b
      where  b.scbcrse_crse_numb = ssbsect_crse_numb and b.scbcrse_subj_code
      = ssbsect_subj_code and b.scbcrse_eff_term  <= ssbsect_term_code) and 
      ssbsect_subj_code = stvsubj_code and  ssbsect_crn = ssrmeet_crn(+) and 
      ssbsect_term_code = ssrmeet_term_code(+) and  ssrmeet_term_code =
      sirasgn_term_code(+) and  ssrmeet_crn       = sirasgn_crn(+) and 
      ssrmeet_catagory  = sirasgn_category(+) and  ssrmeet_catagory  <>'98'
      and  ssrmeet_bldg_code = stvbldg_code(+) and ssbsect_crn is not null
      order by 5,2,3,4,1,41 

Error on line 434:
   (SQR 3722) Could not set up cursor.

SQR: Program Aborting.

Regards,

Joe
-- 
     _/   _/_/_/       _/              ____________    __o
     _/   _/   _/      _/         ______________     _-\<,_
 _/  _/   _/_/_/   _/  _/                     ......(_)/ (_)
  _/_/ oe _/   _/.  _/_/ ah          jjah@cloud.ccsf.edu


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users