From owner-sqr-users@list.iex.net Thu Oct 1 00:13:29 1998 Date: Thu, 1 Oct 1998 00:00:00 -0500 From: Nathan Treadway Subject: Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Treadway, Ray Ontko & Co. (nathant@ontko.com) $Revision: 1.0 $ $Date: 1998-09-30 20:55:21-04 $ This document gathers in one location information about the sqr-users mail list. Directions for common list-server commands are given, as are guidelines for list usage. ______________________________________________________________________ Table of Contents 1. Introduction 1.1 sqr-users Mailing List 1.2 sqr-users Hints and Guidelines Document 2. Basic List Server Commands 2.1 Subscribing to the List 2.2 Unsubscribing from the List 2.3 Digests 2.4 Problems 3. Posting to the sqr-users List 3.1 Posting a Message 3.2 Replying to a Message 4. Job Announcements ______________________________________________________________________ 1. Introduction 1.1. sqr-users Mailing List The sqr-users mailing list was created to enable discussion related to the SQR database language (and to other products from SQRIBE Technologies). The mailing list is provided as a service of Ray Ontko & Co. . A searchable archive of past postings to the mailing list is available from the SQR Users Group web site . 1.2. sqr-users Hints and Guidelines Document This document gives various hints and guidelines about the sqr-users mailing list. It will be posted approximately monthly to the mailing list so that new users can see it (and existing users can find it easily). An HTML version of the document is always available at http://www.sqrug.com/~sqrusers/sqr-users-hints.html. If you have comments or suggestions about this document, please contact me directly at the address listed in the title section. This document is (C)Copyright 1998 by Nathan Treadway. It may be freely copied and distributed provided it is not modified in any way and it retains the original copyright notice. 2. Basic List Server Commands Here are a few basic list server commands. Subscriptions and unsubscriptions can normally be processed automatically by sending the proper command as the only line in the body (not the subject line) of an e-mail message sent to "listserv@usa.net". 2.1. Subscribing to the List To subscribe to the mailing list, the command is subscribe sqr-users Your Name Note that you do not need to provide your e-mail address as it is taken from your message's header. You will be e-mailed a confirmation number, which you must then send back to the list server software before the command goes into effect. 2.2. Unsubscribing from the List To unsubscribe, the command is simply unsubscribe sqr-users Once again, you will need to confirm your command before it goes into effect. 2.3. Digests Once you are subscribed, you can request to get your messages in digest form (one large message containing all of the posts made each day) by sending the command set sqr-users digests If you use digests you should not just "reply" to the digest mailing. Instead, be sure your message's subject header matches the subject in the original posting to which you are replying, and that you don't include a quoted copy of the entire digest in your post. Use "nodigests" instead of "digests" to restore normal distribution. 2.4. Problems If the above commands do not work, please contact the list manager at "sqr-users-request@usa.net". (Do not send a message to the list itself -- no one there can help you with your problem!) Please include a copy of the error message you received from the list server. 3. Posting to the sqr-users List In order to post to the mailing list, you must be subscribed to it (see directions above). 3.1. Posting a Message Once you are subscribed, you can post to the list by sending a message to "sqr-users@usa.net". Be sure your subject heading describes your specific problem or question (i.e. don't just use "SQR Question"). In the body of your message, include the following information: o SQRIBE product and version number o Operating system and version number o Database name and version number o Application name (i.e. PeopleSoft) and a version number, if any Also, include a short code sample whenever possible. It's much easier for other people to understand what you are trying to do when they can see the actual code. (At the same time, be careful not to post confidential code, since the posting will be placed in a public web archive.) Note that your question should relate to a SQRIBE product in some way. Don't ask questions about PeopleSoft, for example, unless you are having SQR-related issues. (To find out your version of SQR, you can run the following program: begin-program show $sqr-ver end-program This will produce a line that looks something like this: SQR/3.0.15/Intel/SCO Unix R3.2 V5.0/Oracle 7.2.2.3/Sep 25 1996 Include the full line of output in your message. Note that the oper- ating system version number listed here is the one under which this copy of SQR was compiled, so you'll still need to include your own information.) 3.2. Replying to a Message Messages sent out on the list have a Reply-To header pointing to the list. This means that you should be able to simply use your mail reader's "reply" function to send a reply to the list. Generally, all answers to questions sent out on the list should be public replies, so that other subscribers can benefit. In those case where a private reply is appropriate, be sure to over-ride the default Reply-To address. If your message is not actually a reply to the earlier message, please do not use the "reply" function. Instead, address a new e-mail message to the list as described in the previous section. (This is because the list archives will incorrectly show the new message in the original thread if you use "reply".) You should quote enough lines from the original message that later readers can understand your answer even if they missed the orginal question. At the same time, you should remove any unnecessary lines from the quote in order to make your message shorter and easier to understand. In particular, in order to prevent mail loops, the list server will not accept messages that contain From:, Sender:, etc. lines pointing back to the sqr-users list, even if these lines are quoted (i.e. with ">" characters). Be sure to delete these from your reply message before sending it. 4. Job Announcements SQR-related job announcements are currently allowed on the list, subject to the following restrictions: o The subject line must begin with the words "JOB ANNOUNCEMENT:". o The message should be very short (fewer than 20 lines), giving brief details of the job and a way for those interested to get more information directly (i.e. web page or phone number). o The message should specifically remind people interested in the job to respond directly to the message author (and not to use their mailer's "reply" function, since that would send the reply out on the list). o Post job announcements as infrequently as possible. From owner-sqr-users@list.iex.net Thu Oct 1 08:04:20 1998 Date: Thu, 1 Oct 1998 08:51:04 -0400 From: GINO SANTERRE Subject: How to print a report in a window When we launch a report(SQR) in the menu of People soft. We can chose the destination of the output(file, printer, window). Do anyone have problems with the window destination? I think this option create a file named %OUTPUTDEST% on my disk but i never see my report in a window like Crystal Report do. Thanks Gino Santerre Le Groupe Quebectel From owner-sqr-users@list.iex.net Thu Oct 1 09:28:54 1998 Date: Thu, 1 Oct 1998 10:03:37 -0500 From: Stuart Wood Subject: Re: How to print a report in a window Append -ZIV to your command line in your process definition and run the report to FILE. ______________________________ Reply Separator _________________________________ Subject: How to print a report in a window Author: at internet Date: 10/1/98 8:51 AM When we launch a report(SQR) in the menu of People soft. We can chose the destination of the output(file, printer, window). Do anyone have problems with the window destination? I think this option create a file named %OUTPUTDEST% on my disk but i never see my report in a window like Crystal Report do. Thanks Gino Santerre Le Groupe Quebectel From owner-sqr-users@list.iex.net Thu Oct 1 09:30:21 1998 Date: Thu, 1 Oct 1998 09:10:15 -0500 From: "Korbel Gordon E." Subject: Page 1 of xx Hi all, Platform: Sybase SQR Version: 3.0.18.1.1 I'm working with Peoplesoft and SQR, and I need some help! The Peoplesoft provided invoice contains a page # field -- The users here have requested that I modify that so it reads, per invoice, Page 1 of xx. Is there some trick of which I am unaware, or is this probably going to be as difficult as I think it will be? Thanks in advance! -Gordon Korbel From owner-sqr-users@list.iex.net Thu Oct 1 09:33:21 1998 Date: Thu, 1 Oct 1998 09:13:05 CDT From: "C. Willis III" Subject: Re: SQR Tools - www.tcssoft.com sorry, guys; obviously I am broken too for sending the message to the group rather than the person I meant to send it to. clark ----Original Message Follows---- your site is broken. how can i get a demo copy? clark ----Original Message Follows---- We are marketing the following tools for SQR System Analysts and Programmers, which will drametically increase the productivity: ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Thu Oct 1 09:43:52 1998 Date: Thu, 1 Oct 1998 07:26:57 PDT From: mahesh bhor Subject: Re: Page 1 of xx If you are going to print only one invoice per report you can try the example given alongwith "page-number" in the SQR Manual . It makes use of a new command last-page which I think is introduced from ver 3.0 onwards . Hope this helps . Mahesh ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Thu Oct 1 10:05:18 1998 Date: Thu, 1 Oct 1998 09:51:42 -0500 From: Ray Ontko Subject: Re: lotus notes interface Howdy, Thank you all for your response to my Lotus Notes question. I also got a private reply which the author agreed to let me repost here: > Ray, > > 1. There is an ODBC driver for Lotus Notes available at the Lotus Web > Site http://www.lotus.com. We do not use it at this time. It is free. I > would think that you could use SQR to join Oracle and Notes via separate > queries > > 2. What we are doing is using a product called Replic-Action from > http://www.casahl.com that sychronizes Data between Notes and Oracle using > ODBC. This product allows us full two-way synchronization of data but is > pretty pricey. (10K). > > 3. Within Lotus Notes you can embed ODBC to bring Oracle into Notes but I > have not worked with that at all. > > 4. Other interesting and cheaper solutions may be using Lotus Approach > with both Oracle and Notes. > > Hope this helps. > > -Ben Burkland > BBurklan@erim-int.com > > >From: Ray Ontko[SMTP:rayo@ONTKO.COM] > >Sent: September 30, 1998 5:10 AM > >To: Multiple recipients of list SQR-USERS > >Subject: lotus notes > > > >Howdy, > > > >Is anyone out there using SQR as a reporting tool for > >a Lotus Notes database? If so, how do you do it? > >Can SQR (with ODBC) be used directly against a Lotus > >Notes database? Does Lotus have an export function that > >we could use to readily load the data into an Oracle > >database? > > > >Ray > >---------------------------------------------------------------------- > >Ray Ontko | Ray Ontko & Co | "Ask me about SQR as a DW tool" > >rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | "Time for a new signature line." rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ From owner-sqr-users@list.iex.net Thu Oct 1 10:10:54 1998 Date: Thu, 1 Oct 1998 10:29:09 -0400 From: John Walker Subject: Re: Page 1 of xx I use the following; print 'Page ' (4,63) page-number (4) print ' of ' (4) last-page (4) I hope this helps. "Korbel Gordon E." on 10/01/98 10:10:15 AM Please respond to SQR-USERS@USA.NET To: Multiple recipients of list SQR-USERS cc: (bcc: John A. Walker/CEI/FirstEnergy) Subject: Page 1 of xx Hi all, Platform: Sybase SQR Version: 3.0.18.1.1 I'm working with Peoplesoft and SQR, and I need some help! The Peoplesoft provided invoice contains a page # field -- The users here have requested that I modify that so it reads, per invoice, Page 1 of xx. Is there some trick of which I am unaware, or is this probably going to be as difficult as I think it will be? Thanks in advance! -Gordon Korbel From owner-sqr-users@list.iex.net Thu Oct 1 10:16:36 1998 Date: Thu, 1 Oct 1998 09:56:43 -0500 From: Ray Ontko Subject: Re: lotus notes Howdy, Thank you all for your response to my Lotus Notes question. I also got a private reply which the author agreed to let me repost here: > Ray, > > 1. There is an ODBC driver for Lotus Notes available at the Lotus Web > Site http://www.lotus.com. We do not use it at this time. It is free. I > would think that you could use SQR to join Oracle and Notes via separate > queries > > 2. What we are doing is using a product called Replic-Action from > http://www.casahl.com that sychronizes Data between Notes and Oracle using > ODBC. This product allows us full two-way synchronization of data but is > pretty pricey. (10K). > > 3. Within Lotus Notes you can embed ODBC to bring Oracle into Notes but I > have not worked with that at all. > > 4. Other interesting and cheaper solutions may be using Lotus Approach > with both Oracle and Notes. > > Hope this helps. > > -Ben Burkland > BBurklan@erim-int.com > > >Howdy, > > > >Is anyone out there using SQR as a reporting tool for > >a Lotus Notes database? If so, how do you do it? > >Can SQR (with ODBC) be used directly against a Lotus > >Notes database? Does Lotus have an export function that > >we could use to readily load the data into an Oracle > >database? > > > >Ray Ray ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | "Time for a new signature line." rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ From owner-sqr-users@list.iex.net Thu Oct 1 10:39:29 1998 Date: Thu, 1 Oct 1998 11:26:11 -0400 From: td Subject: Re: Page 1 of x To obtain X of Y pages in advance.... It all depends on the particular application your running. I had a similar requirement recently. I rewrote the A/R Statement/Build process from scratch. At the START of each statement I know the ENDING balance, final aging listing, Number of customers for each Correspondence Customer, etc, AND total pages (x of y) for Each customer. The delivered process basically produces a list without any logic so a rewrite was necessary. You may need to produce and populate a header table so you can calculate the invoice lines and predetermine the number of pages that will be produced. Hope this helps. -Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice tdelia@erols.com From owner-sqr-users@list.iex.net Thu Oct 1 10:44:38 1998 Date: Thu, 1 Oct 1998 11:08:14 -0400 From: td Subject: SQR Tools - SQR Analyzer/Application Engine Analyzer This is my first post to this group... I noticed an interest in SQR Tools... I'm considering posting some I've developed on my website as ShareWare. The source is "leaking" out anyway (as far as Japan) so I may as well post them. Here are some sample tools... ALL written in SQR so there are no additional system requirements. I have plenty more other than those listed below. A - SQR Analyzer (written in SQR). Performs the following... - Include File Expander (Prompts for files not to include such as datetime.sqc, datemath.sqc, number.sqc, etc. - Most of the time you don't want these!). - Lists each Routine and called Routines. - Produces Process Flowchart. * Report Listing adds nice touch to SQR process documentation! B - PeopleSoft Application Engine Analyzer. Ever try to analyze what's happening in Application Engine Process (Payment Predictor, AR Update, etc.). Sections are scattered without link to next section. PeopleSoft suggests looking at the Trace file but that only shows you the output under a "given" set of circumstances. My process maps A/E from start (MAIN) to finish. Performs the following... - Lists the A/E Cache Record. - Lists Sections/Called Sections and Attributes. - Produces process Flowchart. C - Array Processing Tools. - Need to Sort an Array - Here's how to do it properly without sloppy, redundant coding - streamlined approach used (as always). - Need to lookup data in large array - 1000's of entries and starting from 1 to x bogging down your process? Code a Binary Search from scratch (Not Load-Lookup) - Here's how to do it! D - COBOL Analyzer. Of course! Tons more... WinBatch Utilities that Print .LIS files, Ensure PeopleSoft Trace settings are turned OFF on the workstation at Startup, Access Windows Registry Database for any number of reasons (for instance, clear out your Netscape URL history automatically)! I just need to know if posting is worthwhile! I'm very busy these days! -Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice tdelia@erols.com From owner-sqr-users@list.iex.net Thu Oct 1 10:46:27 1998 Date: Thu, 1 Oct 1998 10:31:33 -0500 From: "Korbel Gordon E." Subject: Re: Page 1 of xx This won't work for me because each invoice in the report starts on page '1' -- that is, in a ten page report, I might have four distinct invoices all of which need page 1 of xx on the first page of that particular invoice. I suspect there is no easy way... Thanks for the help! -Gordon Korbel > -----Original Message----- > print 'Page ' (4,63) > page-number (4) > print ' of ' (4) > last-page (4) > > I hope this helps. > --------- > Hi all, > Platform: Sybase > SQR Version: 3.0.18.1.1 > I'm working with Peoplesoft and SQR, and I need some help! > The Peoplesoft provided invoice contains a page # field -- The users here > have requested that I modify that so it reads, per invoice, Page 1 of xx. > Is there some trick of which I am unaware, or is this probably going to be > as difficult as I think it will be? > Thanks in advance! > -Gordon Korbel From owner-sqr-users@list.iex.net Thu Oct 1 11:01:10 1998 Date: Thu, 1 Oct 1998 11:50:53 -0400 From: td Subject: SQR Tools - SQR Analyzer/Application Engine Analyzer This is my SECOND post to this group... (this didn't post the 1st time)! I noticed an interest in SQR Tools... I'm considering posting some I've developed on my website as ShareWare. The source is "leaking" out anyway (as far as Japan) so I may as well post them. Here are some sample tools... ALL written in SQR so there are no additional system requirements. I have plenty more other than those listed below. A - SQR Analyzer (written in SQR). Performs the following... - Include File Expander (Prompts for files not to include such as datetime.sqc, datemath.sqc, number.sqc, etc. - Most of the time you don't want these!). - Lists each Routine and called Routines. - Produces Process Flowchart. * Report Listing adds nice touch to SQR process documentation! B - PeopleSoft Application Engine Analyzer. Ever try to analyze what's happening in Application Engine Process (Payment Predictor, AR Update, etc.). Sections are scattered without link to next section. PeopleSoft suggests looking at the Trace file but that only shows you the output under a "given" set of circumstances. My process maps A/E from start (MAIN) to finish. Performs the following... - Lists the A/E Cache Record. - Lists Sections/Called Sections and Attributes. - Produces process Flowchart. C - Array Processing Tools. - Need to Sort an Array - Here's how to do it properly without sloppy, redundant coding - streamlined approach used (as always). - Need to lookup data in large array - 1000's of entries and starting from 1 to x bogging down your process? Code a Binary Search from scratch (Not Load-Lookup) - Here's how to do it! D - COBOL Analyzer. Of course! Tons more... WinBatch Utilities that Print .LIS files, Ensure PeopleSoft Trace settings are turned OFF on the workstation at Startup, Access Windows Registry Database for any number of reasons (for instance, clear out your Netscape URL history automatically)! How about Mainframe 370 Assembler Utilities running on a PC! I've done extensive development in that area - well, maybe there's not much of a demand for those! :) Not many people need ASM370 Object Code to Source Translaters these days! I just need to know if posting is worthwhile! I'm very busy these days! -Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice tdelia@erols.com From owner-sqr-users@list.iex.net Thu Oct 1 11:19:30 1998 Date: Thu, 1 Oct 1998 11:05:35 -0400 From: Jim Hardesty Subject: Re: Page 1 of xx -Reply Wow, good idea. But no, I don't think you are missing the easy answer because I do not think there is one. SQR reports are page based. A page is not physically written until it reaches the line limit or because of a new-page command. Until that point, you can go up or down in the page and write where ever you want on the page. But once the page is written, it is written to the .lis file and as far as I know it is not possible to go back a page in the report file. There is no old-page command. One obvious way to do it is put some constant value in place of the total number of pages, like '&&&&&&' and then replace that value in the .lis file at the end when you know the final number of pages. Is there some way within the sqr itself to "close" the .lis file so you could then perform an "open" on it and treat it like a flat file and make the replacements? Or would you have to write a seperate program or script that would be executed after the sqr was complete to replace the value in the .lis file? jim From owner-sqr-users@list.iex.net Thu Oct 1 11:24:03 1998 Date: Thu, 1 Oct 1998 11:04:47 CDT From: "C. Willis III" Subject: Re: SQR Tools - SQR Analyzer/Application Engine Analyzer I'd certainly appreciate it!! clark ----Original Message Follows---- This is my SECOND post to this group... (this didn't post the 1st time)! I noticed an interest in SQR Tools... I'm considering posting some I've developed on my website as ShareWare. The source is "leaking" out anyway (as far as Japan) so I may as well post them. Here are some sample tools... ALL written in SQR so there are no additional system requirements. I have plenty more other than those listed below. I just need to know if posting is worthwhile! I'm very busy these days! -Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice tdelia@erols.com ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Thu Oct 1 11:25:17 1998 Date: Thu, 1 Oct 1998 11:59:00 -0400 From: Art DiRamio Subject: Re: Page 1 of xx Gordon - We throw the following 2 lines into our reports to get that very format: Page-Number ( ,114) 'Page: ' Last-Page ( ) ' of ' The output generated is: Page 14 of 27 -- Art > -----Original Message----- > From: Korbel Gordon E. [SMTP:GEKORBEL@NORSTAN.COM] > Sent: Thursday, October 01, 1998 10:10 AM > To: Multiple recipients of list SQR-USERS > Subject: Page 1 of xx > > Hi all, > > Platform: Sybase > SQR Version: 3.0.18.1.1 > > I'm working with Peoplesoft and SQR, and I need some help! > > The Peoplesoft provided invoice contains a page # field -- The users here > have requested that I modify that so it reads, per invoice, Page 1 of xx. > > Is there some trick of which I am unaware, or is this probably going to be > as difficult as I think it will be? > > Thanks in advance! > > -Gordon Korbel From owner-sqr-users@list.iex.net Thu Oct 1 12:58:47 1998 Date: Thu, 1 Oct 1998 10:51:42 -0700 From: Suzanne MacLaughlin Subject: Re: Page 1 of xx There is a relatively easy way to accomplish the page numbering. My client has the same request to have multiple reports in one .lis with each one having 1 of xx (for each invoice). I was able to accomplish this by creating an array create-array name=array size=500 field=lastpg:number and using it for substitution in the original .lis file. The header will contain print 'Page No.' (,+2) print #page-count (,) edit 999 let $lastpage = ' of @@' || to_char(#array_pos) print $lastpage (,+1) which will be the position within the array. The array will have the value that is to be replaced. When the report is complete, the report is closed and the following will replace the array position with the actual number of thru pages. move $sqr-report to $save_report new-report 'junk.lis' open $save_report as 1 for-reading record=190:vary let $output = $save_report || '.new' open $output as 2 for-writing record=190:vary while 1 read 1 into $string:190 if #end-file break end-if if instr($string,'@@',1) unstring $string by '@@' into $page $marker if $marker <> $oldmarker let $lastpage = to_char(array.lastpg(to_number($marker))) move $marker to $oldmarker end-if let $page = $page || $lastpage write 2 from $page else write 2 from $string end-if end-while close 1 close 2 let $cmd = 'rm ' || $save_report Call system using $cmd #status Suzanne MacLaughlin COAD Solutions ---"Korbel Gordon E." wrote: > > This won't work for me because each invoice in the report starts on page '1' > -- that is, in a ten page report, I might have four distinct invoices all of > which need page 1 of xx on the first page of that particular invoice. > > I suspect there is no easy way... > > Thanks for the help! > > -Gordon Korbel > > > -----Original Message----- > > print 'Page ' (4,63) > > page-number (4) > > print ' of ' (4) > > last-page (4) > > > > I hope this helps. > > > --------- > > Hi all, > > Platform: Sybase > > SQR Version: 3.0.18.1.1 > > I'm working with Peoplesoft and SQR, and I need some help! > > The Peoplesoft provided invoice contains a page # field -- The users here > > have requested that I modify that so it reads, per invoice, Page 1 of xx. > > Is there some trick of which I am unaware, or is this probably going to be > > as difficult as I think it will be? > > Thanks in advance! > > -Gordon Korbel > _________________________________________________________ DO YOU YAHOO!? Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Thu Oct 1 12:59:24 1998 Date: Thu, 1 Oct 1998 11:41:34 -0500 From: Nathan Treadway Subject: Incorrect error from sqr-users listserver It seems that the list server for the user-users list is incorrectly sending error messages when people post to the list. The messages say that the posting is rejected because an identical message was already posted. If you received this error message, please do not re-post; your message was actually sent out sucessfully before the error message was generated. Instead, please forward the error message directly to me (nathant@ontko.com) so I can try to find out why this is happening. Thanks. Nathan Treadway sqr-users list manager ---------------------------------------------------------------------------- Nathan Treadway | Ray Ontko & Co. | Software consulting services nathant@ontko.com | Richmond, IN | http://www.ontko.com/ From owner-sqr-users@list.iex.net Thu Oct 1 13:11:32 1998 Date: Thu, 1 Oct 1998 10:53:43 -0700 From: Suzanne MacLaughlin Subject: Page 1 of xx There is a relatively easy way to accomplish the page numbering. My client has the same request to have multiple reports in one .lis with each one having 1 of xx (for each invoice). I was able to accomplish this by creating an array create-array name=array size=500 field=lastpg:number and using it for substitution in the original .lis file. The header will contain print 'Page No.' (,+2) print #page-count (,) edit 999 let $lastpage = ' of @@' || to_char(#array_pos) print $lastpage (,+1) which will be the position within the array. The array will have the value that is to be replaced. When the report is complete, the report is closed and the following will replace the array position with the actual number of thru pages. move $sqr-report to $save_report new-report 'junk.lis' open $save_report as 1 for-reading record=190:vary let $output = $save_report || '.new' open $output as 2 for-writing record=190:vary while 1 read 1 into $string:190 if #end-file break end-if if instr($string,'@@',1) unstring $string by '@@' into $page $marker if $marker <> $oldmarker let $lastpage = to_char(array.lastpg(to_number($marker))) move $marker to $oldmarker end-if let $page = $page || $lastpage write 2 from $page else write 2 from $string end-if end-while close 1 close 2 let $cmd = 'rm ' || $save_report Call system using $cmd #status Suzanne MacLaughlin COAD Solutions _________________________________________________________ DO YOU YAHOO!? Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Thu Oct 1 13:25:46 1998 Date: Thu, 1 Oct 1998 15:10:33 -0300 From: Develop Informatica - Suporte Subject: I got the message error 4747 Hi there I have a report that run in version 2.5 of SQR. I got the message "out of memory" when running SQRT in version 3.0 for the same report. I got the message "The SQT file is corrupted and cannot be processed. SQR abort loading the runtime file." when running SQRT in version 4.3. Any suggestions? Thanks in Advance, Paulo Cesar Prado ----------------------------------------------- Suporte Tecnico - Develop Informatica Rua dos Andradas, 1001/901 Centro Porto Alegre - RS - Brasil CEP 90020-007 Tel/Fax: +55 51 211 2300 http://www.develop.com.br mailto:desenv@develop.com.br - Desenvolvimento mailto:adm@develop.com.br - Administrativo mailto:info@develop.com.br - Comercial mailto:suporte@develop.com.br - Suporte ----------------------------------------------- From owner-sqr-users@list.iex.net Thu Oct 1 13:42:51 1998 Date: Thu, 1 Oct 1998 13:22:54 -0500 From: "Korbel Gordon E." Subject: Re: Page 1 of xx Thanks for the very complete solution!!! -Gordon > -----Original Message----- > > > There is a relatively easy way to accomplish the page numbering. My > client has the same request to have multiple reports in one .lis with > each one having 1 of xx (for each invoice). > > I was able to accomplish this by creating an array > > create-array name=array size=500 > field=lastpg:number > > and using it for substitution in the original .lis file. The header > will contain > > print 'Page No.' (,+2) > print #page-count (,) edit 999 > let $lastpage = ' of @@' || to_char(#array_pos) > print $lastpage (,+1) > > which will be the position within the array. The array will have the > value that is to be replaced. When the report is complete, the report > is closed and the following will replace the array position with the > actual number of thru pages. > > move $sqr-report to $save_report > new-report 'junk.lis' > open $save_report as 1 for-reading record=190:vary > let $output = $save_report || '.new' > open $output as 2 for-writing record=190:vary > while 1 > read 1 into $string:190 > if #end-file > break > end-if > if instr($string,'@@',1) > unstring $string by '@@' into $page $marker > if $marker <> $oldmarker > let $lastpage = to_char(array.lastpg(to_number($marker))) > move $marker to $oldmarker > end-if > let $page = $page || $lastpage > write 2 from $page > else > write 2 from $string > end-if > end-while > close 1 > close 2 > let $cmd = 'rm ' || $save_report > Call system using $cmd #status > > Suzanne MacLaughlin > COAD Solutions > > > > ---"Korbel Gordon E." wrote: > > > > This won't work for me because each invoice in the report starts on > page '1' > > -- that is, in a ten page report, I might have four distinct > invoices all of > > which need page 1 of xx on the first page of that particular invoice. > > > > I suspect there is no easy way... > > > > Thanks for the help! > > > > -Gordon Korbel > > > > > -----Original Message----- > > > print 'Page ' (4,63) > > > page-number (4) > > > print ' of ' (4) > > > last-page (4) > > > > > > I hope this helps. > > > > > --------- > > > Hi all, > > > Platform: Sybase > > > SQR Version: 3.0.18.1.1 > > > I'm working with Peoplesoft and SQR, and I need some help! > > > The Peoplesoft provided invoice contains a page # field -- The > users here > > > have requested that I modify that so it reads, per invoice, Page 1 > of xx. > > > Is there some trick of which I am unaware, or is this probably > going to be > > > as difficult as I think it will be? > > > Thanks in advance! > > > -Gordon Korbel > > > > _________________________________________________________ > DO YOU YAHOO!? > Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Thu Oct 1 13:55:00 1998 Date: Thu, 1 Oct 1998 14:40:19 -0400 From: "Wanko, Christopher G, CFCTRCFFIN" Subject: Re: I got the message error 4747 > I have a report that run in version 2.5 of SQR. > I got the message "out of memory" when running SQRT in > version 3.0 for the > same report. I got the message "The > SQT file is corrupted and cannot be processed. SQR abort > loading the runtime file." when running SQRT in version 4.3. Recompile the SQR in the environment you wish to run it. SQRs must be recompiled between version releases (v2 recompile to work on v3, v3 recompile to work on v4...) -Chris From owner-sqr-users@list.iex.net Thu Oct 1 14:40:31 1998 Date: Thu, 1 Oct 1998 16:18:07 -0300 From: Develop Informatica - Suporte Subject: Re: I got the message error 4747 Yes, I did it. I recompiled the report in releases 3 and 4. At 02:40 PM 10/1/98 -0400, you wrote: >> I have a report that run in version 2.5 of SQR. >> I got the message "out of memory" when running SQRT in >> version 3.0 for the >> same report. I got the message "The >> SQT file is corrupted and cannot be processed. SQR abort >> loading the runtime file." when running SQRT in version 4.3. > >Recompile the SQR in the environment you wish to run it. SQRs must be >recompiled between version releases (v2 recompile to work on v3, v3 >recompile to work on v4...) > >-Chris > > ----------------------------------------------- Suporte Tecnico - Develop Informatica Rua dos Andradas, 1001/901 Centro Porto Alegre - RS - Brasil CEP 90020-007 Tel/Fax: +55 51 211 2300 http://www.develop.com.br mailto:desenv@develop.com.br - Desenvolvimento mailto:adm@develop.com.br - Administrativo mailto:info@develop.com.br - Comercial mailto:suporte@develop.com.br - Suporte ----------------------------------------------- From owner-sqr-users@list.iex.net Thu Oct 1 14:54:42 1998 Date: Thu, 1 Oct 1998 16:33:34 -0300 From: Develop Informatica - Suporte Subject: Re: I got the message error 4747 Yes, I did it. I recompiled the report in releases 3 and 4. At 02:40 PM 10/1/98 -0400, you wrote: >> I have a report that run in version 2.5 of SQR. >> I got the message "out of memory" when running SQRT in >> version 3.0 for the >> same report. I got the message "The >> SQT file is corrupted and cannot be processed. SQR abort >> loading the runtime file." when running SQRT in version 4.3. > >Recompile the SQR in the environment you wish to run it. SQRs must be >recompiled between version releases (v2 recompile to work on v3, v3 >recompile to work on v4...) > >-Chris > > ----------------------------------------------- Suporte Tecnico - Develop Informatica Rua dos Andradas, 1001/901 Centro Porto Alegre - RS - Brasil CEP 90020-007 Tel/Fax: +55 51 211 2300 http://www.develop.com.br mailto:desenv@develop.com.br - Desenvolvimento mailto:adm@develop.com.br - Administrativo mailto:info@develop.com.br - Comercial mailto:suporte@develop.com.br - Suporte ----------------------------------------------- From owner-sqr-users@list.iex.net Thu Oct 1 15:02:51 1998 Date: Thu, 1 Oct 1998 15:48:02 -0400 From: Randy Learn Subject: Commit in DB2/MVS Any help would be greatly appreciated. I am using DB2 version 4 and SQR version 4 on windows NT and MVS mainframe. I have an sqr program that issues a commit after x number of records are processed. When I run it on the NT/Client it runs ok, when I run it on the MVS server it abends after the commit is executed. The commit is actually working, but it seems to blow up on a select after the commit. The program runs ok on MVS with the 'commit' commented out. This is a conversion program that will load a large volume of data and needs to do a commit periodically. Has anyone else run into this??? DSN ENDED DUE TO ERROR+ SYSTEM ABEND CODE 04E REASON CODE 00E7000C PSW 077C100087713180 ASIDS: HOME 1LSCX041 **** ERROR **** ABEND occurred in line 1020 of SQ043(SQ048) ,offset 000390 Program terminated by operating system. ABEND code = S04E A non-C or system routine was running at the time of ABEND From owner-sqr-users@list.iex.net Thu Oct 1 15:30:23 1998 Date: Thu, 1 Oct 1998 16:07:03 -0400 From: "McCoy, Ken D (INPO)" Subject: Re: Commit in DB2/MVS A commit in DB2 will automatically close your open cursor so when you do another fetch of the cursor it will blow up HTH Ken > -----Original Message----- > From: Randy Learn [SMTP:rlearn@UAKRON.EDU] > Sent: Thursday, October 01, 1998 3:48 PM > To: Multiple recipients of list SQR-USERS > Subject: Commit in DB2/MVS > > Any help would be greatly appreciated. > > I am using DB2 version 4 and SQR version 4 on windows NT and MVS > mainframe. > I have an sqr program that issues a commit after x number of records > are > processed. > When I run it on the NT/Client it runs ok, when I run it on the MVS > server it abends after the commit is executed. The commit is actually > working, but it seems to blow up on a select after the commit. The > program runs ok on MVS with the 'commit' commented out. This is a > conversion program that will load a large volume of data and needs to > do > a commit periodically. > > Has anyone else run into this??? > > > DSN ENDED DUE TO ERROR+ > SYSTEM ABEND CODE 04E REASON CODE 00E7000C PSW 077C100087713180 > ASIDS: HOME > 1LSCX041 **** ERROR **** > ABEND occurred in line 1020 of SQ043(SQ048) ,offset > 000390 > > Program terminated by operating system. ABEND code = S04E > A non-C or system routine was running at the time of ABEND From owner-sqr-users@list.iex.net Thu Oct 1 15:55:44 1998 Date: Thu, 1 Oct 1998 13:44:02 PDT From: Parijat Sahai Subject: Re: Commit in DB2/MVS Why don't you post the code in question? Maybe issuing a COMMIT between FETCHes is causing the problem (loss of Cursor handle perhaps - don't know if it makes sense in MVS). Parijat. > >I am using DB2 version 4 and SQR version 4 on windows NT and MVS >mainframe. >I have an sqr program that issues a commit after x number of records are >processed. >When I run it on the NT/Client it runs ok, when I run it on the MVS >server it abends after the commit is executed. The commit is actually >working, but it seems to blow up on a select after the commit. The >program runs ok on MVS with the 'commit' commented out. This is a >conversion program that will load a large volume of data and needs to do >a commit periodically. > >Has anyone else run into this??? > > >DSN ENDED DUE TO ERROR+ > SYSTEM ABEND CODE 04E REASON CODE 00E7000C PSW 077C100087713180 >ASIDS: HOME >1LSCX041 **** ERROR **** > ABEND occurred in line 1020 of SQ043(SQ048) ,offset 000390 > > Program terminated by operating system. ABEND code = S04E > A non-C or system routine was running at the time of ABEND > ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Thu Oct 1 15:59:13 1998 Date: Thu, 1 Oct 1998 14:47:11 -0600 From: Robert Goshko Subject: Re: Commit in DB2/MVS Randy, If you have any cursors open (BEGIN-SELECT, BEGIN-SQL), and you COMMIT, DB@ will close ALL open cursors. So if your in the middle of a select loop like so: BEGIN-SELECT row1 row2 ADD 1 TO #RowCount IF ( #RowCount > 100 ) COMMIT MOVE 0 TO #RowCount END-IF FROM table WHERE row3 =x END-SELECT Soon as you commit, the database no longer has a cursor pointing to where you are, so when you go to process another row, the database won't know what you want and blow your SQR. If you need to COMMIT in the middle of a select loop, you have to save the keys as to where you are (and select date in the order of the keys), get out of the select loop, commit, and then restart your select loop with the row greater than your saved keys (a real pain). ......Rob _________________________________________________ Robert Goshko Owner Axis Developments System Consulting Services Sherwood Park, Alberta, Canada http://www.axis-dev.com/ -----Original Message----- From: Discussion of SQR, SQRIBE Technologies's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Randy Learn Sent: Thursday, October 01, 1998 1:48 PM To: Multiple recipients of list SQR-USERS Subject: Commit in DB2/MVS Any help would be greatly appreciated. I am using DB2 version 4 and SQR version 4 on windows NT and MVS mainframe. I have an sqr program that issues a commit after x number of records are processed. When I run it on the NT/Client it runs ok, when I run it on the MVS server it abends after the commit is executed. The commit is actually working, but it seems to blow up on a select after the commit. The program runs ok on MVS with the 'commit' commented out. This is a conversion program that will load a large volume of data and needs to do a commit periodically. Has anyone else run into this??? DSN ENDED DUE TO ERROR+ SYSTEM ABEND CODE 04E REASON CODE 00E7000C PSW 077C100087713180 ASIDS: HOME 1LSCX041 **** ERROR **** ABEND occurred in line 1020 of SQ043(SQ048) ,offset 000390 Program terminated by operating system. ABEND code = S04E A non-C or system routine was running at the time of ABEND From owner-sqr-users@list.iex.net Thu Oct 1 16:30:59 1998 Date: Thu, 1 Oct 1998 17:25:16 -0400 From: td Subject: Commit Problem not only in DB2 In regard to the COMMIT problem in DB2 where the SELECT cursor is closed when the COMMIT is executed is not limited to DB2. I'm developing a large-scale Oracle process but due to system limitations (like an available work-station, userids, network access, etc) I was forced to begin developing a prototype in SQLBase. I encountered the same problem - Once the Commit is executed any subsequent fetches return an error. Since I was testing a low volume of data (and don't use SQLBase that much) I had the luxury of simply changing my commit count and moving along. Just thought some of the group could use this info for future reference. -Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice From owner-sqr-users@list.iex.net Thu Oct 1 16:34:07 1998 Date: Thu, 1 Oct 1998 17:25:58 -0500 From: David Anderson Subject: UNIX and Log Files I'm trying to recover the LOG of an SQR execution in UNIX. Specifically, when my SQR executes it SHOWs information which is captured by the LOG file (at least in Windows). I cannot find/locate the LOG for an SQR execution from the UNIX command prompt. If the SQR errors during execution, which of course rarely happens, a LOG is created containing the error messages. Help would be GREATLY appreciated... Dave From owner-sqr-users@list.iex.net Thu Oct 1 17:01:13 1998 Date: Thu, 1 Oct 1998 16:48:43 -0500 From: "Schaetzly, Kent" Subject: Re: Commit in DB2/MVS Try the 'with hold' option of declare cursor. DECLARE cursor-name CURSOR WITH HOLD FOR select ... I believe that DB2 will retain cursor positions across comit points when you specify this. Kent Schaetzly The Millennium Project phone: 506-5170 > -----Original Message----- > From: Randy Learn [SMTP:rlearn@UAKRON.EDU] > Sent: Thursday, October 01, 1998 3:48 PM > To: Multiple recipients of list SQR-USERS > Subject: Commit in DB2/MVS > > Any help would be greatly appreciated. > > I am using DB2 version 4 and SQR version 4 on windows NT and MVS > mainframe. > I have an sqr program that issues a commit after x number of records are > processed. > When I run it on the NT/Client it runs ok, when I run it on the MVS > server it abends after the commit is executed. The commit is actually > working, but it seems to blow up on a select after the commit. The > program runs ok on MVS with the 'commit' commented out. This is a > conversion program that will load a large volume of data and needs to do > a commit periodically. > > Has anyone else run into this??? > > > DSN ENDED DUE TO ERROR+ > SYSTEM ABEND CODE 04E REASON CODE 00E7000C PSW 077C100087713180 > ASIDS: HOME > 1LSCX041 **** ERROR **** > ABEND occurred in line 1020 of SQ043(SQ048) ,offset 000390 > > Program terminated by operating system. ABEND code = S04E > A non-C or system routine was running at the time of ABEND From owner-sqr-users@list.iex.net Thu Oct 1 17:05:41 1998 Date: Thu, 1 Oct 1998 14:22:16 -0700 From: John Sayre Subject: Re: Commit in DB2/MVS John Sayre@GAPINC 10/01/98 02:22 PM We do commits inside loops with DB2 regularly. If we don't save the values where we are (let $oldempl=&emplid for example) the cursor is closed and restarts at the top, processing the same rows it just did. Forever. There is a DB2 option 'With Hold' that you issue when you declare the cursors which keeps you at the same row when the cursor is reopened. Please respond to SQR-USERS@USA.NET To: Multiple recipients of list SQR-USERS cc: (bcc: John Sayre/SB/GAPINC) Subject: Commit in DB2/MVS Any help would be greatly appreciated. I am using DB2 version 4 and SQR version 4 on windows NT and MVS mainframe. I have an sqr program that issues a commit after x number of records are processed. When I run it on the NT/Client it runs ok, when I run it on the MVS server it abends after the commit is executed. The commit is actually working, but it seems to blow up on a select after the commit. The program runs ok on MVS with the 'commit' commented out. This is a conversion program that will load a large volume of data and needs to do a commit periodically. Has anyone else run into this??? DSN ENDED DUE TO ERROR+ SYSTEM ABEND CODE 04E REASON CODE 00E7000C PSW 077C100087713180 ASIDS: HOME 1LSCX041 **** ERROR **** ABEND occurred in line 1020 of SQ043(SQ048) ,offset 000390 Program terminated by operating system. ABEND code = S04E A non-C or system routine was running at the time of ABEND From owner-sqr-users@list.iex.net Thu Oct 1 17:07:18 1998 Date: Thu, 1 Oct 1998 18:01:48 -0400 From: td Subject: Re: UNIX and Log Files Dave, Log files are controlled by the -o flag. If you can see the flag setting look there. In Unix it's usually /tmp or /temp. If the flag isn't set then the sqr.log is written in the current directory, i.e. where your SQR program resides. This is the typical configuration at least. -Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice David Anderson wrote: > > I'm trying to recover the LOG of an SQR execution in UNIX. Specifically, > when my SQR executes it SHOWs information which is captured by the > LOG file (at least in Windows). I cannot find/locate the LOG for an SQR > execution from the UNIX command prompt. If the SQR errors during > execution, which of course rarely happens, a LOG is created containing > the error messages. > > Help would be GREATLY appreciated... > Dave From owner-sqr-users@list.iex.net Thu Oct 1 19:25:05 1998 Date: Thu, 1 Oct 1998 18:04:02 -0500 From: Wes Williams Subject: Security violation attempting to access -Reply Are you accessing standard Maximo supplied tables ? Or have you created = some custom apps...tables? Wes Williams Sundstrand Aerospace 815.226.6196 wwilliams@snds.com From owner-sqr-users@list.iex.net Thu Oct 1 19:34:57 1998 Date: Thu, 1 Oct 1998 20:26:21 -0400 From: Tony DeLia Subject: SQR Tools Web-site coming soon... Hello... Due to the positive responses I've received today I will post some very useful SQR Utilities on my web site. I will most likely have it completed before the end of this month - I have some deadlines I need to focus on at the moment. Anyone interested can e-mail me and I'll provide the info when it will be ready (Anyone who e-mailed today will be notified). Here's some of the possibilities... All original work... - SQR Analyzer - Application Engine Analyzer - PS Tree Reporting (even detects children seperated from parents) - PS Tree Loader (Just used for AR Corporate Customer Conversion with Customer# Ranges included) - Dynamic Record Extract - (Can load output into Excel/Access/etc) and supports nested SubRecord Definitions - Array Processing Techniques - Sorting/Binary Search - PS/Oracle Database Audit - COBOL Analyzer - WinBatch Utilities... - I'll maintain a section on Useful (but rarely seen) SQR Techniques... - more, more... You may be wondering why I'm offering this? I've been at many client-sites and as a courtesy I leave my tools and instructions on how to use them (as well as help the SQR programmers directly). My work is "leaking" out anyway so I'd like to at least build my reputation and maybe get some credit for my work! And it doesn't hurt helping someone along the way! :0) -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com From owner-sqr-users@list.iex.net Thu Oct 1 20:46:28 1998 Date: Thu, 1 Oct 1998 21:39:04 -0400 From: "Medeiros, Suzette G." Subject: Re: SQR Tools Web-site coming soon... I would really appreciate any SQR's functions and/or routines you have. Thanking You In Advance, > ---------- > From: Tony DeLia[SMTP:tdelia@EROLS.COM] > Sent: Thursday, October 01, 1998 7:26 PM > To: Multiple recipients of list SQR-USERS > Subject: SQR Tools Web-site coming soon... > > Hello... > > Due to the positive responses I've received today I will post some very > useful SQR Utilities on my web site. I will most likely have it > completed before the end of this month - I have some deadlines I need to > focus on at the moment. Anyone interested can e-mail me and I'll provide > the info when it will be ready (Anyone who e-mailed today will be > notified). Here's some of the possibilities... All original work... > > - SQR Analyzer > - Application Engine Analyzer > - PS Tree Reporting (even detects children seperated from parents) > - PS Tree Loader (Just used for AR Corporate Customer Conversion with > Customer# Ranges included) > - Dynamic Record Extract - (Can load output into Excel/Access/etc) > and supports nested SubRecord Definitions > - Array Processing Techniques - Sorting/Binary Search > - PS/Oracle Database Audit > - COBOL Analyzer > - WinBatch Utilities... > - I'll maintain a section on Useful (but rarely seen) SQR Techniques... > - more, more... > > You may be wondering why I'm offering this? I've been at many > client-sites and as a courtesy I leave my tools and instructions on how > to use them (as well as help the SQR programmers directly). My work is > "leaking" out anyway so I'd like to at least build my reputation and > maybe get some credit for my work! And it doesn't hurt helping someone > along the way! :0) > > > -- > Tony DeLia > AnswerThink Consulting Group > PeopleSoft Solutions Practice - Delphi Partners > tdelia@erols.com > From owner-sqr-users@list.iex.net Thu Oct 1 20:49:49 1998 Date: Thu, 1 Oct 1998 21:42:07 -0400 From: "Medeiros, Suzette G." Subject: Re: UNIX and Log Files David, In our environment the log files reside in $PS_HOME/log/database instance, an example can be $PS_HOME/log/FOD. I hope this helps. > ---------- > From: David Anderson[SMTP:DANDERS1@SMTPGW.IS.HFH.EDU] > Sent: Thursday, October 01, 1998 5:25 PM > To: Multiple recipients of list SQR-USERS > Subject: UNIX and Log Files > > I'm trying to recover the LOG of an SQR execution in UNIX. Specifically, > when my SQR executes it SHOWs information which is captured by the > LOG file (at least in Windows). I cannot find/locate the LOG for an SQR > execution from the UNIX command prompt. If the SQR errors during > execution, which of course rarely happens, a LOG is created containing > the error messages. > > Help would be GREATLY appreciated... > Dave > From owner-sqr-users@list.iex.net Thu Oct 1 21:36:15 1998 Date: Thu, 1 Oct 1998 21:25:14 -0500 From: Don Mellen Subject: Re: Page 1 of xx On Thu, 1 Oct 1998, Korbel Gordon E. wrote: > Thanks for the very complete solution!!! > > -Gordon Another solution is to print two reports. Use "use-report" for the second of your report definitions and print your first PO/WO/Inv etc, then store the current page variable to a variable you use in the header for the "of" part. Then "use-report" the report you defined first and reset the current page variable to 1 and reprint the same thing, then loop and it will "use-report" the second defined one and print the next Inv/PO/WO etc. It allows the "of" printing without having to know where your output is going. Of course, I believe both of these solutions have already been suggested in the past and can be found in the archives at www.sqrug.com. ----------------------------------------------------------------------- Donald Mellen | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/ donm@ontko.com | "In the beginning, there was nothing, which exploded" From owner-sqr-users@list.iex.net Fri Oct 2 00:30:57 1998 Date: Fri, 2 Oct 1998 15:10:45 +1100 From: NGUYEN Ken Subject: Re: SQR Tools Web-site coming soon... Hi Tony, Please include me in your mailout. Please use the following email address ken.n.nguyen@au.pwcglobal.com as I may not be at this cleint site at the end of this month. Cheers Ken Nguyen. Tony DeLia: >Hello... > >Due to the positive responses I've received today I will post some very >useful SQR Utilities on my web site. I will most likely have it >completed before the end of this month - I have some deadlines I need to >focus on at the moment. Anyone interested can e-mail me and I'll provide >the info when it will be ready (Anyone who e-mailed today will be >notified). Here's some of the possibilities... All original work... > >- SQR Analyzer >- Application Engine Analyzer >- PS Tree Reporting (even detects children seperated from parents) >- PS Tree Loader (Just used for AR Corporate Customer Conversion with > Customer# Ranges included) >- Dynamic Record Extract - (Can load output into Excel/Access/etc) > and supports nested SubRecord Definitions >- Array Processing Techniques - Sorting/Binary Search >- PS/Oracle Database Audit >- COBOL Analyzer >- WinBatch Utilities... >- I'll maintain a section on Useful (but rarely seen) SQR Techniques... >- more, more... > >You may be wondering why I'm offering this? I've been at many >client-sites and as a courtesy I leave my tools and instructions on how >to use them (as well as help the SQR programmers directly). My work is >"leaking" out anyway so I'd like to at least build my reputation and >maybe get some credit for my work! And it doesn't hurt helping someone >along the way! :0) > > >-- >Tony DeLia >AnswerThink Consulting Group >PeopleSoft Solutions Practice - Delphi Partners >tdelia@erols.com > From owner-sqr-users@list.iex.net Fri Oct 2 07:32:06 1998 Date: Fri, 2 Oct 1998 08:20:14 -0400 From: Saibaba Subject: Re: SQR Tools Web-site coming soon... Hi Tony, I am very much interested in looking your valuable work and interested to share your knowledge and experience. Any help in this regard is highly appreciated. Thank you in advance. Sai. -----Original Message----- From: Tony DeLia To: Multiple recipients of list SQR-USERS Date: October 1, 1998 8:44 PM Subject: SQR Tools Web-site coming soon... >Hello... > >Due to the positive responses I've received today I will post some very >useful SQR Utilities on my web site. I will most likely have it >completed before the end of this month - I have some deadlines I need to >focus on at the moment. Anyone interested can e-mail me and I'll provide >the info when it will be ready (Anyone who e-mailed today will be >notified). Here's some of the possibilities... All original work... > >- SQR Analyzer >- Application Engine Analyzer >- PS Tree Reporting (even detects children seperated from parents) >- PS Tree Loader (Just used for AR Corporate Customer Conversion with > Customer# Ranges included) >- Dynamic Record Extract - (Can load output into Excel/Access/etc) > and supports nested SubRecord Definitions >- Array Processing Techniques - Sorting/Binary Search >- PS/Oracle Database Audit >- COBOL Analyzer >- WinBatch Utilities... >- I'll maintain a section on Useful (but rarely seen) SQR Techniques... >- more, more... > >You may be wondering why I'm offering this? I've been at many >client-sites and as a courtesy I leave my tools and instructions on how >to use them (as well as help the SQR programmers directly). My work is >"leaking" out anyway so I'd like to at least build my reputation and >maybe get some credit for my work! And it doesn't hurt helping someone >along the way! :0) > > >-- >Tony DeLia >AnswerThink Consulting Group >PeopleSoft Solutions Practice - Delphi Partners >tdelia@erols.com From owner-sqr-users@list.iex.net Fri Oct 2 07:34:01 1998 Date: Fri, 2 Oct 1998 08:23:57 -0400 From: Saibaba Subject: Re: SQR Tools Web-site coming soon... Please include me in your mail list and my e-mail Id is: bsaibaba@algoma.com and saibalan1@hotmail.com Thanks in advance. Sai. -----Original Message----- From: Tony DeLia To: Multiple recipients of list SQR-USERS Date: October 1, 1998 8:44 PM Subject: SQR Tools Web-site coming soon... >Hello... > >Due to the positive responses I've received today I will post some very >useful SQR Utilities on my web site. I will most likely have it >completed before the end of this month - I have some deadlines I need to >focus on at the moment. Anyone interested can e-mail me and I'll provide >the info when it will be ready (Anyone who e-mailed today will be >notified). Here's some of the possibilities... All original work... > >- SQR Analyzer >- Application Engine Analyzer >- PS Tree Reporting (even detects children seperated from parents) >- PS Tree Loader (Just used for AR Corporate Customer Conversion with > Customer# Ranges included) >- Dynamic Record Extract - (Can load output into Excel/Access/etc) > and supports nested SubRecord Definitions >- Array Processing Techniques - Sorting/Binary Search >- PS/Oracle Database Audit >- COBOL Analyzer >- WinBatch Utilities... >- I'll maintain a section on Useful (but rarely seen) SQR Techniques... >- more, more... > >You may be wondering why I'm offering this? I've been at many >client-sites and as a courtesy I leave my tools and instructions on how >to use them (as well as help the SQR programmers directly). My work is >"leaking" out anyway so I'd like to at least build my reputation and >maybe get some credit for my work! And it doesn't hurt helping someone >along the way! :0) > > >-- >Tony DeLia >AnswerThink Consulting Group >PeopleSoft Solutions Practice - Delphi Partners >tdelia@erols.com From owner-sqr-users@list.iex.net Fri Oct 2 07:44:53 1998 Date: Fri, 2 Oct 1998 08:35:35 -0500 From: Darwin Anderson Subject: Sort on calculated field Sybase 11.0.2.1 SQR 3.0.13.2 How can I sort in descending order on a field that is created by a calculation during processing? I have existing fields for regular hours and overtime hours from which I calclulate the percent of OT to Regular. When the report is printed, I want the highest percentage to be on the first line. I suspect that I need to use a bind variable in some way. TIA Darwin dander03@avis.com 516-222-3064 From owner-sqr-users@list.iex.net Fri Oct 2 07:58:25 1998 Date: Fri, 2 Oct 1998 08:50:53 -0500 From: David Anderson Subject: Re: UNIX and Log Files -Reply I have tried using the -O but to no avail. Specifically, the system reutrns an error indicating the -O is an illegal command. Further research seems to indicate the -O is for Windows only, at least with release 3 of SQR. Thanks for your suggestion, but still looking for the answer... Dave >>> td Oct 1 5:01pm >>> Dave, Log files are controlled by the -o flag. If you can see the flag setting look there. In Unix it's usually /tmp or /temp. If the flag isn't set then the sqr.log is written in the current directory, i.e. where your SQR program resides. This is the typical configuration at least. -Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice David Anderson wrote: > > I'm trying to recover the LOG of an SQR execution in UNIX. Specifically, > when my SQR executes it SHOWs information which is captured by the > LOG file (at least in Windows). I cannot find/locate the LOG for an SQR > execution from the UNIX command prompt. If the SQR errors during > execution, which of course rarely happens, a LOG is created containing > the error messages. > > Help would be GREATLY appreciated... > Dave From owner-sqr-users@list.iex.net Fri Oct 2 08:04:59 1998 Date: Fri, 2 Oct 1998 08:55:33 -0400 From: Charu Srinivas Subject: Re: UNIX and Log Files Dave- When you are running the sqr on the Unix prompt just use the redirect sign(>>) to redirect the output to the desired file in the desired directory. You can use : sqr filename.sqr login/password -f directory name >> directoryname/filename Hope this helps. Charu DANDERS1@SMTPGW.IS.HFH.EDU on 10/01/98 06:25:58 PM Please respond to SQR-USERS@USA.NET To: SQR-USERS@list.iex.net cc: (bcc: C. Srinivas/dsisys/US) Subject: UNIX and Log Files I'm trying to recover the LOG of an SQR execution in UNIX. Specifically, when my SQR executes it SHOWs information which is captured by the LOG file (at least in Windows). I cannot find/locate the LOG for an SQR execution from the UNIX command prompt. If the SQR errors during execution, which of course rarely happens, a LOG is created containing the error messages. Help would be GREATLY appreciated... Dave From owner-sqr-users@list.iex.net Fri Oct 2 08:08:37 1998 Date: Fri, 2 Oct 1998 08:59:08 -0400 From: Charu Srinivas Subject: Re: Unix and log files Dave- When you are running the sqr on the Unix prompt just use the redirect sign(>>) to redirect the output to the desired file in the desired directory. You can use : sqr filename.sqr login/password -f directory name >> directoryname/filename Hope this helps. Charu From owner-sqr-users@list.iex.net Fri Oct 2 08:10:34 1998 Date: Fri, 2 Oct 1998 06:54:43 -0500 From: Gopal Nandakumar Subject: Re: SQR TOOLS WEB-SITE IS WORKING NOW www.tcssoft.com Hi All, Thank you for those who responded to my previous email. Already some of you might have received our free demo software. The purpose of this email is to let you all to know that our the demo software could be requested through our web side www.tcssoft.com. Following SQR Tools are available: 1. SQR Expander: * Expands SQRs and produces Expanded & Compiler listing files 2. SQR Shrink * Shrinks the Expanded file saving the changes made in the Expanded file into individual files 3. SQR Interactive Debugger * We believe ours is the first real Interactive Debugger for SQR Lets the user to stop execution temporarily at any logical break point, view and modify any Global or Local variable, view the source code as they are executed, stop the execution at any logical break point after executing a user defined routine, creates a Trace file which has all the necessary information to debug the SQR quickly and efficiently. 4. SQR Multi Cross Reference * Creates Multi Cross Reference information both through reports and on-line. The Multi Cross Reference information is very valuable to both System Analysts and Programmers to get the job done quickly, efficiently with cosiderably less cost. It also keeps track of execution information of every SQR added to the system like start date & time, end date & time, completion code, platform and database used. Our SQR tools are neither script files nor simple procedures that could be input into other SQRs. Out SQR tools are full blown commercial software that will increase productivity considerably. They are written in SQR. To get more information visit our web side www.tcssoft.com Thanks. Gopal TCS INC. (210) 491 0046 (USA) From owner-sqr-users@list.iex.net Fri Oct 2 08:22:45 1998 Date: Fri, 2 Oct 1998 09:09:47 -0400 From: Charu Srinivas Subject: Re: UNIX and Log Files -Reply Dave- When you are running the sqr on the Unix prompt just use the redirect sign(>>) to redirect the output to the desired file in the desired directory. You can use : sqr filename.sqr login/password -f directory name >> directoryname/filename Hope this helps. Charu DANDERS1@SMTPGW.IS.HFH.EDU on 10/02/98 09:50:53 AM Please respond to SQR-USERS@USA.NET To: SQR-USERS@list.iex.net cc: (bcc: C. Srinivas/dsisys/US) Subject: Re: UNIX and Log Files -Reply I have tried using the -O but to no avail. Specifically, the system reutrns an error indicating the -O is an illegal command. Further research seems to indicate the -O is for Windows only, at least with release 3 of SQR. Thanks for your suggestion, but still looking for the answer... Dave >>> td Oct 1 5:01pm >>> Dave, Log files are controlled by the -o flag. If you can see the flag setting look there. In Unix it's usually /tmp or /temp. If the flag isn't set then the sqr.log is written in the current directory, i.e. where your SQR program resides. This is the typical configuration at least. -Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice David Anderson wrote: > > I'm trying to recover the LOG of an SQR execution in UNIX. Specifically, > when my SQR executes it SHOWs information which is captured by the > LOG file (at least in Windows). I cannot find/locate the LOG for an SQR > execution from the UNIX command prompt. If the SQR errors during > execution, which of course rarely happens, a LOG is created containing > the error messages. > > Help would be GREATLY appreciated... > Dave From owner-sqr-users@list.iex.net Fri Oct 2 09:25:11 1998 Date: Fri, 2 Oct 1998 10:05:03 -0400 From: GINO SANTERRE Subject: Re[2]: How to print a report in a window --0__=kGiS0JlmolebtSVJGRFu2PKIED3N472nErWqpePcAt26HDdFEdZGrsxJ Content-type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-transfer-encoding: quoted-printable Pour : GINO SANTERRE/QUEBEC-TEL/CA cc : Objet : Re[2]: How to print a report in a window Your solution work great to send the report on screen. Everybody h= ere are very interested by this new feature. Only one problem still not resolve. It's the fact than we can send the report directly to the printer(with the option Printer). SQRW try to open the printer like it = was a file and an error message appear. Do you know a solution to append the parameter -ZIV only when the destination is file or window and skip it when we choose printer? Thanks a lot for your help. Gino Santerre Le Groupe Qu=E9bectel = --0__=kGiS0JlmolebtSVJGRFu2PKIED3N472nErWqpePcAt26HDdFEdZGrsxJ Content-type: text/plain; charset=us-ascii Content-Disposition: inline You want to append the -ZIV to your SQR Flags, not the Command Line. ______________________________ Reply Separator _________________________________ Subject: Re: How to print a report in a window Author: "GINO SANTERRE" at internet Date: 10/1/98 11:58 AM I try your solution but i got this message: --0__=kGiS0JlmolebtSVJGRFu2PKIED3N472nErWqpePcAt26HDdFEdZGrsxJ-- From owner-sqr-users@list.iex.net Fri Oct 2 09:32:50 1998 Date: Fri, 2 Oct 1998 10:13:37 -0400 From: "Singh, Rajvirendra ( NYSDS )" Subject: Re: SQR Tools Web-site coming soon... Please also include me in any mailings: rvsingh@hotmail.com Thanks!!! * Rajvirendra Singh -----Original Message----- From: Korbel Gordon E. [SMTP:GEKORBEL@NORSTAN.COM] Sent: Friday, October 02, 1998 9:02 AM To: Multiple recipients of list SQR-USERS Subject: Re: SQR Tools Web-site coming soon... Please also include me in any mailings: gkorbel@winternet.com Thanks!!! -Gordon > -----Original Message----- > > Hello... > > Due to the positive responses I've received today I will post some very > useful SQR Utilities on my web site. I will most likely have it > completed before the end of this month - I have some deadlines I need to > focus on at the moment. Anyone interested can e-mail me and I'll provide > the info when it will be ready (Anyone who e-mailed today will be > notified). Here's some of the possibilities... All original work... > > - SQR Analyzer > - Application Engine Analyzer > - PS Tree Reporting (even detects children seperated from parents) > - PS Tree Loader (Just used for AR Corporate Customer Conversion with > Customer# Ranges included) > - Dynamic Record Extract - (Can load output into Excel/Access/etc) > and supports nested SubRecord Definitions > - Array Processing Techniques - Sorting/Binary Search > - PS/Oracle Database Audit > - COBOL Analyzer > - WinBatch Utilities... > - I'll maintain a section on Useful (but rarely seen) SQR Techniques... > - more, more... > > You may be wondering why I'm offering this? I've been at many > client-sites and as a courtesy I leave my tools and instructions on how > to use them (as well as help the SQR programmers directly). My work is > "leaking" out anyway so I'd like to at least build my reputation and > maybe get some credit for my work! And it doesn't hurt helping someone > along the way! :0) > > > -- > Tony DeLia > AnswerThink Consulting Group > PeopleSoft Solutions Practice - Delphi Partners > tdelia@erols.com From owner-sqr-users@list.iex.net Fri Oct 2 09:48:00 1998 Date: Fri, 2 Oct 1998 09:33:49 -0500 From: "Doris K.Brinson" Subject: Sort on calculated field -Reply I have to admit not knowing much about "bind" variables, but a work around that you can use is to write the information you need (including the calculated field) into a temporary file. Then you can select the data in any order you want from that file. It's not the most efficient solution, but it should work for you. Doris. >>> Darwin Anderson 10/02/98 08:35am >>> Sybase 11.0.2.1 SQR 3.0.13.2 How can I sort in descending order on a field that is created by a calculation during processing? I have existing fields for regular hours and overtime hours from which I calclulate the percent of OT to Regular. When the report is printed, I want the highest percentage to be on the first line. I suspect that I need to use a bind variable in some way. TIA Darwin dander03@avis.com 516-222-3064 From owner-sqr-users@list.iex.net Fri Oct 2 09:53:56 1998 Date: Fri, 2 Oct 1998 09:29:40 -0500 From: David Anderson Subject: Re: Unix and log files -Reply The -F flag/argument is not at issue. I am having difficulty retrieving the LOG file formed during successful execution of the SQR. My programming has a number of SHOW commands that should populate the LOG file. However, upon successful completion of the SQR I have an LIS but no LOG. If the program encounters a syntax/programming error an LOG file is created containing the SQR error messages (ie, &varname not defined). Still looking for the right answer... Dave >>> Charu Srinivas Oct 2 7:59am >>> Dave- When you are running the sqr on the Unix prompt just use the redirect sign(>>) to redirect the output to the desired file in the desired directory. You can use : sqr filename.sqr login/password -f directory name >> directoryname/filename Hope this helps. Charu From owner-sqr-users@list.iex.net Fri Oct 2 09:55:11 1998 Date: Fri, 2 Oct 1998 10:39:32 -0400 From: Charu Srinivas Subject: Re: Unix and log files -Reply Dave- The -f will only produce the .lis file but the >> (redirect) sign will create the .log file and send it to the directory you want. Again the syntax will be sqr filename.sqr >> directoryname/filename.log Charu DANDERS1@SMTPGW.IS.HFH.EDU on 10/02/98 10:29:40 AM Please respond to SQR-USERS@USA.NET To: SQR-USERS@list.iex.net cc: (bcc: C. Srinivas/dsisys/US) Subject: Re: Unix and log files -Reply The -F flag/argument is not at issue. I am having difficulty retrieving the LOG file formed during successful execution of the SQR. My programming has a number of SHOW commands that should populate the LOG file. However, upon successful completion of the SQR I have an LIS but no LOG. If the program encounters a syntax/programming error an LOG file is created containing the SQR error messages (ie, &varname not defined). Still looking for the right answer... Dave >>> Charu Srinivas Oct 2 7:59am >>> Dave- When you are running the sqr on the Unix prompt just use the redirect sign(>>) to redirect the output to the desired file in the desired directory. You can use : sqr filename.sqr login/password -f directory name >> directoryname/filename Hope this helps. Charu From owner-sqr-users@list.iex.net Fri Oct 2 09:57:42 1998 Date: Fri, 2 Oct 1998 07:06:20 -0700 From: "Kodali, Venkat" Subject: Re: SQR Tools Web-site coming soon... Please include me in your mail list , my e-mail id is venkatkodali@allianfs.com Venkat Kodali 847 405 8914 -----Original Message----- From: Tony DeLia [SMTP:tdelia@EROLS.COM] Sent: Thursday, October 01, 1998 7:26 PM To: Multiple recipients of list SQR-USERS Subject: SQR Tools Web-site coming soon... Hello... Due to the positive responses I've received today I will post some very useful SQR Utilities on my web site. I will most likely have it completed before the end of this month - I have some deadlines I need to focus on at the moment. Anyone interested can e-mail me and I'll provide the info when it will be ready (Anyone who e-mailed today will be notified). Here's some of the possibilities... All original work... - SQR Analyzer - Application Engine Analyzer - PS Tree Reporting (even detects children seperated from parents) - PS Tree Loader (Just used for AR Corporate Customer Conversion with Customer# Ranges included) - Dynamic Record Extract - (Can load output into Excel/Access/etc) and supports nested SubRecord Definitions - Array Processing Techniques - Sorting/Binary Search - PS/Oracle Database Audit - COBOL Analyzer - WinBatch Utilities... - I'll maintain a section on Useful (but rarely seen) SQR Techniques... - more, more... You may be wondering why I'm offering this? I've been at many client-sites and as a courtesy I leave my tools and instructions on how to use them (as well as help the SQR programmers directly). My work is "leaking" out anyway so I'd like to at least build my reputation and maybe get some credit for my work! And it doesn't hurt helping someone along the way! :0) -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com From owner-sqr-users@list.iex.net Fri Oct 2 10:03:38 1998 Date: Fri, 2 Oct 1998 10:53:21 -0400 From: Charu Srinivas Subject: Re: Unix and log files The -f will only produce the .lis file but the >> (redirect) sign will create the .log file and send it to the directory you want. You have to type the following at the Unix prompt sqr filename.sqr >> directoryname/filename.log Charu DANDERS1@SMTPGW.IS.HFH.EDU on 10/02/98 10:29:40 AM Please respond to SQR-USERS@USA.NET To: SQR-USERS@list.iex.net cc: (bcc: C. Srinivas/dsisys/US) Subject: Re: Unix and log files -Reply The -F flag/argument is not at issue. I am having difficulty retrieving the LOG file formed during successful execution of the SQR. My programming has a number of SHOW commands that should populate the LOG file. However, upon successful completion of the SQR I have an LIS but no LOG. If the program encounters a syntax/programming error an LOG file is created containing the SQR error messages (ie, &varname not defined). Still looking for the right answer... Dave >>> Charu Srinivas Oct 2 7:59am >>> Dave- When you are running the sqr on the Unix prompt just use the redirect sign(>>) to redirect the output to the desired file in the desired directory. You can use : sqr filename.sqr login/password -f directory name >> directoryname/filename Hope this helps. Charu From owner-sqr-users@list.iex.net Fri Oct 2 10:04:27 1998 Date: Fri, 2 Oct 1998 10:55:07 -0400 From: Charu Srinivas Subject: Re: SQR Tools Web-site coming soon... Please include me in your mailing list. My email address is: csrinivas@dsisys.com Thanks!! Charu From owner-sqr-users@list.iex.net Fri Oct 2 10:47:13 1998 Date: Fri, 2 Oct 1998 10:21:23 -0500 From: Richard_Tomancik Subject: Re: Sort on calculated field Oracle 7.3.3.2 SQRW 3.0.18.1.1 If you select it , you can do an 'order by' on it. See the following: REG_HRS OT_HRS (OT_HRS/REG_HRS) --------- --------- ---------------- 10 10 1 -10 -10 1 -20 -10 .5 20 10 .5 20 10 .5 -20 -10 .5 40 0 0 80 0 0 54.67 0 0 9 rows selected. SQL> l 1 select reg_hrs, ot_hrs, (ot_hrs / reg_hrs) 2 from ps_pay_earnings 3 where reg_hrs <> 0 4* order by (ot_hrs / reg_hrs) desc Richard Tomancik richard_tomancik@tiginsurance.com Darwin Anderson on 10/02/98 08:35:35 AM Please respond to SQR-USERS@USA.NET To: Multiple recipients of list SQR-USERS cc: (bcc: Richard Tomancik/Consultant/Dallas/TIG) Subject: Sort on calculated field Sybase 11.0.2.1 SQR 3.0.13.2 How can I sort in descending order on a field that is created by a calculation during processing? I have existing fields for regular hours and overtime hours from which I calclulate the percent of OT to Regular. When the report is printed, I want the highest percentage to be on the first line. I suspect that I need to use a bind variable in some way. TIA Darwin dander03@avis.com 516-222-3064 From owner-sqr-users@list.iex.net Fri Oct 2 10:49:05 1998 Date: Fri, 2 Oct 1998 10:32:19 -0500 From: "Isaacs, Timothy J. CER" Subject: Re: Security violation attempting to access -Reply Yes, they are accessing a new table they created. Timothy Isaacs USACERL UL-I CMMS Team 800 USACERL (800 872-2375) P.O. Box 9005 217 373-4468 direct Champaign, IL 61826 217 373-3490 fax http://www.cecer.army.mil "If you think your life is bad, try being a Cub fan" TJI > -----Original Message----- > From: Wes Williams [SMTP:wwilliams@MAIL.RKD.SNDS.COM] > Sent: Thursday, October 01, 1998 6:04 PM > To: Multiple recipients of list SQR-USERS > Subject: Security violation attempting to access -Reply > > Are you accessing standard Maximo supplied tables ? Or have you > created some custom apps...tables? > Wes Williams > Sundstrand Aerospace > 815.226.6196 > wwilliams@snds.com From owner-sqr-users@list.iex.net Fri Oct 2 10:50:33 1998 Date: Fri, 2 Oct 1998 09:49:44 -0500 From: David Anderson Subject: UNIX and LOG Files I have opened the script used to execute an SQR. There was not a -O flag set in the line SQRFLAGS so I added one. The old code was: SQRFLAGS="-f$HOME/output/$filename.lis -i$PS_USQR/;$PS_SQR/ -e$HOME/output/$filename.log" The revised code became: SQRFLAGS="-f$HOME/output/$filename.lis -i$PS_USQR/;$PS_SQR/ -e$HOME/output/$filename.log -o$HOME/output/$filename.log" When I tried executing the script I encountered: Unknown flag on command line: -o/home/gacker1/output/garncnv.log Hope this additional information is helpful... Dave From owner-sqr-users@list.iex.net Fri Oct 2 10:56:22 1998 Date: Fri, 2 Oct 1998 09:18:30 -0600 From: Renee Klund Subject: Re: SQR Tools Web-site coming soon... I would be interested=21 Thanx, Ren=E9e Klund (rklund=40waterpik.com) = = = = = = = = =20 From owner-sqr-users@list.iex.net Fri Oct 2 11:21:55 1998 Date: Fri, 2 Oct 1998 10:25:05 -0500 From: Mike Fulce Subject: "Me Too" syndrome I'm sure that most of you are aware that when you respond to a message delivered by this listserver, that everyone in the list also receives the message. Mr DeLia's postings to offer a website with useful information are certainly appropriate and appreciated. However, I would like to suggest that all of the personal "Me Too" responses be sent directly to Mr. Delia's e-mail address: tdelia@EROLS.COM instead of this listserver. That way, Mr. Delia gets your message but the rest of the list members don't have to see it and we can keep the content of this listserver specific to subjects about SQR. I know I'll probably get flamed for this, but if you do, please respond directly to my e-mail address m.fulce@usm.edu instead of replying to the list. Thanks ______________________________________________________________________________ Mike Fulce Database Administrator I OTR-Admin. Resources University of Southern Mississippi m.fulce@usm.edu Voice:(601) 266-6102 Fax:(601) 266-6881 207 Forrest County Hall Box 5171, Hattiesburg, MS 309406 From owner-sqr-users@list.iex.net Fri Oct 2 11:30:19 1998 Date: Fri, 2 Oct 1998 08:02:20 -0500 From: "Korbel Gordon E." Subject: Re: SQR Tools Web-site coming soon... Please also include me in any mailings: gkorbel@winternet.com Thanks!!! -Gordon > -----Original Message----- > > Hello... > > Due to the positive responses I've received today I will post some very > useful SQR Utilities on my web site. I will most likely have it > completed before the end of this month - I have some deadlines I need to > focus on at the moment. Anyone interested can e-mail me and I'll provide > the info when it will be ready (Anyone who e-mailed today will be > notified). Here's some of the possibilities... All original work... > > - SQR Analyzer > - Application Engine Analyzer > - PS Tree Reporting (even detects children seperated from parents) > - PS Tree Loader (Just used for AR Corporate Customer Conversion with > Customer# Ranges included) > - Dynamic Record Extract - (Can load output into Excel/Access/etc) > and supports nested SubRecord Definitions > - Array Processing Techniques - Sorting/Binary Search > - PS/Oracle Database Audit > - COBOL Analyzer > - WinBatch Utilities... > - I'll maintain a section on Useful (but rarely seen) SQR Techniques... > - more, more... > > You may be wondering why I'm offering this? I've been at many > client-sites and as a courtesy I leave my tools and instructions on how > to use them (as well as help the SQR programmers directly). My work is > "leaking" out anyway so I'd like to at least build my reputation and > maybe get some credit for my work! And it doesn't hurt helping someone > along the way! :0) > > > -- > Tony DeLia > AnswerThink Consulting Group > PeopleSoft Solutions Practice - Delphi Partners > tdelia@erols.com From owner-sqr-users@list.iex.net Fri Oct 2 11:34:31 1998 Date: Fri, 2 Oct 1998 11:03:16 -0400 From: Charu Srinivas Subject: Re: Unix and log files This is my third reply. The first two were rejected for posting. The -f will only produce the .lis file but the >> (redirect) sign will create the .log file and send it to the directory you want. You have to type the following at the Unix prompt sqr filename.sqr >> directoryname/filename.log Charu DANDERS1@SMTPGW.IS.HFH.EDU on 10/02/98 10:29:40 AM Please respond to SQR-USERS@USA.NET To: SQR-USERS@list.iex.net cc: (bcc: C. Srinivas/dsisys/US) Subject: Re: Unix and log files -Reply The -F flag/argument is not at issue. I am having difficulty retrieving the LOG file formed during successful execution of the SQR. My programming has a number of SHOW commands that should populate the LOG file. However, upon successful completion of the SQR I have an LIS but no LOG. If the program encounters a syntax/programming error an LOG file is created containing the SQR error messages (ie, &varname not defined). Still looking for the right answer... Dave >>> Charu Srinivas Oct 2 7:59am >>> Dave- When you are running the sqr on the Unix prompt just use the redirect sign(>>) to redirect the output to the desired file in the desired directory. You can use : sqr filename.sqr login/password -f directory name >> directoryname/filename Hope this helps. Charu From owner-sqr-users@list.iex.net Fri Oct 2 11:38:35 1998 Date: Fri, 2 Oct 1998 10:36:31 EDT From: Ken Look Subject: Re: Sort on calculated field Darwin, you need to order by field position of calculation desc. If your calculated field is in position 3 of your select statement, then your order by would look like this: Order by 3 desc Ken Look Look Consulting 773-267-5089 From owner-sqr-users@list.iex.net Fri Oct 2 11:53:12 1998 Date: Fri, 2 Oct 1998 12:22:47 EDT From: John Paulson Subject: Re: SQR Tools Web-site coming soon... Tony, Please include me in your mail list!!! Thanks! John email - paulsonj@aol.com From owner-sqr-users@list.iex.net Fri Oct 2 12:14:40 1998 Date: Fri, 2 Oct 1998 13:09:16 -0400 From: Tony DeLia Subject: Sample SQR Code - Sorting Arrays Hello... Since there seems to be some interest in my SQR Tools Site I thought I'd post a small sample. Here is some "snippets" of an Array Sorting Algorithm. I've also included my Assembler Version (Snippets) because I've included comments and a brief narrative of the sorting process within the program. Also, maybe there are some old timers out there learning SQR! The SQR Example sorts an array with a primary key (ARRkey), a secondary key (ARRsec) and has the data stored in ARRrec (I used 1 field for illustrative purposes). SORTptr is the main pointer- SCANptr is the pointer for comparison. Hope nobody minds me posting to the site. -Tony DeLia !********************************************************************** !* * !* MODULE: SORT EXAMPLE. * !* AUTHOR: TONY DELIA. * !* DATE: 10/02/1998. * !* SYSTEM: TD SQR UTILITY SERIES. * !* DESC: SORTING ARRAYS. * !* * !********************************************************************** .. .. do Define-Array do Load-Array do Sort-Array .. .. !********************************************************************** !* Define Array * !********************************************************************** begin-procedure Define-Array create-array name=ARRdat size=2000 field=ARRrec:char - field=ARRkey:char - field=ARRsec:char let #ARRmax = 2000 let #ARRctr = 0 end-procedure !********************************************************************** !* Load Array * !********************************************************************** begin-procedure Load-Array let #ARRctr = 0 while 1 = 1 read #mmc-i-no into $rec:200 if #end-file = 1 break end-if . . put $rec $skey $ssec into ARRdat (#ARRctr) ARRrec ARRkey ARRsec let #ARRctr = #ARRctr + 1 end-while end-procedure !********************************************************************** !* Sort Array * !********************************************************************** begin-procedure Sort-Array let #ARRmax = #ARRctr let #SORTptr = 0 while #SORTptr < #ARRmax let #SCANptr = #SORTptr + 1 get $SORTrec $SORTkey $SORTsec - from ARRdat (#SORTptr) ARRrec ARRkey ARRsec while #SCANptr < #ARRmax get $SCANrec $SCANkey $SCANsec - from ARRdat (#SCANptr) ARRrec ARRkey ARRsec if ($SORTkey > $SCANkey) or ($SORTkey = $SCANkey and $SORTsec > $SCANsec) put $SORTrec $SORTkey $SORTsec - into ARRdat (#SCANptr) ARRrec ARRkey ARRsec put $SCANrec $SCANkey $SCANsec - into ARRdat (#SORTptr) ARRrec ARRkey ARRsec let $SORTrec = $SCANrec let $SORTkey = $SCANkey let $SORTsec = $SCANsec end-if let #SCANptr = #SCANptr + 1 end-while let #SORTptr = #SORTptr + 1 end-while end-procedure !********************************************************************** Here's the Assembler Version (see comments for description of the above process). TITLE 'TDSRT - ASSEMBLER SORT TABLE // TONY DELIA' *********************************************************************** * * * MODULE: TDSRT. * * AUTHOR: TONY DELIA. * * DATE: 06/28/90. * * DESC: SORT TABLE OF ELEMENTS. * * * *********************************************************************** . . *********************************************************************** * SORT TABLE * *********************************************************************** DC F'0' RETURN ADDRESS SAVE AREA SORT EQU * ST 6,*-4 SAVE RETURN ADDRESS * LH 5,SCTR LOAD ENTRY COUNT CH 5,=H'1' ONLY 1 ENTRY ??? BE SORTX YES - EXIT SORT ROUTINE * BCTR 5,0 DECREMENT ENTRY COUNT BY 1 MH 5,=Y(SLEN) MULTIPLY BY ENTRY LENGTH LA 5,STABLE(5) POINT TO LAST ENTRY ST 5,SLAST SAVE LAST ENTRY ADDRESS * LA 4,STABLE INIT LO PTR - 1ST ENTRY LA 5,SLEN(,4) INIT HI PTR - 2ND ENTRY SORTIT EQU * CLC 0(SLEN,4),0(5) IS LO ENTRY > HI ENTRY ?? BNH BUMPHI NO - BUMP HI PTR XC 0(SLEN,4),0(5) YES - ISOLATE UNIQUE BITS XC 0(SLEN,5),0(4) REPLACE LO WITH HI ENTRY XC 0(SLEN,4),0(5) REPLACE HI WITH LO ENTRY BUMPHI EQU * LA 5,SLEN(,5) BUMP HI POINTER C 5,SLAST HI POINTER PAST LIMIT ?? BNH SORTIT NO - COMPARE AGAIN BUMPLO EQU * LA 4,SLEN(,4) YES - BUMP LO POINTER LA 5,SLEN(,4) RESET HI PTR = LO + 1 C 4,SLAST LO POINTER PAST LIMIT ?? BL SORTIT NO - KEEP SORTING SORTX EQU * L 6,SORT-4 RESTORE LINK REGISTER BR 6 BRANCH ON LINK REGISTER *********************************************************************** * * * BUBBLE SORT LOGIC: (LOW VALUES RISE LIKE BUBBLES TO TOP). * * * * INITIALLY, LO PTR (4) POINTS TO ENTRY 1 AND HI PTR (5) AT 2. * * LO PTR WILL REMAIN THE SAME UNTIL HI PTR REACHES THE END OF * * TABLE. EACH TIME HI PTR MOVES ALONG A COMPARE IS MADE WITH * * LO PTR AND ELEMENTS MAY BE SWAPPED. THIS FORCES THE LOWEST * * VALUE TO THE BEGINNING OF THE TABLE. THEN LO PTR IS BUMPED * * TO THE NEXT ENTRY AND HI PTR IS RESET TO LO PTR + 1 ENTRY. * * PROCESS IS THEN REPEATED FORCING THE NEXT LO VALUE TO 2ND * * TABLE POSITION. THIS WILL REPEAT ITSELF UNTIL LO PTR HAS * * REACHED THE LAST TABLE POSITION. NOTE THAT EACH TIME LO PTR * * IS BUMPED, THE PRECEDING ELEMENTS ARE IN SORTED ORDER AND * * ARE NO LONGER INCLUDED IN THE SORT PROCESS (TABLE SHRINKS). * * * *********************************************************************** EJECT *********************************************************************** * TABLE * *********************************************************************** SLEN EQU 2 TABLE ENTRY LENGTH SLAST DC F'0' LAST ENTRY ADDRESS SCTR DC H'0' NUMBER OF TABLE ENTRIES STABLE DC 100CL2' ' 2-DIGIT TABLE STABLEX DC X'FF' END-OF-TABLE MARKER *********************************************************************** END TDSRT -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com From owner-sqr-users@list.iex.net Fri Oct 2 15:53:19 1998 Date: Fri, 2 Oct 1998 15:32:23 -0500 From: Mike Fulce Subject: PeopleSoft SQR Development Question Maybe this is more for DBA's (specifically ORACLE) than developers. (However, I'm not looking for philosophy, just practicality) I'd just like to hear how some of your shops manage the SQR development environment with PeopleSoft, ie: Do you allow developer's to run SQR's outside of PeopleSoft as user sysadm? If not, then how do you handle table reference coding in SQR's? Do you create synonymns for all of the PS tables or do you force the developers to code the schema name? What privileges do you give your developer's in the development database? Full or limited? If limited, please specify. Do you allow custom SQR's to build/drop temporary tables dynamically or do they use truncate on existing tables that the DBA created. I would like to have a loose development environment by giving SQR developers privileges to be able to connect to the development database outside of PeopleSoft to do queries or to run SQR's directly and to be able to do some DDL. However, since we are new to PeopleSoft and Oracle/SQR, our developer's are inexperienced. So I really don't want them connecting with the privileges of user sysadm outside of PeopleSoft. I'd appreciate any responses. ______________________________________________________________________________ Mike Fulce __ Oracle Database Administrator I OTR-Admin. Resources ___/ /__ University of Southern Mississippi m.fulce@usm.edu /__ ___/ Voice:(601) 266-6102 Fax:(601) 266-6881 207 Forrest County Hall / / Box 5171, Hattiesburg, MS 39406 / / http://ocean.otr.usm.edu/~rmfulce ^^ From owner-sqr-users@list.iex.net Fri Oct 2 17:48:50 1998 Date: Fri, 2 Oct 1998 17:00:03 -0400 From: "Hope, Sandra" Subject: Re: PeopleSoft SQR Development Question Sandra Hope DynCorp hopes@dyncorp.com Office: (703) 715-4428 RESYSTEMIZATION PeopleSoft > ---------- > From: Mike Fulce[SMTP:m.fulce@USM.EDU] > Reply To: SQR-USERS@USA.NET > Sent: Friday, October 02, 1998 4:32 PM > To: Multiple recipients of list SQR-USERS > Subject: PeopleSoft SQR Development Question > > Maybe this is more for DBA's (specifically ORACLE) than developers. > (However, I'm not looking for philosophy, just practicality) I'm a developer not a DBA! > I'd just like to hear how some of your shops manage the SQR development > environment with PeopleSoft, ie: > > Do you allow developer's to run SQR's outside of PeopleSoft as user > sysadm? Typically, developers are (should be) allowed to run SQR's outside of PS since this allows more flexibility. > If not, then how do you handle table reference coding in SQR's? Do you > create synonymns for all of the PS tables or do you force the developers > to > code the schema name? > > What privileges do you give your developer's in the development database? > Full or limited? If limited, please specify. Some companies allow full access and sometimes get into trouble if the developers aren't sure of what they are doing; other companies provide minimal to all and then piece-meal additional access to others as-needed. > Do you allow custom SQR's to build/drop temporary tables dynamically or do > they use truncate on existing tables that the DBA created. If build/drops occur on a regular basis, the database will become fragmented and cumbersome. Truncate allows data to be dropped but tablespace to remain. > I would like to have a loose development environment by giving SQR > developers privileges to be able to connect to the development database > outside of PeopleSoft to do queries or to run SQR's directly and to be > able > to do some DDL. However, since we are new to PeopleSoft and Oracle/SQR, > our developer's are inexperienced. So I really don't want them connecting > with the privileges of user sysadm outside of PeopleSoft. > If your developers are inexperienced, limit their access; they can unknowingly create problems. You can limit access to specific tables (not sure if this applies to commands like 'delete'). > I'd appreciate any responses. > __________________________________________________________________________ > ____ > Mike Fulce __ Oracle Database Administrator I > OTR-Admin. Resources ___/ /__ University of Southern Mississippi > m.fulce@usm.edu /__ ___/ Voice:(601) 266-6102 Fax:(601) 266-6881 > 207 Forrest County Hall / / Box 5171, Hattiesburg, MS 39406 > / / http://ocean.otr.usm.edu/~rmfulce > ^^ > From owner-sqr-users@list.iex.net Fri Oct 2 21:29:56 1998 Date: Fri, 2 Oct 1998 15:56:58 -0700 From: Michael Lee Subject: Re: PeopleSoft SQR Development Question Mike, These are very good questions. However, the PS-Users mailing list may be a better place to discuss this (You can subscribe at http://www.ps-users.com). I say that because lately, this list has been flooded with lots of mail. Anyways, everyone pretty much has a different methodology when it comes to DB access by developers. So, I must announce that this is just my opinion. In general, the developers should have loose access when it comes to DB security, Tools access, etc. Having a version control/source control system also helps out in the development environment. If you have a tight, monitored development area, you will be pushing more responsibility to your PeopleSoft Security Admin as well as the DBA(s). In my opinion, when the development area is NOT a "loose" environment, then the project tends to go very slow because you will have to funnel your requests through to the proper people. Your system integration/user acceptance TEST database should be a locked environment. And, your production environment should of course be locked. You may want your tech lead and your PeopleSoft security administrator to have tools/SQR (outside of PS)/sysadm access. All other developers should have full access to the development area. The demo database is usually open to all. I would suggest refreshing this every 2 weeks or every month. Make sure that when you have a minor upgrade, to refresh the database, then perform the upgrade, and then back up the database again... i.e. when going from 6.00 to 6.01, refresh the DB with 6.00, then upgrade to 6.01, then backup the 6.01 database. PeopleSoft doesn't ship a demo DB for minor upgrade releases, and you want to have a clean DB when upgrading the demo database. If you have any other questions, feel free to contact me at my client site (M-Th): 312.653.5026. Michael K. Lee mlee@agconsult.com Consultant AG Consulting, an ADP Company http://www.agconsult.com Mike Fulce on 10/02/98 01:32:23 PM Please respond to SQR-USERS@USA.NET To: Multiple recipients of list SQR-USERS cc: (bcc: Michael Lee/Application Group) Subject: PeopleSoft SQR Development Question Maybe this is more for DBA's (specifically ORACLE) than developers. (However, I'm not looking for philosophy, just practicality) I'd just like to hear how some of your shops manage the SQR development environment with PeopleSoft, ie: Do you allow developer's to run SQR's outside of PeopleSoft as user sysadm? If not, then how do you handle table reference coding in SQR's? Do you create synonymns for all of the PS tables or do you force the developers to code the schema name? What privileges do you give your developer's in the development database? Full or limited? If limited, please specify. Do you allow custom SQR's to build/drop temporary tables dynamically or do they use truncate on existing tables that the DBA created. I would like to have a loose development environment by giving SQR developers privileges to be able to connect to the development database outside of PeopleSoft to do queries or to run SQR's directly and to be able to do some DDL. However, since we are new to PeopleSoft and Oracle/SQR, our developer's are inexperienced. So I really don't want them connecting with the privileges of user sysadm outside of PeopleSoft. I'd appreciate any responses. ___________________________________________________________________________ ___ Mike Fulce __ Oracle Database Administrator I OTR-Admin. Resources ___/ /__ University of Southern Mississippi m.fulce@usm.edu /__ ___/ Voice:(601) 266-6102 Fax:(601) 266-6881 207 Forrest County Hall / / Box 5171, Hattiesburg, MS 39406 / / http://ocean.otr.usm.edu/~rmfulce ^^ From owner-sqr-users@list.iex.net Sat Oct 3 02:51:13 1998 Date: Sat, 3 Oct 1998 00:21:05 -0700 From: Albert Howard Subject: If I may be so bold.. I'd like to add two suggestions to our gracious host's tips for using this mailing list, if I may: 1. Several people have posted multiple replys thinking that their messages didn't make it. This is because either they didn't see their own post, or saw the several rejections that result from all the dead addresses on the posting. Two commands sent to the list server will guarantee you will know your message posted: SET SQR-USERS REP and SET SQR-USERS ACK The first one tells the list server to send your postings back to you. The second one tells the list server to acknowledge your posting. Send these commands to listserv@usa.net. And remember, the stuff you get that says "rejected" etc..is not usually your message rejecting from the list..its the postmaster at one of the receiving mailboxes of ONE poster on the list. 2. If you are going to turn on an Autoresponder that says "I am out of the office" for vacation, you might want to unsubscribe to the list while you are gone so you don't send 100 of those autoresponses out per day. Hope you find this helpful... From owner-sqr-users@list.iex.net Sun Oct 4 09:34:55 1998 Date: Sun, 4 Oct 1998 10:09:06 EDT From: Venugopala R vatti Subject: Re: SQR Tools Web-site coming soon... In a message dated 98-10-01 20:23:41 EDT, you write: << tdelia@EROLS.COM >> I am interested in your tools. please include me in you mailing list. Email : Vgpreddy@yahoo.com or venuv999@aol.com From owner-sqr-users@list.iex.net Mon Oct 5 08:59:50 1998 Date: Mon, 5 Oct 1998 09:27:48 -0400 From: Jim Hardesty Subject: PeopleSoft SQR Development Question -Reply Mike, I'm sure you will get many responses advocating strong control of the databases. And there are many good reasons to have strong control. But let me give another side. We are a small PeopleSoft HRMS shop that has been live with PeopleSoft for almost 3 years and we have been through 3 release upgrades. We have only two PeopleSoft developers and PeopleSoft shares one DBA with Oracle Financials, a completely custom Sales pay system and a completely custom A/R system. That is one DBA with about 15-20 databases on 4 machines. So you can imagine how thinly our DBA resource is stretched. Both of the PeopleSoft developers know the sysadm password in all of the test and production databases. The developers build, drop and alter tables and indexes in both test and production. The developers run sqr, sql, etc as sysadm. Obviously, sqr has the access and ability to dynamically create and drop tables. But we don't do it. Only because PeopleSoft doesn't. We tend to model our SQR after PoepleSoft's and they always use an existing temporary table. One thing I would change would be to truncate the table at the end of the sqr instead of leaving a bunch of temporary data in a table. Maybe it doesn't matter, but it seems messy. The reason we allow developers to have the sysadm password is pure practicality. The DBA simply doesn't have the time or expertise to add any value to the process. We can show the DBA our alter scripts and he will hem and haw and whine about the inefficiency. But when it comes right down to it, the things he wants to change are the very things that PeopleSoft does on purpose. The DBA has a tendency to do much more harm than good when he looks at the PeopleSoft scripts. Are there risks? Of course. The developers can make mistakes. Production tables can be dropped. Does it happen? Yes. We once accidently dropped the production security tables. That's a bad thing, things got pretty exciting that day. But guess what, DBAs make mistakes too. I can guarantee that if I gave a script to the DBA that dropped the production security tables, and told him to run it, he would. Because he doesn't understand our tables and he would have no clue. Where is the control and what value is added? And that is what tends to happen when you control access. The people who are responsible for the changes don't have the access to make them. The people with the access don't have the same intimate understanding of what they are changing and aren't responsible for the changes. You end up with expertise and responsibility in one group, access and control in another. What value is added if I stand over someone's shoulder and tell them "ok, go to sqlplus and run psalter.sql" simply because they have access and I don't? Honestly, we should probably have more control, primarily in production. But if we did, less work would get done and more mistakes would be made. The changes need to be made. The big question you have to answer is who is best able to make the changes, not whose job description should give them control of the ability to make changes. On the question of synonyms: You've hit the nail on the head. How do developers access the tables if they aren't sysadm? You can't force them to code the schema name. You might have different schema names in different databases. Also, sqr should be written platform independent, let alone schema independent. You could create public synonyms for all of the tables. But then the developers need access to the tables, so you grant access to the tables. What kind of access? Well, they need read. Ooops, they need update. Ooops, they need delete. On which tables? PS_%. Ooops, they need some system PS% (no underscore). Ooops, they need more. Shoot. They need everything, or just about. So you create scripts that grant this access and create these synonyms. And these scripts need to be run every time tables are altered. And you probably created a role with this access and granted the role to the developers. So, when all is said and done, the developers can now select, update and delete from every table owned by sysadm. That is a lot of work, virtually for nothing. Ok, so they developers can't create or drop tables. Big deal. Once someone can write "truncate ps_job;" then you should probably trust them a little. Note: all of this only makes sense in a small shop with limited, trusted resources. You wouldn't be able to do this if you had 20 developers and 3 DBA's. I'm not sure what you'd do, but it would take more resources to control than we have total. Just my two cents, I hope everyone isn't adding my name to the list of resume's to be thrown away immediately upon receipt... jim >>> Mike Fulce 10/02/98 04:32pm >>> Maybe this is more for DBA's (specifically ORACLE) than developers. (However, I'm not looking for philosophy, just practicality) I'd just like to hear how some of your shops manage the SQR development environment with PeopleSoft, ie: Do you allow developer's to run SQR's outside of PeopleSoft as user sysadm? If not, then how do you handle table reference coding in SQR's? Do you create synonymns for all of the PS tables or do you force the developers to code the schema name? What privileges do you give your developer's in the development database? Full or limited? If limited, please specify. Do you allow custom SQR's to build/drop temporary tables dynamically or do they use truncate on existing tables that the DBA created. I would like to have a loose development environment by giving SQR developers privileges to be able to connect to the development database outside of PeopleSoft to do queries or to run SQR's directly and to be able to do some DDL. However, since we are new to PeopleSoft and Oracle/SQR, our developer's are inexperienced. So I really don't want them connecting with the privileges of user sysadm outside of PeopleSoft. I'd appreciate any responses. ______________________________________________________________________________ Mike Fulce __ Oracle Database Administrator I OTR-Admin. Resources ___/ /__ University of Southern Mississippi m.fulce@usm.edu /__ ___/ Voice:(601) 266-6102 Fax:(601) 266-6881 207 Forrest County Hall / / Box 5171, Hattiesburg, MS 39406 / / http://ocean.otr.usm.edu/~rmfulce ^^ From owner-sqr-users@list.iex.net Mon Oct 5 10:19:44 1998 Date: Mon, 5 Oct 1998 11:11:25 -0400 From: *Sehat Rosny Subject: Recursive function in SQRW Hello Have any one of you ever implement recursive procedure in SQRW ? Or is it possible to do recursive in SQRW? (the global variable of SQRW made recursive hard to implement) thank you in advance. any comment is welcome bye Sehat Rosny PeopleSoft and SQRW Developer From owner-sqr-users@list.iex.net Mon Oct 5 10:20:58 1998 Date: Mon, 5 Oct 1998 10:58:29 -0400 From: Cynthia Jones Subject: Job Announcement - PeopleSoft Contracts (Atlanta, San Diego, Chicago) I have a client with the following IMMEDIATE contract needs: 1) = PeopleSoft Financials Technical SQR Programmer. Locale: San Diego. = Requires: SQR, 2+ yrs. exp. w/PeopleSoft (v7), Billing, Costing and/or = Pricing exp. 2) PeopleSoft HRMS Technical Consultant. Locale: Chicago. = Requires: 2+ yrs. PeopleSoft exp., SQR, PeopleCode, PeopleTools. 3) = PeopleSoft Financials Technical Consultant. Locale: Atlanta. Requires: = SQR, PeopleCOde, PeopleTools. Interested? Contact me ASAP!!=20 Cynthia Jones Leader Institute www.peoplestaff.com 800.683.6690 x111=09 From owner-sqr-users@list.iex.net Mon Oct 5 10:49:38 1998 Date: Mon, 5 Oct 1998 11:27:20 -0400 From: Eric Dimick Eastman Subject: Re: Recursive function in SQRW Sehat, The short answer is that it can't be done. "Local" variables and parameters in SQR are not not like local variables in most languages. There is only only copy per invocation of the program, as opposed to being kept in a call stack. So recursive calls in SQR are little more than elaborate looping construct. The good news is that anything that can be done with recursive procedures can be done without them. To remove the recursion from an algorithm you basically create your own array to emulate a call stack. I have done this on a couple on occasions. E-mail me if you are interested in the gory details. Hope This helps, (or at least that you aren't too disappointed) Eric *Sehat Rosny wrote: > > Hello > > Have any one of you ever > implement recursive procedure in SQRW ? > Or is it possible to do recursive in SQRW? > (the global variable of SQRW made recursive > hard to implement) > > thank you in advance. > any comment is welcome > > bye > Sehat Rosny > PeopleSoft and SQRW Developer From owner-sqr-users@list.iex.net Mon Oct 5 11:03:30 1998 Date: Mon, 5 Oct 1998 11:40:45 -0400 From: Ranade Sameer Subject: Re: Recursive function in SQRW Hi, I never needed to implement recursive functions for any practical purposes in SQR, but it's possible to implement. I don't think global variables should cause any problems in general. Here is sample example: begin-report do test1 ! This is called recursively later. let #count=0 end-report begin-procedure test1 let #count=#count+1 if #count > 12 ! test condition to come out of SQR STOP else display 'Now in test1' display #count do test1 ! Call same procedure end-if end-procedure Above is very simple example, but it can be coded for complex tasks too. Hope it helps, -Sameer -----Original Message----- From: *Sehat Rosny [mailto:SRosny@SMTPGW.ARMS.OHIO-STATE.EDU] Sent: Monday, October 05, 1998 11:11 AM To: Multiple recipients of list SQR-USERS Subject: Recursive function in SQRW Hello Have any one of you ever implement recursive procedure in SQRW ? Or is it possible to do recursive in SQRW? (the global variable of SQRW made recursive hard to implement) thank you in advance. any comment is welcome bye Sehat Rosny PeopleSoft and SQRW Developer From owner-sqr-users@list.iex.net Mon Oct 5 13:55:41 1998 Date: Mon, 5 Oct 1998 13:31:36 -0500 From: Nathan Treadway Subject: Re: Recursive function in SQRW > Have any one of you ever > implement recursive procedure in SQRW ? > Or is it possible to do recursive in SQRW? > (the global variable of SQRW made recursive > hard to implement) Yes, it is possible, but it's definitely a pain. Go to http://www.sqrug.com/~sqrusers/search/ and search for the word "factorial" for a response to this question posted a few months ago, complete with a sample program. (Searching for "recursive" will also give you the answer, but not quite so quickly.) Nathan ---------------------------------------------------------------------------- Nathan Treadway | Ray Ontko & Co. | Software consulting services nathant@ontko.com | Richmond, IN | http://www.ontko.com/ From owner-sqr-users@list.iex.net Mon Oct 5 14:05:14 1998 Date: Mon, 5 Oct 1998 14:34:30 -0400 From: "rethier(contr-ird)" Subject: IsDate Function Hi! I am current using SQR version 4.0. Is there a IsDate() function that can validate the user input as a validate date data entry? Or have any one of you developed a subroutine that can validate a valid date data entry? and this function is independent of the database server. Thank you in advance! Rachel From owner-sqr-users@list.iex.net Mon Oct 5 14:33:03 1998 Date: Mon, 5 Oct 1998 14:13:47 CDT From: "C. Willis III" Subject: Re: IsDate Function Rachel, for validating user input as a date in sqr, see page 156 of your language reference manual; use type = date. clark Hi! I am current using SQR version 4.0. Is there a IsDate() function that can validate the user input as a validate date data entry? Or have any one of you developed a subroutine that can validate a valid date data entry? and this function is independent of the database server. Thank you in advance! Rachel ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Mon Oct 5 15:00:14 1998 Date: Mon, 5 Oct 1998 15:33:59 -0400 From: "rethier(contr-ird)" Subject: Re: IsDate Function Clark, Thanks for your response, but unfortunately I don't get the user input via terminal, I get inputs via reading from a temp parameter storing table. Rachel -----Original Message----- From: C. Willis III [mailto:ceprn@HOTMAIL.COM] Sent: Monday, October 05, 1998 3:14 PM To: Multiple recipients of list SQR-USERS Subject: Re: IsDate Function Rachel, for validating user input as a date in sqr, see page 156 of your language reference manual; use type = date. clark Hi! I am current using SQR version 4.0. Is there a IsDate() function that can validate the user input as a validate date data entry? Or have any one of you developed a subroutine that can validate a valid date data entry? and this function is independent of the database server. Thank you in advance! Rachel ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Mon Oct 5 15:17:02 1998 Date: Mon, 5 Oct 1998 14:31:01 -0500 From: Don Mellen Subject: Re: IsDate Function Alas, this will not work if you want users to be able to "enter" past that input for a predetermined default. If you use the "status=" option it will allow blanks but again not verify a good date. If you don't care about defaulting the date input, type=date works great and will display nice error messages when it is not a proper date/format and re-prompt. On Mon, 5 Oct 1998, C. Willis III wrote: > Rachel, > > for validating user input as a date in sqr, see page 156 of your > language reference manual; use type = date. > clark > > Hi! > I am current using SQR version 4.0. Is there a IsDate() function that > can validate the user input as a validate date data entry? Or have any > one of you developed a subroutine that can validate a valid date data > entry? and this function is independent of the database server. > Thank you in advance! > > Rachel > > > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com > ----------------------------------------------------------------------- Donald Mellen | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/ donm@ontko.com | "In the beginning, there was nothing, which exploded" From owner-sqr-users@list.iex.net Mon Oct 5 19:36:16 1998 Date: Tue, 6 Oct 1998 08:21:06 +0800 From: Wayne Ivory Subject: Re: PeopleSoft SQR Development Question -Reply Jim, Congratulations on one of the most well-written emails I=27ve ever seen. = The fact that I happen to agree with you is by-the-by - it was well set = out, comprehensive, yet easy to read. I work for a mining company in Western Australia. We have 400 employees = and an IS department of 14 people. We are in the process of implementing = PeopleSoft HRMS and have 2 dedicated programmers and a project manager, = and share the single DBA with the rest of the department. We programmers = have the sysadm password for our 6 environments (including production) and = do all the things your programmers do (ie sqls, drop, alter). Our DBA is = very competent, but extremely busy, and like yours doesn=27t have an = intimate understanding of PeopleSoft. Yes, mistakes can happen in production, but that=27s why God invented = backups, and I think it actually strengthens your character. In fact = I=27ve devised a maxim I=27ll call Wayne=27s Law for want of a better name = (note: contains obscentity - skip next paragraph if you think you=27ll be = offended): =22Good programmers fuck up really badly once=21=22 Whether it be =22rm *=22 or =22drop=22 or any one of the other potentially = catastrophic commands at our disposal, my experience has been that once = this happens to someone it rattles them to the point that they sort of = develop a sixth sense about these things. I can think of about 10 people = (including myself) that fall into this category. Of course Jim=27s and my approach won=27t suit every site, but Mike (the = original poster) be assured it is a worthwhile consideration. If you have = competent professional people working on your project then you can afford = to trust them with this sort of responsibility. Wayne Ivory Information Services Westralian Sands Limited >>> Jim Hardesty 5/10/98 9:27:48 pm >>> Mike, I=27m sure you will get many responses advocating strong control of the = databases. And there are many good reasons to have strong control. But let me give another side. ___________________________________________________________________________= ___ Mike Fulce __ Oracle Database Administrator I OTR-Admin. Resources ___/ /__ University of Southern Mississippi m.fulce=40usm.edu /__ ___/ Voice:(601) 266-6102 Fax:(601) = 266-6881 207 Forrest County Hall / / Box 5171, Hattiesburg, MS 39406 / / http://ocean.otr.usm.edu/=7Ermfulce=20 =5E=5E From owner-sqr-users@list.iex.net Tue Oct 6 06:52:59 1998 Date: Tue, 6 Oct 1998 06:43:59 -0500 From: Gopal Nandakumar Subject: Re: Recursive function in SQRW Hello, I have used recursive procedure in SQRW which works like in any other language. The only problem I found is that when the recursive procedure is called within the procedure again and again for more than a certain number of times the program abends with a general protection failure. In other words somewhere the memory gets corrupt. So I could not use it successfully calling for more than 300 times. Thanks.