From owner-sqr-users@USA.NET Tue Jan 2 18:06:14 1996 Date: Tue, 2 Jan 1996 17:45:07 EST From: "Suresh P. Reddy" <74634.400@COMPUSERVE.COM> Subject: Useful Scripts Need helpful hints in writting good SQR code. Any help is highly appreciated. Thanks SPR From owner-sqr-users@USA.NET Sun Jan 7 23:19:52 1996 Date: Sun, 7 Jan 1996 22:16:37 -0500 From: Ray Ontko Subject: Re: Useful Scripts > Need helpful hints in writting good SQR code. Any help is highly appreciated. > Thanks > SPR Suresh, SQR netters, Here's a start at SQR naming conventions and coding standards. I mean for this to get the discussion rolling, so please offer additions, alternatives, and comments. Ray ----- SQR Coding Guidelines January 7, 1996 Ray Ontko Purpose This document provides recommended naming conventions and coding guidelines for SQR programs. Overview This document addresses the following areas of SQR program development: o File Naming o Variable Naming o Reusable Program Modules o Passing Parameters between Procedures o Internal Documentation File Naming All SQR program files should be named xxx.sqr. Include files should be named xxx.sqh, where xxx is the name of the module defined in the file, or the primary module if several are defined. Max files for a given program should be named xxx.sqm, where xxx.sqr is the name of the program they are used with. Under Unix, all file names should be all lower-case. Variable Naming Avoid use of the hyphen "-" in variable names. Use the underscore "_" instead. Avoid use of capitalization in variable names. Use the underscore "_" character to separate different "words" within the variable name. Reusable Program Modules All reusable program modules should be created as .sqh files. Each sqh file should contain one callable procedure (it may also contain procedures that are called exclusively by the procedure). The file should be named .sqh. Any procedures which are used exclusively within the reusable program module (i.e., they are not also reusable) should be named _xxx (where is the name of the main procedure in the file) to avoid naming conflicts with user-defined procedures or other reusable procedures. Avoid using global variables in reusable program modules. If these are needed for persistence between calls to the module, all such global variables should be named _xxx (starting with # or $ or &, of course). Reusable program modules used by a program should be referenced using the "#include" directive immediately after "end-report". All reusable program modules should be wrappered with: #ifndef ____ #define ____ ... #endif Any "#include" needed by a reusable program module should be added after the "#define ____ " shown above. Passing Parameters between Procedures All procedures implemented as external reusable program modules should pass all values into and out of the procedure using parameters (no global variables). Reusable procedures which pass no parameters should be declared LOCAL. Procedures specific to a program may use global variables, but this practice is recommended only for the smallest of programs. These procedures should be declared within the program file (not #included). Internal Documentation All programs and include files should include the following header: !++ ! ! Name ! ! xxx.sqr (or xxx.sqh) ! ! Description ! ! ! ! Parameters ! ! ! ! Notes ! ! ! !-- ! ! Modification History ! ! Name Date Comment ! --------------- ---------- ----------------------------------------- ! yyyy.mm.dd Created ! A simple program can be used to extract the information between the "!++" and the "!--". From owner-sqr-users@USA.NET Fri Jan 12 13:30:50 1996 Date: Fri, 12 Jan 1996 12:26:34 -0400 From: GARRIDF@SNYFARVA.CC.FARMINGDALE.EDU Subject: Can Easy SQR access flat files? Is there a way for ESQR (EASY SQR) to either READ a flat file or INPUT data to be used in selecting data from the Oracle tables? We wanted to match a file of ID's to the name, address and other information on the database, and create an extract file for word processing. -------------------------------------------------------------------------------- | F R A N C E S G A R R I D O | |Associate Director for Administrative Computing Phone: (516) 420-2757 | |SUNY College of Technology at Farmingdale Fax: (516) 420-2696 | |Computer Center, Greenley Hall DECNET: SFARVA::GARRIDF| |2350 Route 110 BITNET: GARRIDF@SNYFARVA| |Farmingdale, New York 11735-1021 INTERNET:garridf@snyfarva.cc.farmingdale.edu| -------------------------------------------------------------------------------- From owner-sqr-users@USA.NET Mon Jan 15 16:42:40 1996 Date: Mon, 15 Jan 1996 14:01:47 -0400 From: Edward Forman Subject: Re: Can Easy SQR access flat files? fran, I have tons of regular SQR program which read flat files to get student IDs for processing. If this will help please contact me and I'll send you some samples. Ed Date: Fri, 12 Jan 1996 12:26:34 -0400 From: GARRIDF@SNYFARVA.CC.FARMINGDALE.EDU Subject: Can Easy SQR access flat files? Is there a way for ESQR (EASY SQR) to either READ a flat file or INPUT data to be used in selecting data from the Oracle tables? We wanted to match a file of ID's to the name, address and other information on the database, and create an extract file for word processing. ------------------------------------------------------------------------------- - | F R A N C E S G A R R I D O | |Associate Director for Administrative Computing Phone: (516) 420-2757 | |SUNY College of Technology at Farmingdale Fax: (516) 420-2696 | |Computer Center, Greenley Hall DECNET: SFARVA::GARRIDF| |2350 Route 110 BITNET: GARRIDF@SNYFARVA| |Farmingdale, New York 11735-1021 INTERNET:garridf@snyfarva.cc.farmingdale.edu| ------------------------------------------------------------------------------- - From owner-sqr-users@USA.NET Tue Jan 16 11:22:08 1996 Date: Tue, 16 Jan 1996 18:01:46 GMT From: Avi Algazy Subject: SQR With Sybase 10 I'm trying to compile some SQR scripts using SQR 2.4 for Sybase, on SCO. The server is Sybase 10 running on Aviion. Some scripts are compiled OK. For the others - I get error message similar to this: Sybase DBCANCEL error in cursor 9: (20296) Timed out waiting for server to acknowledge attention. Cursor 9 is: Cursor #9: SQL = select getdate() Compiles = 1 Executes = 0 Rows = 0 When the server is Sybase 4.9 (Also on Aviion) all the scripts are compiled OK When the client is Aviion (instead of SCO) running SQR 2.5, and the server is the same Sybase 10 - it is also OK Does anyone have any experience using SQR on SCO with Sybase10 as a server? Does SQR 2.x on SCO CAN work with Sybase 10? What about SQR3? Any help would be greatly appreciated - Avi Algazy From owner-sqr-users@USA.NET Tue Jan 16 16:37:02 1996 Date: Tue, 16 Jan 1996 11:52:41 PST From: johnk@SUN4.MITI.COM Subject: Re: SQR With Sybase 10 I would suggest getting SQR version 3.0.8 for SCO Unix/Sybase. I would doubt that any testing was ever done with V2.4 against a Sybase 10 database. I remember a very old SQR bug with select statements that did not have a from table clause, for example: select getdate(). I can't remember if the error you are reporting was the same. I would expect it to fail on both 4.9 and 10 databases, so it does not appear to be the problem. John L. Kellogg MITI Technical Support ______________________________ Reply Separator _________________________________ Subject: SQR With Sybase 10 Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 16/01/96 9:22 AM I'm trying to compile some SQR scripts using SQR 2.4 for Sybase, on SCO. The server is Sybase 10 running on Aviion. Some scripts are compiled OK. For the others - I get error message similar to this: Sybase DBCANCEL error in cursor 9: (20296) Timed out waiting for server to acknowledge attention. Cursor 9 is: Cursor #9: SQL = select getdate() Compiles = 1 Executes = 0 Rows = 0 When the server is Sybase 4.9 (Also on Aviion) all the scripts are compiled OK When the client is Aviion (instead of SCO) running SQR 2.5, and the server is the same Sybase 10 - it is also OK Does anyone have any experience using SQR on SCO with Sybase10 as a server? Does SQR 2.x on SCO CAN work with Sybase 10? What about SQR3? Any help would be greatly appreciated - Avi Algazy From owner-sqr-users@USA.NET Wed Feb 7 01:51:01 1996 Date: Wed, 7 Feb 1996 13:47:07 +0800 From: Raymond Yip Subject: SQR on HP What is the latest version of SQR on HP-UX? Raymond Yip MTRC From owner-sqr-users@USA.NET Thu Feb 8 11:17:32 1996 Date: Thu, 8 Feb 1996 07:14:51 PST From: marcosr@SUN4.MITI.COM Subject: Re: SQR on HP SQR Workbench v3.0 for HP-UX SQR Version HP-UX Version Database 3.0.12.1 10 Sybase 4.9.2,10.0.2/Oracle 6.0.37,7.0.16 3.0.5 9.0.5 Sybase 4.9.2,10.0.2/Oracle 6.0.37,7.0.16 3.0.7.3 9.0.5 Informix 6.00.UE1,7.10UC1 3.0.7 9.0.6 Ingres 6.4 MITI Technical Support ______________________________ Reply Separator _________________________________ Subject: SQR on HP Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 2/7/96 5:37 PM What is the latest version of SQR on HP-UX? Raymond Yip MTRC From owner-sqr-users@USA.NET Thu Feb 8 12:26:25 1996 Date: Thu, 8 Feb 1996 08:09:52 PST From: diannej@SUN4.MITI.COM Subject: Re: SQR on HP Oracle/HP-UX = 3.0.12.1 Sybase/HP-UX = 3.0.12.1 Informix/HP-UX = 3.0.7.3 Ingres/HP-UX = 3.0.7 ______________________________ Reply Separator _________________________________ Subject: SQR on HP Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 2/7/96 5:37 PM What is the latest version of SQR on HP-UX? Raymond Yip MTRC From owner-sqr-users@USA.NET Thu Feb 8 13:28:04 1996 Date: Thu, 8 Feb 1996 08:54:52 PST From: johnk@SUN4.MITI.COM Subject: Re[2]: SQR on HP There is also a 3.0.12.2 version available for the Oracle database that supports HP-UX version 9. John L. Kellogg MITI Technical Support Manager ______________________________ Reply Separator _________________________________ Subject: Re: SQR on HP Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 08/02/96 8:34 AM SQR Workbench v3.0 for HP-UX SQR Version HP-UX Version Database 3.0.12.1 10 Sybase 4.9.2,10.0.2/Oracle 6.0.37,7.0.16 3.0.5 9.0.5 Sybase 4.9.2,10.0.2/Oracle 6.0.37,7.0.16 3.0.7.3 9.0.5 Informix 6.00.UE1,7.10UC1 3.0.7 9.0.6 Ingres 6.4 MITI Technical Support ______________________________ Reply Separator _________________________________ Subject: SQR on HP Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 2/7/96 5:37 PM What is the latest version of SQR on HP-UX? Raymond Yip MTRC From owner-sqr-users@USA.NET Thu Feb 8 13:33:47 1996 Date: Thu, 8 Feb 1996 08:29:51 PST From: jefff@SUN4.MITI.COM Subject: Re: SQR on HP For HP/UX 9.x.x = 3.0.5 For HP/UX 10.x.x = 3.0.12.2 Regards, Jeff Fishman jefff@miti.com ______________________________ Reply Separator _________________________________ Subject: SQR on HP Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 2/7/96 5:37 PM What is the latest version of SQR on HP-UX? Raymond Yip MTRC From owner-sqr-users@USA.NET Thu Feb 8 14:35:30 1996 Date: Thu, 8 Feb 1996 10:55:55 -0800 From: Mark Shields Subject: Re: SQR on HP Diane, We are currently running SQR on HP-UX 9.X connecting to Oracle ? We would like to be able to run SQR on the same machine and connect to another HP running HP-UX 10.0 and sybase. What are our options ? Thanks, Mark Shields (mshields@qualcomm.com) > Oracle/HP-UX = 3.0.12.1 > Sybase/HP-UX = 3.0.12.1 > Informix/HP-UX = 3.0.7.3 > Ingres/HP-UX = 3.0.7 > > >______________________________ Reply Separator >_________________________________ >Subject: SQR on HP >Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet >Date: 2/7/96 5:37 PM > > >What is the latest version of SQR on HP-UX? > >Raymond Yip >MTRC From owner-sqr-users@USA.NET Thu Feb 8 14:47:10 1996 Date: Thu, 8 Feb 1996 10:58:35 -0800 From: Mark Shields Subject: Re: SQR on HP Jeff, I sent this mail to Diane@MITI but I recognized your name so I'm sending it to you too. Thanks. We are currently running SQR on HP-UX 9.X connecting to Oracle ? We would like to be able to run SQR on the same machine and connect to another HP running HP-UX 10.0 and sybase. What are our options ? Thanks, Mark S (mshields@qualcomm.com) > For HP/UX 9.x.x = 3.0.5 > For HP/UX 10.x.x = 3.0.12.2 > > > Regards, > > Jeff Fishman > jefff@miti.com > > >______________________________ Reply Separator >_________________________________ >Subject: SQR on HP >Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet >Date: 2/7/96 5:37 PM > > >What is the latest version of SQR on HP-UX? > >Raymond Yip >MTRC From owner-sqr-users@USA.NET Wed Feb 14 19:48:05 1996 Date: Wed, 14 Feb 1996 16:00:58 -0800 From: Benjamin Le Subject: FORM FEED Hi there: I'm writing a SQR progam to produce graduation listing and labels. The program will print listing or labels base upon option parameter. If listing selected, I want to print the listing with new-page at 55 lines. If labels selected, labels will continue printing without form feed. Since 'no-formfeed' command is only placed in BEGIN-SETUP that cause no form feed, how do I control the form feed in the program? Any help is really appreciated. Thanks. ___________________________________________________________________________ Benjamin Le Voice: (503)-977-4970 Portland Community College, Information Technology Fax : (503)-977-4987 P.O. Box 19000, Portland, Oregon 97280-0990 Internet: ble@pcc.edu ___________________________________________________________________________ From owner-sqr-users@USA.NET Thu Feb 15 08:47:54 1996 Date: Thu, 15 Feb 1996 08:14:15 -0500 From: Richard Vernick Subject: FORM FEED -Reply You can use no-formfeed all of the time. In your header section, print (or not) a chr(12) as the first character for formfeeds. Checkout 'encode' in the manual. Hope this helps.. >>> Benjamin Le 02/14/96 07:00pm >>> Hi there: I'm writing a SQR progam to produce graduation listing and labels. The program will print listing or labels base upon option parameter. If listing selected, I want to print the listing with new-page at 55 lines. If labels selected, labels will continue printing without form feed. Since 'no-formfeed' command is only placed in BEGIN-SETUP that cause no form feed, how do I control the form feed in the program? Any help is really appreciated. Thanks. ___________________________________________________________________________ Benjamin Le Voice: (503)-977-4970 Portland Community College, Information Technology Fax : (503)-977-4987 P.O. Box 19000, Portland, Oregon 97280-0990 Internet: ble@pcc.edu ___________________________________________________________________________ From owner-sqr-users@USA.NET Thu Feb 15 09:02:50 1996 Date: Thu, 15 Feb 1996 08:27:59 -0400 From: Edward Forman Subject: Re: FORM FEED Use an ASK statement to set the number of lines per page and use the #IFDEF DEBUG - #ENDIF syntax to incluse/exclude the no-formfeed statement. Here's a sample form an SQR that sets the lines/page at run time: ask numlines 'How many lines-per-page would you like? (66 or 80)?' page-size {numlines} 136 Ed Forman Associate Director, Computer Services SUNY - College at Oneonta FORMANEL@ONEONTA.EDU (607)436-2706 --------------------------------------------------------------------------- Original Message: Date: Wed, 14 Feb 1996 16:00:58 -0800 From: Benjamin Le Subject: FORM FEED Hi there: I'm writing a SQR progam to produce graduation listing and labels. The program will print listing or labels base upon option parameter. If listing selected, I want to print the listing with new-page at 55 lines. If labels selected, labels will continue printing without form feed. Since 'no-formfeed' command is only placed in BEGIN-SETUP that cause no form feed, how do I control the form feed in the program? Any help is really appreciated. Thanks. ___________________________________________________________________________ Benjamin Le Voice: (503)-977-4970 Portland Community College, Information Technology Fax : (503)-977-4987 P.O. Box 19000, Portland, Oregon 97280-0990 Internet: ble@pcc.edu ___________________________________________________________________________ From owner-sqr-users@USA.NET Thu Feb 15 09:03:38 1996 Date: Thu, 15 Feb 1996 08:39:31 -0400 From: Edward Forman Subject: Re: FORM FEED -Reply But won't you then lose SQR's nice ability to automatically create pages of the correct length for you? Ed Forman Associate Director, Computer Services SUNY - College at Oneonta FORMANEL@ONEONTA.EDU (607)436-2706 --------------------------------------------------------------------------- Original Message: Date: Thu, 15 Feb 1996 08:14:15 -0500 From: Richard Vernick Subject: FORM FEED -Reply You can use no-formfeed all of the time. In your header section, print (or not) a chr(12) as the first character for formfeeds. Checkout 'encode' in the manual. Hope this helps.. >>> Benjamin Le 02/14/96 07:00pm >>> Hi there: I'm writing a SQR progam to produce graduation listing and labels. The program will print listing or labels base upon option parameter. If listing selected, I want to print the listing with new-page at 55 lines. If labels selected, labels will continue printing without form feed. Since 'no-formfeed' command is only placed in BEGIN-SETUP that cause no form feed, how do I control the form feed in the program? Any help is really appreciated. Thanks. ___________________________________________________________________________ Benjamin Le Voice: (503)-977-4970 Portland Community College, Information Technology Fax : (503)-977-4987 P.O. Box 19000, Portland, Oregon 97280-0990 Internet: ble@pcc.edu ___________________________________________________________________________ From owner-sqr-users@USA.NET Tue Feb 20 13:33:10 1996 Date: Tue, 20 Feb 1996 09:36:19 -0800 From: Benjamin Le Subject: Page Break Hi SQRers: I might confused you with my previous help message. This is more clear I think. I writing a program with combination of listing and labels. There is a parameter option for user to choose either producing listing or labels. The problem is page breaking. If listing, I want to have a page break at 55 lines. If labes then do not do page break. If I put 'no-formfeed' command at BEGIN-SETUP then listing will not do a page break. Without 'no-formfeed' command, labels will do page break after each label print. Here is an example: !------------------------------------------- BEGIN-SETUP page-size {numlines} 133 no-formfeed END-SETUP !------------------------------------------- BEGIN-PROCEDURE start-up input $option 'Enter LIST or LABEL ' evaluate $option when = 'LIST' #define numlines 55 do list_query break when = 'LABEL' #define numlines 6 do label_query break end-evaluate END-PROCEDURE !------------------------------------------- BEGIN-PROCEDURE list_query begin-select spriden_id &id ... from ... where ... print &id (+1,1) ... if (#current-line = 55) ! Do page break at 55 lines for listing. new-page ! Since 'no-formfeed' at BEGIN-SETUP, it won't end-if ! do form feed here ??? end-select END-PROCEDURE !------------------------------------------- BEGIN-PROCEDURE labe_query columns 1 35 69 103 begin-select spvadds_street_line1 &ad1 ... from ... where ... print &ad1 (+1,1) ! Do not do page break for labels. ... next-column end-select END-PROCEDURE Please show me how to control page break as above example. Thank you for your help. Ben. ___________________________________________________________________________ Benjamin Le Voice: (503)-977-4970 Portland Community College, Information Technology Fax : (503)-977-4987 P.O. Box 19000, Portland, Oregon 97280-0990 Internet: ble@pcc.edu ___________________________________________________________________________ From owner-sqr-users@USA.NET Wed Feb 21 16:28:04 1996 Date: Wed, 21 Feb 1996 11:59:35 PST From: johnk@SUN4.MITI.COM Subject: Re: Page Break With SQR version 3, you can declare multiple report formats in the same SQR program and then use the input from the user to decide which output format to produce. It would even be possible to produce both output formats during the same data selection if the program is coded correctly. For example: begin-setup declare-layout labels paper-size=(8.5,2) ! values are in inches formfeed=no end-declare declare-layout list paper-size=(8.5,11) ! values are in inches end-declare declare-report labels layout=labels end-declare declare-report list layout=list end-declare end-setup begin-program input $option 'Enter LIST or LABEL ' evaluate $option when = 'LIST' use-report list do list_query when = 'LABEL' use-report labels do label_query end-evaluate ... end-program John L. Kellogg MITI Technical Support Manager ______________________________ Reply Separator _________________________________ Subject: Page Break Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 21/02/96 10:07 AM Hi SQRers: I might confused you with my previous help message. This is more clear I think. I writing a program with combination of listing and labels. There is a parameter option for user to choose either producing listing or labels. The problem is page breaking. If listing, I want to have a page break at 55 lines. If labes then do not do page break. If I put 'no-formfeed' command at BEGIN-SETUP then listing will not do a page break. Without 'no-formfeed' command, labels will do page break after each label print. Here is an example: !------------------------------------------- BEGIN-SETUP page-size {numlines} 133 no-formfeed END-SETUP !------------------------------------------- BEGIN-PROCEDURE start-up input $option 'Enter LIST or LABEL ' evaluate $option when = 'LIST' #define numlines 55 do list_query break when = 'LABEL' #define numlines 6 do label_query break end-evaluate END-PROCEDURE !------------------------------------------- BEGIN-PROCEDURE list_query begin-select spriden_id &id ... from ... where ... print &id (+1,1) ... if (#current-line = 55) ! Do page break at 55 lines for listing. new-page ! Since 'no-formfeed' at BEGIN-SETUP, it won't end-if ! do form feed here ??? end-select END-PROCEDURE !------------------------------------------- BEGIN-PROCEDURE labe_query columns 1 35 69 103 begin-select spvadds_street_line1 &ad1 ... from ... where ... print &ad1 (+1,1) ! Do not do page break for labels. ... next-column end-select END-PROCEDURE Please show me how to control page break as above example. Thank you for your help. Ben. ___________________________________________________________________________ Benjamin Le Voice: (503)-977-4970 Portland Community College, Information Technology Fax : (503)-977-4987 P.O. Box 19000, Portland, Oregon 97280-0990 Internet: ble@pcc.edu ___________________________________________________________________________ From owner-sqr-users@USA.NET Fri Feb 23 19:54:46 1996 Date: Fri, 23 Feb 1996 15:54:05 -0800 From: Benjamin Le Subject: Sorting Hi there I don't know if this is the right place to get help about this situation. How do you sort the last word in a field which has first name, middle(some none middle) and last name separated by space in the begin-select query: begin-procedure main begin-select shbdipl_name &diploma_name print &diploma_name (+1,1) from saturn.shbdipl where ... ORDER BY ??? end-select end-procedure SHBDIPL_NAME is student full name field. What I want is sort that field by the last word(last name). I'm really strugling with this. Your help is appreciated. Exp: Benjamin Le John B. Adams Smith J. Johnson How do you sort by Le, Adams and Johnson? Thanks. ___________________________________________________________________________ Benjamin Le Voice: (503)-977-4970 Portland Community College, Information Technology Fax : (503)-977-4987 P.O. Box 19000, Portland, Oregon 97280-0990 Internet: ble@pcc.edu ___________________________________________________________________________ From owner-sqr-users@USA.NET Fri Feb 23 20:40:58 1996 Date: Fri, 23 Feb 1996 16:45:43 PST From: Andrew Zitelli Subject: Re: Sorting Benjamin Le wrote: > How do you sort the last word in a field which has first name, middle > (some none middle) and last name separated by space in the begin-select > query: > > begin-procedure main > begin-select > shbdipl_name &diploma_name > print &diploma_name (+1,1) > from saturn.shbdipl > where ... > ORDER BY ??? > end-select > end-procedure > > SHBDIPL_NAME is student full name field. What I want is sort that field by > the last word (last name). I'm really struggling with this. Your help is > appreciated. Example: > Benjamin Le > John B. Adams > Smith J. Johnson > How do you sort by Le, Adams and Johnson? In Oracle, the SQL syntax would be: select substr (name, (instr (name, ' ', -1) + 1)) last_name, name from x where name is not null order by last_name; I haven't tried to use anything like this in SQR. If you are unable to easily do this directly, SQR can easily create a database view based on this query, generate the desired listing, and then delete the view. The instr function will return the position of the last blank, or zero if there is none. This will fail for names like "Benjamin Le, Jr." where it will sort on the "Jr.". If you are using an underlying database, the appropriate structure for names is to break out first, middle and last names, as well as titles as separate fields. It is easier to concatenate them when needed than to automatically break them out into separate fields when needed. I hope this is useful. -- Andy Zitelli, Silicon Systems, Inc. From owner-sqr-users@USA.NET Mon Feb 26 09:08:23 1996 Date: Mon, 26 Feb 1996 08:14:52 -0400 From: "He who is satisfied....fails" Subject: Re: Sorting Ben, That is a difficult situation since last name may not be the last word in the field. Two suggestions for a full-proof method: 1. Join the NAME table which has last name as a separate field then sort OR 2. SUBSTR the name field into up to 4 fields (FIRST MIDDLE LAST EXT). I am assuming is the max. Then if your have only TWO not null field, the second one is the last name. If you have FOUR, the third field is the last name. If you have middle name as X., then the rest is easy. Therefore if you have THREE not null fields, check if middle exists. OK. Now you know which field is the last name. SQR "WRITE" this last name field to a file with other needed data (GPA....etc). SORT the file via VMS SORT or other operating system functions (CALL SYSTEM USING.....). The result will be a sorted listing of your table. READ the data from the file for further SQR processing. Good Luck Nick Moscaritolo Sr. Programmer/Analyst II Bentley College Waltham, MA 02154 From owner-sqr-users@USA.NET Mon Feb 26 09:50:33 1996 Date: Mon, 26 Feb 1996 08:58:26 EST From: Al Snodgrass Subject: Re: Sorting I think you would need to create an expression in the SQL that would attempt to separate out the last name. Even if you did not want to print it, once the expression exists you can sort on it. Locate the last space by and substring the column starting at the position after the space to the end of the column. Thanks, Al _________________________________ >From the desk of Al Snodgrass Internet: asnodgrass@banyan.com CompuServe: 74512,646 Voice: 508-836-2866 ------------- Original Text From: Benjamin Le , on 2/23/96 3:54 PM: To: "Multiple recipients of list SQR-USERS" Hi there I don't know if this is the right place to get help about this situation. How do you sort the last word in a field which has first name, middle(some none middle) and last name separated by space in the begin-select query: begin-procedure main begin-select shbdipl_name &diploma_name print &diploma_name (+1,1) from saturn.shbdipl where ... ORDER BY ??? end-select end-procedure SHBDIPL_NAME is student full name field. What I want is sort that field by the last word(last name). I'm really strugling with this. Your help is appreciated. Exp: Benjamin Le John B. Adams Smith J. Johnson How do you sort by Le, Adams and Johnson? Thanks. ___________________________________________________________________________ Benjamin Le Voice: (503)-977-4970 Portland Community College, Information Technology Fax : (503)-977-4987 P.O. Box 19000, Portland, Oregon 97280-0990 Internet: ble@pcc.edu ___________________________________________________________________________ From owner-sqr-users@USA.NET Thu Feb 29 11:27:49 1996 Date: Thu, 29 Feb 1996 05:35:59 -0500 From: John Palmieri Subject: Dynamic Query Variables I am trying to use Dynamic Query Variables in the following way: Begin-Procedure main let $from_table='company' begin-select co_nbr from [$from_table] end-select End-Procedure main I receive the following message: (SQR 5528) Sybase DBSQLEXEC error in cursor 1: (156) Incorrect syntax near the keyword 'where'. SQL: select co_nbr from where 1 = 2 Error on line 76: (SQR 3716) Error in SQL statement. Errors were found in the program file. SQR: Program Aborting. Why doesen't this work? The column co_nbr is a column in the company table. I tried alot of variations like including the table name in the column expression company.co_nbr, using alaises in the from clause. but it still does not work. I followed the book line by line and I still cannot understand what is wrong. Any help would by appreicated. John Palmieri From owner-sqr-users@USA.NET Thu Feb 29 12:13:30 1996 Date: Thu, 29 Feb 1996 08:57:45 -0700 From: "Sean M. Shaw" Subject: Re: Dynamic Query Variables >Begin-Procedure main >let $from_table='company' >begin-select >co_nbr >from [$from_table] from [existing_table:$from_table] >end-select >End-Procedure main This works on Oracle, I'm not sure about sybase. Insert any existing table name where I put "existing_table". ************************************ * Sean M. Shaw * * Oracle Data Automation Manager * * The University of New Mexico * * Health Sciences Center Library * * Albuquerque, NM 87131-5686 * * Work: 505.277.6109 * * Fax: 505.277.5350 * * EMail: sshaw@biblio.unm.edu * ************************************ From owner-sqr-users@USA.NET Thu Feb 29 12:43:03 1996 Date: Thu, 29 Feb 1996 10:47:34 CST From: Melissa Santamaria Subject: Re: Dynamic Query Variables When you use dynamic tables, you must give it a "known" table to parse the statement with, but when you run it, it will use your dynamic table string. For instance, you would do: begin-select co_nbr from [another_table_name_that_has_co_nbr_column : $from_table] end-select So you must include a static table name with the column you need to query from the dynamic table. See this in the SQR Workbench manual under "dynamic variables -- with table names" (in my version, on page 2.35. Have fun. Melissa SantaMaria > > I am trying to use Dynamic Query Variables in the following way: > > Begin-Procedure main > > let $from_table='company' > > begin-select > > co_nbr > > from [$from_table] > > end-select > > End-Procedure main > > I receive the following message: > > (SQR 5528) Sybase DBSQLEXEC error in cursor 1: > (156) Incorrect syntax near the keyword 'where'. > > SQL: select co_nbr from where 1 = 2 > > Error on line 76: > (SQR 3716) Error in SQL statement. > > Errors were found in the program file. > > SQR: Program Aborting. > > Why doesen't this work? > > The column co_nbr is a column in the company table. > I tried alot of variations like including the table name in the column > expression company.co_nbr, using alaises in the from clause. but it still does > not work. > I followed the book line by line and I still cannot understand what is wrong. > > Any help would by appreicated. > > John Palmieri > From owner-sqr-users@USA.NET Fri Mar 1 13:23:52 1996 Date: Fri, 1 Mar 1996 08:56:21 PST From: johnk@SUN4.MITI.COM Subject: Re: Dynamic Query Variables Pre-V3 SQR does require a static table name on the left side of a dynamic table specification as other respondents have mentioned. After V3, the code you have here should work. John L. Kellogg MITI Tehnical Support Manager ______________________________ Reply Separator _________________________________ Subject: Dynamic Query Variables Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 01/03/96 5:39 AM I am trying to use Dynamic Query Variables in the following way: Begin-Procedure main let $from_table='company' begin-select co_nbr from [$from_table] end-select End-Procedure main I receive the following message: (SQR 5528) Sybase DBSQLEXEC error in cursor 1: (156) Incorrect syntax near the keyword 'where'. SQL: select co_nbr from where 1 = 2 Error on line 76: (SQR 3716) Error in SQL statement. Errors were found in the program file. SQR: Program Aborting. Why doesen't this work? The column co_nbr is a column in the company table. I tried alot of variations like including the table name in the column expression company.co_nbr, using alaises in the from clause. but it still does not work. I followed the book line by line and I still cannot understand what is wrong. Any help would by appreicated. John Palmieri From owner-sqr-users@USA.NET Thu Mar 7 22:47:28 1996 Date: Thu, 7 Mar 1996 18:56:31 -0800 From: Mara L Wells Subject: SQR and OPS$ Accounts I am having a problem with an item I found in the FAQ section of the SQR Users web site. Q: Are ORACLE OPS$ accounts supported in a connect string for SQR? A: Yes. After login as an OPS$ user, the SQR command is: sqr report /@t:machine:database What follows are the results of several tests I ran to determine how SQR and the OPS$ accounts were set up on the system I am using. Null inputs (i.e. tapping enter as a response) are annotated as . -------------------------------------------------------------------- TEST 1: :/home> sqlplus / SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:45:29 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Connected to: Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production SQL> exit Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production :/home> ------------------------------------------------------------------------ TEST 2: :/home> sqlplus /@t:corpdev1:new7 SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:46:47 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. ERROR: ORA-01004: default username feature not supported; logon denied ORA-01988: remote os logon is not allowed Enter user-name: Connected to: Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production SQL> exit Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production :/home> ------------------------------------------------------------------------ TEST 3: :/home> sqlplus SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:47:59 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Enter user-name: 12345 Enter password: Connected to: Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production SQL> exit Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production :/home> ------------------------------------------------------------------------ TEST 4: :/home> sqr dropall.sqr / SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. (SQR 5528) ORACLE ORLON error -1004 in cursor 0: ORA-01004: default username feature not supported; logon denied (SQR 4701) Can't logon to the database. SQR: Program Aborting. :/home> ----------------------------------------------------------------------- TEST 5: :/home> sqr dropall.sqr /@t:corpdev1:new7 SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. (SQR 5528) ORACLE ORLON error -1004 in cursor 0: ORA-01004: default username feature not supported; logon denied (SQR 4701) Can't logon to the database. SQR: Program Aborting. :/home> ------------------------------------------------------------------------ TEST 6: :/home> sqr dropall.sqr Enter Username: Enter Password: SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. (SQR 5528) ORACLE ORLON error -1004 in cursor 0: ORA-01004: default username feature not supported; logon denied (SQR 4701) Can't logon to the database. SQR: Program Aborting. :/home> ----------------------------------------------------------------------- TEST 7: :/home> sqr dropall.sqr Enter Username: 12345 Enter Password: SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. SQR: End of Run. :/home> --------------------------- After trying these combinations of the suggested command line, I was still at a loss as to what the system was expecting. My question to the learned peoples who read this list is this. What can be done to get the OPS$ accounts work with SQR? Is it the SQR setup? Is it the Oracle database setup? Is it the UNIX setup? Or possibly a combination of all 3? Mara L. Wells Senior Consultant REALOGIC, Inc. From owner-sqr-users@USA.NET Fri Mar 8 09:58:42 1996 Date: Fri, 8 Mar 1996 08:57:50 EST From: "Gayle L. Masters" Subject: Re: SQR and OPS$ Accounts The remote login feature error message is a function of the ORACLE setup. There is a parameter that must be turned on in the init.ora file. The parameter is REMOTE_OS_AUTHENT=TRUE This will allow remote login using the /. ______________________________ Reply Separator _________________________________ Subject: SQR and OPS$ Accounts Author: SQR-USERS@USA.NET at MRA Date: 3/7/96 10:43 PM I am having a problem with an item I found in the FAQ section of the SQR Users web site. Q: Are ORACLE OPS$ accounts supported in a connect string for SQR? A: Yes. After login as an OPS$ user, the SQR command is: sqr report /@t:machine:database What follows are the results of several tests I ran to determine how SQR and the OPS$ accounts were set up on the system I am using. Null inputs (i.e. tapping enter as a response) are annotated as . -------------------------------------------------------------------- TEST 1: :/home> sqlplus / SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:45:29 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Connected to: Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production SQL> exit Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production :/home> ------------------------------------------------------------------------ TEST 2: :/home> sqlplus /@t:corpdev1:new7 SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:46:47 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. ERROR: ORA-01004: default username feature not supported; logon denied ORA-01988: remote os logon is not allowed Enter user-name: Connected to: Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production SQL> exit Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production :/home> ------------------------------------------------------------------------ TEST 3: :/home> sqlplus SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:47:59 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Enter user-name: 12345 Enter password: Connected to: Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production SQL> exit Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production :/home> ------------------------------------------------------------------------ TEST 4: :/home> sqr dropall.sqr / SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. (SQR 5528) ORACLE ORLON error -1004 in cursor 0: ORA-01004: default username feature not supported; logon denied (SQR 4701) Can't logon to the database. SQR: Program Aborting. :/home> ----------------------------------------------------------------------- TEST 5: :/home> sqr dropall.sqr /@t:corpdev1:new7 SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. (SQR 5528) ORACLE ORLON error -1004 in cursor 0: ORA-01004: default username feature not supported; logon denied (SQR 4701) Can't logon to the database. SQR: Program Aborting. :/home> ------------------------------------------------------------------------ TEST 6: :/home> sqr dropall.sqr Enter Username: Enter Password: SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. (SQR 5528) ORACLE ORLON error -1004 in cursor 0: ORA-01004: default username feature not supported; logon denied (SQR 4701) Can't logon to the database. SQR: Program Aborting. :/home> ----------------------------------------------------------------------- TEST 7: :/home> sqr dropall.sqr Enter Username: 12345 Enter Password: SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. SQR: End of Run. :/home> --------------------------- After trying these combinations of the suggested command line, I was still at a loss as to what the system was expecting. My question to the learned peoples who read this list is this. What can be done to get the OPS$ accounts work with SQR? Is it the SQR setup? Is it the Oracle database setup? Is it the UNIX setup? Or possibly a combination of all 3? Mara L. Wells Senior Consultant REALOGIC, Inc. The following is an attached File item from cc:Mail. It contains information that had to be encoded to ensure successful transmission through various mail systems. To decode the file use the UUDECODE program. --------------------------------- Cut Here --------------------------------- begin 644 rfc822.txt M4F5C96EV960Z(&)Y(&-C;6%I;"!F"YN971C;VTN8V]M("AI M>#$P+FEX+FYE=&-O;2YC;VT@6S$Y.2XQ.#(N,3(P+C$P72D@8GD-"B`@("`@ M("`@("!M86EL+G5S82YN970@*#@N-BXQ,"\X+C8N,3`I('=I=&@@15--5%`@ M:60@5$%!,3$S,C<@9F]R#0H@("`@("`@("`@/%-14BU54T524T!54T$N;F5T M/CL@5&AU+"`W($UA"YN971C;VTN8V]M("@X+C8N,3,O4TU)+30N,2]. M971C;VTI(&ED(%-!03$Q-3@Y.R!4:'4L#0H@("`@("`@("`@-R!-87(@,3DY M-B`Q.#HU-CHS,2`M,#@P,`T*365S"YN971C;VTN8V]M/@T*1&%T93H@("`@("`@("!4 M:'4L(#<@36%R(#$Y.38@,3@Z-38Z,S$@+3`X,#`-"E)E<&QY+51O.B!345(M M55-%4E-`55-!+DY%5`T*4V5N9&5R.B`B1&ES8W5S. -------------------------------------------------------------------- TEST 1: :/home> sqlplus / SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:45:29 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Connected to: Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production SQL> exit Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production :/home> ------------------------------------------------------------------------ TEST 2: :/home> sqlplus /@t:corpdev1:new7 SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:46:47 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. ERROR: ORA-01004: default username feature not supported; logon denied ORA-01988: remote os logon is not allowed Enter user-name: Connected to: Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production SQL> exit Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production :/home> ------------------------------------------------------------------------ TEST 3: :/home> sqlplus SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:47:59 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Enter user-name: 12345 Enter password: Connected to: Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production SQL> exit Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.1.4.3.0 - Production :/home> ------------------------------------------------------------------------ TEST 4: :/home> sqr dropall.sqr / SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. (SQR 5528) ORACLE ORLON error -1004 in cursor 0: ORA-01004: default username feature not supported; logon denied (SQR 4701) Can't logon to the database. SQR: Program Aborting. :/home> ----------------------------------------------------------------------- TEST 5: :/home> sqr dropall.sqr /@t:corpdev1:new7 SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. (SQR 5528) ORACLE ORLON error -1004 in cursor 0: ORA-01004: default username feature not supported; logon denied (SQR 4701) Can't logon to the database. SQR: Program Aborting. :/home> ------------------------------------------------------------------------ TEST 6: :/home> sqr dropall.sqr Enter Username: Enter Password: SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. (SQR 5528) ORACLE ORLON error -1004 in cursor 0: ORA-01004: default username feature not supported; logon denied (SQR 4701) Can't logon to the database. SQR: Program Aborting. :/home> ----------------------------------------------------------------------- TEST 7: :/home> sqr dropall.sqr Enter Username: 12345 Enter Password: SQR: Structured Query Report Writer V3.0.7 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. SQR: End of Run. :/home> --------------------------- After trying these combinations of the suggested command line, I was still at a loss as to what the system was expecting. My question to the learned peoples who read this list is this. What can be done to get the OPS$ accounts work with SQR? Is it the SQR setup? Is it the Oracle database setup? Is it the UNIX setup? Or possibly a combination of all 3? Mara L. Wells Senior Consultant REALOGIC, Inc. From owner-sqr-users@USA.NET Fri Mar 8 13:39:09 1996 Date: Fri, 8 Mar 1996 11:23:40 -0600 From: johng@RMF41.USACE.ARMY.MIL Subject: Reply to SQR and OPS$ Accounts > I am having a problem with an item I found in the FAQ section of the > SQR Users web site. > > Q: Are ORACLE OPS$ accounts supported in a connect string for SQR? > A: Yes. After login as an OPS$ user, the SQR command is: > sqr report /@t:machine:database > > What follows are the results of several tests I ran to determine how > SQR and the OPS$ accounts were set up on the system I am using. Null > inputs (i.e. tapping enter as a response) are annotated as input>. > > -------------------------------------------------------------------- > > TEST 1: > > :/home> sqlplus / > > SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:45:29 1996 > > Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. > > Connected to: > Oracle7 Server Release 7.1.4.3.0 - Production Release > With the distributed and parallel query options > PL/SQL Release 2.1.4.3.0 - Production > *** above, you connected to a LOCAL data base as ops$, as expected > SQL> exit > Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release > With the distributed and parallel query options > PL/SQL Release 2.1.4.3.0 - Production > :/home> > > ------------------------------------------------------------------------ > > TEST 2: > > :/home> sqlplus /@t:corpdev1:new7 > > SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:46:47 1996 > > Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. > > ERROR: ORA-01004: default username feature not supported; logon denied > ORA-01988: remote os logon is not allowed > *** above, you failed to connect to a REMOTE data base as ops$, I believe due to the fact that REMOTE_OS_AUTHENT (sp?) was not enabled in that data base's init.ora file > Enter user-name: > > Connected to: > Oracle7 Server Release 7.1.4.3.0 - Production Release > With the distributed and parallel query options > PL/SQL Release 2.1.4.3.0 - Production > *** above was connection to LOCAL data base again, same as in test 1 above, since you provided no '@t:corpdev1:new7' sqlnet connect string, just null input > SQL> exit > Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release > With the distributed and parallel query options > PL/SQL Release 2.1.4.3.0 - Production > :/home> > > ------------------------------------------------------------------------ > > TEST 3: > > :/home> sqlplus > > SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar 7 14:47:59 1996 > > Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. > > Enter user-name: 12345 > Enter password: > > Connected to: > Oracle7 Server Release 7.1.4.3.0 - Production Release > With the distributed and parallel query options > PL/SQL Release 2.1.4.3.0 - Production > *** above, you connected to a LOCAL data base as 12345 user, as expected > SQL> exit > Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release > With the distributed and parallel query options > PL/SQL Release 2.1.4.3.0 - Production > :/home> > > ------------------------------------------------------------------------ > > TEST 4: > > :/home> sqr dropall.sqr / > SQR: Structured Query Report Writer V3.0.7 > Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. > > (SQR 5528) ORACLE ORLON error -1004 in cursor 0: > ORA-01004: default username feature not supported; logon denied > (SQR 4701) Can't logon to the database. > > SQR: Program Aborting. > :/home> > *** Above should have been a connection to a LOCAL data base as ops$ user. What you see though is a problem we experienced when upgrading to Oracle7 v7.1.4 from Oracle v6.0.33, and using SQR v2.5.7 as was delivered on the SQR install tape. For whatever reason, the sqr executable delivered required that REMOTE_OS_AUTHENT be enabled in the data base init.ora file, even for LOCAL ops$ connects. We finallly solved this by relinking the sqr executable which picked up new libraries from the new Oracle version we had installed (our and Oracle's opinion), after which we were able to do local ops$ sqr sessions without REMOTE_OS_AUTHENT being enabled on out local data base, which could have been a real security problem!!! > ----------------------------------------------------------------------- > > TEST 5: > > :/home> sqr dropall.sqr /@t:corpdev1:new7 > SQR: Structured Query Report Writer V3.0.7 > Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. > > (SQR 5528) ORACLE ORLON error -1004 in cursor 0: > ORA-01004: default username feature not supported; logon denied > (SQR 4701) Can't logon to the database. > *** Above is the same failure to log on to a REMOTE data base that was exhibited in the first part of test 2 above, where REMOTE_OS_AUTHENT is not enabled in the remote data base. > SQR: Program Aborting. > :/home> > > ------------------------------------------------------------------------ > > TEST 6: > > :/home> sqr dropall.sqr > Enter Username: > Enter Password: > > SQR: Structured Query Report Writer V3.0.7 > Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. > > (SQR 5528) ORACLE ORLON error -1004 in cursor 0: > ORA-01004: default username feature not supported; logon denied > (SQR 4701) Can't logon to the database. > *** above is same problem as in test 4, the bug in SQR requiring that REMOTE_OS_AUTHENT be enabled even for local ops$ logins. > SQR: Program Aborting. > :/home> > > ----------------------------------------------------------------------- > > TEST 7: > > :/home> sqr dropall.sqr > Enter Username: 12345 > Enter Password: > > SQR: Structured Query Report Writer V3.0.7 > Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. > *** above was not an ops$ login to a LOCAL data base, so every thing is cool... no OS authentication involved.. > SQR: End of Run. > :/home> > > --------------------------- > > After trying these combinations of the suggested command line, I was > still at a loss as to what the system was expecting. > > My question to the learned peoples who read this list is this. What > can be done to get the OPS$ accounts work with SQR? Is it the SQR > setup? Is it the Oracle database setup? Is it the UNIX setup? Or > possibly a combination of all 3? > > Mara L. Wells > Senior Consultant > REALOGIC, Inc. My suggestion, Mara, is to follow the instructions that came with your SQR install kit, and relink the sqr executable, making sure that your environment points to the correct Oracle home for the Oracle version you are targeting. This should correct SQR logins in tests 4 and 6. If you really want to use ops$ logins to remote data bases (t:corpdev1:new7) then on those data bases you would need to set REMOTE_OS_AUTHENT on in the init.ora file. (This was over a year ago, and I don't have my notes handy, so the name may be slightly different.) Be aware of the security hole this opens, as a user anywhere on the network (internet???) running sql net (asssuming they know your host name and data base name) can connect to your data base if there is an ops$ account set up for that same user name. Good luck, let me know if I can answer any questions. ------------------------------------------------------------------------ John Griffin (205) 890-2401 | A TTTTTTT SSSSS Advanced Technology Systems (ATS), Inc. | A A T S 4801 University Square, Suite 2 | AaaaA T SSSSS Huntsville, AL 35816-3431 | A A T S johng@rmf41.usace.army.mil | A A T SSSSS ------------------------------------------------------------------------ From owner-sqr-users@USA.NET Mon Mar 11 14:23:19 1996 Date: Mon, 11 Mar 1996 13:22:08 EST From: Benjamin GUINEBERTIERE <100604.544@COMPUSERVE.COM> Subject: time to delevelop a report How long is it to develop a report with SQR using SQLWindows? Is it between 2 and 5 days or between 2 and 5 hours ? What are the main differences between SQR and other tools such as - ReportWindows - Access - Crystal Report - Word - Excel ... Thank's in advance ****************************************************************** * Benjamin GUINEBERTIERE * * Paris, France e-mail: 100604.544@compuserve.com * ****************************************************************** From owner-sqr-users@USA.NET Mon Mar 11 18:37:14 1996 Date: Mon, 11 Mar 1996 17:51:54 -0500 From: Ray Ontko Subject: Re: time to delevelop a report > How long is it to develop a report with SQR using SQLWindows? > Is it between 2 and 5 days or between 2 and 5 hours ? Benjamin, If you know SQR, and you know the database and data that you're using, and you know the requirements/layout of the report to be developed, 2-5 hours is about right. Add extra for serious testing or documentation. As a consultant, I frequently run into situations where the client doesn't really have a good understanding of the underlying data or the real needs of the end users of the report. Under these circumstances, I use 2-5 days in my estimates. Ray ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | "Ask me about SQR and the WEB." rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ From owner-sqr-users@USA.NET Tue Mar 12 09:38:00 1996 Date: Tue, 12 Mar 1996 08:27:00 -0500 From: John Douglas Subject: Re: time to delevelop a report Benjamin, Yesterday, I developed an SQR that reads three fields from an input file, moves values to five $variables and performs a SQL insert into one PeopleSoft table. I had it working (and de-bugged) in about five hours. I began with an existing SQR, the SQR user guide and no experience in creating an upload (SQU). I do have about six months experience creating reports with SQR for windows. I believe an experienced person can create simple reports in two to five hours. I have no experience on the other query tools you mentioned. The only thing I've heard is SQR runs faster than most others in an ORACLE environment. I don't think SQR is for the end-user...like crystal for instance. I know of one company that uses Access and intends to continue same. Hope this helps you. John Douglas Lockheed Martin Utility Services From owner-sqr-users@USA.NET Tue Mar 12 15:27:22 1996 Date: Tue, 12 Mar 1996 11:16:16 PST From: Marcie Hague Subject: WRAP Command I am trying to print two multiline text fields side by side on a report. I am using the statements: print &id (+1,40) print &desc (0,50) wrap 30 5 ON=<13> STRIP=<10> print &res (0,85) wrap 30 5 ON=<13> STRIP=<10> The first multiline field prints fine, but the next multiline field prints on the row where the first field ends. I want them to appear in columns, both sarting on the same row. I experimented a bit with fixed positioning, but was unable to get this to work. Here is my goal: ID Description Resolution -- ----------- ---------- 54321 This is a test This is a test to to demonstrate demonstrate how I how I would like would like the the description resolution text to text to be be displayed. displayed. This is what I am getting with the above mentioned statements: ID Description Resolution -- ----------- ---------- 54321 This is a test to demonstrate how I would like the description text to be displayed. This is a test to demonstrate how I would like the resolution text to be displayed. Is it possible for this to work using the wrap command? I have resolved a problem like this before by using 'unstring' and printing each line individually, but I think there must be a better way. Thank you, Marcie Hague MicroSim Corporation Irvine, CA From owner-sqr-users@USA.NET Tue Mar 12 16:24:28 1996 Date: Tue, 12 Mar 1996 14:39:25 -0600 From: Johnny Ravela Subject: Reply to WRAP Command > I am trying to print two multiline text fields side by side on a report. I am > using the statements: > > print &id (+1,40) > print &desc (0,50) wrap 30 5 ON=<13> STRIP=<10> > print &res (0,85) wrap 30 5 ON=<13> STRIP=<10> > Here is the fix to your problem. And anybody who wants to see a simple sqr program. ! !!!!!!!!!!!!!!!!!!! This is an SQR program ! !!!!!!!!!!!!!!!!!!! begin-report do 001-setup-stuff end-report begin-procedure 001-setup-stuff let $id = 'JOHNNY1234' let $desc = 'The first multiline field prints fine, but the next one may not print well.' let $res = 'Looks like the second multiline field prints fine as well.' print $id (+1,7) print $desc (,20) wrap 15 6 ON = <13> STRIP = <10> keep-top print $res (,50) wrap 15 6 ON = <13> STRIP = <10> keep-top ! keep-top retains the current line position except if a page break ! occurs, in which case line 1 is used as the current line. new-page ! gotta flush............ end-procedure ! ! !!!!!!!!!!!!!!!!!!!!!!!!!!!!! Here is the listing file (.lis) ! !!!!!!!!!!!!!!!!!!!!!!!!!!!!! JOHNNY1234 The first Looks like the multiline field second prints fine, multiline field but the next prints fine as one may not well. print well. From owner-sqr-users@USA.NET Tue Mar 12 16:56:37 1996 Date: Tue, 12 Mar 1996 16:16:15 -0500 From: Ray Ontko Subject: Re: WRAP Command Try using the WRAP option KEEP-TOP. This will cause the current position to be on the line which began the wrapped portion. Ray > I am trying to print two multiline text fields side by side on a report. I am > using the statements: > > print &id (+1,40) > print &desc (0,50) wrap 30 5 ON=<13> STRIP=<10> > print &res (0,85) wrap 30 5 ON=<13> STRIP=<10> > > The first multiline field prints fine, but the next multiline field prints on > the row where the first field ends. I want them to appear in columns, both > sarting on the same row. I experimented a bit with fixed positioning, but > was unable to get this to work. > Here is my goal: > > > ID Description Resolution > -- ----------- ---------- > 54321 This is a test This is a test to > to demonstrate demonstrate how I > how I would like would like the > the description resolution text to > text to be be displayed. > displayed. > > This is what I am getting with the above mentioned statements: > > ID Description Resolution > -- ----------- ---------- > 54321 This is a test > to demonstrate > how I would like > the description > text to be > displayed. This is a test to > demonstrate how I > would like the > resolution text to > be displayed. > > Is it possible for this to work using the wrap command? I have resolved a > problem like this before by using 'unstring' and printing each line > individually, but I think there must be a better way. > > Thank you, > > Marcie Hague > MicroSim Corporation > Irvine, CA > ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | "Ask me about SQR and the WEB." rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ From owner-sqr-users@USA.NET Tue Mar 12 16:58:21 1996 Date: Tue, 12 Mar 1996 16:10:48 -0400 From: DCHOLAGH@BENTLEY.EDU Subject: WRAP In order to retain the current line position use KEEP-TO option on the PRINT statement: PRINT &id (+1,1) PRINT &desc (0,50) WRAP 30 5 KEEP-TOP PRINT &res (0,85) WRAP 30 5 David Cholaghian Bentley College Waltham, MA From owner-sqr-users@USA.NET Tue Mar 12 18:30:22 1996 Date: Tue, 12 Mar 1996 16:31:08 -0600 From: johng@RMF41.USACE.ARMY.MIL Subject: Reply to WRAP Command > I am trying to print two multiline text fields side by side on a report. I am > using the statements: > > print &id (+1,40) > print &desc (0,50) wrap 30 5 ON=<13> STRIP=<10> > print &res (0,85) wrap 30 5 ON=<13> STRIP=<10> > > The first multiline field prints fine, but the next multiline field prints on > the row where the first field ends. I want them to appear in columns, both > sarting on the same row. I experimented a bit with fixed positioning, but > was unable to get this to work. > Here is my goal: > > > ID Description Resolution > -- ----------- ---------- > 54321 This is a test This is a test to > to demonstrate demonstrate how I > how I would like would like the > the description resolution text to > text to be be displayed. > displayed. > > This is what I am getting with the above mentioned statements: > > ID Description Resolution > -- ----------- ---------- > 54321 This is a test > to demonstrate > how I would like > the description > text to be > displayed. This is a test to > demonstrate how I > would like the > resolution text to > be displayed. > > Is it possible for this to work using the wrap command? I have resolved a > problem like this before by using 'unstring' and printing each line > individually, but I think there must be a better way. > > Thank you, > > Marcie Hague > MicroSim Corporation > Irvine, CA Everyone was right about the keep-top option of the wrap command, but you also need to have a mechanism to allow the next id value to print below the longest wrapped set of text. The NEXT-LISTING command allows this, keeping track of the lowest line already printed on the page, and postioning the current line number and absolute line number also below that. Since absolute line number is reset, you can actully print the &desc and &res columns using absolute line numbers without using keep-top. The need= option of next-listing will allow you to reserve some room to process the next item on a page, without unnecessary splitting across pages. Something like this print &id (1,40) ! absolute line allowed by using next-listing print &desc (1,50) wrap 30 5 ON=<13> STRIP=<10> print &res (1,85) wrap 30 5 ON=<13> STRIP=<10> next-listing skiplines=1 need=5 ! skip a line between id's ! ensure min of 5 lines for next id ------------------------------------------------------------------------ John Griffin (205) 890-2401 | A TTTTTTT SSSSS Advanced Technology Systems (ATS), Inc. | A A T S 4801 University Square, Suite 2 | AaaaA T SSSSS Huntsville, AL 35816-3431 | A A T S johng@rmf41.usace.army.mil | A A T SSSSS ------------------------------------------------------------------------ From owner-sqr-users@USA.NET Wed Mar 13 11:16:04 1996 Date: Wed, 13 Mar 1996 07:20:53 PST From: Marcie Hague Subject: Re: Reply to WRAP Command Thank you all very much! I was getting a bit frustrated when the 'keep-top' option that everyone recommended wouldn't work. After I added 'NEXT_LISTING', however, it worked fine! Thanks again everyone! Thank you John!! Marcie ______________________________ Reply Separator _________________________________ Subject: Reply to WRAP Command Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet-Mail Date: 3/12/96 5:37 PM > I am trying to print two multiline text fields side by side on a report. I am > using the statements: > > print &id (+1,40) > print &desc (0,50) wrap 30 5 ON=<13> STRIP=<10> > print &res (0,85) wrap 30 5 ON=<13> STRIP=<10> > > The first multiline field prints fine, but the next multiline field prints on > the row where the first field ends. I want them to appear in columns, both > sarting on the same row. I experimented a bit with fixed positioning, but > was unable to get this to work. > Here is my goal: > > > ID Description Resolution > -- ----------- ---------- > 54321 This is a test This is a test to > to demonstrate demonstrate how I > how I would like would like the > the description resolution text to > text to be be displayed. > displayed. > > This is what I am getting with the above mentioned statements: > > ID Description Resolution > -- ----------- ---------- > 54321 This is a test > to demonstrate > how I would like > the description > text to be > displayed. This is a test to > demonstrate how I > would like the > resolution text to > be displayed. > > Is it possible for this to work using the wrap command? I have resolved a > problem like this before by using 'unstring' and printing each line > individually, but I think there must be a better way. > > Thank you, > > Marcie Hague > MicroSim Corporation > Irvine, CA Everyone was right about the keep-top option of the wrap command, but you also need to have a mechanism to allow the next id value to print below the longest wrapped set of text. The NEXT-LISTING command allows this, keeping track of the lowest line already printed on the page, and postioning the current line number and absolute line number also below that. Since absolute line number is reset, you can actully print the &desc and &res columns using absolute line numbers without using keep-top. The need= option of next-listing will allow you to reserve some room to process the next item on a page, without unnecessary splitting across pages. Something like this print &id (1,40) ! absolute line allowed by using next-listing print &desc (1,50) wrap 30 5 ON=<13> STRIP=<10> print &res (1,85) wrap 30 5 ON=<13> STRIP=<10> next-listing skiplines=1 need=5 ! skip a line between id's ! ensure min of 5 lines for next id ------------------------------------------------------------------------ John Griffin (205) 890-2401 | A TTTTTTT SSSSS Advanced Technology Systems (ATS), Inc. | A A T S 4801 University Square, Suite 2 | AaaaA T SSSSS Huntsville, AL 35816-3431 | A A T S johng@rmf41.usace.army.mil | A A T SSSSS ------------------------------------------------------------------------ From owner-sqr-users@USA.NET Wed Mar 13 22:10:20 1996 Date: Wed, 13 Mar 1996 21:16:47 -0500 From: "Mara L. Wells" Subject: SQR Workbench -- [ From: Mara L. Wells * EMC.Ver #2.5.02 ] -- The client I am currently working for is considering acquiring SQR Workbench for the SQR report developers. Sometimes GUI front ends are more cumbersome than those that use the command line as the interface. How does workbench development compare with UNIX vi development? All comments welcome. Mara Wells Senior Consultant REALOGIC, Inc. From owner-sqr-users@USA.NET Thu Mar 14 07:20:17 1996 Date: Thu, 14 Mar 1996 12:33:57 +0100 From: Thierry Delhaye Subject: SQR output formats Hello, I would like to produce reports in HTML format, including simple tabular = formats=20 but also pie charts and other graphical objects. The data will be extracted from an Oracle7 database. I plan to use SQR to generate an ASCII output of my non-graphical = reports, then=20 include them in a HTML page. My question is: as SQR3 can produce graphics, is there a way to generate = them =20 in gif format (or any other format supported by a web browser) that I = can=20 include later in my HTML page ? Thanks in advance, Thierry DELHAYE BIM Engineering Europe td@bim.be From owner-sqr-users@USA.NET Thu Mar 14 09:28:59 1996 Date: Thu, 14 Mar 1996 08:20:00 -0500 From: james rader Subject: Re: SQR Workbench Mara, At INTELSAT our developers routinely develop SQR programs on our Windows boxes and then FTP them to the UNIX boxes for execution. Beyond the fact that this gives us such benefits of client server development as: - Use of cheap boxes to develop - Isolation of developing code from production - Ability of developers to tailor their environments to fit their own personnal quirks, etc the Workbench environment provides a nifty windowing paradigm for our work. We can see source, results, and errors all in a unified world. (I know UNIX people are used to a lot of this but in the restrictive Microsoft Windows 3.1 world this is great.) The flip side for on-UNIX development is, how do you explain cluttering up the process table and burning all those resources to your system administrator? On a PC, we don't care. The bottom line is that our gang always have a choice, vi or on-PC with ReportMate and they've crossed over heavily to ReportMate. N.B. Even for top programmers, ReportMate is great for generating quick-starts on development jobs. If you want to persue this further I am: Jim Rader Internet Email: james.rader@intelsat.int ------------- Original Text >From MLWELLS@SMTPGATE (Mara L. Wells) {mlwells%IX.NETCOM.COM.@intelsat1.intelsat.int}, on 13/3/96 9:16 PM: To: SQR-USER@SMTPGATE (Multiple recipients of list SQR-USERS) {SQR-USERS@USA.NET} -- [ From: Mara L. Wells * EMC.Ver #2.5.02 ] -- The client I am currently working for is considering acquiring SQR Workbench for the SQR report developers. Sometimes GUI front ends are more cumbersome than those that use the command line as the interface. How does workbench development compare with UNIX vi development? All comments welcome. Mara Wells Senior Consultant REALOGIC, Inc. From owner-sqr-users@USA.NET Thu Mar 14 13:02:01 1996 Date: Thu, 14 Mar 1996 11:59:08 -0500 From: Nathan Treadway Subject: Re: SQR Workbench > The client I am currently working for is considering acquiring SQR Workbench > for the SQR report developers. Sometimes GUI front ends are more cumbersome > than those that use the command line as the interface. How does workbench > development compare with UNIX vi development? We have been very pleased with the latest version of SQR for Windows (i.e. SQR ReportMate v3.5). It has an integrated editor, SPF file viewer, and output window. You can edit, run, and view the output quickly and easily from within one Windows application. The integrated editor knows about SQR, so it can highlight keywords, comments, etc. You can pass a full command line to the program when you execute it, so you don't loose any of the flexibility of the command line versions. The older version for SQR for Windows (v3.0) did not have an integrated editor and SPF viewer; it was not a good development environment. Nathan ---------------------------------------------------------------------------- Nathan Treadway | Ray Ontko & Co. | info@ontko.com (auto-reply server) nathant@ontko.com | Richmond, IN | ftp.ontko.com, http://www.ontko.com/ From owner-sqr-users@USA.NET Mon Mar 18 14:20:44 1996 Date: Mon, 18 Mar 1996 13:47:25 -0500 From: Adam Hoffman Subject: Re: Upper/Lower If you are using ORACLE you can use the initcap function. At 01:05 PM 3/18/96 EST, you wrote: >I've noticed there is an upper and a lower function in SQR. Is there a what is >sometimes called a 'proper' function that will capitalize just the first >character. > >Thanks! > > Adam Hoffman Consultant Still-Life With Systems Inc. RPI Phone: (518) 276-3095 RPI Email: hoffma@rpi.edu From owner-sqr-users@usa.net Mon Mar 18 14:40:11 1996 Date: Mon, 18 Mar 1996 13:05:40 EST From: Dawn Dennis <76105.3147@COMPUSERVE.COM> Subject: Upper/Lower I've noticed there is an upper and a lower function in SQR. Is there a what is sometimes called a 'proper' function that will capitalize just the first character. Thanks! From owner-sqr-users@USA.NET Mon Mar 18 15:35:05 1996 Date: Mon, 18 Mar 1996 14:22:45 -0800 From: Bruce Tobin Subject: Restarts? Does SQR offer any kind of built-in restart capability? If not, has anyone written any code to implement such a capability? Thanks. From owner-sqr-users@USA.NET Mon Mar 18 16:42:57 1996 Date: Mon, 18 Mar 1996 11:42:43 PST From: johnk@SUN4.MITI.COM Subject: Re[2]: Upper/Lower This can be done in SQR with the following command: let $str = upper(substr($str,1,1)) || substr($str,2,length($str) -1) This appeared in Volume 5, Number 3 of the MITI Forum newsletter. ______________________________ Reply Separator _________________________________ Subject: Re: Upper/Lower Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 18/03/96 11:33 AM If you are using ORACLE you can use the initcap function. At 01:05 PM 3/18/96 EST, you wrote: >I've noticed there is an upper and a lower function in SQR. Is there a what is >sometimes called a 'proper' function that will capitalize just the first >character. > >Thanks! > > Adam Hoffman Consultant Still-Life With Systems Inc. RPI Phone: (518) 276-3095 RPI Email: hoffma@rpi.edu From owner-sqr-users@USA.NET Mon Mar 18 17:05:31 1996 Date: Mon, 18 Mar 1996 16:35:46 -0500 From: Nathan Treadway Subject: Re: Upper/Lower > >I've noticed there is an upper and a lower function in SQR. Is there a what is > >sometimes called a 'proper' function that will capitalize just the first > >character. > > If you are using ORACLE you can use the initcap function. > Yes, if you are using Oracle you can use Oracle's Initcap function in your begin-select section. Unfortunately, there is no such a function within SQR itself -- if you have a $variable, you can't just say "let $variable_initcap = initcap($variable)". A workaround is to select from dual -- the code fragment -------------------------------------------------- let $test_lower = 'hello there everyone' begin-select initcap($test_lower) &test_initcap from dual end-select display &test_initcap ---------------------------------------------------- will produce the output "Hello There Everyone". Hope this helps. Nathan ---------------------------------------------------------------------------- Nathan Treadway | Ray Ontko & Co. | info@ontko.com (auto-reply server) nathant@ontko.com | Richmond, IN | ftp.ontko.com, http://www.ontko.com/ From owner-sqr-users@USA.NET Wed Mar 20 21:03:12 1996 Date: Tue, 19 Mar 1996 23:34:57 +0000 From: Steve Subject: Re: Upper/Lower This method within SQR is better because, unless you can do the processing within a select statement in the first place, it's better to manipulate data within SQR than opening a database cursor just to carry out something like this. (It also shows the power of the SQR functions in string manipulations - even if it appears that you cannot do some of the things SQL can do, you can sort it out within SQR using the string functions - long-winded perhaps but it does the job very well in my experience.) Steve Nicholas -------------------------------------------------------------------- >This can be done in SQR with the following command: > > let $str = upper(substr($str,1,1)) || substr($str,2,length($str) -1) > >This appeared in Volume 5, Number 3 of the MITI Forum newsletter. > > >If you are using ORACLE you can use the initcap function. > >At 01:05 PM 3/18/96 EST, you wrote: >>I've noticed there is an upper and a lower function in SQR. Is there a what is >>sometimes called a 'proper' function that will capitalize just the first >>character. >> >>Thanks! >> >> > >Adam Hoffman >Consultant >Still-Life With Systems Inc. >RPI Phone: (518) 276-3095 >RPI Email: hoffma@rpi.edu From owner-sqr-users@USA.NET Wed Mar 20 22:29:43 1996 Date: Wed, 20 Mar 1996 21:41:50 -0500 From: Nathan Treadway Subject: Re: Upper/Lower > > This method within SQR is better because, unless you can do the > processing within a select statement in the first place, it's better to > manipulate data within SQR than opening a database cursor just to carry > out something like this. [...] > > >This can be done in SQR with the following command: > > > > let $str = upper(substr($str,1,1)) || substr($str,2,length($str) -1) One important point: this will uppercase the first character in the string, while using the Oracle "initcap" function uppercases the first character *of each word* in the string. Also, initcap will make all the other characters in the string lower case (e.g. "hELLO tHerE" becomes "Hello There"). This kind of processing isn't easy to do within SQR itself. Staying within SQR is definitly preferable, both in order to avoid opening a new cursor and so that the SQR program can be portable across databases. It would be nice if MITI added a "proper"/"initcap" function to the language for those situations when you want to capitalize all the words in a string (rather than just the first one), such as printing a $variable as a title of a report. Nathan ---------------------------------------------------------------------------- Nathan Treadway | Ray Ontko & Co. | info@ontko.com (auto-reply server) nathant@ontko.com | Richmond, IN | ftp.ontko.com, http://www.ontko.com/ From owner-sqr-users@USA.NET Fri Mar 22 00:18:23 1996 Date: Thu, 21 Mar 1996 23:20:33 -0500 From: "Matthew F. Reagan" Subject: Re: Upper/Lower >Staying within SQR is definitly preferable, both in order to avoid opening a >new cursor and so that the SQR program can be portable across databases. >It would be nice if MITI added a "proper"/"initcap" function to the >language for those situations when you want to capitalize all the words in >a string (rather than just the first one), such as printing a $variable as a >title of a report. Gee, Nathan, I smell an article. One of the really powerful capabilities of SQR is the ability to add user written functions. Write a function in C, add it to ufunc.c, compile it, relink the SQR executable, and voila, you have a new function that can be used in LET, WHILE, IF, etc. Just imagine: let $newstring = initcap($oldstring) This would be even better than Oracle's initcap function, in that SQR doesn't care if it is a LONG datatype or not. I haven't done this in years, and it was done in Vax C (not ANSI by a long shot) on a Vax under VMS 5.5, but the basic capability still exists on all platforms currently supported. Has anyone done something similar? Can we assemble enough people to do it here? The gauntlet has been thrown down... will someone accept the challenge? I will dredge my old 8" floppies for the notes, if someone will volunteer the C knowledge (of which I am sorely lacking). Matt... From owner-sqr-users@USA.NET Fri Mar 22 04:06:32 1996 Date: Fri, 22 Mar 1996 03:43:07 -0500 From: Wes Bailey Subject: Re: Upper/Lower I believe the Language Tutorial (not the Reference Manual) which accompanies SQR3.0 shows how to do what you have suggested with the initcap function as its example. Wes Bailey Consultant Ray Ontko & Co. ---------------------------------------------------------------------- Gee, Nathan, I smell an article. One of the really powerful capabilities of SQR is the ability to add user written functions. Write a function in C, add it to ufunc.c, compile it, relink the SQR executable, and voila, you have a new function that can be used in LET, WHILE, IF, etc. Just imagine: let $newstring = initcap($oldstring) This would be even better than Oracle's initcap function, in that SQR doesn't care if it is a LONG datatype or not. I haven't done this in years, and it was done in Vax C (not ANSI by a long shot) on a Vax under VMS 5.5, but the basic capability still exists on all platforms currently supported. Has anyone done something similar? Can we assemble enough people to do it here? The gauntlet has been thrown down... will someone accept the challenge? I will dredge my old 8" floppies for the notes, if someone will volunteer the C knowledge (of which I am sorely lacking). Matt... From owner-sqr-users@USA.NET Fri Mar 22 13:29:41 1996 Date: Fri, 22 Mar 1996 12:37:36 -0500 From: Nathan Treadway Subject: Re: Upper/Lower > Gee, Nathan, I smell an article. One of the really powerful > capabilities of SQR is the ability to add user written functions. > Write a function in C, add it to ufunc.c, compile it, relink the > SQR executable, and voila, you have a new function that can be used > in LET, WHILE, IF, etc. Just imagine: > > let $newstring = initcap($oldstring) > As Wes mentioned, the SQR Language Tutorial does give exactly this example in its section on ufuncs. However, relinking SQR is not always possible; it would be nice if fairly standard functions like initcap were avaible in the baseline language. Of course, having someone write a ufuncs to do various string processing would be a first step towards getting them included in the language. I can't relink SQR myself, but I am putting together a list of string functions I'd like to suggest for possible inclusion in SQR. Initcap is one, as is "unstring-into-an-array" for use when you don't know how many "pieces" you'll have after you use unstring. I'll plan to post my list to sqr-users when I get a chance to clean it up. If anyone has particular suggestions for string functions they've needed while programming in SQR, I'd be interested to hear. (Please send them directly to me since I'll be summarizing to the mailing list once I've collected the suggestions.) Nathan ---------------------------------------------------------------------------- Nathan Treadway | Ray Ontko & Co. | info@ontko.com (auto-reply server) nathant@ontko.com | Richmond, IN | ftp.ontko.com, http://www.ontko.com/ From owner-sqr-users@USA.NET Wed Mar 27 11:24:47 1996 Date: Wed, 27 Mar 1996 10:36:20 EST From: Scott Humphrey Subject: HPLASER type printing We've been using SQR V3.0.5 for some time now and the the ability to use laserjet and postscript printing is very useful. The major problem I am encountering now is that while my print jobs print fine on our HP Laserjet 4Si printers, the output is a mess on any laserjet lower than a 4Si. Page breaks occur at points where they shouldn't be and at time one line wil print per page. Does anyone know how to make these reports print on older laserjets since the vast majority of printers we have on site here are older models. I've tried all of the "-o raw" stuff but that has no effect. Thanks, Scott xsah1@sunyit.edu From owner-sqr-users@USA.NET Thu Mar 28 13:48:34 1996 Date: Thu, 28 Mar 1996 08:29:39 PST From: johnk@SUN4.MITI.COM Subject: Re: HPLASER type printing Scott, We have some cases in our database where output printed from a Unix host to an HPLaserjet printer splits over many pages. The -oraw option has worked in most situations. How much memory does your typical HPLasejet 3 printer have? I would recommend a minimum of 2MB, but 4MB would probably be better. If you would like to e-mail me a .lis file, I can try printing it here on my HPLJ3 off of a sun host. John L. Kellogg MITI Technical Support Manager send e-mail to: support@miti.com ______________________________ Reply Separator _________________________________ Subject: HPLASER type printing Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 27/03/96 10:35 AM We've been using SQR V3.0.5 for some time now and the the ability to use laserjet and postscript printing is very useful. The major problem I am encountering now is that while my print jobs print fine on our HP Laserjet 4Si printers, the output is a mess on any laserjet lower than a 4Si. Page breaks occur at points where they shouldn't be and at time one line wil print per page. Does anyone know how to make these reports print on older laserjets since the vast majority of printers we have on site here are older models. I've tried all of the "-o raw" stuff but that has no effect. Thanks, Scott xsah1@sunyit.edu From owner-sqr-users@USA.NET Sun Mar 31 21:15:23 1996 Date: Sun, 31 Mar 1996 17:16:52 PST From: Mark Johnson Subject: Re: SQR output formats Thierry, I don't believe there is a way to create GIF files from SQR, short of a screen capture. A solution that may work for you is to create SPF files and have the web users associate the SQR Viewer with their browser. An other solution would be to generate Postscript, and have it converted to Adobe's PDF format, so that it could be viewed by Acrobat. Mark >Hello, > >I would like to produce reports in HTML format, including simple tabular = >formats=20 >but also pie charts and other graphical objects. > >The data will be extracted from an Oracle7 database. > >I plan to use SQR to generate an ASCII output of my non-graphical = >reports, then=20 >include them in a HTML page. > >My question is: as SQR3 can produce graphics, is there a way to generate = >them =20 >in gif format (or any other format supported by a web browser) that I = >can=20 >include later in my HTML page ? > >Thanks in advance, > >Thierry DELHAYE >BIM Engineering Europe >td@bim.be > From owner-sqr-users@USA.NET Tue Apr 2 14:34:13 1996 Date: Tue, 2 Apr 1996 13:38:56 -0400 From: "He who is satisfied....fails" Subject: Problem with SQR procedure using MAX in select Question: What does this procedure return? After running this procedure, it returns NULL to the variable $max_seqno. I think it should return the character '0'. The WHERE brings back NO rows but the MAX function seems to put NULL in &col_max_seqno and in turn MOVES NULL to $max_seqno. Is this right ? BEGIN-PROCEDURE get_seqno move '0' to $max_seqno BEGIN-SELECT max(sarappd_seq_no) + 1 &col_max_seqno move &col_max_seqno to $max_seqno FROM saturn.sarappd WHERE sarappd_pidm = $pidm AND sarappd_term_code_entry = $term AND sarappd_appl_no = $appl_no END-SELECT END-PROCEDURE Please let me know ! NICK MOSCARITOLO Sr P/A II Bentley College From owner-sqr-users@USA.NET Tue Apr 2 14:52:13 1996 Date: Tue, 2 Apr 1996 12:06:29 -0700 From: "Sean M. Shaw" Subject: Re: Problem with SQR procedure using MAX in select >BEGIN-PROCEDURE get_seqno > move '0' to $max_seqno >BEGIN-SELECT >max(sarappd_seq_no) + 1 &col_max_seqno Oracle will always return a record when you use the min, max, avg, etc., functions, even if the where clause excludes all records. In this case, it will return a "null" value and a "null" + 1 is still null. One way to handle this is: nvl(max(sarappd_seq_no),0) + 1 &col_max_seqno Although a "sequence" would probably serve you better if it is just a "sequential" number that you are looking for. ************************************ * Sean M. Shaw * * Oracle Data Automation Manager * * The University of New Mexico * * Health Sciences Center Library * * Albuquerque, NM 87131-5686 * * Work: 505.277.6109 * * Fax: 505.277.5350 * * EMail: sshaw@biblio.unm.edu * ************************************ From owner-sqr-users@USA.NET Tue Apr 2 18:05:27 1996 Date: Tue, 2 Apr 1996 17:23:40 EST From: "Suresh P. Reddy" <74634.400@COMPUSERVE.COM> Subject: End of Communication Channel I was running a SQR report and encountered ORA-03113 END OF COMMUNICATION CHANNEL. Can somebody tell me how to solve this problem or what makes this happen. Thank you for your help. From owner-sqr-users@USA.NET Wed Apr 3 07:09:40 1996 Date: Wed, 3 Apr 1996 13:23:00 -0800 From: =D6rjan Nordlund Subject: Select Columns in Left Margin Does anyone know why you have to place column names at the left margin in SELECT paragraphs? The User's Guide explains that it is to differentiate column names and commands. Couldn't there be a more aesthetical solution? A structured program in SQR is because of this an oxymoron... Have I perhaps overlooked any "work around" of this matter? Orjan Nordlund National Council for Crime Prevention Sweden From owner-sqr-users@USA.NET Wed Apr 3 07:52:00 1996 Date: Wed, 3 Apr 1996 07:23:01 -0500 From: Ray Ontko Subject: Re: End of Communication Channel > I was running a SQR report and encountered ORA-03113 END OF COMMUNICATION > CHANNEL. Can somebody tell me how to solve this problem or what makes this > happen. Thank you for your help. I recall that this is either a problem with SQL*Net or, if you're running VMS, your BYTLM (or some other process parameter) is too small. One of the DBAs out there should be able to answer the question. Try asking on ORACLE-L or comp.databases.oracle. Ray ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | "Ask me about SQR and the WEB." rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ From owner-sqr-users@USA.NET Wed Apr 3 09:43:01 1996 Date: Wed, 3 Apr 1996 06:52:41 CST From: Murthy Patamalla Subject: Re: End of Communication Channel I recall that this error is usually due to a failure on the SQL*net connection. Make sure that the SQL*net connection is working right and in some cases you need to bring SQL*net down and up again. This may solve your problem. Another aspect to look at is the size of the Rollback segments and the operation that is being performed. Good luck!!! Murthy ______________________________ Reply Separator _________________________________ Subject: End of Communication Channel Author: SQR-USERS@usa.net at INTERNET-HUB Date: 4/2/96 4:30 PM I was running a SQR report and encountered ORA-03113 END OF COMMUNICATION CHANNEL. Can somebody tell me how to solve this problem or what makes this happen. Thank you for your help. From owner-sqr-users@USA.NET Wed Apr 3 10:03:59 1996 Date: Wed, 3 Apr 1996 09:17:00 -0500 From: John Douglas Subject: Leap Year Problem? Greetings from southeastern Ohio. We are running SQRW V2.3.1 on Oracle7.0.15. We have an SQR that reports employee status changes, like transfers and promotions, between a user specified start date and end date. When the user answers the related start date and end date prompts with 01-FEB-96 and 29-FEB-96 respectively, the query returns about half of the records it should return. The user gets all records by running the query twice; first with the 01-FEB-96 start date and 28-FEB-96 end date; and then with a start date AND end date of 29-FEB-96. Does this have anything to do with leap year? Here is the code: FROM ps_personal_data p1, ps_job j2, ps_job j WHERE ((j1.effdt between $start_date and $end_date) or ((j1.action_dt between $start_date and $end_date) and (j1.effdt between $end_dt_minus_2yrs and $end_date))) AND j1.action = $transfer AND length(j1.deptid) = 6 AND length(j2.deptid) = 6 AND ((substr(j1.deptid,1,3) = $SlctLoc) or (substr(j2.deptid,1,3) = $SlctLoc)) AND j2.emplid = j1.emplid AND j2.effdt = (select MAX(j3.effdt) from ps_job j3 where j3.emplid = j2.emplid and j3.deptid <> j1.deptid and j3.effdt < j1.effdt) AND j2.effseq = (select MAX(j4.effseq) from ps_job j4 where j4.emplid = j2.emplid and j4.deptid = j2.deptid and j4.effdt = j2.effdt) AND p1.emplid = j1.emplid ORDER BY j1.effdt, j1.effseq desc, j1.emplid END-SELECT Any ideas? Thanks, John Douglas Lockheed Martin Utility Services Piketon, Ohio From owner-sqr-users@USA.NET Wed Apr 3 10:22:00 1996 Date: Wed, 3 Apr 1996 08:54:13 -0600 From: johng@RMF41.USACE.ARMY.MIL Subject: Reply to Select Columns in Left Margin > Does anyone know why you have to place column names at the left margin in > SELECT paragraphs? The User's Guide explains that it is to differentiate > column names and commands. Couldn't there be a more aesthetical solution? A > structured program in SQR is because of this an oxymoron... > > Have I perhaps overlooked any "work around" of this matter? > > Orjan Nordlund > National Council for Crime Prevention Sweden Only the first column name needs to be indented, if you are using only column names and &aliases, provided you use comma separators between the column/alias pairs, and there is no intermixed SQR code. Assuming you put all column specs first within the begin-select, followed by the sqr code, then there is only 1 "sore thumb" in your structured listing. IHMO, structured-listings don't mean structured programs, although they can help in reading one. They can also hide logic flaws... hence, lack of indentation within the begin-selects doesn't bother me. ------------------------------------------------------------------------ John Griffin (205) 890-2401 | A TTTTTTT SSSSS Advanced Technology Systems (ATS), Inc. | A A T S 4801 University Square, Suite 2 | AaaaA T SSSSS Huntsville, AL 35816-3431 | A A T S johng@rmf41.usace.army.mil | A A T SSSSS ------------------------------------------------------------------------ From owner-sqr-users@USA.NET Wed Apr 3 10:38:47 1996 Date: Wed, 3 Apr 1996 10:06:28 -0500 From: Gary Gallup - Sys Mgr/DBA Subject: SQR Upgrade I've had SQR running here for several years, first on a VAX, then on a Dec 5900 running Ulrix. My database is Oracle. When I moved from the VAX to the 5900, the license transfer was very reasonable. Now I'm moveing to a Digital Alpha 2100 on unix, and have found that the license transfer fee is huge. I would like to know if others with SQR have had similar experiences or if you are looking at any other products to replace SQR? Thanks. __________________________________________________________________ \ Gary Gallup Information Technology Services_/ | Systems+Database+Network Northwestern Michigan College _/ | gary@nmc.edu Traverse City, MI 49684 _____/ \_______________________________________________________/ From owner-sqr-users@USA.NET Wed Apr 3 12:09:00 1996 Date: Wed, 3 Apr 1996 10:41:31 EDT From: geetter@UHAVAX.HARTFORD.EDU Subject: REMOVING INSERT, DELETE, UPDATE FUNCTIONALITY I am a new SQR user and would like to know how, if possible, to prevent almost all SQR users from inserting, deleting or updating data via SQR. All users are assigned ORACLE table grants via ROLES and these roles do have grants to many tables that include INSERT, DELETE, and UPDATE. The grants are necessary to use ORACLE FORMS in our systems and use of the FORMS through a FORMS security interface controls our users in there ability to use the FORMS in query mode only or update mode. These underlying grants leave us vulnerable to uses being able to write SQR processes which could update, delete or add data to our database. On the other hand, we do want this funtionality for our development staff. Another question I have is, is there a way to allow only specific accounts to have access to SQR? Please answer direcdtly if you prefer to geetter@uhavax.hartford.edu . Any help would be most appreciated. Allan Geetter Data Security Administrator University of Hartford West Hartford, CT 06117 Voice (860) 768-4889 FAX (860) 768-4907 From owner-sqr-users@USA.NET Wed Apr 3 12:18:53 1996 Date: Wed, 3 Apr 1996 09:50:34 -0600 From: "Jeff T. Orwick [C]" Subject: Re: Select Columns in Left Margin No you haven't over looked anything its the way the parser works. You can however place more than one column on the same line if separated by a comma. I've been using SQR for 3 years now and WOULD NOT recommend this solution. The best way to deal with it esthetically that I've found is this. begin-procedure proc_name begin-select table.column1 &co1 table.column2 &co2 to_char(table.column3) &co3 print &co1 (+1,1,10) print &co2 (,+1,20) print &co3 (,+1,9) from table where ... end-select end-procedure I know it's not what you're hoping for, but, if you're consistent, your eye quickly learns to distinguish the pieces. -- Jeff T. Orwick orwickj@tusc.com <- Full time address http://www.tusc.com From owner-sqr-users@USA.NET Wed Apr 3 12:31:56 1996 Date: Wed, 3 Apr 1996 10:04:40 -0600 From: "Jeff T. Orwick [C]" Subject: Re: Leap Year Problem? Do you have this same problem for any other date ranges? And what's the date on the missing records if you specify the full range of dates. You may want to consider doing an explicit to_date on your end_date value to force the date to the last second of Feb-29 (i.e. to_date($end_date||' 23:59:59','DD-MON-RR HH24:MI:SS') ) If you don't specify the hours minutes seconds oracle converts it to 00:00:00 of that day. Remember oracle dates always store the time in the database. If the field you're checking was populated by SYSDATE it will have time in it and not be defaulted to 00:00:00. Because 29-FEB-96 00:00:00 occurs before 29-FEB-96 08:00:00 these records would NOT be returned. That said, I've also noticed that when using between and dates that or the same day on each side without specifying times seems to return everything that happend that day. Hope this helps you. Jeff Orwick TUSC On Apr 3, 9:17am, John Douglas wrote: > Subject: Leap Year Problem? > Greetings from southeastern Ohio. > > We are running SQRW V2.3.1 on Oracle7.0.15. > > We have an SQR that reports employee status changes, like transfers > and promotions, between a user specified start date and end date. > > When the user answers the related start date and end date prompts with > 01-FEB-96 and 29-FEB-96 respectively, the query returns about half of the > records it should return. The user gets all records by running the query twice; > first with the 01-FEB-96 start date and 28-FEB-96 end date; and then with a > start date AND end date of 29-FEB-96. Does this have anything to do with leap > year? > > Here is the code: > > FROM ps_personal_data p1, ps_job j2, ps_job j > WHERE ((j1.effdt between $start_date and $end_date) > or ((j1.action_dt between $start_date and $end_date) > and (j1.effdt between $end_dt_minus_2yrs and $end_date))) > AND j1.action = $transfer > AND length(j1.deptid) = 6 > AND length(j2.deptid) = 6 > AND ((substr(j1.deptid,1,3) = $SlctLoc) or > (substr(j2.deptid,1,3) = $SlctLoc)) > AND j2.emplid = j1.emplid > AND j2.effdt = (select MAX(j3.effdt) > from ps_job j3 > where j3.emplid = j2.emplid > and j3.deptid <> j1.deptid > and j3.effdt < j1.effdt) > AND j2.effseq = (select MAX(j4.effseq) > from ps_job j4 > where j4.emplid = j2.emplid > and j4.deptid = j2.deptid > and j4.effdt = j2.effdt) > AND p1.emplid = j1.emplid > ORDER BY j1.effdt, j1.effseq desc, j1.emplid > END-SELECT > > Any ideas? > > Thanks, > > John Douglas > Lockheed Martin Utility Services > Piketon, Ohio >-- End of excerpt from John Douglas -- Jeff T. Orwick orwickj@tusc.com <- Full time address http://www.tusc.com From owner-sqr-users@USA.NET Wed Apr 3 12:57:24 1996 Date: Wed, 3 Apr 1996 07:59:30 PST From: johnk@SUN4.MITI.COM Subject: Re: Leap Year Problem? Do you get the same problem when running the query in SQL*Plus? If so, it would be an Oracle problem. John L. Kellogg MITI Technical Support Manager ______________________________ Reply Separator _________________________________ Subject: Leap Year Problem? Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 03/04/96 7:25 AM Greetings from southeastern Ohio. We are running SQRW V2.3.1 on Oracle7.0.15. We have an SQR that reports employee status changes, like transfers and promotions, between a user specified start date and end date. When the user answers the related start date and end date prompts with 01-FEB-96 and 29-FEB-96 respectively, the query returns about half of the records it should return. The user gets all records by running the query twice; first with the 01-FEB-96 start date and 28-FEB-96 end date; and then with a start date AND end date of 29-FEB-96. Does this have anything to do with leap year? Here is the code: FROM ps_personal_data p1, ps_job j2, ps_job j WHERE ((j1.effdt between $start_date and $end_date) or ((j1.action_dt between $start_date and $end_date) and (j1.effdt between $end_dt_minus_2yrs and $end_date))) AND j1.action = $transfer AND length(j1.deptid) = 6 AND length(j2.deptid) = 6 AND ((substr(j1.deptid,1,3) = $SlctLoc) or (substr(j2.deptid,1,3) = $SlctLoc)) AND j2.emplid = j1.emplid AND j2.effdt = (select MAX(j3.effdt) from ps_job j3 where j3.emplid = j2.emplid and j3.deptid <> j1.deptid and j3.effdt < j1.effdt) AND j2.effseq = (select MAX(j4.effseq) from ps_job j4 where j4.emplid = j2.emplid and j4.deptid = j2.deptid and j4.effdt = j2.effdt) AND p1.emplid = j1.emplid ORDER BY j1.effdt, j1.effseq desc, j1.emplid END-SELECT Any ideas? Thanks, John Douglas Lockheed Martin Utility Services Piketon, Ohio From owner-sqr-users@USA.NET Wed Apr 3 13:01:42 1996 Date: Wed, 3 Apr 1996 11:14:19 -0600 From: "Jeff T. Orwick [C]" Subject: Re: REMOVING INSERT, DELETE, UPDATE FUNCTIONALITY RE: Your second question. Another question I have is, is there a way to allow only specific accounts to have access to SQR? If you're on unix only give access execute sqr to the unix groups you want to have those privelages. As for your first question your DBA may be able to restrict access based on the tool accessing the data-base. I seem to remember that ability at some level covered in an Oracle 6 DBA class. I don't think you could specifiy restrictions to update, delete, etc. at the table level, AND I don't know if that functionality extended to third party products or not. -- Jeff T. Orwick orwickj@tusc.com <- Full time address http://www.tusc.com From owner-sqr-users@USA.NET Wed Apr 3 13:25:49 1996 Date: Wed, 3 Apr 1996 12:50:57 -0500 From: Wes Bailey Subject: Re: Leap Year Problem? Due to the timestamp on the date in the database, all the dates on the 29th are being lost because the following example relation is true: j1.effdt $end_date 1996:04:04:08:00:00 > 1996:04:04:00:00:00 using the TRUNC function should eliminate your problem. Here is how the WHERE clause should be written: where ((trunc(j1.effdt) between to_date($start_date) and to_date($end_date))) or ((trunc(j1.action_dt) between to_date($start_date) and to_date($end_date))) or ((trunc(j1.effdt) between to_date($end_dt_minus_2yrs) and to_date($end_date))) ... Wes Bailey Consultant Ray Ontko & Co. ---------------------------------------------------------------------- > Subject: Leap Year Problem? > Greetings from southeastern Ohio. > > We are running SQRW V2.3.1 on Oracle7.0.15. > > We have an SQR that reports employee status changes, like transfers > and promotions, between a user specified start date and end date. > > When the user answers the related start date and end date prompts with > 01-FEB-96 and 29-FEB-96 respectively, the query returns about half of the > records it should return. The user gets all records by running the query twice; > first with the 01-FEB-96 start date and 28-FEB-96 end date; and then with a > start date AND end date of 29-FEB-96. Does this have anything to do with leap > year? > > Here is the code: > > FROM ps_personal_data p1, ps_job j2, ps_job j > WHERE ((j1.effdt between $start_date and $end_date) > or ((j1.action_dt between $start_date and $end_date) > and (j1.effdt between $end_dt_minus_2yrs and $end_date))) > AND j1.action = $transfer > AND length(j1.deptid) = 6 > AND length(j2.deptid) = 6 > AND ((substr(j1.deptid,1,3) = $SlctLoc) or > (substr(j2.deptid,1,3) = $SlctLoc)) > AND j2.emplid = j1.emplid > AND j2.effdt = (select MAX(j3.effdt) > from ps_job j3 > where j3.emplid = j2.emplid > and j3.deptid <> j1.deptid > and j3.effdt < j1.effdt) > AND j2.effseq = (select MAX(j4.effseq) > from ps_job j4 > where j4.emplid = j2.emplid > and j4.deptid = j2.deptid > and j4.effdt = j2.effdt) > AND p1.emplid = j1.emplid > ORDER BY j1.effdt, j1.effseq desc, j1.emplid > END-SELECT > > Any ideas? > > Thanks, > > John Douglas > Lockheed Martin Utility Services > Piketon, Ohio From owner-sqr-users@USA.NET Wed Apr 3 14:16:38 1996 Date: Wed, 3 Apr 1996 13:21:00 -0500 From: John Douglas Subject: Subscription procedure. Please send me the proper address and procedure a person needs to use to subscribe to SQR-USERS...I had this person send his subscribe request to SQR-USERS@USA.net He got a response that seems to indicate he is sending his request to the wrong address. Thanks, John Douglas. Lockheed Martin Utility Services DOUGLASJG@ORNL.GOV PS. Thanks for the feedback on the Leap Year problem. From owner-sqr-users@USA.NET Wed Apr 3 15:28:48 1996 Date: Wed, 3 Apr 1996 13:39:16 -0600 From: "Jeff T. Orwick [C]" Subject: Re: Leap Year Problem? DON'T USE TRUNC on dates. Any function on a column throws away any indexes that is available for use now or in the future. Instead use to_date on your variables to insure they cover the entire day in your range. On Apr 3, 12:50pm, Wes Bailey wrote: > Subject: Re: Leap Year Problem? > Due to the timestamp on the date in the database, all the dates on the > 29th are being lost because the following example relation is true: > > j1.effdt $end_date > 1996:04:04:08:00:00 > 1996:04:04:00:00:00 > > using the TRUNC function should eliminate your problem. Here is how > the WHERE clause should be written: > > where ((trunc(j1.effdt) between to_date($start_date) > and > to_date($end_date))) > or ((trunc(j1.action_dt) between to_date($start_date) > and > to_date($end_date))) > or ((trunc(j1.effdt) between to_date($end_dt_minus_2yrs) > and > to_date($end_date))) > ... > > Wes Bailey > Consultant > Ray Ontko & Co. > > ---------------------------------------------------------------------- > > Subject: Leap Year Problem? > > Greetings from southeastern Ohio. > > > > We are running SQRW V2.3.1 on Oracle7.0.15. > > > > We have an SQR that reports employee status changes, like transfers > > and promotions, between a user specified start date and end date. > > > > When the user answers the related start date and end date prompts with > > 01-FEB-96 and 29-FEB-96 respectively, the query returns about half of the > > records it should return. The user gets all records by running the query > twice; > > first with the 01-FEB-96 start date and 28-FEB-96 end date; and then with a > > start date AND end date of 29-FEB-96. Does this have anything to do with leap > > year? > > > > Here is the code: > > > > FROM ps_personal_data p1, ps_job j2, ps_job j > > WHERE ((j1.effdt between $start_date and $end_date) > > or ((j1.action_dt between $start_date and $end_date) > > and (j1.effdt between $end_dt_minus_2yrs and $end_date))) > > AND j1.action = $transfer > > AND length(j1.deptid) = 6 > > AND length(j2.deptid) = 6 > > AND ((substr(j1.deptid,1,3) = $SlctLoc) or > > (substr(j2.deptid,1,3) = $SlctLoc)) > > AND j2.emplid = j1.emplid > > AND j2.effdt = (select MAX(j3.effdt) > > from ps_job j3 > > where j3.emplid = j2.emplid > > and j3.deptid <> j1.deptid > > and j3.effdt < j1.effdt) > > AND j2.effseq = (select MAX(j4.effseq) > > from ps_job j4 > > where j4.emplid = j2.emplid > > and j4.deptid = j2.deptid > > and j4.effdt = j2.effdt) > > AND p1.emplid = j1.emplid > > ORDER BY j1.effdt, j1.effseq desc, j1.emplid > > END-SELECT > > > > Any ideas? > > > > Thanks, > > > > John Douglas > > Lockheed Martin Utility Services > > Piketon, Ohio >-- End of excerpt from Wes Bailey -- Jeff T. Orwick orwickj@tusc.com <- Full time address http://www.tusc.com From owner-sqr-users@USA.NET Wed Apr 3 18:12:01 1996 Date: Wed, 3 Apr 1996 13:51:04 PST From: johnk@SUN4.MITI.COM Subject: Re: Select Columns in Left Margin The need to differentiate column names from SQR command is indeed the main reason that column names are required to be placed at the left margin. The need arises from SQRs ability to allow commands to be embedded in select blocks. For example: begin-select column let #variable = #variable + &column print #variable (+1,1) from table end-select We are looking at an enhancement that would allow indentation of the begin-select block, thus re-defining the left margin for that select block only. The code could look like this: begin-procedure main1 do main2 end-procedure begin-procedure main2 begin-select column let #variable = #variable + &column from table end-select end-procedure John L. Kellogg MITI Technical Support Manager ______________________________ Reply Separator _________________________________ Subject: Select Columns in Left Margin Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 03/04/96 5:24 AM Does anyone know why you have to place column names at the left margin in SELECT paragraphs? The User's Guide explains that it is to differentiate column names and commands. Couldn't there be a more aesthetical solution? A structured program in SQR is because of this an oxymoron... Have I perhaps overlooked any "work around" of this matter? Orjan Nordlund National Council for Crime Prevention Sweden From owner-sqr-users@USA.NET Thu Apr 4 01:08:53 1996 Date: Thu, 4 Apr 1996 00:28:40 -0500 From: Nathan Treadway Subject: Re: Leap Year Problem? >When the user answers the related start date and end date prompts with >01-FEB-96 and 29-FEB-96 respectively, the query returns about half of the >records it should return. The user gets all records by running the query twice; >first with the 01-FEB-96 start date and 28-FEB-96 end date; and then with a >start date AND end date of 29-FEB-96. Does this have anything to do with leap >year? > >Here is the code: > >FROM ps_personal_data p1, ps_job j2, ps_job j >WHERE ((j1.effdt between $start_date and $end_date) > or ((j1.action_dt between $start_date and $end_date) > and (j1.effdt between $end_dt_minus_2yrs and $end_date))) [...] The comments on the list about using an explicit to_date($end_date||'23:59:59'...) for date comparisions are true, but I don't think they completely explain the stated problem. There are three date ranges mentioned in the message: A 01-FEB-96 to 29-FEB-96 B1 01-FEB-96 to 28-FEB-96 B2 29-FEB-96 to 29-FEB-96 Without the explicit to_date calls, range A would return *at least as many* records as B1 and B2 together. This is because times between 28-FEB-96 00:00:01 and 28-FEB-96 23:59:59 would not be returned for either B1 or B2, but would be for A. All other records would be treated the same by A and (B1 and B2) (e.g. the records from 29-FEB-96 that are being skipped because of the missing to_date will be skipped in both cases). On the other hand, the message states that A is returning *fewer* records than B1 and B2 together. 1 WHERE ((j1.effdt between $start_date and $end_date) 2 or ((j1.action_dt between $start_date and $end_date) 3 and (j1.effdt between $end_dt_minus_2yrs and $end_date))) I can't say for sure without knowing what happens for other date ranges, but my guess is that the records missing from A but found in B2 have '29-FEB-96 00:00:00' in action_dt (i.e. have times already truncated) but some other value in effdt (probably 29-FEB-96 with some time value). If this is true, then line 1 would reject and line 2 would match all those records in both A and B2; the difference between the results would possibly due to the calculation of $end_dt_minus_2yrs for A. Mr. Douglas, perhaps you could let us know how this is calculated, as well as results you get from other pairs of date ranges. (Also, are all the records missing from A found in B2, or are there some also found in B1? Are there any records in B2 that *are* found in A?) If you've already solved the problem, what was causing it? Nathan ---------------------------------------------------------------------------- Nathan Treadway | Ray Ontko & Co. | info@ontko.com (auto-reply server) nathant@ontko.com | Richmond, IN | ftp.ontko.com, http://www.ontko.com/ From owner-sqr-users@USA.NET Thu Apr 4 01:46:17 1996 Date: Thu, 4 Apr 1996 12:17:40 +0530 From: Deepak Sharma Subject: Re: End of Communication Channel I faced the same problem a few times. I got to know from someone that there is a patch available which solves it. Otherwise the only solution is to restart the SQL*Net listener. > > I was running a SQR report and encountered ORA-03113 END OF COMMUNICATION > CHANNEL. Can somebody tell me how to solve this problem or what makes this > happen. Thank you for your help. > BRgds, + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - Deepak Sharma +-----------------------------+ Tel : 0091118562585 Sr. Lead Analyst / ####### ## ## ## / 88-92 Tata Unisys Limited / ## ## ## ## / NEPZ / ## ## ## ## / Fax : 0091118562584 Noida / ## ## ## ## / INDIA / ## ######## ####### / +-----------------------------+ Tlx : 05905211TULNIN deepak@tulnepz.unisys.com + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - From owner-sqr-users@USA.NET Thu Apr 4 14:28:08 1996 Date: Thu, 4 Apr 1996 09:51:07 PST From: johnk@SUN4.MITI.COM Subject: Re: SQR Upgrade Here is a reply from MITI's Direct Sales Manager: "SQR license fees are based on multiple variables, not solely geared toward hardware platforms. MITI does attempt to gear the pricing as precisely as we can to specific computing environments. We welcome users to contact us at any time for information related to their unique configurations." "Regarding license transference, the MITI policy is to apply full credit for the existing license, thus allowing users to retain their full investment in SQR. This is not necessarily the standard practice in the industry, but we feel that this flexibility is a valuable benefit to our users who employ SQR as an enterprise reporting tool." Regards, John L. Kellogg MITI Technical Support Manager ______________________________ Reply Separator _________________________________ Subject: SQR Upgrade Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet Date: 03/04/96 8:59 AM I've had SQR running here for several years, first on a VAX, then on a Dec 5900 running Ulrix. My database is Oracle. When I moved from the VAX to the 5900, the license transfer was very reasonable. Now I'm moveing to a Digital Alpha 2100 on unix, and have found that the license transfer fee is huge. I would like to know if others with SQR have had similar experiences or if you are looking at any other products to replace SQR? Thanks. __________________________________________________________________ \ Gary Gallup Information Technology Services_/ | Systems+Database+Network Northwestern Michigan College _/ | gary@nmc.edu Traverse City, MI 49684 _____/ \_______________________________________________________/ From owner-sqr-users@USA.NET Fri Apr 5 10:58:18 1996 Date: Fri, 5 Apr 1996 10:00:21 -0500 From: Ray Ontko Subject: Re: SQR Upgrade John, I'm not sure how you value the existing license, but if your prices have gone up substantially since the original purchase (say 5 years ago), then the perceived cost is very high. I know of at least one other customer that was very frustrated on this count. They upgraded from a Vax to an alpha, and the cost to upgrade their SQR license would have been MORE than the cost of the machine, operating system, and Oracle license combined. Applying full credit for the existing license is great, but where does that leave your existing customers given your current pricing strategy? Ray > > Here is a reply from MITI's Direct Sales Manager: > > "SQR license fees are based on multiple variables, not solely > geared toward hardware platforms. MITI does attempt to gear the > pricing as precisely as we can to specific computing environments. We > welcome users to contact us at any time for information related to > their unique configurations." > > "Regarding license transference, the MITI policy is to apply full > credit for the existing license, thus allowing users to retain their > full investment in SQR. This is not necessarily the standard practice > in the industry, but we feel that this flexibility is a valuable > benefit to our users who employ SQR as an enterprise reporting tool." > > Regards, > > John L. Kellogg > MITI Technical Support Manager > > ______________________________ Reply Separator _________________________________ > Subject: SQR Upgrade > Author: usa.net!SQR-USERS@netcomsv.netcom.com at Internet > Date: 03/04/96 8:59 AM > > > I've had SQR running here for several years, first on a VAX, then on a Dec 5900 > running Ulrix. My database is Oracle. When I moved from the VAX to the 5900, the > license transfer was very reasonable. Now I'm moveing to a Digital Alpha 2100 on > unix, and have found that the license transfer fee is huge. I would like to know > if others with SQR have had > similar experiences or if you are looking at any other products to replace SQR? > Thanks. > > ____________________________