From owner-sqr-users@list.iex.net Mon Jul 1 00:22:01 2002 Date: Mon, 1 Jul 2002 00:00:02 -0500 From: Nathan Stratton Treadway Subject: Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Stratton Treadway, Ray Ontko & Co. (nathanst@ontko.com) $Revision: 1.12 $ $Date: 2002-04-17 18:14:01-04 $ This document gathers in one location information about the sqr-users mail list. Directions for common list-server commands (including unsubscribing) are given, as are guidelines for list usage. ______________________________________________________________________ Table of Contents 1. Changes Since Previous Version 2. Introduction 2.1 sqr-users Mailing List 2.2 sqr-users Hints and Guidelines Document 2.3 Related Mailing Lists 3. Basic List Server Commands 3.1 Subscribing to the List 3.2 Unsubscribing from the List 3.3 Digests 3.4 Problems 4. Posting to the sqr-users List 4.1 General Guidelines 4.2 Posting a Message 4.3 Replying to a Message 5. Job Announcements ______________________________________________________________________ 1. Changes Since Previous Version Changes since the version dated 2001/06/17: o Mention SQR for PeopleSoft in the ``Posting to the sqr-users list'' section. o Add additional mailing lists in the ``Related Mailing Lists'' section. 2. Introduction 2.1. sqr-users Mailing List The sqr-users mailing list was created to enable discussion related to the SQR database language from Brio Technology (and to other products in the Brio Report line). Note: SQR used to be published by SQRIBE Technologies, which was merged into Brio Technology on August 3, 1999. 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 . 2.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/sqr-users/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 Copyright (C) 1999-2002 by Ray Ontko & Co. It may be freely copied and distributed provided it is not modified in any way and it retains the original copyright notice. 2.3. Related Mailing Lists sqr-jobs : SQR Jobs This list may be used for posting SQR-related resumes, recruitments, job announcements, project announcments, help- wanted, work-wanted ads, etc. To join, follow the link above, or send any message to "sqr-jobs-subscribe@yahoogroups.com". peoplesoft-fans : PeopleSoft Fans Moderated list covering technical and functional aspects of PeopleSoft. To join, follow the link above, or send any message to "peoplesoft-fans-subscribe@yahoogroups.com". psusers : PeopleSoft Users For questions related to any aspect of PeopleSoft (including PeopleSoft-specific SQR issues). This list has merged with the Peoplesoft Fans mailing list and is no longer active, but the message achive can still be found at the link above. BrioSQRReport : Brio SQR Report List for discussion of Brio Report (SQR) and Brio ReportBuilder. To join, follow the link above, or send any message to "BrioSQRReport-subscribe@yahoogroups.com". rm-users : ReportMart Users List for discussion of Brio Portal (previously called ReportMart). To join, follow the link above, or send a message containing the word "subscribe" in the body to "rm-users- request@sqrug.com". brioportalusersgroup : Brio Portal Users Group list List for discussion of Brio Portal. To join, follow the link above, or send any message to "brioportalusersgroup- subscribe@yahoogroups.com". brio-l : OpenITx Brio List List for technical and functional discussion of all Brio products (but most messages are currently about Brio Intelligence). To join, follow the link above. briolist : Brio List List for discussion of Brio Intelligence. To join, follow the link above, or send any message to "briolist- subscribe@yahoogroups.com". 3. Basic List Server Commands Note: The list used to be hosted on usa.net , but the list-hosting service was moved to list.iex.net . The old addresses for the list, list server, and list owner no longer work. A few basic list server commands are listed below. Send these commands as the only line in the body (not the subject line) of an e- mail message sent to "listserv@list.iex.net". 3.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. 3.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. 3.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. 3.4. Problems If the above commands do not work, please contact the list manager at "sqr-users-request@list.iex.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. 4. Posting to the sqr-users List In order to post to the mailing list, you must be subscribed to it (see directions above). 4.1. General Guidelines For any message you send to the list, please: o make sure your mail software is not requesting a "Return Receipt". (If a "Return Receipt Requested" message is sent to the list, several dozen receipt messages will be sent out to the mailing list in reply from other subscribers.) o do not send messages in HTML or other special formats. (These are not usable by many subscribers, especially those who receive their messages in digest format.) o keep in mind that over 500 people will receive your message, and that it will appear in the list archives. Try to keep your messages on-topic and relatively short. Consider sending your message directly to particular people if it won't interest many of the list's subscribers. 4.2. Posting a Message Once you are subscribed, you can post to the list by sending a message to "sqr-users@list.iex.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, please include the following information: o Brio product and version number (see notes below) 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 or proprietary code, since the posting will be placed in a public web archive.) Note that your question should relate to SQR or other Brio Report product in some way. Don't ask questions about PeopleSoft, for example, unless you are having SQR-related issues. (For PeopleSoft- specific questions, see other lists mentioned in the ``Related Mailing Lists'' section.) In 2001, PeopleSoft purchased from Brio the rights to the source code for SQR and began releasing its own line of SQR. PeopleSoft's SQR version numbering is different that Brio's, so it's very important to mention whether you are using Brio's "SQR" or "SQR for PeopleSoft". (This info is included automatically if you include the full SQR version-id string as described below.) To get the version-id string for your copy 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. In a command-line environment (Unix, etc.), you can also get this version-id string by saying sqr -id 4.3. 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 cases 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 original 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. (Remember that some people read the mailing list in digest form, and the digests become much larger if unnecessary lines are quoted.) 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. 5. Job Announcements Please do not post job announcements on the sqr-users list, but use the sqr-jobs list instead. (See ``Related Mailing Lists'' for more information on this list.) A large number of sqr-users subscribers do not want to receive job announcements (for various reasons) and such posts tend to cause a lot of disruption of the discussion on the list. From owner-sqr-users@list.iex.net Mon Jul 1 13:06:02 2002 Date: Mon, 1 Jul 2002 13:46:44 -0400 From: Brian Fitzpatrick Subject: Use of bind variables I am looking into a possibility for optimizing an SQR report which is running against an Oracle 8 database. The report is run lets say 500 times initiated from a UNIX shell script - each time a parameter for a location is passed to the report, and the report query is identical except for a location number. I have been building the query in a fashion such as this: . . let $where_loc = 'where location = ''' $param ''' begin-select last_name &lname first_name &fname from employee [$where_loc] As I understand it, because of the WHERE clause, the query is physically different each time and will be reparsed by the query optimizer each time the report is run. HOWEVER, I am told that if the query were to use a bind variable like below, it would only be parsed once: begin-select last_name &lname first_name &fname from employee where location = :loc The question is: can this be done, and if so, how do I supply a value to the bind variable :loc ? I've tried a begin-sql / end-sql block prior to the report query, but the bind variable only exists for the duration of the PL/SQL block. Does this approach make sense for optimization, is this workable, will it buy us much of a savings? Are there other areas I should be looking at (indexing perhaps)? Any input is appreciated, thanks. From owner-sqr-users@list.iex.net Mon Jul 1 13:05:59 2002 Date: Mon, 1 Jul 2002 23:17:18 +0530 From: Harish_Venkiteswaran Subject: Problems with dynamic sql Hi, Im trying a dynamic sql proc in Oracle.The code is given below.It accepts the following parameters Sourcetable Keyfields Longfield create or replace procedure extractdata (source in varchar2,keyfield in varchar2,longfield in long) IS TYPE cur_typ IS REF CURSOR; get_rec_curr cur_typ; get_rec_prev cur_typ; long_curr LONG; long_prev LONG; len_long_curr number(10); len_long_prev number(10); status number(1); val VARCHAR2(32767); sqlstr varchar(1000); BEGIN OPEN get_rec_curr FOR 'SELECT ' || keyfield||','||longfield ||' FROM ' || source ;<<>> LOOP FETCH get_rec_curr INTO VAL; EXIT WHEN get_rec_curr%NOTFOUND; dbms_output.put_line(val); END LOOP; CLOSE get_rec_curr; END; I want to extract long field and do some manipulation.I have the following problems.When I execute the procedure I get the following error ORA-00932: inconsistent datatypes ORA-06512: at "SYSADM.COMPARELONG", line 21 ORA-06512: at line 2 I know that the problem is because keyfield is of type varchar and longfield has a long column.Is there a solution for the problem. Thanks & Regards Harish ************************************************************************** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ************************************************************************** From owner-sqr-users@list.iex.net Mon Jul 1 17:41:13 2002 Date: Mon, 1 Jul 2002 15:26:40 -0700 From: Bill Smith Subject: Trouble using EXECUTE statement to run Oracle function I've got an SQR program that reads preventative maintenance records (PMs) in a Maximo database and generates workorders for any PMs that are due. The outer logic of the program selects the PMs that are due. Within this select block are several procedures that update several other tables in the database as needed for each particular workorder. One of these procedures calls the SQR Execute statement to run an Oracle stored function. The final step in the process is to write the new workorder itself. The only COMMIT statement in the code is after the workorder is successfully written - all other table updates are dependant upon the workorder getting written, so if any step fails, all other processing for the current workorder must be rolled back. There is no transaction processing in the Oracle procedure. Boring tech stuff: SQR Version is ODBC 4.3.4, running against Oracle 8i. Platform is Win 2000 and an HPUX database server. What's failing: When the compiled version of this program is run from our production job scheduler or a DOS prompt on my development PC, the program fails on an ODBC login failure. The message is "(SQR 1803) CONNECT failed. Perhaps username/password incorrect." I've loaded the program with #debug show statements to create a log, and found that the login failure is occurring the first time the program attempts the EXECUTE statement. The DOS command line that I'm using is: C:\SQRIBE\SQRServer\ODBC\ODB\BINW\sqrwt y:\SQR43~1.4\NGSPMG~1\NGSWOGEN.sqt DSN=xxxxxxx;UID=xxxxxxx;PWD=xxxxxxx -XCB -E -Oy:\SQR43~1.4\NGSPMG~1\NGSWogen.log -XMB -Fy:\SQR43~1.4\NGSPMG~1\NGSWogen.lis What's working: If I load the source for this program into Visual Sqribe and execute it using the preview window, it runs successfully. If I run the compiled version using the "SQR Execute" utility (sqrwtj.exe), it runs successfully. What I've tried: I've verified that the DSN name, uid and password are all correct in the DOS command line as well as the ODBC connect strings within the program. I've tried changing the DOS 8.3 representations of the path to the full folder names in the DOS batch file. I've tried adding a separate COMMIT statement in the Oracle function and setting it to an autonomous transaction so that it's transaction processing wouldn't affect the calling program. I've tried beating my head against my desk. None of these attempts have had any effect except for the head thing, which hurt like heck. Any suggestions for where to go next would be much appreciated... From owner-sqr-users@list.iex.net Tue Jul 2 07:08:34 2002 Date: Tue, 2 Jul 2002 08:00:37 -0400 From: George Jansen Subject: Re: Trouble using EXECUTE statement to run Oracle function a. Is it possible that the effective path is different at your command prompt (odbc needs %0RACLE_HOME%\BIN on the path)? b. Shouldn't you be using a BEGIN-SQL paragraph, this being Oracle? c. But "b" isn't the cause of your failure; you aren't getting that far. d. From your control panel you can turn on ODBC logging... >>> whsmith@SRPNET.COM 07/01/02 06:26PM >>> I've got an SQR program that reads preventative maintenance records (PMs) in a Maximo database and generates workorders for any PMs that are due. The outer logic of the program selects the PMs that are due. Within this select block are several procedures that update several other tables in the database as needed for each particular workorder. One of these procedures calls the SQR Execute statement to run an Oracle stored function. The final step in the process is to write the new workorder itself. The only COMMIT statement in the code is after the workorder is successfully written - all other table updates are dependant upon the workorder getting written, so if any step fails, all other processing for the current workorder must be rolled back. There is no transaction processing in the Oracle procedure. Boring tech stuff: SQR Version is ODBC 4.3.4, running against Oracle 8i. Platform is Win 2000 and an HPUX database server. What's failing: When the compiled version of this program is run from our production job scheduler or a DOS prompt on my development PC, the program fails on an ODBC login failure. The message is "(SQR 1803) CONNECT failed. Perhaps username/password incorrect." I've loaded the program with #debug show statements to create a log, and found that the login failure is occurring the first time the program attempts the EXECUTE statement. The DOS command line that I'm using is: C:\SQRIBE\SQRServer\ODBC\ODB\BINW\sqrwt y:\SQR43~1.4\NGSPMG~1\NGSWOGEN.sqt DSN=xxxxxxx;UID=xxxxxxx;PWD=xxxxxxx -XCB -E -Oy:\SQR43~1.4\NGSPMG~1\NGSWogen.log -XMB -Fy:\SQR43~1.4\NGSPMG~1\NGSWogen.lis What's working: If I load the source for this program into Visual Sqribe and execute it using the preview window, it runs successfully. If I run the compiled version using the "SQR Execute" utility (sqrwtj.exe), it runs successfully. What I've tried: I've verified that the DSN name, uid and password are all correct in the DOS command line as well as the ODBC connect strings within the program. I've tried changing the DOS 8.3 representations of the path to the full folder names in the DOS batch file. I've tried adding a separate COMMIT statement in the Oracle function and setting it to an autonomous transaction so that it's transaction processing wouldn't affect the calling program. I've tried beating my head against my desk. None of these attempts have had any effect except for the head thing, which hurt like heck. Any suggestions for where to go next would be much appreciated... From owner-sqr-users@list.iex.net Tue Jul 2 09:50:24 2002 Date: Tue, 2 Jul 2002 07:45:15 -0700 From: "Walcker, Paul" Subject: Re: Trouble using EXECUTE statement to run Oracle function I would like to append on to George's thought. I would do the ODBC trace for both methods of executing the program. It seems like Visual Scribe may be making some ODBC settings behind the scenes that you are not aware of. Capturing and comparing both traces may provide some useful insights (if you can wade through all the ODBC API calls). Paul -----Original Message----- From: George Jansen [mailto:GJANSEN@AFLCIO.ORG] Sent: Tuesday, July 02, 2002 5:01 AM To: SQR-USERS@list.iex.net Subject: Re: Trouble using EXECUTE statement to run Oracle function a. Is it possible that the effective path is different at your command prompt (odbc needs %0RACLE_HOME%\BIN on the path)? b. Shouldn't you be using a BEGIN-SQL paragraph, this being Oracle? c. But "b" isn't the cause of your failure; you aren't getting that far. d. From your control panel you can turn on ODBC logging... >>> whsmith@SRPNET.COM 07/01/02 06:26PM >>> I've got an SQR program that reads preventative maintenance records (PMs) in a Maximo database and generates workorders for any PMs that are due. The outer logic of the program selects the PMs that are due. Within this select block are several procedures that update several other tables in the database as needed for each particular workorder. One of these procedures calls the SQR Execute statement to run an Oracle stored function. The final step in the process is to write the new workorder itself. The only COMMIT statement in the code is after the workorder is successfully written - all other table updates are dependant upon the workorder getting written, so if any step fails, all other processing for the current workorder must be rolled back. There is no transaction processing in the Oracle procedure. Boring tech stuff: SQR Version is ODBC 4.3.4, running against Oracle 8i. Platform is Win 2000 and an HPUX database server. What's failing: When the compiled version of this program is run from our production job scheduler or a DOS prompt on my development PC, the program fails on an ODBC login failure. The message is "(SQR 1803) CONNECT failed. Perhaps username/password incorrect." I've loaded the program with #debug show statements to create a log, and found that the login failure is occurring the first time the program attempts the EXECUTE statement. The DOS command line that I'm using is: C:\SQRIBE\SQRServer\ODBC\ODB\BINW\sqrwt y:\SQR43~1.4\NGSPMG~1\NGSWOGEN.sqt DSN=xxxxxxx;UID=xxxxxxx;PWD=xxxxxxx -XCB -E -Oy:\SQR43~1.4\NGSPMG~1\NGSWogen.log -XMB -Fy:\SQR43~1.4\NGSPMG~1\NGSWogen.lis What's working: If I load the source for this program into Visual Sqribe and execute it using the preview window, it runs successfully. If I run the compiled version using the "SQR Execute" utility (sqrwtj.exe), it runs successfully. What I've tried: I've verified that the DSN name, uid and password are all correct in the DOS command line as well as the ODBC connect strings within the program. I've tried changing the DOS 8.3 representations of the path to the full folder names in the DOS batch file. I've tried adding a separate COMMIT statement in the Oracle function and setting it to an autonomous transaction so that it's transaction processing wouldn't affect the calling program. I've tried beating my head against my desk. None of these attempts have had any effect except for the head thing, which hurt like heck. Any suggestions for where to go next would be much appreciated... From owner-sqr-users@list.iex.net Tue Jul 2 01:43:43 2002 Date: Mon, 1 Jul 2002 23:34:16 -0700 From: slash barbarian Subject: SQR 4736 Unexpected End of File Hi there... I have a problem here regarding to SQR report, using SQR Reportmate Oracle 7. And the report will be used in MAXIMO version 3.0 (if I was not mistaken). The problem is, when I running the report from MAXIMO, I get an error SQR 4736 Unexpected End-of_File while processing the run-time file What does the error means and how do I solve it? Thank you. Hafiz __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com From owner-sqr-users@list.iex.net Tue Jul 2 10:14:44 2002 Date: Tue, 2 Jul 2002 10:58:47 -0400 From: "Sampson, Thomas" Subject: multiple reports output to single file I'm including a simple example of what I'm trying to do: I'm trying to use a multiple layout/multiple report sqr to produce a single (pdf) output file. Everything works as it should except I can't figure out how to combine the separate output sections (each with it's own header and layout) into one output file. Is this possible? What I currently see is two files: rep_01.pdf rep.pdf Here's the code: ----------------------- begin-setup declare-layout rep1_layout orientation = landscape end-declare declare-layout rep2_layout orientation = portrait end-declare declare-report rep1_report layout = rep1_layout end-declare declare-report rep2_report layout = rep2_layout end-declare end-setup begin-program do rep1 do rep2 end-program begin-heading 10 for-reports = (rep1_report) print 'rep1 heading' (1,50) end-heading begin-heading 10 for-reports = (rep2_report) print 'rep2 heading' (1,50) end-heading begin-procedure rep1 let $text = 'printing rep1 report........' do print_rep1 end-procedure begin-procedure rep2 let $text = 'printing rep2 report........' do print_rep2 end-procedure begin-procedure print_rep1 use-report rep1_report print $text (1,1) end-procedure begin-procedure print_rep2 use-report rep2_report print $text (1,1) end-procedure SQR/6.0/Sun/SunOS 5.5.1/Sybase CT-LIB 10.0.4/Apr 27 2000 ********************************************************************** This email and any files transmitted with it are confidential and are intended solely for the use of the individual or entity to whom it is addressed. This communication may contain protected or privileged material and should only be viewed by the intended recipient(s). If you are not the intended recipient or the person responsible for delivering the email to the intended recipient(s), be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of this email is strictly prohibited. ********************************************************************** From owner-sqr-users@list.iex.net Tue Jul 2 14:48:24 2002 Date: Tue, 2 Jul 2002 15:40:55 -0400 From: Don Barclay Subject: Re: multiple reports output to single file Thomas, I am fairly certain that what you are trying to do is not possible within SQR. You could insert the second PDF document into the first document with Adobe (if you have Acrobat or higher). If you are doing this within PeopleSoft, there is a way to get both pdf files sent to the report manager (append "-MR 3" to the parameter list for the process definition). Don Barclay Westinghouse Savannah River Co. Aiken, SC >>I'm including a simple example of what I'm trying to do: I'm trying to use a multiple layout/multiple report sqr to produce a single (pdf) output file. Everything works as it should except I can't figure out how to combine the separate output sections (each with it's own header and layout) into one output file. Is this possible? What I currently see is two files: rep_01.pdf rep.pdf Here's the code: ----------------------- begin-setup declare-layout rep1_layout orientation = landscape end-declare declare-layout rep2_layout orientation = portrait end-declare declare-report rep1_report layout = rep1_layout end-declare declare-report rep2_report layout = rep2_layout end-declare end-setup begin-program do rep1 do rep2 end-program From owner-sqr-users@list.iex.net Tue Jul 2 16:43:37 2002 Date: Tue, 2 Jul 2002 17:38:26 -0400 From: "Sampson, Thomas" Subject: Re: multiple reports output to single file Thanks - that's what I was afraid of I began looking around for a unix utility to do the job after the fact but it doesn't seem to exist either. I guess I'll take a different angle. -----Original Message----- From: Don Barclay [mailto:don.barclay@SRS.GOV] Sent: Tuesday, July 02, 2002 3:41 PM To: SQR-USERS@list.iex.net Subject: Re: multiple reports output to single file Thomas, I am fairly certain that what you are trying to do is not possible within SQR. You could insert the second PDF document into the first document with Adobe (if you have Acrobat or higher). If you are doing this within PeopleSoft, there is a way to get both pdf files sent to the report manager (append "-MR 3" to the parameter list for the process definition). Don Barclay Westinghouse Savannah River Co. Aiken, SC >>I'm including a simple example of what I'm trying to do: I'm trying to use a multiple layout/multiple report sqr to produce a single (pdf) output file. Everything works as it should except I can't figure out how to combine the separate output sections (each with it's own header and layout) into one output file. Is this possible? What I currently see is two files: rep_01.pdf rep.pdf Here's the code: ----------------------- begin-setup declare-layout rep1_layout orientation = landscape end-declare declare-layout rep2_layout orientation = portrait end-declare declare-report rep1_report layout = rep1_layout end-declare declare-report rep2_report layout = rep2_layout end-declare end-setup begin-program do rep1 do rep2 end-program ********************************************************************** This email and any files transmitted with it are confidential and are intended solely for the use of the individual or entity to whom it is addressed. This communication may contain protected or privileged material and should only be viewed by the intended recipient(s). If you are not the intended recipient or the person responsible for delivering the email to the intended recipient(s), be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of this email is strictly prohibited. ********************************************************************** From owner-sqr-users@list.iex.net Wed Jul 3 03:06:33 2002 Date: Wed, 3 Jul 2002 01:03:28 -0700 From: Franck Masson Subject: Re: multiple reports output to single file Thomas, there are some PDF utilities that manage the merge of pdf file. so to deliver what you want you have to run sqr and then run this utilities to merge all the output in one file. you will have to work to the naming convention of the sqr output to merge the output files in the right order Franck, -----Original Message----- From: Sampson, Thomas [mailto:Sampson@FHLB-OF.COM] Sent: mardi 2 juillet 2002 16:59 To: SQR-USERS@list.iex.net Subject: multiple reports output to single file I'm including a simple example of what I'm trying to do: I'm trying to use a multiple layout/multiple report sqr to produce a single (pdf) output file. Everything works as it should except I can't figure out how to combine the separate output sections (each with it's own header and layout) into one output file. Is this possible? What I currently see is two files: rep_01.pdf rep.pdf Here's the code: ----------------------- begin-setup declare-layout rep1_layout orientation = landscape end-declare declare-layout rep2_layout orientation = portrait end-declare declare-report rep1_report layout = rep1_layout end-declare declare-report rep2_report layout = rep2_layout end-declare end-setup begin-program do rep1 do rep2 end-program begin-heading 10 for-reports = (rep1_report) print 'rep1 heading' (1,50) end-heading begin-heading 10 for-reports = (rep2_report) print 'rep2 heading' (1,50) end-heading begin-procedure rep1 let $text = 'printing rep1 report........' do print_rep1 end-procedure begin-procedure rep2 let $text = 'printing rep2 report........' do print_rep2 end-procedure begin-procedure print_rep1 use-report rep1_report print $text (1,1) end-procedure begin-procedure print_rep2 use-report rep2_report print $text (1,1) end-procedure SQR/6.0/Sun/SunOS 5.5.1/Sybase CT-LIB 10.0.4/Apr 27 2000 ********************************************************************** This email and any files transmitted with it are confidential and are intended solely for the use of the individual or entity to whom it is addressed. This communication may contain protected or privileged material and should only be viewed by the intended recipient(s). If you are not the intended recipient or the person responsible for delivering the email to the intended recipient(s), be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of this email is strictly prohibited. ********************************************************************** From owner-sqr-users@list.iex.net Tue Jul 2 14:37:22 2002 Date: Tue, 2 Jul 2002 14:28:30 -0500 From: Yuri Hunter Subject: scheduling SQR in PSoft
Hi,
 
I would like to schedule an SQR process in PeopleSoft Process Scheduler (weekly) but do not have a need for a user to ever run the process from a panel/menu.
 
Is it necessary to link this SQR process to a panel group?  Is there anything different in terms of process definition setup in Process Scheduler when the SQR process will be user-run or always scheduled?
 
Thanks.
 

Send and receive Hotmail on your mobile device: Click Here
From owner-sqr-users@list.iex.net Tue Jul 2 14:46:33 2002 Date: Tue, 2 Jul 2002 15:36:36 -0400 From: Bruce Kelling Subject: Re: scheduling SQR in PSoft Yes, you must link sqr to at least one panel group. It is a required field in the process definition. If users do not need to see the panel group, don't grant menu access to that item. The only difference for user-run setup in process definition is the process security groups, but your first sentence stated that you do not have a need for this. Bruce -----Original Message----- From: Yuri Hunter [mailto:lakef250@HOTMAIL.COM] Sent: Tuesday, July 02, 2002 3:29 PM To: SQR-USERS@list.iex.net Subject: scheduling SQR in PSoft Hi, I would like to schedule an SQR process in PeopleSoft Process Scheduler (weekly) but do not have a need for a user to ever run the process from a panel/menu. Is it necessary to link this SQR process to a panel group? Is there anything different in terms of process definition setup in Process Scheduler when the SQR process will be user-run or always scheduled? Thanks. _____ Send and receive Hotmail on your mobile device: Click Here From owner-sqr-users@list.iex.net Tue Jul 2 15:03:39 2002 Date: Tue, 2 Jul 2002 14:56:13 -0500 From: Yuri Hunter Subject: Re: scheduling SQR in PSoft

Thank you.

>From: Bruce Kelling
>Reply-To: sqr-users@list.iex.net
>To: SQR-USERS@list.iex.net
>Subject: Re: scheduling SQR in PSoft
>Date: Tue, 2 Jul 2002 15:36:36 -0400
>
>Yes, you must link sqr to at least one panel group. It is a required field
>in the process definition. If users do not need to see the panel group,
>don't grant menu access to that item.
>
>The only difference for user-run setup in process definition is the process
>security groups, but your first sentence stated that you do not have a need
>for this.
>
>
>Bruce
>
> -----Original Message-----
>From: Yuri Hunter [mailto:lakef250@HOTMAIL.COM]
>Sent: Tuesday, July 02, 2002 3:29 PM
>To: SQR-USERS@list.iex.net
>Subject: scheduling SQR in PSoft
>
>
>
>Hi,
>
>I would like to schedule an SQR process in PeopleSoft Process Scheduler
>(weekly) but do not have a need for a user to ever run the process from a
>panel/menu.
>
>Is it necessary to link this SQR process to a panel group? Is there
>anything different in terms of process definition setup in Process Scheduler
>when the SQR process will be user-run or always scheduled?
>
>Thanks.
>
>
> _____
>
>Send and receive Hotmail on your mobile device: Click Here
>

Chat with friends online, try MSN Messenger: Click Here
From owner-sqr-users@list.iex.net Tue Jul 2 16:35:15 2002 Date: Tue, 2 Jul 2002 14:29:11 -0700 From: Allen Cunningham Subject: sqr check for existence of a file on network drive I have an sqr I want to schedule to run, but only if a file exists on a network drive, and that file is not empty. I need to find out a way for the sqr to check if the file exists. Anybody know of any sqr file functions that will work for this?? ac *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* Allen Cunningham Sonoma State University Administrative Information Systems allen.cunningham@sonoma.edu ph 707.664.2052 fx 707.664.2505 This message has been sent using 100% recycled electrons. *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* From owner-sqr-users@list.iex.net Tue Jul 2 16:40:54 2002 Date: Tue, 2 Jul 2002 16:36:19 -0500 From: James Womeldorf Subject: Re: sqr check for existence of a file on network drive Let #FileExists = exists($File_Path_And_Name) if #FileExists = zero after the call the file exists. Jim Womeldorf Programmer/Analyst Fastenal Company jwomeldo@fastenal.com (507) 453-8250 -----Original Message----- From: Allen Cunningham [mailto:allen.cunningham@SONOMA.EDU] Sent: Tuesday, July 02, 2002 4:29 PM To: SQR-USERS@list.iex.net Subject: sqr check for existence of a file on network drive I have an sqr I want to schedule to run, but only if a file exists on a network drive, and that file is not empty. I need to find out a way for the sqr to check if the file exists. Anybody know of any sqr file functions that will work for this?? ac *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* Allen Cunningham Sonoma State University Administrative Information Systems allen.cunningham@sonoma.edu ph 707.664.2052 fx 707.664.2505 This message has been sent using 100% recycled electrons. *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* From owner-sqr-users@list.iex.net Tue Jul 2 17:06:08 2002 Date: Tue, 2 Jul 2002 15:00:14 -0700 From: Allen Cunningham Subject: Re: sqr check for existence of a file on network drive Hi Jim - Thanks for the suggestion, I will try that. ac -----Original Message----- From: Discussion of SQR, Brio Software's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of James Womeldorf Sent: Tuesday, July 02, 2002 2:36 PM To: SQR-USERS@list.iex.net Subject: Re: sqr check for existence of a file on network drive Let #FileExists = exists($File_Path_And_Name) if #FileExists = zero after the call the file exists. Jim Womeldorf Programmer/Analyst Fastenal Company jwomeldo@fastenal.com (507) 453-8250 -----Original Message----- From: Allen Cunningham [mailto:allen.cunningham@SONOMA.EDU] Sent: Tuesday, July 02, 2002 4:29 PM To: SQR-USERS@list.iex.net Subject: sqr check for existence of a file on network drive I have an sqr I want to schedule to run, but only if a file exists on a network drive, and that file is not empty. I need to find out a way for the sqr to check if the file exists. Anybody know of any sqr file functions that will work for this?? ac *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* Allen Cunningham Sonoma State University Administrative Information Systems allen.cunningham@sonoma.edu ph 707.664.2052 fx 707.664.2505 This message has been sent using 100% recycled electrons. *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* From owner-sqr-users@list.iex.net Tue Jul 2 16:44:40 2002 Date: Tue, 2 Jul 2002 14:39:01 -0700 From: Robert Stojkovic Subject: Re: sqr check for existence of a file on network drive Allen That would depend on the type of file you are looking for on the network. If you are looking for a flat file do an open and check for file status it was able to open the file. If you want to know how many rows in the flat file have it start reading the file in if more than 1 row is process then exit the read and proceeded with your sqr. If no rows then exit sqr. Hope this helps Robert Stojkovic robert.stojkovic@io-consulting .com visit our website www.io-consulting.com cell 661 857 0350 -----Original Message----- From: Discussion of SQR, Brio Software's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Allen Cunningham Sent: Tuesday, July 02, 2002 2:29 PM To: SQR-USERS@list.iex.net Subject: sqr check for existence of a file on network drive I have an sqr I want to schedule to run, but only if a file exists on a network drive, and that file is not empty. I need to find out a way for the sqr to check if the file exists. Anybody know of any sqr file functions that will work for this?? ac *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* Allen Cunningham Sonoma State University Administrative Information Systems allen.cunningham@sonoma.edu ph 707.664.2052 fx 707.664.2505 This message has been sent using 100% recycled electrons. *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* From owner-sqr-users@list.iex.net Tue Jul 2 17:06:18 2002 Date: Tue, 2 Jul 2002 15:00:14 -0700 From: Allen Cunningham Subject: Re: sqr check for existence of a file on network drive It would be a text file, sounds like 'exists', will tell me if it's there, and then if there are any rows, it's not empty. ac -----Original Message----- From: Discussion of SQR, Brio Software's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Robert Stojkovic Sent: Tuesday, July 02, 2002 2:39 PM To: SQR-USERS@list.iex.net Subject: Re: sqr check for existence of a file on network drive Allen That would depend on the type of file you are looking for on the network. If you are looking for a flat file do an open and check for file status it was able to open the file. If you want to know how many rows in the flat file have it start reading the file in if more than 1 row is process then exit the read and proceeded with your sqr. If no rows then exit sqr. Hope this helps Robert Stojkovic robert.stojkovic@io-consulting .com visit our website www.io-consulting.com cell 661 857 0350 -----Original Message----- From: Discussion of SQR, Brio Software's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Allen Cunningham Sent: Tuesday, July 02, 2002 2:29 PM To: SQR-USERS@list.iex.net Subject: sqr check for existence of a file on network drive I have an sqr I want to schedule to run, but only if a file exists on a network drive, and that file is not empty. I need to find out a way for the sqr to check if the file exists. Anybody know of any sqr file functions that will work for this?? ac *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* Allen Cunningham Sonoma State University Administrative Information Systems allen.cunningham@sonoma.edu ph 707.664.2052 fx 707.664.2505 This message has been sent using 100% recycled electrons. *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* From owner-sqr-users@list.iex.net Wed Jul 3 13:34:24 2002 Date: Wed, 3 Jul 2002 11:22:36 -0700 From: Shanti Ramakrishnan Subject: SQt file error Hi, I have SQR version 6.0. When I execute a report the .lis file is created with no problem. As soon as I try to run an sqt file ... that is when I get the "(SQR 4723) RunTime file must be recreated for this version of SQR....SQR for PeopleSoft: Program Aborting." Does any one have any idea why this error? Thanks for your input on this. --------------------------------- Do You Yahoo!? New! SBC Yahoo! Dial - 1st Month Free & unlimited access From owner-sqr-users@list.iex.net Wed Jul 3 14:09:12 2002 Date: Wed, 3 Jul 2002 14:58:59 -0400 From: Larry Roux Subject: Re: SQt file error According to the SQR manual, the SQT was compiled under a different version of SQR. You need to re-compile the SQR into an SQT and then try again. ****************************** Larry Roux Syracuse University lroux@syr.edu ******************************* >>> shanpram@YAHOO.COM 07/03/02 02:22PM >>> Hi, I have SQR version 6.0. When I execute a report the .lis file is created with no problem. As soon as I try to run an sqt file ... that is when I get the "(SQR 4723) RunTime file must be recreated for this version of SQR....SQR for PeopleSoft: Program Aborting." Does any one have any idea why this error? Thanks for your input on this. --------------------------------- Do You Yahoo!? New! SBC Yahoo! Dial - 1st Month Free & unlimited access From owner-sqr-users@list.iex.net Wed Jul 3 14:40:47 2002 Date: Wed, 3 Jul 2002 12:35:49 -0700 From: Sarat Subject: CommitTransactions via SQR in SQL Server Hello, I have an SQR which updates production tables. When we were on AS400/DB2, we specifically used SQC 'do commit transactions' after every procedure which performs updates. After we migrated to SQL Server 2000, one of our migration strategies was to remove all commit-transaction procedure calls because by-default, SQL Server has auto-commit ON. Doubts I have are: 1. Is it not true that it is faster to commit transaction in batches vs one by one? 2. If I need to commit transaction in groups, then I should modify my SQR to use BEGIN TRANSACTION and COMMIT TRANSACTION within SQL Paragraphs. right? Thanks, Sarat. __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com From owner-sqr-users@list.iex.net Thu Jul 4 11:35:56 2002 Date: Thu, 4 Jul 2002 11:30:25 -0500 From: "Wayne C. Findley" Subject: Seeking...CHANGE FORMAT I have to create a Problem Report change form for USERS within my company who requires changes to the PeopleSoft System. Whether this be a new SQR or changes (modification, deletion or creation) to menus, panels, panel groups etc. Has anyone got a change form where persons can use to apply for these changes? I need a format of this form so I can modify for our purposes if necessary. I need this like yesterday. Thanks Again. From owner-sqr-users@list.iex.net Fri Jul 5 12:03:28 2002 Date: Fri, 5 Jul 2002 11:39:02 -0500 From: Mr Jody Nabors Subject: Date error on Diff-Date A number of our sqrs are getting the following error: (SQR 1921) Bad date mask starting at: '-MON-YYYY'. I have the troublesome Diff-Date line of code commented out. The report runs fine w/out it. The situation: We recently moved from a PS 7 to PS 8.16 and from Oracle 7 to 8.16. We figure it has something to do w/ how the dates are stored in the database. All sqrs were running fine in the old environment. We have been cleaning up the conversion, and the reports are the last thing on the list so everything else is in order in the environment. This is the snippet of code where I am receiving the error: begin-procedure Process-Main2 let #tot1 = 0 begin-SELECT !distinct c1.deptid move &a1.n_start_dt to $START_DT1 LET $SYSDATE = SUBSTR($REPORTDATE,7,4) || '-' || SUBSTR($REPORTDATE,1,2) || '-' ||SUBSTR($REPORTDATE,4,2) !DO Diff-Date($SYSDATE,$START_DT1,#yy_diff,#MM_diff,#DD_diff) ((HERE IS THE ERROR)) let #DD_DIFF = #DD_DIFF +1 ADD #DD_diff TO #SUBTOTAL_DT ADD #DD_diff TO #GRANDTOTAL_DT d1.name (+1, {col1}) b1.n_rate_rank (0, {col2}) d1.emplid (0, {col3}) edit xxx-xx-xxxx a1.n_start_dt !(0, {col4}) e1.descr .... Jody From owner-sqr-users@list.iex.net Fri Jul 5 18:56:45 2002 Date: Fri, 5 Jul 2002 16:49:38 -0700 From: Ken Boettger Subject: RBSBIG I am looking for information on RBSBIG. I would like to allocate a larger sector programmatically during the execution of an SQR report (something we run in batch every evening). Can this be done with SQR code? -KenB From owner-sqr-users@list.iex.net Tue Jul 9 15:40:47 2002 Date: Tue, 9 Jul 2002 16:34:29 -0400 From: Paul Hoyte Subject: Re: RBSBIG Ken: If you have the correct privileges to issue the command, this can be done by executing a sql statement as part of the begin-sql paragraph. The command will set the current transaction to use which ever rollback segment that you specify in the command... begin-procedure test_rbs begin-sql SET TRANSACTION USE ROLLBACK SEGMENT rbs3; insert into dept values(9888, 'TEST DEPT', 'BRIGHTON'); end-sql end-procedure Paul Hoyte Sharidionne Inc. (248) 559-6868 Email: phoyte@sharidionne.com -----Original Message----- From: Discussion of SQR, Brio Software's database reporting language [mailto:SQR-USERS@list.iex.net] On Behalf Of Ken Boettger Sent: Friday, July 05, 2002 7:50 PM To: SQR-USERS@list.iex.net Subject: RBSBIG I am looking for information on RBSBIG. I would like to allocate a larger sector programmatically during the execution of an SQR report (something we run in batch every evening). Can this be done with SQR code? -KenB From owner-sqr-users@list.iex.net Wed Jul 10 12:50:37 2002 Date: Wed, 10 Jul 2002 10:45:28 -0700 From: Ken Boettger Subject: Re: RBSBIG Thanks Paul. I will need to play with the privs in our test environment. I think one of our people here tried this but it still used the default segment. So the privs may be the issue here. Thanks. -Ken >>> phoyte@SHARIDIONNE.COM 07/09/02 01:34PM >>> Ken: If you have the correct privileges to issue the command, this can be done by executing a sql statement as part of the begin-sql paragraph. The command will set the current transaction to use which ever rollback segment that you specify in the command... begin-procedure test_rbs begin-sql SET TRANSACTION USE ROLLBACK SEGMENT rbs3; insert into dept values(9888, 'TEST DEPT', 'BRIGHTON'); end-sql end-procedure Paul Hoyte Sharidionne Inc. (248) 559-6868 Email: phoyte@sharidionne.com -----Original Message----- From: Discussion of SQR, Brio Software's database reporting language [mailto:SQR-USERS@list.iex.net] On Behalf Of Ken Boettger Sent: Friday, July 05, 2002 7:50 PM To: SQR-USERS@list.iex.net Subject: RBSBIG I am looking for information on RBSBIG. I would like to allocate a larger sector programmatically during the execution of an SQR report (something we run in batch every evening). Can this be done with SQR code? -KenB From owner-sqr-users@list.iex.net Wed Jul 10 13:22:35 2002 Date: Wed, 10 Jul 2002 11:04:17 -0700 From: Jennifer Tran Subject: Re: RBSBIG You might also want to specify the rollback segment to be used by the operator(s) (either it is a person, a session or a specific process) while running the SQR job. This needs to be done by the DBA within oracle, in constrast to the SET TRANSACTION...command issued within the SQR itself. By defaulting the rollback segment to be used in the oracle environment, when the operator runs the SQR, the specified rollback segment must be used. Also, you might want to use COMMIT statement to assist in using the rollback segment. HTH.. Ken Boettger wrote: > Thanks Paul. > > I will need to play with the privs in our test environment. I think one of our people here tried this but it still used the default segment. So the privs may be the issue here. Thanks. > > -Ken > > >>> phoyte@SHARIDIONNE.COM 07/09/02 01:34PM >>> > Ken: > > If you have the correct privileges to issue the command, this can be > done by executing a sql statement as part of the begin-sql paragraph. > The command will set the current transaction to use which ever rollback > segment that you specify in the command... > > begin-procedure test_rbs > > begin-sql > > SET TRANSACTION USE ROLLBACK SEGMENT rbs3; > > insert into dept values(9888, 'TEST DEPT', 'BRIGHTON'); > > end-sql > > end-procedure > > Paul Hoyte > Sharidionne Inc. > (248) 559-6868 > Email: phoyte@sharidionne.com > > -----Original Message----- > From: Discussion of SQR, Brio Software's database reporting language > [mailto:SQR-USERS@list.iex.net] On Behalf Of Ken Boettger > Sent: Friday, July 05, 2002 7:50 PM > To: SQR-USERS@list.iex.net > Subject: RBSBIG > > I am looking for information on RBSBIG. I would like to allocate a > larger sector programmatically during the execution of an SQR report > (something we run in batch every evening). Can this be done with SQR > code? > > -KenB -- ********************************* California State University ----------- Jennifer Tran Email: jtran@bms.calstate.edu Phone: (562)951-4395 Making everyday a better day ********************************* From owner-sqr-users@list.iex.net Wed Jul 10 14:36:53 2002 Date: Wed, 10 Jul 2002 15:21:17 -0400 From: George Jansen Subject: Re: RBSBIG When in doubt, check. I decided to test this and created a scratch table: create table scratch(a varchar2(2)); I then ran the followig SQR: !!!!!!!!!!!!!!!!!!!! begin-program do insert-rows do insert-rows end-program begin-procedure set-rbs begin-sql set transaction use rollback segment rbsbig; end-sql end-procedure begin-procedure check-rbs ($message) begin-select r.name &rname display $message noline display &rname from v$transaction t, v$rollname r where t.xidusn = r.usn and t.ses_addr = (select distinct s.saddr from v$session s, v$mystat m where s.sid = m.sid) end-select end-procedure check-rbs begin-procedure random-dml begin-sql insert into scratch(a) values('b'); end-sql end-procedure random-dml begin-procedure insert-rows do set-rbs do random-dml do check-rbs('Expect rbsbig, segment is ') commit do random-dml do check-rbs('Segment probably changed: ') commit end-procedure insert-rows !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! and, as expected, saw Expect rbsbig, segment is RBSBIG Segment probably changed: R01 Expect rbsbig, segment is RBSBIG Segment probably changed: R01 Note that you will have to have privileges on the dynamic performance views to actually run this SQR; however it requires no special privileges to issue the 'SET TRANSACTION USE ROLLBACK SEGMENT". From owner-sqr-users@list.iex.net Wed Jul 10 14:51:38 2002 Date: Wed, 10 Jul 2002 15:42:39 -0400 From: Paul Hoyte Subject: Re: RBSBIG George: I would suggest executing the insert/update commands within the same begin-sql paragraph or set the transaction to use the rbsbig EACH time just before you execute the insert but within the same begin-sql paragraph. Transactions can be assigned different RBSegments so to hold the one that you want you have to use it right away with the sql paragraph. Executing another procedure will create the possibility to lose the assigned segment. HTH Paul Hoyte Sharidionne Inc. (248) 559-6868 Email: phoyte@sharidionne.com -----Original Message----- From: Discussion of SQR, Brio Software's database reporting language [mailto:SQR-USERS@list.iex.net] On Behalf Of George Jansen Sent: Wednesday, July 10, 2002 3:21 PM To: SQR-USERS@list.iex.net Subject: Re: RBSBIG When in doubt, check. I decided to test this and created a scratch table: create table scratch(a varchar2(2)); I then ran the followig SQR: !!!!!!!!!!!!!!!!!!!! begin-program do insert-rows do insert-rows end-program begin-procedure set-rbs begin-sql set transaction use rollback segment rbsbig; end-sql end-procedure begin-procedure check-rbs ($message) begin-select r.name &rname display $message noline display &rname from v$transaction t, v$rollname r where t.xidusn = r.usn and t.ses_addr = (select distinct s.saddr from v$session s, v$mystat m where s.sid = m.sid) end-select end-procedure check-rbs begin-procedure random-dml begin-sql insert into scratch(a) values('b'); end-sql end-procedure random-dml begin-procedure insert-rows do set-rbs do random-dml do check-rbs('Expect rbsbig, segment is ') commit do random-dml do check-rbs('Segment probably changed: ') commit end-procedure insert-rows !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! and, as expected, saw Expect rbsbig, segment is RBSBIG Segment probably changed: R01 Expect rbsbig, segment is RBSBIG Segment probably changed: R01 Note that you will have to have privileges on the dynamic performance views to actually run this SQR; however it requires no special privileges to issue the 'SET TRANSACTION USE ROLLBACK SEGMENT". From owner-sqr-users@list.iex.net Wed Jul 10 15:11:44 2002 Date: Wed, 10 Jul 2002 16:06:35 -0400 From: George Jansen Subject: Re: RBSBIG Paul, What I was trying to illustrate was that a. SET TRANSACTION USE ROLLBACK SEGMENT is effective within SQR b. It is good for one transaction only: do a commit (or rollback, or issue some DDL), and the new transaction will probably bring a new rollback segment. Sorry if this wasn't clear. It doesn't have to be the same begin-sql/end-sql paragraph; executing another procedure will affect it only there's a commit or rollback (possibly implicit or auto). George >>> phoyte@SHARIDIONNE.COM 07/10/02 03:42PM >>> George: I would suggest executing the insert/update commands within the same begin-sql paragraph or set the transaction to use the rbsbig EACH time just before you execute the insert but within the same begin-sql paragraph. Transactions can be assigned different RBSegments so to hold the one that you want you have to use it right away with the sql paragraph. Executing another procedure will create the possibility to lose the assigned segment. HTH Paul Hoyte Sharidionne Inc. (248) 559-6868 Email: phoyte@sharidionne.com From owner-sqr-users@list.iex.net Wed Jul 10 22:24:37 2002 Date: Wed, 10 Jul 2002 23:16:19 -0400 From: Paul Hoyte Subject: Re: RBSBIG Sorry George. Guess I did mis-understand the reply. Comes from reading too fast I guess. Your solution is correct and you would have to set the RBS for each transaction. Paul Hoyte Sharidionne Inc. (248) 559-6868 Email: phoyte@sharidionne.com -----Original Message----- From: Discussion of SQR, Brio Software's database reporting language [mailto:SQR-USERS@list.iex.net] On Behalf Of George Jansen Sent: Wednesday, July 10, 2002 4:07 PM To: SQR-USERS@list.iex.net Subject: Re: RBSBIG Paul, What I was trying to illustrate was that a. SET TRANSACTION USE ROLLBACK SEGMENT is effective within SQR b. It is good for one transaction only: do a commit (or rollback, or issue some DDL), and the new transaction will probably bring a new rollback segment. Sorry if this wasn't clear. It doesn't have to be the same begin-sql/end-sql paragraph; executing another procedure will affect it only there's a commit or rollback (possibly implicit or auto). George >>> phoyte@SHARIDIONNE.COM 07/10/02 03:42PM >>> George: I would suggest executing the insert/update commands within the same begin-sql paragraph or set the transaction to use the rbsbig EACH time just before you execute the insert but within the same begin-sql paragraph. Transactions can be assigned different RBSegments so to hold the one that you want you have to use it right away with the sql paragraph. Executing another procedure will create the possibility to lose the assigned segment. HTH Paul Hoyte Sharidionne Inc. (248) 559-6868 Email: phoyte@sharidionne.com From owner-sqr-users@list.iex.net Sat Jul 6 16:49:33 2002 Date: Sat, 6 Jul 2002 22:24:20 +0100 From: Colin Nicol Subject: Date Wild cards in stored Procedures?? I use Microsoft Query within Excel to extract data from the SQL server = by using Stored Procedures set up for me by the IT dept. eg., exec moy_stockissues '20020630' Instead of having to change the date every day by editing the query..is = there a form of words that I can put in place of the date that would = mean..." Today" or " Today + 1" .... or can the "SQL date" be made = to refer to an Excel cell ??? Thanks, Colin From owner-sqr-users@list.iex.net Mon Jul 8 03:36:40 2002 Date: Mon, 8 Jul 2002 16:10:16 +0800 From: Esther Chin Subject: How to run SQR from web ? Hi there, I have just subcribed to sqr-users. Anybody can tell me how to run the = sqr program from the web ? Best regards, Esther=20 From owner-sqr-users@list.iex.net Mon Jul 8 04:08:10 2002 Date: Mon, 8 Jul 2002 14:28:47 +0530 From: "Nandi, Jyotirmoy (Cognizant)" Subject: Re: How to run SQR from web ? hi esther, 1. first of all create a run-control page or select one from the given run-control pages. include that run-control page in a component and the component in turn to a menu. 2. give security to the run-control page by going to the menu Go -> PeopleTools -> Maintain Security -> use. choose one of the roles to which your user-profile has access. then choose one of the permission lists which are associated with that role. in the chosen permission list include the run-control page. 3. create a process-definition for your SQR by going to the menu Go -> PeopleTools -> Process Scheduler Manager -> use -> process definitions. after all the above are done u can navigate to the page you have created thru the webmode. after that in that page press the RUN button. you will be directed to a process request page. therein select ur own SQR and press OK. the SQR should run and the results can be seen in the Process Monitor link on that page. if the SQR has errors a status of Error will be shown otherwise a status of Success will come up. regards, jyoti -----Original Message----- From: Esther Chin [mailto:esther_chin@CONGRUENT.COM.SG] Sent: Monday, July 08, 2002 1:40 PM To: SQR-USERS@list.iex.net Subject: How to run SQR from web ? Hi there, I have just subcribed to sqr-users. Anybody can tell me how to run the sqr program from the web ? Best regards, Esther From owner-sqr-users@list.iex.net Mon Jul 8 04:39:13 2002 Date: Mon, 8 Jul 2002 17:15:31 +0800 From: Esther Chin Subject: Re: How to run SQR from web ? Hi jyoti, Thanks for your explaination. But what i mentioned is not in peoplesoft. My concern is how to run the sqr report from the normal web site? Let say, is it possible to use html or javascript to call up the sqr program and run it from the web? if yes, may i know how to do that ? please advise. Best regards, Esther ----- Original Message ----- From: "Nandi, Jyotirmoy (Cognizant)" To: Sent: Monday, July 08, 2002 4:58 PM Subject: Re: How to run SQR from web ? > hi esther, > > 1. first of all create a run-control page or select one from the given > run-control pages. include that run-control page in a component and the > component in turn to a menu. > > 2. give security to the run-control page by going to the menu Go -> > PeopleTools -> Maintain Security -> use. choose one of the roles to which > your user-profile has access. then choose one of the permission lists which > are associated with that role. in the chosen permission list include the > run-control page. > > 3. create a process-definition for your SQR by going to the menu Go -> > PeopleTools -> Process Scheduler Manager -> use -> process definitions. > > after all the above are done u can navigate to the page you have > created thru the webmode. after that in that page press the RUN button. you > will be directed to a process request page. therein select ur own SQR and > press OK. the SQR should run and the results can be seen in the Process > Monitor link on that page. if the SQR has errors a status of Error will be > shown otherwise a status of Success will come up. > > regards, > jyoti > > -----Original Message----- > From: Esther Chin [mailto:esther_chin@CONGRUENT.COM.SG] > Sent: Monday, July 08, 2002 1:40 PM > To: SQR-USERS@list.iex.net > Subject: How to run SQR from web ? > > > Hi there, > > I have just subcribed to sqr-users. Anybody can tell me how to run the sqr > program from the web ? > > Best regards, > Esther > From owner-sqr-users@list.iex.net Mon Jul 8 04:43:20 2002 Date: Mon, 8 Jul 2002 11:27:56 +0200 From: "Kloprogge,Frank F.J.M." Subject: Re: How to run SQR from web ? Hi Esther, have a look at www.sqrug.com and search for "running web" HTH Frank Kloprogge Fontys ICT-Services DBA PeopleSoft 08778-78710 -----Oorspronkelijk bericht----- Van: Esther Chin [mailto:esther_chin@CONGRUENT.COM.SG] Verzonden: maandag 8 juli 2002 11:16 Aan: SQR-USERS@LIST.IEX.NET Onderwerp: Re: How to run SQR from web ? Hi jyoti, Thanks for your explaination. But what i mentioned is not in peoplesoft. My concern is how to run the sqr report from the normal web site? Let say, is it possible to use html or javascript to call up the sqr program and run it from the web? if yes, may i know how to do that ? please advise. Best regards, Esther ----- Original Message ----- From: "Nandi, Jyotirmoy (Cognizant)" To: Sent: Monday, July 08, 2002 4:58 PM Subject: Re: How to run SQR from web ? > hi esther, > > 1. first of all create a run-control page or select one from the given > run-control pages. include that run-control page in a component and the > component in turn to a menu. > > 2. give security to the run-control page by going to the menu Go -> > PeopleTools -> Maintain Security -> use. choose one of the roles to which > your user-profile has access. then choose one of the permission lists which > are associated with that role. in the chosen permission list include the > run-control page. > > 3. create a process-definition for your SQR by going to the menu Go -> > PeopleTools -> Process Scheduler Manager -> use -> process definitions. > > after all the above are done u can navigate to the page you have > created thru the webmode. after that in that page press the RUN button. you > will be directed to a process request page. therein select ur own SQR and > press OK. the SQR should run and the results can be seen in the Process > Monitor link on that page. if the SQR has errors a status of Error will be > shown otherwise a status of Success will come up. > > regards, > jyoti > > -----Original Message----- > From: Esther Chin [mailto:esther_chin@CONGRUENT.COM.SG] > Sent: Monday, July 08, 2002 1:40 PM > To: SQR-USERS@list.iex.net > Subject: How to run SQR from web ? > > > Hi there, > > I have just subcribed to sqr-users. Anybody can tell me how to run the sqr > program from the web ? > > Best regards, > Esther > From owner-sqr-users@list.iex.net Mon Jul 8 05:11:13 2002 Date: Mon, 8 Jul 2002 03:00:22 -0700 From: Franck Masson Subject: Re: How to run SQR from web ? it is quit easy you have to setup a CGI/ASP/servlet that will launch sqr and return the output execution. then after you just have to design an html page ( html form) to collect the parameters and send it to your cgi/asp/servlet. there is a chapter/documentation with the 2 books that is provided with SQR server Franck, -----Original Message----- From: Esther Chin [mailto:esther_chin@CONGRUENT.COM.SG] Sent: lundi 8 juillet 2002 11:16 To: SQR-USERS@list.iex.net Subject: Re: How to run SQR from web ? Hi jyoti, Thanks for your explaination. But what i mentioned is not in peoplesoft. My concern is how to run the sqr report from the normal web site? Let say, is it possible to use html or javascript to call up the sqr program and run it from the web? if yes, may i know how to do that ? please advise. Best regards, Esther ----- Original Message ----- From: "Nandi, Jyotirmoy (Cognizant)" To: Sent: Monday, July 08, 2002 4:58 PM Subject: Re: How to run SQR from web ? > hi esther, > > 1. first of all create a run-control page or select one from the given > run-control pages. include that run-control page in a component and the > component in turn to a menu. > > 2. give security to the run-control page by going to the menu Go -> > PeopleTools -> Maintain Security -> use. choose one of the roles to which > your user-profile has access. then choose one of the permission lists which > are associated with that role. in the chosen permission list include the > run-control page. > > 3. create a process-definition for your SQR by going to the menu Go -> > PeopleTools -> Process Scheduler Manager -> use -> process definitions. > > after all the above are done u can navigate to the page you have > created thru the webmode. after that in that page press the RUN button. you > will be directed to a process request page. therein select ur own SQR and > press OK. the SQR should run and the results can be seen in the Process > Monitor link on that page. if the SQR has errors a status of Error will be > shown otherwise a status of Success will come up. > > regards, > jyoti > > -----Original Message----- > From: Esther Chin [mailto:esther_chin@CONGRUENT.COM.SG] > Sent: Monday, July 08, 2002 1:40 PM > To: SQR-USERS@list.iex.net > Subject: How to run SQR from web ? > > > Hi there, > > I have just subcribed to sqr-users. Anybody can tell me how to run the sqr > program from the web ? > > Best regards, > Esther > From owner-sqr-users@list.iex.net Mon Jul 8 06:49:36 2002 Date: Mon, 8 Jul 2002 07:28:44 -0400 From: George Jansen Subject: Re: How to run SQR from web ? FYI, it is fairly straightforward to set up a Perl module to run SQR, should it be that you're running Apache. >>> franck.masson@BRIO.COM 07/08/02 06:00AM >>> it is quit easy you have to setup a CGI/ASP/servlet that will launch sqr and return the output execution. then after you just have to design an html page ( html form) to collect the parameters and send it to your cgi/asp/servlet. there is a chapter/documentation with the 2 books that is provided with SQR server Franck, -----Original Message----- From: Esther Chin [mailto:esther_chin@CONGRUENT.COM.SG] Sent: lundi 8 juillet 2002 11:16 To: SQR-USERS@list.iex.net Subject: Re: How to run SQR from web ? Hi jyoti, Thanks for your explaination. But what i mentioned is not in peoplesoft. My concern is how to run the sqr report from the normal web site? Let say, is it possible to use html or javascript to call up the sqr program and run it from the web? if yes, may i know how to do that ? please advise. Best regards, Esther ----- Original Message ----- From: "Nandi, Jyotirmoy (Cognizant)" To: Sent: Monday, July 08, 2002 4:58 PM Subject: Re: How to run SQR from web ? > hi esther, > > 1. first of all create a run-control page or select one from the given > run-control pages. include that run-control page in a component and the > component in turn to a menu. > > 2. give security to the run-control page by going to the menu Go -> > PeopleTools -> Maintain Security -> use. choose one of the roles to which > your user-profile has access. then choose one of the permission lists which > are associated with that role. in the chosen permission list include the > run-control page. > > 3. create a process-definition for your SQR by going to the menu Go -> > PeopleTools -> Process Scheduler Manager -> use -> process definitions. > > after all the above are done u can navigate to the page you have > created thru the webmode. after that in that page press the RUN button. you > will be directed to a process request page. therein select ur own SQR and > press OK. the SQR should run and the results can be seen in the Process > Monitor link on that page. if the SQR has errors a status of Error will be > shown otherwise a status of Success will come up. > > regards, > jyoti > > -----Original Message----- > From: Esther Chin [mailto:esther_chin@CONGRUENT.COM.SG] > Sent: Monday, July 08, 2002 1:40 PM > To: SQR-USERS@list.iex.net > Subject: How to run SQR from web ? > > > Hi there, > > I have just subcribed to sqr-users. Anybody can tell me how to run the sqr > program from the web ? > > Best regards, > Esther > From owner-sqr-users@list.iex.net Mon Jul 8 11:16:06 2002 Date: Mon, 8 Jul 2002 09:04:48 -0700 From: David Donnelly Subject: Reboot of W2K after SQR 6.1.3 runs I have recently installed SQR 6.1.3 on Windows 2000 Professional. Friday, while working on a program, I noticed that W2K suddenly rebooted. This happened more than once, but not while "anything important" was running. I had run a report in SQR five or ten minutes earlier, and was looking at the output with a file browser, or doing something else I do all the time. This happened several times. I don't know what's causing it, but I think it has to do with SQR having run. Has anyone seen this? BTW, the only Oracle installed on this machine is the client for 8i. Thanks. Dave Donnelly ISIS BioComp phone (909) 677-2446 fax (781) 207-5533 From owner-sqr-users@list.iex.net Mon Jul 8 11:16:08 2002 Date: Mon, 8 Jul 2002 09:10:42 -0700 From: David Donnelly Subject: How does SQR on Windows accommodate different versions of Oracle? I notice that SQR 6.1.3 will work fine when the Windows (2k) machine on which it is running has either Oracle 8 or Oracle 8i installed. These are very different sets of software, and with my own applications, which use the Oracle Precompiler, I had to recompile and link with the two different versions of the precompiler to get my app to work with 8i. It occurs to me that perhaps 8i is backward compatible -- I didn't try that as I only have one client machine -- but does anyone know how this works? Thanks in advance. Dave Donnelly ISIS BioComp phone (909) 677-2446 fax (781) 207-5533 From owner-sqr-users@list.iex.net Mon Jul 8 22:19:51 2002 Date: Mon, 8 Jul 2002 22:16:18 -0500 From: Ray Ontko Subject: Re: How does SQR on Windows accommodate different versions of Oracle? Dave, > I notice that SQR 6.1.3 will work fine when the Windows (2k) machine on > which it is running has either Oracle 8 or Oracle 8i installed. > > These are very different sets of software, and with my own applications, > which use the Oracle Precompiler, I had to recompile and link with the two > different versions of the precompiler to get my app to work with 8i. > > It occurs to me that perhaps 8i is backward compatible -- I didn't try that > as I only have one client machine -- but does anyone know how this works? I'm not sure, exactly, but SQR uses an earlier interface to the oracle kernel than the one used by the pre-compilers. I think it's called the OCI (Oracle Call Interface). It may be that this interface doesn't require a re-link. Ray ---------------------------------------------------------------------- Ray Ontko rayo@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ From owner-sqr-users@list.iex.net Mon Jul 8 13:42:52 2002 Date: Mon, 8 Jul 2002 12:13:49 -0600 From: Brian Wiechman Subject: SQR not stopping when error occurs I am running an sqr that is encountering a unique constraint error. The log shows that it is going into the sqlerr.sqc and it should issue a stop command and stop the sqr from further processing. This is not the case. The sqr continues to log messages and will run to success and even update the process monitor status. Any suggestions? The log shows the normal error and stop request. Error on line 59: (SQR 3301) Program stopped by user request. SQR: Program Aborting. The sqr continues at this point and runs to success. It is as if the STOP command has no effect on the process. Any help would be greatly appreciated. Thanks. From owner-sqr-users@list.iex.net Mon Jul 8 13:35:51 2002 Date: Mon, 8 Jul 2002 13:18:31 -0500 From: "Wiechman, Brian - Contractor for Peoplesoft" Subject: SQR is not abending when it should. I am running an sqr that is encountering a unique constraint error. The log shows that it is going into the sqlerr.sqc and it should issue a stop command and stop the sqr from further processing. This is not the case. The sqr continues to log messages and will run to success and even update the process monitor status. Any suggestions? The log shows the normal error and stop request. Error on line 59: (SQR 3301) Program stopped by user request. SQR: Program Aborting. The sqr continues at this point and runs to success. It is as if the STOP command has not effect on the process. Any help would be greatly appreciated. Thanks. From owner-sqr-users@list.iex.net Mon Jul 8 15:50:17 2002 Date: Mon, 8 Jul 2002 15:46:21 -0500 From: "Ross, Steven" Subject: Move command not moving in SQR I have written a 'move' function in SQR that invokes the system move = command (Win2K) on a 'source' file(s) and 'target' directory. This works when = I run it from a command line, however, when I run it from and SQR, it moves = one file in the directory, then errs out. The return code that I'm getting = is "1" -- "incorrect function". Here's a snippet of code: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ !* Check for Windows Version !* if rtrim($_kc_WinVer,' ') =3D '' do kc-GetWindowsVersion end-if !* Make sure the source directory ends with a slash... !* if substr($Source_Directory,length($Source_Directory),1) <> '\' let $kc_tmp_s =3D '\' else let $kc_tmp_s =3D '' end-if !* Load command line !* let $kc_src_dir =3D $Source_Directory || $kc_tmp_s !* Determine command line by Windows Version... !* Evaluate $_kc_WinVer when =3D 'WinXP' when =3D 'Win2K' let $kc_cmd =3D getenv('COMSPEC') ||' /c move /y ' || = $kc_src_dir || $Files_To_Move || ' ' || $Target_Directory break when-other !* NT or other... let $kc_cmd =3D getenv('COMSPEC') ||' /c move ' || = $kc_src_dir || $Files_To_Move || ' ' || $Target_Directory break end-evaluate !* Make the system call to back up the files !* CALL SYSTEM USING $kc_cmd #ReturnValue WAIT !* On Windows systems, a non zero value indicates a failure. !* let $tmp_ReturnValue =3D '(' || ltrim(edit(#ReturnValue,'999'),' ') = || ')' if #ReturnValue <> 0 display ' Move: Failed' noline display $tmp_ReturnValue else display ' Move: Succeeded' noline display $tmp_ReturnValue end-if ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Okay, I know this is more than a snippet... Any help would be greatly appreciated. TIA, Steven Ross Applications Developer/Analyst sross@kcm.org =A0 From owner-sqr-users@list.iex.net Tue Jul 9 14:44:02 2002 Date: Tue, 9 Jul 2002 15:28:01 -0400 From: Andrew Lin Subject: any way to jump BACK a page? Hi all, Is there any command that allows you to jump back a page in SQR? I'm printing a report that displays columns of data with subtotals at the bottom of each column. ie Group1: Earnings Taxes 1 1 2 2 3 3 4 4 5 5 6 6 7 8 9 -- -- total A total B Every time I start a new group, I simply note the #current-line and then use the position command to jump back to the start of the column before printing the next column. The only problem is if one of the columns extends to the next page. Then my results end up looking like: Group 1 Earnings 1 2 3 ... 65 66 ------------------page break----------------- 67 68 69 -- Total A Taxes 1 2 3 4 5 -- Total B I'd like the SQR to be able to jump back to the original start page. Is this possible? Thanks! -Andrew From owner-sqr-users@list.iex.net Tue Jul 9 14:54:23 2002 Date: Tue, 9 Jul 2002 15:49:10 -0400 From: "Vanslette, Steve" Subject: Re: any way to jump BACK a page? Rather than try to fool sqr, I think I would approach this using several arrays to capture all the data until I knew the sub totals, then print the arrays side by side. Using a flag for each array to know when the last element of the array has been printed, and a sub-total for that column (array) has been printed, then set the flag to stop processing that column any further. This would allow you to print each page going forward without re-positioning or worrying about what page you were on now. I hope this helps. -----Original Message----- From: Andrew Lin [mailto:alin@TECHNEGLAS.COM] Sent: Tuesday, July 09, 2002 3:28 PM To: SQR-USERS@list.iex.net Subject: any way to jump BACK a page? Hi all, Is there any command that allows you to jump back a page in SQR? I'm printing a report that displays columns of data with subtotals at the bottom of each column. ie Group1: Earnings Taxes 1 1 2 2 3 3 4 4 5 5 6 6 7 8 9 -- -- total A total B Every time I start a new group, I simply note the #current-line and then use the position command to jump back to the start of the column before printing the next column. The only problem is if one of the columns extends to the next page. Then my results end up looking like: Group 1 Earnings 1 2 3 ... 65 66 ------------------page break----------------- 67 68 69 -- Total A Taxes 1 2 3 4 5 -- Total B I'd like the SQR to be able to jump back to the original start page. Is this possible? Thanks! -Andrew From owner-sqr-users@list.iex.net Tue Jul 9 15:26:53 2002 Date: Tue, 9 Jul 2002 13:13:56 -0700 From: "Lynds,Rick" Subject: Re: any way to jump BACK a page? As I understand it, an entire page is held in memory until going to the next pages forces it to flush. Once a page is flushed, there is no going back. I like Steve's response about doing the work in arrays for this sort of formatting. HTH Rick -----Original Message----- From: Andrew Lin [mailto:alin@TECHNEGLAS.COM] Sent: Tuesday, July 09, 2002 12:28 PM To: SQR-USERS@list.iex.net Subject: any way to jump BACK a page? Hi all, Is there any command that allows you to jump back a page in SQR? I'm printing a report that displays columns of data with subtotals at the bottom of each column. ie Group1: Earnings Taxes 1 1 2 2 3 3 4 4 5 5 6 6 7 8 9 -- -- total A total B Every time I start a new group, I simply note the #current-line and then use the position command to jump back to the start of the column before printing the next column. The only problem is if one of the columns extends to the next page. Then my results end up looking like: Group 1 Earnings 1 2 3 ... 65 66 ------------------page break----------------- 67 68 69 -- Total A Taxes 1 2 3 4 5 -- Total B I'd like the SQR to be able to jump back to the original start page. Is this possible? Thanks! -Andrew From owner-sqr-users@list.iex.net Tue Jul 9 15:56:17 2002 Date: Tue, 9 Jul 2002 16:49:07 -0400 From: Andrew Lin Subject: Re: any way to jump BACK a page? Rick, Steve, thanks for your pearls of wisdom. I was hoping I wouldn't have to switch to arrays since that would mean recoding quite a bit. Guess it's either that or force the user to deal with really ugly reports :) . Thanks. -Andrew >>> rlynds@MWDH2O.COM 07/09/02 04:13PM >>> As I understand it, an entire page is held in memory until going to the next pages forces it to flush. Once a page is flushed, there is no going back. I like Steve's response about doing the work in arrays for this sort of formatting. HTH Rick -----Original Message----- From: Andrew Lin [mailto:alin@TECHNEGLAS.COM] Sent: Tuesday, July 09, 2002 12:28 PM To: SQR-USERS@list.iex.net Subject: any way to jump BACK a page? Hi all, Is there any command that allows you to jump back a page in SQR? I'm printing a report that displays columns of data with subtotals at the bottom of each column. ie Group1: Earnings Taxes 1 1 2 2 3 3 4 4 5 5 6 6 7 8 9 -- -- total A total B Every time I start a new group, I simply note the #current-line and then use the position command to jump back to the start of the column before printing the next column. The only problem is if one of the columns extends to the next page. Then my results end up looking like: Group 1 Earnings 1 2 3 ... 65 66 ------------------page break----------------- 67 68 69 -- Total A Taxes 1 2 3 4 5 -- Total B I'd like the SQR to be able to jump back to the original start page. Is this possible? Thanks! -Andrew From owner-sqr-users@list.iex.net Tue Jul 9 16:05:32 2002 Date: Tue, 9 Jul 2002 17:00:38 -0400 From: "Vanslette, Steve" Subject: Re: any way to jump BACK a page? Don't look at it as a recoding nightmare, rather an opportunity to improve a bad design and show off. Best of luck... -----Original Message----- From: Andrew Lin [mailto:alin@TECHNEGLAS.COM] Sent: Tuesday, July 09, 2002 4:49 PM To: SQR-USERS@list.iex.net Subject: Re: any way to jump BACK a page? Rick, Steve, thanks for your pearls of wisdom. I was hoping I wouldn't have to switch to arrays since that would mean recoding quite a bit. Guess it's either that or force the user to deal with really ugly reports :) . Thanks. -Andrew >>> rlynds@MWDH2O.COM 07/09/02 04:13PM >>> As I understand it, an entire page is held in memory until going to the next pages forces it to flush. Once a page is flushed, there is no going back. I like Steve's response about doing the work in arrays for this sort of formatting. HTH Rick -----Original Message----- From: Andrew Lin [mailto:alin@TECHNEGLAS.COM] Sent: Tuesday, July 09, 2002 12:28 PM To: SQR-USERS@list.iex.net Subject: any way to jump BACK a page? Hi all, Is there any command that allows you to jump back a page in SQR? I'm printing a report that displays columns of data with subtotals at the bottom of each column. ie Group1: Earnings Taxes 1 1 2 2 3 3 4 4 5 5 6 6 7 8 9 -- -- total A total B Every time I start a new group, I simply note the #current-line and then use the position command to jump back to the start of the column before printing the next column. The only problem is if one of the columns extends to the next page. Then my results end up looking like: Group 1 Earnings 1 2 3 ... 65 66 ------------------page break----------------- 67 68 69 -- Total A Taxes 1 2 3 4 5 -- Total B I'd like the SQR to be able to jump back to the original start page. Is this possible? Thanks! -Andrew From owner-sqr-users@list.iex.net Wed Jul 10 09:35:34 2002 Date: Wed, 10 Jul 2002 14:24:55 +0000 From: Elvis Pressly Subject: Re: any way to jump BACK a page? Andrew, Take a look at the PAY003/DDP003 sqrs that print the checks/advices. You will notice that you can really print anywhere within the page because the whole page layout is controlled. I have been doing a lot of page printing like yours and I don't really need arrays to control the whole page layout. I think the problem really lies in where to put the extra item that makes the page overflow. Do you want to print it to the next column or where? The only time I have to use arrays in printing is when I am controlling the page dynamically. i.e., printing open enrollments where pages can run from 2 to something. If you can tell me what to do with the extra items, then maybe I can be of help. >From: Andrew Lin >Reply-To: sqr-users@list.iex.net >To: SQR-USERS@list.iex.net >Subject: Re: any way to jump BACK a page? >Date: Tue, 9 Jul 2002 16:49:07 -0400 > >Rick, Steve, thanks for your pearls of wisdom. > >I was hoping I wouldn't have to switch to arrays since that would mean >recoding quite a bit. > >Guess it's either that or force the user to deal with really ugly >reports :) . > >Thanks. > >-Andrew > > > >>> rlynds@MWDH2O.COM 07/09/02 04:13PM >>> >As I understand it, an entire page is held in memory until going to the >next >pages forces it to flush. Once a page is flushed, there is no going >back. >I like Steve's response about doing the work in arrays for this sort >of >formatting. > >HTH > >Rick > >-----Original Message----- >From: Andrew Lin [mailto:alin@TECHNEGLAS.COM] >Sent: Tuesday, July 09, 2002 12:28 PM >To: SQR-USERS@list.iex.net >Subject: any way to jump BACK a page? > > >Hi all, > >Is there any command that allows you to jump back a page in SQR? > >I'm printing a report that displays columns of data with subtotals at >the bottom of each column. ie > >Group1: >Earnings Taxes >1 1 >2 2 >3 3 >4 4 >5 5 >6 6 >7 >8 >9 >-- -- >total A total B > >Every time I start a new group, I simply note the #current-line and >then use the position command to jump back to the start of the column >before printing the next column. The only problem is if one of the >columns extends to the next page. Then my results end up looking >like: > >Group 1 >Earnings >1 >2 >3 >... >65 >66 >------------------page break----------------- >67 >68 >69 >-- >Total A > Taxes > 1 > 2 > 3 > 4 > 5 > -- > Total B > >I'd like the SQR to be able to jump back to the original start page. >Is this possible? > >Thanks! > >-Andrew _________________________________________________________________ Join the world’s largest e-mail service with MSN Hotmail. http://www.hotmail.com From owner-sqr-users@list.iex.net Wed Jul 10 10:18:34 2002 Date: Wed, 10 Jul 2002 11:12:10 -0400 From: Andrew Lin Subject: Re: any way to jump BACK a page? > 3 > 4 > 5 > -- > Total B > >I'd like the SQR to be able to jump back to the original start page. >Is this possible? > >Thanks! > >-Andrew _________________________________________________________________ Join the worldÆs largest e-mail service with MSN Hotmail. http://www.hotmail.com From owner-sqr-users@list.iex.net Wed Jul 10 10:53:13 2002 Date: Wed, 10 Jul 2002 11:29:04 -0400 From: Scott Barnes Subject: Re: any way to jump BACK a page? Much in the same way SQR prints pages numbers in the format page 1 of 30 you can do this. What you will need to do is have temp variables that hold the total you want and one more to tell the procedure to skip printing when you run through the program the first time. If this is a fast program this should be no problem. Have you main procedure call the other procedures twice. around your calls to print procedures include code like this if $print_first = 'Y' add #col1 to #tot1 add #col2 to #tot2 ... else do print_my_data end-if in print_my_data add the line print #tot1 (,,) ... in the appropriate place. An alternative would be to use your database to get the totals, and just use a begin-select sum(col1) &tot1 from Table_one where ... end-select then print the &tot1 in the appropriate places. Just some thoughts, there are many ways you can do this!! Scotty At 11:12 AM 7/10/02, you wrote: > > > 3 > > 4 > > 5 > > -- > > Total B > > > >I'd like the SQR to be able to jump back to the original start page. > >Is this possible? > > > >Thanks! > > > >-Andrew > > > > >_________________________________________________________________ >Join the worldÆs largest e-mail service with MSN Hotmail. >http://www.hotmail.com ---------- Scott A Barnes Programmer/Analyst Wesleyan University North College 860-685-2807 ---------- Lottery: A tax on people who are bad at math! From owner-sqr-users@list.iex.net Wed Jul 10 10:07:58 2002 Date: Wed, 10 Jul 2002 10:56:26 -0400 From: Brian Fitzpatrick Subject: Report performance This is a follow on to a previous message. I have a large number of reports which are generated for which the query is identical except for a location, so I am trying to parameterize the query so the Oracle parser will only parse it once. The skeleton of the query is like this: begin-select ... from ... where.... and location in $locations By doing it this way, the SQL statement ends up with a :1 where $locations is and each time the query is executed after the first time, it wont need to be reparsed since Oracle will see it as the same query. But I dont understand why this wont work. I even tried hardcoding it like this: let $locations='''4632''' print $locations (+1,1) .. .. begin-select ... from ... where.... and location in $locations The print $locations confirms $locations does indeed contain '4632' But what blows me away is that the following (which seems identical) DOES WORK - can anyone explain why??? begin-select ... from ... where.... and location in '4632' Why does it work with the literal but not the string? Thanks for any insight. From owner-sqr-users@list.iex.net Wed Jul 10 10:21:44 2002 Date: Wed, 10 Jul 2002 17:01:11 +0200 From: "O'riordan, Paul (EI05)" Subject: Re: Report performance You don't have any Brackets around the Locations let $locations='''4632''' This should read let $locations='(' ||'"'||'4632'||'"'|| ')' Hope this helps Paul Honeywell Paul O'Riordan Honeywell Application Services Application Developer Industrial Park, Cork Road PeopleSoft Applications, Waterford Ireland +353-51-309856 Direct +353-51-309821 Fax +353-87-9083244 Mobile mailto:Paul.O'Riordan@honeywell.com -----Original Message----- From: Brian Fitzpatrick [mailto:FITZPBM@KELLYSERVICES.COM] Sent: 10 July 2002 15:56 To: SQR-USERS@list.iex.net Subject: Report performance This is a follow on to a previous message. I have a large number of reports which are generated for which the query is identical except for a location, so I am trying to parameterize the query so the Oracle parser will only parse it once. The skeleton of the query is like this: begin-select ... from ... where.... and location in $locations By doing it this way, the SQL statement ends up with a :1 where $locations is and each time the query is executed after the first time, it wont need to be reparsed since Oracle will see it as the same query. But I dont understand why this wont work. I even tried hardcoding it like this: let $locations='''4632''' print $locations (+1,1) .. .. begin-select ... from ... where.... and location in $locations The print $locations confirms $locations does indeed contain '4632' But what blows me away is that the following (which seems identical) DOES WORK - can anyone explain why??? begin-select ... from ... where.... and location in '4632' Why does it work with the literal but not the string? Thanks for any insight. From owner-sqr-users@list.iex.net Wed Jul 10 10:08:23 2002 Date: Wed, 10 Jul 2002 10:01:12 -0500 From: "Knapp, Richard" Subject: Re: Report performance Your string is missing the parens, no? Richard Knapp Database Programmer/Analyst Administrative Systems Project University of Missouri System 573-884-3157 knappr@umsystem.edu -----Original Message----- From: Brian Fitzpatrick [mailto:FITZPBM@KELLYSERVICES.COM] Sent: Wednesday, July 10, 2002 9:56 AM To: SQR-USERS@list.iex.net Subject: Report performance This is a follow on to a previous message. I have a large number of reports which are generated for which the query is identical except for a location, so I am trying to parameterize the query so the Oracle parser will only parse it once. The skeleton of the query is like this: begin-select ... from ... where.... and location in $locations By doing it this way, the SQL statement ends up with a :1 where $locations is and each time the query is executed after the first time, it wont need to be reparsed since Oracle will see it as the same query. But I dont understand why this wont work. I even tried hardcoding it like this: let $locations='''4632''' print $locations (+1,1) .. .. begin-select ... from ... where.... and location in $locations The print $locations confirms $locations does indeed contain '4632' But what blows me away is that the following (which seems identical) DOES WORK - can anyone explain why??? begin-select ... from ... where.... and location in '4632' Why does it work with the literal but not the string? Thanks for any insight. From owner-sqr-users@list.iex.net Wed Jul 10 12:27:59 2002 Date: Wed, 10 Jul 2002 12:21:54 -0500 From: Daniel Vandenberg Subject: Re: Report performance The string does not work because the string includes the quotes, so it does not match location in the database, which does not contain the quotes. The following statement would give the same results as the literal. let $locations='4632' If you are just selecting one location, your SQL should say location = $locations instead of location in $locations If you want $locations to be able to contain a list of several codes you could have the following: let $locations ='(''4632'', ''4633'', ''4634'')' begin-select ... from... where... and location in [$locations] However, if you put $locations in brackets like this, the SQL will contain the literals instead of a bind variable like :1, so it will be parsed every time. At 10:56 AM 7/10/2002 -0400, you wrote: >This is a follow on to a previous message. I have a large number of >reports which are generated for which the query is identical except for a >location, so I am trying to parameterize the query so the Oracle parser >will only parse it once. The skeleton of the query is like this: > >begin-select >... >from ... >where.... >and location in $locations > >By doing it this way, the SQL statement ends up with a :1 where $locations >is and each time the query is executed after the first time, it wont need >to be reparsed since Oracle will see it as the same query. > >But I dont understand why this wont work. I even tried hardcoding it like >this: > >let $locations='''4632''' >print $locations (+1,1) >.. >.. >begin-select >... >from ... >where.... >and location in $locations > >The print $locations confirms $locations does indeed contain '4632' > >But what blows me away is that the following (which seems identical) DOES >WORK - can anyone explain why??? > >begin-select >... >from ... >where.... >and location in '4632' > >Why does it work with the literal but not the string? Thanks for any >insight. -------------------------------------------------------------------------------- Daniel Vandenberg | Email : vandberg@uwosh.edu Applications Programming University of Wisconsin Oshkosh From owner-sqr-users@list.iex.net Wed Jul 10 10:28:19 2002 Date: Wed, 10 Jul 2002 11:22:03 -0400 From: Andrew Lin Subject: Resend: any way to jump BACK a page? Hey Elvis, My layout actually is very similar to PAY003. It reports almost the same info (a bit more), except by groups rather than individuals. Also, it reports both employEE and employER info. For example, for a particular pay period and particular business unit, the columns would altogether include:: EE earnings (earn code, count, hours, actual amount) EE taxes (state, type/locality, count, amount, taxable gross) ER taxes (state, type/locality, count, amount, taxable gross) EE Deductions (ded code, descr, count, amount) ER Deductions (ded code, descr, count, amount) Figures are broken out as specifically as one business unit over one pay period and as broadly as the entire company over a number of given periods. As you can guess, the report is already pretty crowded. The columns that tend to run over the most are EE taxes, EE Deductions and earnings. For the final tally (the entire company), the taxes alone tend to run over for several pages by themselves. Even if I were to rearrange the columns and allow the EE taxes to wrap to a second column, it would still likely require a second page at some point. Thanks for your feedback. Andrew From owner-sqr-users@list.iex.net Wed Jul 10 11:39:32 2002 Date: Wed, 10 Jul 2002 16:32:59 +0000 From: Elvis Pressly Subject: Re: Resend: any way to jump BACK a page? Andrew, I see your predicament here. This used to happen to me when I tried to accomodate user's whims. Now it either they have several reports or a line printer report that is up to 132 columns. Users have to understand that printing of informations are limited only to the size of available printing media. There is no know commercially available medium to print the size of a columnar pad. >From: Andrew Lin >Reply-To: sqr-users@list.iex.net >To: SQR-USERS@list.iex.net >Subject: Resend: any way to jump BACK a page? >Date: Wed, 10 Jul 2002 11:22:03 -0400 > >Hey Elvis, > >My layout actually is very similar to PAY003. It reports almost the >same info (a bit more), except by groups rather than individuals. Also, >it reports both employEE and employER info. > >For example, for a particular pay period and particular business unit, >the columns would altogether include:: >EE earnings (earn code, count, hours, actual amount) >EE taxes (state, type/locality, count, amount, taxable gross) >ER taxes (state, type/locality, count, amount, taxable gross) >EE Deductions (ded code, descr, count, amount) >ER Deductions (ded code, descr, count, amount) > >Figures are broken out as specifically as one business unit over one >pay period and as broadly as the entire company over a number of given >periods. > >As you can guess, the report is already pretty crowded. The columns >that tend to run over the most are EE taxes, EE Deductions and earnings. > For the final tally (the entire company), the taxes alone tend to run >over for several pages by themselves. Even if I were to rearrange the >columns and allow the EE taxes to wrap to a second column, it would >still likely require a second page at some point. > >Thanks for your feedback. > >Andrew _________________________________________________________________ Join the world’s largest e-mail service with MSN Hotmail. http://www.hotmail.com From owner-sqr-users@list.iex.net Wed Jul 10 11:54:23 2002 Date: Wed, 10 Jul 2002 11:48:17 -0500 From: "Ross, Steven" Subject: Update: Move command not moving in SQR I didn't notice any replies to this message, and I still have the = problem. I've now tested it both on the client, and on the server (running this = in PeopleSoft 7.5), and the problem is identical. Help! Please! Steven Original message: ----------------- I have written a 'move' function in SQR that invokes the system move = command (Win2K) on a 'source' file(s) and 'target' directory. This works when = I run it from a command line, however, when I run it from and SQR, it moves = one file in the directory, then errs out. The return code that I'm getting = is "1" -- "incorrect function". Here's a snippet of code: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ !* Check for Windows Version !* if rtrim($_kc_WinVer,' ') =3D '' do kc-GetWindowsVersion end-if !* Make sure the source directory ends with a slash... !* if substr($Source_Directory,length($Source_Directory),1) <> '\' let $kc_tmp_s =3D '\' else let $kc_tmp_s =3D '' end-if !* Load command line !* let $kc_src_dir =3D $Source_Directory || $kc_tmp_s !* Determine command line by Windows Version... !* Evaluate $_kc_WinVer when =3D 'WinXP' when =3D 'Win2K' let $kc_cmd =3D getenv('COMSPEC') ||' /c move /y ' || = $kc_src_dir || $Files_To_Move || ' ' || $Target_Directory break when-other !* NT or other... let $kc_cmd =3D getenv('COMSPEC') ||' /c move ' || = $kc_src_dir || $Files_To_Move || ' ' || $Target_Directory break end-evaluate !* Make the system call to back up the files !* CALL SYSTEM USING $kc_cmd #ReturnValue WAIT !* On Windows systems, a non zero value indicates a failure. !* let $tmp_ReturnValue =3D '(' || ltrim(edit(#ReturnValue,'999'),' ') = || ')' if #ReturnValue <> 0 display ' Move: Failed' noline display $tmp_ReturnValue else display ' Move: Succeeded' noline display $tmp_ReturnValue end-if ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Okay, I know this is more than a snippet... Any help would be greatly appreciated. TIA, Steven Ross Applications Developer/Analyst sross@kcm.org =A0 From owner-sqr-users@list.iex.net Wed Jul 10 12:16:40 2002 Date: Wed, 10 Jul 2002 10:20:27 -0700 From: Bill Winkfield Subject: Re: Update: Move command not moving in SQR Hi Steven, Here is a snippet of code that has been working for us for some time now. !********************************************************************** !* Archive Source File * !********************************************************************** Begin-Procedure Archive-Source-File let $comspec = getenv('comspec') let $cmdpath = $comspec || ' @start /min /wait cmd /c move ' let $command = $cmdpath #debuga Show 'Archive path in Archive-Source-File: ' $Path !// ! Source for move command. !-----------------------------------------------! let $command = $command || $SourceData || $DefaultBlank || $Path #debuga Show $command call system using $command #rtrn_status If #rtrn_status <> 0 !// ! PC/Windows: A value less than 32 ! indicates an error. !-----------------------------------------------! #debuga Show 'The command failed -- manual intervention required.' #debuga Show #rtrn_status let $log_msg = 'Archiving of ' || $SourceData || ' cannot be completed.' || $CrLf || 'Error status of ' || to_char(#rtrn_status) || ' received.' write {LOG_FILE} from $log_msg Else let $log_msg = 'Data archived sucessfully to ' || $Path || '.' write {LOG_FILE} from $log_msg End-If End-Procedure Archive-Source-File !********************************************************************** !* End of procedure. * !********************************************************************** Hope this helps, Bill -----Original Message----- From: Ross, Steven [mailto:sross@KCM.ORG] Sent: Wednesday, July 10, 2002 9:48 AM To: SQR-USERS@list.iex.net Subject: Update: Move command not moving in SQR I didn't notice any replies to this message, and I still have the problem. I've now tested it both on the client, and on the server (running this in PeopleSoft 7.5), and the problem is identical. Help! Please! Steven Original message: ----------------- I have written a 'move' function in SQR that invokes the system move command (Win2K) on a 'source' file(s) and 'target' directory. This works when I run it from a command line, however, when I run it from and SQR, it moves one file in the directory, then errs out. The return code that I'm getting is "1" -- "incorrect function". Here's a snippet of code: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ !* Check for Windows Version !* if rtrim($_kc_WinVer,' ') = '' do kc-GetWindowsVersion end-if !* Make sure the source directory ends with a slash... !* if substr($Source_Directory,length($Source_Directory),1) <> '\' let $kc_tmp_s = '\' else let $kc_tmp_s = '' end-if !* Load command line !* let $kc_src_dir = $Source_Directory || $kc_tmp_s !* Determine command line by Windows Version... !* Evaluate $_kc_WinVer when = 'WinXP' when = 'Win2K' let $kc_cmd = getenv('COMSPEC') ||' /c move /y ' || $kc_src_dir || $Files_To_Move || ' ' || $Target_Directory break when-other !* NT or other... let $kc_cmd = getenv('COMSPEC') ||' /c move ' || $kc_src_dir || $Files_To_Move || ' ' || $Target_Directory break end-evaluate !* Make the system call to back up the files !* CALL SYSTEM USING $kc_cmd #ReturnValue WAIT !* On Windows systems, a non zero value indicates a failure. !* let $tmp_ReturnValue = '(' || ltrim(edit(#ReturnValue,'999'),' ') || ')' if #ReturnValue <> 0 display ' Move: Failed' noline display $tmp_ReturnValue else display ' Move: Succeeded' noline display $tmp_ReturnValue end-if ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Okay, I know this is more than a snippet... Any help would be greatly appreciated. TIA, Steven Ross Applications Developer/Analyst sross@kcm.org From owner-sqr-users@list.iex.net Wed Jul 10 14:58:25 2002 Date: Wed, 10 Jul 2002 14:49:42 -0500 From: "Ross, Steven" Subject: Re: Update: Move command not moving in SQR Thanks, Bill. I will try this. Steven Ross Applications Developer/Analyst sross@kcm.org =A0 -----Original Message----- From: Bill Winkfield [mailto:bwinkfield@GIA.EDU] Sent: Wednesday, July 10, 2002 12:20 PM To: SQR-USERS@list.iex.net Subject: Re: Update: Move command not moving in SQR Hi Steven, Here is a snippet of code that has been working for us for some time = now. !********************************************************************** !* Archive Source File * !********************************************************************** Begin-Procedure Archive-Source-File let $comspec =3D getenv('comspec') let $cmdpath =3D $comspec || ' @start /min /wait cmd /c move ' let $command =3D $cmdpath #debuga Show 'Archive path in Archive-Source-File: ' $Path !// ! Source for move command. !-----------------------------------------------! let $command =3D $command || $SourceData || $DefaultBlank || $Path #debuga Show $command call system using $command #rtrn_status If #rtrn_status <> 0 !// ! PC/Windows: A value less than 32 ! indicates an error. !-----------------------------------------------! #debuga Show 'The command failed -- manual intervention required.' #debuga Show #rtrn_status let $log_msg =3D 'Archiving of ' || $SourceData || ' cannot be completed.' || $CrLf || 'Error status of ' || to_char(#rtrn_status) || ' received.' write {LOG_FILE} from $log_msg Else let $log_msg =3D 'Data archived sucessfully to ' || $Path || '.' write {LOG_FILE} from $log_msg End-If End-Procedure Archive-Source-File !********************************************************************** !* End of procedure. * !********************************************************************** Hope this helps, Bill -----Original Message----- From: Ross, Steven [mailto:sross@KCM.ORG] Sent: Wednesday, July 10, 2002 9:48 AM To: SQR-USERS@list.iex.net Subject: Update: Move command not moving in SQR I didn't notice any replies to this message, and I still have the = problem. I've now tested it both on the client, and on the server (running this = in PeopleSoft 7.5), and the problem is identical. Help! Please! Steven Original message: ----------------- I have written a 'move' function in SQR that invokes the system move = command (Win2K) on a 'source' file(s) and 'target' directory. This works when = I run it from a command line, however, when I run it from and SQR, it moves = one file in the directory, then errs out. The return code that I'm getting = is "1" -- "incorrect function". Here's a snippet of code: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ !* Check for Windows Version !* if rtrim($_kc_WinVer,' ') =3D '' do kc-GetWindowsVersion end-if !* Make sure the source directory ends with a slash... !* if substr($Source_Directory,length($Source_Directory),1) <> '\' let $kc_tmp_s =3D '\' else let $kc_tmp_s =3D '' end-if !* Load command line !* let $kc_src_dir =3D $Source_Directory || $kc_tmp_s !* Determine command line by Windows Version... !* Evaluate $_kc_WinVer when =3D 'WinXP' when =3D 'Win2K' let $kc_cmd =3D getenv('COMSPEC') ||' /c move /y ' || = $kc_src_dir || $Files_To_Move || ' ' || $Target_Directory break when-other !* NT or other... let $kc_cmd =3D getenv('COMSPEC') ||' /c move ' || = $kc_src_dir || $Files_To_Move || ' ' || $Target_Directory break end-evaluate !* Make the system call to back up the files !* CALL SYSTEM USING $kc_cmd #ReturnValue WAIT !* On Windows systems, a non zero value indicates a failure. !* let $tmp_ReturnValue =3D '(' || ltrim(edit(#ReturnValue,'999'),' ') = || ')' if #ReturnValue <> 0 display ' Move: Failed' noline display $tmp_ReturnValue else display ' Move: Succeeded' noline display $tmp_ReturnValue end-if ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Okay, I know this is more than a snippet... Any help would be greatly appreciated. TIA, Steven Ross Applications Developer/Analyst sross@kcm.org From owner-sqr-users@list.iex.net Wed Jul 10 16:08:17 2002 Date: Wed, 10 Jul 2002 15:49:59 -0500 From: "Sengupta, Rajib (CC-Contractor)" Subject: questions about SQR I have couple of questions regarding SQR's version a) what is the latest version of SQR b) how old is SQR version 4.3- whether this version will run in windows 2000 or not? Do anyone of you have experience of running SQR version 4.3 in windows 2000. Rajib From owner-sqr-users@list.iex.net Wed Jul 10 16:28:30 2002 Date: Wed, 10 Jul 2002 16:13:47 -0500 From: Thomas Kupka Subject: Re: questions about SQR We are using SQR version 4.3.4 In order to get it to work on the Windows 2000 platform, you have to delete a key in you registry. Open "regedit" and do a search for "SQR". Delete any registry keys that fall under the AppCompatibility node. This will allow you to run your SQR's without problem. If you are unfamiliar with editing the registry, you may want to get help from someone with more experience. Editing the registry can have fatal consequences if you delete the wrong keys. Hope this helps, and good luck. Tom Kupka DePaul University Information and Application Support tkupka@depaul.edu >>> Rajib.Sengupta@CONAGRAFOODS.COM 7/10/02 3:49:59 PM >>> I have couple of questions regarding SQR's version a) what is the latest version of SQR b) how old is SQR version 4.3- whether this version will run in windows 2000 or not? Do anyone of you have experience of running SQR version 4.3 in windows 2000. Rajib From owner-sqr-users@list.iex.net Wed Jul 10 16:53:15 2002 Date: Wed, 10 Jul 2002 14:48:46 -0700 From: Allen Cunningham Subject: Re: questions about SQR There is a better, 'PeopleSoft-Approved' fix for running SQR 4.3.4 under Win 2K that does not involve having to mess with the registry. See my upload at http://groups.yahoo.com/group/peoplesoft/files in a file named "sqr_fix.doc" ac -----Original Message----- From: Discussion of SQR, Brio Software's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Thomas Kupka Sent: Wednesday, July 10, 2002 2:14 PM To: SQR-USERS@list.iex.net Subject: Re: questions about SQR We are using SQR version 4.3.4 In order to get it to work on the Windows 2000 platform, you have to delete a key in you registry. Open "regedit" and do a search for "SQR". Delete any registry keys that fall under the AppCompatibility node. This will allow you to run your SQR's without problem. If you are unfamiliar with editing the registry, you may want to get help from someone with more experience. Editing the registry can have fatal consequences if you delete the wrong keys. Hope this helps, and good luck. Tom Kupka DePaul University Information and Application Support tkupka@depaul.edu >>> Rajib.Sengupta@CONAGRAFOODS.COM 7/10/02 3:49:59 PM >>> I have couple of questions regarding SQR's version a) what is the latest version of SQR b) how old is SQR version 4.3- whether this version will run in windows 2000 or not? Do anyone of you have experience of running SQR version 4.3 in windows 2000. Rajib From owner-sqr-users@list.iex.net Wed Jul 10 16:21:54 2002 Date: Wed, 10 Jul 2002 16:17:55 -0500 From: "Knapp, Richard" Subject: Re: questions about SQR Yes, I am running 4.3 on a W2K machine. No problems. No problems with NT 4 either. Richard Knapp Database Programmer/Analyst Administrative Systems Project University of Missouri System 573-884-3157 knappr@umsystem.edu -----Original Message----- From: Sengupta, Rajib (CC-Contractor) [mailto:Rajib.Sengupta@CONAGRAFOODS.COM] Sent: Wednesday, July 10, 2002 3:50 PM To: SQR-USERS@list.iex.net Subject: questions about SQR I have couple of questions regarding SQR's version a) what is the latest version of SQR b) how old is SQR version 4.3- whether this version will run in windows 2000 or not? Do anyone of you have experience of running SQR version 4.3 in windows 2000. Rajib From owner-sqr-users@list.iex.net Wed Jul 10 16:35:29 2002 Date: Wed, 10 Jul 2002 16:32:12 -0500 From: the dragon Subject: Re: questions about SQR The latest version of sqr is 6.x SQR v4.3.4 will not run in W2K, because of a bug that will not allow it to write to c:\temp. ----Original Message Follows---- I have couple of questions regarding SQR's version a) what is the latest version of SQR b) how old is SQR version 4.3- whether this version will run in windows 2000 or not? Do anyone of you have experience of running SQR version 4.3 in windows 2000. Rajib PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away. "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." Carl Sandburg (1878 - 1967) NOTICE: The information contained in this message is protected under United States and international copyright laws and is intended for the personal and confidential use of the addressee. If you are not the addressee or an agent of the addressee, you are hereby notified that you have received this message in error and that any review, dissemination, distribution, taking of any action in reliance upon or copying of this message without the express written permission of the sender or his agent is strictly prohibited and in violation of referenced statutes. If you have received this message in error, please notify me (the sender) immediately and you are instructed to delete this message from any computer it resides on, including mail servers. SIGNATURE : Do not meddle in the affairs of dragons, for you are crunchy and taste good with ketchup. You've got to be a Spirit; don't need no Ghosts. Do or Do Not; There is NO Try. Any clod can have facts, but having an opinion is an art ! A conclusion is simply the place where someone got tired of thinking. When a continuing state of defiance and disorder can not be checked by the rules, then new and sometimes drastic rules must be forged to maintain order; the alternative is anarchy. M. Clark Willis III - Sr Systems Analyst, Sr Consultant ceprn@hotmail.com aolim:ceprn ICQ 6633136 Help stamp out SPAM!! http://www.cauce.org The senders of unsolicited commercial e-mail to me at any site are liable for a $500 per message cost under USC 47, which may be found at http://www.law.cornell.edu/uscode/47 Contents of this message, in it's entirety, (c) 2000 M. Clark Willis III / Dragon Enterprises inc. All rights reserved. _________________________________________________________________ Join the world’s largest e-mail service with MSN Hotmail. http://www.hotmail.com From owner-sqr-users@list.iex.net Wed Jul 10 16:50:24 2002 Date: Wed, 10 Jul 2002 14:44:50 -0700 From: Allen Cunningham Subject: Re: questions about SQR SQR 4.3.4 WILL run on Win 2K with a simple 'PeopleSoft-Approved' patch. ac -----Original Message----- From: Discussion of SQR, Brio Software's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of the dragon Sent: Wednesday, July 10, 2002 2:32 PM To: SQR-USERS@list.iex.net Subject: Re: questions about SQR The latest version of sqr is 6.x SQR v4.3.4 will not run in W2K, because of a bug that will not allow it to write to c:\temp. ----Original Message Follows---- I have couple of questions regarding SQR's version a) what is the latest version of SQR b) how old is SQR version 4.3- whether this version will run in windows 2000 or not? Do anyone of you have experience of running SQR version 4.3 in windows 2000. Rajib PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away. "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." Carl Sandburg (1878 - 1967) NOTICE: The information contained in this message is protected under United States and international copyright laws and is intended for the personal and confidential use of the addressee. If you are not the addressee or an agent of the addressee, you are hereby notified that you have received this message in error and that any review, dissemination, distribution, taking of any action in reliance upon or copying of this message without the express written permission of the sender or his agent is strictly prohibited and in violation of referenced statutes. If you have received this message in error, please notify me (the sender) immediately and you are instructed to delete this message from any computer it resides on, including mail servers. SIGNATURE : Do not meddle in the affairs of dragons, for you are crunchy and taste good with ketchup. You've got to be a Spirit; don't need no Ghosts. Do or Do Not; There is NO Try. Any clod can have facts, but having an opinion is an art ! A conclusion is simply the place where someone got tired of thinking. When a continuing state of defiance and disorder can not be checked by the rules, then new and sometimes drastic rules must be forged to maintain order; the alternative is anarchy. M. Clark Willis III - Sr Systems Analyst, Sr Consultant ceprn@hotmail.com aolim:ceprn ICQ 6633136 Help stamp out SPAM!! http://www.cauce.org The senders of unsolicited commercial e-mail to me at any site are liable for a $500 per message cost under USC 47, which may be found at http://www.law.cornell.edu/uscode/47 Contents of this message, in it's entirety, (c) 2000 M. Clark Willis III / Dragon Enterprises inc. All rights reserved. _________________________________________________________________ Join the world’s largest e-mail service with MSN Hotmail. http://www.hotmail.com From owner-sqr-users@list.iex.net Wed Jul 10 17:11:23 2002 Date: Wed, 10 Jul 2002 17:01:47 -0500 From: "Sengupta, Rajib (CC-Contractor)" Su