From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Fri Apr 01 00:00:43 2005 Date: Fri, 1 Apr 2005 00:00:02 -0500 From: Nathan Stratton Treadway Subject: [sqr-users] Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Stratton Treadway, Ray Ontko & Co. (nathanst@ontko.com) $Revision: 1.21 $ $Date: 2004-03-04 02:47:15-05 $ This document gathers in one location information about the sqr-users mail list. It includes instructions for changing your subscription settings (including unsubscribing), as well are guidelines for using the list. ______________________________________________________________________ Table of Contents 1. Changes Since Previous Version 2. Introduction 2.1 sqr-users Mailing List 2.2 sqr-users Hints and Guidelines Document 2.3 Related Mailing Lists 3. Interacting with the list server 3.1 Subscribing to the list 3.2 Unsubscribing from the list 3.3 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 2003/11/01: o Update subscribe/unsubscribe instructions for new version of list server software. o New URL for "brio-l" mailing list. 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. BrioSQRReport : Brio SQR Report List for discussion of SQR and SQR Developer (formerly called Brio.Report and Report Builder). To join, follow the link above, or send any message to "BrioSQRReport- subscribe@yahoogroups.com". rm-users : Report- Mart Users List for discussion of Brio Portal (previously called ReportMart). To join, follow the link above, or send a message containing the word "subscribe" in the body to "rm-users- request@sqrug.org". brioportalusersgroup : Brio Portal Users Group list List for discussion of Brio Portal. To join, follow the link above, or send any message to "brioportalusersgroup- subscribe@yahoogroups.com". brio-l : OpenITx Brio List List for technical and functional discussion of all Brio products (but most messages are currently about Brio Intelligence). To join, follow the link above. briousers : Brio.Enter- prise Users List for discussion of the Brio.Enterprise (now called Intelligence) product. To join, follow the link above, or send any message to "briousers-subscribe@yahoogroups.com". briolist : Brio List List for discussion of Brio Intelligence. To join, follow the link above, or send any message to "briolist- subscribe@yahoogroups.com". 3. Interacting with the list server The sqr-users mailing list is hosted using the Mailman list server package. All of the Mailman functions described below are accessed from the sqr-users List Info page, "http://www.sqrug.org/mailman/listinfo/sqr-users". 3.1. Subscribing to the list To subscribe to the mailing list, enter your e-mail address in the box at the top of the "Subscribing to sqr-users" section on the sqr-users List Info page, then choose a password to assign to your subscription and enter it in both "Password" boxes. This password is used to allow you to securely make changes to your subscription over the web. This password will be e-mailed back to you at varous times, so you should not use the same password as you are using elsewhere. Once you have filled in the password, click the "Subscribe" button. You should soon receive an e-mail message from the list server requesting confirmation of your subscription request. Once you have replied to this message and your subscription has been processed, you will receive a "welcome" message giving you more information on the list server, and as well as a reminder of the subscription password you chose. 3.2. Unsubscribing from the list To unsubscribe from the list, go to the sqr-users List Info page, enter your e-mail address in the "sqr-users Subscribers" section 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+sqrusersarch=sqrug.org@sqrug.org Fri Apr 01 11:54:46 2005 Date: Fri, 1 Apr 2005 08:54:07 -0800 From: marc pechaitis Subject: [sqr-users] printing two portrait pages on a landscape page? Hello all - Thanks again for the help with the column problem I inquired about yesterday. The report I am writing is for a SQR being run in PeopleSoft. PeopleSoft delivers a sqc called "setup02.sqc" that formats the report to print in Landscape orientation (and "setup01.sqc" prints in Portrait). The users want the report to print two (portrait) pages to a landscape orientation. Is anyone aware of a different sqc that might do this automatically? If not, does anyone have any suggestions about a way to do this (counting lines? -ugh)? We were thinking that this could be an issue better handled with the printer driver, but the report will be printing directly from PeopleSoft so it would be extra tricky to figure out how to make that work. So I figured I'd throw it out there to see if anyone has figured out a good way to do this. Thanks! Marc _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Fri Apr 01 12:20:23 2005 From: "Paul Hoyte" Subject: RE: [sqr-users] printing two portrait pages on a landscape page? Date: Fri, 1 Apr 2005 12:19:46 -0500 Marc: One way would be to use the printer driver and tell it to print 2 pages per sheet in landscape mode (like PowerPoint), or you can use the column feature of SQR and create 2 logical pages in landscape mode and then just print using the column commands. Paul Hoyte (248) 752-6970 Cell (586) 421-9640 Fax pa_hoyte@si-proservices.com (Email) www.sharidionne.com (Web Site) -----Original Message----- From: sqr-users-bounces+pi_hoyte=si-proservices.com@sqrug.org [mailto:sqr-users-bounces+pi_hoyte=si-proservices.com@sqrug.org] On Behalf Of marc pechaitis Sent: Friday, April 01, 2005 11:54 AM To: sqr-users@sqrug.org Subject: [sqr-users] printing two portrait pages on a landscape page? Hello all - Thanks again for the help with the column problem I inquired about yesterday. The report I am writing is for a SQR being run in PeopleSoft. PeopleSoft delivers a sqc called "setup02.sqc" that formats the report to print in Landscape orientation (and "setup01.sqc" prints in Portrait). The users want the report to print two (portrait) pages to a landscape orientation. Is anyone aware of a different sqc that might do this automatically? If not, does anyone have any suggestions about a way to do this (counting lines? -ugh)? We were thinking that this could be an issue better handled with the printer driver, but the report will be printing directly from PeopleSoft so it would be extra tricky to figure out how to make that work. So I figured I'd throw it out there to see if anyone has figured out a good way to do this. Thanks! Marc _______________________________________________ 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+sqrusersarch=sqrug.org@sqrug.org Fri Apr 01 15:15:45 2005 Subject: Re: [sqr-users] printing two portrait pages on a landscape page? Date: Fri, 1 Apr 2005 18:07:05 -0000 From: Hi Marc, some printers (Xerox, IBM transport underneath the physical versus logical page) will allow you to select that as a printer setup option in the driver (multiple logical pages on one physical page, seascape, landscape, shrink, etc); however, I have only done it using a postscript printer (not HP since it needs quarter inch of transport on left and right side). Some of the problems include whether you are using fixed versus proportional font and whether you are using html and/or a line printer and of course images. The SQR user group library has some code that might provide some assistance. How the includes there mesh with Oracle/PeopleSoft sqc's I can't comment on without experimentation. John Willson jwillson@dssltd.com marc pechaitis said: > Hello all - > Thanks again for the help with the column problem I inquired about yesterday. > > The report I am writing is for a SQR being run in PeopleSoft. > PeopleSoft delivers a sqc called "setup02.sqc" that formats the report > to print in Landscape orientation (and "setup01.sqc" prints in > Portrait). > > The users want the report to print two (portrait) pages to a landscape > orientation. Is anyone aware of a different sqc that might do this > automatically? If not, does anyone have any suggestions about a way > to do this (counting lines? -ugh)? > > We were thinking that this could be an issue better handled with the > printer driver, but the report will be printing directly from > PeopleSoft so it would be extra tricky to figure out how to make that > work. So I figured I'd throw it out there to see if anyone has > figured out a good way to do this. > > > Thanks! > Marc > > _______________________________________________ > 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+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 10:40:26 2005 From: Olga.Gal@bbh.com Date: Mon, 4 Apr 2005 11:38:53 -0400 Subject: [sqr-users] Dynamic SQL vs. Fixed SQL execution We're using SQR ver 8.1 with Oracle 9i in the datawarehousing environment, which produce reports with huge amount of data and run VERY slow. Our SQR programs construct the Where clause and Order By clause dynamically, based on user's selection, and execute the same Select statement many times within the same job. This makes me think that the slow performance is possibly caused in part by repeating parsing of these dynamically constructed Select statements. Before taking a dramatic decision of changing the reports to have only fixed Select statements with bind variables ONLY in the Where clause, I would like to get assured that this approach will in fact eliminate unnecessary parsing (compilation) of SQL statements. My question is: Is the SQR compiling each SQL statement before executing it, or would it do it only for a newly executed SQL. In other words, if my program will repeatedly call a routine that executes a fixed SQL statement, will this statement get parsed every time it's being executed, or only the first time. Also, are there any other ways to speed up that time of processing? Any advice is greatly appreciated. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 10:50:51 2005 Subject: Re: [sqr-users] Dynamic SQL vs. Fixed SQL execution From: Wjatscheslaw KRAVTCHENKO Date: Mon, 4 Apr 2005 17:50:35 +0200 Dear Olga, is the SQR forming the SQL slow or is the response from the database slow? How slow is it? Wha amount of data are we dealing with? We had a similar scenario on Sybase 12.5 transactional data. Do you have any samples of the SQL stmts ? How is the Warehouse structured? Anticipating your reply. ______________________________________ Wjatscheslaw Kravtchenko (Mr.) ProFi System Manager Assistant ProFi Technical Team Information Technology Service Division for Management United Nations Office on Drugs and Crime Tel: (+43-1) 26060-4153 Websites: www.unov.org, www.unodc.org ______________________________________ Impossible is not a fact, only an opinion Olga.Gal@bbh.com Sent by: sqr-users-bounces To +wjatscheslaw.kra sqr-users@sqrug.org vtchenko=unvienna cc .org@sqrug.org Subject [sqr-users] Dynamic SQL vs. Fixed Monday, 4 April SQL execution 2005 17:38 Please respond to "This list is for discussion about the SQR database reporting language from Hyperion Solutions." We're using SQR ver 8.1 with Oracle 9i in the datawarehousing environment, which produce reports with huge amount of data and run VERY slow. Our SQR programs construct the Where clause and Order By clause dynamically, based on user's selection, and execute the same Select statement many times within the same job. This makes me think that the slow performance is possibly caused in part by repeating parsing of these dynamically constructed Select statements. Before taking a dramatic decision of changing the reports to have only fixed Select statements with bind variables ONLY in the Where clause, I would like to get assured that this approach will in fact eliminate unnecessary parsing (compilation) of SQL statements. My question is: Is the SQR compiling each SQL statement before executing it, or would it do it only for a newly executed SQL. In other words, if my program will repeatedly call a routine that executes a fixed SQL statement, will this statement get parsed every time it's being executed, or only the first time. Also, are there any other ways to speed up that time of processing? Any advice is greatly appreciated. _______________________________________________ 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+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 10:59:30 2005 From: Bob Stone Subject: RE: [sqr-users] Dynamic SQL vs. Fixed SQL execution Date: Mon, 4 Apr 2005 10:58:24 -0500 if you're creating the where clause dynamically, then yes, it will re-parse the select statement many times, adding a lot of unnecessary overhead. Also, when not using bind variables the SQL will sometime not use the index that was made for that specfic SQL. I forget the exact reasons for this, but we recently went through a similar exercise with one of our DBAs. We had a quick process/report turn slow all of a sudden, because we changed something and it stopped using a bind variable which made it stop using the index. (also of concern was that reparsing of the same repetitive SQL over and over). If a procedure executes a fixed SQL, I don't think it reparses it everytime. (I'm not 100% on this, but quite sure). I think rewriting it might solve your time issue. (sorry if that's not the answer you wanted to hear). We're using an older verison of SQR and we're on Oracle 8i, so I couldn't say if the problem we had is something fixed in later versions of either...but because of the type of problem, I would assume it's still the same. Another way to speed up the SQR running time is tuning the SQLs...making sure they're using an index every time. The actual processing of non-SQL parts of SQRs is ususally negligible compared to IO/DB time. You might discuss this with your DBA, run a trace to find which statements in the SQR are the slowest. Perhaps there's one statement taking 60% of the time, and could be tuned down to a matter of seconds? We've run into that with our reports/processes. I dont know where you are on SQR tuning these reports, so perhaps switching to non-dynamic SQL is the biggest time saver. But to answer your original question, yes, I think if you could rewrite them non-dynamically that would save you parsing time (sometimes huge overhead, depending on number of times SQL is run). -----Original Message----- From: sqr-users-bounces+bstone=fastenal.com@sqrug.org [mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of Olga.Gal@bbh.com Sent: Monday, April 04, 2005 10:39 AM To: sqr-users@sqrug.org Subject: [sqr-users] Dynamic SQL vs. Fixed SQL execution We're using SQR ver 8.1 with Oracle 9i in the datawarehousing environment, which produce reports with huge amount of data and run VERY slow. Our SQR programs construct the Where clause and Order By clause dynamically, based on user's selection, and execute the same Select statement many times within the same job. This makes me think that the slow performance is possibly caused in part by repeating parsing of these dynamically constructed Select statements. Before taking a dramatic decision of changing the reports to have only fixed Select statements with bind variables ONLY in the Where clause, I would like to get assured that this approach will in fact eliminate unnecessary parsing (compilation) of SQL statements. My question is: Is the SQR compiling each SQL statement before executing it, or would it do it only for a newly executed SQL. In other words, if my program will repeatedly call a routine that executes a fixed SQL statement, will this statement get parsed every time it's being executed, or only the first time. Also, are there any other ways to speed up that time of processing? Any advice is greatly appreciated. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 11:24:40 2005 Subject: Re: [sqr-users] Dynamic SQL vs. Fixed SQL execution From: Olga.Gal@bbh.com Date: Mon, 4 Apr 2005 12:22:52 -0400 Dear Wjatscheslaw, To produce the report we access huge wide tables (millions of rows) that are joined by indexed columns. The queries are carefully tuned and executed fairly quickly outside the SQR. The processing starts with an outer (driving) query, which supposed to be the slowest one. It would return back thousands of rows, and then 90 percent of the selected rows are processed further: for each processed rows the SQR then selects additional information from 6 other queries that are dynamically constructed (dynamic SQL). These queries access the data by Primary Key, and are very fast when executed outside SQR. It takes over an hour to generate a report of about 650 such entities. By monitoring the progression of the report it looks like the driving query (the slowest one) is returning the list of entities fairly quickly, but it still takes too long to finish the report, which brought me to think that the problem is not really in the data access. Wjatscheslaw KRAVTCHENKO Sent by: sqr-users-bounces+olga.gal=bbh.com@sqrug.org To "This list is for discussion about the SQR database reporting language from Hyperion Solutions." 04/04/2005 11:50 AM cc Subject Please respond to Re: [sqr-users] Dynamic SQL vs. Fixed SQL execution "This list is for discussion about the SQR database reporting language from Hyperion Solutions." Dear Olga, is the SQR forming the SQL slow or is the response from the database slow? How slow is it? Wha amount of data are we dealing with? We had a similar scenario on Sybase 12.5 transactional data. Do you have any samples of the SQL stmts ? How is the Warehouse structured? Anticipating your reply. ______________________________________ Wjatscheslaw Kravtchenko (Mr.) ProFi System Manager Assistant ProFi Technical Team Information Technology Service Division for Management United Nations Office on Drugs and Crime Tel: (+43-1) 26060-4153 Websites: www.unov.org, www.unodc.org ______________________________________ Impossible is not a fact, only an opinion Olga.Gal@bbh.com Sent by: sqr-users-bounces To +wjatscheslaw.kra sqr-users@sqrug.org vtchenko=unvienna cc .org@sqrug.org Subject [sqr-users] Dynamic SQL vs. Fixed Monday, 4 April SQL execution 2005 17:38 Please respond to "This list is for discussion about the SQR database reporting language from Hyperion Solutions." We're using SQR ver 8.1 with Oracle 9i in the datawarehousing environment, which produce reports with huge amount of data and run VERY slow. Our SQR programs construct the Where clause and Order By clause dynamically, based on user's selection, and execute the same Select statement many times within the same job. This makes me think that the slow performance is possibly caused in part by repeating parsing of these dynamically constructed Select statements. Before taking a dramatic decision of changing the reports to have only fixed Select statements with bind variables ONLY in the Where clause, I would like to get assured that this approach will in fact eliminate unnecessary parsing (compilation) of SQL statements. My question is: Is the SQR compiling each SQL statement before executing it, or would it do it only for a newly executed SQL. In other words, if my program will repeatedly call a routine that executes a fixed SQL statement, will this statement get parsed every time it's being executed, or only the first time. Also, are there any other ways to speed up that time of processing? Any advice is greatly appreciated. _______________________________________________ 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+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 11:44:47 2005 Date: Mon, 04 Apr 2005 12:43:20 -0400 From: "George Jansen" Subject: Re: [sqr-users] Dynamic SQL vs. Fixed SQL execution Before you tune ANYTHING you should know where your time is going. As a fir= st step you might try something as simple as adding a procedure that looks = at v$mystat and seeing what the waits are. >>> Olga.Gal@bbh.com 04/04/05 11:38 AM >>> We're using SQR ver 8.1 with Oracle 9i in the datawarehousing environment, = which produce reports with huge amount of data and run VERY slow. Our SQR programs construct the Where clause and Order By clause dynamically= , based on user's selection, and execute the same Select statement many tim= es within the same job. This makes me think that the slow performance is po= ssibly caused in part by repeating parsing of these dynamically constructed Select statements. Before taking a dramatic decision of changing the reports to have only fixe= d Select statements with bind variables ONLY in the Where clause, I would l= ike to get assured that this approach will in fact eliminate unnecessary pa= rsing (compilation) of SQL statements. My question is: Is the SQR compiling each SQL statement before executing it, or would it d= o it only for a newly executed SQL. In other words, if my program will repe= atedly call a routine that executes a fixed SQL statement, will this statem= ent get parsed every time it's being executed, or only the first time. Also, are there any other ways to speed up that time of processing? Any advice is greatly appreciated. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org=20 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+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 12:10:21 2005 From: "the dragon" Subject: RE: [sqr-users] Dynamic SQL vs. Fixed SQL execution Date: Mon, 04 Apr 2005 12:01:22 -0500 You can see how many times the sql compiles, as well as how many times it executes, and the number of rows returned by using the -s parameter when you run the program. 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) ----Original Message Follows---- We're using SQR ver 8.1 with Oracle 9i in the datawarehousing environment, which produce reports with huge amount of data and run VERY slow. Our SQR programs construct the Where clause and Order By clause dynamically, based on user's selection, and execute the same Select statement many times within the same job. This makes me think that the slow performance is possibly caused in part by repeating parsing of these dynamically constructed Select statements. Before taking a dramatic decision of changing the reports to have only fixed Select statements with bind variables ONLY in the Where clause, I would like to get assured that this approach will in fact eliminate unnecessary parsing (compilation) of SQL statements. My question is: Is the SQR compiling each SQL statement before executing it, or would it do it only for a newly executed SQL. In other words, if my program will repeatedly call a routine that executes a fixed SQL statement, will this statement get parsed every time it's being executed, or only the first time. Also, are there any other ways to speed up that time of processing? Any advice is greatly appreciated. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 12:58:24 2005 From: yamuna_pandiyan@freddiemac.com Date: Mon, 4 Apr 2005 13:57:40 -0400 Subject: [sqr-users] Call SYSTEM command in SQR The following command works fine or the directory from where the SQR program runs - '/shared/main/prog/sqr'. This command does not look for the file in a different directory, i.e the data file resides in the data directory - ' /shared/main/files/data/'. This is true if the command is used with or without the predefined prefix variable. Example, I copied the data file from the above data directory to the SQR directory and ran the program.The latest SAMPLE.DAT file name was written into the filename defined in the '$file_list' variable. I did the cd command, $SqrDirectory = '/shared/main/prog/sqr' $DataDirectory = '/shared/main/files/data/' $CMD = 'cd' || $DataDirectory $cmd = 'ls -lt SAMPLE.DAT >' || $DataDirectory || $file_list call system using $syscmd #unix_status I get the error - SAMPLE.DAT: No such file or directory But the above command works fine if I copy the SAMPLE.DAT file into the SQR directory and use the following code, $cmd = 'ls -lt SAMPLE.DAT >' || ' ' || $file_list call system using $syscmd #unix_status Could you help me how to use the 'cd' command with 'ls' command or how to make the program to look for the data file in the data directory...? Thanks, Yamuna "Dattatraya Shetty" "This list is for discussion about the SQR database reporting language Sent by : from Hyperion Solutions." sqr-users-bounces+yamuna_pan diyan=freddiemac.com@sqrug.o cc rg Subject Re: [sqr-users] Call SYSTEM 03/31/2005 01:25 command in SQR PM Please respond to "This list is for discussion about the SQR database reporting language from Hyperion Solutions." This fix is simple change the command to below let $syscmd = 'ls -lts SAMPLE.DAT >' || $file_list call system using $syscmd #unix_status if #unix_status != 0 stop quiet end-if No need to Add the fileprefix to ls command. Its a Unix command and if you add fileprefix to it the OS will try and execute the ls command from the path in fileprefix which is wrong because the ls command is not stored there. its some where in /usr/bin: if you need to give the path then log into unix and issue the following command $> whence ls see the path displayed and use that. HTH Datta. --- yamuna_pandiyan@freddiemac.com wrote: > > Hi Datta, > > The sqr program resides in the ' /shared/main/prog/sqr' directory. > The > data file is created in the ' /shared/main/files/data/' directory. > The There > is nothing wrong in the {FILEPREFIX} variable, since it woks fine > with all > other programs. > > But I am not able to debug, since I have limited knowledge on the > Call > System commands. When the below program is executed, the > Latestfile.dat is > created in the directory ' /shared/main/files/data/'. > > The error that is generated when executing the program is 'sh: > /shared/main/files/data/ls : not found' if I use the below command. > if I remove the {FILEPREFIX} from the command line, the following > error is > generated - '/shared/main/files/data/ : cannot execute' > > Thanks, > Yamuna > 918-5127 > > > > > "Dattatraya > > Shetty" > > To > oo.com> "This list is for discussion > about > the SQR database reporting > language > Sent by : from Hyperion Solutions." > > sqr-users-bounces+yamuna_pan > > diyan=freddiemac.com@sqrug.o > cc > rg > > > Subject > Re: [sqr-users] Call SYSTEM > > 03/30/2005 04:14 command in SQR > > PM > > > > > > Please respond to > > "This list is > > for discussion > > about the SQR > > database > > reporting > > language from > > Hyperion > > Solutions." > > > .org> > > > > > > > > > > Hi Yamuna, > The Second part of the Problem is that FILEPREFIX may be an > environment > variable not defined? > > In the fist part the $ is missing for an environment variable. > > It would help if you let us know what is the excat command you are > typing > in at the command > prompt. > > e.g > > $> ls -tls FMUBSOPT.DAT* > ${FILEPREFIX}Latestfile.dat > is this what you are typing ? > > The fact that you are getting a file called Latestfile.dat tells me > that > the FILEPREFIX is not defined or you are not keying it correctly what > is it > ? > > HTH > Datta. > > > > yamuna_pandiyan@freddiemac.com wrote: > I am using a program which needs to compare the current data with the > previous data file. the data files are created with date and time > stamp. > This program runs once a week, and has to compare the previous weeks > data > file to monitor the data changes. > > I am using the following command on the Unix box to sort the most > recent > file > > let $file_list = '{FILEPREFIX}Latestfile.dat' > > let $syscmd = '{FILEPREFIX}' || 'ls -lts SAMPLE.DAT >' || $file_list > call system using $syscmd #unix_status > if #unix_status != 0 > stop quiet > end-if > > The Latestfile.dat file is created in the data directory mentioned > under > {FILEPREFIX}. But the file is empty. I would like to send the result > of the > command > ls -lts SAMPLE.DAT* to the above file. > when I execute the above program in the unix server , I ge the > following > error - {FILEPREFIX} : cannot execute, but a blank file in the name > of > Latestfile.dat is created. > > any help in greatly appreciated. > > Thanks, > Yamuna > 918-5127 > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > > --------------------------------- > Do you Yahoo!? > Yahoo! Sports - Sign up for Fantasy Baseball. > > _______________________________________________ > 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 > __________________________________ Do you Yahoo!? Yahoo! Personals - Better first dates. More second dates. http://personals.yahoo.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 13:07:28 2005 Date: Mon, 04 Apr 2005 14:06:19 -0400 From: "Prasanna Menta" Subject: Re: [sqr-users] Call SYSTEM command in SQR You have to know the structure of your unix file directory - Also you need a space between cd and $datadirectory $CMD = 'cd' || ' '|| $DataDirectory yamuna_pandiyan@freddiemac.com wrote: > > The following command works fine or the directory from where the SQR > program runs - '/shared/main/prog/sqr'. This command does not look for the > file in a different directory, i.e the data file resides in the data > directory - ' /shared/main/files/data/'. This is true if the command is > used with or without the predefined prefix variable. > > Example, I copied the data file from the above data directory to the SQR > directory and ran the program.The latest SAMPLE.DAT file name was written > into the filename defined in the '$file_list' variable. > > I did the cd command, $SqrDirectory = '/shared/main/prog/sqr' > $DataDirectory = '/shared/main/files/data/' > > $CMD = 'cd' || $DataDirectory > $cmd = 'ls -lt SAMPLE.DAT >' || $DataDirectory || > $file_list > call system using $syscmd #unix_status > > I get the error - SAMPLE.DAT: No such file or directory > > But the above command works fine if I copy the SAMPLE.DAT file into the SQR > directory and use the following code, > > $cmd = 'ls -lt SAMPLE.DAT >' || ' ' || $file_list > call system using $syscmd #unix_status > > Could you help me how to use the 'cd' command with 'ls' command or how to > make the program to look for the data file in the data directory...? > > Thanks, > Yamuna > > > "Dattatraya > Shetty" > oo.com> "This list is for discussion about > the SQR database reporting language > Sent by : from Hyperion Solutions." > sqr-users-bounces+yamuna_pan > diyan=freddiemac.com@sqrug.o cc > rg > Subject > Re: [sqr-users] Call SYSTEM > 03/31/2005 01:25 command in SQR > PM > > > Please respond to > "This list is > for discussion > about the SQR > database > reporting > language from > Hyperion > Solutions." > .org> > > > > This fix is simple change the command to below > let $syscmd = 'ls -lts SAMPLE.DAT >' || $file_list > call system using $syscmd #unix_status > if #unix_status != 0 > stop quiet > end-if > > No need to Add the fileprefix to ls command. Its a Unix command and if > you add fileprefix to it the OS will try and execute the ls command > from the path in fileprefix which is wrong because the ls command is > not stored there. its some where in /usr/bin: > if you need to give the path then log into unix and issue the following > command > > $> whence ls > see the path displayed and use that. > > HTH > Datta. > > --- yamuna_pandiyan@freddiemac.com wrote: > > > > > Hi Datta, > > > > The sqr program resides in the ' /shared/main/prog/sqr' directory. > > The > > data file is created in the ' /shared/main/files/data/' directory. > > The > There > > is nothing wrong in the {FILEPREFIX} variable, since it woks fine > > with all > > other programs. > > > > But I am not able to debug, since I have limited knowledge on the > > Call > > System commands. When the below program is executed, the > > Latestfile.dat is > > created in the directory ' /shared/main/files/data/'. > > > > The error that is generated when executing the program is 'sh: > > /shared/main/files/data/ls : not found' if I use the below command. > > if I remove the {FILEPREFIX} from the command line, the following > > error is > > generated - '/shared/main/files/data/ : cannot execute' > > > > Thanks, > > Yamuna > > 918-5127 > > > > > > > > > > "Dattatraya > > > > Shetty" > > > > > To > > oo.com> "This list is for discussion > > about > > the SQR database reporting > > language > > Sent by : from Hyperion Solutions." > > > > sqr-users-bounces+yamuna_pan > > > > diyan=freddiemac.com@sqrug.o > > cc > > rg > > > > > > Subject > > Re: [sqr-users] Call SYSTEM > > > > 03/30/2005 04:14 command in SQR > > > > PM > > > > > > > > > > > > Please respond to > > > > "This list is > > > > for discussion > > > > about the SQR > > > > database > > > > reporting > > > > language from > > > > Hyperion > > > > Solutions." > > > > > > > .org> > > > > > > > > > > > > > > > > > > > > Hi Yamuna, > > The Second part of the Problem is that FILEPREFIX may be an > > environment > > variable not defined? > > > > In the fist part the $ is missing for an environment variable. > > > > It would help if you let us know what is the excat command you are > > typing > > in at the command > > prompt. > > > > e.g > > > > $> ls -tls FMUBSOPT.DAT* > ${FILEPREFIX}Latestfile.dat > > is this what you are typing ? > > > > The fact that you are getting a file called Latestfile.dat tells me > > that > > the FILEPREFIX is not defined or you are not keying it correctly what > > is it > > ? > > > > HTH > > Datta. > > > > > > > > yamuna_pandiyan@freddiemac.com wrote: > > I am using a program which needs to compare the current data with the > > previous data file. the data files are created with date and time > > stamp. > > This program runs once a week, and has to compare the previous weeks > > data > > file to monitor the data changes. > > > > I am using the following command on the Unix box to sort the most > > recent > > file > > > > let $file_list = '{FILEPREFIX}Latestfile.dat' > > > > let $syscmd = '{FILEPREFIX}' || 'ls -lts SAMPLE.DAT >' || $file_list > > call system using $syscmd #unix_status > > if #unix_status != 0 > > stop quiet > > end-if > > > > The Latestfile.dat file is created in the data directory mentioned > > under > > {FILEPREFIX}. But the file is empty. I would like to send the result > > of the > > command > > ls -lts SAMPLE.DAT* to the above file. > > when I execute the above program in the unix server , I ge the > > following > > error - {FILEPREFIX} : cannot execute, but a blank file in the name > > of > > Latestfile.dat is created. > > > > any help in greatly appreciated. > > > > Thanks, > > Yamuna > > 918-5127 > > > > > > > > _______________________________________________ > > sqr-users mailing list > > sqr-users@sqrug.org > > http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > > --------------------------------- > > Do you Yahoo!? > > Yahoo! Sports - Sign up for Fantasy Baseball. > > > > _______________________________________________ > > 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 > > > > __________________________________ > Do you Yahoo!? > Yahoo! Personals - Better first dates. More second dates. > http://personals.yahoo.com > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 13:13:08 2005 Subject: Re: [sqr-users] Call SYSTEM command in SQR From: yamuna_pandiyan@freddiemac.com Date: Mon, 4 Apr 2005 14:12:32 -0400 Yes there is a space in the cmd line , that was a typo in the below email, it should be - $CMD = 'cd' || ' ' || $DataDirectory Thanks, Yamuna 918-5127 "Prasanna Menta" To Sent by : "This list is for discussion about sqr-users-bounces+yamuna_pan the SQR database reporting language diyan=freddiemac.com@sqrug.o from Hyperion Solutions." rg cc 04/04/2005 02:06 Subject PM Re: [sqr-users] Call SYSTEM command in SQR Please respond to "This list is for discussion about the SQR database reporting language from Hyperion Solutions." You have to know the structure of your unix file directory - Also you need a space between cd and $datadirectory $CMD = 'cd' || ' '|| $DataDirectory yamuna_pandiyan@freddiemac.com wrote: > > The following command works fine or the directory from where the SQR > program runs - '/shared/main/prog/sqr'. This command does not look for the > file in a different directory, i.e the data file resides in the data > directory - ' /shared/main/files/data/'. This is true if the command is > used with or without the predefined prefix variable. > > Example, I copied the data file from the above data directory to the SQR > directory and ran the program.The latest SAMPLE.DAT file name was written > into the filename defined in the '$file_list' variable. > > I did the cd command, $SqrDirectory = '/shared/main/prog/sqr' > $DataDirectory = '/shared/main/files/data/' > > $CMD = 'cd' ||$DataDirectory > $cmd = 'ls -lt SAMPLE.DAT >' || $DataDirectory || > $file_list > call system using $syscmd #unix_status > > I get the error - SAMPLE.DAT: No such file or directory > > But the above command works fine if I copy the SAMPLE.DAT file into the SQR > directory and use the following code, > > $cmd = 'ls -lt SAMPLE.DAT >' || ' ' || $file_list > call system using $syscmd #unix_status > > Could you help me how to use the 'cd' command with 'ls' command or how to > make the program to look for the data file in the data directory...? > > Thanks, > Yamuna > > > "Dattatraya > Shetty" > oo.com> "This list is for discussion about > the SQR database reporting language > Sent by : from Hyperion Solutions." > sqr-users-bounces+yamuna_pan > diyan=freddiemac.com@sqrug.o cc > rg > Subject > Re: [sqr-users] Call SYSTEM > 03/31/2005 01:25 command in SQR > PM > > > Please respond to > "This list is > for discussion > about the SQR > database > reporting > language from > Hyperion > Solutions." > .org> > > > > This fix is simple change the command to below > let $syscmd = 'ls -lts SAMPLE.DAT >' || $file_list > call system using $syscmd #unix_status > if #unix_status != 0 > stop quiet > end-if > > No need to Add the fileprefix to ls command. Its a Unix command and if > you add fileprefix to it the OS will try and execute the ls command > from the path in fileprefix which is wrong because the ls command is > not stored there. its some where in /usr/bin: > if you need to give the path then log into unix and issue the following > command > > $> whence ls > see the path displayed and use that. > > HTH > Datta. > > --- yamuna_pandiyan@freddiemac.com wrote: > > > > > Hi Datta, > > > > The sqr program resides in the ' /shared/main/prog/sqr' directory. > > The > > data file is created in the ' /shared/main/files/data/' directory. > > The > There > > is nothing wrong in the {FILEPREFIX} variable, since it woks fine > > with all > > other programs. > > > > But I am not able to debug, since I have limited knowledge on the > > Call > > System commands. When the below program is executed, the > > Latestfile.dat is > > created in the directory ' /shared/main/files/data/'. > > > > The error that is generated when executing the program is 'sh: > > /shared/main/files/data/ls : not found' if I use the below command. > > if I remove the {FILEPREFIX} from the command line, the following > > error is > > generated - '/shared/main/files/data/ : cannot execute' > > > > Thanks, > > Yamuna > > 918-5127 > > > > > > > > > > "Dattatraya > > > > Shetty" > > > > > To > > oo.com> "This list is for discussion > > about > > the SQR database reporting > > language > > Sent by : from Hyperion Solutions." > > > > sqr-users-bounces+yamuna_pan > > > > diyan=freddiemac.com@sqrug.o > > cc > > rg > > > > > > Subject > > Re: [sqr-users] Call SYSTEM > > > > 03/30/2005 04:14 command in SQR > > > > PM > > > > > > > > > > > > Please respond to > > > > "This list is > > > > for discussion > > > > about the SQR > > > > database > > > > reporting > > > > language from > > > > Hyperion > > > > Solutions." > > > > > > > .org> > > > > > > > > > > > > > > > > > > > > Hi Yamuna, > > The Second part of the Problem is that FILEPREFIX may be an > > environment > > variable not defined? > > > > In the fist part the $ is missing for an environment variable. > > > > It would help if you let us know what is the excat command you are > > typing > > in at the command > > prompt. > > > > e.g > > > > $> ls -tls FMUBSOPT.DAT* > ${FILEPREFIX}Latestfile.dat > > is this what you are typing ? > > > > The fact that you are getting a file called Latestfile.dat tells me > > that > > the FILEPREFIX is not defined or you are not keying it correctly what > > is it > > ? > > > > HTH > > Datta. > > > > > > > > yamuna_pandiyan@freddiemac.com wrote: > > I am using a program which needs to compare the current data with the > > previous data file. the data files are created with date and time > > stamp. > > This program runs once a week, and has to compare the previous weeks > > data > > file to monitor the data changes. > > > > I am using the following command on the Unix box to sort the most > > recent > > file > > > > let $file_list = '{FILEPREFIX}Latestfile.dat' > > > > let $syscmd = '{FILEPREFIX}' || 'ls -lts SAMPLE.DAT >' || $file_list > > call system using $syscmd #unix_status > > if #unix_status != 0 > > stop quiet > > end-if > > > > The Latestfile.dat file is created in the data directory mentioned > > under > > {FILEPREFIX}. But the file is empty. I would like to send the result > > of the > > command > > ls -lts SAMPLE.DAT* to the above file. > > when I execute the above program in the unix server , I ge the > > following > > error - {FILEPREFIX} : cannot execute, but a blank file in the name > > of > > Latestfile.dat is created. > > > > any help in greatly appreciated. > > > > Thanks, > > Yamuna > > 918-5127 > > > > > > > > _______________________________________________ > > sqr-users mailing list > > sqr-users@sqrug.org > > http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > > --------------------------------- > > Do you Yahoo!? > > Yahoo! Sports - Sign up for Fantasy Baseball. > > > > _______________________________________________ > > 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 > > > > __________________________________ > Do you Yahoo!? > Yahoo! Personals - Better first dates. More second dates. > http://personals.yahoo.com > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 13:56:05 2005 Subject: RE: [sqr-users] Call SYSTEM command in SQR Date: Mon, 4 Apr 2005 14:54:51 -0400 From: "Beller, Jay" Do you have WRITE access to $DataDirectory? Try a command like "touch anyfile" in $DataDirectory and see if you get per= mission error. -----Original Message----- From: sqr-users-bounces+jbeller=3Dlbisoftware.com@sqrug.org [mailto:sqr-users-bounces+jbeller=3Dlbisoftware.com@sqrug.org]On Behalf Of yamuna_pandiyan@freddiemac.com Sent: Monday, April 04, 2005 2:13 PM To: sqr-users@sqrug.org Subject: Re: [sqr-users] Call SYSTEM command in SQR Yes there is a space in the cmd line , that was a typo in the below email, it should be - $CMD =3D 'cd' || ' ' || $DataDirectory Thanks, Yamuna 918-5127 =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 "Prasanna Menta"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20 =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20 To= =20 Sent by : "This list is for discussion about= =20=20 sqr-users-bounces+yamuna_pan the SQR database reporting language= =20 diyan=3Dfreddiemac.com@sqrug.o from Hyperion Solutions."=20=20=20= =20=20=20=20=20=20=20=20 rg =20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 cc= =20 =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 04/04/2005 02:06 Subject= =20 PM Re: [sqr-users] Call SYSTEM=20=20=20= =20=20=20=20=20=20 command in SQR=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 Please respond to=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20 "This list is=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20 for discussion=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20 about the SQR=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20 database=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 reporting=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 language from=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20 Hyperion=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 Solutions."=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20 =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20 =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 You have to know the structure of your unix file directory - Also you need a space between cd and $datadirectory $CMD =3D 'cd' || ' '|| $DataDirectory yamuna_pandiyan@freddiemac.com wrote: > > The following command works fine or the directory from where the SQR > program runs - '/shared/main/prog/sqr'. This command does not look for the > file in a different directory, i.e the data file resides in the data > directory - ' /shared/main/files/data/'. This is true if the command is > used with or without the predefined prefix variable. > > Example, I copied the data file from the above data directory to the SQR > directory and ran the program.The latest SAMPLE.DAT file name was written > into the filename defined in the '$file_list' variable. > > I did the cd command, $SqrDirectory =3D '/shared/main/prog/sqr' > $DataDirectory =3D '/shared/main/files/data/' > > $CMD =3D 'cd' ||$DataDirectory > $cmd =3D 'ls -lt SAMPLE.DAT >' || $DataDirectory || > $file_list > call system using $syscmd #unix_status > > I get the error - SAMPLE.DAT: No such file or directory > > But the above command works fine if I copy the SAMPLE.DAT file into the SQR > directory and use the following code, > > $cmd =3D 'ls -lt SAMPLE.DAT >' || ' ' || $file_list > call system using $syscmd #unix_status > > Could you help me how to use the 'cd' command with 'ls' command or how to > make the program to look for the data file in the data directory...? > > Thanks, > Yamuna > > > "Dattatraya > Shetty" > oo.com> "This list is for discussion about > the SQR database reporting language > Sent by : from Hyperion Solutions." > sqr-users-bounces+yamuna_pan > diyan=3Dfreddiemac.com@sqrug.o cc > rg > Subject > Re: [sqr-users] Call SYSTEM > 03/31/2005 01:25 command in SQR > PM > > > Please respond to > "This list is > for discussion > about the SQR > database > reporting > language from > Hyperion > Solutions." > .org> > > > > This fix is simple change the command to below > let $syscmd =3D 'ls -lts SAMPLE.DAT >' || $file_list > call system using $syscmd #unix_status > if #unix_status !=3D 0 > stop quiet > end-if > > No need to Add the fileprefix to ls command. Its a Unix command and if > you add fileprefix to it the OS will try and execute the ls command > from the path in fileprefix which is wrong because the ls command is > not stored there. its some where in /usr/bin: > if you need to give the path then log into unix and issue the following > command > > $> whence ls > see the path displayed and use that. > > HTH > Datta. > > --- yamuna_pandiyan@freddiemac.com wrote: > > > > > Hi Datta, > > > > The sqr program resides in the ' /shared/main/prog/sqr' directory. > > The > > data file is created in the ' /shared/main/files/data/' directory. > > The > There > > is nothing wrong in the {FILEPREFIX} variable, since it woks fine > > with all > > other programs. > > > > But I am not able to debug, since I have limited knowledge on the > > Call > > System commands. When the below program is executed, the > > Latestfile.dat is > > created in the directory ' /shared/main/files/data/'. > > > > The error that is generated when executing the program is 'sh: > > /shared/main/files/data/ls : not found' if I use the below command. > > if I remove the {FILEPREFIX} from the command line, the following > > error is > > generated - '/shared/main/files/data/ : cannot execute' > > > > Thanks, > > Yamuna > > 918-5127 > > > > > > > > > > "Dattatraya > > > > Shetty" > > > > > To > > oo.com> "This list is for discussion > > about > > the SQR database reporting > > language > > Sent by : from Hyperion Solutions." > > > > sqr-users-bounces+yamuna_pan > > > > diyan=3Dfreddiemac.com@sqrug.o > > cc > > rg > > > > > > Subject > > Re: [sqr-users] Call SYSTEM > > > > 03/30/2005 04:14 command in SQR > > > > PM > > > > > > > > > > > > Please respond to > > > > "This list is > > > > for discussion > > > > about the SQR > > > > database > > > > reporting > > > > language from > > > > Hyperion > > > > Solutions." > > > > > > > .org> > > > > > > > > > > > > > > > > > > > > Hi Yamuna, > > The Second part of the Problem is that FILEPREFIX may be an > > environment > > variable not defined? > > > > In the fist part the $ is missing for an environment variable. > > > > It would help if you let us know what is the excat command you are > > typing > > in at the command > > prompt. > > > > e.g > > > > $> ls -tls FMUBSOPT.DAT* > ${FILEPREFIX}Latestfile.dat > > is this what you are typing ? > > > > The fact that you are getting a file called Latestfile.dat tells me > > that > > the FILEPREFIX is not defined or you are not keying it correctly what > > is it > > ? > > > > HTH > > Datta. > > > > > > > > yamuna_pandiyan@freddiemac.com wrote: > > I am using a program which needs to compare the current data with the > > previous data file. the data files are created with date and time > > stamp. > > This program runs once a week, and has to compare the previous weeks > > data > > file to monitor the data changes. > > > > I am using the following command on the Unix box to sort the most > > recent > > file > > > > let $file_list =3D '{FILEPREFIX}Latestfile.dat' > > > > let $syscmd =3D '{FILEPREFIX}' || 'ls -lts SAMPLE.DAT >' || $file_list > > call system using $syscmd #unix_status > > if #unix_status !=3D 0 > > stop quiet > > end-if > > > > The Latestfile.dat file is created in the data directory mentioned > > under > > {FILEPREFIX}. But the file is empty. I would like to send the result > > of the > > command > > ls -lts SAMPLE.DAT* to the above file. > > when I execute the above program in the unix server , I ge the > > following > > error - {FILEPREFIX} : cannot execute, but a blank file in the name > > of > > Latestfile.dat is created. > > > > any help in greatly appreciated. > > > > Thanks, > > Yamuna > > 918-5127 > > > > > > > > _______________________________________________ > > sqr-users mailing list > > sqr-users@sqrug.org > > http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > > --------------------------------- > > Do you Yahoo!? > > Yahoo! Sports - Sign up for Fantasy Baseball. > > > > _______________________________________________ > > 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 > > > > __________________________________ > Do you Yahoo!? > Yahoo! Personals - Better first dates. More second dates. > http://personals.yahoo.com > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 14:09:11 2005 Date: Mon, 04 Apr 2005 15:07:46 -0400 From: "Jamie Harris" Subject: Re: [sqr-users] Call SYSTEM command in SQR Hello, > $CMD = 'cd' ||$DataDirectory > $cmd = 'ls -lt SAMPLE.DAT >' || $DataDirectory || > $file_list > call system using $syscmd #unix_status > It looks to me like the above would not even try to execute the 'cd' command, since there is no 'call system' between the first and second lines (which each assign $CMD to something). SQR variable names are not case-sensitive. I also see that in the call system command, it's using $syscmd - I don't know where that gets set, but it's not going to contain the cd or ls commands above. If 'cd' won't work in the program, you could try this: let $cmd = 'ls -lt ' $DataDirectory || '/SAMPLE.DAT >' || $DataDirectory || > $file_list call system using $cmd #unix_status There's no need to actually move to $DataDirectory if all you need is a listing of the sample.dat files within. Hope this helps! ----------------------------------------------------- James Harris Junior Systems Programmer/Analyst Information Technology Division Frederick Community College ----------------------------------------------------- >>> yamuna_pandiyan@freddiemac.com 4/4/2005 2:12:32 PM >>> Yes there is a space in the cmd line , that was a typo in the below email, it should be - $CMD = 'cd' || ' ' || $DataDirectory Thanks, Yamuna 918-5127 "Prasanna Menta" To Sent by : "This list is for discussion about sqr-users-bounces+yamuna_pan the SQR database reporting language diyan=freddiemac.com@sqrug.o from Hyperion Solutions." rg cc 04/04/2005 02:06 Subject PM Re: [sqr-users] Call SYSTEM command in SQR Please respond to "This list is for discussion about the SQR database reporting language from Hyperion Solutions." You have to know the structure of your unix file directory - Also you need a space between cd and $datadirectory $CMD = 'cd' || ' '|| $DataDirectory yamuna_pandiyan@freddiemac.com wrote: > > The following command works fine or the directory from where the SQR > program runs - '/shared/main/prog/sqr'. This command does not look for the > file in a different directory, i.e the data file resides in the data > directory - ' /shared/main/files/data/'. This is true if the command is > used with or without the predefined prefix variable. > > Example, I copied the data file from the above data directory to the SQR > directory and ran the program.The latest SAMPLE.DAT file name was written > into the filename defined in the '$file_list' variable. > > I did the cd command, $SqrDirectory = '/shared/main/prog/sqr' > $DataDirectory = '/shared/main/files/data/' > > $CMD = 'cd' ||$DataDirectory > $cmd = 'ls -lt SAMPLE.DAT >' || $DataDirectory || > $file_list > call system using $syscmd #unix_status > > I get the error - SAMPLE.DAT: No such file or directory > > But the above command works fine if I copy the SAMPLE.DAT file into the SQR > directory and use the following code, > > $cmd = 'ls -lt SAMPLE.DAT >' || ' ' || $file_list > call system using $syscmd #unix_status > > Could you help me how to use the 'cd' command with 'ls' command or how to > make the program to look for the data file in the data directory...? > > Thanks, > Yamuna > > > "Dattatraya > Shetty" > oo.com> "This list is for discussion about > the SQR database reporting language > Sent by : from Hyperion Solutions." > sqr-users-bounces+yamuna_pan > diyan=freddiemac.com@sqrug.o cc > rg > Subject > Re: [sqr-users] Call SYSTEM > 03/31/2005 01:25 command in SQR > PM > > > Please respond to > "This list is > for discussion > about the SQR > database > reporting > language from > Hyperion > Solutions." > .org> > > > > This fix is simple change the command to below > let $syscmd = 'ls -lts SAMPLE.DAT >' || $file_list > call system using $syscmd #unix_status > if #unix_status != 0 > stop quiet > end-if > > No need to Add the fileprefix to ls command. Its a Unix command and if > you add fileprefix to it the OS will try and execute the ls command > from the path in fileprefix which is wrong because the ls command is > not stored there. its some where in /usr/bin: > if you need to give the path then log into unix and issue the following > command > > $> whence ls > see the path displayed and use that. > > HTH > Datta. > > --- yamuna_pandiyan@freddiemac.com wrote: > > > > > Hi Datta, > > > > The sqr program resides in the ' /shared/main/prog/sqr' directory. > > The > > data file is created in the ' /shared/main/files/data/' directory. > > The > There > > is nothing wrong in the {FILEPREFIX} variable, since it woks fine > > with all > > other programs. > > > > But I am not able to debug, since I have limited knowledge on the > > Call > > System commands. When the below program is executed, the > > Latestfile.dat is > > created in the directory ' /shared/main/files/data/'. > > > > The error that is generated when executing the program is 'sh: > > /shared/main/files/data/ls : not found' if I use the below command. > > if I remove the {FILEPREFIX} from the command line, the following > > error is > > generated - '/shared/main/files/data/ : cannot execute' > > > > Thanks, > > Yamuna > > 918-5127 > > > > > > > > > > "Dattatraya > > > > Shetty" > > > > > To > > oo.com> "This list is for discussion > > about > > the SQR database reporting > > language > > Sent by : from Hyperion Solutions." > > > > sqr-users-bounces+yamuna_pan > > > > diyan=freddiemac.com@sqrug.o > > cc > > rg > > > > > > Subject > > Re: [sqr-users] Call SYSTEM > > > > 03/30/2005 04:14 command in SQR > > > > PM > > > > > > > > > > > > Please respond to > > > > "This list is > > > > for discussion > > > > about the SQR > > > > database > > > > reporting > > > > language from > > > > Hyperion > > > > Solutions." > > > > > > > .org> > > > > > > > > > > > > > > > > > > > > Hi Yamuna, > > The Second part of the Problem is that FILEPREFIX may be an > > environment > > variable not defined? > > > > In the fist part the $ is missing for an environment variable. > > > > It would help if you let us know what is the excat command you are > > typing > > in at the command > > prompt. > > > > e.g > > > > $> ls -tls FMUBSOPT.DAT* > ${FILEPREFIX}Latestfile.dat > > is this what you are typing ? > > > > The fact that you are getting a file called Latestfile.dat tells me > > that > > the FILEPREFIX is not defined or you are not keying it correctly what > > is it > > ? > > > > HTH > > Datta. > > > > > > > > yamuna_pandiyan@freddiemac.com wrote: > > I am using a program which needs to compare the current data with the > > previous data file. the data files are created with date and time > > stamp. > > This program runs once a week, and has to compare the previous weeks > > data > > file to monitor the data changes. > > > > I am using the following command on the Unix box to sort the most > > recent > > file > > > > let $file_list = '{FILEPREFIX}Latestfile.dat' > > > > let $syscmd = '{FILEPREFIX}' || 'ls -lts SAMPLE.DAT >' || $file_list > > call system using $syscmd #unix_status > > if #unix_status != 0 > > stop quiet > > end-if > > > > The Latestfile.dat file is created in the data directory mentioned > > under > > {FILEPREFIX}. But the file is empty. I would like to send the result > > of the > > command > > ls -lts SAMPLE.DAT* to the above file. > > when I execute the above program in the unix server , I ge the > > following > > error - {FILEPREFIX} : cannot execute, but a blank file in the name > > of > > Latestfile.dat is created. > > > > any help in greatly appreciated. > > > > Thanks, > > Yamuna > > 918-5127 > > > > > > > > _______________________________________________ > > sqr-users mailing list > > sqr-users@sqrug.org > > http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > > --------------------------------- > > Do you Yahoo!? > > Yahoo! Sports - Sign up for Fantasy Baseball. > > > > _______________________________________________ > > 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 > > > > __________________________________ > Do you Yahoo!? > Yahoo! Personals - Better first dates. More second dates. > http://personals.yahoo.com > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 14:16:44 2005 Subject: Re: [sqr-users] Call SYSTEM command in SQR From: yamuna_pandiyan@freddiemac.com Date: Mon, 4 Apr 2005 15:16:01 -0400 Thanks Jamie, It works. Yamuna 918-5127 "Jamie Harris" To sqr-users@sqrug.org Sent by : cc sqr-users-bounces+yamuna_pan diyan=freddiemac.com@sqrug.o Subject rg Re: [sqr-users] Call SYSTEM command in SQR 04/04/2005 03:07 PM Please respond to "This list is for discussion about the SQR database reporting language from Hyperion Solutions." Hello, > $CMD = 'cd' ||$DataDirectory > $cmd = 'ls -lt SAMPLE.DAT >' || $DataDirectory || > $file_list > call system using $syscmd #unix_status > It looks to me like the above would not even try to execute the 'cd' command, since there is no 'call system' between the first and second lines (which each assign $CMD to something). SQR variable names are not case-sensitive. I also see that in the call system command, it's using $syscmd - I don't know where that gets set, but it's not going to contain the cd or ls commands above. If 'cd' won't work in the program, you could try this: let $cmd = 'ls -lt ' $DataDirectory || '/SAMPLE.DAT >' || $DataDirectory || > $file_list call system using $cmd #unix_status There's no need to actually move to $DataDirectory if all you need is a listing of the sample.dat files within. Hope this helps! ----------------------------------------------------- James Harris Junior Systems Programmer/Analyst Information Technology Division Frederick Community College ----------------------------------------------------- >>> yamuna_pandiyan@freddiemac.com 4/4/2005 2:12:32 PM >>> Yes there is a space in the cmd line , that was a typo in the below email, it should be - $CMD = 'cd' || ' ' || $DataDirectory Thanks, Yamuna 918-5127 "Prasanna Menta" To Sent by : "This list is for discussion about sqr-users-bounces+yamuna_pan the SQR database reporting language diyan=freddiemac.com@sqrug.o from Hyperion Solutions." rg cc 04/04/2005 02:06 Subject PM Re: [sqr-users] Call SYSTEM command in SQR Please respond to "This list is for discussion about the SQR database reporting language from Hyperion Solutions." You have to know the structure of your unix file directory - Also you need a space between cd and $datadirectory $CMD = 'cd' || ' '|| $DataDirectory yamuna_pandiyan@freddiemac.com wrote: > > The following command works fine or the directory from where the SQR > program runs - '/shared/main/prog/sqr'. This command does not look for the > file in a different directory, i.e the data file resides in the data > directory - ' /shared/main/files/data/'. This is true if the command is > used with or without the predefined prefix variable. > > Example, I copied the data file from the above data directory to the SQR > directory and ran the program.The latest SAMPLE.DAT file name was written > into the filename defined in the '$file_list' variable. > > I did the cd command, $SqrDirectory = '/shared/main/prog/sqr' > $DataDirectory = '/shared/main/files/data/' > > $CMD = 'cd' ||$DataDirectory > $cmd = 'ls -lt SAMPLE.DAT >' || $DataDirectory || > $file_list > call system using $syscmd #unix_status > > I get the error - SAMPLE.DAT: No such file or directory > > But the above command works fine if I copy the SAMPLE.DAT file into the SQR > directory and use the following code, > > $cmd = 'ls -lt SAMPLE.DAT >' || ' ' || $file_list > call system using $syscmd #unix_status > > Could you help me how to use the 'cd' command with 'ls' command or how to > make the program to look for the data file in the data directory...? > > Thanks, > Yamuna > > > "Dattatraya > Shetty" > oo.com> "This list is for discussion about > the SQR database reporting language > Sent by : from Hyperion Solutions." > sqr-users-bounces+yamuna_pan > diyan=freddiemac.com@sqrug.o cc > rg > Subject > Re: [sqr-users] Call SYSTEM > 03/31/2005 01:25 command in SQR > PM > > > Please respond to > "This list is > for discussion > about the SQR > database > reporting > language from > Hyperion > Solutions." > .org> > > > > This fix is simple change the command to below > let $syscmd = 'ls -lts SAMPLE.DAT >' || $file_list > call system using $syscmd #unix_status > if #unix_status != 0 > stop quiet > end-if > > No need to Add the fileprefix to ls command. Its a Unix command and if > you add fileprefix to it the OS will try and execute the ls command > from the path in fileprefix which is wrong because the ls command is > not stored there. its some where in /usr/bin: > if you need to give the path then log into unix and issue the following > command > > $> whence ls > see the path displayed and use that. > > HTH > Datta. > > --- yamuna_pandiyan@freddiemac.com wrote: > > > > > Hi Datta, > > > > The sqr program resides in the ' /shared/main/prog/sqr' directory. > > The > > data file is created in the ' /shared/main/files/data/' directory. > > The > There > > is nothing wrong in the {FILEPREFIX} variable, since it woks fine > > with all > > other programs. > > > > But I am not able to debug, since I have limited knowledge on the > > Call > > System commands. When the below program is executed, the > > Latestfile.dat is > > created in the directory ' /shared/main/files/data/'. > > > > The error that is generated when executing the program is 'sh: > > /shared/main/files/data/ls : not found' if I use the below command. > > if I remove the {FILEPREFIX} from the command line, the following > > error is > > generated - '/shared/main/files/data/ : cannot execute' > > > > Thanks, > > Yamuna > > 918-5127 > > > > > > > > > > "Dattatraya > > > > Shetty" > > > > > To > > oo.com> "This list is for discussion > > about > > the SQR database reporting > > language > > Sent by : from Hyperion Solutions." > > > > sqr-users-bounces+yamuna_pan > > > > diyan=freddiemac.com@sqrug.o > > cc > > rg > > > > > > Subject > > Re: [sqr-users] Call SYSTEM > > > > 03/30/2005 04:14 command in SQR > > > > PM > > > > > > > > > > > > Please respond to > > > > "This list is > > > > for discussion > > > > about the SQR > > > > database > > > > reporting > > > > language from > > > > Hyperion > > > > Solutions." > > > > > > > .org> > > > > > > > > > > > > > > > > > > > > Hi Yamuna, > > The Second part of the Problem is that FILEPREFIX may be an > > environment > > variable not defined? > > > > In the fist part the $ is missing for an environment variable. > > > > It would help if you let us know what is the excat command you are > > typing > > in at the command > > prompt. > > > > e.g > > > > $> ls -tls FMUBSOPT.DAT* > ${FILEPREFIX}Latestfile.dat > > is this what you are typing ? > > > > The fact that you are getting a file called Latestfile.dat tells me > > that > > the FILEPREFIX is not defined or you are not keying it correctly what > > is it > > ? > > > > HTH > > Datta. > > > > > > > > yamuna_pandiyan@freddiemac.com wrote: > > I am using a program which needs to compare the current data with the > > previous data file. the data files are created with date and time > > stamp. > > This program runs once a week, and has to compare the previous weeks > > data > > file to monitor the data changes. > > > > I am using the following command on the Unix box to sort the most > > recent > > file > > > > let $file_list = '{FILEPREFIX}Latestfile.dat' > > > > let $syscmd = '{FILEPREFIX}' || 'ls -lts SAMPLE.DAT >' || $file_list > > call system using $syscmd #unix_status > > if #unix_status != 0 > > stop quiet > > end-if > > > > The Latestfile.dat file is created in the data directory mentioned > > under > > {FILEPREFIX}. But the file is empty. I would like to send the result > > of the > > command > > ls -lts SAMPLE.DAT* to the above file. > > when I execute the above program in the unix server , I ge the > > following > > error - {FILEPREFIX} : cannot execute, but a blank file in the name > > of > > Latestfile.dat is created. > > > > any help in greatly appreciated. > > > > Thanks, > > Yamuna > > 918-5127 > > > > > > > > _______________________________________________ > > sqr-users mailing list > > sqr-users@sqrug.org > > http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > > --------------------------------- > > Do you Yahoo!? > > Yahoo! Sports - Sign up for Fantasy Baseball. > > > > _______________________________________________ > > 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 > > > > __________________________________ > Do you Yahoo!? > Yahoo! Personals - Better first dates. More second dates. > http://personals.yahoo.com > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing 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+sqrusersarch=sqrug.org@sqrug.org Mon Apr 04 18:41:20 2005 Date: Mon, 04 Apr 2005 16:40:40 -0700 From: David Donnelly Subject: [sqr-users] Re: Dynamic SQL vs. Fixed SQL execution Olga, After you use the -s SQR flag to check how many times statements are compiled, I would suggest that you hard-code the queries that you think are being constructed in one of your tests that seems to run a long time. In other words, if you think you are constructing let $where = 'where a.b = c.d' and use [$where] as dynamic where-clause, then actually hard-code the same clause and see how that runs. I guess you'll need to do this for all six dynamic queries. You might want to limit the size of the outer query result set (using loops=nnn) to less than "several thousand" while doing this. To be honest, I don't know if the queries are recompiled every time they're executed. The possibilities are (1)yes; (2) no; and (3) only if the contents of the dynamic clause is changed. You could test the latter, and if that turns out to be the case, then don't change the $where variable unless you need to. I have some vague memory of doing this, but I can't remember for sure. And finally, it is possible that the compiler doesn't have enough info to decide about indexes. One thing that is sometimes possible is to include "extra" predicates. For example, if you have a multi-field key, and your dynamic query selects a lower-level part of it, make sure the higher-level fields are all specified. And what about compiler hints -- might they be any help? I have never had to use them. Best of luck Dave _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Apr 05 04:46:20 2005 Subject: Re: [sqr-users] Dynamic SQL vs. Fixed SQL execution From: Wjatscheslaw KRAVTCHENKO Date: Tue, 5 Apr 2005 11:45:31 +0200 Dear Olga, as I said we had a similar problem with the performance of our reports, we rewrote the warehouse. Now we denormalize the data 4 times a day and serve the reports instantly. No joins, no aggregates, everything is precalculated. I think that by tuning SQL/SQR you'll not be able tosignificantly speed up the performance. At least we made this experience, which forced us to rethink the warehouse structure. Regards, ______________________________________ Wjatscheslaw Kravtchenko (Mr.) ProFi System Manager Assistant ProFi Technical Team Information Technology Service Division for Management United Nations Office on Drugs and Crime Tel: (+43-1) 26060-4153 Websites: www.unov.org, www.unodc.org ______________________________________ Impossible is not a fact, only an opinion Olga.Gal@bbh.com Sent by: sqr-users-bounces To +wjatscheslaw.kra "This list is for discussion about vtchenko=unvienna the SQR database reporting language .org@sqrug.org from Hyperion Solutions." cc Monday, 4 April 2005 18:22 Subject Re: [sqr-users] Dynamic SQL vs. Fixed SQL execution Please respond to "This list is for discussion about the SQR database reporting language from Hyperion Solutions." Dear Wjatscheslaw, To produce the report we access huge wide tables (millions of rows) that are joined by indexed columns. The queries are carefully tuned and executed fairly quickly outside the SQR. The processing starts with an outer (driving) query, which supposed to be the slowest one. It would return back thousands of rows, and then 90 percent of the selected rows are processed further: for each processed rows the SQR then selects additional information from 6 other queries that are dynamically constructed (dynamic SQL). These queries access the data by Primary Key, and are very fast when executed outside SQR. It takes over an hour to generate a report of about 650 such entities. By monitoring the progression of the report it looks like the driving query (the slowest one) is returning the list of entities fairly quickly, but it still takes too long to finish the report, which brought me to think that the problem is not really in the data access. Wjatscheslaw KRAVTCHENKO Sent by: sqr-users-bounces+olga.gal=bbh.com@sqrug.org To "This list is for discussion about the SQR database reporting language from Hyperion Solutions." 04/04/2005 11:50 AM cc Subject Please respond to Re: [sqr-users] Dynamic SQL vs. Fixed SQL execution "This list is for discussion about the SQR database reporting language from Hyperion Solutions." Dear Olga, is the SQR forming the SQL slow or is the response from the database slow? How slow is it? Wha amount of data are we dealing with? We had a similar scenario on Sybase 12.5 transactional data. Do you have any samples of the SQL stmts ? How is the Warehouse structured? Anticipating your reply. ______________________________________ Wjatscheslaw Kravtchenko (Mr.) ProFi System Manager Assistant ProFi Technical Team Information Technology Service Division for Management United Nations Office on Drugs and Crime Tel: (+43-1) 26060-4153 Websites: www.unov.org, www.unodc.org ______________________________________ Impossible is not a fact, only an opinion Olga.Gal@bbh.com Sent by: sqr-users-bounces To +wjatscheslaw.kra sqr-users@sqrug.org vtchenko=unvienna cc .org@sqrug.org Subject [sqr-users] Dynamic SQL vs. Fixed Monday, 4 April SQL execution 2005 17:38 Please respond to "This list is for discussion about the SQR database reporting language from Hyperion Solutions." We're using SQR ver 8.1 with Oracle 9i in the datawarehousing environment, which produce reports with huge amount of data and run VERY slow. Our SQR programs construct the Where clause and Order By clause dynamically, based on user's selection, and execute the same Select statement many times within the same job. This makes me think that the slow performance is possibly caused in part by repeating parsing of these dynamically constructed Select statements. Before taking a dramatic decision of changing the reports to have only fixed Select statements with bind variables ONLY in the Where clause, I would like to get assured that this approach will in fact eliminate unnecessary parsing (compilation) of SQL statements. My question is: Is the SQR compiling each SQL statement before executing it, or would it do it only for a newly executed SQL. In other words, if my program will repeatedly call a routine that executes a fixed SQL statement, will this statement get parsed every time it's being executed, or only the first time. Also, are there any other ways to speed up that time of processing? Any advice is greatly appreciated. _______________________________________________ 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+sqrusersarch=sqrug.org@sqrug.org Tue Apr 05 10:35:03 2005 Date: Tue, 05 Apr 2005 11:34:23 -0400 From: Gina Bencke Subject: [sqr-users] Performance Issue with Oracle9i Good Morning, =A0 We are experiencing a strange performance issue with an SQR that worked efficiently prior to our Oracle 9i Upgrade.=A0 We have a program that ran i= n < 5 minutes under Oracle 8 which is taking several hours using Oracle 9i.=A0 = We have identified the specific procedure containing a Select Paragraph that runs slow but can not understand why.=A0 When the logic of the program is changed to call this procedure using a hard coded value for one of the bind variables (which happens to be one of the key fields) the performance issue disappears.=A0 =A0(Obviously changing the bind variable to a hard coded val= ue is not a work around, but simply a step in trying to understand the problem.) =A0 I was able to isolate the problem by writing a test program which simply sets the values of the bind variables and then calls this procedure and the poor performance is repeatable when isolated in this fashion. =A0 I have provided the code for the procedure whose performance is unacceptable below.=A0 However I do not think that the problem is in the join itself . Other programs which use the two tables found in the select paragraph are not experiencing poor performance.=A0 Also executing the join from SQL*Plus does not result in a performance issue.=A0=20 =A0 Has anyone else experienced a similar issue?=A0 Is there a environment parameter that may need to be set? =A0 Gina Bencke Business Analyst Information Services & Technology Cleveland State University Phone: (216) 875-9630 Fax:=A0=A0=A0 (216) 687-9200 =A0 =A0 !*************************************************************************** *********** begin-procedure Get-Skip-Students=20 !*************************************************************************** *********** =A0 =A0=A0 let $Skip_Student =3D 'Y' =A0 begin-SELECT loops=3D1 JB.JOBCODE JC.JOB_FUNCTION =A0 =A0=A0 evaluate &JC.JOB_FUNCTION =A0=A0=A0=A0 when =3D 'STW' =A0=A0=A0=A0 when =3D 'GTA' =A0=A0=A0=A0 when =3D 'NTG' =A0=A0=A0=A0=A0=A0 let $Skip_Student =3D 'Y'=A0=20 =A0=A0=A0=A0=A0=A0 break =A0=A0=A0=A0 when-other=20 =A0=A0=A0=A0=A0=A0 let $Skip_Student =3D 'N'=20 =A0=A0=A0=A0=A0=A0 break =A0=A0=A0 end-evaluate =A0=A0=20 =A0 FROM=A0 PS_JOB JB, =A0=A0=A0=A0=A0 PS_JOBCODE_TBL JC WHERE JB.EMPLID =3D $Old_Emplid =A0 AND JB.EMPL_RCD =3D 0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 !RK 02/19/2003 =A0 AND JB.EFFDT =3D (Select MAX(JB2.EFFDT) from PS_JOB JB2 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 where JB2.EMPLID =3D= JB.EMPLID =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and JB2.EMPL_R= CD =3D JB.EMPL_RCD=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 !RK 02/19/2003 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and JB2.EFFDT = <=3D $Quarter_End_Dt) =A0 AND JB.EFFSEQ =3D (Select MAX(JB3.EFFSEQ) from PS_JOB JB3 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 where JB3.EMPLID =3D= JB.EMPLID =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and JB3.EMPL_R= CD =3D JB.EMPL_RCD=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 !RK 02/19/2003 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and JB3.EFFDT = =3D JB.EFFDT) =A0 AND JB.JOBCODE =3D JC.JOBCODE =A0 and JC.EFFDT=A0=A0 =3D (select max(JC1.EFFDT) from ps_JOBCODE_TBL JC1 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 where JC1.Jobcode= =3D JC.jobcode =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and jc1.effdt <= =3D JB.effdt =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and jc1.eff_Statu= s =3D 'A') end-SELECT =A0 end-procedure =A0 =A0 Gina Bencke Business Analyst Information Services & Technology Cleveland State University Phone: (216) 875-9630 Fax:=A0=A0=A0 (216) 687-9200 =A0 =A0 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Apr 05 10:55:26 2005 From: "Lynds,Rick" Subject: RE: [sqr-users] Performance Issue with Oracle9i Date: Tue, 5 Apr 2005 08:54:16 -0700 I noticed that you are not using SETID on the JOBCODE_TBL. That is the primary key field. Could it be that some quirk is causing the code to perform a table scan where it had not done so before? You can modify your join (and subquery) to join on JC.SETID =3D JB.SETID_JOBCODE HTH 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 Gina Bencke Sent: Tuesday, April 05, 2005 8:34 AM To: sqr-users@sqrug.org Subject: [sqr-users] Performance Issue with Oracle9i Good Morning, =A0 We are experiencing a strange performance issue with an SQR that worked efficiently prior to our Oracle 9i Upgrade.=A0 We have a program that ran i= n < 5 minutes under Oracle 8 which is taking several hours using Oracle 9i.=A0 = We have identified the specific procedure containing a Select Paragraph that runs slow but can not understand why.=A0 When the logic of the program is changed to call this procedure using a hard coded value for one of the bind variables (which happens to be one of the key fields) the performance issue disappears.=A0 =A0(Obviously changing the bind variable to a hard coded val= ue is not a work around, but simply a step in trying to understand the problem.) =A0 I was able to isolate the problem by writing a test program which simply sets the values of the bind variables and then calls this procedure and the poor performance is repeatable when isolated in this fashion. =A0 I have provided the code for the procedure whose performance is unacceptable below.=A0 However I do not think that the problem is in the join itself . Other programs which use the two tables found in the select paragraph are not experiencing poor performance.=A0 Also executing the join from SQL*Plus does not result in a performance issue.=A0=20 =A0 Has anyone else experienced a similar issue?=A0 Is there a environment parameter that may need to be set? =A0 Gina Bencke Business Analyst Information Services & Technology Cleveland State University Phone: (216) 875-9630 Fax:=A0=A0=A0 (216) 687-9200 =A0 =A0 !*************************************************************************** *********** begin-procedure Get-Skip-Students !*************************************************************************** *********** =A0 =A0=A0 let $Skip_Student =3D 'Y' =A0 begin-SELECT loops=3D1 JB.JOBCODE JC.JOB_FUNCTION =A0 =A0=A0 evaluate &JC.JOB_FUNCTION =A0=A0=A0=A0 when =3D 'STW' =A0=A0=A0=A0 when =3D 'GTA' =A0=A0=A0=A0 when =3D 'NTG' =A0=A0=A0=A0=A0=A0 let $Skip_Student =3D 'Y' =A0=A0=A0=A0=A0=A0 break =A0=A0=A0=A0 when-other =A0=A0=A0=A0=A0=A0 let $Skip_Student =3D 'N'=20 =A0=A0=A0=A0=A0=A0 break =A0=A0=A0 end-evaluate =A0=A0=20 =A0 FROM=A0 PS_JOB JB, =A0=A0=A0=A0=A0 PS_JOBCODE_TBL JC WHERE JB.EMPLID =3D $Old_Emplid =A0 AND JB.EMPL_RCD =3D 0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 !RK 02/19/2003 =A0 AND JB.EFFDT =3D (Select MAX(JB2.EFFDT) from PS_JOB JB2 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 where JB2.EMPLID =3D= JB.EMPLID =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and JB2.EMPL_R= CD =3D JB.EMPL_RCD=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 !RK 02/19/2003 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and JB2.EFFDT = <=3D $Quarter_End_Dt) =A0 AND JB.EFFSEQ =3D (Select MAX(JB3.EFFSEQ) from PS_JOB JB3 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 where JB3.EMPLID =3D= JB.EMPLID =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and JB3.EMPL_R= CD =3D JB.EMPL_RCD=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 !RK 02/19/2003 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and JB3.EFFDT = =3D JB.EFFDT) =A0 AND JB.JOBCODE =3D JC.JOBCODE =A0 and JC.EFFDT=A0=A0 =3D (select max(JC1.EFFDT) from ps_JOBCODE_TBL JC1 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 where JC1.Jobcode= =3D JC.jobcode =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and jc1.effdt <= =3D JB.effdt =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and jc1.eff_Statu= s =3D 'A') end-SELECT =A0 end-procedure =A0 =A0 Gina Bencke Business Analyst Information Services & Technology Cleveland State University Phone: (216) 875-9630 Fax:=A0=A0=A0 (216) 687-9200 =A0 =A0 _______________________________________________ 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+sqrusersarch=sqrug.org@sqrug.org Tue Apr 05 13:15:24 2005 Date: Tue, 05 Apr 2005 14:14:47 -0400 From: Gina Bencke Subject: RE: [sqr-users] Performance Issue with Oracle9i Thanks to a suggestion from Rick I modified the SQL to include setid in the where clause. (Btw Rick "AND JC.SETID =3D JB.SETID_JOBCODE" did not help but "AND JC.SETID =3D $SetID" did.) This alone was not enough