From sqr-users-bounces@sqrug.org Mon Jan 01 00:02:21 2007 Date: Mon, 1 Jan 2007 00:00:03 -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.22 $ $Date: 2005/08/19 05:12:40 $ This document gathers in one location information about the sqr-users mail list. It includes instructions for changing your subscription settings (including unsubscribing), as well are guidelines for using the list. ______________________________________________________________________ Table of Contents 1. Changes Since Previous Version 2. Introduction 2.1 sqr-users Mailing List 2.2 sqr-users Hints and Guidelines Document 2.3 Related Mailing Lists 3. Interacting with the list server 3.1 Subscribing to the list 3.2 Unsubscribing from the list 3.3 When your email address changes 3.4 Digests 3.5 Obtaining your password 3.6 Problems 4. Posting to the sqr-users List 4.1 General Guidelines 4.2 Posting a Message 4.3 Replying to a Message 5. Job Announcements ______________________________________________________________________ 1. Changes Since Previous Version Changes since the version dated 2005/08/19: o Added note about spam filtering and sender verification systems blocking subscription confirmation messages. 2. Introduction 2.1. sqr-users Mailing List The sqr-users mailing list was created to enable discussion related to the SQR database language from Hyperion (and to other products in the SQR product suite). Note: SQR used to be published by SQRIBE Technologies, which was merged into Brio Technology on August 3, 1999. Brio Technology changed its name to Brio Software in October 2001. On October 16, 2003, Hyperion acquired Brio Software. SQR and related applications have also been called "Brio.Report" and "Brio Reports". The mailing list is provided as a service of Ray Ontko & Co. A searchable archive of past postings to the mailing list is available from the SQR Users Group web site . 2.2. sqr-users Hints and Guidelines Document This document gives various hints and guidelines about the sqr-users mailing list. It will be posted approximately monthly to the mailing list so that new users can see it (and existing users can find it easily). An HTML version of the document is always available at http://www.sqrug.org/sqr-users/sqr-users-hints.html. If you have comments or suggestions about this document, please contact me directly at the address listed in the title section. This document is Copyright (C) 1999-2004 by Ray Ontko & Co. It may be freely copied and distributed provided it is not modified in any way and it retains the original copyright notice. 2.3. Related Mailing Lists Here are some mailing lists that cover topics relating to SQR in some way. sqr-jobs : SQR Jobs This list may be used for posting SQR-related resumes, recruitments, job announcements, project announcments, help- wanted, work-wanted ads, etc. To join, follow the link above, or send any message to "sqr-jobs-subscribe@yahoogroups.com". peoplesoft-fans : PeopleSoft Fans Moderated list covering technical and functional aspects of PeopleSoft. To join, follow the link above, or send any message to "peoplesoft-fans-subscribe@yahoogroups.com". psusers : PeopleSoft Users For questions related to any aspect of PeopleSoft (including PeopleSoft-specific SQR issues). This list has merged with the Peoplesoft Fans mailing list and is no longer active, but the message achive can still be found at the link above. HyperionSQR : Hyperion- SQR List for discussion of SQR, SQR Developer (formerly called Brio.Report and Report Builder), and SQR iServer. To join, follow the link above, or send any message to "HyperionSQR- subscribe@yahoogroups.com". rm-users : Report- Mart Users List for discussion of Brio Portal (previously called ReportMart). To join, follow the link above, or send a message containing the word "subscribe" in the body to "rm-users- request@sqrug.org". brioportalusersgroup : Brio Portal Users Group list List for discussion of Brio Portal. To join, follow the link above, or send any message to "brioportalusersgroup- subscribe@yahoogroups.com". brio-l : OpenITx Brio List List for technical and functional discussion of all Brio products (but most messages are currently about Brio Intelligence). To join, follow the link above. briousers : Brio.Enter- prise Users List for discussion of the Brio.Enterprise (now called Intelligence) product. To join, follow the link above, or send any message to "briousers-subscribe@yahoogroups.com". briolist : Brio List List for discussion of Brio Intelligence. To join, follow the link above, or send any message to "briolist- subscribe@yahoogroups.com". 3. Interacting with the list server The sqr-users mailing list is hosted using the Mailman list server package. All of the Mailman functions described below are accessed from the sqr-users List Info page, "http://www.sqrug.org/mailman/listinfo/sqr-users". 3.1. Subscribing to the list To subscribe to the mailing list, enter your e-mail address in the box at the top of the "Subscribing to sqr-users" section on the sqr-users List Info page, then choose a password to assign to your subscription and enter it in both "Password" boxes. This password is used to allow you to securely make changes to your subscription over the web. This password will be e-mailed back to you at varous times, so you should not use the same password as you are using elsewhere. Once you have filled in the password, click the "Subscribe" button. You should soon receive an e-mail message from the list server requesting confirmation of your subscription request. Once you have replied to this message and your subscription has been processed, you will receive a "welcome" message giving you more information on the list server, and as well as a reminder of the subscription password you chose. Many sites are now implementing spam filtering and/or sender verification systems, which can prevent your confirmation message from getting delivered to you. You may need check for a message from "sqr- users-request[-AT-]sqrug.org" caught in the filter, or add that address to your list of authorized senders, in order to complete the confirmation process. 3.2. Unsubscribing from the list To unsubscribe from the list, go to the sqr-users List Info page, enter your e-mail address in the "sqr-users Subscribers" section near the bottom of the page, and click the "Unsubscribe or edit options" button. You will then see the "member options" page; fill in your password at the top of the page and click "Log in". (If you have forgotton your password, see the ``Obtaining your Password'' section, below). This will take you to the "membership configuration" page. In the middle of that page, there will be an "Unsubscribing from sqr- users" section; simply turn on the "Yes, I really want to unsubscribe" checkbox and click the "Unsubscribe" button. You should get a page back saying that the removal was successful (as well as a confirmation email message). The removal will take effect immediately; there is no need to send in a confirmation e-mail message. If you don't know your password, you can choose to unsubscribe using e-mail confirmation. Instead of entering the password on the "member options" page, just click the "Unsubscribe" button found just below the "Log In" button. You will then see a page noting that a confirmation e-mail has been sent; once you follow the confirmation instructions in that e-mail your removal will be complete. 3.3. When your email address changes If your e-mail address changes -- for example, if your company changes names, or if your mail administrator changes the way your name is spelled out or abbreviated -- you will need to update your subscription to match your new address before you can post to the list (see the ``Posting'' section, below). To do this, simply log in to your "membership configuration" page (see the directions for logging in to this page in the ``Unsubscribing'' section, above). At the top of that page you'll see a "Changing your membership information" section. Fill out the text boxes and click the "Change My Address and Name" button. You will be sent a confirmation e-mail, and once you have responded to that e-mail your subscription will be updated and you can begin posting from your new address. 3.4. Digests You can request to get your messages in digest form (one large message containing all of the posts made each day). To do this, log in to your "membership configuration" page (see the directions on doing this in the ``Unsubscribing'' section, above). At the bottom of the page, you will see a section titled "Your sqr-users Subscription Options". Change the "Set Digest Mode" option (the second option in the list) to "On" and click the "Submit My Changes" button at the bottom of the page. You actually have a choice of two different digest formats, "MIME" or "Plain Text". The MIME format sends each individual list message as a separate MIME attachment to the digest; the plain text format consists of one large text message, with all of the individual messages run together. Which format you will want to use depends on your mail- reading software (and personal preference). If you are not sure which format will work best, you can just leave the default setting of "MIME" and see if the next morning's digest is easy to read in your mail reader -- you can change to the other format at any time using that same web page. If you use digests, you should not just "reply" to the digest mailing. Instead, be sure your message's subject header matches the subject in the original posting to which you are replying, and that you don't include a quoted copy of the entire digest in your post. To restore normal distribution, simply follow the above instructions choosing the "Off" setting for the Digest option. 3.5. Obtaining your password You will not be able to change your subscription options without your password. If you have forgotten your password, simply click the "Email My Password to Me" button on the "member options" page and your password will be emailed to you. You will also be e-mailed a copy of your password at the beginning of each month, as part of a "mailing list memberships reminder" message from the Mailman software. 3.6. Problems If the above instructions do not work, please contact the list manager at "sqr-users-manager@sqrug.org". (Do not send a message to the list itself -- no one there can help you with your problem!) Please include a description of what you were trying to do and what exactly happened when you made the attempt. 4. Posting to the sqr-users List In order to post to the mailing list, you must be subscribed to it (see directions above). More specifically, in order for Mailman to recognize that this message was sent by a subscriber, the return address on your message much exactly match the addresss you used to subscribe -- if you have more than one address you will need to be sure that you are sending from the correct one. (See the ``When you email address changes'' section, above, if your e-mail address has changed since you subscribed.) 4.1. General Guidelines For any message you send to the list, please: o make sure your mail software is not requesting a "Return Receipt". (If a "Return Receipt Requested" message is sent to the list, several dozen receipt messages will be sent out to the mailing list as other subscribers' mail systems repond....) o do not send messages in HTML or other special formats. (These are not usable by many subscribers, especially those who receive their messages in digest format.) The list server will delete these special attachments from your message and send you a warning notice. Also, if you are trying to send (for example) SQR source code files, make sure your mail-reader software is assigning proper MIME types to your attachments or they may not be distributed. o keep in mind that over 500 people will receive your message, and that it will appear in the list archives. Try to keep your messages on-topic and relatively short. Consider sending your message directly to particular people if it won't interest many of the list's subscribers. o ignore abusive and inappropriate messages that get sent to the list, rather than getting drawn into a debate. The very nature of these messages will cause most subscribers to automatically discredit them -- no "response" is necessary. To the contrary, any response will only "add fuel to the fire," prolonging the controversy and further interupting the normal conversation on the list. 4.2. Posting a Message Once you are subscribed, you can post to the list by sending a message to "sqr-users@sqrug.org". Be sure your subject heading describes your specific problem or question (i.e. don't just use "SQR Question"). In the body of your message, please include the following information: o SQR product name and version number (see notes below) o Operating system name and version number o Database name and version number o Application name (i.e. PeopleSoft) and a version number, if any Also, include a short code sample whenever possible. It's much easier for other people to understand what you are trying to do when they can see the actual code. (At the same time, be careful not to post confidential or proprietary code, since the posting will be placed in a public web archive.) Note that your question should relate to SQR or other Brio Report product in some way. Don't ask questions about PeopleSoft, for example, unless you are having SQR-related issues. (For PeopleSoft- specific questions, see other lists mentioned in the ``Related Mailing Lists'' section.) In 2001, PeopleSoft purchased from Brio the rights to the source code for SQR and began releasing its own line of SQR. PeopleSoft's SQR version numbering is different from Brio/Hyperion's, so it's very important to mention whether you are using Hyperion's "SQR" or "SQR for PeopleSoft". (This info is included automatically if you include the full SQR version-id string as described below.) To get the version-id string for your copy of SQR, you can run the following program: begin-program show $sqr-ver end-program This will produce a line that looks something like this: SQR/3.0.15/Intel/SCO Unix R3.2 V5.0/Oracle 7.2.2.3/Sep 25 1996 Include the full line of output in your message. Note that the oper- ating system and database version numbers listed here are the ones under which this copy of SQR was compiled, so you'll still need to include this information for your own environment. In a command-line environment (Unix, etc.), you can also get this version-id string by saying sqr -id 4.3. Replying to a Message Messages sent out on the list have a Reply-To header pointing to the list. This means that you should be able to simply use your mail reader's "reply" function to send a reply to the list. Generally, all answers to questions sent out on the list should be public replies, so that other subscribers can benefit. In those cases where a private reply is appropriate, be sure to over-ride the default Reply-To address. If your message is not actually a reply to the earlier message, please do not use the "reply" function. Instead, address a new e-mail message to the list as described in the previous section. (This is because the list archives will incorrectly show the new message in the original thread if you use "reply".) You should quote enough lines from the original message that later readers can understand your answer even if they missed the original question. At the same time, you should remove any unnecessary lines from the quote in order to make your message shorter and easier to understand. (Remember that some people read the mailing list in digest form, and the digests become much larger if unnecessary lines are quoted.) 5. Job Announcements Please do not post job announcements on the sqr-users list, but use the sqr-jobs list instead. (See ``Related Mailing Lists'' for more information on this list.) A large number of sqr-users subscribers do not want to receive job announcements (for various reasons) and such posts tend to cause a lot of disruption of the discussion on the list. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Tue Jan 02 00:31:10 2007 Date: Tue, 2 Jan 2007 10:54:42 +0530 From: Sanjay Sambhe Subject: [sqr-users] SQR problem Hi all, while debugging, I have came accross one statement in SQR i.e [PS_CS_EMPL_EVT_T01 X : $e.cs_event_view_name]. Here both are the record names. $e.cs_event_view_name is view in PS which is dynamically selected in another procedure. Can anybody please explain what is the logic behind this? BEGIN-SELECT X.EMPLID &x.emplid X.CS_EVENT_TYPE &x.cs_event_type TO_CHAR(trunc(X.CS_EVENT_ENTRY_DT),'YYYY-MM-DD') &x.cs_event_entry_dt TO_CHAR(trunc(X.CS_EVENT_EFFDT),'YYYY-MM-DD') &x.cs_event_effdt X.CS_PRIORITY_FLAG &x.cs_priority_flag X.CS_EVENT_PARAM &x.cs_event_param show 'Processing Employee: ' &x.emplid ', Event: ' &x.cs_event_type ', Effdt: ' &x.cs_event_effdt show '&x.cs_priority_flag ' &x.cs_priority_flag ',&x.cs_event_param ' &x.cs_event_param do Insert-Employee-Event FROM [PS_CS_EMPL_EVT_T01 X : $e.cs_event_view_name] WHERE not exists (select * from ps_cs_wr_tbl where emplid = x.emplid) and ( !** entire line for Transition project ** (X.CS_EVENT_EFFDT between trunc(SYSDATE)-{RECENCY_DAYS} and trunc(SYSDATE)+#future_days) OR (X.CS_EVENT_ENTRY_DT >= trunc(SYSDATE)-{RECENCY_DAYS} AND X.CS_EVENT_EFFDT <= trunc(SYSDATE)+#future_days)) !** ) For Transition project ** END-SELECT Thanks and Regards, ss _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Tue Jan 02 04:53:23 2007 Subject: RE: [sqr-users] SQR problem Date: Tue, 2 Jan 2007 03:49:40 -0600 From: "Jim Womeldorf" This is a new one to me.=20=20 I have not found any documentation about this but running the following program snippet sheds some light on the subject: Begin-Program show 'Here I am' let $substitute =3D 'xlattable' !Example 1 !let $substitute =3D 'psrecfield' !Example 2 do demo show 'done' End-Program begin-procedure demo begin-select count(fieldname) &c show 'count ' &c from [xlattable : $substitute] end-select end-procedure demo ----------------- If the program is run as shown then the count is 10965 which is indeed the count of our records in xlattable. If the program is run as with the 'Example 2' line active then the count is 198812 which is correct for psrecfield. Apparently the $ value after the colon is substituted for the table name shown, which, by the way, must be a valid table name. ----------------- The code with the colon in it is, interestingly enough, compiled at compile-time using the table name to the left of the colon and then is compiled again at run-time using the value of $substitute. The only purpose of the compile-time compile that comes to mind is that it might provide an 'early warning' if there is something fundamentally wrong with the statement. Otherwise it is necessary to wait until run-time to find that out.=20=20 Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org] On Behalf Of Sanjay Sambhe Sent: Monday, January 01, 2007 11:25 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] SQR problem Hi all, =20 while debugging, I have came accross one statement in SQR i.e [PS_CS_EMPL_EVT_T01 X : $e.cs_event_view_name]. Here both are the record names. $e.cs_event_view_name is view in PS which is dynamically selected in another procedure. Can anybody please explain what is the logic behind this? =20 BEGIN-SELECT X.EMPLID &x.emplid X.CS_EVENT_TYPE &x.cs_event_type TO_CHAR(trunc(X.CS_EVENT_ENTRY_DT),'YYYY-MM-DD') &x.cs_event_entry_dt=20 TO_CHAR(trunc(X.CS_EVENT_EFFDT),'YYYY-MM-DD') &x.cs_event_effdt X.CS_PRIORITY_FLAG &x.cs_priority_flag X.CS_EVENT_PARAM &x.cs_event_param show 'Processing Employee: ' &x.emplid ', Event: ' &x.cs_event_type ', Effdt: ' &x.cs_event_effdt show '&x.cs_priority_flag ' &x.cs_priority_flag ',&x.cs_event_param ' &x.cs_event_param do Insert-Employee-Event FROM [PS_CS_EMPL_EVT_T01 X : $e.cs_event_view_name] WHERE not exists (select * from ps_cs_wr_tbl where emplid =3D x.emplid) and ( !** entire line for Transition project ** (X.CS_EVENT_EFFDT between trunc(SYSDATE)-{RECENCY_DAYS} and trunc(SYSDATE)+#future_days) OR (X.CS_EVENT_ENTRY_DT >=3D trunc(SYSDATE)-{RECENCY_DAYS} AND=20 X.CS_EVENT_EFFDT <=3D trunc(SYSDATE)+#future_days)) !** ) For Transition project ** END-SELECT =20 =20 =20 Thanks and Regards, ss =20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Tue Jan 02 09:30:05 2007 Date: Tue, 2 Jan 2007 09:27:52 -0500 From: "White . Denise" Subject: [sqr-users] RE: SQR problem I believe that this is necessary because the program would not compile with a variable table name in the BEGIN-SELECT (the variable value is not set until after the program has begun execution). Therefore, it is necessary to 'trick' the compiler by providing a valid table name to be used at compile time. Once the variable value has been set, the new table name will be in effect. Denise M. White EBS Programmer/Analyst III Dynamics Research Corporation ------------------------------ Message: 2 Date: Tue, 2 Jan 2007 03:49:40 -0600 From: "Jim Womeldorf" Subject: RE: [sqr-users] SQR problem To: "This list is for discussion about the SQR database reporting language from Hyperion Solutions." Message-ID: <42E50BB7EAB3764E8654A54710243EC0071F72AB@excmb01.backup> Content-Type: text/plain; charset=3D"US-ASCII" This is a new one to me.=20=20 I have not found any documentation about this but running the following program snippet sheds some light on the subject: Begin-Program show 'Here I am' let $substitute =3D 'xlattable' !Example 1 !let $substitute =3D 'psrecfield' !Example 2 do demo show 'done' End-Program begin-procedure demo begin-select count(fieldname) &c show 'count ' &c from [xlattable : $substitute] end-select end-procedure demo ----------------- If the program is run as shown then the count is 10965 which is indeed the count of our records in xlattable. If the program is run as with the 'Example 2' line active then the count is 198812 which is correct for psrecfield. Apparently the $ value after the colon is substituted for the table name shown, which, by the way, must be a valid table name. ----------------- The code with the colon in it is, interestingly enough, compiled at compile-time using the table name to the left of the colon and then is compiled again at run-time using the value of $substitute. The only purpose of the compile-time compile that comes to mind is that it might provide an 'early warning' if there is something fundamentally wrong with the statement. Otherwise it is necessary to wait until run-time to find that out.=20=20 Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org] On Behalf Of Sanjay Sambhe Sent: Monday, January 01, 2007 11:25 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] SQR problem Hi all, =20 while debugging, I have came accross one statement in SQR i.e [PS_CS_EMPL_EVT_T01 X : $e.cs_event_view_name]. Here both are the record names. $e.cs_event_view_name is view in PS which is dynamically selected in another procedure. Can anybody please explain what is the logic behind this? =20 BEGIN-SELECT X.EMPLID &x.emplid X.CS_EVENT_TYPE &x.cs_event_type TO_CHAR(trunc(X.CS_EVENT_ENTRY_DT),'YYYY-MM-DD') &x.cs_event_entry_dt TO_CHAR(trunc(X.CS_EVENT_EFFDT),'YYYY-MM-DD') &x.cs_event_effdt X.CS_PRIORITY_FLAG &x.cs_priority_flag X.CS_EVENT_PARAM &x.cs_event_param show 'Processing Employee: ' &x.emplid ', Event: ' &x.cs_event_type ', Effdt: ' &x.cs_event_effdt show '&x.cs_priority_flag ' &x.cs_priority_flag ',&x.cs_event_param ' &x.cs_event_param do Insert-Employee-Event FROM [PS_CS_EMPL_EVT_T01 X : $e.cs_event_view_name] WHERE not exists (select * from ps_cs_wr_tbl where emplid =3D x.emplid) and ( !** entire line for Transition project ** (X.CS_EVENT_EFFDT between trunc(SYSDATE)-{RECENCY_DAYS} and trunc(SYSDATE)+#future_days) OR (X.CS_EVENT_ENTRY_DT >=3D trunc(SYSDATE)-{RECENCY_DAYS} AND=20 X.CS_EVENT_EFFDT <=3D trunc(SYSDATE)+#future_days)) !** ) For Transition project ** END-SELECT =20 =20 =20 Thanks and Regards, ss _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 13:09:06 2007 Date: Thu, 4 Jan 2007 18:06:04 +0000 (GMT) From: john.tucker@oracle.com Subject: [sqr-users] Thinking about trying something - looking for ideas I have a rather different question today. I am thinking about writing an SQR that would look for orphan data anywhere in a PeopleSoft database, using PeopleTools tables to figure out what to look at rather than using a static list of tables. I have an idea or two about how to go about this... but I wondered if anyone out here had already written something similar and/or had some suggestions for things to do/avoid. For example, there will be a lot of dynamic SQL getting processed in this SQR. Does that usually stop indexes from being used? If so, is there any way I can force indexes to be used? Also, would it be better to say "select [whatever] from [child] where not exists ([whatever] in [parent])" or would it be better to write two select statements, pulling all rows from the child table and trying to match them with rows in the parent table in a separate select statement? Either way, I already know this thing is going to take forever to run. I don't want you fine folks to do my work for me, but I was pretty sure someone out there would have suggestions on how I can make this a better, faster tool than the way I would have written it without asking. -John T. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 13:18:46 2007 Subject: RE: [sqr-users] Thinking about trying something - looking for ideas Date: Thu, 4 Jan 2007 13:15:33 -0500 From: "Overcashier, Patricia" John, This sounds like it would be a very useful sqr. Could you please pass on any suggestions you may get. I'm interested in looking for orphans too and agree that it is easy to imagine and put together an sqr to do that, using dynamic sqls selecting from the tools tables, but also agree that it would take forever to run.=20=20 Thanks, Patti -----Original Message----- From: sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org [mailto:sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org] On Behalf Of john.tucker@oracle.com Sent: Thursday, January 04, 2007 1:06 PM To: sqr-users@sqrug.org Subject: [sqr-users] Thinking about trying something - looking for ideas I have a rather different question today. I am thinking about writing an SQR that would look for orphan data anywhere in a PeopleSoft database, using PeopleTools tables to figure out what to look at rather than using a static list of tables. I have an idea or two about how to go about this... but I wondered if anyone out here had already written something similar and/or had some suggestions for things to do/avoid. For example, there will be a lot of dynamic SQL getting processed in this SQR. Does that usually stop indexes from being used? If so, is there any way I can force indexes to be used? Also, would it be better to say "select [whatever] from [child] where not exists ([whatever] in [parent])" or would it be better to write two select statements, pulling all rows from the child table and trying to match them with rows in the parent table in a separate select statement? Either way, I already know this thing is going to take forever to run. I don't want you fine folks to do my work for me, but I was pretty sure someone out there would have suggestions on how I can make this a better, faster tool than the way I would have written it without asking. -John T. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 13:21:58 2007 Date: Thu, 04 Jan 2007 13:17:08 -0500 From: Ray Ontko Subject: Re: [sqr-users] Thinking about trying something - looking for ideas John, Here's another approach that you might consider: Read the Peoplesoft tables and generate SQR programs that contain static queries (instead of dynamic ones). I'm not sure, BTW, that the performance would be worse with dynamic queries. It may depend on which database you're using. Ray john.tucker@oracle.com wrote: > I have a rather different question today. I am thinking about writing an SQR that would look for orphan data anywhere in a PeopleSoft database, using PeopleTools tables to figure out what to look at rather than using a static list of tables. I have an idea or two about how to go about this... but I wondered if anyone out here had already written something similar and/or had some suggestions for things to do/avoid. > > For example, there will be a lot of dynamic SQL getting processed in this SQR. Does that usually stop indexes from being used? If so, is there any way I can force indexes to be used? > > Also, would it be better to say "select [whatever] from [child] where not exists ([whatever] in [parent])" or would it be better to write two select statements, pulling all rows from the child table and trying to match them with rows in the parent table in a separate select statement? Either way, I already know this thing is going to take forever to run. > > I don't want you fine folks to do my work for me, but I was pretty sure someone out there would have suggestions on how I can make this a better, faster tool than the way I would have written it without asking. > > -John T. > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 13:27:46 2007 Date: Thu, 4 Jan 2007 10:25:12 -0800 (PST) From: Arun Kumar Subject: [sqr-users] creating HTML reports I did search the group page for answers. But didnt suceed. I want HTML ouput. Trying for the first time. The sqr is running on windows= machine. its windows2003 server. added the following entry in sqr.ini=20 [Environment:DDO] SQR_DDO_JRE_CLASS=3D\..\..\server.jar;\..\..\mail.jar;\..\..\jcert.jar;\..\= ..\jnet.jar;\..\..\jsse.jar;\..\..\activation.jar;\..\..\autolink.jar;\..\.= .\classes12.zip;\..\..\una2000.jar But I am still getting the follwoing error message. (SQR 7715) Failed to start the Java Virtual Machine (JVM). Possible causes are: missing or invalid jre files, incorrect CLASSPATH, or insufficient resources. I dont have any knowledge on the initial installation. Any help would be appreciated... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around=20 http://mail.yahoo.com=20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 13:36:50 2007 Date: Thu, 04 Jan 2007 13:33:52 -0500 From: "George Jansen" Subject: Re: [sqr-users] Thinking about trying something - looking for ideas Dynamic SQL will not per se prevent the use of indexes. If you were to be performing essentially the same query hundreds or thousands of times, you'd want to avoid dynamic SQL. In this case, you'll be running one query per table, won't you? So the additional overhead in the parsing is likely to be vanishingly small compared to the other and unavoidable costs of the query. Do make sure that your schema is reasonably well analyzed, though. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 13:40:22 2007 Date: Thu, 4 Jan 2007 10:35:43 -0800 (PST) From: Arun Kumar Subject: Re: [sqr-users] Thinking about trying something - looking for ideas I am not sure about this. If all the data comes in to the PS database thru its front end then orphan = rows are impossible to create in first place. Orphans row exists because of data imported thru back doors and most of the= time during the initial conversion. Once you fixed those process you will never have orphans, right? Just my 2 cents. ----- Original Message ---- From: "Overcashier, Patricia" To: This list is for discussion about the SQR database reportinglanguage fr= om Hyperion Solutions. Sent: Thursday, January 4, 2007 1:15:33 PM Subject: RE: [sqr-users] Thinking about trying something - looking for ideas John, This sounds like it would be a very useful sqr. Could you please pass on any suggestions you may get. I'm interested in looking for orphans too and agree that it is easy to imagine and put together an sqr to do that, using dynamic sqls selecting from the tools tables, but also agree that it would take forever to run.=20=20 Thanks, Patti -----Original Message----- From: sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org [mailto:sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org] On Behalf Of john.tucker@oracle.com Sent: Thursday, January 04, 2007 1:06 PM To: sqr-users@sqrug.org Subject: [sqr-users] Thinking about trying something - looking for ideas I have a rather different question today. I am thinking about writing an SQR that would look for orphan data anywhere in a PeopleSoft database, using PeopleTools tables to figure out what to look at rather than using a static list of tables. I have an idea or two about how to go about this... but I wondered if anyone out here had already written something similar and/or had some suggestions for things to do/avoid. For example, there will be a lot of dynamic SQL getting processed in this SQR. Does that usually stop indexes from being used? If so, is there any way I can force indexes to be used? Also, would it be better to say "select [whatever] from [child] where not exists ([whatever] in [parent])" or would it be better to write two select statements, pulling all rows from the child table and trying to match them with rows in the parent table in a separate select statement? Either way, I already know this thing is going to take forever to run. I don't want you fine folks to do my work for me, but I was pretty sure someone out there would have suggestions on how I can make this a better, faster tool than the way I would have written it without asking. -John T. _______________________________________________ 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!? Tired of spam? Yahoo! Mail has the best spam protection around=20 http://mail.yahoo.com=20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 13:46:03 2007 Subject: RE: [sqr-users] Thinking about trying something - looking for ideas Date: Thu, 4 Jan 2007 13:42:58 -0500 From: "Overcashier, Patricia" Only in the perfect world :) In my world we are always running batch processes underneath to add, update, and delete rows in cases where our users do not want to do mass data entry for hundreds or thousands of rows. -----Original Message----- From: sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org [mailto:sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org] On Behalf Of Arun Kumar Sent: Thursday, January 04, 2007 1:36 PM To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] Thinking about trying something - looking for ideas I am not sure about this. If all the data comes in to the PS database thru its front end then orphan rows are impossible to create in first place. Orphans row exists because of data imported thru back doors and most of the time during the initial conversion. Once you fixed those process you will never have orphans, right? Just my 2 cents. ----- Original Message ---- From: "Overcashier, Patricia" To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Sent: Thursday, January 4, 2007 1:15:33 PM Subject: RE: [sqr-users] Thinking about trying something - looking for ideas John, This sounds like it would be a very useful sqr. Could you please pass on any suggestions you may get. I'm interested in looking for orphans too and agree that it is easy to imagine and put together an sqr to do that, using dynamic sqls selecting from the tools tables, but also agree that it would take forever to run.=20=20 Thanks, Patti -----Original Message----- From: sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org [mailto:sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org] On Behalf Of john.tucker@oracle.com Sent: Thursday, January 04, 2007 1:06 PM To: sqr-users@sqrug.org Subject: [sqr-users] Thinking about trying something - looking for ideas I have a rather different question today. I am thinking about writing an SQR that would look for orphan data anywhere in a PeopleSoft database, using PeopleTools tables to figure out what to look at rather than using a static list of tables. I have an idea or two about how to go about this... but I wondered if anyone out here had already written something similar and/or had some suggestions for things to do/avoid. For example, there will be a lot of dynamic SQL getting processed in this SQR. Does that usually stop indexes from being used? If so, is there any way I can force indexes to be used? Also, would it be better to say "select [whatever] from [child] where not exists ([whatever] in [parent])" or would it be better to write two select statements, pulling all rows from the child table and trying to match them with rows in the parent table in a separate select statement? Either way, I already know this thing is going to take forever to run. I don't want you fine folks to do my work for me, but I was pretty sure someone out there would have suggestions on how I can make this a better, faster tool than the way I would have written it without asking. -John T. _______________________________________________ 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!? Tired of spam? Yahoo! Mail has the best spam protection around=20 http://mail.yahoo.com=20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 13:55:41 2007 Date: Thu, 04 Jan 2007 11:53:06 -0700 From: jwillson@dssltd.com Subject: Re: [sqr-users] Thinking about trying something - looking for ideas Hi John. here is a collection of possibilties: 1) In doing a report suite I benchmarked a single dynamic SQL retrieval versus numerous individual retrievals and the individual ones were both faster and easier to debug. However, the elegance of the dynamic SQL made for a very short SQR. 2) In doing PS table retrievals I use dynamic SQL to load the most recent version of a possibly underlying changing table so that I don't need to know what it is until run time of the SQR with the dynamic SQL. Works well in large development projects where multiple people have control of individual tables as opposed to 1 person having control of tables. 3) SQR that writes SQR's based upon that dynamic SQL is possible as stated in 2). Others have one that parses SQL Plus and builds an SQR. Both still require tweaking (Decode instruction, etc) 4) Tree walking software for PS is tricky (as you have to do the matching you are suggesting of parent child keys) and I have only gone down 7 levels in a Department Security tree. You may be able to find someone who has generalized this. Again, it might be better to tree represent say in a load array then to do selects down through all the paths to the leaves and work off the load array. 5) Index tuning on the keys will help. Just some possibilities. John Willson http://www.dssltd.com Quoting john.tucker@oracle.com: > I have a rather different question today. I am thinking about > writing an SQR that would look for orphan data anywhere in a > PeopleSoft database, using PeopleTools tables to figure out what to > look at rather than using a static list of tables. I have an idea or > two about how to go about this... but I wondered if anyone out here > had already written something similar and/or had some suggestions for > things to do/avoid. > > For example, there will be a lot of dynamic SQL getting processed in > this SQR. Does that usually stop indexes from being used? If so, is > there any way I can force indexes to be used? > > Also, would it be better to say "select [whatever] from [child] where > not exists ([whatever] in [parent])" or would it be better to write > two select statements, pulling all rows from the child table and > trying to match them with rows in the parent table in a separate > select statement? Either way, I already know this thing is going to > take forever to run. > > I don't want you fine folks to do my work for me, but I was pretty > sure someone out there would have suggestions on how I can make this > a better, faster tool than the way I would have written it without > asking. > > -John T. > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 13:59:47 2007 Date: Thu, 04 Jan 2007 13:57:08 -0500 From: Donald Mellen Subject: Re: [sqr-users] creating HTML reports Well, I'm assuming you're using DDO, otherwise that statement won't be looked at anyway. You might try putting it in the [Environment:Common] section. If you don't need the JVM embeded directly into the sqr, you could also add SQR_USEJVM = False in the same section. HTH, Don Arun Kumar wrote: > I did search the group page for answers. But didnt suceed. > > I want HTML ouput. Trying for the first time. The sqr is running on windows machine. its windows2003 server. > > added the following entry in sqr.ini > > [Environment:DDO] > SQR_DDO_JRE_CLASS=\..\..\server.jar;\..\..\mail.jar;\..\..\jcert.jar;\..\..\jnet.jar;\..\..\jsse.jar;\..\..\activation.jar;\..\..\autolink.jar;\..\..\classes12.zip;\..\..\una2000.jar > > > But I am still getting the follwoing error message. > > (SQR 7715) Failed to start the Java Virtual Machine (JVM). > Possible causes are: missing or invalid jre files, > incorrect CLASSPATH, or insufficient resources. > > I dont have any knowledge on the initial installation. > > Any help would be appreciated... > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > -- ----------------------------------------------------------------------- Donald Mellen | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/ donm@ontko.com | "In the beginning, there was nothing, which exploded" _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 14:21:50 2007 From: "Franck Masson" Subject: RE: [sqr-users] creating HTML reports Date: Thu, 4 Jan 2007 20:21:47 +0100 Arun check in your sqr.ini these entries [Environment:Common] SQRDIR=D:\BPS8\SQR8\SQR\ODB\BINW BRIO_HOME=D:\BPS8\SQR8 and verify that you have a JRE folder under the BRIO_HOME folder Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 19:25 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: [sqr-users] creating HTML reports I did search the group page for answers. But didnt suceed. I want HTML ouput. Trying for the first time. The sqr is running on windows machine. its windows2003 server. added the following entry in sqr.ini [Environment:DDO] SQR_DDO_JRE_CLASS=\..\..\server.jar;\..\..\mail.jar;\..\..\jcert.jar;\..\..\ jnet.jar;\..\..\jsse.jar;\..\..\activation.jar;\..\..\autolink.jar;\..\..\cl asses12.zip;\..\..\una2000.jar But I am still getting the follwoing error message. (SQR 7715) Failed to start the Java Virtual Machine (JVM). Possible causes are: missing or invalid jre files, incorrect CLASSPATH, or insufficient resources. I dont have any knowledge on the initial installation. Any help would be appreciated... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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@sqrug.org Thu Jan 04 15:12:38 2007 Date: Thu, 4 Jan 2007 12:09:57 -0800 (PST) From: Arun Kumar Subject: Re: [sqr-users] creating HTML reports Thanks to franck and Don, I tried both suggestions and its still giving me the same error. I did add = all the entries to my common section. My ini file looks like this now=20 [Environment:Common] SQRDIR=3Dc:\sqrbin\windows BRIO_HOME=3Dc:\sqrbin\ SQR_USEJVM =3D TRUE SQR_DDO_JRE_PATH=3Dc:\sqrbin\jre SQR_DDO_JRE_CLASS=3Dc:\jar\server.jar;c:\jar\mail.jar;c:\jar\jcert.jar;c:\j= ar\jnet.jar;c:\jar\jsse.jar;c:\jar\activation.jar;c:\jar\autolink.jar;c:\ja= r\classes12.zip;c:\jar\una2000.jar I did create a new folder (jre) under brio_home. Strangely when I turn sqr_usejvm to false. SQR runs without error but does= nt produce the output file either. What am I missing?=20 Thanks again. arun ----- Original Message ---- From: Franck Masson To: This list is for discussion about the SQR database reportinglanguage fr= om Hyperion Solutions. Sent: Thursday, January 4, 2007 2:21:47 PM Subject: RE: [sqr-users] creating HTML reports Arun check in your sqr.ini these entries [Environment:Common] SQRDIR=3DD:\BPS8\SQR8\SQR\ODB\BINW BRIO_HOME=3DD:\BPS8\SQR8 and verify that you have a JRE folder under the BRIO_HOME folder Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 19:25 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: [sqr-users] creating HTML reports I did search the group page for answers. But didnt suceed. I want HTML ouput. Trying for the first time. The sqr is running on windows machine. its windows2003 server. added the following entry in sqr.ini [Environment:DDO] SQR_DDO_JRE_CLASS=3D\..\..\server.jar;\..\..\mail.jar;\..\..\jcert.jar;\..\= ..\ jnet.jar;\..\..\jsse.jar;\..\..\activation.jar;\..\..\autolink.jar;\..\..\cl asses12.zip;\..\..\una2000.jar But I am still getting the follwoing error message. (SQR 7715) Failed to start the Java Virtual Machine (JVM). Possible causes are: missing or invalid jre files, incorrect CLASSPATH, or insufficient resources. I dont have any knowledge on the initial installation. Any help would be appreciated... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around=20 http://mail.yahoo.com=20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 15:36:27 2007 From: "the dragon" Subject: Re: [sqr-users] Thinking about trying something - looking for ideas Date: Thu, 04 Jan 2007 14:33:41 -0600 Good points - I agree. Unfortunately, like where I work, the idiots have a serious fetish for putting crap in through very poorly designed and almost untested interfaces, and then we waste too much time cleaning up the mess. Evidently, they (idiot users and even more clueless management) feel it's a better use of time, resources and money having developers determine the problem and then having PS admins and DBAs fix the errors in the database through sql scripts rather than taking the time to fix the feeds. /shakes head peace, clark 'the dragon' willis PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away. I work to live; I don't live to work. "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." -- Carl Sandburg (1878 - 1967) It is impossible to defeat an ignorant man in argument. -- William G. McAdoo Religion is regarded by the common people as true, by the wise as false, and by the rulers as useful. -- Seneca ----Original Message Follows---- I am not sure about this. If all the data comes in to the PS database thru its front end then orphan rows are impossible to create in first place. Orphans row exists because of data imported thru back doors and most of the time during the initial conversion. Once you fixed those process you will never have orphans, right? Just my 2 cents. ----- Original Message ---- John, This sounds like it would be a very useful sqr. Could you please pass on any suggestions you may get. I'm interested in looking for orphans too and agree that it is easy to imagine and put together an sqr to do that, using dynamic sqls selecting from the tools tables, but also agree that it would take forever to run. Thanks, Patti -----Original Message----- I have a rather different question today. I am thinking about writing an SQR that would look for orphan data anywhere in a PeopleSoft database, using PeopleTools tables to figure out what to look at rather than using a static list of tables. I have an idea or two about how to go about this... but I wondered if anyone out here had already written something similar and/or had some suggestions for things to do/avoid. For example, there will be a lot of dynamic SQL getting processed in this SQR. Does that usually stop indexes from being used? If so, is there any way I can force indexes to be used? Also, would it be better to say "select [whatever] from [child] where not exists ([whatever] in [parent])" or would it be better to write two select statements, pulling all rows from the child table and trying to match them with rows in the parent table in a separate select statement? Either way, I already know this thing is going to take forever to run. I don't want you fine folks to do my work for me, but I was pretty sure someone out there would have suggestions on how I can make this a better, faster tool than the way I would have written it without asking. -John T. _________________________________________________________________ Find sales, coupons, and free shipping, all in one place!  MSN Shopping Sales & Deals http://shopping.msn.com/content/shp/?ctid=198,ptnrid=176,ptnrdata=200639 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 15:40:06 2007 From: "the dragon" Subject: RE: [sqr-users] Thinking about trying something - looking for ideas Date: Thu, 04 Jan 2007 14:36:13 -0600 Not just a perfect world, but in a rational, logical, intelligent world. Of course, that would exclude the in-duh-viduals we work for... peace, clark 'the dragon' willis PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away. I work to live; I don't live to work. "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." -- Carl Sandburg (1878 - 1967) It is impossible to defeat an ignorant man in argument. -- William G. McAdoo Religion is regarded by the common people as true, by the wise as false, and by the rulers as useful. -- Seneca ----Original Message Follows---- Only in the perfect world :) In my world we are always running batch processes underneath to add, update, and delete rows in cases where our users do not want to do mass data entry for hundreds or thousands of rows. -----Original Message----- I am not sure about this. If all the data comes in to the PS database thru its front end then orphan rows are impossible to create in first place. Orphans row exists because of data imported thru back doors and most of the time during the initial conversion. Once you fixed those process you will never have orphans, right? Just my 2 cents. ----- Original Message ---- John, This sounds like it would be a very useful sqr. Could you please pass on any suggestions you may get. I'm interested in looking for orphans too and agree that it is easy to imagine and put together an sqr to do that, using dynamic sqls selecting from the tools tables, but also agree that it would take forever to run. Thanks, Patti -----Original Message----- I have a rather different question today. I am thinking about writing an SQR that would look for orphan data anywhere in a PeopleSoft database, using PeopleTools tables to figure out what to look at rather than using a static list of tables. I have an idea or two about how to go about this... but I wondered if anyone out here had already written something similar and/or had some suggestions for things to do/avoid. For example, there will be a lot of dynamic SQL getting processed in this SQR. Does that usually stop indexes from being used? If so, is there any way I can force indexes to be used? Also, would it be better to say "select [whatever] from [child] where not exists ([whatever] in [parent])" or would it be better to write two select statements, pulling all rows from the child table and trying to match them with rows in the parent table in a separate select statement? Either way, I already know this thing is going to take forever to run. I don't want you fine folks to do my work for me, but I was pretty sure someone out there would have suggestions on how I can make this a better, faster tool than the way I would have written it without asking. -John T. _________________________________________________________________ Dave vs. Carl: The Insignificant Championship Series.  Who will win? http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http://davevscarl.spaces.live.com/?icid=T001MSN38C07001 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 15:49:21 2007 From: "Franck Masson" Subject: RE: [sqr-users] creating HTML reports Date: Thu, 4 Jan 2007 21:49:07 +0100 What is your SQR version ? you should not have to create the jre folder, itshould be created during the installation. it seem that you are missing all the JVM installation which should be jre1.1.7 or 1.18 or 1.2 depending your SQR version Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 21:10 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] creating HTML reports Thanks to franck and Don, I tried both suggestions and its still giving me the same error. I did add all the entries to my common section. My ini file looks like this now [Environment:Common] SQRDIR=c:\sqrbin\windows BRIO_HOME=c:\sqrbin\ SQR_USEJVM = TRUE SQR_DDO_JRE_PATH=c:\sqrbin\jre SQR_DDO_JRE_CLASS=c:\jar\server.jar;c:\jar\mail.jar;c:\jar\jcert.jar;c:\jar\ jnet.jar;c:\jar\jsse.jar;c:\jar\activation.jar;c:\jar\autolink.jar;c:\jar\cl asses12.zip;c:\jar\una2000.jar I did create a new folder (jre) under brio_home. Strangely when I turn sqr_usejvm to false. SQR runs without error but doesnt produce the output file either. What am I missing? Thanks again. arun ----- Original Message ---- From: Franck Masson To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Sent: Thursday, January 4, 2007 2:21:47 PM Subject: RE: [sqr-users] creating HTML reports Arun check in your sqr.ini these entries [Environment:Common] SQRDIR=D:\BPS8\SQR8\SQR\ODB\BINW BRIO_HOME=D:\BPS8\SQR8 and verify that you have a JRE folder under the BRIO_HOME folder Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 19:25 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: [sqr-users] creating HTML reports I did search the group page for answers. But didnt suceed. I want HTML ouput. Trying for the first time. The sqr is running on windows machine. its windows2003 server. added the following entry in sqr.ini [Environment:DDO] SQR_DDO_JRE_CLASS=\..\..\server.jar;\..\..\mail.jar;\..\..\jcert.jar;\..\..\ jnet.jar;\..\..\jsse.jar;\..\..\activation.jar;\..\..\autolink.jar;\..\..\cl asses12.zip;\..\..\una2000.jar But I am still getting the follwoing error message. (SQR 7715) Failed to start the Java Virtual Machine (JVM). Possible causes are: missing or invalid jre files, incorrect CLASSPATH, or insufficient resources. I dont have any knowledge on the initial installation. Any help would be appreciated... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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@sqrug.org Thu Jan 04 15:57:00 2007 Date: Thu, 4 Jan 2007 12:54:11 -0800 (PST) From: Arun Kumar Subject: Re: [sqr-users] Thinking about trying something - looking for ideas Again, The emphasis should be to fix the process rather than looking for or= phans.. If you use people soft delivered tools like component interface then you wo= n't have orphans. My response is strickly for Peoplesoft archietecture where there is no fore= ign keys enabled in the back. In a way almost all the tables are candidates to be messed up. so if you looking for orphans in everytable in every possible way is more c= umbersome as you are chasing your tail than looking at the processes and fi= xing them. I am not being arguementative just playing devils advacate. ----- Original Message ---- From: "Overcashier, Patricia" To: This list is for discussion about the SQR database reportinglanguage fr= om Hyperion Solutions. Sent: Thursday, January 4, 2007 1:42:58 PM Subject: RE: [sqr-users] Thinking about trying something - looking for ideas Only in the perfect world :) In my world we are always running batch processes underneath to add, update, and delete rows in cases where our users do not want to do mass data entry for hundreds or thousands of rows. -----Original Message----- From: sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org [mailto:sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org] On Behalf Of Arun Kumar Sent: Thursday, January 04, 2007 1:36 PM To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] Thinking about trying something - looking for ideas I am not sure about this. If all the data comes in to the PS database thru its front end then orphan rows are impossible to create in first place. Orphans row exists because of data imported thru back doors and most of the time during the initial conversion. Once you fixed those process you will never have orphans, right? Just my 2 cents. ----- Original Message ---- From: "Overcashier, Patricia" To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Sent: Thursday, January 4, 2007 1:15:33 PM Subject: RE: [sqr-users] Thinking about trying something - looking for ideas John, This sounds like it would be a very useful sqr. Could you please pass on any suggestions you may get. I'm interested in looking for orphans too and agree that it is easy to imagine and put together an sqr to do that, using dynamic sqls selecting from the tools tables, but also agree that it would take forever to run.=20=20 Thanks, Patti -----Original Message----- From: sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org [mailto:sqr-users-bounces+patricia.overcashier=3Daam.com@sqrug.org] On Behalf Of john.tucker@oracle.com Sent: Thursday, January 04, 2007 1:06 PM To: sqr-users@sqrug.org Subject: [sqr-users] Thinking about trying something - looking for ideas I have a rather different question today. I am thinking about writing an SQR that would look for orphan data anywhere in a PeopleSoft database, using PeopleTools tables to figure out what to look at rather than using a static list of tables. I have an idea or two about how to go about this... but I wondered if anyone out here had already written something similar and/or had some suggestions for things to do/avoid. For example, there will be a lot of dynamic SQL getting processed in this SQR. Does that usually stop indexes from being used? If so, is there any way I can force indexes to be used? Also, would it be better to say "select [whatever] from [child] where not exists ([whatever] in [parent])" or would it be better to write two select statements, pulling all rows from the child table and trying to match them with rows in the parent table in a separate select statement? Either way, I already know this thing is going to take forever to run. I don't want you fine folks to do my work for me, but I was pretty sure someone out there would have suggestions on how I can make this a better, faster tool than the way I would have written it without asking. -John T. _______________________________________________ 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!? Tired of spam? Yahoo! Mail has the best spam protection around=20 http://mail.yahoo.com=20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around=20 http://mail.yahoo.com=20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Thu Jan 04 17:00:56 2007 Date: Thu, 4 Jan 2007 13:58:12 -0800 (PST) From: Arun Kumar Subject: Re: [sqr-users] creating HTML reports Our version is 8.1 We have jre 1.4.2 installed on the server but the location is different. ..= \jvm\windows\1.4.2 Well. Can it work with 1.4.2 version? ----- Original Message ---- From: Franck Masson To: This list is for discussion about the SQR database reportinglanguage fr= om Hyperion Solutions. Sent: Thursday, January 4, 2007 3:49:07 PM Subject: RE: [sqr-users] creating HTML reports What is your SQR version ? you should not have to create the jre folder, itshould be created during the installation. it seem that you are missing all the JVM installation which should be jre1.1.7 or 1.18 or 1.2 depending your SQR version Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 21:10 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] creating HTML reports Thanks to franck and Don, I tried both suggestions and its still giving me the same error. I did add all the entries to my common section. My ini file looks like this now [Environment:Common] SQRDIR=3Dc:\sqrbin\windows BRIO_HOME=3Dc:\sqrbin\ SQR_USEJVM =3D TRUE SQR_DDO_JRE_PATH=3Dc:\sqrbin\jre SQR_DDO_JRE_CLASS=3Dc:\jar\server.jar;c:\jar\mail.jar;c:\jar\jcert.jar;c:\j= ar\ jnet.jar;c:\jar\jsse.jar;c:\jar\activation.jar;c:\jar\autolink.jar;c:\jar\cl asses12.zip;c:\jar\una2000.jar I did create a new folder (jre) under brio_home. Strangely when I turn sqr_usejvm to false. SQR runs without error but doesnt produce the output file either. What am I missing? Thanks again. arun ----- Original Message ---- From: Franck Masson To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Sent: Thursday, January 4, 2007 2:21:47 PM Subject: RE: [sqr-users] creating HTML reports Arun check in your sqr.ini these entries [Environment:Common] SQRDIR=3DD:\BPS8\SQR8\SQR\ODB\BINW BRIO_HOME=3DD:\BPS8\SQR8 and verify that you have a JRE folder under the BRIO_HOME folder Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 19:25 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: [sqr-users] creating HTML reports I did search the group page for answers. But didnt suceed. I want HTML ouput. Trying for the first time. The sqr is running on windows machine. its windows2003 server. added the following entry in sqr.ini [Environment:DDO] SQR_DDO_JRE_CLASS=3D\..\..\server.jar;\..\..\mail.jar;\..\..\jcert.jar;\..\= ..\ jnet.jar;\..\..\jsse.jar;\..\..\activation.jar;\..\..\autolink.jar;\..\..\cl asses12.zip;\..\..\una2000.jar But I am still getting the follwoing error message. (SQR 7715) Failed to start the Java Virtual Machine (JVM). Possible causes are: missing or invalid jre files, incorrect CLASSPATH, or insufficient resources. I dont have any knowledge on the initial installation. Any help would be appreciated... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around=20 http://mail.yahoo.com=20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Jan 05 04:34:59 2007 From: "Franck Masson" Subject: RE: [sqr-users] creating HTML reports Date: Fri, 5 Jan 2007 10:32:30 +0100 Is it SQR from Hyperion ? if yes unisntall and install again the SQR server. You should have a JRE folder with all the subfolders and files necessary . Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 22:58 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] creating HTML reports Our version is 8.1 We have jre 1.4.2 installed on the server but the location is different. ..\jvm\windows\1.4.2 Well. Can it work with 1.4.2 version? ----- Original Message ---- From: Franck Masson To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Sent: Thursday, January 4, 2007 3:49:07 PM Subject: RE: [sqr-users] creating HTML reports What is your SQR version ? you should not have to create the jre folder, itshould be created during the installation. it seem that you are missing all the JVM installation which should be jre1.1.7 or 1.18 or 1.2 depending your SQR version Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 21:10 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] creating HTML reports Thanks to franck and Don, I tried both suggestions and its still giving me the same error. I did add all the entries to my common section. My ini file looks like this now [Environment:Common] SQRDIR=c:\sqrbin\windows BRIO_HOME=c:\sqrbin\ SQR_USEJVM = TRUE SQR_DDO_JRE_PATH=c:\sqrbin\jre SQR_DDO_JRE_CLASS=c:\jar\server.jar;c:\jar\mail.jar;c:\jar\jcert.jar;c:\jar\ jnet.jar;c:\jar\jsse.jar;c:\jar\activation.jar;c:\jar\autolink.jar;c:\jar\cl asses12.zip;c:\jar\una2000.jar I did create a new folder (jre) under brio_home. Strangely when I turn sqr_usejvm to false. SQR runs without error but doesnt produce the output file either. What am I missing? Thanks again. arun ----- Original Message ---- From: Franck Masson To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Sent: Thursday, January 4, 2007 2:21:47 PM Subject: RE: [sqr-users] creating HTML reports Arun check in your sqr.ini these entries [Environment:Common] SQRDIR=D:\BPS8\SQR8\SQR\ODB\BINW BRIO_HOME=D:\BPS8\SQR8 and verify that you have a JRE folder under the BRIO_HOME folder Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 19:25 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: [sqr-users] creating HTML reports I did search the group page for answers. But didnt suceed. I want HTML ouput. Trying for the first time. The sqr is running on windows machine. its windows2003 server. added the following entry in sqr.ini [Environment:DDO] SQR_DDO_JRE_CLASS=\..\..\server.jar;\..\..\mail.jar;\..\..\jcert.jar;\..\..\ jnet.jar;\..\..\jsse.jar;\..\..\activation.jar;\..\..\autolink.jar;\..\..\cl asses12.zip;\..\..\una2000.jar But I am still getting the follwoing error message. (SQR 7715) Failed to start the Java Virtual Machine (JVM). Possible causes are: missing or invalid jre files, incorrect CLASSPATH, or insufficient resources. I dont have any knowledge on the initial installation. Any help would be appreciated... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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@sqrug.org Fri Jan 05 09:32:58 2007 Date: Fri, 05 Jan 2007 09:29:30 -0500 From: "Jamie Harris" Subject: Re: [sqr-users] Thinking about trying something - looking for ideas Arun, That's not entirely true. If Peoplesoft does everything correctly, then yes there should never be new orphans. But Peoplesoft is a very big, complex system, and even the best testing on their end doesn't mean there aren't bugs. There are situations where orphans do happen. For example, if you term-activate a student and their academic standing gets calculated (which happens immediately if you have that setup), then you delete the term-activation row (using the Peoplesoft page, not SQL) their academic standing row for that same term is left in place. This causes the "no matching buffer found for level" error when you pull up the Term History page for that student. That's because a row exists in PS_ACAD_STDNG but not in the parent PS_STDNT_CAR_TERM. This is Peoplesoft-delivered functionality here, not some back-door data import. This kind of bug is hard to catch in testing because it's so rare that anyone deletes a row in that table that people don't always think to test what happens when you do. Note: this may be fixed by now, I haven't checked in a while and like I said, it's rare that people delete rows from that table. Bugs like that do happen, and you want to know when a new one has sprung up before it's a lot of rows to deal with. You never know what's going to break in that latest patch bundle and users don't always take the time to test as thoroughly as they should before such patches are moved into production. As for an orphan-finding program I've found such a thing much easier in Perl using the DBI modules. Perl is great with the dynamic SQL kinds of things (like select * from table, etc.) - my Perl is about 100 lines of actual code not counting the lines that handle the user input and/or reading from command line parameters (I don't run it inside of Peoplesoft). But in SQR it's certainly do-able - especially using the technique others have mentioned where one SQR writes code into an .SQC that a second SQR runs (so you avoid all the dynamic SQL). You can queue those together into a Peoplesoft job and won't need Perl at all. The hard part about the Peoplesoft database (at least from what I've seen - on SQL server) is that it doesn't set up any foreign key constraints in the tables, it lets the user interface take care of all that based on the pages. So the only way to really know what table is a child of what other table is to select from PSRECDEFN and use the parent table listed there. Then you can ask the database server what the keys are in the two tables, figure out which keys they have in common, etc. Sometimes the parent table listed in PSRECDEFN is wrong, however, so an orphan finder done this way will return some false positives. But you know it's a false positive when it tells you that EVERY row in that table is an orphan. The peoplesoft pages are what really control the relationships, if table A is scroll level 1 and table B is scroll level 2 that's what makes it treat table A as table B's parent, so the "parent record" field in PSRECDEFN is meant as merely a convenience for developers as far as I can tell (which is why sometimes it's wrong and they haven't fixed it). I'm not bashing Peoplesoft here, I'm just saying that in a system with over 16,000 tables it's hard to keep out the bugs sometimes, and that includes creation of orphans. It's nice to have a program that says you have no orphans, and then the next week it tells you that you still have none. That's much better than having a line down the hallway and have students who you can't help because somewhere in their data is an orphan and the cashier (who doesn't know what "no matching buffer found for level" even means yet) has to tell them to come back later. ----------------------------------------------------- James Harris Enterprise Application Analyst/Programmer Information Technology Division Frederick Community College ----------------------------------------------------- >>> Arun Kumar 1/4/2007 1:35 PM >>> I am not sure about this. If all the data comes in to the PS database thru its front end then orphan rows are impossible to create in first place. Orphans row exists because of data imported thru back doors and most of the time during the initial conversion. Once you fixed those process you will never have orphans, right? Just my 2 cents. ----- Original Message ---- From: "Overcashier, Patricia" To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Sent: Thursday, January 4, 2007 1:15:33 PM Subject: RE: [sqr-users] Thinking about trying something - looking for ideas John, This sounds like it would be a very useful sqr. Could you please pass on any suggestions you may get. I'm interested in looking for orphans too and agree that it is easy to imagine and put together an sqr to do that, using dynamic sqls selecting from the tools tables, but also agree that it would take forever to run. Thanks, Patti -----Original Message----- From: sqr-users-bounces+patricia.overcashier=aam.com@sqrug.org [mailto:sqr-users-bounces+patricia.overcashier=aam.com@sqrug.org] On Behalf Of john.tucker@oracle.com Sent: Thursday, January 04, 2007 1:06 PM To: sqr-users@sqrug.org Subject: [sqr-users] Thinking about trying something - looking for ideas I have a rather different question today. I am thinking about writing an SQR that would look for orphan data anywhere in a PeopleSoft database, using PeopleTools tables to figure out what to look at rather than using a static list of tables. I have an idea or two about how to go about this... but I wondered if anyone out here had already written something similar and/or had some suggestions for things to do/avoid. For example, there will be a lot of dynamic SQL getting processed in this SQR. Does that usually stop indexes from being used? If so, is there any way I can force indexes to be used? Also, would it be better to say "select [whatever] from [child] where not exists ([whatever] in [parent])" or would it be better to write two select statements, pulling all rows from the child table and trying to match them with rows in the parent table in a separate select statement? Either way, I already know this thing is going to take forever to run. I don't want you fine folks to do my work for me, but I was pretty sure someone out there would have suggestions on how I can make this a better, faster tool than the way I would have written it without asking. -John T. _______________________________________________ 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!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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@sqrug.org Fri Jan 05 11:05:29 2007 From: "Elvis Pressly" Subject: RE: [sqr-users] Thinking about trying something - looking for ideas Date: Fri, 05 Jan 2007 16:02:52 +0000 John, I have done this with one of my client long time ago (I think it was in version 7.5). I'm telling you it can be very ugly to find orphan records. The way I did it is first consider which fields are the driving mechanism to establish what you will consider orphan record. You have to understand that different application has different mechanism. For HR and related application you will use EMPLID and PS_JOB, for AP, AR, INVENTORY or Financial it's different. For HR you create a temporay table that contains unique EMPLID from PS_JOB. This table should only have one field or column (EMPLID). It's up to you what type of employee to be inserted into the temp table. Use PSRECFLDFN table (select only tables that has field EMPLID) as your reference. depending on which platform, use that table name or schema name from the PSRECFLDFN. In SQR you can then use the table name as a dynamic table name. The rest is just comparing EMPLID vs the temp table. Hope this help. _________________________________________________________________ >From photos to predictions, The MSN Entertainment Guide to Golden Globes has it all. http://tv.msn.com/tv/globes2007/?icid=nctagline1 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Jan 05 13:16:52 2007 Date: Fri, 5 Jan 2007 18:14:09 +0000 (GMT) From: john.tucker@oracle.com Subject: [sqr-users] RE: Thinking about trying something - looking for ideas Thanks for the suggestions, everyone. I am working with data from a rather large (in number of tables) initial conversion, trying to make sure no one forgot a table, but I definitely agree with what everyone said about the perfect world. It would be great, but it ain't gonna happen. One of the suggestions mentioned building an array to go down the children - I actually have an SQR already that takes an input of a tablename and then tells you the parents all the way up the line and all of the children all the way down the line. I thought about going with an array for that one, but ended up just writing several copies of the same select statement, going down to the next each time a child is found. I believe that the deepest I was able to find was around 13 levels, so that's how many copies I included. There are probably more elegant solutions, programming-wise, but it works well, so I haven't messed with it. -John T. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Jan 05 14:56:43 2007 Date: Fri, 5 Jan 2007 19:53:51 +0000 (GMT) From: ashwin reddy Subject: [sqr-users] problem connecting to different database we are runnin an sqr and it has to access data from two different oracle databases from within the sqr. The default database is HRINT here is a snippet of the code i am using Begin-select a.emplid ..... from IT_PAY_LIST@prod a end-select if i run the select statement from oracle it connects to the prod database without any problem... do i need to initialize somethin at the begining of the sqr before using the database link i created in oracle Send instant messages to your online friends http://uk.messenger.yahoo.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Fri Jan 05 15:00:19 2007 Date: Fri, 05 Jan 2007 14:57:17 -0500 From: "Larry Roux" Subject: Re: [sqr-users] problem connecting to different database We are able to use DB links from SQR with no problem. Are you sure the ID you are using in SQL is the same you are using from SQR? What error are you seeing? Larry Roux Information Systems Syracuse University lroux@syr.edu >>> ashwin443@yahoo.co.uk 1/5/2007 2:53:51 PM >>> we are runnin an sqr and it has to access data from two different oracle databases from within the sqr. The default database is HRINT here is a snippet of the code i am using Begin-select a.emplid ..... from IT_PAY_LIST@prod a end-select if i run the select statement from oracle it connects to the prod database without any problem... do i need to initialize somethin at the begining of the sqr before using the database link i created in oracle Send instant messages to your online friends http://uk.messenger.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@sqrug.org Fri Jan 05 18:17:33 2007 Date: Fri, 05 Jan 2007 15:14:21 -0800 From: "Howard Thompson" Subject: [sqr-users] PL-SQL Oracle character variable assignment problem I have and SQR program that executes some Oracle PL-SQL that assigns values= to SQR variables. The numeric variables work fine; the string variables do= n't seem to "bind". =20 Here is a distilled version that shows the error: BEGIN-PROGRAM BEGIN-SQL BEGIN $SqrStr :=3D 'Hello, world';; #SqrNum :=3D 100;; END;; END-SQL DISPLAY '$SqrStr: ' NOLINE DISPLAY $SqrStr=20 DISPLAY '#SqrNum: ' NOLINE DISPLAY #SqrNum=20 END-PROGRAM When run, I get this: Hyperion SQR Server - 8.5.0.0.0.566 Copyright (c) 1994-2006 Hyperion Solutions Corporation. All Rights Reserve= d. $SqrStr:=20=20 #SqrNum: 100.000000 Version info SQR: Hyperion SQR Server - 8.5.0.0.0.566 SQL*Plus SQL*Plus: Release 9.2.0.7.0 - Production on Fri Jan 5 15:13:18 2007 Oracle: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production =20 Thanks in advance! -- Howard Thompson Senior IS Project Lead Oregon Health & Science University thompsoh@ohsu.edu 503-494-5936 "A thing of beauty is a joy forever" -- John Keats _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Jan 08 04:44:52 2007 Date: Mon, 8 Jan 2007 15:07:08 +0530 From: Sanjay Sambhe Subject: RE: [sqr-users] RE: SQR problem thanks all, for your valuable information -----Original Message----- From: sqr-users-bounces+ssambhe=cisco.com@sqrug.org [mailto:sqr-users-bounces+ssambhe=cisco.com@sqrug.org] On Behalf Of White . Denise Sent: Tuesday, January 02, 2007 7:58 PM To: sqr-users@sqrug.org Subject: [sqr-users] RE: SQR problem I believe that this is necessary because the program would not compile with a variable table name in the BEGIN-SELECT (the variable value is not set until after the program has begun execution). Therefore, it is necessary to 'trick' the compiler by providing a valid table name to be used at compile time. Once the variable value has been set, the new table name will be in effect. Denise M. White EBS Programmer/Analyst III Dynamics Research Corporation ------------------------------ Message: 2 Date: Tue, 2 Jan 2007 03:49:40 -0600 From: "Jim Womeldorf" Subject: RE: [sqr-users] SQR problem To: "This list is for discussion about the SQR database reporting language from Hyperion Solutions." Message-ID: <42E50BB7EAB3764E8654A54710243EC0071F72AB@excmb01.backup> Content-Type: text/plain; charset="US-ASCII" This is a new one to me. I have not found any documentation about this but running the following program snippet sheds some light on the subject: Begin-Program show 'Here I am' let $substitute = 'xlattable' !Example 1 !let $substitute = 'psrecfield' !Example 2 do demo show 'done' End-Program begin-procedure demo begin-select count(fieldname) &c show 'count ' &c from [xlattable : $substitute] end-select end-procedure demo ----------------- If the program is run as shown then the count is 10965 which is indeed the count of our records in xlattable. If the program is run as with the 'Example 2' line active then the count is 198812 which is correct for psrecfield. Apparently the $ value after the colon is substituted for the table name shown, which, by the way, must be a valid table name. ----------------- The code with the colon in it is, interestingly enough, compiled at compile-time using the table name to the left of the colon and then is compiled again at run-time using the value of $substitute. The only purpose of the compile-time compile that comes to mind is that it might provide an 'early warning' if there is something fundamentally wrong with the statement. Otherwise it is necessary to wait until run-time to find that out. Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org] On Behalf Of Sanjay Sambhe Sent: Monday, January 01, 2007 11:25 PM To: 'This list is for discussion about the SQR database reporting language from Hyperion Solutions.' Subject: [sqr-users] SQR problem Hi all, while debugging, I have came accross one statement in SQR i.e [PS_CS_EMPL_EVT_T01 X : $e.cs_event_view_name]. Here both are the record names. $e.cs_event_view_name is view in PS which is dynamically selected in another procedure. Can anybody please explain what is the logic behind this? BEGIN-SELECT X.EMPLID &x.emplid X.CS_EVENT_TYPE &x.cs_event_type TO_CHAR(trunc(X.CS_EVENT_ENTRY_DT),'YYYY-MM-DD') &x.cs_event_entry_dt TO_CHAR(trunc(X.CS_EVENT_EFFDT),'YYYY-MM-DD') &x.cs_event_effdt X.CS_PRIORITY_FLAG &x.cs_priority_flag X.CS_EVENT_PARAM &x.cs_event_param show 'Processing Employee: ' &x.emplid ', Event: ' &x.cs_event_type ', Effdt: ' &x.cs_event_effdt show '&x.cs_priority_flag ' &x.cs_priority_flag ',&x.cs_event_param ' &x.cs_event_param do Insert-Employee-Event FROM [PS_CS_EMPL_EVT_T01 X : $e.cs_event_view_name] WHERE not exists (select * from ps_cs_wr_tbl where emplid = x.emplid) and ( !** entire line for Transition project ** (X.CS_EVENT_EFFDT between trunc(SYSDATE)-{RECENCY_DAYS} and trunc(SYSDATE)+#future_days) OR (X.CS_EVENT_ENTRY_DT >= trunc(SYSDATE)-{RECENCY_DAYS} AND X.CS_EVENT_EFFDT <= trunc(SYSDATE)+#future_days)) !** ) For Transition project ** END-SELECT Thanks and Regards, ss _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Jan 08 13:51:21 2007 Date: Mon, 8 Jan 2007 10:45:55 -0800 (PST) From: Arun Kumar Subject: Re: [sqr-users] creating HTML reports I am coming to conclusion that Our SQR is either stripped down version or d= oesnt have all the options installed. Either way I dont have any control over it. My report is very simple HTML pages so creating HTML pages using write comm= and instead. ( Like creating excel spread sheet using SYLK format.) Thanks for all your help. ----- Original Message ---- From: Franck Masson To: This list is for discussion about the SQR database reportinglanguage fr= om Hyperion Solutions. Sent: Friday, January 5, 2007 4:32:30 AM Subject: RE: [sqr-users] creating HTML reports Is it SQR from Hyperion ? if yes unisntall and install again the SQR server. You should have a JRE folder with all the subfolders and files necessary . Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 22:58 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] creating HTML reports Our version is 8.1 We have jre 1.4.2 installed on the server but the location is different. ..\jvm\windows\1.4.2 Well. Can it work with 1.4.2 version? ----- Original Message ---- From: Franck Masson To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Sent: Thursday, January 4, 2007 3:49:07 PM Subject: RE: [sqr-users] creating HTML reports What is your SQR version ? you should not have to create the jre folder, itshould be created during the installation. it seem that you are missing all the JVM installation which should be jre1.1.7 or 1.18 or 1.2 depending your SQR version Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 21:10 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: Re: [sqr-users] creating HTML reports Thanks to franck and Don, I tried both suggestions and its still giving me the same error. I did add all the entries to my common section. My ini file looks like this now [Environment:Common] SQRDIR=3Dc:\sqrbin\windows BRIO_HOME=3Dc:\sqrbin\ SQR_USEJVM =3D TRUE SQR_DDO_JRE_PATH=3Dc:\sqrbin\jre SQR_DDO_JRE_CLASS=3Dc:\jar\server.jar;c:\jar\mail.jar;c:\jar\jcert.jar;c:\j= ar\ jnet.jar;c:\jar\jsse.jar;c:\jar\activation.jar;c:\jar\autolink.jar;c:\jar\cl asses12.zip;c:\jar\una2000.jar I did create a new folder (jre) under brio_home. Strangely when I turn sqr_usejvm to false. SQR runs without error but doesnt produce the output file either. What am I missing? Thanks again. arun ----- Original Message ---- From: Franck Masson To: This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions. Sent: Thursday, January 4, 2007 2:21:47 PM Subject: RE: [sqr-users] creating HTML reports Arun check in your sqr.ini these entries [Environment:Common] SQRDIR=3DD:\BPS8\SQR8\SQR\ODB\BINW BRIO_HOME=3DD:\BPS8\SQR8 and verify that you have a JRE folder under the BRIO_HOME folder Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=3Dlaposte.net@sqrug.org]On Behalf Of Arun Kumar Sent: jeudi 4 janvier 2007 19:25 To: This list is for discussion about the SQR database reporting languagefrom Hyperion Solutions. Subject: [sqr-users] creating HTML reports I did search the group page for answers. But didnt suceed. I want HTML ouput. Trying for the first time. The sqr is running on windows machine. its windows2003 server. added the following entry in sqr.ini [Environment:DDO] SQR_DDO_JRE_CLASS=3D\..\..\server.jar;\..\..\mail.jar;\..\..\jcert.jar;\..\= ..\ jnet.jar;\..\..\jsse.jar;\..\..\activation.jar;\..\..\autolink.jar;\..\..\cl asses12.zip;\..\..\una2000.jar But I am still getting the follwoing error message. (SQR 7715) Failed to start the Java Virtual Machine (JVM). Possible causes are: missing or invalid jre files, incorrect CLASSPATH, or insufficient resources. I dont have any knowledge on the initial installation. Any help would be appreciated... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.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 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around=20 http://mail.yahoo.com=20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Jan 08 17:23:26 2007 From: tam kag Date: Mon, 8 Jan 2007 22:20:44 +0000 Subject: [sqr-users] RANK function Hello, I'm in the process of writing a top 10 report. I want to take advantage of = the DB2 function RANK Here is the relevant code in the begin-select block: begin-select...store.company_number &company_numbersku8.sku_6_digit &sku6_d= igit (,10)sum(fst.current_send_store_qty) &transfered_qty (,20)sum(fst.send= _sku_retail_price_amt_lcy * fst.current_send_store_qty) &transfered_price r= ank() over (partition by store.company_number,sku8.sku_6_digit order by &tr= ansfered_price) &temp (,40)...end-select I know that putting a SQR column variable is not correct but I want to rank= by that metric. Also, just to test the rank function I have replaced the c= olumn variable with fst.send_sku_retail_price_amt_lcy but that did not work= either. Does any one know how to use the RANK function in SQR? please let me know w= hat I'm doing wrong here. =20 Thank you _________________________________________________________________ Fixing up the home? Live Search can help. http://imagine-windowslive.com/search/kits/default.aspx?kit=3Dimprove&local= e=3Den-US&source=3Dwlmemailtaglinenov06 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Jan 08 17:54:09 2007 From: tam kag Subject: RE: [sqr-users] RANK function Date: Mon, 8 Jan 2007 22:51:34 +0000 for some reason the line feed and carriage return was removed from the code= , I will try again =20 begin-select -- -- -- store.company_number &company_numbersku8 numbersku8.sku_6_digit &sku6_digit (,10) sum(fst.current_send_store_qty) &transfered_qty (,20) sum(fst.send_sku_retail_price_amt_lcy * fst.current_send_store_qty) &transf= ered_price=20 rank() over (partition by store.company_number,sku8.sku_6_digit order by &t= ransfered_price) &temp (,40) -- -- -- end-select > From: kaghdot@hotmail.com> To: sqr-users@sqrug.org> Date: Mon, 8 Jan 2007= 22:20:44 +0000> Subject: [sqr-users] RANK function> > Hello,> I'm in the p= rocess of writing a top 10 report. I want to take advantage of the DB2 func= tion RANK> Here is the relevant code in the begin-select block:> begin-sele= ct...store.company_number &company_numbersku8.sku_6_digit &sku6_digit (,10)= sum(fst.current_send_store_qty) &transfered_qty (,20)sum(fst.send_sku_retai= l_price_amt_lcy * fst.current_send_store_qty) &transfered_price rank() over= (partition by store.company_number,sku8.sku_6_digit order by &transfered_p= rice) &temp (,40)...end-select> I know that putting a SQR column variable i= s not correct but I want to rank by that metric. Also, just to test the ran= k function I have replaced the column variable with fst.send_sku_retail_pri= ce_amt_lcy but that did not work either.> Does any one know how to use the = RANK function in SQR? please let me know what I'm doing wrong here.> > Than= k you> _________________________________________________________________> F= ixing up the home? Live Search can help.> http://imagine-windowslive.com/se= arch/kits/default.aspx?kit=3Dimprove&locale=3Den-US&source=3Dwlmemailtaglin= enov06> > _______________________________________________> sqr-users mailin= g list> sqr-users@sqrug.org> http://www.sqrug.org/mailman/listinfo/sqr-users _________________________________________________________________ Fixing up the home? Live Search can help. http://imagine-windowslive.com/search/kits/default.aspx?kit=3Dimprove&local= e=3Den-US&source=3Dwlmemailtaglinenov06 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Mon Jan 08 20:29:21 2007 From: "Paul Hoyte" Subject: RE: [sqr-users] RANK function Date: Mon, 8 Jan 2007 20:27:03 -0500 Tam: select empnum &empnum Dept &dept Salary &salary rank() over (partition by dept order by salary desc nulls last) as rank &rank1 dense_rank() over (partition by dept order by salary desc nulls last) as denserank &rank2 row_number() over (partition by dept order by salary desc nulls last)as rownumber &rank3 from emptab Don't forget to use the "as fieldname" then the SQR &column_name Paul Hoyte (248) 752-6970 pa_hoyte@si-proservices.com -----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 tam kag Sent: Monday, January 08, 2007 5:52 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] RANK function for some reason the line feed and carriage return was removed from the code, I will try again begin-select -- -- -- store.company_number &company_numbersku8 numbersku8.sku_6_digit &sku6_digit (,10) sum(fst.current_send_store_qty) &transfered_qty (,20) sum(fst.send_sku_retail_price_amt_lcy * fst.current_send_store_qty) &transfered_price rank() over (partition by store.company_number,sku8.sku_6_digit order by &transfered_price) &temp (,40) -- -- -- end-select > From: kaghdot@hotmail.com> To: sqr-users@sqrug.org> Date: Mon, 8 Jan 2007 22:20:44 +0000> Subject: [sqr-users] RANK function> > Hello,> I'm in the process of writing a top 10 report. I want to take advantage of the DB2 function RANK> Here is the relevant code in the begin-select block:> begin-select...store.company_number &company_numbersku8.sku_6_digit &sku6_digit (,10)sum(fst.current_send_store_qty) &transfered_qty (,20)sum(fst.send_sku_retail_price_amt_lcy * fst.current_send_store_qty) &transfered_price rank() over (partition by store.company_number,sku8.sku_6_digit order by &transfered_price) &temp (,40)...end-select> I know that putting a SQR column variable is not correct but I want to rank by that metric. Also, just to test the rank function I have replaced the column variable with fst.send_sku_retail_price_amt_lcy but that did not work either.> Does any one know how to use the RANK function in SQR? please let me know what I'm doing wrong here.> > Thank you> _________________________________________________________________> Fixing up the home? Live Search can help.> http://imagine-windowslive.com/search/kits/default.aspx?kit=improve&locale=e n-US&source=wlmemailtaglinenov06> > _______________________________________________> sqr-users mailing list> sqr-users@sqrug.org> http://www.sqrug.org/mailman/listinfo/sqr-users _________________________________________________________________ Fixing up the home? Live Search can help. http://imagine-windowslive.com/search/kits/default.aspx?kit=improve&locale=e n-US&source=wlmemailtaglinenov06 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Tue Jan 09 12:21:29 2007 From: tam kag Subject: RE: [sqr-users] RANK function Date: Tue, 9 Jan 2007 17:18:35 +0000 Thanks Paul, =20 unfortunately that didn't work; I'm getting the following error: (SQR 1305) CMPSQL: Unknown data type in database: 492. =20 The program runs without issues when I comment the line with the rank funct= ion, to make sure its not the rest of my program causing the error. =20 =20 > From: pi_hoyte@si-proservices.com> To: sqr-users@sqrug.org> Subject: RE: = [sqr-users] RANK function> Date: Mon, 8 Jan 2007 20:27:03 -0500> > Tam:> > = select empnum &empnum> Dept &dept> Salary &salary> rank() over (partition b= y dept order by salary desc nulls last) as rank> &rank1> dense_rank() over = (partition by dept order by salary desc nulls last) as> denserank &rank2> r= ow_number() over (partition by dept order by salary desc nulls last)as> row= number &rank3> from emptab> > Don't forget to use the "as fieldname" then t= he SQR &column_name> > Paul Hoyte> (248) 752-6970> pa_hoyte@si-proservices.= com> > -----Original Message-----> From: sqr-users-bounces+pi_hoyte=3Dsi-pr= oservices.com@sqrug.org> [mailto:sqr-users-bounces+pi_hoyte=3Dsi-proservice= s.com@sqrug.org] On Behalf> Of tam kag> Sent: Monday, January 08, 2007 5:52= PM> To: sqr-users@sqrug.org> Subject: RE: [sqr-users] RANK function> > for= some reason the line feed and carriage return was removed from the code,> = I will try again> > begin-select> --> --> --> store.company_number &company= _numbersku8 numbersku8.sku_6_digit &sku6_digit> (,10)> sum(fst.current_send= _store_qty) &transfered_qty (,20)> sum(fst.send_sku_retail_price_amt_lcy * = fst.current_send_store_qty)> &transfered_price> rank() over (partition by s= tore.company_number,sku8.sku_6_digit order by> &transfered_price) &temp (,4= 0)> --> --> --> end-select> > > > > From: kaghdot@hotmail.com> To: sqr-user= s@sqrug.org> Date: Mon, 8 Jan 2007> 22:20:44 +0000> Subject: [sqr-users] RA= NK function> > Hello,> I'm in the> process of writing a top 10 report. I wa= nt to take advantage of the DB2> function RANK> Here is the relevant code i= n the begin-select block:>> begin-select...store.company_number &company_nu= mbersku8.sku_6_digit> &sku6_digit (,10)sum(fst.current_send_store_qty) &tra= nsfered_qty> (,20)sum(fst.send_sku_retail_price_amt_lcy * fst.current_send_= store_qty)> &transfered_price rank() over (partition by> store.company_numb= er,sku8.sku_6_digit order by &transfered_price) &temp> (,40)...end-select> = I know that putting a SQR column variable is not correct> but I want to ran= k by that metric. Also, just to test the rank function I> have replaced the= column variable with fst.send_sku_retail_price_amt_lcy but> that did not w= ork either.> Does any one know how to use the RANK function in> SQR? please= let me know what I'm doing wrong here.> > Thank you>> ____________________= _____________________________________________> Fixing up> the home? Live Se= arch can help.>> http://imagine-windowslive.com/search/kits/default.aspx?ki= t=3Dimprove&locale=3De> n-US&source=3Dwlmemailtaglinenov06> >> ____________= ___________________________________> sqr-users mailing list>> sqr-users@sqr= ug.org> http://www.sqrug.org/mailman/listinfo/sqr-users> __________________= _______________________________________________> Fixing up the home? Live S= earch can help.> http://imagine-windowslive.com/search/kits/default.aspx?ki= t=3Dimprove&locale=3De> n-US&source=3Dwlmemailtaglinenov06> > _____________= __________________________________> sqr-users mailing list> sqr-users@sqrug= .org> http://www.sqrug.org/mailman/listinfo/sqr-users> > > ________________= _______________________________> sqr-users mailing list> sqr-users@sqrug.or= g> http://www.sqrug.org/mailman/listinfo/sqr-users _________________________________________________________________ Type your favorite song.=A0 Get a customized station.=A0 Try MSN Radio powe= red by Pandora. http://radio.msn.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Jan 10 01:34:12 2007 Date: Tue, 9 Jan 2007 22:31:22 -0800 (PST) From: mimi bekele Subject: [sqr-users] (no subject) Does anyone know if reading RTF file allowed in SQR V. 6.0? =20 ___________________________________________________________________________= _________ Have a burning question?=20=20 Go to www.Answers.yahoo.com and get answers from real people who know. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Jan 10 07:01:26 2007 From: "the dragon" Subject: RE: [sqr-users] (no subject) Date: Wed, 10 Jan 2007 05:57:57 -0600 You can read any type of file, I believe, but whether the data will be in a type of input to be usable, that's the real question. Or more bluntly, you can, but you don't want to. this is what the inside of an rtf file looks like in a text editor... {\rtf1\ansi\ansicpg1252\uc1 \deff0\deflang1033\deflangfe1033{\fonttbl{\f0\froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f6\fmodern\fcharset0\fprq1{\*\panose 00000000000000000000}Courier;} {\f45\fswiss\fcharset0\fprq2{\*\panose 020b0a04020102020204}Arial Black;}{\f69\froman\fcharset238\fprq2 Times New Roman CE;}{\f70\froman\fcharset204\fprq2 Times New Roman Cyr;}{\f72\froman\fcharset161\fprq2 Times New Roman Greek;} {\f73\froman\fcharset162\fprq2 Times New Roman Tur;}{\f74\froman\fcharset177\fprq2 Times New Roman (Hebrew);}{\f75\froman\fcharset178\fprq2 Times New Roman (Arabic);}{\f76\froman\fcharset186\fprq2 Times New Roman Baltic;} {\f429\fswiss\fcharset238\fprq2 Arial Black CE;}{\f430\fswiss\fcharset204\fprq2 Arial Black Cyr;}{\f432\fswiss\fcharset161\fprq2 Arial Black Greek;}{\f433\fswiss\fcharset162\fprq2 Arial Black Tur;}{\f436\fswiss\fcharset186\fprq2 Arial Black Baltic;}} {\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0; \red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\ql \li0\ri0\widctlpar\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \fs20\lang1033\langfe1033\cgrid\langnp1033\langfenp1033 \snext0 Normal;}{\*\cs10 \additive Default Paragraph Font;}}{\info{\title test}{\author Clark 'the dragon' Willis}{\operator Clark 'the dragon' Willis}{\creatim\yr2007\mo1\dy10\hr5\min49} {\revtim\yr2007\mo1\dy10\hr5\min50}{\version1}{\edmins1}{\nofpages1}{\nofwords0}{\nofchars0}{\*\company Dragon Enterprises}{\nofcharsws0}{\vern8269}} \widowctrl\ftnbj\aenddoc\noxlattoyen\expshrtn\noultrlspc\dntblnsbdb\nospaceforul\formshade\horzdoc\dghspace180\dgvspace180\dghorigin1701\dgvorigin1984\dghshow0\dgvshow0\jexpand\viewkind1\viewscale100\pgbrdrhead\pgbrdrfoot\nolnhtadjtbl \fet0\sectd \linex0\endnhere\sectdefaultcl {\*\pnseclvl1\pnucrm\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl2\pnucltr\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl3\pndec\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl4 \pnlcltr\pnstart1\pnindent720\pnhang{\pntxta )}}{\*\pnseclvl5\pndec\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl6\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl7\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (} {\pntxta )}}{\*\pnseclvl8\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl9\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}\pard\plain \ql \li0\ri0\widctlpar\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \fs20\lang1033\langfe1033\cgrid\langnp1033\langfenp1033 {test\tab test \par }{\f6 test \par \par }{\f45 test \par \par }{\f45\fs36 test \par }\pard \ql \li0\ri0\widctlpar\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 {\f45\fs36 \par }} As you vcan see, there is a lot of formatting crap, because rtf stands for rich text format. I believe it was designed to allow word documents to be imported and exported between word editors before companies started providing APIs. (http://en.wikipedia.org/wiki/Rtf) (http://en.wikipedia.org/wiki/API) The words you're looking for are "test". If you want to put in coding to wade through all the extraneous stuff, have fun, but it seems like a waste of time to me - have the file created as .txt, or .csv or something that would be considered a "flat" file. peace, clark 'the dragon' willis PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away. I work to live; I don't live to work. "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." -- Carl Sandburg (1878 - 1967) It is impossible to defeat an ignorant man in argument. -- William G. McAdoo Religion is regarded by the common people as true, by the wise as false, and by the rulers as useful. -- Seneca ----Original Message Follows---- Does anyone know if reading RTF file allowed in SQR V. 6.0? _________________________________________________________________ Get FREE Web site and company branded e-mail from Microsoft Office Live http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Jan 10 13:18:43 2007 From: "Melissa Teator" Date: Wed, 10 Jan 2007 13:15:41 -0500 Subject: [sqr-users] SQR Help I am new to SQR and am trying to write a program that looks at tables=20 on 2 different schemas. Then using a column name determines if any=20 rows are duplicates. If it is a duplicate then deletes the row in one=20 of the schemas. Any idea on the best way to approach this? I will be=20 reading in table names and column names from a file. Any help is=20 appreciated! Thanks! -M=20 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Jan 10 13:48:44 2007 From: "the dragon" Subject: RE: [sqr-users] SQR Help Date: Wed, 10 Jan 2007 12:46:06 -0600 do a search on the archives at sqrug.com. Other than that, have your DBA create a database link between the databases and reference it in the code. peace, clark 'the dragon' willis PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away. I work to live; I don't live to work. "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." -- Carl Sandburg (1878 - 1967) It is impossible to defeat an ignorant man in argument. -- William G. McAdoo Religion is regarded by the common people as true, by the wise as false, and by the rulers as useful. -- Seneca ----Original Message Follows---- I am new to SQR and am trying to write a program that looks at tables on 2 different schemas. Then using a column name determines if any rows are duplicates. If it is a duplicate then deletes the row in one of the schemas. Any idea on the best way to approach this? I will be reading in table names and column names from a file. Any help is appreciated! Thanks! -M _________________________________________________________________ Your Hotmail address already works to sign into Windows Live Messenger! Get it now http://clk.atdmt.com/MSN/go/msnnkwme0020000001msn/direct/01/?href=http://get.live.com/messenger/overview _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Jan 10 15:03:15 2007 From: tam kag Subject: RE: [sqr-users] RANK function Date: Wed, 10 Jan 2007 20:00:35 +0000 Well.... I figured it out =20 all it needs is to wrap the RANK function with INTEGER or CHAR function. =20 =20 From: kaghdot@hotmail.comTo: sqr-users@sqrug.orgSubject: RE: [sqr-users] RA= NK functionDate: Tue, 9 Jan 2007 17:18:35 +0000 Thanks Paul, unfortunately that didn't work; I'm getting the following erro= r:(SQR 1305) CMPSQL: Unknown data type in database: 492. The program runs w= ithout issues when I comment the line with the rank function, to make sure = its not the rest of my program causing the error.=20=20 > From: pi_hoyte@si-proservices.com> To: sqr-users@sqrug.org> Subject: RE: = [sqr-users] RANK function> Date: Mon, 8 Jan 2007 20:27:03 -0500> > Tam:> > = select empnum &empnum> Dept &dept> Salary &salary> rank() over (partition b= y dept order by salary desc nulls last) as rank> &rank1> dense_rank() over = (partition by dept order by salary desc nulls last) as> denserank &rank2> r= ow_number() over (partition by dept order by salary desc nulls last)as> row= number &rank3> from emptab> > Don't forget to use the "as fieldname" then t= he SQR &column_name> > Paul Hoyte> (248) 752-6970> pa_hoyte@si-proservices.= com> > -----Original Message-----> From: sqr-users-bounces+pi_hoyte=3Dsi-pr= oservices.com@sqrug.org> [mailto:sqr-users-bounces+pi_hoyte=3Dsi-proservice= s.com@sqrug.org] On Behalf> Of tam kag> Sent: Monday, January 08, 2007 5:52= PM> To: sqr-users@sqrug.org> Subject: RE: [sqr-users] RANK function> > for= some reason the line feed and carriage return was removed from the code,> = I will try again> > begin-select> --> --> --> store.company_number &company= _numbersku8 numbersku8.sku_6_digit &sku6_digit> (,10)> sum(fst.current_send= _store_qty) &transfered_qty (,20)> sum(fst.send_sku_retail_price_amt_lcy * = fst.current_send_store_qty)> &transfered_price> rank() over (partition by s= tore.company_number,sku8.sku_6_digit order by> &transfered_price) &temp (,4= 0)> --> --> --> end-select> > > > > From: kaghdot@hotmail.com> To: sqr-user= s@sqrug.org> Date: Mon, 8 Jan 2007> 22:20:44 +0000> Subject: [sqr-users] RA= NK function> > Hello,> I'm in the> process of writing a top 10 report. I wa= nt to take advantage of the DB2> function RANK> Here is the relevant code i= n the begin-select block:>> begin-select...store.company_number &company_nu= mbersku8.sku_6_digit> &sku6_digit (,10)sum(fst.current_send_store_qty) &tra= nsfered_qty> (,20)sum(fst.send_sku_retail_price_amt_lcy * fst.current_send_= store_qty)> &transfered_price rank() over (partition by> store.company_numb= er,sku8.sku_6_digit order by &transfered_price) &temp> (,40)...end-select> = I know that putting a SQR column variable is not correct> but I want to ran= k by that metric. Also, just to test the rank function I> have replaced the= column variable with fst.send_sku_retail_price_amt_lcy but> that did not w= ork either.> Does any one know how to use the RANK function in> SQR? please= let me know what I'm doing wrong here.> > Thank you>> ____________________= _____________________________________________> Fixing up> the home? Live Se= arch can help.>> http://imagine-windowslive.com/search/kits/default.aspx?ki= t=3Dimprove&locale=3De> n-US&source=3Dwlmemailtaglinenov06> >> ____________= ___________________________________> sqr-users mailing list>> sqr-users@sqr= ug.org> http://www.sqrug.org/mailman/listinfo/sqr-users> __________________= _______________________________________________> Fixing up the home? Live S= earch can help.> http://imagine-windowslive.com/search/kits/default.aspx?ki= t=3Dimprove&locale=3De> n-US&source=3Dwlmemailtaglinenov06> > _____________= __________________________________> sqr-users mailing list> sqr-users@sqrug= .org> http://www.sqrug.org/mailman/listinfo/sqr-users> > > ________________= _______________________________> sqr-users mailing list> sqr-users@sqrug.or= g> http://www.sqrug.org/mailman/listinfo/sqr-users Get free, personalized online radio with MSN Radio powered by Pandora. Try = it!=20 _________________________________________________________________ Type your favorite song.=A0 Get a customized station.=A0 Try MSN Radio powe= red by Pandora. http://radio.msn.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Jan 10 15:16:02 2007 From: "Franck Masson" Subject: RE: [sqr-users] (no subject) Date: Wed, 10 Jan 2007 21:16:34 +0100 RTF file is ascii file, so you can read these file in SQR . Franck -----Original Message----- From: sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org [mailto:sqr-users-bounces+franck.masson2002=laposte.net@sqrug.org]On Behalf Of mimi bekele Sent: mercredi 10 janvier 2007 07:31 To: sqr-users@sqrug.org Subject: [sqr-users] (no subject) Does anyone know if reading RTF file allowed in SQR V. 6.0? ____________________________________________________________________________ ________ Have a burning question? Go to www.Answers.yahoo.com and get answers from real people who know. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces@sqrug.org Wed Jan 10 16:21:37 2007 Subject: RE: [sqr-users] SQR Help Date: Wed, 10 Jan 2007 15:19:23 -0600 From: "Knapp, Richard" Hi Melissa, For this kind of application (volume repetitive comparisons) I like to use arrays. To do this in your case I would load the values of the column you want to compare and the key column(s) (from the smaller table if there is a significant difference in size) into an array. Then get a column value from the other table and rip through the array looking for a match. If you find one before you get to the end, do the delete. If not, go to the next record's value. Richard Knapp AITS - Data Warehouse Group University of Missouri 615 Locust Street #200 Columbia, MO 65201 573-882-8856 knappr@umsystem.edu =20 -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.o