[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]
RE: [sqr-users] 6.2 -> 8.3 Upgrade problems

Subject: RE: [sqr-users] 6.2 -> 8.3 Upgrade problems
From: "Alexander, Steve" <Steven.Alexander@sanjoseca.gov>
Date: Fri, 17 Jun 2005 09:19:27 -0700
Delivery-date: Fri, 17 Jun 2005 11:20:08 -0500
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
I know this isn't what you asked, but this looks like "write-only" code,
never intended to be maintained.  It's joining nine tables, and some with
outer joins.  (That probably runs slowly.)  The tables seem to be far from
normalized.  Most of the tables don't have aliases, so you can't tell which
fields belong to which tables.  The elaborate decode statements suggest
either that the contents of the tables are not really what you wished them
to be or the SQR program was written without regard for the actual data.

The mystery is why "SCRATTR_ATTR_CODE" is an invalid identifier.  You didn't
mention any change to the tables or to the Oracle software.  Perhaps this
very long SQL statement got truncated when SQR sent it to the database
server, and the table containing SCRATTR_ATTR_CODE was omitted from the FROM
clause.

-----Original Message-----
From: Joe R. Jah [mailto:jjah@cloud.ccsf.cc.ca.us]
Sent: Wednesday, June 15, 2005 8:49 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] 6.2 -> 8.3 Upgrade problems


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

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
Follow-Ups:
- RE: [sqr-users] 6.2 -> 8.3 Upgrade problems
  - From: "Paul Hoyte" <pi_hoyte@si-proservices.com>
- RE: [sqr-users] 6.2 -> 8.3 Upgrade problems
  - From: "Joe R. Jah" <jjah@cloud.ccsf.cc.ca.us>
Prev by Date: [sqr-users] 6.2 -> 8.3 Upgrade problems
Next by Date: RE: [sqr-users] 6.2 -> 8.3 Upgrade problems
Previous by thread: [sqr-users] 6.2 -> 8.3 Upgrade problems
Next by thread: RE: [sqr-users] 6.2 -> 8.3 Upgrade problems
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page