From daemon Fri Feb 2 01:32:19 1996 Received: from ccvm.sunysb.edu by alice.jcc.com; (5.65v3.0/1.1.8.2/01Aug94-0142PM) id AA18684; Fri, 2 Feb 1996 01:32:14 -0500 Message-Id: <9602020632.AA18684@alice.jcc.com> Received: from CCVM.SUNYSB.EDU by ccvm.sunysb.edu (IBM VM SMTP V2R3) with BSMTP id 3148; Fri, 02 Feb 96 01:17:10 EST Received: from CCVM.SUNYSB.EDU (NJE origin LISTSERV@SBCCVM) by CCVM.SUNYSB.EDU (LMail V1.2a/1.8a) with BSMTP id 9489; Fri, 2 Feb 1996 01:17:05 -0500 Date: Fri, 2 Feb 1996 01:15:45 EST Reply-To: "ORACLE database mailing list." Sender: "ORACLE database mailing list." From: Chris Don Subject: IMPROMPTU X-To: seabrook@is.co.za, ORACLE-L@CCVM.SUNYSB.EDU To: Multiple recipients of list ORACLE-L ---- Mail Item Text Follows To: INTERNET--IBMMAIL Subject: IMPROMPTU I have been testing using Impromptu as a query tool against Oracle and love it but have hit a snag which I think is an Oracle constraint which I can't identify. I need to pull data from 2 tables GL_HEAD(general ledger records) and GL_MAST (general ledger master). I pull just the description from GL_MAST and 4 fields from GL_HEAD - FISC_YEAR, COMPANY, GL_ACCNO and a calculated field which is the opening balance + month1 debits - month1 credits + month2 debits - month2 credits etc.up to 16 months of values............ I list the Impromptu SQL below - it is interesting that Impromptu puts brackets around each part of the calculation - there are 31 of them !!! We have: Hardware: Compaq Proliant 100mHz CPU, 512 MB RAM, 23 GB disk Op system: Netware 3.11 Oracle: V 7.0.16.0 Oracle Netware SPX Server V 1.1.1.8 We use IPX/SPX protocol My problem is that this SQL crashes my server immediately whether I run from Impromptu or from SQL*PLUS. I get no Oracle error codes - within 1 second the server is down. I edited out all the brackets in Impromptu and the job runs fine. I also identified that if I have up to 12 fields in my calculated field then the job works fine (get results back in a few seconds using only 300 rows of data) but as I add the next field to the calculation it brings my server down. Unfortunately I can't tell my users to edit their Impromptu SQL code to remove the brackets every time so need to identify why my server is falling over - can anyone advise me on what to look for in Oracle logs or operating system level. Netware has not given us any reasonable error codes to work with so far ???? select T1.FISC_YEAR "c1",T1.COMPANY "c2",T1.GL_ACCNO "c3",T2.GL_DESC "c4", (((((((((((((((((((((((((((((((T1.BAL_LOCAL+T1.L_CURDR01)-T1.L_CURCR01)+T1.L_ CURDR02)-T1.L_CURCR02)+T1.L_CURDR03)-T1.L_CURCR03)+T1.L_CURDR04)-T1.L_CURCR04)+ T1.L_CURDR05)-T1.L_CURCR05)+T1.L_CURDR06)-T1.L_CURCR06)+T1.L_CURDR07)-T1.L_ CURCR07)+T1.L_CURDR08)-T1.L_CURCR08)+T1.L_CURDR09)-T1.L_CURCR09)+T1.L_CURDR10)- T1.L_CURCR10)+T1.L_CURDR11)-T1.L_CURCR11)+T1.L_CURDR12)-T1.L_CURCR12)+T1.L_ CURDR13)-T1.L_CURCR13)+T1.L_CURDR14)-T1.L_CURCR14)+T1.L_CURDR15)-T1.L_CURCR15)+ T1.L_CURDR16)-T1.L_CURCR16 "c5" from EIW.CUR_GLMAST T2,EIW.GL_HEAD T1 where T2.COMPANY=T1.COMPANY and T2.GL_ACCNO=T1.GL_ACCNO +--------------------------------------------------------------------+ | | / Chris Don, Data Administration Specialist | | \ South Africa / Engen Marketing Ltd, Engen Court,Cape Town | | | _/ Tel: South Africa 010-27-21-403-5143 | | \ _/ Fax: South Africa 010-27-21-403-4067 | | | _/ Internet Identity: ZAWLHCD1@IBMMAIL.COM | |Cape \ _/ | |Town \*_____/ | +--------------------------------------------------------------------+