From sqr-users-admin@sqrug.org Wed Oct 1 00:02:57 2003 Date: Wed, 1 Oct 2003 00:00:02 -0500 From: Nathan Stratton Treadway Subject: [sqr-users] Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Stratton Treadway, Ray Ontko & Co. (nathanst@ontko.com) $Revision: 1.14 $ $Date: 2002-11-13 12:55:07-05 $ This document gathers in one location information about the sqr-users mail list. It includes instructions for changing your subscription settings (including unsubscribing), as well are guidelines for using the list. ______________________________________________________________________ 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. Interacting with the list server 3.1 Subscribing to the list 3.2 Unsubscribing from the list 3.3 Digests 3.4 Obtaining your password 3.5 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 2002/04/17: o Reflect the new location of the list and the new list server software being used. 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 Software (and to other products in the SQR product suite). Note: SQR used to be published by SQRIBE Technologies, which was merged into Brio Technology on August 3, 1999. Brio Technology changed its name to Brio Software in October 2001. SQR and related applications have also been called "Brio.Report" and "Brio Reports". 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.org/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 Here are some mailing lists that cover topics relating to SQR in some way. 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 SQR and SQR Developer (formerly called Brio.Report and Report Builder). To join, follow the link above, or send any message to "BrioSQRReport- subscribe@yahoogroups.com". rm-users : Report- Mart 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.org". 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. briousers : Brio.Enter- prise Users List for discussion of the Brio.Enterprise (now called Intelligence) product. To join, follow the link above, or send any message to "briousers-subscribe@yahoogroups.com". 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. Interacting with the list server The sqr-users mailing list is hosted using the MailMan list server package. All of the MailMan functions described below are accessed from the sqr-users List Info page, "http://www.sqrug.org/mailman/listinfo/sqr-users". 3.1. Subscribing to the list To subscribe to the mailing list, enter your e-mail address in the box at the top of the "Subscribing to sqr-users" section on the sqr-users List Info page, then choose a password to assign to your subscription and enter it in both "Password" boxes. This password is used to allow you to securely make changes to your subscription over the web. This password will be e-mailed back to you at varous times, so you should not use the same password as you are using elsewhere. Once you have filled in the password, click the "Subscribe" button. You should soon receive an e-mail message from the list server requesting confirmation of your subscription request. Once you have replied to this message and your subscription has been processed, you will receive a "welcome" message giving you more information on the list server, and as well as a reminder of the subscription password you chose. 3.2. Unsubscribing from the list To unsubscribe from the list, go to the sqr-users List Info page, enter your e-mail address in the "sqr-users Subscribers" section, and click the "Edit Options" button. In the page that comes up, there will be an "Unsubscribing from sqr-users" section; simply enter your password in the box there and click the "Unsubscribe" button. Assuming you enter the correct password, you will recieve a message stating "You have been unsubscribed." and the removal will take effect immediately (there is no need to send in a confirmation e-mail message). (If you have forgotton your password, see the instructions below.) 3.3. Digests You can request to get your messages in digest form (one large message containing all of the posts made each day). To do this, enter your address in the "sqr-users Subscribers" section of the sqr-users List Info page and click the "Edit Options" button. At the bottom of the resulting page, you will see a section titled "Your sqr-users Subscription Options". Change the "Set Digest Mode" option to "On", enter your subscription password in the box provided, and click the "Submit My Changes" button. (If you have forgotton your password, see the instructions below.) You actually have a choice of two different digest formats, "MIME" or "Plain Text". The MIME format sends each individual list message as a separate MIME attachment to the digest; the plain text format consists of one large text message, with all of the individual messages run together. Which format you will want to use depends on your mail- reading software (and personal preference). If you are not sure which format will work best, you can just leave the default setting of "MIME" and see if the next morning's digest is easy to read in your mail reader -- you can change to the other format at any time using that same web page. 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. To restore normal distribution, simply follow the above instructions choosing the "Off" setting for the Digest option. 3.4. Obtaining your password You will not be able to unsubscribe or change your subscription options without your password. If you have forgotten your password, simply click the "Email My Password to Me" button on the Edit Options page and your password will be e-mailed to you. You will also be e- mailed a copy of your password at the beginning of each month, as part of a "mailing list memberships reminder" message from the MailMan software. 3.5. Problems If the above instructions do not work, please contact the list manager at "sqr-users-manager@sqrug.org". (Do not send a message to the list itself -- no one there can help you with your problem!) Please include a description of what you were trying to do and what exactly happened when you made the attempt. 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.) The list server will delete these special attachments from your message and send you a warning notice. 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. o ignore abusive and inappropriate messages that get sent to the list, rather than getting drawn into a debate. The very nature of these messages will cause most subscribers to automatically discredit them -- no "response" is necessary. To the contrary, any response will only "add fule to the fire," prolonging the controversy and further interupting the normal conversation on the list. 4.2. Posting a Message Once you are subscribed, you can post to the list by sending a message to "sqr-users@sqrug.org". 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 from 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 this infor- mation for your own environment. 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.) 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. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 08:37:31 2003 From: "Ottaway, Eric (PS, GNF, Contractor)" Date: Wed, 1 Oct 2003 09:35:39 -0400 Subject: [sqr-users] Formatting $CURRENT-DATE Does anyone know how to format the $CURRENT-DATE predefined variable to something like 'MM/DD/YY:HH:MI:SS'? Thanks in advance, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 09:12:09 2003 From: "Schelske, Steve K." Subject: RE: [sqr-users] Formatting $CURRENT-DATE Date: Wed, 1 Oct 2003 10:11:42 -0400 Its not exactly what you asked for, but here's another option let $ReportDate = datetostr(datenow(),'MM/DD/YYYY') let $ReportTime = datetostr(datenow(),'HH:MI:SS') You probably can put the formatting together into one variable. -----Original Message----- From: Ottaway, Eric (PS, GNF, Contractor) [mailto:Eric.Ottaway@gnf.com] Sent: Wednesday, October 01, 2003 9:36 AM To: 'sqr-users@sqrug.org' Subject: [sqr-users] Formatting $CURRENT-DATE Does anyone know how to format the $CURRENT-DATE predefined variable to something like 'MM/DD/YY:HH:MI:SS'? Thanks in advance, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 09:19:06 2003 Date: Thu, 02 Oct 2003 00:18:59 +1000 From: Steve Cavill Subject: RE: [sqr-users] Formatting $CURRENT-DATE Hi Eric, $current-date is a standard SQR date variable, so you can format it using edit masks like any date, either with the print command or the edit function e.g. print $current-date (1,1) edit dd/mm/yy:hh:mi:ss let $textdate=edit($current-date,'dd/mm/yy:hh:mi:ss') print $textdate (2,1) Cheers, Steve. -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Ottaway, Eric (PS, GNF, Contractor) Sent: Wednesday, 1 October 2003 11:36 PM To: 'sqr-users@sqrug.org' Subject: [sqr-users] Formatting $CURRENT-DATE Does anyone know how to format the $CURRENT-DATE predefined variable to something like 'MM/DD/YY:HH:MI:SS'? Thanks in advance, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 13:11:40 2003 From: "Ottaway, Eric (PS, GNF, Contractor)" Date: Wed, 1 Oct 2003 14:08:38 -0400 Subject: [sqr-users] String to Date format Hey guys and gals, I'm still having trouble trying to format a string ('06/05/2003 01:03:00) into a date with the same format. Anyone have any ideas on how to do this? I've tried using a to_date function and a strtodate function. Both failed. Thanks, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 13:34:12 2003 Date: Wed, 01 Oct 2003 13:36:24 -0500 From: "Mike Eischen" Subject: Re: [sqr-users] String to Date format Hi Eric, With strtodate you need to use a format mask which indicates the date format you want the strtodate function to return, and the format masks vary depending on what database you're using. What database are you using? Can you include a code sample showing the syntax? Thanks, Mike >>> Eric.Ottaway@gnf.com 10/01/03 01:08PM >>> Hey guys and gals, I'm still having trouble trying to format a string ('06/05/2003 01:03:00) into a date with the same format. Anyone have any ideas on how to do this? I've tried using a to_date function and a strtodate function. Both failed. Thanks, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 13:38:04 2003 Subject: RE: [sqr-users] String to Date format Date: Wed, 1 Oct 2003 13:37:00 -0500 From: "Knapp, Richard" I'm guessing the the date part is month/day so the format string for your example would be: 'MM/DD/YYYY HH:MI:SS' Do you not have access to any SQR documentation? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: Ottaway, Eric (PS, GNF, Contractor) [mailto:Eric.Ottaway@gnf.com] Sent: Wednesday, October 01, 2003 1:09 PM To: 'sqr-users@sqrug.org' Subject: [sqr-users] String to Date format Hey guys and gals, I'm still having trouble trying to format a string ('06/05/2003 01:03:00) into a date with the same format. Anyone have any ideas on how to do this? I've tried using a to_date function and a strtodate function. Both failed. Thanks, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 13:41:07 2003 From: "Ottaway, Eric (PS, GNF, Contractor)" Subject: RE: [sqr-users] String to Date format Date: Wed, 1 Oct 2003 14:39:51 -0400 !**************************************************************** BEGIN-PROCEDURE get_last_time BEGIN-SELECT to_date(maxvars.varvalue,'MM/DD/YYYY HH:MI:SS') &lastdate FROM [$schema]maxvars WHERE maxvars.varname = 'GNF_RCPT_TIMESTAMP' END-SELECT END-PROCEDURE !**************************************************************** BEGIN-PROCEDURE check_date ! LET $lastdate = strtodate($lastdate,'MM/DD/YYYY HH:MI:SS') PRINT $lastdate (+10,1)!edit 'MM/DD/YYYY HH:MI:SS' !***************TESTING BEGIN-SELECT DISTINCT MAX_RCV_INTERFACE.PONUM &currPO MAX_RCV_INTERFACE.IMPORTTIMESTAMP &currdates MOVE &currPO TO $currPO MOVE &currdates TO $currdates :$DATEMASK LET $currdates = strtodate($currdates,'MM/DD/YYYY') PRINT $currdates (+1,1)!edit 'MM/DD/YYYY HH:MI:SS' !***************TESTING NEW-PAGE ! LET #diffdate = datediff($currdates,$lastdate,'SECOND') ! SHOW 'CurrDates = ' $currdates ! SHOW 'LastDate = ' $lastdate ! IF #diffdate > 0 ! DO Get_Receiving_Ticket_Data ! END-IF -----Original Message----- From: Mike Eischen [mailto:MEISCHEN@genesco.com] Sent: Wednesday, October 01, 2003 2:36 PM To: sqr-users@sqrug.org Subject: Re: [sqr-users] String to Date format Hi Eric, With strtodate you need to use a format mask which indicates the date format you want the strtodate function to return, and the format masks vary depending on what database you're using. What database are you using? Can you include a code sample showing the syntax? Thanks, Mike >>> Eric.Ottaway@gnf.com 10/01/03 01:08PM >>> Hey guys and gals, I'm still having trouble trying to format a string ('06/05/2003 01:03:00) into a date with the same format. Anyone have any ideas on how to do this? I've tried using a to_date function and a strtodate function. Both failed. Thanks, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 13:47:08 2003 From: Jim McKeever Subject: RE: [sqr-users] String to Date format Date: Wed, 1 Oct 2003 12:40:45 -0600 Could the problem be that you have &lastdate in your first procedure, but then you reference $lastdate when you print? -----Original Message----- From: Ottaway, Eric (PS, GNF, Contractor) [mailto:Eric.Ottaway@gnf.com] Sent: Wednesday, October 01, 2003 12:40 PM To: 'sqr-users@sqrug.org' Subject: RE: [sqr-users] String to Date format !**************************************************************** BEGIN-PROCEDURE get_last_time BEGIN-SELECT to_date(maxvars.varvalue,'MM/DD/YYYY HH:MI:SS') &lastdate FROM [$schema]maxvars WHERE maxvars.varname = 'GNF_RCPT_TIMESTAMP' END-SELECT END-PROCEDURE !**************************************************************** BEGIN-PROCEDURE check_date ! LET $lastdate = strtodate($lastdate,'MM/DD/YYYY HH:MI:SS') PRINT $lastdate (+10,1)!edit 'MM/DD/YYYY HH:MI:SS' !***************TESTING BEGIN-SELECT DISTINCT MAX_RCV_INTERFACE.PONUM &currPO MAX_RCV_INTERFACE.IMPORTTIMESTAMP &currdates MOVE &currPO TO $currPO MOVE &currdates TO $currdates :$DATEMASK LET $currdates = strtodate($currdates,'MM/DD/YYYY') PRINT $currdates (+1,1)!edit 'MM/DD/YYYY HH:MI:SS' !***************TESTING NEW-PAGE ! LET #diffdate = datediff($currdates,$lastdate,'SECOND') ! SHOW 'CurrDates = ' $currdates ! SHOW 'LastDate = ' $lastdate ! IF #diffdate > 0 ! DO Get_Receiving_Ticket_Data ! END-IF -----Original Message----- From: Mike Eischen [mailto:MEISCHEN@genesco.com] Sent: Wednesday, October 01, 2003 2:36 PM To: sqr-users@sqrug.org Subject: Re: [sqr-users] String to Date format Hi Eric, With strtodate you need to use a format mask which indicates the date format you want the strtodate function to return, and the format masks vary depending on what database you're using. What database are you using? Can you include a code sample showing the syntax? Thanks, Mike >>> Eric.Ottaway@gnf.com 10/01/03 01:08PM >>> Hey guys and gals, I'm still having trouble trying to format a string ('06/05/2003 01:03:00) into a date with the same format. Anyone have any ideas on how to do this? I've tried using a to_date function and a strtodate function. Both failed. Thanks, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 13:52:07 2003 From: "Ottaway, Eric (PS, GNF, Contractor)" Subject: RE: [sqr-users] String to Date format Date: Wed, 1 Oct 2003 14:51:10 -0400 Thanks for the quick responses "SQR in Peoplesoft" has been of little help. The string looks like a date in the table I reference, but is not in fact a DATE. Therefore, I can't use datediff() to return a value. So I'm trying to basically keep the string as it is, but as a date instead of a string. Convert this into a date of the same format(String = 01/01/2001 01:15:00). Eric -----Original Message----- From: Knapp, Richard [mailto:KnappR@umsystem.edu] Sent: Wednesday, October 01, 2003 2:37 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] String to Date format I'm guessing the the date part is month/day so the format string for your example would be: 'MM/DD/YYYY HH:MI:SS' Do you not have access to any SQR documentation? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: Ottaway, Eric (PS, GNF, Contractor) [mailto:Eric.Ottaway@gnf.com] Sent: Wednesday, October 01, 2003 1:09 PM To: 'sqr-users@sqrug.org' Subject: [sqr-users] String to Date format Hey guys and gals, I'm still having trouble trying to format a string ('06/05/2003 01:03:00) into a date with the same format. Anyone have any ideas on how to do this? I've tried using a to_date function and a strtodate function. Both failed. Thanks, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 13:54:08 2003 From: "Ottaway, Eric (PS, GNF, Contractor)" Subject: RE: [sqr-users] String to Date format Date: Wed, 1 Oct 2003 14:52:33 -0400 Hey Jim, &lastdate is moved between procedure calls. I just didn't show that part of the code. Eric -----Original Message----- From: Jim McKeever [mailto:JMcKeever@fastaff.com] Sent: Wednesday, October 01, 2003 2:41 PM To: 'sqr-users@sqrug.org' Subject: RE: [sqr-users] String to Date format Could the problem be that you have &lastdate in your first procedure, but then you reference $lastdate when you print? -----Original Message----- From: Ottaway, Eric (PS, GNF, Contractor) [mailto:Eric.Ottaway@gnf.com] Sent: Wednesday, October 01, 2003 12:40 PM To: 'sqr-users@sqrug.org' Subject: RE: [sqr-users] String to Date format !**************************************************************** BEGIN-PROCEDURE get_last_time BEGIN-SELECT to_date(maxvars.varvalue,'MM/DD/YYYY HH:MI:SS') &lastdate FROM [$schema]maxvars WHERE maxvars.varname = 'GNF_RCPT_TIMESTAMP' END-SELECT END-PROCEDURE !**************************************************************** BEGIN-PROCEDURE check_date ! LET $lastdate = strtodate($lastdate,'MM/DD/YYYY HH:MI:SS') PRINT $lastdate (+10,1)!edit 'MM/DD/YYYY HH:MI:SS' !***************TESTING BEGIN-SELECT DISTINCT MAX_RCV_INTERFACE.PONUM &currPO MAX_RCV_INTERFACE.IMPORTTIMESTAMP &currdates MOVE &currPO TO $currPO MOVE &currdates TO $currdates :$DATEMASK LET $currdates = strtodate($currdates,'MM/DD/YYYY') PRINT $currdates (+1,1)!edit 'MM/DD/YYYY HH:MI:SS' !***************TESTING NEW-PAGE ! LET #diffdate = datediff($currdates,$lastdate,'SECOND') ! SHOW 'CurrDates = ' $currdates ! SHOW 'LastDate = ' $lastdate ! IF #diffdate > 0 ! DO Get_Receiving_Ticket_Data ! END-IF -----Original Message----- From: Mike Eischen [mailto:MEISCHEN@genesco.com] Sent: Wednesday, October 01, 2003 2:36 PM To: sqr-users@sqrug.org Subject: Re: [sqr-users] String to Date format Hi Eric, With strtodate you need to use a format mask which indicates the date format you want the strtodate function to return, and the format masks vary depending on what database you're using. What database are you using? Can you include a code sample showing the syntax? Thanks, Mike >>> Eric.Ottaway@gnf.com 10/01/03 01:08PM >>> Hey guys and gals, I'm still having trouble trying to format a string ('06/05/2003 01:03:00) into a date with the same format. Anyone have any ideas on how to do this? I've tried using a to_date function and a strtodate function. Both failed. Thanks, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 13:58:07 2003 Date: Wed, 01 Oct 2003 14:00:36 -0500 From: "Mike Eischen" Subject: RE: [sqr-users] String to Date format Okay, and what database are you using? >>> Eric.Ottaway@gnf.com 10/01/03 01:39PM >>> !**************************************************************** BEGIN-PROCEDURE get_last_time BEGIN-SELECT to_date(maxvars.varvalue,'MM/DD/YYYY HH:MI:SS') &lastdate FROM [$schema]maxvars WHERE maxvars.varname = 'GNF_RCPT_TIMESTAMP' END-SELECT END-PROCEDURE !**************************************************************** BEGIN-PROCEDURE check_date ! LET $lastdate = strtodate($lastdate,'MM/DD/YYYY HH:MI:SS') PRINT $lastdate (+10,1)!edit 'MM/DD/YYYY HH:MI:SS' !***************TESTING BEGIN-SELECT DISTINCT MAX_RCV_INTERFACE.PONUM &currPO MAX_RCV_INTERFACE.IMPORTTIMESTAMP &currdates MOVE &currPO TO $currPO MOVE &currdates TO $currdates :$DATEMASK LET $currdates = strtodate($currdates,'MM/DD/YYYY') PRINT $currdates (+1,1)!edit 'MM/DD/YYYY HH:MI:SS' !***************TESTING NEW-PAGE ! LET #diffdate = datediff($currdates,$lastdate,'SECOND') ! SHOW 'CurrDates = ' $currdates ! SHOW 'LastDate = ' $lastdate ! IF #diffdate > 0 ! DO Get_Receiving_Ticket_Data ! END-IF -----Original Message----- From: Mike Eischen [mailto:MEISCHEN@genesco.com] Sent: Wednesday, October 01, 2003 2:36 PM To: sqr-users@sqrug.org Subject: Re: [sqr-users] String to Date format Hi Eric, With strtodate you need to use a format mask which indicates the date format you want the strtodate function to return, and the format masks vary depending on what database you're using. What database are you using? Can you include a code sample showing the syntax? Thanks, Mike >>> Eric.Ottaway@gnf.com 10/01/03 01:08PM >>> Hey guys and gals, I'm still having trouble trying to format a string ('06/05/2003 01:03:00) into a date with the same format. Anyone have any ideas on how to do this? I've tried using a to_date function and a strtodate function. Both failed. Thanks, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 14:16:52 2003 Date: Wed, 01 Oct 2003 15:15:32 -0400 From: Matt Rogish Subject: [sqr-users] Dynamic Programming and Output Generation Hi all, We're using SQR 4.3.4 (on AIX??) to write many, many SQR reports. The problem we've found is that over the years (and hundreds of programs later) we've got a mix of different styles etc. and it's becoming increasingly difficult. Oftentimes we have reports that take the same input parameters but, of course, they are written differently so that some have bounds checking and others don't etc. What I'd like to do is store the input parameters in a DBMS table and then run it at the beginning of every report to build the input list, e.g. something like this: (pseudocode) begin-select name &name prompt &prompt length &len type &type !this way input &name MAXLEN=&len &text TYPE=&type !or with an array to be gone over later Put &name &prompt &len &type Into paramArray( #something ) FROM params WHERE program_name = '{program_name}' end-select But I can't get SQR to accept any sort of input like that. Secondly, we use BRIO Portal 6 (whatever is one or two version(s) behind the current one) which doesn't support emailing the output from a job (SQR Program) to more than one person. We're not entirely sure the latest version of BRIO.Portal supports this, so if it doesn't we'd like a way to be able to email output to more than one person using SQR. I was thinking storing the distribution lists in another DBMS table and then using the Unix mail command to mail it to someone else. I see from this old thread (http://www.sqrug.org/sqr-users/sqrusers_97/msg00150.html) that I can close the output of the current program, but how do I find out the file name of the output file (usually a .pdf via -printer:pd) so that I can use it as an attachment. Anyone tried something like this and have a better way? Or is Brio Portal 7 (the version we're looking at upgrading to) more advanced than the version we're using now? Thanks, -- Matt Rogish - rogishmn@muohio.edu Programmer Advancement Services Miami University 513.529.1420 Do not meddle in the affairs of SQR, for it is subtle and quick to anger! _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 14:19:06 2003 Subject: RE: [sqr-users] String to Date format Date: Wed, 1 Oct 2003 15:16:44 -0400 From: "Ven Garlapati" Eric: If I read your message right, you are trying to convert a string to date format? In Oracle you can use like: select to_date ('06/05/2003 01:03:00','dd/mm/yyyy HH:MI:SS') from dual; I got the answer as: 6-May-2003 1:03:00. If this is right, you can incorporate same logic!! Thanks, Ven Garlapati vxg@creditacceptance.com (248) 353-2700 ext.514 -----Original Message----- From: Ottaway, Eric (PS, GNF, Contractor) [mailto:Eric.Ottaway@gnf.com] Sent: Wednesday, October 01, 2003 2:51 PM To: 'sqr-users@sqrug.org' Subject: RE: [sqr-users] String to Date format Thanks for the quick responses "SQR in Peoplesoft" has been of little help. The string looks like a date in the table I reference, but is not in fact a DATE. Therefore, I can't use datediff() to return a value. So I'm trying to basically keep the string as it is, but as a date instead of a string. Convert this into a date of the same format(String = 01/01/2001 01:15:00). Eric -----Original Message----- From: Knapp, Richard [mailto:KnappR@umsystem.edu] Sent: Wednesday, October 01, 2003 2:37 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] String to Date format I'm guessing the the date part is month/day so the format string for your example would be: 'MM/DD/YYYY HH:MI:SS' Do you not have access to any SQR documentation? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: Ottaway, Eric (PS, GNF, Contractor) [mailto:Eric.Ottaway@gnf.com] Sent: Wednesday, October 01, 2003 1:09 PM To: 'sqr-users@sqrug.org' Subject: [sqr-users] String to Date format Hey guys and gals, I'm still having trouble trying to format a string ('06/05/2003 01:03:00) into a date with the same format. Anyone have any ideas on how to do this? I've tried using a to_date function and a strtodate function. Both failed. Thanks, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 15:06:07 2003 Subject: RE: [sqr-users] Dynamic Programming and Output Generation Date: Wed, 1 Oct 2003 15:05:30 -0500 From: "Sengupta, Rajib (CAG-CC-Contractor)" I am not sure exactly what you want here , but I guess we do almost the same thing in our Peoplesoft environment while creating report from online. We have hundreds of reports which have different parameters , also depending on the value entered or not entered on the panel the sqr query become dynamic - It happens this way : When a user enters some value on online and run the report the values are stored in a DBMS table .Then at start of the SQR we do a select on the table as you have mentioned below . There are two parts in the whole thing: A. store the data/values on the table. In our peoplesoft system we use the usual peopletools and peoplecode to save and edit data entered online. B. then run the SQR report in the server Your SQR structure will be sumthing like this : BEGIN-REPORT DO INIT-REPORT DO GET-AGING-CATEGORY DO MAIN-PROCESS -- here is the actual report DO GRAND-TOTAL DO END-PROCESS END-REPORT in the init-report you can query the above table to get your data : BEGIN-PROCEDURE INIT-REPORT DO INIT-DATETIME ..... DO SELECT-PARAMETERS -- this will have your select statement as below ........... END-PROCEDURE !INIT-REPORT you will have your data in the $variables - now depending on the value in the $variables you will create your dynamic where clause of the sql which you can use in your main-process SELECT. ============================ about email it depends on which system you are - if you are in unix box- you can use the unix email shell commands to send your sqr outputs- .lis or .spf files to multiple users. I guess there should be similar batch commands for windows. for mainframe(MVS) environment you can use MQseries - lotus notes to send multiple emails. Infact there is some API by which lotus notes can send emails to microsoft exchange. so you create your file in MVS (OS/390) - write it to MQ- lotus notes will pick it up from MQ and will send it to exchange server. But for all these you have to have a good understanding of each of the technology mentioned. Thanks, Rajib Rajib Sengupta Systems Analyst and Team Lead Peoplesoft Financial Consultant Conagra Business Systems Phone: 402-595-5749 -----Original Message----- From: Matt Rogish [mailto:rogishmn@muohio.edu] Sent: Wednesday, October 01, 2003 2:16 PM To: sqr-users@sqrug.org Subject: [sqr-users] Dynamic Programming and Output Generation Hi all, We're using SQR 4.3.4 (on AIX??) to write many, many SQR reports. The problem we've found is that over the years (and hundreds of programs later) we've got a mix of different styles etc. and it's becoming increasingly difficult. Oftentimes we have reports that take the same input parameters but, of course, they are written differently so that some have bounds checking and others don't etc. What I'd like to do is store the input parameters in a DBMS table and then run it at the beginning of every report to build the input list, e.g. something like this: (pseudocode) begin-select name &name prompt &prompt length &len type &type !this way input &name MAXLEN=&len &text TYPE=&type !or with an array to be gone over later Put &name &prompt &len &type Into paramArray( #something ) FROM params WHERE program_name = '{program_name}' end-select But I can't get SQR to accept any sort of input like that. Secondly, we use BRIO Portal 6 (whatever is one or two version(s) behind the current one) which doesn't support emailing the output from a job (SQR Program) to more than one person. We're not entirely sure the latest version of BRIO.Portal supports this, so if it doesn't we'd like a way to be able to email output to more than one person using SQR. I was thinking storing the distribution lists in another DBMS table and then using the Unix mail command to mail it to someone else. I see from this old thread (http://www.sqrug.org/sqr-users/sqrusers_97/msg00150.html) that I can close the output of the current program, but how do I find out the file name of the output file (usually a .pdf via -printer:pd) so that I can use it as an attachment. Anyone tried something like this and have a better way? Or is Brio Portal 7 (the version we're looking at upgrading to) more advanced than the version we're using now? Thanks, -- Matt Rogish - rogishmn@muohio.edu Programmer Advancement Services Miami University 513.529.1420 Do not meddle in the affairs of SQR, for it is subtle and quick to anger! _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Oct 1 16:09:31 2003 From: "subrahmanyam uppuganti" Date: Wed, 01 Oct 2003 21:08:05 +0000 Subject: [sqr-users] problems with wrap when working on the main frame Hi, I am working on an sqr in Purchasing that has Descr254 and prints it on to the report,the environment that Iam working with is IBM main frames/OS390/DB2. Iam trying to wrap the Descr 254 on Print that will look like this: print $descr_long (#line, 10, 115) wrap 115 10 on=<37> as 37 is the EBCDIC decimal for line feed and to my surprise its wrapping when it encounters either 3 or 7 or 37 and not on the line feed or carriage return.Looks like its not recogising angular braces (<>) and simply ignoring them. I was wondering if there is any equivalent for <> (angular Braces ) when working on the main frame. I tried using \37\ and didnt work on. Is there any way that will make it work. I would reallly appreciate if some one can help me in this regard. Thanks In advance Subbu Uppuganti _________________________________________________________________ Share your photos without swamping your Inbox. Get Hotmail Extra Storage today! http://join.msn.com/?PAGE=features/es _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Oct 2 01:18:19 2003 Date: Thu, 02 Oct 2003 16:08:15 +1000 From: Steve Cavill Subject: RE: [sqr-users] Dynamic Programming and Output Generation Hi Matt, Rajib's method for dynamically selecting run time parms is fairly common - don't use input command at all, rather store the parms in a table, and retrieve them with select. Regarding the email, you can email the output to a distribution list, would that help? Another method is to subscribe multiple users to the job output, then Portal will send multiple emails, one for each subscriber. You can use the Portal API to maintain the subscription list. I know the above works in V7, I'm pretty sure that will work in V6 also. Cheers, Steve. -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Sengupta, Rajib (CAG-CC-Contractor) Sent: Thursday, 2 October 2003 6:06 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Dynamic Programming and Output Generation I am not sure exactly what you want here , but I guess we do almost the same thing in our Peoplesoft environment while creating report from online. We have hundreds of reports which have different parameters , also depending on the value entered or not entered on the panel the sqr query become dynamic - It happens this way : When a user enters some value on online and run the report the values are stored in a DBMS table .Then at start of the SQR we do a select on the table as you have mentioned below . There are two parts in the whole thing: A. store the data/values on the table. In our peoplesoft system we use the usual peopletools and peoplecode to save and edit data entered online. B. then run the SQR report in the server Your SQR structure will be sumthing like this : BEGIN-REPORT DO INIT-REPORT DO GET-AGING-CATEGORY DO MAIN-PROCESS -- here is the actual report DO GRAND-TOTAL DO END-PROCESS END-REPORT in the init-report you can query the above table to get your data : BEGIN-PROCEDURE INIT-REPORT DO INIT-DATETIME ..... DO SELECT-PARAMETERS -- this will have your select statement as below ........... END-PROCEDURE !INIT-REPORT you will have your data in the $variables - now depending on the value in the $variables you will create your dynamic where clause of the sql which you can use in your main-process SELECT. ============================ about email it depends on which system you are - if you are in unix box- you can use the unix email shell commands to send your sqr outputs- .lis or .spf files to multiple users. I guess there should be similar batch commands for windows. for mainframe(MVS) environment you can use MQseries - lotus notes to send multiple emails. Infact there is some API by which lotus notes can send emails to microsoft exchange. so you create your file in MVS (OS/390) - write it to MQ- lotus notes will pick it up from MQ and will send it to exchange server. But for all these you have to have a good understanding of each of the technology mentioned. Thanks, Rajib Rajib Sengupta Systems Analyst and Team Lead Peoplesoft Financial Consultant Conagra Business Systems Phone: 402-595-5749 -----Original Message----- From: Matt Rogish [mailto:rogishmn@muohio.edu] Sent: Wednesday, October 01, 2003 2:16 PM To: sqr-users@sqrug.org Subject: [sqr-users] Dynamic Programming and Output Generation Hi all, We're using SQR 4.3.4 (on AIX??) to write many, many SQR reports. The problem we've found is that over the years (and hundreds of programs later) we've got a mix of different styles etc. and it's becoming increasingly difficult. Oftentimes we have reports that take the same input parameters but, of course, they are written differently so that some have bounds checking and others don't etc. What I'd like to do is store the input parameters in a DBMS table and then run it at the beginning of every report to build the input list, e.g. something like this: (pseudocode) begin-select name &name prompt &prompt length &len type &type !this way input &name MAXLEN=&len &text TYPE=&type !or with an array to be gone over later Put &name &prompt &len &type Into paramArray( #something ) FROM params WHERE program_name = '{program_name}' end-select But I can't get SQR to accept any sort of input like that. Secondly, we use BRIO Portal 6 (whatever is one or two version(s) behind the current one) which doesn't support emailing the output from a job (SQR Program) to more than one person. We're not entirely sure the latest version of BRIO.Portal supports this, so if it doesn't we'd like a way to be able to email output to more than one person using SQR. I was thinking storing the distribution lists in another DBMS table and then using the Unix mail command to mail it to someone else. I see from this old thread (http://www.sqrug.org/sqr-users/sqrusers_97/msg00150.html) that I can close the output of the current program, but how do I find out the file name of the output file (usually a .pdf via -printer:pd) so that I can use it as an attachment. Anyone tried something like this and have a better way? Or is Brio Portal 7 (the version we're looking at upgrading to) more advanced than the version we're using now? Thanks, -- Matt Rogish - rogishmn@muohio.edu Programmer Advancement Services Miami University 513.529.1420 Do not meddle in the affairs of SQR, for it is subtle and quick to anger! _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Oct 3 11:31:56 2003 From: "Alexander, Steve" Subject: RE: [sqr-users] Dynamic Programming and Output Generation Date: Fri, 3 Oct 2003 09:37:03 -0700 Matt, A lot of the body of many SQR commands has to be literals. The variable that is to contain the values you input has to be a variable, not a variable containing the name of a variable. That still leaves you some options - I can think of three. First possibility: write a program to read the database table and construct an SQR command line like this: sqr myprog.sqr [connectivity] [flags] $first_parm 5 "Enter zipcode" char Then execute that command line. Within the program (myprog), use the ASK command to read the command line. Program: begin-setup ask variable ask length ask prompt ask type end-setup begin-program input {variable} maxlen={length} '{prompt}' type={type} ... end-program Drawbacks: Your program has to be hardcoded for the number of parameters, with possibly a lot of ask commands (variable1, variable2, variable3). If the command line gets too long, there is a provision to use a text file instead. Second possibility: write a program to read the data table and construct an SQR include file that contains the input commands. Then compile and execute the main program, which contains an include statement like so: begin-program #include 'input_statements.sqc' ... end-program Drawbacks: If you're running more than one program at once, you need a different include file name for each. If you're running more than one copy of the same program at once, you may need to differentiate the include file names and pass the different name through the command line / ask statement technique. Even if every copy of the program has the same input statements, you don't want to be deleting/rewriting an include file when the SQR compiler is looking for it. Third possibility: Instead of put the input statement parameters in a database table, put the input values in a database table. Now that I given you the techniques, I'm wondering why you want to do this. The input parameters to a program don't change that often, so why not hardcode them in that program? It's easier to maintain a program when everything is in one place, rather than hiding information about the parameters in a database. If you expect to change the inputs frequently, you'll have to change the programs anyway. If many of your programs use the same parameters, use include files. If the parameters you use vary widely from program to program, what use is this change? Since you have so many programs, and this approach forces you to change them all, is it worth it. -----Original Message----- From: Steve Cavill [mailto:steve.cavill@infoclarity.com.au] Sent: Wednesday, October 01, 2003 11:08 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Dynamic Programming and Output Generation Hi Matt, Rajib's method for dynamically selecting run time parms is fairly common - don't use input command at all, rather store the parms in a table, and retrieve them with select. Regarding the email, you can email the output to a distribution list, would that help? Another method is to subscribe multiple users to the job output, then Portal will send multiple emails, one for each subscriber. You can use the Portal API to maintain the subscription list. I know the above works in V7, I'm pretty sure that will work in V6 also. Cheers, Steve. -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Sengupta, Rajib (CAG-CC-Contractor) Sent: Thursday, 2 October 2003 6:06 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Dynamic Programming and Output Generation I am not sure exactly what you want here , but I guess we do almost the same thing in our Peoplesoft environment while creating report from online. We have hundreds of reports which have different parameters , also depending on the value entered or not entered on the panel the sqr query become dynamic - It happens this way : When a user enters some value on online and run the report the values are stored in a DBMS table .Then at start of the SQR we do a select on the table as you have mentioned below . There are two parts in the whole thing: A. store the data/values on the table. In our peoplesoft system we use the usual peopletools and peoplecode to save and edit data entered online. B. then run the SQR report in the server Your SQR structure will be sumthing like this : BEGIN-REPORT DO INIT-REPORT DO GET-AGING-CATEGORY DO MAIN-PROCESS -- here is the actual report DO GRAND-TOTAL DO END-PROCESS END-REPORT in the init-report you can query the above table to get your data : BEGIN-PROCEDURE INIT-REPORT DO INIT-DATETIME ..... DO SELECT-PARAMETERS -- this will have your select statement as below ........... END-PROCEDURE !INIT-REPORT you will have your data in the $variables - now depending on the value in the $variables you will create your dynamic where clause of the sql which you can use in your main-process SELECT. ============================ about email it depends on which system you are - if you are in unix box- you can use the unix email shell commands to send your sqr outputs- .lis or .spf files to multiple users. I guess there should be similar batch commands for windows. for mainframe(MVS) environment you can use MQseries - lotus notes to send multiple emails. Infact there is some API by which lotus notes can send emails to microsoft exchange. so you create your file in MVS (OS/390) - write it to MQ- lotus notes will pick it up from MQ and will send it to exchange server. But for all these you have to have a good understanding of each of the technology mentioned. Thanks, Rajib Rajib Sengupta Systems Analyst and Team Lead Peoplesoft Financial Consultant Conagra Business Systems Phone: 402-595-5749 -----Original Message----- From: Matt Rogish [mailto:rogishmn@muohio.edu] Sent: Wednesday, October 01, 2003 2:16 PM To: sqr-users@sqrug.org Subject: [sqr-users] Dynamic Programming and Output Generation Hi all, We're using SQR 4.3.4 (on AIX??) to write many, many SQR reports. The problem we've found is that over the years (and hundreds of programs later) we've got a mix of different styles etc. and it's becoming increasingly difficult. Oftentimes we have reports that take the same input parameters but, of course, they are written differently so that some have bounds checking and others don't etc. What I'd like to do is store the input parameters in a DBMS table and then run it at the beginning of every report to build the input list, e.g. something like this: (pseudocode) begin-select name &name prompt &prompt length &len type &type !this way input &name MAXLEN=&len &text TYPE=&type !or with an array to be gone over later Put &name &prompt &len &type Into paramArray( #something ) FROM params WHERE program_name = '{program_name}' end-select But I can't get SQR to accept any sort of input like that. Secondly, we use BRIO Portal 6 (whatever is one or two version(s) behind the current one) which doesn't support emailing the output from a job (SQR Program) to more than one person. We're not entirely sure the latest version of BRIO.Portal supports this, so if it doesn't we'd like a way to be able to email output to more than one person using SQR. I was thinking storing the distribution lists in another DBMS table and then using the Unix mail command to mail it to someone else. I see from this old thread (http://www.sqrug.org/sqr-users/sqrusers_97/msg00150.html) that I can close the output of the current program, but how do I find out the file name of the output file (usually a .pdf via -printer:pd) so that I can use it as an attachment. Anyone tried something like this and have a better way? Or is Brio Portal 7 (the version we're looking at upgrading to) more advanced than the version we're using now? Thanks, -- Matt Rogish - rogishmn@muohio.edu Programmer Advancement Services Miami University 513.529.1420 Do not meddle in the affairs of SQR, for it is subtle and quick to anger! _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Oct 2 07:19:22 2003 From: "Ottaway, Eric (PS, GNF, Contractor)" Date: Thu, 2 Oct 2003 08:16:02 -0400 Subject: [sqr-users] #DEFINE directive Anyone, Can the #DEFINE directive be used in a program to substitute the SQR_DB_DATE_FORMAT value in the SQR.ini file? Thanks a bunch, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 03:50:23 2003 Date: Fri, 03 Oct 2003 10:36:50 +1000 From: Steve Cavill Subject: RE: [sqr-users] #DEFINE directive I don't think so, but you could use the -ZIF flag to point to a different SQR.INI. Why do you want to dynamically change the SQR_DB_DATE_FORMAT setting, there may be another way to achieve your end result. Steve. -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Ottaway, Eric (PS, GNF, Contractor) Sent: Thursday, 2 October 2003 10:16 PM To: 'sqr-users@sqrug.org' Subject: [sqr-users] #DEFINE directive Anyone, Can the #DEFINE directive be used in a program to substitute the SQR_DB_DATE_FORMAT value in the SQR.ini file? Thanks a bunch, Eric B. Ottaway _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Oct 2 16:33:32 2003 Date: Thu, 02 Oct 2003 17:31:46 -0400 From: Matt Rogish Subject: [sqr-users] Problem with Printing w/On-Break and Before/After All, I have a result set like this: (column names) Event, Function, Function_Start_Date, Function_End_Date, Task, Task_Start_Date, Task_End_date, Task_Complete_Date I'm ordering by event, function, function_start_date, task, task_start_date Example Rows: Setup, AAI, 25-MAR-2002, 19-AUG-2002, Conduct Research on Branding, 25-MAR-2002, 05-APR-2002, 25-MAR-2002 Setup, AAI, 25-MAR-2002, 19-AUG-2002, Prepare Presentation to Board, 25-MAR-2002, 12-APR-2002, 25-MAR-2002 Setup, AAI, 25-MAR-2002, 19-AUG-2002, Presentation to Board, 12-APR-2002, 12-APR-2002, 12-APR-2002 Setup, Athl, 19-JUN-2002, 01-AUG-2002,,,, ..... Setup, Launch, 22-NOV-2002, 22-NOV-2002, Launch web site, 01-JAN-2003, 01-JAN-2003, 01-JAN-2003 What I'd like to see in the output is: --Event-- Setup Function AAI 25-MAR-2002 19-AUG-2002 Tasks Conduct Research on Branding 25-MAR-2002 05-APR-2002 Prepare Presentation to Board 25-MAR-2002 12-APR-2002 Presentation to Board 12-APR-2002 12-APR-2002 Function Athl .... ... Function Launch ... Tasks Launch web site My printing logic looks something like this: begin-select event (,) On-Break Level=1 Before=event_heading function (,) On-Break Level=2 Before=function_heading f_start_date (,) On-Break Level=2 ... task (,) On-Break Level=3 Before=task_heading t_start_date (,) On-Break Level=4 ... And the event_heading function looks something like: begin-function event-heading Print 'Event' (+1) Position (+1) end-function This one works fine. The problem is with the function_heading and task_heading functions (which look just like the event-heading function). Obviously they end up firing every time and don't even work correctly. Can anyone provide some insight? Thanks, -- Matt Rogish - rogishmn@muohio.edu Programmer Advancement Services Miami University 513.529.1420 Do not meddle in the affairs of SQR, for it is subtle and quick to anger! _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 00:35:36 2003 Date: Fri, 03 Oct 2003 10:18:10 +1000 From: Steve Cavill Subject: RE: [sqr-users] Problem with Printing w/On-Break and Before/After Matt, i think you just have too many break fields, if two or more fields are related, (e.g. your Function, Function_Start_Date, Function_End_Date, ), just break on one of them. so your code would read begin-select event () On-Break Level=1 Before=event_heading function () On-Break Level=2 PRINT=NEVER Before=function_heading f_start_date f_end_date ... task (1,10) On-Break Level=3 Before=task_heading t_start_date (,+2) ... Note that function, f_start_date and f_end_date are NOT printed in the select. Instead you print then in the function_heading e..g begin-procedure function_heading next-listing print 'Function' (1,1) print &Function (2,10) print &f_start_date (2,20) print &f_end_date (2,30) next-listing ... I recommend you use next-listing instead of position (), position can get very confusing with line positioning. HTH. Regards, Steve -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Matt Rogish Sent: Friday, 3 October 2003 7:32 AM To: sqr-users@sqrug.org Subject: [sqr-users] Problem with Printing w/On-Break and Before/After All, I have a result set like this: (column names) Event, Function, Function_Start_Date, Function_End_Date, Task, Task_Start_Date, Task_End_date, Task_Complete_Date I'm ordering by event, function, function_start_date, task, task_start_date Example Rows: Setup, AAI, 25-MAR-2002, 19-AUG-2002, Conduct Research on Branding, 25-MAR-2002, 05-APR-2002, 25-MAR-2002 Setup, AAI, 25-MAR-2002, 19-AUG-2002, Prepare Presentation to Board, 25-MAR-2002, 12-APR-2002, 25-MAR-2002 Setup, AAI, 25-MAR-2002, 19-AUG-2002, Presentation to Board, 12-APR-2002, 12-APR-2002, 12-APR-2002 Setup, Athl, 19-JUN-2002, 01-AUG-2002,,,, ..... Setup, Launch, 22-NOV-2002, 22-NOV-2002, Launch web site, 01-JAN-2003, 01-JAN-2003, 01-JAN-2003 What I'd like to see in the output is: --Event-- Setup Function AAI 25-MAR-2002 19-AUG-2002 Tasks Conduct Research on Branding 25-MAR-2002 05-APR-2002 Prepare Presentation to Board 25-MAR-2002 12-APR-2002 Presentation to Board 12-APR-2002 12-APR-2002 Function Athl .... ... Function Launch ... Tasks Launch web site My printing logic looks something like this: begin-select event (,) On-Break Level=1 Before=event_heading function (,) On-Break Level=2 Before=function_heading f_start_date (,) On-Break Level=2 ... task (,) On-Break Level=3 Before=task_heading t_start_date (,) On-Break Level=4 ... And the event_heading function looks something like: begin-function event-heading Print 'Event' (+1) Position (+1) end-function This one works fine. The problem is with the function_heading and task_heading functions (which look just like the event-heading function). Obviously they end up firing every time and don't even work correctly. Can anyone provide some insight? Thanks, -- Matt Rogish - rogishmn@muohio.edu Programmer Advancement Services Miami University 513.529.1420 Do not meddle in the affairs of SQR, for it is subtle and quick to anger! _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Oct 3 09:09:00 2003 Date: Fri, 3 Oct 2003 09:06:28 -0500 (EST) From: Don Mellen Subject: Re: [sqr-users] Problem with Printing w/On-Break and Before/After Try using print=never and printing the "whole" heading section and dropping the level=3 and 4 breaks. Something like.... begin-select event (,) On-Break print=never Level=1 Before=event_heading function (,) On-Break print=never Level=2 Before=function_heading f_start_date (,) On-Break print=never Level=2 ... task || ' ' || t_start_date || ' ' || t_end_date (+1) ... begin-function event_heading Print 'Event' (+1) Print &event (+1) Position (+1) end-function begin-function function_heading Print 'Function' (+1) Print &function (+1) Print &f_start_date ( , #date1_pos) Print &f_end_date ( , #date2_pos) Position (+1) Print 'Task' (+1) end-function (of course, you'll need your databases date to char function in the select). HTH, Don On Thu, 2 Oct 2003, Matt Rogish wrote: > All, > > I have a result set like this: > (column names) > Event, Function, Function_Start_Date, Function_End_Date, Task, > Task_Start_Date, Task_End_date, Task_Complete_Date > > I'm ordering by event, function, function_start_date, task, task_start_date > > Example Rows: > Setup, AAI, 25-MAR-2002, 19-AUG-2002, Conduct Research on > Branding, 25-MAR-2002, 05-APR-2002, 25-MAR-2002 > Setup, AAI, 25-MAR-2002, 19-AUG-2002, Prepare Presentation to Board, > 25-MAR-2002, 12-APR-2002, 25-MAR-2002 > Setup, AAI, 25-MAR-2002, 19-AUG-2002, Presentation to > Board, 12-APR-2002, 12-APR-2002, 12-APR-2002 > Setup, Athl, 19-JUN-2002, 01-AUG-2002,,,, > ..... > Setup, Launch, 22-NOV-2002, 22-NOV-2002, Launch web site, 01-JAN-2003, > 01-JAN-2003, 01-JAN-2003 > > > What I'd like to see in the output is: > --Event-- > Setup > > Function > AAI 25-MAR-2002 19-AUG-2002 > > Tasks > Conduct Research on Branding 25-MAR-2002 05-APR-2002 > Prepare Presentation to Board 25-MAR-2002 12-APR-2002 > Presentation to Board 12-APR-2002 12-APR-2002 > > Function > Athl .... > ... > Function > Launch ... > > Tasks > Launch web site > > My printing logic looks something like this: > begin-select > event (,) On-Break Level=1 Before=event_heading > function (,) On-Break Level=2 Before=function_heading > f_start_date (,) On-Break Level=2 > ... > task (,) On-Break Level=3 Before=task_heading > t_start_date (,) On-Break Level=4 > ... > > And the event_heading function looks something like: > begin-function event-heading > Print 'Event' (+1) > Position (+1) > end-function > > This one works fine. The problem is with the function_heading and > task_heading functions (which look just like the event-heading function). > > Obviously they end up firing every time and don't even work correctly. Can > anyone provide some insight? > > > Thanks, > > -- > Matt Rogish - rogishmn@muohio.edu > Programmer > Advancement Services > Miami University > 513.529.1420 > > Do not meddle in the affairs of SQR, > for it is subtle and quick to anger! > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > ----------------------------------------------------------------------- Donald Mellen | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/ donm@ontko.com | "In the beginning, there was nothing, which exploded" _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Oct 3 00:20:13 2003 Date: Fri, 03 Oct 2003 15:17:46 +1000 From: "Ian Mills" Subject: [sqr-users] Hi All, Hi All, I am getting the following error from me report. I am running against a Sybase database using SQR v6.1.2. I did find a similar error posted to this user group in Sep 2001 but noticed that nobody replied. I was wondering if anyone else more recently has encounted this problem and remembers how they fixed it. I would agree with the message in Sep 2001 that it is somehow related to using a temporyary table. What makes this more confusing is that the error does not occur when I call the procedure using "Cursor #1:" (shown below). If I call either "Cursor #2:" or "Cursor #3:", the error occurs. (SQR 5528) Sybase dbdesc: ct_results error in cursor 3: (5702) The SQL Server is terminating this process. Error on line 471: (SQR 3722) Could not set up cursor. SQR: Program Aborting. (SQR 5528) Sybase ct_send error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5528) Sybase dblogf: ct_send (end tran) error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5528) Sybase ct_send error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5528) Sybase dblogf: ct_send error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5502) Cannot drop SQR generated stored procedure: SQR_1404_1065157375. Cursor Status: Cursor #1: SQL = SELECT les_s, name, sum(tot_rec), sum(borr_rec), sum(co_rec), count(distinct lse_s), count(distinct unit_s) FROM [$_gs_temp_table] GROUP BY les_s ,name order by 3 desc Compiles = 0 Executes = 0 Rows = 0 Cursor #2: SQL = SELECT asset_code_desc, les_s, name, sum(tot_rec), sum(borr_rec), sum(co_rec), count(distinct lse_s), count(distinct unit_s) FROM [$_gs_temp_table] GROUP BY asset_code_desc ,les_s ,name order by 1 ,4 desc Compiles = 0 Executes = 0 Rows = 0 Cursor #3: SQL = SELECT com_s, les_s, name, sum(tot_rec), sum(borr_rec), sum(co_rec), count(distinct lse_s), count(distinct unit_s) FROM [$_gs_temp_table] GROUP BY com_s ,les_s ,name order by 1 ,4 desc Compiles = 1 Executes = 0 Rows = 0 below is the create statement that creates the temporary table... create table [$_gs_temp_table] ( com_s char(2) NULL ,les_s char(9) ,lse_s char(10) ,unit_s char(10) ,asset_code_desc varchar(50) ,name varchar(125) NULL ,tot_rec float ,borr_rec float ,co_rec float ); Regards, Ian Mills ORIX Australia Pty Ltd _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Oct 3 02:01:52 2003 Date: Fri, 03 Oct 2003 16:36:15 +1000 From: Steve Cavill Subject: RE: [sqr-users] Hi All, Hi Ian, You are using a global variable for the temp table name - is there any chance that could contribute to the problem - are all the procedures that use the table name local? Steve. -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Ian Mills Sent: Friday, 3 October 2003 3:18 PM To: sqr-users@sqrug.org Subject: [sqr-users] Hi All, Hi All, I am getting the following error from me report. I am running against a Sybase database using SQR v6.1.2. I did find a similar error posted to this user group in Sep 2001 but noticed that nobody replied. I was wondering if anyone else more recently has encounted this problem and remembers how they fixed it. I would agree with the message in Sep 2001 that it is somehow related to using a temporyary table. What makes this more confusing is that the error does not occur when I call the procedure using "Cursor #1:" (shown below). If I call either "Cursor #2:" or "Cursor #3:", the error occurs. (SQR 5528) Sybase dbdesc: ct_results error in cursor 3: (5702) The SQL Server is terminating this process. Error on line 471: (SQR 3722) Could not set up cursor. SQR: Program Aborting. (SQR 5528) Sybase ct_send error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5528) Sybase dblogf: ct_send (end tran) error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5528) Sybase ct_send error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5528) Sybase dblogf: ct_send error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5502) Cannot drop SQR generated stored procedure: SQR_1404_1065157375. Cursor Status: Cursor #1: SQL = SELECT les_s, name, sum(tot_rec), sum(borr_rec), sum(co_rec), count(distinct lse_s), count(distinct unit_s) FROM [$_gs_temp_table] GROUP BY les_s ,name order by 3 desc Compiles = 0 Executes = 0 Rows = 0 Cursor #2: SQL = SELECT asset_code_desc, les_s, name, sum(tot_rec), sum(borr_rec), sum(co_rec), count(distinct lse_s), count(distinct unit_s) FROM [$_gs_temp_table] GROUP BY asset_code_desc ,les_s ,name order by 1 ,4 desc Compiles = 0 Executes = 0 Rows = 0 Cursor #3: SQL = SELECT com_s, les_s, name, sum(tot_rec), sum(borr_rec), sum(co_rec), count(distinct lse_s), count(distinct unit_s) FROM [$_gs_temp_table] GROUP BY com_s ,les_s ,name order by 1 ,4 desc Compiles = 1 Executes = 0 Rows = 0 below is the create statement that creates the temporary table... create table [$_gs_temp_table] ( com_s char(2) NULL ,les_s char(9) ,lse_s char(10) ,unit_s char(10) ,asset_code_desc varchar(50) ,name varchar(125) NULL ,tot_rec float ,borr_rec float ,co_rec float ); Regards, Ian Mills ORIX Australia Pty Ltd _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 17:36:05 2003 Date: Tue, 07 Oct 2003 08:34:47 +1000 From: "Ian Mills" Subject: RE: [sqr-users] Hi All, Steve, Thanks for your reply. I use global variables all the time in my SQR programs when doing temporary tables. I also have a habit of declaring all my procedures local. I have investigated the error message returned and it appears as though it comes from a known bug in Sybase V12. We plan to investigate further on Wednesday morning. Regards, Ian Mills ORIX Australia Pty Ltd >>> steve.cavill@infoclarity.com.au 03/10/2003 4:36:15 pm >>> Hi Ian, You are using a global variable for the temp table name - is there any chance that could contribute to the problem - are all the procedures that use the table name local? Steve. -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Ian Mills Sent: Friday, 3 October 2003 3:18 PM To: sqr-users@sqrug.org Subject: [sqr-users] Hi All, Hi All, I am getting the following error from me report. I am running against a Sybase database using SQR v6.1.2. I did find a similar error posted to this user group in Sep 2001 but noticed that nobody replied. I was wondering if anyone else more recently has encounted this problem and remembers how they fixed it. I would agree with the message in Sep 2001 that it is somehow related to using a temporyary table. What makes this more confusing is that the error does not occur when I call the procedure using "Cursor #1:" (shown below). If I call either "Cursor #2:" or "Cursor #3:", the error occurs. (SQR 5528) Sybase dbdesc: ct_results error in cursor 3: (5702) The SQL Server is terminating this process. Error on line 471: (SQR 3722) Could not set up cursor. SQR: Program Aborting. (SQR 5528) Sybase ct_send error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5528) Sybase dblogf: ct_send (end tran) error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5528) Sybase ct_send error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5528) Sybase dblogf: ct_send error in cursor 0: (49) ct_send(): user api layer: external error: This routine cannot be called because another command structure has results pending. (SQR 5502) Cannot drop SQR generated stored procedure: SQR_1404_1065157375. Cursor Status: Cursor #1: SQL = SELECT les_s, name, sum(tot_rec), sum(borr_rec), sum(co_rec), count(distinct lse_s), count(distinct unit_s) FROM [$_gs_temp_table] GROUP BY les_s ,name order by 3 desc Compiles = 0 Executes = 0 Rows = 0 Cursor #2: SQL = SELECT asset_code_desc, les_s, name, sum(tot_rec), sum(borr_rec), sum(co_rec), count(distinct lse_s), count(distinct unit_s) FROM [$_gs_temp_table] GROUP BY asset_code_desc ,les_s ,name order by 1 ,4 desc Compiles = 0 Executes = 0 Rows = 0 Cursor #3: SQL = SELECT com_s, les_s, name, sum(tot_rec), sum(borr_rec), sum(co_rec), count(distinct lse_s), count(distinct unit_s) FROM [$_gs_temp_table] GROUP BY com_s ,les_s ,name order by 1 ,4 desc Compiles = 1 Executes = 0 Rows = 0 below is the create statement that creates the temporary table... create table [$_gs_temp_table] ( com_s char(2) NULL ,les_s char(9) ,lse_s char(10) ,unit_s char(10) ,asset_code_desc varchar(50) ,name varchar(125) NULL ,tot_rec float ,borr_rec float ,co_rec float ); Regards, Ian Mills ORIX Australia Pty Ltd _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Oct 3 11:34:06 2003 From: "Alexander, Steve" Subject: RE: [sqr-users] (SQR 6806) String2Em: String to Decimal Object Co nversion Error. Date: Fri, 3 Oct 2003 09:37:49 -0700 Thanks for the reference, Larry, but I didn't understand the advice given there. That forum seems to be pretty loose compared to the serious tone we usually keep here. I've done some further research and found I was wrong about the values in the field. It's actually a different field causing the problem, and that field usually has 11 digit numbers. Does that give anybody any ideas? -----Original Message----- From: Larry Roux [mailto:lroux@syr.edu] Sent: Friday, September 26, 2003 6:06 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] (SQR 6806) String2Em: String to Decimal Object Conversion Error. There is a similar question answered on the PeopleSoft Fans site: http://www.peoplesoftfans.com/psfans/forum/tools/messages/3295.html ****************************** Larry Roux Syracuse University lroux@syr.edu ******************************* >>> Steve.Alexander@ci.sj.ca.us 09/26/03 20:35 PM >>> Thanks for the suggestion. I had done that. Also, the select worked when I didn't return this field and didn't work when I did return this field. Strangely, this program has been in production (unchanged) for months. -----Original Message----- From: the dragon [mailto:ceprn@hotmail.com] Sent: Friday, September 26, 2003 3:48 PM To: sqr-users@sqrug.org Cc: Steve.Alexander@ci.sj.ca.us Subject: Re: [sqr-users] (SQR 6806) String2Em: String to Decimal Object Conversion Error. Look in your where clause. check for data type mismatches. copy your sql statement tinot your sql tool and run it with the same values you're using in your sqr. clarl 'the dragon' willis 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) ----Original Message Follows---- I got the following message on the first row of a select statement when reading a numeric field: (SQR 6806) String2Em: String to Decimal Object Conversion Error. What does it mean? The field contains the number 1. _________________________________________________________________ Get MSN 8 Dial-up Internet Service FREE for one month. Limited time offer-- sign up now! http://join.msn.com/?page=dept/dialup _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Oct 17 12:54:23 2003 From: Peter Burton Subject: RE: [sqr-users] (SQR 6806) String2Em: String to Decimal Object Co nversion Error. Date: Fri, 17 Oct 2003 13:53:29 -0400 Steve, This is because Oracle made a change in 9.2.0.3. Have a DBA issue the following command: alter system set event='10499 trace name context forever,level 1' COMMENT='return 0 for the scale if NUMBER created without specifying the scale' SCOPE=SPFILE Peter > -----Original Message----- > From: Alexander, Steve [mailto:Steven.Alexander@sanjoseca.gov] > Sent: Friday, October 17, 2003 11:34 AM > To: SQR User Group (E-mail) > Subject: [sqr-users] (SQR 6806) String2Em: String to Decimal Object > Conversion Error. > > > When I got this error message a few weeks ago, I dealt with > it by rewritting > the program. Now I've got it again, on a > Peoplesoft-delivered program, > BEN040. Both programs used to work, and I've just realized that they > stopped working on our STAGE database when we upgraded from > Oracle 8.1.7.4 > to 9.2.0.3. BEN040 still works in PROD, which is still at > Oracle 8.1.7.4. > Does anyone know of a bad SQR 6/Oracle 9 interaction? > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Oct 3 14:32:00 2003 From: Margaret.Bermel@bbh.com Date: Fri, 3 Oct 2003 15:10:49 -0400 Subject: [sqr-users] Relative Printing with a PDF File Hi all, I am trying to use relative printing with a PDF File and it doesn't seem to be working. Alter-Printer Font=3 Point-Size=12 font-type=fixed Print &Entity_nam () Center print $first_break (+2,60,) bold print '(' (,+1,1) bold print #first_break1_total (,+2,5) edit 99.99 print ')' (,+1,1) bold If I open the PDF file the "(" on some lines print correctly on others it isoverlapping on the first field that it should be relative to. What's strange is that when I export the file from adobe into a text file and view it, it looks fine. Is there a printer option or font that needs to be set? Any help will be most appreciated. Thanks, Margaret _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Oct 7 21:50:25 2003 From: Ray Ontko Date: Tue, 7 Oct 2003 21:48:16 -0500 Subject: Re: [sqr-users] Relative Printing with a PDF File Hi Margaret, When you use relative positioning, make sure that the size of your character cell matches the font size. In this case, 12-point courier would dictate a char-width of 12*0.6 = 7.2 pts (10 char/inch). Another popular size for Courier is 10pt which results in a char-width of 10*0.6= 6 pts (12 char/inch). What does it say in your declare-layout? Ray On Fri, Oct 03, 2003 at 03:10:49PM -0400, Margaret.Bermel@bbh.com wrote: > Hi all, > > I am trying to use relative printing with a PDF File and it doesn't seem > to be working. > > Alter-Printer Font=3 Point-Size=12 font-type=fixed > Print &Entity_nam () Center > print $first_break (+2,60,) bold > print '(' (,+1,1) bold > print #first_break1_total (,+2,5) edit 99.99 > print ')' (,+1,1) bold > > If I open the PDF file the "(" on some lines print correctly on others it > isoverlapping on the first field that it should be relative to. > > What's strange is that when I export the file from adobe into a text file > and view it, it looks fine. > > Is there a printer option or font that needs to be set? Any help will be > most appreciated. > > Thanks, > Margaret > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users ---------------------------------------------------------------------- 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/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Oct 3 17:22:32 2003 From: "the dragon" Date: Fri, 03 Oct 2003 17:21:12 -0500 Subject: [sqr-users] post OK, I'll probably get in trouble for it, but this affects US IT workers, some of whom actually use sqr, so I'll post this.. If it is inappropriate, I apologize in advance. LEAD STORY H1-B VISA CAP FALLS TO 65,000 | eWeek As IT unemployment numbers continue to soar, the U.S. Congress this week decided on dropping the H-1B visa limit back to 65,000. That's a new low since 2000, when the number of visas granted was boosted to 195,000. For the full details, click: http://www.eweek.com/article2/0,4149,1305899,00.asp clark 'the dragon' willis 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) _________________________________________________________________ Get MSN 8 Dial-up Internet Service FREE for one month. Limited time offer-- sign up now! http://join.msn.com/?page=dept/dialup _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Sat Oct 4 20:37:58 2003 Date: Sat, 4 Oct 2003 18:35:33 -0700 (PDT) From: Ania Wajda Subject: [sqr-users] Declare-Chart and Print-chart help Hello friends, We are using PS v8.15 tools. I am trying to run a sqr with declare-chart and print-chart commands. I am getting the following error. Are there any settings to change in the .ini file? Any help appreciated. -Ania Error on line 67: (SQR 3503) Unknown variable type. Chart-Size = ( 50,50) Error on line 68: (SQR 3701) Invalid command. Title = 'Employee Sales' Error on line 69: (SQR 3701) Invalid command. Type = 100%-bar Error on line 70: (SQR 3701) Invalid command. 3D-Effects = yes Error on line 71: (SQR 3701) Invalid command. X-Axis-Label = 'Employees' Error on line 72: (SQR 3701) Invalid command. Y-Axis-Label = 'Sales (in thousands)' Error on line 73: (SQR 3701) Invalid command. End-Declare Error on line 95: (SQR 3503) Unknown variable type. Print-Chart emp_sales_chart (#y, #x) Error on line 96: (SQR 3701) Invalid command. Fill = color Error on line 97: (SQR 3701) Invalid command. Sub-Title = '100% Bar-Chart: bar100.sqr' Error on line 98: (SQR 3701) Invalid command. Data-Array-Row-Count = #row Error on line 99: (SQR 3701) Invalid command. Data-Array-Column-Count = #col Error on line 100: (SQR 3701) Invalid command. Data-Array-Column-Labels = ('Jun', 'Jul', 'Aug') Error on line 101: (SQR 3701) Invalid command. Data-Array = emp_sales Errors were found in the program file. SQR for PeopleSoft: Program Aborting. --------------------------------- Do you Yahoo!? The New Yahoo! Shopping - with improved product search _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 09:10:21 2003 Date: Mon, 06 Oct 2003 09:09:32 -0500 From: Bob.Melosi@edwardjones.com Subject: Re: [sqr-users] Declare-Chart and Print-chart help I had the same problem. It is the version of PS tools you are using. The version of SQR that comes with that version has a problem with charts. We are running PS v8.18 in HR and it didn't work. I ran with PS v8.42 in our financial area and it worked. If I remember correctly, v8.19 is the lowest version you can use. Bob > Hello friends, > > > We are using PS v8.15 tools. I am trying to run a sqr with declare-chart and print-chart commands. I am getting the following error. Are there any settings to change in the .ini file? > > Any help appreciated. > > > -Ania > > Error on line 67: > (SQR 3503) Unknown variable type. > Chart-Size = ( 50,50) > Error on line 68: > (SQR 3701) Invalid command. > Title = 'Employee Sales' > Error on line 69: > (SQR 3701) Invalid command. > Type = 100%-bar > Error on line 70: > (SQR 3701) Invalid command. > 3D-Effects = yes > Error on line 71: > (SQR 3701) Invalid command. > X-Axis-Label = 'Employees' > Error on line 72: > (SQR 3701) Invalid command. > Y-Axis-Label = 'Sales (in thousands)' > Error on line 73: > (SQR 3701) Invalid command. > End-Declare > Error on line 95: > (SQR 3503) Unknown variable type. > Print-Chart emp_sales_chart (#y, #x) > Error on line 96: > (SQR 3701) Invalid command. > Fill = color > Error on line 97: > (SQR 3701) Invalid command. > Sub-Title = '100% Bar-Chart: bar100.sqr' > Error on line 98: > (SQR 3701) Invalid command. > Data-Array-Row-Count = #row > Error on line 99: > (SQR 3701) Invalid command. > Data-Array-Column-Count = #col > Error on line 100: > (SQR 3701) Invalid command. > Data-Array-Column-Labels = ('Jun', 'Jul', 'Aug') > Error on line 101: > (SQR 3701) Invalid command. > Data-Array = emp_sales > Errors were found in the program file. > SQR for PeopleSoft: Program Aborting. > > > > > --------------------------------- > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 19:02:15 2003 Date: Mon, 6 Oct 2003 17:01:20 -0700 (PDT) From: Ania Wajda Subject: Re: [sqr-users] Declare-Chart and Print-chart help Hi Bob, Thanks for the information, you provided. We are planing to Upgrade PS tools to version 8.19. I will try again using PS tools v8.19. Thanks again. Ania. Bob.Melosi@edwardjones.com wrote: I had the same problem. It is the version of PS tools you are using. The version of SQR that comes with that version has a problem with charts. We are running PS v8.18 in HR and it didn't work. I ran with PS v8.42 in our financial area and it worked. If I remember correctly, v8.19 is the lowest version you can use. Bob > Hello friends, > > > We are using PS v8.15 tools. I am trying to run a sqr with declare-chart and print-chart commands. I am getting the following error. Are there any settings to change in the .ini file? > > Any help appreciated. > > > -Ania > > Error on line 67: > (SQR 3503) Unknown variable type. > Chart-Size = ( 50,50) > Error on line 68: > (SQR 3701) Invalid command. > Title = 'Employee Sales' > Error on line 69: > (SQR 3701) Invalid command. > Type = 100%-bar > Error on line 70: > (SQR 3701) Invalid command. > 3D-Effects = yes > Error on line 71: > (SQR 3701) Invalid command. > X-Axis-Label = 'Employees' > Error on line 72: > (SQR 3701) Invalid command. > Y-Axis-Label = 'Sales (in thousands)' > Error on line 73: > (SQR 3701) Invalid command. > End-Declare > Error on line 95: > (SQR 3503) Unknown variable type. > Print-Chart emp_sales_chart (#y, #x) > Error on line 96: > (SQR 3701) Invalid command. > Fill = color > Error on line 97: > (SQR 3701) Invalid command. > Sub-Title = '100% Bar-Chart: bar100.sqr' > Error on line 98: > (SQR 3701) Invalid command. > Data-Array-Row-Count = #row > Error on line 99: > (SQR 3701) Invalid command. > Data-Array-Column-Count = #col > Error on line 100: > (SQR 3701) Invalid command. > Data-Array-Column-Labels = ('Jun', 'Jul', 'Aug') > Error on line 101: > (SQR 3701) Invalid command. > Data-Array = emp_sales > Errors were found in the program file. > SQR for PeopleSoft: Program Aborting. > > > > > --------------------------------- > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users --------------------------------- Do you Yahoo!? The New Yahoo! Shopping - with improved product search _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Sun Oct 5 15:01:24 2003 Date: Sun, 5 Oct 2003 12:59:27 -0700 (PDT) From: Roland Ferreras Subject: [sqr-users] on-break Performance I have an SQR program that will be processing a lot on information and I'm in need to tweak it as much as possible. My question is what's the overhead performance of ON-BREAK commands when used in a BEGIN-SELECT block? I'm thinking of rewriting the following code using 'if-then' logic to find my breaks depending on the performance. I have around 5 BEGIN-SELECT procedures that I would have to recode. Below is a copy of my code... begin-SELECT on-error = ESG-SQL-Error LINE.EMPLID () ON-BREAK level=1 save=$EMPLID LINE.COMPANY () ON-BREAK level=2 save=$COMPANY LINE.PAYGROUP () ON-BREAK level=3 save=$PAYGROUP LINE.PAY_END_DT () ON-BREAK level=4 save=$PAY_END_DT LINE.OFF_CYCLE () ON-BREAK level=5 save=$OFF_CYCLE to_char(LINE.PAGE_NBR) () ON-BREAK level=6 save=$PAGE_NBR to_char(LINE.LINE_NBR) () ON-BREAK level=7 save=$LINE_NBR AFTER=Pay-Line-Break to_char(LINE.EMPL_TYPE) () ON-BREAK level=8 save=$EMPL_TYPE FROM PS_PAY_LINE LINE !------------------------------------------- WHERE LINE.COMPANY = $COMPANY AND LINE.PAYGROUP = $PAYGROUP AND LINE.PAY_END_DT = $PAY_END_DT AND LINE.CONFIRMED = 'Y' ORDER BY LINE.EMPLID ,LINE.COMPANY ,LINE.PAYGROUP ,LINE.PAY_END_DT ,LINE.OFF_CYCLE ,LINE.PAGE_NBR ,LINE.LINE_NBR end-SELECT FYI I'm running this in a Windows 2000/Oracle 9 shop. Thanks! Roland "If you are going through hell, keep going." - Sir Winston Churchill (1874-1965) --------------------------------- Do you Yahoo!? The New Yahoo! Shopping - with improved product search _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Sun Oct 5 20:24:53 2003 From: Ray Ontko Date: Sun, 5 Oct 2003 20:23:04 -0500 Subject: Re: [sqr-users] on-break Performance Roland, I've never seen any performance problems caused by on-break. It should be as fast or faster then putting in your own if-then logic. Ray On Sun, Oct 05, 2003 at 12:59:27PM -0700, Roland Ferreras wrote: > I have an SQR program that will be processing a lot on information and I'm in need to tweak it as much as possible. My question is what's the overhead performance of ON-BREAK commands when used in a BEGIN-SELECT block? I'm thinking of rewriting the following code using 'if-then' logic to find my breaks depending on the performance. I have around 5 BEGIN-SELECT procedures that I would have to recode. > > Below is a copy of my code... > > begin-SELECT on-error = ESG-SQL-Error > LINE.EMPLID () ON-BREAK level=1 save=$EMPLID > LINE.COMPANY () ON-BREAK level=2 save=$COMPANY > LINE.PAYGROUP () ON-BREAK level=3 save=$PAYGROUP > LINE.PAY_END_DT () ON-BREAK level=4 save=$PAY_END_DT > LINE.OFF_CYCLE () ON-BREAK level=5 save=$OFF_CYCLE > to_char(LINE.PAGE_NBR) () ON-BREAK level=6 save=$PAGE_NBR > to_char(LINE.LINE_NBR) () ON-BREAK level=7 save=$LINE_NBR AFTER=Pay-Line-Break > to_char(LINE.EMPL_TYPE) () ON-BREAK level=8 save=$EMPL_TYPE > > FROM PS_PAY_LINE LINE > !------------------------------------------- > WHERE LINE.COMPANY = $COMPANY > AND LINE.PAYGROUP = $PAYGROUP > AND LINE.PAY_END_DT = $PAY_END_DT > AND LINE.CONFIRMED = 'Y' > ORDER BY LINE.EMPLID > ,LINE.COMPANY > ,LINE.PAYGROUP > ,LINE.PAY_END_DT > ,LINE.OFF_CYCLE > ,LINE.PAGE_NBR > ,LINE.LINE_NBR > end-SELECT > > > FYI I'm running this in a Windows 2000/Oracle 9 shop. > > Thanks! > > Roland > > > > > > "If you are going through hell, keep going." > - Sir Winston Churchill (1874-1965) > > --------------------------------- > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users ---------------------------------------------------------------------- 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/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 10:56:05 2003 From: "Alexander, Steve" Subject: RE: [sqr-users] on-break Performance Date: Mon, 6 Oct 2003 08:59:03 -0700 Given the fact that this table is indexed, it may not matter, but you might simplify the sort. Since there's only one value of COMPANY, PAYGROUP, and PAY_END_DT, why sort on them? And, do you really care which PAGE_NUM, LINE_NUM, and OFF_CYCLE values come first? Or is there more to this program than we see? PS_PAY_LINE has some interesting information, but I usually use it to link EMPLID to the rest of the paycheck. Are you doing that? If so, select the other tables at the same time; join PS_PAY_LINE with PS_PAY_EARNINGS or PS_PAY_DEDUCTION or whatever. That's much, much faster than reading each row of PS_PAY_LINE and then using the key fields to read one row of another table. -----Original Message----- From: Ray Ontko [mailto:rayo@ontko.com] Sent: Sunday, October 05, 2003 6:23 PM To: sqr-users@sqrug.org Subject: Re: [sqr-users] on-break Performance Roland, I've never seen any performance problems caused by on-break. It should be as fast or faster then putting in your own if-then logic. Ray On Sun, Oct 05, 2003 at 12:59:27PM -0700, Roland Ferreras wrote: > I have an SQR program that will be processing a lot on information and I'm in need to tweak it as much as possible. My question is what's the overhead performance of ON-BREAK commands when used in a BEGIN-SELECT block? I'm thinking of rewriting the following code using 'if-then' logic to find my breaks depending on the performance. I have around 5 BEGIN-SELECT procedures that I would have to recode. > > Below is a copy of my code... > > begin-SELECT on-error = ESG-SQL-Error > LINE.EMPLID () ON-BREAK level=1 save=$EMPLID > LINE.COMPANY () ON-BREAK level=2 save=$COMPANY > LINE.PAYGROUP () ON-BREAK level=3 save=$PAYGROUP > LINE.PAY_END_DT () ON-BREAK level=4 save=$PAY_END_DT > LINE.OFF_CYCLE () ON-BREAK level=5 save=$OFF_CYCLE > to_char(LINE.PAGE_NBR) () ON-BREAK level=6 save=$PAGE_NBR > to_char(LINE.LINE_NBR) () ON-BREAK level=7 save=$LINE_NBR AFTER=Pay-Line-Break > to_char(LINE.EMPL_TYPE) () ON-BREAK level=8 save=$EMPL_TYPE > > FROM PS_PAY_LINE LINE > !------------------------------------------- > WHERE LINE.COMPANY = $COMPANY > AND LINE.PAYGROUP = $PAYGROUP > AND LINE.PAY_END_DT = $PAY_END_DT > AND LINE.CONFIRMED = 'Y' > ORDER BY LINE.EMPLID > ,LINE.COMPANY > ,LINE.PAYGROUP > ,LINE.PAY_END_DT > ,LINE.OFF_CYCLE > ,LINE.PAGE_NBR > ,LINE.LINE_NBR > end-SELECT > > > FYI I'm running this in a Windows 2000/Oracle 9 shop. > > Thanks! > > Roland > > > > > > "If you are going through hell, keep going." > - Sir Winston Churchill (1874-1965) > > --------------------------------- > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users ---------------------------------------------------------------------- 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/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 07:50:23 2003 From: Margaret.Bermel@bbh.com Date: Mon, 6 Oct 2003 08:46:39 -0400 Subject: [sqr-users] Relative Printing with a PDF File Hi all, If anyone can help, please do. Thanks. I am trying to use relative printing with a PDF File and it doesn't seem to be working. Alter-Printer Font=3 Point-Size=12 font-type=fixed Print &Entity_nam () Center print $first_break (+2,60,) bold print '(' (,+1,1) bold print #first_break1_total (,+2,5) edit 99.99 print ')' (,+1,1) bold If I open the PDF file the "(" on some lines print correctly on others it isoverlapping on the first field that it should be relative to. What's strange is that when I export the file from adobe into a text file and view it, it looks fine. Is there a printer option or font that needs to be set? Any help will be most appreciated. Thanks, Margaret _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 08:01:07 2003 From: Peter Burton Subject: RE: [sqr-users] Relative Printing with a PDF File Date: Mon, 6 Oct 2003 06:00:05 -0700 This is a multi-part message in MIME format... ------_=_NextPart_000_01C38C09.C363FC90 Content-Type: text/plain; charset="iso-8859-1" Margaret, Works fine for me. Peter -----Original Message----- From: Margaret.Bermel@bbh.com [mailto:Margaret.Bermel@bbh.com] Sent: Monday, October 06, 2003 8:47 AM To: sqr-users@sqrug.org Subject: [sqr-users] Relative Printing with a PDF File Hi all, If anyone can help, please do. Thanks. I am trying to use relative printing with a PDF File and it doesn't seem to be working. Alter-Printer Font=3 Point-Size=12 font-type=fixed Print &Entity_nam () Center print $first_break (+2,60,) bold print '(' (,+1,1) bold print #first_break1_total (,+2,5) edit 99.99 print ')' (,+1,1) bold If I open the PDF file the "(" on some lines print correctly on others it isoverlapping on the first field that it should be relative to. What's strange is that when I export the file from adobe into a text file and view it, it looks fine. Is there a printer option or font that needs to be set? Any help will be most appreciated. Thanks, Margaret _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ------_=_NextPart_000_01C38C09.C363FC90 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'application/octet-stream' MIME type. It has been automatically discarded before sending the message to the list. ------_=_NextPart_000_01C38C09.C363FC90 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'application/octet-stream' MIME type. It has been automatically discarded before sending the message to the list. ------_=_NextPart_000_01C38C09.C363FC90-- _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 10:53:48 2003 Date: Mon, 6 Oct 2003 09:51:19 -0600 From: "Turner, Ivan" Subject: [sqr-users] Dynamically Use Oracle Hint I usually know the hint I want to use and write the code as such: Begin-Select on-error = mucked-up /*+ index(ia PSBITEM_ACTIVITY) */ C.segment_id ia.business_unit o o o from ps_item_activity ia, pc_customer c where ... End-Select I now need to know if there is a way to build hints dynamically. I will be accessing the table based on user choices where each choice has an index that is optimal for table access. Has anyone done this? _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 16:27:26 2003 From: "Mark Hall" Date: Mon, 06 Oct 2003 16:23:57 -0500 Subject: [sqr-users] Help! SQR mysteriously prints extra pages! I am totally stumped on this one. I have an sqr (DDP003P.SQR, The check advice print, for all of you peoplesoft types out there) that for some strange reason prints mystery pages. I can see no reason why. My checks print fine and the advices use the same layout. When I run the process to a pdf file the pdf looks fine. All the text and graphics are on one page just like they should be. When I look at the raw .lis file I only see one formfeed (at the bottom of the page where it should be). But when I run it to an hp printer it just goes crazy. It prints some stuff on the first page. Then it prints some stuff on the next page. Then it goes to the next page and prints a line. Then it prints a line on the next page. And so on for a few more pages. Then it prints a whole bunch of blank pages. Then it prints the category subtotals on one page. Then finally it prints the grand totals on the last page. It spits out about 45 pages between blanks and pages with stuff on them. I've tried the formfeed=no option but that doesn't work. Like I said I don't see any extra formfeeds. Does anyone have any idea what the problem is? Any help would be greatly appreciated. We are going live in a few weeks and I am at my wits end. I am running the PeopleSoft version of sqr on an AIX 5.1 box and printing to an HP LaserJet 4000T. PeopleTools release 8.4.3. Mark Hall IS Analyst ERP Team 260-373-8796 mark.hall@parkview.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 16:38:06 2003 From: "Alexander, Steve" Subject: RE: [sqr-users] Help! SQR mysteriously prints extra pages! Date: Mon, 6 Oct 2003 14:43:54 -0700 Does the "some stuff" look like HP's printer control language or like Postscript? Maybe SQR is writing Postscript and the printer is expecting PCL or vica versa. -----Original Message----- From: Mark Hall [mailto:Mark.Hall@parkview.com] Sent: Monday, October 06, 2003 2:24 PM To: sqr-users@sqrug.org Subject: [sqr-users] Help! SQR mysteriously prints extra pages! I am totally stumped on this one. I have an sqr (DDP003P.SQR, The check advice print, for all of you peoplesoft types out there) that for some strange reason prints mystery pages. I can see no reason why. My checks print fine and the advices use the same layout. When I run the process to a pdf file the pdf looks fine. All the text and graphics are on one page just like they should be. When I look at the raw .lis file I only see one formfeed (at the bottom of the page where it should be). But when I run it to an hp printer it just goes crazy. It prints some stuff on the first page. Then it prints some stuff on the next page. Then it goes to the next page and prints a line. Then it prints a line on the next page. And so on for a few more pages. Then it prints a whole bunch of blank pages. Then it prints the category subtotals on one page. Then finally it prints the grand totals on the last page. It spits out about 45 pages between blanks and pages with stuff on them. I've tried the formfeed=no option but that doesn't work. Like I said I don't see any extra formfeeds. Does anyone have any idea what the problem is? Any help would be greatly appreciated. We are going live in a few weeks and I am at my wits end. I am running the PeopleSoft version of sqr on an AIX 5.1 box and printing to an HP LaserJet 4000T. PeopleTools release 8.4.3. Mark Hall IS Analyst ERP Team 260-373-8796 mark.hall@parkview.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 16:45:05 2003 Date: Mon, 06 Oct 2003 16:47:30 -0500 From: "Mike Eischen" Subject: Re: [sqr-users] Help! SQR mysteriously prints extra pages! Is it possible that your printer has a setting that does a page eject after a certain amount of time? I seem to remember some laser printers having that feature. If you were printing something with graphics, the printer would eject a partially completed image. >>> Mark.Hall@parkview.com 10/06/03 04:23PM >>> I am totally stumped on this one. I have an sqr (DDP003P.SQR, The check advice print, for all of you peoplesoft types out there) that for some strange reason prints mystery pages. I can see no reason why. My checks print fine and the advices use the same layout. When I run the process to a pdf file the pdf looks fine. All the text and graphics are on one page just like they should be. When I look at the raw .lis file I only see one formfeed (at the bottom of the page where it should be). But when I run it to an hp printer it just goes crazy. It prints some stuff on the first page. Then it prints some stuff on the next page. Then it goes to the next page and prints a line. Then it prints a line on the next page. And so on for a few more pages. Then it prints a whole bunch of blank pages. Then it prints the category subtotals on one page. Then finally it prints the grand totals on the last page. It spits out about 45 pages between blanks and pages with stuff on them. I've tried the formfeed=no option but that doesn't work. Like I said I don't see any extra formfeeds. Does anyone have any idea what the problem is? Any help would be greatly appreciated. We are going live in a few weeks and I am at my wits end. I am running the PeopleSoft version of sqr on an AIX 5.1 box and printing to an HP LaserJet 4000T. PeopleTools release 8.4.3. Mark Hall IS Analyst ERP Team 260-373-8796 mark.hall@parkview.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 17:07:06 2003 From: "Mark Hall" Date: Mon, 06 Oct 2003 17:06:12 -0500 Subject: RE: [sqr-users] Help! SQR mysteriously prints extra pages! No strange "stuff" appears on the report. Everythng that prints is what I expect to print it just prints on different pages. Mark Hall IS Analyst ERP Team 260-373-8796 mark.hall@parkview.com >>> Steve.Alexander@sanjoseca.gov 10/06/03 04:43PM >>> Does the "some stuff" look like HP's printer control language or like Postscript? Maybe SQR is writing Postscript and the printer is expecting PCL or vica versa. -----Original Message----- From: Mark Hall [mailto:Mark.Hall@parkview.com] Sent: Monday, October 06, 2003 2:24 PM To: sqr-users@sqrug.org Subject: [sqr-users] Help! SQR mysteriously prints extra pages! I am totally stumped on this one. I have an sqr (DDP003P.SQR, The check advice print, for all of you peoplesoft types out there) that for some strange reason prints mystery pages. I can see no reason why. My checks print fine and the advices use the same layout. When I run the process to a pdf file the pdf looks fine. All the text and graphics are on one page just like they should be. When I look at the raw .lis file I only see one formfeed (at the bottom of the page where it should be). But when I run it to an hp printer it just goes crazy. It prints some stuff on the first page. Then it prints some stuff on the next page. Then it goes to the next page and prints a line. Then it prints a line on the next page. And so on for a few more pages. Then it prints a whole bunch of blank pages. Then it prints the category subtotals on one page. Then finally it prints the grand totals on the last page. It spits out about 45 pages between blanks and pages with stuff on them. I've tried the formfeed=no option but that doesn't work. Like I said I don't see any extra formfeeds. Does anyone have any idea what the problem is? Any help would be greatly appreciated. We are going live in a few weeks and I am at my wits end. I am running the PeopleSoft version of sqr on an AIX 5.1 box and printing to an HP LaserJet 4000T. PeopleTools release 8.4.3. Mark Hall IS Analyst ERP Team 260-373-8796 mark.hall@parkview.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 17:20:07 2003 From: "Alexander, Steve" Subject: RE: [sqr-users] Help! SQR mysteriously prints extra pages! Date: Mon, 6 Oct 2003 15:25:40 -0700 Do other reports print OK? How about other employees' advices? -----Original Message----- From: Mark Hall [mailto:Mark.Hall@parkview.com] Sent: Monday, October 06, 2003 3:06 PM To: Steve.Alexander@sanjoseca.gov; sqr-users@sqrug.org Subject: RE: [sqr-users] Help! SQR mysteriously prints extra pages! No strange "stuff" appears on the report. Everythng that prints is what I expect to print it just prints on different pages. Mark Hall IS Analyst ERP Team 260-373-8796 mark.hall@parkview.com >>> Steve.Alexander@sanjoseca.gov 10/06/03 04:43PM >>> Does the "some stuff" look like HP's printer control language or like Postscript? Maybe SQR is writing Postscript and the printer is expecting PCL or vica versa. -----Original Message----- From: Mark Hall [mailto:Mark.Hall@parkview.com] Sent: Monday, October 06, 2003 2:24 PM To: sqr-users@sqrug.org Subject: [sqr-users] Help! SQR mysteriously prints extra pages! I am totally stumped on this one. I have an sqr (DDP003P.SQR, The check advice print, for all of you peoplesoft types out there) that for some strange reason prints mystery pages. I can see no reason why. My checks print fine and the advices use the same layout. When I run the process to a pdf file the pdf looks fine. All the text and graphics are on one page just like they should be. When I look at the raw .lis file I only see one formfeed (at the bottom of the page where it should be). But when I run it to an hp printer it just goes crazy. It prints some stuff on the first page. Then it prints some stuff on the next page. Then it goes to the next page and prints a line. Then it prints a line on the next page. And so on for a few more pages. Then it prints a whole bunch of blank pages. Then it prints the category subtotals on one page. Then finally it prints the grand totals on the last page. It spits out about 45 pages between blanks and pages with stuff on them. I've tried the formfeed=no option but that doesn't work. Like I said I don't see any extra formfeeds. Does anyone have any idea what the problem is? Any help would be greatly appreciated. We are going live in a few weeks and I am at my wits end. I am running the PeopleSoft version of sqr on an AIX 5.1 box and printing to an HP LaserJet 4000T. PeopleTools release 8.4.3. Mark Hall IS Analyst ERP Team 260-373-8796 mark.hall@parkview.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 17:32:11 2003 From: Ray Ontko Date: Mon, 6 Oct 2003 17:31:20 -0500 Subject: Re: [sqr-users] Help! SQR mysteriously prints extra pages! Mark, The problem is that you are attempting to print past the end of the page, so the HP printer is adding a newline after each print attempt. I don't recall the exact escape sequence that you need to send the printer, but it's the one that defines the printable area of the page. Since you're printing past the printable area, the printer thinks you want to start the NEXT text on a fresh page. Ray On Mon, Oct 06, 2003 at 04:23:57PM -0500, Mark Hall wrote: > I am totally stumped on this one. I have an sqr (DDP003P.SQR, The check > advice print, for all of you peoplesoft types out there) that for some > strange reason prints mystery pages. I can see no reason why. My > checks print fine and the advices use the same layout. When I run the > process to a pdf file the pdf looks fine. All the text and graphics are > on one page just like they should be. When I look at the raw .lis file > I only see one formfeed (at the bottom of the page where it should be). > But when I run it to an hp printer it just goes crazy. It prints some > stuff on the first page. Then it prints some stuff on the next page. > Then it goes to the next page and prints a line. Then it prints a line > on the next page. And so on for a few more pages. Then it prints a > whole bunch of blank pages. Then it prints the category subtotals on > one page. Then finally it prints the grand totals on the last page. It > spits out about 45 pages between blanks and pages with stuff on them. > > I've tried the formfeed=no option but that doesn't work. Like I said I > don't see any extra formfeeds. > > Does anyone have any idea what the problem is? Any help would be > greatly appreciated. We are going live in a few weeks and I am at my > wits end. > > I am running the PeopleSoft version of sqr on an AIX 5.1 box and > printing to an HP LaserJet 4000T. PeopleTools release 8.4.3. > > > > Mark Hall > IS Analyst > ERP Team > 260-373-8796 > mark.hall@parkview.com > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users ---------------------------------------------------------------------- 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/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Oct 6 17:57:13 2003 From: "Mark Hall" Date: Mon, 06 Oct 2003 17:35:42 -0500 Subject: RE: [sqr-users] Help! SQR mysteriously prints extra pages! Like I said ealier, my checks print fine; everything is on one page. Other reports print fine. This is why I am so stumped. I can't see any reason why the report behaves this way. Mark Hall IS Analyst ERP Team 260-373-8796 mark.hall@parkview.com >>> Steve.Alexander@sanjoseca.gov 10/06/03 05:25PM >>> Do other reports print OK? How about other employees' advices? -----Original Message----- From: Mark Hall [mailto:Mark.Hall@parkview.com] Sent: Monday, October 06, 2003 3:06 PM To: Steve.Alexander@sanjoseca.gov; sqr-users@sqrug.org Subject: RE: [sqr-users] Help! SQR mysteriously prints extra pages! No strange "stuff" appears on the report. Everythng that prints is what I expect to print it just prints on different pages. Mark Hall IS Analyst ERP Team 260-373-8796 mark.hall@parkview.com >>> Steve.Alexander@sanjoseca.gov 10/06/03 04:43PM >>> Does the "some stuff" look like HP's printer control language or like Postscript? Maybe SQR is writing Postscript and the printer is expecting PCL or vica versa. -----Original Message----- From: Mark Hall [mailto:Mark.Hall@parkview.com] Sent: Monday, October 06, 2003 2:24 PM To: sqr-users@sqrug.org Subject: [sqr-users] Help! SQR mysteriously prints extra pages! I am totally stumped on this one. I have an sqr (DDP003P.SQR, The check advice print, for all of you peoplesoft types out there) that for some strange reason prints mystery pages. I can see no reason why. My checks print fine and the advices use the same layout. When I run the process to a pdf file the pdf looks fine. All the text and graphics are on one page just like they should be. When I look at the raw .lis file I only see one formfeed (at the bottom of the page where it should be). But when I run it to an hp printer it just goes crazy. It prints some stuff on the first page. Then it prints some stuff on the next page. Then it goes to the next page and prints a line. Then it prints a line on the next page. And so on for a few more pages. Then it prints a whole bunch of blank pages. Then it prints the category subtotals on one page. Then finally it prints the grand totals on the last page. It spits out about 45 pages between blanks and pages with stuff on them. I've tried the formfeed=no option but that doesn't work. Like I said I don't see any extra formfeeds. Does anyone have any idea what the problem is? Any help would be greatly appreciated. We are going live in a few weeks and I am at my wits end. I am running the PeopleSoft version of sqr on an AIX 5.1 box and printing to an HP LaserJet 4000T. PeopleTools release 8.4.3. Mark Hall IS Analyst ERP Team 260-373-8796 mark.hall@parkview.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing