From sqr-users-bounces@sqrug.org Sun Apr 01 00:02:21 2007 Date: Sun, 1 Apr 2007 00:00:03 -0400 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.23 $ $Date: 2006/07/27 16:28:24 $ 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 When your email address changes 3.4 Digests 3.5 Obtaining your password 3.6 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 2005/08/19: o Added note about spam filtering and sender verification systems blocking subscription confirmation messages. 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 Hyperion (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. On October 16, 2003, Hyperion acquired Brio Software. 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-2004 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. HyperionSQR : Hyperion- SQR List for discussion of SQR, SQR Developer (formerly called Brio.Report and Report Builder), and SQR iServer. To join, follow the link above, or send any message to "HyperionSQR- 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. Many sites are now implementing spam filtering and/or sender verification systems, which can prevent your confirmation message from getting delivered to you. You may need check for a message from "sqr- users-request[-AT-]sqrug.org" caught in the filter, or add that address to your list of authorized senders, in order to complete the confirmation process. 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 near the bottom of the page, and click the "Unsubscribe or edit options" button. You will then see the "member options" page; fill in your password at the top of the page and click "Log in". (If you have forgotton your password, see the ``Obtaining your Password'' section, below). This will take you to the "membership configuration" page. In the middle of that page, there will be an "Unsubscribing from sqr- users" section; simply turn on the "Yes, I really want to unsubscribe" checkbox and click the "Unsubscribe" button. You should get a page back saying that the removal was successful (as well as a confirmation email message). The removal will take effect immediately; there is no need to send in a confirmation e-mail message. If you don't know your password, you can choose to unsubscribe using e-mail confirmation. Instead of entering the password on the "member options" page, just click the "Unsubscribe" button found just below the "Log In" button. You will then see a page noting that a confirmation e-mail has been sent; once you follow the confirmation instructions in that e-mail your removal will be complete. 3.3. When your email address changes If your e-mail address changes -- for example, if your company changes names, or if your mail administrator changes the way your name is spelled out or abbreviated -- you will need to update your subscription to match your new address before you can post to the list (see the ``Posting'' section, below). To do this, simply log in to your "membership configuration" page (see the directions for logging in to this page in the ``Unsubscribing'' section, above). At the top of that page you'll see a "Changing your membership information" section. Fill out the text boxes and click the "Change My Address and Name" button. You will be sent a confirmation e-mail, and once you have responded to that e-mail your subscription will be updated and you can begin posting from your new address. 3.4. 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, log in to your "membership configuration" page (see the directions on doing this in the ``Unsubscribing'' section, above). At the bottom of the page, you will see a section titled "Your sqr-users Subscription Options". Change the "Set Digest Mode" option (the second option in the list) to "On" and click the "Submit My Changes" button at the bottom of the page. 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.5. Obtaining your password You will not be able to change your subscription options without your password. If you have forgotten your password, simply click the "Email My Password to Me" button on the "member options" page and your password will be emailed 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.6. 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). More specifically, in order for Mailman to recognize that this message was sent by a subscriber, the return address on your message much exactly match the addresss you used to subscribe -- if you have more than one address you will need to be sure that you are sending from the correct one. (See the ``When you email address changes'' section, above, if your e-mail address has changed since you subscribed.) 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 as other subscribers' mail systems repond....) 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. Also, if you are trying to send (for example) SQR source code files, make sure your mail-reader software is assigning proper MIME types to your attachments or they may not be distributed. 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 fuel 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 SQR product name and version number (see notes below) o Operating system name 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/Hyperion's, so it's very important to mention whether you are using Hyperion'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 and database version numbers listed here are the ones under which this copy of SQR was compiled, so you'll still need to include this information 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-bounces@sqrug.org Wed Apr 04 13:14:04 2007 Date: Wed, 4 Apr 2007 13:11:02 -0400 From: "Vaidyanathan, Ramakrishnan \(GE, Corporate, consultant\)" Subject: [sqr-users] passing parameters to sqr.. Hi, =20 Is it possible to pass parameters to the sqr while calling that sqr thr= u Unix shell script?Please help me with some code snippets how to go about = it. Thanks Ram _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Apr 04 13:32:53 2007 From: "Alexander, Steven" Subject: RE: [sqr-users] passing parameters to sqr.. Date: Wed, 4 Apr 2007 10:29:59 -0700 You can put as many parameters as you like at the end of the command line. Then use the "ask" command (to read them into #define parameters during compilation) or the "input" command (to read them into variables during execution). -----Original Message----- From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org [mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Wednesday, April 04, 2007 10:11 AM To: sqr-users@sqrug.org Subject: [sqr-users] passing parameters to sqr.. Hi, Is it possible to pass parameters to the sqr while calling that sqr thru Unix shell script?Please help me with some code snippets how to go about it. Thanks Ram _______________________________________________ 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-bounces@sqrug.org Wed Apr 04 13:43:00 2007 Subject: RE: [sqr-users] passing parameters to sqr.. Date: Wed, 4 Apr 2007 13:40:05 -0400 From: "Vaidyanathan, Ramakrishnan \(GE, Corporate, consultant\)" Hi, Actually there is a procedure in sqr that will take date as the input. = Can I pass the date as a parameter to this sqr thru unix shell script? -----Original Message----- From: sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org [mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org]On Behalf Of Alexander, Steven Sent: Wednesday, April 04, 2007 1:30 PM To: 'This list is for discussion about the SQR database reportinglanguagefrom Hyperion Solutions.' Subject: RE: [sqr-users] passing parameters to sqr.. You can put as many parameters as you like at the end of the command line. Then use the "ask" command (to read them into #define parameters during compilation) or the "input" command (to read them into variables during execution). -----Original Message----- From: sqr-users-bounces+steven.alexander=3Dsanjoseca.gov@sqrug.org [mailto:sqr-users-bounces+steven.alexander=3Dsanjoseca.gov@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Wednesday, April 04, 2007 10:11 AM To: sqr-users@sqrug.org Subject: [sqr-users] passing parameters to sqr.. Hi, =20 Is it possible to pass parameters to the sqr while calling that sqr thru Unix shell script?Please help me with some code snippets how to go about it. Thanks Ram _______________________________________________ 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-bounces@sqrug.org Wed Apr 04 17:11:57 2007 Subject: RE: [sqr-users] passing parameters to sqr.. Date: Wed, 4 Apr 2007 16:09:44 -0500 From: "Knapp, Richard" But not as a date. Only as the string representation of the date, yes? Richard Knapp EAS - Data Warehouse Group University of Missouri 615 Locust Street #200 Columbia, MO 65201 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Wednesday, April 04, 2007 12:40 PM To: This list is for discussion about the SQR database reportinglanguage fromHyperion Solutions. Subject: RE: [sqr-users] passing parameters to sqr.. Hi, Actually there is a procedure in sqr that will take date as the input. Can I pass the date as a parameter to this sqr thru unix shell script? -----Original Message----- From: sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org [mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org]On Behalf Of Alexander, Steven Sent: Wednesday, April 04, 2007 1:30 PM To: 'This list is for discussion about the SQR database reportinglanguagefrom Hyperion Solutions.' Subject: RE: [sqr-users] passing parameters to sqr.. You can put as many parameters as you like at the end of the command line. Then use the "ask" command (to read them into #define parameters during compilation) or the "input" command (to read them into variables during execution). -----Original Message----- From: sqr-users-bounces+steven.alexander=3Dsanjoseca.gov@sqrug.org [mailto:sqr-users-bounces+steven.alexander=3Dsanjoseca.gov@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Wednesday, April 04, 2007 10:11 AM To: sqr-users@sqrug.org Subject: [sqr-users] passing parameters to sqr.. Hi, =20 Is it possible to pass parameters to the sqr while calling that sqr thru Unix shell script?Please help me with some code snippets how to go about it. Thanks Ram _______________________________________________ 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-bounces@sqrug.org Wed Apr 04 14:47:31 2007 From: "Alexander, Steven" Subject: RE: [sqr-users] passing parameters to sqr.. Date: Wed, 4 Apr 2007 11:44:43 -0700 Yes. Any input statement will take from the command line first. When all the command line parameters are read, then the remaining input statements get data from the DOS window and the keyboard. -----Original Message----- From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org [mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Wednesday, April 04, 2007 10:40 AM To: This list is for discussion about the SQR database reportinglanguage fromHyperion Solutions. Subject: RE: [sqr-users] passing parameters to sqr.. Hi, Actually there is a procedure in sqr that will take date as the input. Can I pass the date as a parameter to this sqr thru unix shell script? -----Original Message----- From: sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org [mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org]On Behalf Of Alexander, Steven Sent: Wednesday, April 04, 2007 1:30 PM To: 'This list is for discussion about the SQR database reportinglanguagefrom Hyperion Solutions.' Subject: RE: [sqr-users] passing parameters to sqr.. You can put as many parameters as you like at the end of the command line. Then use the "ask" command (to read them into #define parameters during compilation) or the "input" command (to read them into variables during execution). -----Original Message----- From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org [mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Wednesday, April 04, 2007 10:11 AM To: sqr-users@sqrug.org Subject: [sqr-users] passing parameters to sqr.. Hi, Is it possible to pass parameters to the sqr while calling that sqr thru Unix shell script?Please help me with some code snippets how to go about it. Thanks Ram _______________________________________________ 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-bounces@sqrug.org Wed Apr 04 18:09:14 2007 From: "Alexander, Steven" Subject: RE: [sqr-users] passing parameters to sqr.. Date: Wed, 4 Apr 2007 15:06:15 -0700 Yes. Good point. -----Original Message----- From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org [mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org] On Behalf Of Knapp, Richard Sent: Wednesday, April 04, 2007 2:10 PM To: This list is for discussion about the SQR database reportinglanguage fromHyperion Solutions. Subject: RE: [sqr-users] passing parameters to sqr.. But not as a date. Only as the string representation of the date, yes? Richard Knapp EAS - Data Warehouse Group University of Missouri 615 Locust Street #200 Columbia, MO 65201 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Wednesday, April 04, 2007 12:40 PM To: This list is for discussion about the SQR database reportinglanguage fromHyperion Solutions. Subject: RE: [sqr-users] passing parameters to sqr.. Hi, Actually there is a procedure in sqr that will take date as the input. Can I pass the date as a parameter to this sqr thru unix shell script? -----Original Message----- From: sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org [mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org]On Behalf Of Alexander, Steven Sent: Wednesday, April 04, 2007 1:30 PM To: 'This list is for discussion about the SQR database reportinglanguagefrom Hyperion Solutions.' Subject: RE: [sqr-users] passing parameters to sqr.. You can put as many parameters as you like at the end of the command line. Then use the "ask" command (to read them into #define parameters during compilation) or the "input" command (to read them into variables during execution). -----Original Message----- From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org [mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Wednesday, April 04, 2007 10:11 AM To: sqr-users@sqrug.org Subject: [sqr-users] passing parameters to sqr.. Hi, Is it possible to pass parameters to the sqr while calling that sqr thru Unix shell script?Please help me with some code snippets how to go about it. Thanks Ram _______________________________________________ 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-bounces@sqrug.org Thu Apr 05 17:01:37 2007 Date: Thu, 05 Apr 2007 16:58:55 -0400 From: Wendy Biggs Subject: [sqr-users] actual execution time for select to process I need to determine the actual execution time of several different select statements. Has anyone else on this list done this? Is there an sqr variable that will get to that information? I can grab system time before & after the select statement and find the difference ... but that is not the same as the actual time the statement took to execute. Any and all help will be appreciated. --wb _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 05 17:20:34 2007 Subject: RE: [sqr-users] actual execution time for select to process Date: Thu, 5 Apr 2007 16:17:49 -0500 From: "Bob Stone" SHOW statements are great for this. Do a 'get-time' procedure of some sort (make your own, select sysdate from dual) and show the date a lot of places in your program. If you're really ambitious, select the sysdate in a format that would allow you to do some math on the timings. Maybe a #hour, #min, #sec variable - carry the 60, that sort of thing. SELECT to_char(sysdate, 'HH24') &hour, to_char(sysdate, 'MI') &min, to_char(sysdate, 'SS') &sec FROM DUAL something like if #new_hour < #old_hour #display_hours =3D (#new_hour + 24) - (#old_hour) else #display_hours =3D #new_hour - #old_hour end-if if #new_mins < #old_mins #display_mins =3D (#new_mins + 60) - (#old_mins) else #display_mins =3D #new_mins - #old_mins end-if show 'timings - ' #display_hours ':' #display_mins -----Original Message----- From: sqr-users-bounces+bstone=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+bstone=3Dfastenal.com@sqrug.org] On Behalf Of Wendy Biggs Sent: Thursday, April 05, 2007 3:59 PM To: sqr-users@sqrug.org Subject: [sqr-users] actual execution time for select to process I need to determine the actual execution time of several different=20 select statements. Has anyone else on this list done this? Is there an sqr variable that will get to that information? I can grab system time before & after the select statement and find=20 the difference ... but that is not the same as the actual time the=20 statement took to execute. Any and all help will be appreciated. --wb _______________________________________________ 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-bounces@sqrug.org Thu Apr 05 20:56:53 2007 From: "Steve Cavill" Subject: RE: [sqr-users] actual execution time for select to process Date: Fri, 6 Apr 2007 10:51:47 +1000 Datenow() sqr function will retur the time. Why do you say "I can grab system time before & after the select statement and find the difference" That's the elapsed time of the select. If you want the query execution CPU time you would need to look at the database logs. Steve. -----Original Message----- From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org [mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] On Behalf Of Wendy Biggs Sent: Friday, 6 April 2007 6:59 AM To: sqr-users@sqrug.org Subject: [sqr-users] actual execution time for select to process I need to determine the actual execution time of several different select statements. Has anyone else on this list done this? Is there an sqr variable that will get to that information? I can grab system time before & after the select statement and find the difference ... but that is not the same as the actual time the statement took to execute. Any and all help will be appreciated. --wb _______________________________________________ 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-bounces@sqrug.org Fri Apr 06 13:53:49 2007 Date: Fri, 06 Apr 2007 13:51:02 -0400 From: Wendy Biggs Subject: RE: [sqr-users] actual execution time for select to process right .... if I: let $start_table = datetostr(datenow(), 'DD-MON-YYYY HH24:MI:SS') begin-select ... end-select let $end_table = datetostr(datenow(), 'DD-MON-YYYY HH24:MI:SS') let #elapsed_table = round(datediff(strtodate($end_table, 'DD-MON-YYYY HH24:MI:SS'), strtodate($start_table, 'DD-MON-YYYY HH24:MI:SS'), 'second'), 0) then #elapsed_table contains the elapsed system time .... that is not what I want I want the query execution CPU time .... I do not have access to the database logs .... is there no way I can get that information though my program? Is there a way to set "something" on so that I can get a trace of the database log in my logsqr file? I ultimately want to print that CPU time as part of my output report. --wb At 08:51 PM 4/5/2007, you wrote: >Datenow() sqr function will retur the time. >Why do you say "I can grab system time before & after the select >statement and find the difference" >That's the elapsed time of the select. If you want the query >execution CPU time you would need to look at the database logs. >Steve. > >-----Original Message----- >From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org >[mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] >On Behalf Of Wendy Biggs >Sent: Friday, 6 April 2007 6:59 AM >To: sqr-users@sqrug.org >Subject: [sqr-users] actual execution time for select to process > >I need to determine the actual execution time of several different >select statements. >Has anyone else on this list done this? >Is there an sqr variable that will get to that information? > >I can grab system time before & after the select statement and find >the difference ... but that is not the same as the actual time >the statement took to execute. > >Any and all help will be appreciated. > > >--wb > > > >_______________________________________________ >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-bounces@sqrug.org Fri Apr 06 16:20:27 2007 From: "the dragon" Subject: RE: [sqr-users] actual execution time for select to process Date: Fri, 06 Apr 2007 15:17:43 -0500 That's as close as you're going to get without having actual database access through a tool such as sql*plus, toad, oem or whatever, and maybe running an explain on your code. Even then, the actual execution time will vary upon a lot of factors, including whether the query is in cache, or if there has been a lot of dml run on the tables affected by the query. Additionally, how long ago statistics was run will be important, as well as any new or removed indexes. I guess I am missing *why* *you* need such accurate measurements of sql execution timing; that's why you keep DBAs around. peace, 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. I work to live; I don't live to work. "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) It is impossible to defeat an ignorant man in argument. -- William G. McAdoo Religion is regarded by the common people as true, by the wise as false, and by the rulers as useful. -- Seneca "I distrust those people who know so well what God wants them to do because I notice it always coincides with their own desires." - Susan B. Anthony ----Original Message Follows---- right .... if I: let $start_table = datetostr(datenow(), 'DD-MON-YYYY HH24:MI:SS') begin-select ... end-select let $end_table = datetostr(datenow(), 'DD-MON-YYYY HH24:MI:SS') let #elapsed_table = round(datediff(strtodate($end_table, 'DD-MON-YYYY HH24:MI:SS'), strtodate($start_table, 'DD-MON-YYYY HH24:MI:SS'), 'second'), 0) then #elapsed_table contains the elapsed system time .... that is not what I want I want the query execution CPU time .... I do not have access to the database logs .... is there no way I can get that information though my program? Is there a way to set "something" on so that I can get a trace of the database log in my logsqr file? I ultimately want to print that CPU time as part of my output report. --wb _________________________________________________________________ MSN is giving away a trip to Vegas to see Elton John.  Enter to win today. http://msnconcertcontest.com?icid-nceltontagline _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Apr 06 16:54:17 2007 Date: Fri, 06 Apr 2007 16:51:38 -0400 From: Wendy Biggs Subject: RE: [sqr-users] actual execution time for select to process - why *I* want accurate measurements Ahh.... there you have it ... we are running Oracle 10g and the DBA's decided to change how they were collecting stats .... ever since then we've been having problems with observations missing from data view (they are there in the underlying table but not in the view) .... so far we, (the DBA's and my team) have not found a pattern to when the obs are dropping out of sight. The current solution is to delete the stats on several tables that are involved in the view and flush the shared pool every time the problem crops up and we report it (which is daily at this point). I don't mean to make them sound incompetent ... we just haven't found the bug yet and I'm having to watch the situation 'by hand' to ensure that our end users are not producing reports with missing data that they are not aware of. and in the middle of the pudding, one of the DBA's asked if we could please try running the query several different ways with different hints to see if they helped. So ... I've got this little program chroned out there to run on an hourly basis with several variations on the base query to watch for when the problem crops up and whether it is affected by including any of the suggested hints. and I want to watch the average time the various permutations of the query are taking because I'm not inclined to add hint A just because it means the obs are not being dropped if hint A consistently averages 2 minutes CPU time... I'm more inclined to want the bug found and fixed. besides, I'm a basically curious person, I like watching for patterns & the CPU times are giving me an idea of whether or not our 'end users' are running against the tables in between my runs. ... more than you really wanted to know, right? --wb At 04:17 PM 4/6/2007, you wrote: >That's as close as you're going to get without having actual >database access through a tool such as sql*plus, toad, oem or >whatever, and maybe running an explain on your code. Even then, the >actual execution time will vary upon a lot of factors, including >whether the query is in cache, or if there has been a lot of dml run >on the tables affected by the query. Additionally, how long ago >statistics was run will be important, as well as any new or removed indexes. > >I guess I am missing *why* *you* need such accurate measurements of >sql execution timing; that's why you keep DBAs around. > >peace, >clark 'the dragon' willis _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 05 08:29:08 2007 From: "Heather Philp" Subject: RE: [sqr-users] passing parameters to sqr.. Date: Thu, 5 Apr 2007 09:26:05 -0300 Yes, we run all our sqr programs at the unix level and pass parameters to them. Sample of the sqr code... INPUT $jobname INPUT $one_up INPUT $v_output_dir INPUT $v_database Sample of the Unix shell script sqr ${prog}.sqr -RT ${uid}/${pswd}@$ORACLE_SID ${PROG} ${one_up} ${output_dir} ${dbase} Data is accepted as character, so if you have dates/number you need to convert Note, we run using precompiled sqt's. Using this method, the ask command will not work, only the input. Heather -----Original Message----- From: Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) [mailto:ramakrishnan.vaidyanathan@ge.com] Sent: April 4, 2007 2:11 PM To: sqr-users@sqrug.org Subject: [sqr-users] passing parameters to sqr.. Hi, Is it possible to pass parameters to the sqr while calling that sqr thru Unix shell script?Please help me with some code snippets how to go about it. Thanks Ram _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Apr 13 11:30:59 2007 Subject: RE: [sqr-users] passing parameters to sqr.. Date: Fri, 13 Apr 2007 11:28:12 -0400 From: "Vaidyanathan, Ramakrishnan \(GE, Corporate, consultant\)" Hi Iam getting error when i use the following shell script sqr /t111/app01/psoft/usdx/usr/sqr/parallel.sqr -RT uid/pwd@ORACLE_SID -i/= t111/app01/psoft/usdx/sqr/ -ZIF/t111/app01/psoft/usdx/sqr/pssqr.unx 1 Iam calling sqr called parallel.sqr thru shell program. last parameter in t= he above syntax is "1" that i want to pass to the sqr. pls help Thanks Ram -----Original Message----- From: sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org [mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org]On Behalf Of Heather Philp Sent: Thursday, April 05, 2007 8:26 AM To: 'This list is for discussion about the SQR database reportinglanguagefrom Hyperion Solutions.' Subject: RE: [sqr-users] passing parameters to sqr.. Yes, we run all our sqr programs at the unix level and pass parameters to them. =20 Sample of the sqr code... INPUT $jobname INPUT $one_up INPUT $v_output_dir INPUT $v_database Sample of the Unix shell script=20 sqr ${prog}.sqr -RT ${uid}/${pswd}@$ORACLE_SID ${PROG} ${one_up} ${output_dir} ${dbase}=20 Data is accepted as character, so if you have dates/number you need to convert Note, we run using precompiled sqt's. Using this method, the ask command will not work, only the input. Heather=20 =20 -----Original Message----- From: Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) [mailto:ramakrishnan.vaidyanathan@ge.com]=20 Sent: April 4, 2007 2:11 PM To: sqr-users@sqrug.org Subject: [sqr-users] passing parameters to sqr.. Hi, =20 Is it possible to pass parameters to the sqr while calling that sqr thru Unix shell script?Please help me with some code snippets how to go about it. Thanks Ram _______________________________________________ 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-bounces@sqrug.org Fri Apr 13 21:13:16 2007 From: "Steve Cavill" Subject: RE: [sqr-users] passing parameters to sqr.. Date: Sat, 14 Apr 2007 11:06:30 +1000 And the error message would be? -----Original Message----- From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org [mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Saturday, 14 April 2007 1:28 AM To: This list is for discussion about the SQR database reportinglanguage fromHyperion Solutions. Subject: RE: [sqr-users] passing parameters to sqr.. Hi Iam getting error when i use the following shell script sqr /t111/app01/psoft/usdx/usr/sqr/parallel.sqr -RT uid/pwd@ORACLE_SID -i/t111/app01/psoft/usdx/sqr/ -ZIF/t111/app01/psoft/usdx/sqr/pssqr.unx 1 Iam calling sqr called parallel.sqr thru shell program. last parameter in the above syntax is "1" that i want to pass to the sqr. pls help Thanks Ram -----Original Message----- From: sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org [mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org]On Behalf Of Heather Philp Sent: Thursday, April 05, 2007 8:26 AM To: 'This list is for discussion about the SQR database reportinglanguagefrom Hyperion Solutions.' Subject: RE: [sqr-users] passing parameters to sqr.. Yes, we run all our sqr programs at the unix level and pass parameters to them. Sample of the sqr code... INPUT $jobname INPUT $one_up INPUT $v_output_dir INPUT $v_database Sample of the Unix shell script sqr ${prog}.sqr -RT ${uid}/${pswd}@$ORACLE_SID ${PROG} ${one_up} ${output_dir} ${dbase} Data is accepted as character, so if you have dates/number you need to convert Note, we run using precompiled sqt's. Using this method, the ask command will not work, only the input. Heather -----Original Message----- From: Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) [mailto:ramakrishnan.vaidyanathan@ge.com] Sent: April 4, 2007 2:11 PM To: sqr-users@sqrug.org Subject: [sqr-users] passing parameters to sqr.. Hi, Is it possible to pass parameters to the sqr while calling that sqr thru Unix shell script?Please help me with some code snippets how to go about it. Thanks Ram _______________________________________________ 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-bounces@sqrug.org Fri Apr 13 21:42:15 2007 From: "the dragon" Subject: RE: [sqr-users] passing parameters to sqr.. Date: Fri, 13 Apr 2007 20:39:37 -0500 You're going to have to think harder to receive the telepathic images he is sending... Don't you know us gurus can solve the problems of the planet with just a couple of lines of information? peace, 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. I work to live; I don't live to work. "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) It is impossible to defeat an ignorant man in argument. -- William G. McAdoo Religion is regarded by the common people as true, by the wise as false, and by the rulers as useful. -- Seneca "I distrust those people who know so well what God wants them to do because I notice it always coincides with their own desires." - Susan B. Anthony ----Original Message Follows---- And the error message would be? -----Original Message----- Hi Iam getting error when i use the following shell script sqr /t111/app01/psoft/usdx/usr/sqr/parallel.sqr -RT uid/pwd@ORACLE_SID -i/t111/app01/psoft/usdx/sqr/ -ZIF/t111/app01/psoft/usdx/sqr/pssqr.unx 1 Iam calling sqr called parallel.sqr thru shell program. last parameter in the above syntax is "1" that i want to pass to the sqr. pls help Thanks Ram _________________________________________________________________ Mortgage refinance is Hot. *Terms. Get a 5.375%* fix rate. Check savings https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h2bbb&disc=y&vers=925&s=4056&p=5117 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Apr 16 11:37:40 2007 From: "Dwight Schrute" Subject: RE: [sqr-users] passing parameters to sqr.. Date: Mon, 16 Apr 2007 10:34:59 -0500 You are passing the parameters in the wrong sequence. You have a flag (-RT) then your database connection and then more flags. Pass all the flags and then the connection. Like this: sqr /t111/app01/psoft/usdx/usr/sqr/parallel.sqr -RT -I/t111/app01/psoft/usdx/sqr/ -ZIF/t111/app01/psoft/usdx/sqr/pssqr.unx uid/pwd@ORACLE_SID 1 >From: "Vaidyanathan, Ramakrishnan (GE, Corporate,consultant)" > >Reply-To: "This list is for discussion about the SQR database >reportinglanguage from Hyperion Solutions." >To: "This list is for discussion about the SQR database reportinglanguage >fromHyperion Solutions." >Subject: RE: [sqr-users] passing parameters to sqr.. >Date: Fri, 13 Apr 2007 11:28:12 -0400 > >Hi > > Iam getting error when i use the following shell script > >sqr /t111/app01/psoft/usdx/usr/sqr/parallel.sqr -RT uid/pwd@ORACLE_SID >-i/t111/app01/psoft/usdx/sqr/ -ZIF/t111/app01/psoft/usdx/sqr/pssqr.unx 1 > >Iam calling sqr called parallel.sqr thru shell program. last parameter in >the above syntax is "1" that i want to pass to the sqr. >pls help > >Thanks >Ram > >-----Original Message----- >From: sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org >[mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org]On >Behalf Of Heather Philp >Sent: Thursday, April 05, 2007 8:26 AM >To: 'This list is for discussion about the SQR database >reportinglanguagefrom Hyperion Solutions.' >Subject: RE: [sqr-users] passing parameters to sqr.. > > >Yes, we run all our sqr programs at the unix level and pass parameters to >them. > >Sample of the sqr code... > INPUT $jobname > INPUT $one_up > INPUT $v_output_dir > INPUT $v_database > > >Sample of the Unix shell script >sqr ${prog}.sqr -RT ${uid}/${pswd}@$ORACLE_SID ${PROG} ${one_up} >${output_dir} ${dbase} > >Data is accepted as character, so if you have dates/number you need to >convert > >Note, we run using precompiled sqt's. Using this method, the ask command >will not work, only the input. > >Heather > >-----Original Message----- >From: Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) >[mailto:ramakrishnan.vaidyanathan@ge.com] >Sent: April 4, 2007 2:11 PM >To: sqr-users@sqrug.org >Subject: [sqr-users] passing parameters to sqr.. > >Hi, > > Is it possible to pass parameters to the sqr while calling that sqr >thru >Unix shell script?Please help me with some code snippets how to go about >it. > >Thanks >Ram > > > > >_______________________________________________ >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 _________________________________________________________________ Exercise your brain! Try Flexicon. http://games.msn.com/en/flexicon/default.htm?icid=flexicon_hmemailtaglineapril07 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Apr 16 13:23:49 2007 Date: Mon, 16 Apr 2007 13:21:03 -0400 From: Donald Mellen Subject: Re: [sqr-users] passing parameters to sqr.. Actually, it should be connection, then flags. Like this: sqr /t111/app01/psoft/usdx/usr/sqr/parallel.sqr uid/pwd@ORACLE_SID -RT -I/t111/app01/psoft/usdx/sqr/ -ZIF/t111/app01/psoft/usdx/sqr/pssqr.unx 1 The above assumes you have a ".sqt" file created and in place. Since you are specifying -RT to use the sqt file, it would be more efficient to use sqrt and the sqt file name, like: sqrt /t111/app01/psoft/usdx/usr/sqr/parallel.sqt uid/pwd@ORACLE_SID -I/t111/app01/psoft/usdx/sqr/ -ZIF/t111/app01/psoft/usdx/sqr/pssqr.unx 1 HTH, Don Dwight Schrute wrote: > You are passing the parameters in the wrong sequence. You have a flag > (-RT) then your database connection and then more flags. Pass all the > flags and then the connection. Like this: > > sqr /t111/app01/psoft/usdx/usr/sqr/parallel.sqr -RT > -I/t111/app01/psoft/usdx/sqr/ -ZIF/t111/app01/psoft/usdx/sqr/pssqr.unx > uid/pwd@ORACLE_SID 1 > > >> From: "Vaidyanathan, Ramakrishnan (GE, Corporate,consultant)" >> >> Reply-To: "This list is for discussion about the SQR database >> reportinglanguage from Hyperion Solutions." >> To: "This list is for discussion about the SQR database >> reportinglanguage fromHyperion Solutions." >> Subject: RE: [sqr-users] passing parameters to sqr.. >> Date: Fri, 13 Apr 2007 11:28:12 -0400 >> >> Hi >> >> Iam getting error when i use the following shell script >> >> sqr /t111/app01/psoft/usdx/usr/sqr/parallel.sqr -RT >> uid/pwd@ORACLE_SID -i/t111/app01/psoft/usdx/sqr/ >> -ZIF/t111/app01/psoft/usdx/sqr/pssqr.unx 1 >> >> Iam calling sqr called parallel.sqr thru shell program. last >> parameter in the above syntax is "1" that i want to pass to the sqr. >> pls help >> >> Thanks >> Ram >> >> -----Original Message----- >> From: sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org >> [mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org]On >> Behalf Of Heather Philp >> Sent: Thursday, April 05, 2007 8:26 AM >> To: 'This list is for discussion about the SQR database >> reportinglanguagefrom Hyperion Solutions.' >> Subject: RE: [sqr-users] passing parameters to sqr.. >> >> >> Yes, we run all our sqr programs at the unix level and pass >> parameters to >> them. >> >> Sample of the sqr code... >> INPUT $jobname >> INPUT $one_up >> INPUT $v_output_dir >> INPUT $v_database >> >> >> Sample of the Unix shell script >> sqr ${prog}.sqr -RT ${uid}/${pswd}@$ORACLE_SID ${PROG} ${one_up} >> ${output_dir} ${dbase} >> >> Data is accepted as character, so if you have dates/number you need to >> convert >> >> Note, we run using precompiled sqt's. Using this method, the ask >> command >> will not work, only the input. >> >> Heather >> >> -----Original Message----- >> From: Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) >> [mailto:ramakrishnan.vaidyanathan@ge.com] >> Sent: April 4, 2007 2:11 PM >> To: sqr-users@sqrug.org >> Subject: [sqr-users] passing parameters to sqr.. >> >> Hi, >> >> Is it possible to pass parameters to the sqr while calling that >> sqr thru >> Unix shell script?Please help me with some code snippets how to go >> about it. >> >> Thanks >> Ram >> >> >> >> >> _______________________________________________ >> 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 > > _________________________________________________________________ > Exercise your brain! Try Flexicon. > http://games.msn.com/en/flexicon/default.htm?icid=flexicon_hmemailtaglineapril07 > > > > _______________________________________________ > 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-bounces@sqrug.org Thu Apr 05 12:38:45 2007 Date: Thu, 5 Apr 2007 09:35:57 -0700 (PDT) From: rose kunwar Subject: [sqr-users] the required environment variable SQRDIR has not been defined Hi everyone, My SQRW.exe file does not work at all. Whenever I try to open this. exe file it says "(SQR 14) the required environment variable SQRDIR has not been defined". I tried to set paths from configuration manager and also copied another profile, but did not help me at all. Any suggestions? Thanks in advance Rose ____________________________________________________________________________________ Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 05 12:47:28 2007 Subject: RE: [sqr-users] the required environment variable SQRDIR has not beendefined Date: Thu, 5 Apr 2007 12:44:16 -0400 From: "Prasanna Menta" You will have to have your paths defined. Right click on the ICON ---> Click on properties and you should have target info, else you will get the error. Hope this helps to fix your problem. Prasanna=20 UMUC -----Original Message----- From: sqr-users-bounces+pmenta=3Dumuc.edu@sqrug.org [mailto:sqr-users-bounces+pmenta=3Dumuc.edu@sqrug.org] On Behalf Of rose kunwar Sent: Thursday, April 05, 2007 12:36 PM To: sqr-users@sqrug.org Subject: [sqr-users] the required environment variable SQRDIR has not beendefined Hi everyone, My SQRW.exe file does not work at all. Whenever I try to open this. exe file it says "(SQR 14) the required environment variable SQRDIR has not been defined". I tried to set paths from configuration manager and also copied another profile, but did not help me at all. Any suggestions? Thanks in advance Rose =20 ________________________________________________________________________ ____________ Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=3Dlist&sid=3D396546091 _______________________________________________ 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-bounces@sqrug.org Fri Apr 06 10:15:56 2007 Subject: RE: [sqr-users] the required environment variable SQRDIR has not beendefined Date: Fri, 6 Apr 2007 10:13:42 -0400 From: "Batchu, Venkata \(HR, IT\)" If you have sqr along with peoplesoft, an .ini file is provided to set the = environment variables. Include this ini file while running sqrw. To include use -ZIF\pssqr.ini in the argument.=20 If you are not using peoplesoft just create an .ini file and give some pat= h for SQRDIR, SQRDIR=3D c:\sqr (for eg:) and include the ini when running sqrw as above. - Venkat -----Original Message----- From: rose kunwar [mailto:rosekunwar@yahoo.com] Sent: Thursday, April 05, 2007 12:36 PM To: sqr-users@sqrug.org Subject: [sqr-users] the required environment variable SQRDIR has not beendefined Hi everyone, My SQRW.exe file does not work at all. Whenever I try to open this. exe fil= e it says "(SQR 14) the required environment variable SQRDIR has not been = defined". I tried to set paths from configuration manager and also copied a= nother profile, but did not help me at all. Any suggestions? Thanks in advance Rose =20 ___________________________________________________________________________= _________ Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=3Dlist&sid=3D396546091 ************************************************************************* This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. ************************************************************************* _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Apr 06 10:27:53 2007 Date: Fri, 6 Apr 2007 07:25:13 -0700 (PDT) From: rose kunwar Subject: Re: [sqr-users] the required environment variable SQRDIR has not beendefined Yes, I have SQR with peoplesoft. I did specify the path along with command line flag -ZIF in the shortcut of SQRW.EXE. Now shortcut is working, however, the original .exe file ( under BINW) is not working. Thankx Rose ----- Original Message ---- From: "Batchu, Venkata (HR, IT)" To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Sent: Friday, April 6, 2007 10:13:42 AM Subject: RE: [sqr-users] the required environment variable SQRDIR has not beendefined If you have sqr along with peoplesoft, an .ini file is provided to set the environment variables. Include this ini file while running sqrw. To include use -ZIF\pssqr.ini in the argument. If you are not using peoplesoft just create an .ini file and give some path for SQRDIR, SQRDIR= c:\sqr (for eg:) and include the ini when running sqrw as above. - Venkat -----Original Message----- From: rose kunwar [mailto:rosekunwar@yahoo.com] Sent: Thursday, April 05, 2007 12:36 PM To: sqr-users@sqrug.org Subject: [sqr-users] the required environment variable SQRDIR has not beendefined Hi everyone, My SQRW.exe file does not work at all. Whenever I try to open this. exe file it says "(SQR 14) the required environment variable SQRDIR has not been defined". I tried to set paths from configuration manager and also copied another profile, but did not help me at all. Any suggestions? Thanks in advance Rose ____________________________________________________________________________________ Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091 ************************************************************************* This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. ************************************************************************* _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ____________________________________________________________________________________ Sucker-punch spam with award-winning protection. Try the free Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/features_spam.html _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Apr 06 09:24:34 2007 Date: Fri, 6 Apr 2007 09:22:22 -0400 From: victorloghin@vgl.ca Subject: [sqr-users] actual execution time for select to process Wendy, I have timed the SQL in SQR programs. I have had to do it so much I actually developed a tool to do it. It will produce a report on the performance of all the SQR cursors in the program. The tool is available for free from my website www.vgl.ca and is under the Tools section and it is called SQL Timor. It works by having procedure calls before and after every SQL statement. It is such a tedious task to insert the calls into the code I developed another tool called the Formator to insert the procedure calls into the code. HTH, Victor Loghin VGL Consulting Inc. VictorLoghin@vgl.ca > Message: 4 > Date: Thu, 05 Apr 2007 16:58:55 -0400 > From: Wendy Biggs > Subject: [sqr-users] actual execution time for select to process > To: sqr-users@sqrug.org > > I need to determine the actual execution time of several different > select statements. > Has anyone else on this list done this? > Is there an sqr variable that will get to that information? > > I can grab system time before & after the select statement and find > the difference ... but that is not the same as the actual time the > statement took to execute. > > Any and all help will be appreciated. > > > --wb _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Apr 06 14:13:55 2007 Date: Fri, 6 Apr 2007 20:11:22 +0200 Subject: RE: [sqr-users] actual execution time for select to process From: "franck\.masson2002" if you need only the cpu time and not cpu time + data transfert let $start_table =3D datetostr(datenow(), 'DD-MON-YYYY HH24:MI:SS') let #first=3D1 begin-select if #first =3D 1 let #first=3D0 let $end_table =3D datetostr(datenow(), 'DD-MON-YYYY HH24:MI:SS') let #elapsed_table =3D round(datediff(strtodate($end_table,'DD-MON= -YYYY HH24:MI:SS'), strtodate($start_table, 'DD-MON-YYYY HH24:MI:SS'), 'sec= ond'), 0) end-if ... end-select If you are running on oracle or ODBC look to the option -B on the command l= ine or with the Begin Select franck, > right .... if I: > > let $start_table =3D datetostr(datenow(), 'DD-MON-YYYY HH24:MI:SS') > begin-select > ... > end-select > let $end_table =3D datetostr(datenow(), 'DD-MON-YYYY HH24:MI:SS') > let #elapsed_table =3D round(datediff(strtodate($end_table, > 'DD-MON-YYYY HH24:MI:SS'), > strtodate($start_table, 'DD-MON-YYYY > HH24:MI:SS'), 'second'), 0) > > > then #elapsed_table contains the elapsed system time .... that is > not what I want > > I want the query execution CPU time .... I do not have access to the > database logs .... is there no way I can get that information though > my program? > > Is there a way to set "something" on so that I can get a trace of the > database log in my logsqr file? > > I ultimately want to print that CPU time as part of my output report. > > --wb > > > At 08:51 PM 4/5/2007, you wrote: > >Datenow() sqr function will retur the time. > >Why do you say "I can grab system time before & after the select > >statement and find the difference" > >That's the elapsed time of the select. If you want the query > >execution CPU time you would need to look at the database logs. > >Steve. > > > >-----Original Message----- > >From: sqr-users-bounces+steve.cavill=3Dinfoclarity.com.au@sqrug.org > >[mailto:sqr-users-bounces+steve.cavill=3Dinfoclarity.com.au@sqrug.org] > >On Behalf Of Wendy Biggs > >Sent: Friday, 6 April 2007 6:59 AM > >To: sqr-users@sqrug.org > >Subject: [sqr-users] actual execution time for select to process > > > >I need to determine the actual execution time of several different > >select statements. > >Has anyone else on this list done this? > >Is there an sqr variable that will get to that information? > > > >I can grab system time before & after the select statement and find > >the difference ... but that is not the same as the actual time > >the statement took to execute. > > > >Any and all help will be appreciated. > > > > > >--wb > > > > > > > >_______________________________________________ > >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 >=20 Cr=E9ez votre adresse =E9lectronique prenom.nom@laposte.net 1 Go d'espace de stockage, anti-spam et anti-virus int=E9gr=E9s. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Apr 06 17:01:34 2007 Date: Fri, 06 Apr 2007 16:58:56 -0400 From: Wendy Biggs Subject: Re: [sqr-users] actual execution time for select to process - thank you franck and victor Thank you Franck and Victor ... Both of the toys you shared have been very helpful and instructive!! --wb At 09:22 AM 4/6/2007, victorloghin@vgl.ca wrote: >Wendy, > >I have timed the SQL in SQR programs. I have had to do it so much I actually >developed a tool to do it. It will produce a report on the performance of all >the SQR cursors in the program. The tool is available for free from my website >www.vgl.ca and is under the Tools section and it is called SQL Timor. It works >by having procedure calls before and after every SQL statement. It is such a >tedious task to insert the calls into the code I developed another tool called >the Formator to insert the procedure calls into the code. > >HTH, >Victor Loghin >VGL Consulting Inc. >VictorLoghin@vgl.ca > > > > Message: 4 > > Date: Thu, 05 Apr 2007 16:58:55 -0400 > > From: Wendy Biggs > > Subject: [sqr-users] actual execution time for select to process > > To: sqr-users@sqrug.org > > > > I need to determine the actual execution time of several different > > select statements. > > Has anyone else on this list done this? > > Is there an sqr variable that will get to that information? > > > > I can grab system time before & after the select statement and find > > the difference ... but that is not the same as the actual time the > > statement took to execute. > > > > Any and all help will be appreciated. > > > > > > --wb > >_______________________________________________ >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-bounces@sqrug.org Sat Apr 07 10:09:57 2007 From: "Benzenhafer, Dave" Subject: RE: [sqr-users] actual execution time for select to process Date: Sat, 7 Apr 2007 09:12:44 -0500 Wendy To get the overall run time of a report and print it on the report, I use the following. In the Setup section I put: DECLARE-VARIABLE Date $time_start END-DECLARE In the Begin-Program section I put: Begin-Program Let $time_start = datenow() Do Main Let #time_diff1 = datediff(datenow(), $time_start, 'second') Let #time_diff = round(#time_diff1,2) End-Program I report the time in seconds in the footer on the last page of the report. Begin-Footing 2 Page-Number (1,135) 'Page ' Last-Page () 'of ' If #time_diff > 0 Print #time_diff (2,117) Edit 999999 End-if End-Footing I hope this is helpful. Dave Benzenhafer -----Original Message----- From: Wendy Biggs [mailto:wendy.biggs@vt.edu] Sent: Friday, April 06, 2007 12:51 PM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Cc: Steve Cavill Subject: RE: [sqr-users] actual execution time for select to process right .... if I: let $start_table = datetostr(datenow(), 'DD-MON-YYYY HH24:MI:SS') begin-select ... end-select let $end_table = datetostr(datenow(), 'DD-MON-YYYY HH24:MI:SS') let #elapsed_table = round(datediff(strtodate($end_table, 'DD-MON-YYYY HH24:MI:SS'), strtodate($start_table, 'DD-MON-YYYY HH24:MI:SS'), 'second'), 0) then #elapsed_table contains the elapsed system time .... that is not what I want I want the query execution CPU time .... I do not have access to the database logs .... is there no way I can get that information though my program? Is there a way to set "something" on so that I can get a trace of the database log in my logsqr file? I ultimately want to print that CPU time as part of my output report. --wb At 08:51 PM 4/5/2007, you wrote: >Datenow() sqr function will retur the time. >Why do you say "I can grab system time before & after the select >statement and find the difference" >That's the elapsed time of the select. If you want the query >execution CPU time you would need to look at the database logs. >Steve. > >-----Original Message----- >From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org >[mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] >On Behalf Of Wendy Biggs >Sent: Friday, 6 April 2007 6:59 AM >To: sqr-users@sqrug.org >Subject: [sqr-users] actual execution time for select to process > >I need to determine the actual execution time of several different >select statements. >Has anyone else on this list done this? >Is there an sqr variable that will get to that information? > >I can grab system time before & after the select statement and find >the difference ... but that is not the same as the actual time >the statement took to execute. > >Any and all help will be appreciated. > > >--wb > > > >_______________________________________________ >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-bounces@sqrug.org Fri Apr 06 17:39:48 2007 From: "Alexander, Steven" Subject: RE: [sqr-users] actual execution time for select to process -why *I* want accurate measurements Date: Fri, 6 Apr 2007 14:30:05 -0700 I'm not familiar with your terminology. Does "observations missing from data view" mean that the view does not deliver all the rows in the underlying table? Have you checked the script for the view? Is it correct? Is it joining the main table to another table that could eliminate some rows? Does it have any "where" clauses that could eliminate some rows? Is it performing any aggregation functions that could combine some rows? Is it using the "distinct" keyword that could eliminate some rows? (Don't underestimate the havoc that bad data can cause.) You might try rebuilding the indices. If they're corrupt, they might not have some of the underlying rows. Also, when you speak of the DBAs collecting stats, do you mean generating statistics for the optimizer by analyzing the tables? Changing that would change the execution plan that the optimizer produces. In a clean database, that affects performance but not results. But in a database with questionable indices, that might not have been used previously, it could change the results. -----Original Message----- From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org [mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org] On Behalf Of Wendy Biggs Sent: Friday, April 06, 2007 1:52 PM To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: RE: [sqr-users] actual execution time for select to process -why *I* want accurate measurements Ahh.... there you have it ... we are running Oracle 10g and the DBA's decided to change how they were collecting stats .... ever since then we've been having problems with observations missing from data view (they are there in the underlying table but not in the view) .... so far we, (the DBA's and my team) have not found a pattern to when the obs are dropping out of sight. The current solution is to delete the stats on several tables that are involved in the view and flush the shared pool every time the problem crops up and we report it (which is daily at this point). I don't mean to make them sound incompetent ... we just haven't found the bug yet and I'm having to watch the situation 'by hand' to ensure that our end users are not producing reports with missing data that they are not aware of. and in the middle of the pudding, one of the DBA's asked if we could please try running the query several different ways with different hints to see if they helped. So ... I've got this little program chroned out there to run on an hourly basis with several variations on the base query to watch for when the problem crops up and whether it is affected by including any of the suggested hints. and I want to watch the average time the various permutations of the query are taking because I'm not inclined to add hint A just because it means the obs are not being dropped if hint A consistently averages 2 minutes CPU time... I'm more inclined to want the bug found and fixed. besides, I'm a basically curious person, I like watching for patterns & the CPU times are giving me an idea of whether or not our 'end users' are running against the tables in between my runs. ... more than you really wanted to know, right? --wb At 04:17 PM 4/6/2007, you wrote: >That's as close as you're going to get without having actual >database access through a tool such as sql*plus, toad, oem or >whatever, and maybe running an explain on your code. Even then, the >actual execution time will vary upon a lot of factors, including >whether the query is in cache, or if there has been a lot of dml run >on the tables affected by the query. Additionally, how long ago >statistics was run will be important, as well as any new or removed indexes. > >I guess I am missing *why* *you* need such accurate measurements of >sql execution timing; that's why you keep DBAs around. > >peace, >clark 'the dragon' willis _______________________________________________ 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-bounces@sqrug.org Fri Apr 06 18:02:22 2007 Subject: RE: [sqr-users] actual execution time for select to process -why *I* want accurate measurements Date: Fri, 6 Apr 2007 17:58:36 -0400 From: "Larry Roux" I saw something similar with 10g. If you have descending indexes you end u= p with lots of issues due to the indexes becoming functions. Once we set O= racle to ignore descending in indexes we got all the correct results every = time. ________________________________ From: sqr-users-bounces+lroux=3Dsyr.edu@sqrug.org on behalf of Alexander, S= teven Sent: Fri 4/6/2007 5:30 PM To: 'This list is for discussion about the SQR database reportinglanguagefr= om Hyperion Solutions.' Subject: RE: [sqr-users] actual execution time for select to process -why *= I* want accurate measurements I'm not familiar with your terminology. Does "observations missing from data view" mean that the view does not deliver all the rows in the underlying table? Have you checked the script for the view? Is it correct? Is it joining the main table to another table that could eliminate some rows? Does it have any "where" clauses that could eliminate some rows? Is it performing any aggregation functions that could combine some rows? Is it using the "distinct" keyword that could eliminate some rows? (Don't underestimate the havoc that bad data can cause.) You might try rebuilding the indices. If they're corrupt, they might not have some of the underlying rows. Also, when you speak of the DBAs collecting stats, do you mean generating statistics for the optimizer by analyzing the tables? Changing that would change the execution plan that the optimizer produces. In a clean database, that affects performance but not results. But in a database with questionable indices, that might not have been used previously, it could change the results. -----Original Message----- From: sqr-users-bounces+steven.alexander=3Dsanjoseca.gov@sqrug.org [mailto:sqr-users-bounces+steven.alexander=3Dsanjoseca.gov@sqrug.org] On Behalf Of Wendy Biggs Sent: Friday, April 06, 2007 1:52 PM To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: RE: [sqr-users] actual execution time for select to process -why *I* want accurate measurements Ahh.... there you have it ... we are running Oracle 10g and the DBA's decided to change how they were collecting stats .... ever since then we've been having problems with observations missing from data view (they are there in the underlying table but not in the view) .... so far we, (the DBA's and my team) have not found a pattern to when the obs are dropping out of sight. The current solution is to delete the stats on several tables that are involved in the view and flush the shared pool every time the problem crops up and we report it (which is daily at this point). I don't mean to make them sound incompetent ... we just haven't found the bug yet and I'm having to watch the situation 'by hand' to ensure that our end users are not producing reports with missing data that they are not aware of. and in the middle of the pudding, one of the DBA's asked if we could please try running the query several different ways with different hints to see if they helped. So ... I've got this little program chroned out there to run on an hourly basis with several variations on the base query to watch for when the problem crops up and whether it is affected by including any of the suggested hints. and I want to watch the average time the various permutations of the query are taking because I'm not inclined to add hint A just because it means the obs are not being dropped if hint A consistently averages 2 minutes CPU time... I'm more inclined to want the bug found and fixed. besides, I'm a basically curious person, I like watching for patterns & the CPU times are giving me an idea of whether or not our 'end users' are running against the tables in between my runs. ... more than you really wanted to know, right? --wb At 04:17 PM 4/6/2007, you wrote: >That's as close as you're going to get without having actual >database access through a tool such as sql*plus, toad, oem or >whatever, and maybe running an explain on your code. Even then, the >actual execution time will vary upon a lot of factors, including >whether the query is in cache, or if there has been a lot of dml run >on the tables affected by the query. Additionally, how long ago >statistics was run will be important, as well as any new or removed indexes. > >I guess I am missing *why* *you* need such accurate measurements of >sql execution timing; that's why you keep DBAs around. > >peace, >clark 'the dragon' willis _______________________________________________ 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-bounces@sqrug.org Wed Apr 11 18:50:29 2007 Date: Wed, 11 Apr 2007 15:48:12 -0700 From: "Marc Kane" Subject: [sqr-users] Page Breaks I am finishing up report where I am conducitng a CASE translsation to "group" the records in the report into 4 categories. I would like to have Page Breaks after each change of the value in the Group Variable. I have included the main procedure and the correlating print procedures: Begin-Procedure Get_MAIN_PROCEDURE !(Comment for Main Procedure) !**************************************** LET $ACCT_ID = ' ' LET $SA_ID = ' ' LET $BSEG_ID = ' ' LET $CRE_DTTM = ' ' LET $STAT_CHG_DTTM = ' ' LET $START_DATE = ' ' LET $END_DATE = ' ' LET $ORIG_DAYS = ' ' LET $REBILL_DAYS = ' ' LET $OVERRIDE = ' ' LET $ORIG_SQ = ' ' LET $BILLED_SQ = ' ' LET $ORIG_AMT = ' ' LET $FINAL_BILL_AMT = ' ' LET $ORIG_OPRID = ' ' LET $REBILLED_BY = ' ' LET $CANCEL_REASON = ' ' LET $TOTAL_DOLLAR_DIFF = ' ' LET $ORIG_PER_DAY = ' ' LET $FINAL_PER_DAY = ' ' LET $FINAL-ORIG = ' ' LET $DIVISION = ' ' LET $ROLENAME = ' ' LET $BREAK_LEVEL_A = ' ' SHOW 'IN Get_MAIN_PROCEDURE' Begin-SELECT F.ACCT_ID &ACCT_ID A.SA_ID &SA_ID A.BSEG_ID &BSEG_ID TO_DATE(A.CRE_DTTM) AS CREATE_DT &CRE_DTTM TO_DATE(A.STAT_CHG_DTTM) AS CHANGE_DT &STAT_CHG_DTTM TO_CHAR(A.START_DT,'YYYY-MM-DD') AS START_DT &START_DT TO_CHAR(A.END_DT,'YYYY-MM-DD') AS END_DT &END_DT (A.END_DT - A.START_DT) + 1 AS ORIG_DAYS &ORIG_DAYS (B.END_DT - B.START_DT) + 1 AS REBILL_DAYS &REBILL_DAYS B.SQ_OVERRIDE_SW AS SQ_OVERRIDE &OVERRIDE C.BILL_SQ AS ORIG_SQ &ORIG_SQ H.BILL_SQ AS BILLED_SQ &BILLED_SQ D.CUR_AMT AS ORIG_AMT &ORIG_AMT E.CUR_AMT AS NEW_BILL_AMT &FINAL_BILL_AMT D.FREEZE_OPRID AS ORIG_OPRID &ORIG_OPRID E.FREEZE_OPRID AS REBILLED_BY &REBILLED_BY G.DESCRSHORT AS CANCEL_REASON &CANCEL_REASON ROUND((E.CUR_AMT - D.CUR_AMT),2) AS TOTAL_$_DIFF &TOTAL_DOLLAR_DIFF ROUND((C.BILL_SQ / ((A.END_DT - A.START_DT) + 1)), 2) AS ORIG_PER_DAY &ORIG_PER_DAY ROUND((E.CUR_AMT / ((B.END_DT - B.START_DT) + 1)), 2) AS FINAL_PER_DAY &FINAL_PER_DAY (CASE WHEN P.ROLENAME LIKE 'BILL%' THEN 'BILLING' WHEN P.ROLENAME LIKE 'Bill%' THEN 'BILLING' WHEN P.ROLENAME LIKE 'CS%' THEN 'CS' WHEN P.ROLENAME LIKE '%BILL%' AND P.ROLENAME LIKE '%CS%'THEN 'PILOT' WHEN P.ROLENAME LIKE '%Bill%' AND P.ROLENAME LIKE '%CS%'THEN 'PILOT' ELSE 'OTHER' END) AS DIVISION &DIVISION P.ROLENAME &ROLENAME (CASE WHEN ROUND((E.CUR_AMT - D.CUR_AMT),2) <= 100 THEN '$100 Or Less' WHEN ROUND((E.CUR_AMT - D.CUR_AMT),2) > 100 AND ROUND((E.CUR_AMT - D.CUR_AMT),2) <=1000 THEN 'More Than $100' WHEN ROUND((E.CUR_AMT - D.CUR_AMT),2) > 1000 AND ROUND((E.CUR_AMT - D.CUR_AMT),2) <=5000 THEN 'More Than $1000' WHEN ROUND((E.CUR_AMT - D.CUR_AMT),2) > 5000 THEN 'More Than $5000' ELSE 'OTHER' END) AS TOTAL_$_DIFF &BREAK_LEVEL_A LET $ACCT_ID = LPAD(&ACCT_ID, 10, '0') LET $SA_ID = LPAD(&SA_ID, 10, '0') LET $BSEG_ID = LPAD(&BSEG_ID, 12, '0') LET $CRE_DTTM = &CRE_DTTM LET $STAT_CHG_DTTM = &STAT_CHG_DTTM LET $START_DATE = &START_DT LET $END_DATE = &END_DT LET $ORIG_DAYS = &ORIG_DAYS LET $REBILL_DAYS = &REBILL_DAYS LET $OVERRIDE = &OVERRIDE LET $ORIG_SQ = &ORIG_SQ LET $BILLED_SQ = &BILLED_SQ LET $ORIG_AMT = &ORIG_AMT LET $FINAL_BILL_AMT = &FINAL_BILL_AMT LET $ORIG_OPRID = &ORIG_OPRID LET $REBILLED_BY = &REBILLED_BY LET $CANCEL_REASON = &CANCEL_REASON LET $TOTAL_DOLLAR_DIFF = &TOTAL_DOLLAR_DIFF LET $ORIG_PER_DAY = &ORIG_PER_DAY LET $FINAL_PER_DAY = &FINAL_PER_DAY LET $FINAL-ORIG = &FINAL_PER_DAY - &ORIG_PER_DAY LET $DIVISION = &DIVISION LET $ROLENAME = &ROLENAME LET $BREAK_LEVEL_A = &BREAK_LEVEL_A SHOW 'ACCT ID: ' $ACCT_ID IF $report_type = 'D' DO Get_PRINT_OUTPUT_DETAIL !Programed END-IF IF $report_type = 'S' DO Get_PRINT_OUTPUT_SUMMARY !Programed END-IF FROM PS_CI_BSEG A, PS_CI_BILL_CAN_RSN G, PS_CI_BSEG B, PS_CI_BSEG_SQ C, PS_CI_BILL F, PS_CI_BSEG_SQ H, PS_CI_FT D, PS_CI_FT E, PSROLEUSER P WHERE TO_CHAR(TRUNC(A.STAT_CHG_DTTM), 'YYYY-MM-DD') BETWEEN TO_CHAR(TO_DATE(TO_DATE($start_dt,'DD-MON-YY')), 'YYYY-MM-DD') AND TO_CHAR(TO_DATE(TO_DATE($end_dt,'DD-MON-YY')), 'YYYY-MM-DD') AND A.CAN_RSN_CD = G.CAN_RSN_CD AND A.BSEG_ID = H.BSEG_ID AND H.UOM_CD = 'TGAL' AND A.BILL_ID = F.BILL_ID AND A.REBILL_SEG_ID = B.BSEG_ID AND B.BSEG_ID = C.BSEG_ID AND C.UOM_CD = 'TGAL' AND A.BSEG_ID = D.SIBLING_ID AND D.FT_TYPE_FLG = 'BS' AND B.BSEG_ID = E.SIBLING_ID AND D.FREEZE_OPRID = P.ROLEUSER AND (P.ROLENAME LIKE 'BILL%' OR P.ROLENAME LIKE 'bill%' OR P.ROLENAME LIKE 'CS%') ORDER BY 18 ASC, 1 ASC, 2 ASC, 6 ASC End-SELECT !***************************** End-Procedure !Get_Procedure_Main !***************************** Begin-Procedure Get_PRINT_OUTPUT_DETAIL !******************************** SHOW 'IN Get_PRINT_OUTPUT_DETAIL PROCEDURE' alter-printer point-size=9 font=4 ! Change font PRINT $ACCT_ID (+2,5,) BOLD PRINT $SA_ID (,18,) BOLD PRINT $BSEG_ID (,32,) BOLD PRINT $CRE_DTTM (,48,) BOLD PRINT $STAT_CHG_DTTM (,63,) BOLD PRINT $START_DATE (,78,) BOLD PRINT $END_DATE (,92,) BOLD PRINT $ORIG_DAYS (,106,) BOLD edit 9999 PRINT $REBILL_DAYS (,120,) BOLD edit 9999 PRINT $OVERRIDE (,141,) BOLD PRINT $ORIG_SQ (,151,) BOLD edit 999999 PRINT $BILLED_SQ (+2,10,)BOLD edit 999999 PRINT $ORIG_AMT (,25,) BOLD edit $$$$$$9.99 PRINT $FINAL_BILL_AMT (,39,) BOLD edit $$$$$$9.99 PRINT $ORIG_OPRID (,55,) BOLD PRINT $REBILLED_BY (,72,) BOLD PRINT $CANCEL_REASON (,87,) BOLD PRINT $TOTAL_DOLLAR_DIFF (,101,) BOLD edit $$$$$$9.99 PRINT $ORIG_PER_DAY (,113,) BOLD edit $$$$$$9.99 PRINT $FINAL_PER_DAY (,128,) BOLD edit $$$$$$9.99 PRINT $FINAL-ORIG (,144,) BOLD edit $$$$$$9.99 PRINT $DIVISION (,160,) BOLD !******************************** End-Procedure !Get_PRINT_OUTPUT_DETAIL !Programed !******************************** Begin-Procedure Get_PRINT_OUTPUT_SUMMARY !******************************** SHOW 'IN Get_PRINT_OUTPUT_SUMMARY PROCEDURE' alter-printer point-size=9 font=4 ! Change font PRINT $ACCT_ID (+1,5,) BOLD PRINT $ORIG_AMT (,19,) BOLD edit $$$$$$9.99 PRINT $FINAL_BILL_AMT (,35,) BOLD edit $$$$$$9.99 PRINT $REBILLED_BY (,51,) BOLD PRINT $TOTAL_DOLLAR_DIFF (,66,) BOLD edit $$$$$$9.99 PRINT $ORIG_PER_DAY (,82,) BOLD edit $$$$$$9.99 PRINT $FINAL_PER_DAY (,96,) BOLD edit $$$$$$9.99 PRINT $FINAL-ORIG (,113,) BOLD edit $$$$$$9.99 PRINT $DIVISION (,129,) BOLD PRINT $BREAK_LEVEL_A (,142,) BOLD On-Break Print=Change/Top-Page !******************************** End-Procedure !Get_PRINT_OUTPUT_SUMMARY !Programed !******************************** Any help would be appreciated. I would also like to print the count of each "Group" at the top of each "New" Page for that group. I have a count procedure but just have no idea what the necessary logic would be to execute the printing of these values in the proper place. -- Thank you, M. Kane Reports Developer (Crystal Reports/SQR) PeopleSoft CIS Production Support GES/New York, NY 702.349.5107 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Apr 11 20:36:41 2007 Date: Thu, 12 Apr 2007 01:33:58 +0100 (BST) From: ashwin reddy Subject: Re: [sqr-users] Page Breaks if you want to print on a new page use --> simply use "new-page" in the sqr for example begin-procedure xxx ..... ...... ... new-page ... ... end-procedure you can also use the on break logic to print on a new-page begin-select business_unit on-break PRINT=CHANGE/TOP-PAGE BEFORE=PROCESS-NEW-PAGE from ps_xxx end-select begin-procedure PROCESS-NEW-PAGE new-page end-procedure every time the new-page function is called the lines that are printed after it ar shifted to a new page hope this helps Mahesh Ravula Marc Kane wrote: I am finishing up report where I am conducitng a CASE translsation to "group" the records in the report into 4 categories. I would like to have Page Breaks after each change of the value in the Group Variable. I have included the main procedure and the correlating print procedures: Begin-Procedure Get_MAIN_PROCEDURE !(Comment for Main Procedure) !**************************************** LET $ACCT_ID = ' ' LET $SA_ID = ' ' LET $BSEG_ID = ' ' LET $CRE_DTTM = ' ' LET $STAT_CHG_DTTM = ' ' LET $START_DATE = ' ' LET $END_DATE = ' ' LET $ORIG_DAYS = ' ' LET $REBILL_DAYS = ' ' LET $OVERRIDE = ' ' LET $ORIG_SQ = ' ' LET $BILLED_SQ = ' ' LET $ORIG_AMT = ' ' LET $FINAL_BILL_AMT = ' ' LET $ORIG_OPRID = ' ' LET $REBILLED_BY = ' ' LET $CANCEL_REASON = ' ' LET $TOTAL_DOLLAR_DIFF = ' ' LET $ORIG_PER_DAY = ' ' LET $FINAL_PER_DAY = ' ' LET $FINAL-ORIG = ' ' LET $DIVISION = ' ' LET $ROLENAME = ' ' LET $BREAK_LEVEL_A = ' ' SHOW 'IN Get_MAIN_PROCEDURE' Begin-SELECT F.ACCT_ID &ACCT_ID A.SA_ID &SA_ID A.BSEG_ID &BSEG_ID TO_DATE(A.CRE_DTTM) AS CREATE_DT &CRE_DTTM TO_DATE(A.STAT_CHG_DTTM) AS CHANGE_DT &STAT_CHG_DTTM TO_CHAR(A.START_DT,'YYYY-MM-DD') AS START_DT &START_DT TO_CHAR(A.END_DT,'YYYY-MM-DD') AS END_DT &END_DT (A.END_DT - A.START_DT) + 1 AS ORIG_DAYS &ORIG_DAYS (B.END_DT - B.START_DT) + 1 AS REBILL_DAYS &REBILL_DAYS B.SQ_OVERRIDE_SW AS SQ_OVERRIDE &OVERRIDE C.BILL_SQ AS ORIG_SQ &ORIG_SQ H.BILL_SQ AS BILLED_SQ &BILLED_SQ D.CUR_AMT AS ORIG_AMT &ORIG_AMT E.CUR_AMT AS NEW_BILL_AMT &FINAL_BILL_AMT D.FREEZE_OPRID AS ORIG_OPRID &ORIG_OPRID E.FREEZE_OPRID AS REBILLED_BY &REBILLED_BY G.DESCRSHORT AS CANCEL_REASON &CANCEL_REASON ROUND((E.CUR_AMT - D.CUR_AMT),2) AS TOTAL_$_DIFF &TOTAL_DOLLAR_DIFF ROUND((C.BILL_SQ / ((A.END_DT - A.START_DT) + 1)), 2) AS ORIG_PER_DAY &ORIG_PER_DAY ROUND((E.CUR_AMT / ((B.END_DT - B.START_DT) + 1)), 2) AS FINAL_PER_DAY &FINAL_PER_DAY (CASE WHEN P.ROLENAME LIKE 'BILL%' THEN 'BILLING' WHEN P.ROLENAME LIKE 'Bill%' THEN 'BILLING' WHEN P.ROLENAME LIKE 'CS%' THEN 'CS' WHEN P.ROLENAME LIKE '%BILL%' AND P.ROLENAME LIKE '%CS%'THEN 'PILOT' WHEN P.ROLENAME LIKE '%Bill%' AND P.ROLENAME LIKE '%CS%'THEN 'PILOT' ELSE 'OTHER' END) AS DIVISION &DIVISION P.ROLENAME &ROLENAME (CASE WHEN ROUND((E.CUR_AMT - D.CUR_AMT),2) <= 100 THEN '$100 Or Less' WHEN ROUND((E.CUR_AMT - D.CUR_AMT),2) > 100 AND ROUND((E.CUR_AMT - D.CUR_AMT),2) <=1000 THEN 'More Than $100' WHEN ROUND((E.CUR_AMT - D.CUR_AMT),2) > 1000 AND ROUND((E.CUR_AMT - D.CUR_AMT),2) <=5000 THEN 'More Than $1000' WHEN ROUND((E.CUR_AMT - D.CUR_AMT),2) > 5000 THEN 'More Than $5000' ELSE 'OTHER' END) AS TOTAL_$_DIFF &BREAK_LEVEL_A LET $ACCT_ID = LPAD(&ACCT_ID, 10, '0') LET $SA_ID = LPAD(&SA_ID, 10, '0') LET $BSEG_ID = LPAD(&BSEG_ID, 12, '0') LET $CRE_DTTM = &CRE_DTTM LET $STAT_CHG_DTTM = &STAT_CHG_DTTM LET $START_DATE = &START_DT LET $END_DATE = &END_DT LET $ORIG_DAYS = &ORIG_DAYS LET $REBILL_DAYS = &REBILL_DAYS LET $OVERRIDE = &OVERRIDE LET $ORIG_SQ = &ORIG_SQ LET $BILLED_SQ = &BILLED_SQ LET $ORIG_AMT = &ORIG_AMT LET $FINAL_BILL_AMT = &FINAL_BILL_AMT LET $ORIG_OPRID = &ORIG_OPRID LET $REBILLED_BY = &REBILLED_BY LET $CANCEL_REASON = &CANCEL_REASON LET $TOTAL_DOLLAR_DIFF = &TOTAL_DOLLAR_DIFF LET $ORIG_PER_DAY = &ORIG_PER_DAY LET $FINAL_PER_DAY = &FINAL_PER_DAY LET $FINAL-ORIG = &FINAL_PER_DAY - &ORIG_PER_DAY LET $DIVISION = &DIVISION LET $ROLENAME = &ROLENAME LET $BREAK_LEVEL_A = &BREAK_LEVEL_A SHOW 'ACCT ID: ' $ACCT_ID IF $report_type = 'D' DO Get_PRINT_OUTPUT_DETAIL !Programed END-IF IF $report_type = 'S' DO Get_PRINT_OUTPUT_SUMMARY !Programed END-IF FROM PS_CI_BSEG A, PS_CI_BILL_CAN_RSN G, PS_CI_BSEG B, PS_CI_BSEG_SQ C, PS_CI_BILL F, PS_CI_BSEG_SQ H, PS_CI_FT D, PS_CI_FT E, PSROLEUSER P WHERE TO_CHAR(TRUNC(A.STAT_CHG_DTTM), 'YYYY-MM-DD') BETWEEN TO_CHAR(TO_DATE(TO_DATE($start_dt,'DD-MON-YY')), 'YYYY-MM-DD') AND TO_CHAR(TO_DATE(TO_DATE($end_dt,'DD-MON-YY')), 'YYYY-MM-DD') AND A.CAN_RSN_CD = G.CAN_RSN_CD AND A.BSEG_ID = H.BSEG_ID AND H.UOM_CD = 'TGAL' AND A.BILL_ID = F.BILL_ID AND A.REBILL_SEG_ID = B.BSEG_ID AND B.BSEG_ID = C.BSEG_ID AND C.UOM_CD = 'TGAL' AND A.BSEG_ID = D.SIBLING_ID AND D.FT_TYPE_FLG = 'BS' AND B.BSEG_ID = E.SIBLING_ID AND D.FREEZE_OPRID = P.ROLEUSER AND (P.ROLENAME LIKE 'BILL%' OR P.ROLENAME LIKE 'bill%' OR P.ROLENAME LIKE 'CS%') ORDER BY 18 ASC, 1 ASC, 2 ASC, 6 ASC End-SELECT !***************************** End-Procedure !Get_Procedure_Main !***************************** Begin-Procedure Get_PRINT_OUTPUT_DETAIL !******************************** SHOW 'IN Get_PRINT_OUTPUT_DETAIL PROCEDURE' alter-printer point-size=9 font=4 ! Change font PRINT $ACCT_ID (+2,5,) BOLD PRINT $SA_ID (,18,) BOLD PRINT $BSEG_ID (,32,) BOLD PRINT $CRE_DTTM (,48,) BOLD PRINT $STAT_CHG_DTTM (,63,) BOLD PRINT $START_DATE (,78,) BOLD PRINT $END_DATE (,92,) BOLD PRINT $ORIG_DAYS (,106,) BOLD edit 9999 PRINT $REBILL_DAYS (,120,) BOLD edit 9999 PRINT $OVERRIDE (,141,) BOLD PRINT $ORIG_SQ (,151,) BOLD edit 999999 PRINT $BILLED_SQ (+2,10,)BOLD edit 999999 PRINT $ORIG_AMT (,25,) BOLD edit $$$$$$9.99 PRINT $FINAL_BILL_AMT (,39,) BOLD edit $$$$$$9.99 PRINT $ORIG_OPRID (,55,) BOLD PRINT $REBILLED_BY (,72,) BOLD PRINT $CANCEL_REASON (,87,) BOLD PRINT $TOTAL_DOLLAR_DIFF (,101,) BOLD edit $$$$$$9.99 PRINT $ORIG_PER_DAY (,113,) BOLD edit $$$$$$9.99 PRINT $FINAL_PER_DAY (,128,) BOLD edit $$$$$$9.99 PRINT $FINAL-ORIG (,144,) BOLD edit $$$$$$9.99 PRINT $DIVISION (,160,) BOLD !******************************** End-Procedure !Get_PRINT_OUTPUT_DETAIL !Programed !******************************** Begin-Procedure Get_PRINT_OUTPUT_SUMMARY !******************************** SHOW 'IN Get_PRINT_OUTPUT_SUMMARY PROCEDURE' alter-printer point-size=9 font=4 ! Change font PRINT $ACCT_ID (+1,5,) BOLD PRINT $ORIG_AMT (,19,) BOLD edit $$$$$$9.99 PRINT $FINAL_BILL_AMT (,35,) BOLD edit $$$$$$9.99 PRINT $REBILLED_BY (,51,) BOLD PRINT $TOTAL_DOLLAR_DIFF (,66,) BOLD edit $$$$$$9.99 PRINT $ORIG_PER_DAY (,82,) BOLD edit $$$$$$9.99 PRINT $FINAL_PER_DAY (,96,) BOLD edit $$$$$$9.99 PRINT $FINAL-ORIG (,113,) BOLD edit $$$$$$9.99 PRINT $DIVISION (,129,) BOLD PRINT $BREAK_LEVEL_A (,142,) BOLD On-Break Print=Change/Top-Page !******************************** End-Procedure !Get_PRINT_OUTPUT_SUMMARY !Programed !******************************** Any help would be appreciated. I would also like to print the count of each "Group" at the top of each "New" Page for that group. I have a count procedure but just have no idea what the necessary logic would be to execute the printing of these values in the proper place. -- Thank you, M. Kane Reports Developer (Crystal Reports/SQR) PeopleSoft CIS Production Support GES/New York, NY 702.349.5107 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users --------------------------------- Yahoo! Mail is the world's favourite email. Don't settle for less, sign up for your freeaccount today. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 12 15:54:23 2007 Date: Thu, 12 Apr 2007 19:51:47 +0000 (GMT) From: ashwin reddy Subject: [sqr-users] creating a .XLS Excel file I have created a lot of csv files. But i always wanted to create a .xls file so that all the leading zeros would be visibile. Has anyone written a sqr that can generate a excel file output using SQR's. I even know that the leading zeros in csv can be visible if we use ="number" ....i just want to know if there is a way a .xls report file can be generated using sqr's Thanks, Ashwin ___________________________________________________________ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 12 16:47:30 2007 Date: Thu, 12 Apr 2007 13:44:42 -0700 (PDT) From: Arun Kumar Subject: Re: [sqr-users] creating a .XLS Excel file You can try creating 'sylk' format files excel will open them. Search the home page for 'sylk' and slk .. You will get lots of hits.. ----- Original Message ---- From: ashwin reddy To: sqr-users@sqrug.org Sent: Thursday, April 12, 2007 3:51:47 PM Subject: [sqr-users] creating a .XLS Excel file I have created a lot of csv files. But i always wanted to create a .xls file so that all the leading zeros would be visibile. Has anyone written a sqr that can generate a excel file output using SQR's. I even know that the leading zeros in csv can be visible if we use ="number" ....i just want to know if there is a way a .xls report file can be generated using sqr's Thanks, Ashwin ___________________________________________________________ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ____________________________________________________________________________________ TV dinner still cooling? Check out "Tonight's Picks" on Yahoo! TV. http://tv.yahoo.com/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 12 16:50:52 2007 Subject: RE: [sqr-users] creating a .XLS Excel file Date: Thu, 12 Apr 2007 15:45:21 -0500 From: "Knapp, Richard" Try this: http://netghost.narod.ru/gff/graphics/summary/micsylk.htm Richard Knapp EAS - Data Warehouse Group University of Missouri 615 Locust Street #200 Columbia, MO 65201 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of ashwin reddy Sent: Thursday, April 12, 2007 2:52 PM To: sqr-users@sqrug.org Subject: [sqr-users] creating a .XLS Excel file I have created a lot of csv files. But i always wanted to create a .xls file so that all the leading zeros would be visibile. Has anyone written a sqr that can generate a excel file output using SQR's. I even know that the leading zeros in csv can be visible if we use =3D"number" ....i just want to know if there is a way a .xls report file can be generated using sqr's Thanks, Ashwin ___________________________________________________________ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/=20 _______________________________________________ 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-bounces@sqrug.org Fri Apr 13 03:08:08 2007 Subject: RE: [sqr-users] creating a .XLS Excel file Date: Fri, 13 Apr 2007 08:04:00 +0100 From: "Simpson, Don I \(UK\)" Ashwin, I've done a fair bit of this, using Symbolic Link (SYLK) Some hints: 1 The avaiable documentation of SYLK is scant and not in any great depth. 2 There are some Excel features, mainly sophisticated ones like cell borders, lines, worksheets, etc., that it seems not to be able to represent. It can do colours. 3 The SYLK format is complicated. I've found the best way to grab it for the end-user purpose is to create a simple spreadsheet of what you want your end-user output to look like, save it as SYLK, look at the SYLK in a text editor, and try to interpret what you are seeing (not always easy). 4 The format requires three basic "types" of output; what I will call pre-amble, then column headers, then cell data. If you build your example spreadsheet bit by bit, you can see what these look like., i.e. first of all, save an empty spreadsheet as SYLK, that will give you the preamble, then add your column headers, etc.. 5 You can manipulate fonts a bit, but, again, it's not crystal clear how it works. 6 If you get SYLK interpretration errors from Excel when you attempt to open your output, it can be very difficult to work out where they actually are, as the line numbers that Excel gives seem to bear no relationship whatsoever to the number of "lines" in your SYLK. The problem is is knowing what SYKL thinks is a "line" (it isn't the number of text lines in the file, for starters!!!) Hope this helps. If I can help any more, just post your request through the forum. Regards, Don Simpson =20 Sent by BAE Systems plc or a member of the BAE Systems group of companies BAE Systems plc A company registered in England and Wales Company Number 1470151 Registered Office: 6 Carlton Gardens, London SW1Y 5AD, United Kingdom -----Original Message----- From: sqr-users-bounces+don.i.simpson=3Dbaesystems.com@sqrug.org [mailto:sqr-users-bounces+don.i.simpson=3Dbaesystems.com@sqrug.org] On Behalf Of Arun Kumar Sent: 12 April 2007 21:45 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] creating a .XLS Excel file *** WARNING *** This mail has originated outside your organization, either from an external partner or the Global Internet.=20 Keep this in mind if you answer this message.=20 You can try creating 'sylk' format files excel will open them. Search the home page for 'sylk' and slk .. You will get lots of hits.. ----- Original Message ---- From: ashwin reddy To: sqr-users@sqrug.org Sent: Thursday, April 12, 2007 3:51:47 PM Subject: [sqr-users] creating a .XLS Excel file I have created a lot of csv files. But i always wanted to create a .xls file so that all the leading zeros would be visibile. Has anyone written a sqr that can generate a excel file output using SQR's. I even know that the leading zeros in csv can be visible if we use =3D"number" ....i just want to know if there is a way a .xls report file can be generated using sqr's Thanks, Ashwin ___________________________________________________________ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/=20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users =20=20=20=20=20=20=20 ________________________________________________________________________ ____________ TV dinner still cooling?=20 Check out "Tonight's Picks" on Yahoo! TV. http://tv.yahoo.com/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ******************************************************************** This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ******************************************************************** _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 12 16:30:29 2007 Date: Thu, 12 Apr 2007 16:27:45 -0400 From: "Vaidyanathan, Ramakrishnan \(GE, Corporate, consultant\)" Subject: [sqr-users] Kicking of Sqr procedures.. Hi All, I have 2 procedures in a sqr. I want to kick both of the procedures parall= ely at the same time. For eg. I have A and B as 2 procedures. I want to kic= k off A and B procedures parallely.Once I kick off procedure A, it shouldn'= t wait for its completion. I should be able to kick off B simultaneously. I= s it possible to achieve this? Thanks Ram _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 12 16:54:28 2007 Subject: RE: [sqr-users] Kicking of Sqr procedures.. Date: Thu, 12 Apr 2007 13:50:28 -0700 From: "Lynds,Rick" Ram, The only way that I see to do that is to create the procedures in separate SQRs and issue system commands to invoke them as separate processes. I don't know of any way to create separate threads of execution in SQR. Rick=20 -----Original Message----- From: sqr-users-bounces+rlynds=3Dmwdh2o.com@sqrug.org [mailto:sqr-users-bounces+rlynds=3Dmwdh2o.com@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Thursday, April 12, 2007 1:28 PM To: sqr-users@sqrug.org Subject: [sqr-users] Kicking of Sqr procedures.. Hi All, I have 2 procedures in a sqr. I want to kick both of the procedures parallely at the same time. For eg. I have A and B as 2 procedures. I want to kick off A and B procedures parallely.Once I kick off procedure A, it shouldn't wait for its completion. I should be able to kick off B simultaneously. Is it possible to achieve this? Thanks Ram _______________________________________________ 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-bounces@sqrug.org Thu Apr 12 16:59:59 2007 From: "the dragon" Subject: RE: [sqr-users] Kicking of Sqr procedures.. Date: Thu, 12 Apr 2007 15:57:21 -0500 correct only one procedure can execute in an sqr at a time. peace, 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. I work to live; I don't live to work. "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) It is impossible to defeat an ignorant man in argument. -- William G. McAdoo Religion is regarded by the common people as true, by the wise as false, and by the rulers as useful. -- Seneca "I distrust those people who know so well what God wants them to do because I notice it always coincides with their own desires." - Susan B. Anthony ----Original Message Follows---- Ram, The only way that I see to do that is to create the procedures in separate SQRs and issue system commands to invoke them as separate processes. I don't know of any way to create separate threads of execution in SQR. Rick -----Original Message----- Hi All, I have 2 procedures in a sqr. I want to kick both of the procedures parallely at the same time. For eg. I have A and B as 2 procedures. I want to kick off A and B procedures parallely.Once I kick off procedure A, it shouldn't wait for its completion. I should be able to kick off B simultaneously. Is it possible to achieve this? Thanks Ram _________________________________________________________________ Interest Rates Fall Again! $430,000 Mortgage for $1,399/mo - Calculate new payment http://www.lowermybills.com/lre/index.jsp?sourceid=lmb-9632-18679&moid=7581 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 12 17:04:37 2007 Date: Thu, 12 Apr 2007 14:02:00 -0700 (PDT) From: Arun Kumar Subject: Re: [sqr-users] Kicking of Sqr procedures.. No. Could you explain why you want to do this? Just curious on the design. ----- Original Message ---- From: "Vaidyanathan, Ramakrishnan (GE, Corporate, consultant)" To: sqr-users@sqrug.org Sent: Thursday, April 12, 2007 4:27:45 PM Subject: [sqr-users] Kicking of Sqr procedures.. Hi All, I have 2 procedures in a sqr. I want to kick both of the procedures parallely at the same time. For eg. I have A and B as 2 procedures. I want to kick off A and B procedures parallely.Once I kick off procedure A, it shouldn't wait for its completion. I should be able to kick off B simultaneously. Is it possible to achieve this? Thanks Ram _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ____________________________________________________________________________________ Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Apr 12 17:20:03 2007 Subject: RE: [sqr-users] Kicking of Sqr procedures.. Date: Thu, 12 Apr 2007 17:17:11 -0400 From: "Vaidyanathan, Ramakrishnan \(GE, Corporate, consultant\)" There is no specific reason for this..Jus wanted to know if its possible or= not. -----Original Message----- From: sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org [mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org]On Behalf Of Arun Kumar Sent: Thursday, April 12, 2007 5:02 PM To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] Kicking of Sqr procedures.. No. Could you explain why you want to do this? Just curious on the design. ----- Original Message ---- From: "Vaidyanathan, Ramakrishnan (GE, Corporate, consultant)" To: sqr-users@sqrug.org Sent: Thursday, April 12, 2007 4:27:45 PM Subject: [sqr-users] Kicking of Sqr procedures.. Hi All, I have 2 procedures in a sqr. I want to kick both of the procedures par= allely at the same time. For eg. I have A and B as 2 procedures. I want to = kick off A and B procedures parallely.Once I kick off procedure A, it shoul= dn't wait for its completion. I should be able to kick off B simultaneously= . Is it possible to achieve this? Thanks Ram _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users =20=20=20=20=20=20=20 ___________________________________________________________________________= _________ Looking for earth-friendly autos?=20 Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ _______________________________________________ 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-bounces@sqrug.org Thu Apr 12 17:29:01 2007 Subject: RE: [sqr-users] Kicking of Sqr procedures.. Date: Thu, 12 Apr 2007 16:26:44 -0500 From: "Knapp, Richard" SQR is a 'procedural' language. Usually this means one step at a time. As someone has already mentioned you can spawn both processes without waits. Richard Knapp EAS - Data Warehouse Group University of Missouri 615 Locust Street #200 Columbia, MO 65201 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Thursday, April 12, 2007 4:17 PM To: This list is for discussion about the SQR database reportinglanguage fromHyperion Solutions. Subject: RE: [sqr-users] Kicking of Sqr procedures.. There is no specific reason for this..Jus wanted to know if its possible or not. -----Original Message----- From: sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org [mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org]On Behalf Of Arun Kumar Sent: Thursday, April 12, 2007 5:02 PM To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] Kicking of Sqr procedures.. No. Could you explain why you want to do this? Just curious on the design. ----- Original Message ---- From: "Vaidyanathan, Ramakrishnan (GE, Corporate, consultant)" To: sqr-users@sqrug.org Sent: Thursday, April 12, 2007 4:27:45 PM Subject: [sqr-users] Kicking of Sqr procedures.. Hi All, I have 2 procedures in a sqr. I want to kick both of the procedures parallely at the same time. For eg. I have A and B as 2 procedures. I want to kick off A and B procedures parallely.Once I kick off procedure A, it shouldn't wait for its completion. I should be able to kick off B simultaneously. Is it possible to achieve this? Thanks Ram _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users =20=20=20=20=20=20=20 ________________________________________________________________________ ____________ Looking for earth-friendly autos?=20 Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ _______________________________________________ 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-bounces@sqrug.org Thu Apr 12 18:06:55 2007 Subject: RE: [sqr-users] Kicking of Sqr procedures.. Date: Thu, 12 Apr 2007 17:04:00 -0500 From: "Bob Stone" If you're looking to do something parallel you can set it up pretty easily in SQR - it's just multiple instances of the same SQR as everyone else says. But you would break down your sections of program into their own SQR to do that parallel section. One thing we've done before to get things to process in parallel is run a sort of 'preprogram' to set up a run control, and then kick off multiple of the 'parallel-program' for each of the run controls the preprogram set up for. In our case it's batch processing, so we know the entire list of what needs to be processed. Lets say that I need a program to do a bunch of things by business unit in program X, and I have 450 business units to run for. In the program I would be looping 1 by 1 through BU, maybe taking 1 minute per BU. To use parallel processing to increase throughput, I would add 'business unit' to a scroll at the run control, such that a 'run for all' would have 450 rows in a child table to psprcsruncntl. My preprogram would run, and knowing I want to create 5 parallel processes it could populate the first run control with the first 100 BUs in the scroll (insert directly to that child table), the second run control with the second 100 BUs, ...etc....leaving 50 BUs for the final run control. We have an outside program that would actually kick off those 5 parallel processes at the same time - but you could even have the SQR loop on the 'call system using' with an SQR command to run program X by each newly created run control.=20=20 The key is to ensure that the data your program touches in the tables is somehow distinct and not-overlapping. Otherwise a table in the first instance will lock out the other 4 instances until it commits, and you may as well have run it one at a time. If your section can't run parallel with other sections, then it wouldn't make sense to split it up. bob -----Original Message----- From: sqr-users-bounces+bstone=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+bstone=3Dfastenal.com@sqrug.org] On Behalf Of Knapp, Richard Sent: Thursday, April 12, 2007 4:27 PM To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Subject: RE: [sqr-users] Kicking of Sqr procedures.. SQR is a 'procedural' language. Usually this means one step at a time. As someone has already mentioned you can spawn both processes without waits. Richard Knapp EAS - Data Warehouse Group University of Missouri 615 Locust Street #200 Columbia, MO 65201 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant) Sent: Thursday, April 12, 2007 4:17 PM To: This list is for discussion about the SQR database reportinglanguage fromHyperion Solutions. Subject: RE: [sqr-users] Kicking of Sqr procedures.. There is no specific reason for this..Jus wanted to know if its possible or not. -----Original Message----- From: sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org [mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=3Dge.com@sqrug.org]On Behalf Of Arun Kumar Sent: Thursday, April 12, 2007 5:02 PM To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] Kicking of Sqr procedures.. No. Could you explain why you want to do this? Just curious on the design. ----- Original Message ---- From: "Vaidyanathan, Ramakrishnan (GE, Corporate, consultant)" To: sqr-users@sqrug.org Sent: Thursday, April 12, 2007 4:27:45 PM Subject: [sqr-users] Kicking of Sqr procedures.. Hi All, I have 2 procedures in a sqr. I want to kick both of the procedures parallely at the same time. For eg. I have A and B as 2 procedures. I want to kick off A and B procedures parallely.Once I kick off procedure A, it shouldn't wait for its completion. I should be able to kick off B simultaneously. Is it possible to achieve this? Thanks Ram _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users =20=20=20=20=20=20=20 ________________________________________________________________________ ____________ Looking for earth-friendly autos?=20 Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ _______________________________________________ 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-bounces@sqrug.org Fri Apr 13 15:18:15 2007 Date: Fri, 13 Apr 2007 15:15:47 -0400 From: "Anurag Tiwari" Subject: [sqr-users] Re: Pay003 spacing between lines. Hi , I want to Dynamically resize the line-height at certain stages of the Report. can anyone help me with this.Its very urgent. -- Thanks and Regards Anurag Tiwari _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Apr 13 15:21:44 2007 Date: Fri, 13 Apr 2007 15:17:05 -0400 From: "Anurag Tiwari" Subject: [sqr-users] Dynamically resize the line-height at certain stages of the Report. Hi , I want to Dynamically resize the line-height at certain stages of the Report. can anyone help me with this.Its very urgent. -- Thanks and Regards Anurag Tiwari _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Apr 13 21:10:00 2007 From: "Steve Cavill" Subject: RE: [sqr-users] Dynamically resize the line-height at certain stages ofthe Report. Date: Sat, 14 Apr 2007 11:04:52 +1000 You can't change the line-height, that is fixed for the program duration. But that only sets the relative positions of the "lines". You can change the height of individual print strings, either with the alter-printer command, which changes the character size for all characters, or the font= and point-size= options of individual print commands. Steve. -----Original Message----- From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org [mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] On Behalf Of Anurag Tiwari Sent: Saturday, 14 April 2007 5:17 AM To: sqr-users@sqrug.org Subject: [sqr-users] Dynamically resize the line-height at certain stages ofthe Report. Hi , I want to Dynamically resize the line-height at certain stages of the Report. can anyone help me with this.Its very urgent. -- Thanks and Regards Anurag Tiwari _______________________________________________ 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-bounces@sqrug.org Fri Apr 13 16:27:08 2007 From: Kaz.Narayanan@averydennison.com Date: Fri, 13 Apr 2007 13:23:47 -0700 Subject: [sqr-users] ODBC SQL dbftch error Hello, I am doing a select in an SQR where we use SQL server. My codes is as follows: BEGIN-SELECT A.PRODUCT A.ACCOUNT A.DEPTID SUM(A.POSTED_TOTAL_AMT) &MAIN_POSTED_TOTAL_AMT FROM PS_LEDGER A, PS_BUS_UNIT_TBL_GL A1 WHERE A.LEDGER IN ('ACTUALS','MGMT_ADJMT') [$AND-CLAUSE] AND A.FISCAL_YEAR = $IN_YEAR AND A.ACCOUNTING_PERIOD [$WHERE_PERIOD] AND A.PRODUCT <> ' ' AND A.ACCOUNT <> ' ' AND A.BUSINESS_UNIT = A1.BUSINESS_UNIT AND ((A.CURRENCY_CD = ' ' AND A.ACCOUNT LIKE '0%') OR (A.CURRENCY_CD = A1.BASE_CURRENCY)) GROUP BY A.PRODUCT,A.ACCOUNT,A.DEPTID ORDER BY A.PRODUCT,A.ACCOUNT,A.DEPTID END-SELECT I get the following error when I try to execute.... SQR 5528) ODBC SQL dbftch error 3926 in cursor 30: [Microsoft][SQL Native Client][SQL Server]The transaction active in this session has been committed or aborted by another session. (SQR 5528) ODBC SQL dbftch error 3971 in cursor 30: [Microsoft][SQL Native Client][SQL Server]Server failed to resume the transaction, desc: 7a00000004. Error on line 394: (SQR 3725)