From sqr-users-admin@sqrug.org Thu Jan 1 00:01:56 2004 Date: Thu, 1 Jan 2004 00:00:02 -0500 From: Nathan Stratton Treadway Subject: [sqr-users] Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Stratton Treadway, Ray Ontko & Co. (nathanst@ontko.com) $Revision: 1.17 $ $Date: 2003-11-01 09:45:47-05 $ This document gathers in one location information about the sqr-users mail list. It includes instructions for changing your subscription settings (including unsubscribing), as well are guidelines for using the list. ______________________________________________________________________ Table of Contents 1. Changes Since Previous Version 2. Introduction 2.1 sqr-users Mailing List 2.2 sqr-users Hints and Guidelines Document 2.3 Related Mailing Lists 3. Interacting with the list server 3.1 Subscribing to the list 3.2 Unsubscribing from the list 3.3 When your email address changes 3.4 Digests 3.5 Obtaining your password 3.6 Problems 4. Posting to the sqr-users List 4.1 General Guidelines 4.2 Posting a Message 4.3 Replying to a Message 5. Job Announcements ______________________________________________________________________ 1. Changes Since Previous Version Changes since the version dated 2002/11/13: o Hyperion has aqcuired Brio Software. o Add section on what to do when one's e-mail address changes. o Mention that the subscription address and message return address must exactly match in order to post to the list. 2. Introduction 2.1. sqr-users Mailing List The sqr-users mailing list was created to enable discussion related to the SQR database language from Hyperion (and to other products in the SQR product suite). Note: SQR used to be published by SQRIBE Technologies, which was merged into Brio Technology on August 3, 1999. Brio Technology changed its name to Brio Software in October 2001. On October 16, 2003, Hyperion acquired Brio Software. SQR and related applications have also been called "Brio.Report" and "Brio Reports". The mailing list is provided as a service of Ray Ontko & Co. A searchable archive of past postings to the mailing list is available from the SQR Users Group web site . 2.2. sqr-users Hints and Guidelines Document This document gives various hints and guidelines about the sqr-users mailing list. It will be posted approximately monthly to the mailing list so that new users can see it (and existing users can find it easily). An HTML version of the document is always available at http://www.sqrug.org/sqr-users/sqr-users-hints.html. If you have comments or suggestions about this document, please contact me directly at the address listed in the title section. This document is Copyright (C) 1999-2002 by Ray Ontko & Co. It may be freely copied and distributed provided it is not modified in any way and it retains the original copyright notice. 2.3. Related Mailing Lists Here are some mailing lists that cover topics relating to SQR in some way. sqr-jobs : SQR Jobs This list may be used for posting SQR-related resumes, recruitments, job announcements, project announcments, help- wanted, work-wanted ads, etc. To join, follow the link above, or send any message to "sqr-jobs-subscribe@yahoogroups.com". peoplesoft-fans : PeopleSoft Fans Moderated list covering technical and functional aspects of PeopleSoft. To join, follow the link above, or send any message to "peoplesoft-fans-subscribe@yahoogroups.com". psusers : PeopleSoft Users For questions related to any aspect of PeopleSoft (including PeopleSoft-specific SQR issues). This list has merged with the Peoplesoft Fans mailing list and is no longer active, but the message achive can still be found at the link above. BrioSQRReport : Brio SQR Report List for discussion of SQR and SQR Developer (formerly called Brio.Report and Report Builder). To join, follow the link above, or send any message to "BrioSQRReport- subscribe@yahoogroups.com". rm-users : Report- Mart Users List for discussion of Brio Portal (previously called ReportMart). To join, follow the link above, or send a message containing the word "subscribe" in the body to "rm-users- request@sqrug.org". brioportalusersgroup : Brio Portal Users Group list List for discussion of Brio Portal. To join, follow the link above, or send any message to "brioportalusersgroup- subscribe@yahoogroups.com". brio-l : OpenITx Brio List List for technical and functional discussion of all Brio products (but most messages are currently about Brio Intelligence). To join, follow the link above. briousers : Brio.Enter- prise Users List for discussion of the Brio.Enterprise (now called Intelligence) product. To join, follow the link above, or send any message to "briousers-subscribe@yahoogroups.com". briolist : Brio List List for discussion of Brio Intelligence. To join, follow the link above, or send any message to "briolist- subscribe@yahoogroups.com". 3. Interacting with the list server The sqr-users mailing list is hosted using the Mailman list server package. All of the Mailman functions described below are accessed from the sqr-users List Info page, "http://www.sqrug.org/mailman/listinfo/sqr-users". 3.1. Subscribing to the list To subscribe to the mailing list, enter your e-mail address in the box at the top of the "Subscribing to sqr-users" section on the sqr-users List Info page, then choose a password to assign to your subscription and enter it in both "Password" boxes. This password is used to allow you to securely make changes to your subscription over the web. This password will be e-mailed back to you at varous times, so you should not use the same password as you are using elsewhere. Once you have filled in the password, click the "Subscribe" button. You should soon receive an e-mail message from the list server requesting confirmation of your subscription request. Once you have replied to this message and your subscription has been processed, you will receive a "welcome" message giving you more information on the list server, and as well as a reminder of the subscription password you chose. 3.2. Unsubscribing from the list To unsubscribe from the list, go to the sqr-users List Info page, enter your e-mail address in the "sqr-users Subscribers" section, and click the "Edit Options" button. In the page that comes up, there will be an "Unsubscribing from sqr-users" section; simply enter your password in the box there and click the "Unsubscribe" button. Assuming you enter the correct password, you will recieve a message stating "You have been unsubscribed." and the removal will take effect immediately (there is no need to send in a confirmation e-mail message). (If you have forgotton your password, see the ``Obtaining your Password'' section, below). 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). Unfortunately, the list server software currently does not provide a way for you to change the e-mail address used in a list subscription. This means that you will need to subscribe yourself from your new address and then unsubscribe yourself from the old address, using the normal subscribe and unsubscribe processes described above. 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, enter your address in the "sqr-users Subscribers" section of the sqr-users List Info page and click the "Edit Options" button. At the bottom of the resulting page, you will see a section titled "Your sqr-users Subscription Options". Change the "Set Digest Mode" option to "On", enter your subscription password in the box provided, and click the "Submit My Changes" button. (If you have forgotton your password, see the instructions below.) You actually have a choice of two different digest formats, "MIME" or "Plain Text". The MIME format sends each individual list message as a separate MIME attachment to the digest; the plain text format consists of one large text message, with all of the individual messages run together. Which format you will want to use depends on your mail- reading software (and personal preference). If you are not sure which format will work best, you can just leave the default setting of "MIME" and see if the next morning's digest is easy to read in your mail reader -- you can change to the other format at any time using that same web page. If you use digests, you should not just "reply" to the digest mailing. Instead, be sure your message's subject header matches the subject in the original posting to which you are replying, and that you don't include a quoted copy of the entire digest in your post. To restore normal distribution, simply follow the above instructions choosing the "Off" setting for the Digest option. 3.5. Obtaining your password You will not be able to unsubscribe or change your subscription options without your password. If you have forgotten your password, simply click the "Email My Password to Me" button on the Edit Options page and your password will be 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.) 4.1. General Guidelines For any message you send to the list, please: o make sure your mail software is not requesting a "Return Receipt". (If a "Return Receipt Requested" message is sent to the list, several dozen receipt messages will be sent out to the mailing list in reply from other subscribers.) o do not send messages in HTML or other special formats. (These are not usable by many subscribers, especially those who receive their messages in digest format.) The list server will delete these special attachments from your message and send you a warning notice. 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 Brio product and version number (see notes below) o Operating system and version number o Database name and version number o Application name (i.e. PeopleSoft) and a version number, if any Also, include a short code sample whenever possible. It's much easier for other people to understand what you are trying to do when they can see the actual code. (At the same time, be careful not to post confidential or proprietary code, since the posting will be placed in a public web archive.) Note that your question should relate to SQR or other Brio Report product in some way. Don't ask questions about PeopleSoft, for example, unless you are having SQR-related issues. (For PeopleSoft- specific questions, see other lists mentioned in the ``Related Mailing Lists'' section.) In 2001, PeopleSoft purchased from Brio the rights to the source code for SQR and began releasing its own line of SQR. PeopleSoft's SQR version numbering is different from Brio's, so it's very important to mention whether you are using Brio's "SQR" or "SQR for PeopleSoft". (This info is included automatically if you include the full SQR version-id string as described below.) To get the version-id string for your copy of SQR, you can run the following program: begin-program show $sqr-ver end-program This will produce a line that looks something like this: SQR/3.0.15/Intel/SCO Unix R3.2 V5.0/Oracle 7.2.2.3/Sep 25 1996 Include the full line of output in your message. Note that the oper- ating system version number listed here is the one under which this copy of SQR was compiled, so you'll still need to include this infor- mation for your own environment. In a command-line environment (Unix, etc.), you can also get this version-id string by saying sqr -id 4.3. Replying to a Message Messages sent out on the list have a Reply-To header pointing to the list. This means that you should be able to simply use your mail reader's "reply" function to send a reply to the list. Generally, all answers to questions sent out on the list should be public replies, so that other subscribers can benefit. In those cases where a private reply is appropriate, be sure to over-ride the default Reply-To address. If your message is not actually a reply to the earlier message, please do not use the "reply" function. Instead, address a new e-mail message to the list as described in the previous section. (This is because the list archives will incorrectly show the new message in the original thread if you use "reply".) You should quote enough lines from the original message that later readers can understand your answer even if they missed the original question. At the same time, you should remove any unnecessary lines from the quote in order to make your message shorter and easier to understand. (Remember that some people read the mailing list in digest form, and the digests become much larger if unnecessary lines are quoted.) 5. Job Announcements Please do not post job announcements on the sqr-users list, but use the sqr-jobs list instead. (See ``Related Mailing Lists'' for more information on this list.) A large number of sqr-users subscribers do not want to receive job announcements (for various reasons) and such posts tend to cause a lot of disruption of the discussion on the list. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 1 01:16:17 2004 Subject: RE: [sqr-users] ORA-1004 or ORA-3113 Date: Thu, 1 Jan 2004 16:15:09 +1000 From: "Bruce Young" We use SQR 4.3.4 with PeopleSoft. I re-linked it against Oracle 9.2 before using it and have had no issues. PeopleSoft listed the relink as a required update for using oracle 9.2. The relink was done using sqrmake. hope this helps, Bruce -----Original Message----- From: George Jansen [mailto:GJANSEN@aflcio.org] Sent: Thursday, 1 January 2004 4:42 AM To: sqr-users@sqrug.org Subject: [sqr-users] ORA-1004 or ORA-3113 We have SQR 4.3.4 linked against Oracle 8.1.7 as our production SQR. When I try to run it against Oracle 9.2.0, I get one of two errors: ORA-01004: default username feature not supported; logon denied if I try to log in over a dispatched connection, ORA-03113: end-of-file on communication channel if I try to log in over a dedicated connection (no @db in my connectivity string, or with (Server=Dedicated) specified in my tnsnames.ora). With SQR 6.2 linked against Oracle 9.2.0, I can connect to 9.2 or 8.1.7 databases with no problem. This strongly suggests, of course, that I should quit worrying and run with 6.2; unfortunately I'm not ready to do that yet. Has anybody else run up against this particular problem with SQR 4.3.4 and Oracle 9.2? George Jansen _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 6 02:19:06 2004 From: "Sudeep Vaidya" Date: Tue, 6 Jan 2004 12:48:52 +0530 Subject: [sqr-users] Memory fault Error while Running SQR. Hi, I'm facing the following error when running a SQR report on Brio Portal. Can somebody give me a clue as to where to look for the problem.. kpir0014 execution failed. kgefec: fatal er/brio/portal/BrioPortal/server/JF_gbo314-d/tmp/JF_gbo314-d-3async-58/sqr_e xec.sh[12]: 20376 Memory fault I'm fetching data from database into an array, and printing it after performing certain calculations. Thanks in Advance, Sudeep _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 6 07:55:05 2004 Date: Tue, 6 Jan 2004 13:53:47 +0100 From: edwin.hommes@bluewin.ch Subject: [sqr-users] 20376 Memory Error Sudeep, Error 20376 is a Null Pointer Deference Error. Basically means that Unix encounters a NULL value in your Array, where it should contain either a blank or a zero. You may want to check your SQL statement that fills your array or work with a temporary table. Regards, Edwin >Hi, > >I'm facing the following error when running a SQR report on Brio Portal. >Can >somebody give me a clue as to where to look for the problem.. > > >kpir0014 execution failed. kgefec: fatal >er/brio/portal/BrioPortal/server/JF_gbo314-d/tmp/JF_gbo314-d-3async-58/sqr_e >xec.sh[12]: 20376 Memory fault > >I'm fetching data from database into an array, and printing it after >performing certain calculations. > >Thanks in Advance, > > >Sudeep _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 6 10:33:42 2004 From: Ray Ontko Date: Tue, 6 Jan 2004 10:32:10 -0500 Subject: Re: [sqr-users] Memory fault Error while Running SQR. Are you sure that SQR is failing? Did you modify start_JF_.sh to set JFdebug=true? If so, what's on line 12 of sqr_exec.sh? If it's the SQR program, then I suppose we can look deeper at the SQR. Ray On Tue, Jan 06, 2004 at 12:48:52PM +0530, Sudeep Vaidya wrote: > Hi, > > I'm facing the following error when running a SQR report on Brio Portal. Can > somebody give me a clue as to where to look for the problem.. > > > kpir0014 execution failed. kgefec: fatal > er/brio/portal/BrioPortal/server/JF_gbo314-d/tmp/JF_gbo314-d-3async-58/sqr_e > xec.sh[12]: 20376 Memory fault > > I'm fetching data from database into an array, and printing it after > performing certain calculations. > > Thanks in Advance, > > > Sudeep > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users ---------------------------------------------------------------------- Ray Ontko rayo@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 6 13:03:53 2004 Subject: RE: [sqr-users] Memory fault Error while Running SQR. Date: Tue, 6 Jan 2004 12:57:59 -0500 From: "Tom Cormier" This is a multi-part message in MIME format... ------_=_NextPart_001_01C3D47E.9F171044 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 SGksDQogDQpEb3VibGUgY2hlY2sgeW91ciBzdWJzY3JpcHRzIGFzIHdlbGwu ICBNYWtlIHN1cmUgeW91IGFyZSBub3QgcG9pbnRpbmcgdG8gb3V0LW9mLWJv dW5kIGFyZWFzIGluIHlvdXIgYXJyYXkgd2hpbGUgbG9hZGluZyBvciBwcmlu dGluZy4NCiANCi0tLXRvbWNvcm1pZXINCg0KCS0tLS0tT3JpZ2luYWwgTWVz c2FnZS0tLS0tIA0KCUZyb206IHNxci11c2Vycy1hZG1pbkBzcXJ1Zy5vcmcg b24gYmVoYWxmIG9mIFJheSBPbnRrbyANCglTZW50OiBUdWUgMS82LzIwMDQg MTA6MzIgQU0gDQoJVG86IHNxci11c2Vyc0BzcXJ1Zy5vcmcgDQoJQ2M6IA0K CVN1YmplY3Q6IFJlOiBbc3FyLXVzZXJzXSBNZW1vcnkgZmF1bHQgRXJyb3Ig d2hpbGUgUnVubmluZyBTUVIuDQoJDQoJDQoNCglBcmUgeW91IHN1cmUgdGhh dCBTUVIgaXMgZmFpbGluZz8gIERpZCB5b3UgbW9kaWZ5IHN0YXJ0X0pGXzxi bGFoPi5zaA0KCXRvIHNldCBKRmRlYnVnPXRydWU/ICBJZiBzbywgd2hhdCdz IG9uIGxpbmUgMTIgb2Ygc3FyX2V4ZWMuc2g/ICBJZg0KCWl0J3MgdGhlIFNR UiBwcm9ncmFtLCB0aGVuIEkgc3VwcG9zZSB3ZSBjYW4gbG9vayBkZWVwZXIg YXQgdGhlIFNRUi4NCgkNCglSYXkNCgkNCglPbiBUdWUsIEphbiAwNiwgMjAw NCBhdCAxMjo0ODo1MlBNICswNTMwLCBTdWRlZXAgVmFpZHlhIHdyb3RlOg0K CT4gSGksDQoJPg0KCT4gSSdtIGZhY2luZyB0aGUgZm9sbG93aW5nIGVycm9y IHdoZW4gcnVubmluZyBhIFNRUiByZXBvcnQgb24gQnJpbyBQb3J0YWwuIENh bg0KCT4gc29tZWJvZHkgZ2l2ZSBtZSBhIGNsdWUgYXMgdG8gd2hlcmUgdG8g bG9vayBmb3IgdGhlIHByb2JsZW0uLg0KCT4NCgk+DQoJPiBrcGlyMDAxNCBl eGVjdXRpb24gZmFpbGVkLiBrZ2VmZWM6IGZhdGFsDQoJPiBlci9icmlvL3Bv cnRhbC9CcmlvUG9ydGFsL3NlcnZlci9KRl9nYm8zMTQtZC90bXAvSkZfZ2Jv MzE0LWQtM2FzeW5jLTU4L3Nxcl9lDQoJPiB4ZWMuc2hbMTJdOiAyMDM3NiBN ZW1vcnkgZmF1bHQNCgk+DQoJPiBJJ20gZmV0Y2hpbmcgZGF0YSBmcm9tIGRh dGFiYXNlIGludG8gYW4gYXJyYXksIGFuZCBwcmludGluZyBpdCBhZnRlcg0K CT4gcGVyZm9ybWluZyBjZXJ0YWluIGNhbGN1bGF0aW9ucy4NCgk+DQoJPiBU aGFua3MgaW4gQWR2YW5jZSwNCgk+DQoJPg0KCT4gU3VkZWVwDQoJPg0KCT4N Cgk+IF9fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19f X19fX19fDQoJPiBzcXItdXNlcnMgbWFpbGluZyBsaXN0DQoJPiBzcXItdXNl cnNAc3FydWcub3JnDQoJPiBodHRwOi8vd3d3LnNxcnVnLm9yZy9tYWlsbWFu L2xpc3RpbmZvL3Nxci11c2Vycw0KCS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0NCglSYXkgT250a28gICByYXlvQG9udGtvLmNvbSAgIFBob25lIDEuNzY1 LjkzNS40MjgzICAgRmF4IDEuNzY1Ljk2Mi45Nzg4DQoJUmF5IE9udGtvICYg Q28uICAgU29mdHdhcmUgQ29uc3VsdGluZyBTZXJ2aWNlcyAgIGh0dHA6Ly93 d3cub250a28uY29tLw0KCQ0KCV9fX19fX19fX19fX19fX19fX19fX19fX19f X19fX19fX19fX19fX19fX19fX19fDQoJc3FyLXVzZXJzIG1haWxpbmcgbGlz dA0KCXNxci11c2Vyc0BzcXJ1Zy5vcmcNCglodHRwOi8vd3d3LnNxcnVnLm9y Zy9tYWlsbWFuL2xpc3RpbmZvL3Nxci11c2Vycw0KCQ0KDQo= ------_=_NextPart_001_01C3D47E.9F171044 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'application/ms-tnef' MIME type. It has been automatically discarded before sending the message to the list. ------_=_NextPart_001_01C3D47E.9F171044-- _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 6 22:15:09 2004 Date: Wed, 07 Jan 2004 14:12:34 +1100 From: "Ian Mills" Subject: [sqr-users] No Value returned from Sybase Stored procedure Hi all, I was wondering if anyone would be able to help me with this strange problem. I am trying to use a stored procedure running against a Sybase database and the output variable doesn't contain any value. Source code below. I have tested the stored procedure in a query analyser tool and it works okay. begin-procedure get_schedule($as_contract_number) declare-variable text $as_contract_number end-declare execute osp_sec_calcschedule $as_contract_number, $_gs_schedule out #debugt show ':' $_gs_schedule ':' end-procedure I use similar functionality in 2 other reports and the values are returned correctly. (shown below) execute osp_arrears_calcfields $as_lease $as_port $as_branch $as_prod $as_sub #ai_del1 out #ai_del2 out #ai_del3 out #ai_del4 out #ai_del5 out #af_arrears out I am running SQR v6.1.3 Any assistance will be appreciated. Regards, Ian Mills Systems Analyst ORIX Australia Corp Ltd _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 7 06:43:21 2004 Subject: Re: [sqr-users] No Value returned from Sybase Stored procedure From: "Richard C Spoth" Date: Wed, 7 Jan 2004 06:41:11 -0500 Ian, Try this. Where @INPUT_VARIALBE is verbatim to the variable in the SP. execute osp_sec_calcschedule @INPUT_VARIABLE = $as_contract_number INTO &schedule Let $_gs_schedule = &schedule #debugt show ':' $_gs_schedule ':' Rick Spoth "Ian Mills" Sent by: sqr-users-admin@sqrug.org 01/06/04 10:12 PM Please respond to sqr-users To: cc: Subject: [sqr-users] No Value returned from Sybase Stored procedure Hi all, I was wondering if anyone would be able to help me with this strange problem. I am trying to use a stored procedure running against a Sybase database and the output variable doesn't contain any value. Source code below. I have tested the stored procedure in a query analyser tool and it works okay. begin-procedure get_schedule($as_contract_number) declare-variable text $as_contract_number end-declare execute osp_sec_calcschedule $as_contract_number, $_gs_schedule out #debugt show ':' $_gs_schedule ':' end-procedure I use similar functionality in 2 other reports and the values are returned correctly. (shown below) execute osp_arrears_calcfields $as_lease $as_port $as_branch $as_prod $as_sub #ai_del1 out #ai_del2 out #ai_del3 out #ai_del4 out #ai_del5 out #af_arrears out I am running SQR v6.1.3 Any assistance will be appreciated. Regards, Ian Mills Systems Analyst ORIX Australia Corp Ltd _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users CONFIDENTIALITY NOTICE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain proprietary, confidential, trade secret or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited and may be a violation of law. If you are not the intended recipient or a person responsible for delivering this message to an intended recipient, please contact the sender by reply email and destroy all copies of the original message. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 7 06:50:14 2004 Subject: Re: [sqr-users] No Value returned from Sybase Stored procedure From: "Richard C Spoth" Date: Wed, 7 Jan 2004 06:49:54 -0500 I forgot to mention you also need to define the data type for &schedule. It should be the same as the value in the SELECT statement of the SP. execute osp_sec_calcschedule @INPUT_VARIABLE = $as_contract_number INTO &schedule char(8) Rick Spoth "Richard C Spoth" Sent by: sqr-users-admin@sqrug.org 01/07/04 06:41 AM Please respond to sqr-users To: sqr-users@sqrug.org cc: Subject: Re: [sqr-users] No Value returned from Sybase Stored procedure Ian, Try this. Where @INPUT_VARIALBE is verbatim to the variable in the SP. execute osp_sec_calcschedule @INPUT_VARIABLE = $as_contract_number INTO &schedule Let $_gs_schedule = &schedule #debugt show ':' $_gs_schedule ':' Rick Spoth "Ian Mills" Sent by: sqr-users-admin@sqrug.org 01/06/04 10:12 PM Please respond to sqr-users To: cc: Subject: [sqr-users] No Value returned from Sybase Stored procedure Hi all, I was wondering if anyone would be able to help me with this strange problem. I am trying to use a stored procedure running against a Sybase database and the output variable doesn't contain any value. Source code below. I have tested the stored procedure in a query analyser tool and it works okay. begin-procedure get_schedule($as_contract_number) declare-variable text $as_contract_number end-declare execute osp_sec_calcschedule $as_contract_number, $_gs_schedule out #debugt show ':' $_gs_schedule ':' end-procedure I use similar functionality in 2 other reports and the values are returned correctly. (shown below) execute osp_arrears_calcfields $as_lease $as_port $as_branch $as_prod $as_sub #ai_del1 out #ai_del2 out #ai_del3 out #ai_del4 out #ai_del5 out #af_arrears out I am running SQR v6.1.3 Any assistance will be appreciated. Regards, Ian Mills Systems Analyst ORIX Australia Corp Ltd _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users CONFIDENTIALITY NOTICE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain proprietary, confidential, trade secret or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited and may be a violation of law. If you are not the intended recipient or a person responsible for delivering this message to an intended recipient, please contact the sender by reply email and destroy all copies of the original message. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 7 17:18:47 2004 Date: Thu, 08 Jan 2004 09:15:42 +1100 From: "Ian Mills" Subject: Re: [sqr-users] No Value returned from Sybase Stored procedure Rick, Thanks for your assistance. I tried to do as you suggested and got the following error (SQR 5528) Sybase CT_RESULTS error in cursor 10: (201) Procedure osp_sec_calcschedule expects parameter @schedule_out, which was not supplied. So I added "@schedule_out = $ls_temp" and got the following error (SQR 5508) The number of EXECUTE...INTO &columns does not match the procedure. If I take away the INTO part the report doesn't crash but the stored procedure still returns no data. Then I changed my thinking. I knew the stored procedure was working, just not with the execute command. I Created a single column temporary table to store the value. Then ran the stored procedure within a BEGIN-SQL block and inserted the returned value into the temporary table. Then used a BEGIN-SELECT block to get the value from the stored procedure. Below is the final procedure that now works. begin-procedure get_schedule($as_contract_number) declare-variable text $as_contract_number end-declare begin-sql delete from [$_gs_table_name] DECLARE @structure varchar(30) exec osp_sec_calcschedule $as_contract_number, @structure out insert INTO [$_gs_table_name] values(@structure) end-sql begin-select structure &structure=char let $_gs_schedule = &structure from [$_gs_table_name] end-select end-procedure Hopefully others can use this if they ever encounter the same problem. Regards, Ian Mills Systems Analyst ORIX Australia Corp Ltd >>> Spoth.Richard@healthnow.org 07/01/2004 10:49:54 pm >>> I forgot to mention you also need to define the data type for &schedule. It should be the same as the value in the SELECT statement of the SP. execute osp_sec_calcschedule @INPUT_VARIABLE = $as_contract_number INTO &schedule char(8) Rick Spoth "Richard C Spoth" Sent by: sqr-users-admin@sqrug.org 01/07/04 06:41 AM Please respond to sqr-users To: sqr-users@sqrug.org cc: Subject: Re: [sqr-users] No Value returned from Sybase Stored procedure Ian, Try this. Where @INPUT_VARIALBE is verbatim to the variable in the SP. execute osp_sec_calcschedule @INPUT_VARIABLE = $as_contract_number INTO &schedule Let $_gs_schedule = &schedule #debugt show ':' $_gs_schedule ':' Rick Spoth "Ian Mills" Sent by: sqr-users-admin@sqrug.org 01/06/04 10:12 PM Please respond to sqr-users To: cc: Subject: [sqr-users] No Value returned from Sybase Stored procedure Hi all, I was wondering if anyone would be able to help me with this strange problem. I am trying to use a stored procedure running against a Sybase database and the output variable doesn't contain any value. Source code below. I have tested the stored procedure in a query analyser tool and it works okay. begin-procedure get_schedule($as_contract_number) declare-variable text $as_contract_number end-declare execute osp_sec_calcschedule $as_contract_number, $_gs_schedule out #debugt show ':' $_gs_schedule ':' end-procedure I use similar functionality in 2 other reports and the values are returned correctly. (shown below) execute osp_arrears_calcfields $as_lease $as_port $as_branch $as_prod $as_sub #ai_del1 out #ai_del2 out #ai_del3 out #ai_del4 out #ai_del5 out #af_arrears out I am running SQR v6.1.3 Any assistance will be appreciated. Regards, Ian Mills Systems Analyst ORIX Australia Corp Ltd _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users CONFIDENTIALITY NOTICE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain proprietary, confidential, trade secret or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited and may be a violation of law. If you are not the intended recipient or a person responsible for delivering this message to an intended recipient, please contact the sender by reply email and destroy all copies of the original message. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 8 08:22:57 2004 Subject: Re: [sqr-users] No Value returned from Sybase Stored procedure From: "Richard C Spoth" Date: Thu, 8 Jan 2004 08:21:44 -0500 That will work but it seems more like a work around. The use of "INTO" is for SP that return the values like a query. You can get the syntax I originally supplied to work by modifying the SP to return the value as part of a SELECT instead for an output variable. Which is what I assumed the SP was structured like. OR To use the SP as is try the following: execute osp_sec_calcschedule @INPUT_PUT = $as_contract_number, @schedule_out = $_gs_schedule OUTPUT You basically need to flag each "OUTPUT" variable when executing in SQR a SP with output variables. Rick Spoth "Ian Mills" Sent by: sqr-users-admin@sqrug.org 01/07/04 05:15 PM Please respond to sqr-users To: cc: Subject: Re: [sqr-users] No Value returned from Sybase Stored procedure Rick, Thanks for your assistance. I tried to do as you suggested and got the following error (SQR 5528) Sybase CT_RESULTS error in cursor 10: (201) Procedure osp_sec_calcschedule expects parameter @schedule_out, which was not supplied. So I added "@schedule_out = $ls_temp" and got the following error (SQR 5508) The number of EXECUTE...INTO &columns does not match the procedure. If I take away the INTO part the report doesn't crash but the stored procedure still returns no data. Then I changed my thinking. I knew the stored procedure was working, just not with the execute command. I Created a single column temporary table to store the value. Then ran the stored procedure within a BEGIN-SQL block and inserted the returned value into the temporary table. Then used a BEGIN-SELECT block to get the value from the stored procedure. Below is the final procedure that now works. begin-procedure get_schedule($as_contract_number) declare-variable text $as_contract_number end-declare begin-sql delete from [$_gs_table_name] DECLARE @structure varchar(30) exec osp_sec_calcschedule $as_contract_number, @structure out insert INTO [$_gs_table_name] values(@structure) end-sql begin-select structure &structure=char let $_gs_schedule = &structure from [$_gs_table_name] end-select end-procedure Hopefully others can use this if they ever encounter the same problem. Regards, Ian Mills Systems Analyst ORIX Australia Corp Ltd >>> Spoth.Richard@healthnow.org 07/01/2004 10:49:54 pm >>> I forgot to mention you also need to define the data type for &schedule. It should be the same as the value in the SELECT statement of the SP. execute osp_sec_calcschedule @INPUT_VARIABLE = $as_contract_number INTO &schedule char(8) Rick Spoth "Richard C Spoth" Sent by: sqr-users-admin@sqrug.org 01/07/04 06:41 AM Please respond to sqr-users To: sqr-users@sqrug.org cc: Subject: Re: [sqr-users] No Value returned from Sybase Stored procedure Ian, Try this. Where @INPUT_VARIALBE is verbatim to the variable in the SP. execute osp_sec_calcschedule @INPUT_VARIABLE = $as_contract_number INTO &schedule Let $_gs_schedule = &schedule #debugt show ':' $_gs_schedule ':' Rick Spoth "Ian Mills" Sent by: sqr-users-admin@sqrug.org 01/06/04 10:12 PM Please respond to sqr-users To: cc: Subject: [sqr-users] No Value returned from Sybase Stored procedure Hi all, I was wondering if anyone would be able to help me with this strange problem. I am trying to use a stored procedure running against a Sybase database and the output variable doesn't contain any value. Source code below. I have tested the stored procedure in a query analyser tool and it works okay. begin-procedure get_schedule($as_contract_number) declare-variable text $as_contract_number end-declare execute osp_sec_calcschedule $as_contract_number, $_gs_schedule out #debugt show ':' $_gs_schedule ':' end-procedure I use similar functionality in 2 other reports and the values are returned correctly. (shown below) execute osp_arrears_calcfields $as_lease $as_port $as_branch $as_prod $as_sub #ai_del1 out #ai_del2 out #ai_del3 out #ai_del4 out #ai_del5 out #af_arrears out I am running SQR v6.1.3 Any assistance will be appreciated. Regards, Ian Mills Systems Analyst ORIX Australia Corp Ltd _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users CONFIDENTIALITY NOTICE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain proprietary, confidential, trade secret or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited and may be a violation of law. If you are not the intended recipient or a person responsible for delivering this message to an intended recipient, please contact the sender by reply email and destroy all copies of the original message. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 8 08:25:07 2004 From: "Sudeep Vaidya" Subject: FW: [sqr-users] 20376 Memory Error Date: Thu, 8 Jan 2004 18:55:05 +0530 Hi, This is regarding the Memory Fault Error which I had asked earlier about.The number 20376 which the error throws is not the error number because this number changes everytime I run the report.So I suppose that this is the Unix Id for the job.As for the other solutions suggested for the problem, on line 12 of the file sqr_exec.sh, we have the following SQRDIR="/brio/portal/ora/bin" export SQRDIR NLS_LANG='american_america.UTF8" export NLS_LANG if [ -n "$PATH" ]; then PATH=$SQRDIR":"$PATH else PATH=$SQRDIR so it has got nothing to do with the memory fault. This is not the problem with the array also because the array is getting cleared after all the lines for a particular order number are populated. Can anyone suggest any other cause for the same error. thanx for the cooperation in advance. regards, sudeep. -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of edwin.hommes@bluewin.ch Sent: Tuesday, January 06, 2004 6:24 PM To: sqr-users@sqrug.org Subject: [sqr-users] 20376 Memory Error Sudeep, Error 20376 is a Null Pointer Deference Error. Basically means that Unix encounters a NULL value in your Array, where it should contain either a blank or a zero. You may want to check your SQL statement that fills your array or work with a temporary table. Regards, Edwin >Hi, > >I'm facing the following error when running a SQR report on Brio Portal. >Can >somebody give me a clue as to where to look for the problem.. > > >kpir0014 execution failed. kgefec: fatal >er/brio/portal/BrioPortal/server/JF_gbo314-d/tmp/JF_gbo314-d-3async-58/sqr_ e >xec.sh[12]: 20376 Memory fault > >I'm fetching data from database into an array, and printing it after >performing certain calculations. > >Thanks in Advance, > > >Sudeep _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 9 11:25:25 2004 Date: Fri, 9 Jan 2004 11:21:27 -0500 From: "Vito Campanelli" Subject: [sqr-users] JSP and SQR Hi, I am very new to SQR, so let me apologize in advance. I wanted to know if anybody had any experience with JSP and SQR. Also, I wanted to know, if it was possible to have a JSP act as a parameter page and then invoke SQR to run the report and display it in either HTML, PDF or excel. Is there any example? Thanks Vito _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 9 11:37:07 2004 Date: Fri, 09 Jan 2004 10:36:23 -0600 From: Bob.Melosi@edwardjones.com Subject: Re: [sqr-users] JSP and SQR I've got experience with both. (6 with SQR and 3 with JSP). Where I work we do a lot of display pages in our PeopleSoft self-service applications so it kind of acts like a SQR in a web page. (we also do updates). This really isn't the forum for JSPs so you can email me directly and let me know what you are doing and maybe I can help. > Hi, > > I am very new to SQR, so let me apologize in advance. > I wanted to know if anybody had any experience with JSP and SQR. > Also, I wanted to know, if it was possible to have a JSP act as a > parameter page and then invoke SQR to run the report and display it in > either HTML, PDF or excel. Is there any example? > > Thanks > Vito > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users Bob Melosi x5-8427 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 9 12:57:12 2004 Date: Fri, 9 Jan 2004 12:56:23 -0500 From: "Wes Williams" Subject: [sqr-users] Report run from editor new-report, pdf files ok, run from command-line files are corrupted why? Hi I am at a loss to explain why I see corrupted pdf's running from a command-line and from the design editor everything works perfect. Sqr 6.13 (Brio report builder 6.13) Any ideas? Thx Wes Williams !******************************************************************** ! Create separate pdf outputs for each room inspected * !******************************************************************** begin-procedure create_PDF_reports let $report = &room||'.pdf' new-report $report use-report one end-procedure _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 9 13:53:08 2004 From: Lori.Puntasecca@frx.com Subject: RE: [sqr-users] Report run from editor new-report, pdf files ok, run from command-line files are corrupted why? Date: Fri, 9 Jan 2004 13:49:41 -0500 Are you running in UNIX? If yes, you need a -PRINTER:PD flag (after your database connectivity) in the command line to create PDFs. Lori A Puntasecca Forest Laboratories, Inc. MIS- Data Tools Team email: lori.puntasecca@frx.com -----Original Message----- From: Wes Williams [mailto:wes@Princeton.EDU] Sent: Friday, January 09, 2004 12:56 PM To: sqr-users@sqrug.org Subject: [sqr-users] Report run from editor new-report, pdf files ok, run from command-line files are corrupted why? Hi I am at a loss to explain why I see corrupted pdf's running from a command-line and from the design editor everything works perfect. Sqr 6.13 (Brio report builder 6.13) Any ideas? Thx Wes Williams !******************************************************************** ! Create separate pdf outputs for each room inspected * !******************************************************************** begin-procedure create_PDF_reports let $report = &room||'.pdf' new-report $report use-report one end-procedure _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ____________________________________________________________________ This e-mail and its attachments may contain Forest Laboratories, Inc. proprietary information that is privileged, confidential or subject to copyright belonging to Forest Laboratories, Inc. This e-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this e-mail, or the employee or agent responsible for delivering this e-mail to the intended recipient, you are hereby notified that any dissemination, distribution, copying or action taken in relation to the contents of and attachments to this e-mail is strictly prohibited and may be unlawful. If you have received this e-mail in error, please notify the sender immediately and permanently delete the original and any copy of this e-mail and any printout. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 9 14:12:14 2004 Date: Fri, 9 Jan 2004 14:11:39 -0500 From: "Wes Williams" Subject: [sqr-users] Good files now, but no attachments from command-line, but works from editor. Thanks , Looks like that is true for windows too. New issue I am using Blat and code Tony Delia created for e-mailing. Again runs fine from editor PDF files are attached to mail. From the command-line no attachments are in mail, but now with the -PRINTER:PD flag good pdf's are being created. I guess I may have to put all the address and rooms into an array and do a pass as the last step. Doesn't make sense though. Thx Wes Williams -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org] On Behalf Of Lori.Puntasecca@frx.com Sent: Friday, January 09, 2004 1:50 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Report run from editor new-report, pdf files ok, run from command-line files are corrupted why? Are you running in UNIX? If yes, you need a -PRINTER:PD flag (after your database connectivity) in the command line to create PDFs. Lori A Puntasecca Forest Laboratories, Inc. MIS- Data Tools Team email: lori.puntasecca@frx.com -----Original Message----- From: Wes Williams [mailto:wes@Princeton.EDU] Sent: Friday, January 09, 2004 12:56 PM To: sqr-users@sqrug.org Subject: [sqr-users] Report run from editor new-report, pdf files ok, run from command-line files are corrupted why? Hi I am at a loss to explain why I see corrupted pdf's running from a command-line and from the design editor everything works perfect. Sqr 6.13 (Brio report builder 6.13) Any ideas? Thx Wes Williams !******************************************************************** ! Create separate pdf outputs for each room inspected * !******************************************************************** begin-procedure create_PDF_reports let $report = &room||'.pdf' new-report $report use-report one end-procedure _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ____________________________________________________________________ This e-mail and its attachments may contain Forest Laboratories, Inc. proprietary information that is privileged, confidential or subject to copyright belonging to Forest Laboratories, Inc. This e-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this e-mail, or the employee or agent responsible for delivering this e-mail to the intended recipient, you are hereby notified that any dissemination, distribution, copying or action taken in relation to the contents of and attachments to this e-mail is strictly prohibited and may be unlawful. If you have received this e-mail in error, please notify the sender immediately and permanently delete the original and any copy of this e-mail and any printout. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 13 09:28:15 2004 From: AmiPatel@loews.com Date: Tue, 13 Jan 2004 09:25:41 -0500 Subject: [sqr-users] Multiple Reports - one to printer and one to specific location. Hello , I want to create 2 reports out of my SQR program , one should go to printer or file ( depending upon whatever is selected from peoplesoft PANEL) and other should go to specific file location. Contents in both reports are not same. I tried NEW-REPORT and also tried USE-REPORT . Now trying both in same program , lets see how it goes....... Please give your suggestions. Please Help.... Thanks AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 13 09:44:08 2004 Subject: RE: [sqr-users] Multiple Reports - one to printer and one to specific location. Date: Tue, 13 Jan 2004 08:43:27 -0600 From: "Knapp, Richard" My solution to this is to create a print file with write commands, close the file and then send the operating system command (via call system) to print the file. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of AmiPatel@loews.com Sent: Tuesday, January 13, 2004 8:26 AM To: sqr-users@sqrug.org Subject: [sqr-users] Multiple Reports - one to printer and one to specific location. Hello , I want to create 2 reports out of my SQR program , one should go to printer or file ( depending upon whatever is selected from peoplesoft PANEL) and other should go to specific file location. Contents in both reports are not same. I tried NEW-REPORT and also tried USE-REPORT . Now trying both in same program , lets see how it goes....... Please give your suggestions. Please Help.... Thanks AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 13 12:20:18 2004 Date: Tue, 13 Jan 2004 12:18:26 -0500 From: "Maria Ravanis" Subject: [sqr-users] Decimal Issue Can someone tell me how I can get a numeric value to display 8 decimal positions? It appears to be defaulting to 6. I have tried using this code: begin-setup declare-variable default-numeric=decimal decimal = #NEWPERCENT(11) end-declare END-SETUP But I keep getting the following error message: Error on line 37: (SQR 7202) Variable (#newpercent) is not a decimal variable and cannot have a precision associated with it. decimal = #NEWPERCENT(11) Errors were found in the program file. SQR for PeopleSoft: Program Aborting. Thanks! Maria _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 13 13:02:38 2004 From: Peter Burton Subject: RE: [sqr-users] Decimal Issue Date: Tue, 13 Jan 2004 13:01:22 -0500 This is a multi-part message in MIME format... ------_=_NextPart_000_01C3D9FF.40ED7EA0 Content-Type: text/plain Maria, You must specify an edit mask. Peter > -----Original Message----- > From: sqr-users-admin@sqrug.org@HYPERION On Behalf Of "Maria > Ravanis" > Sent: Tuesday, January 13, 2004 12:18 PM > To: sqr-users@sqrug.org > Subject: [sqr-users] Decimal Issue > > Can someone tell me how I can get a numeric value to display 8 decimal > positions? It appears to be defaulting to 6. I have tried using this > code: > > > > begin-setup > > declare-variable > > default-numeric=decimal > > decimal = #NEWPERCENT(11) > > end-declare > > END-SETUP > > > > But I keep getting the following error message: > > Error on line 37: > (SQR 7202) Variable (#newpercent) is not a decimal variable and > cannot > have a precision associated with it. > decimal = #NEWPERCENT(11) > > Errors were found in the program file. > > SQR for PeopleSoft: Program Aborting. > > > Thanks! Maria > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > ------_=_NextPart_000_01C3D9FF.40ED7EA0 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'application/ms-tnef' MIME type. It has been automatically discarded before sending the message to the list. ------_=_NextPart_000_01C3D9FF.40ED7EA0-- _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 13 14:57:17 2004 Subject: RE: [sqr-users] Multiple Reports - one to printer and one to specific location. From: AmiPatel@loews.com Date: Tue, 13 Jan 2004 14:56:16 -0500 THANKS ITS WORKING ... I am preparing .lis only and then redirecting it to lpt1 with system call command.....Thanks ap "Knapp, Richard" edu> cc: Sent by: Subject: RE: [sqr-users] Multiple Reports - one to printer sqr-users-admin@s and one to specific location. qrug.org 01/13/2004 09:43 AM Please respond to sqr-users My solution to this is to create a print file with write commands, close the file and then send the operating system command (via call system) to print the file. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of AmiPatel@loews.com Sent: Tuesday, January 13, 2004 8:26 AM To: sqr-users@sqrug.org Subject: [sqr-users] Multiple Reports - one to printer and one to specific location. Hello , I want to create 2 reports out of my SQR program , one should go to printer or file ( depending upon whatever is selected from peoplesoft PANEL) and other should go to specific file location. Contents in both reports are not same. I tried NEW-REPORT and also tried USE-REPORT . Now trying both in same program , lets see how it goes....... Please give your suggestions. Please Help.... Thanks AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 13 17:39:58 2004 Date: Tue, 13 Jan 2004 17:38:28 -0500 From: Matt Rogish Subject: [sqr-users] Having trouble with negative line positioning Hi all, I have a list of donations with attached user name records that I'm grabbing in one query, so the output looks like: $123,555 Matt Cash $12,000,000 Bob Cash $12,000 Joe Credit Card $14,000 Happy Credit Card $15,000 Gilmore Credit Card etc. *Note the order by is type (cash, credit card, etc.) and then dollar amount. What I'd like to do is add the multi-line (it's from a DBMS table) description of the type to the output in a third column. So my sample output would look something like: $123,555 Matt Cash Cash is usually collected on Fridays $12,000,000 Bob Cash and is delivered to the bank in an armored car. Note that the driver is armed with a bazooka, so attempting a robbery would be ill-advised. $12,000 Joe Credit Card Credit Card payments are handled instantly $14,000 Happy Credit Card via our supplier. $15,000 Gilmore Credit Card I was thinking I could do something like this: let #num_in_type = 0 begin-select dollar_amount name type on-break print=never after=get_type_description add 1 to #num_in_type from foo ... end-select procedure get_type_description position (-#num_in_type ) let #num_in_type = 0 end-procedure There are two things wrong with this. First off, I can't use negative positioning with any sort of a variable. If I use position (-#num_in_type) I get: (SQR 4501) Use '+' and negate variable for reverse relative placement. -#num_in_type Ok, so I changed it to be: let #num_in_type = #num_in_type * -1 position (#num_in_type) Which, unsurprisingly, does not work either: (SQR 4503) Fixed line placement #num_in_type must be > 0. Use relative positioning, (+#line,10,0). Is there no way to dynamically use position with a negative variable? I can do position (-1) just fine! Secondly, if the description for 'cash' is more rows than the number of cash items, then it bleeds into the description for credit card, so I'd need a way to create whitespace in between cash and credit card as shown in the example. I can't seem to make this work. Any ideas!? Thanks, -- Matt Rogish - rogishmn@muohio.edu Programmer Advancement Services Miami University 513.529.1420 Do not meddle in the affairs of SQR, for it is subtle and quick to anger! _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 13 17:52:06 2004 From: Ray Ontko Date: Tue, 13 Jan 2004 17:51:31 -0500 Subject: Re: [sqr-users] Having trouble with negative line positioning Matt, Use this: let #num_in_type = #num_in_type * -1 position (+#num_in_type) Also, you may be interested in the KEEP-TOP modifier for PRINT WRAP. Ray On Tue, Jan 13, 2004 at 05:38:28PM -0500, Matt Rogish wrote: > Hi all, > > I have a list of donations with attached user name records that I'm > grabbing in one query, so the output looks like: > $123,555 Matt Cash > $12,000,000 Bob Cash > $12,000 Joe Credit Card > $14,000 Happy Credit Card > $15,000 Gilmore Credit Card > etc. > *Note the order by is type (cash, credit card, etc.) and then dollar amount. > > What I'd like to do is add the multi-line (it's from a DBMS table) > description of the type to the output in a third column. So my sample > output would look something like: > $123,555 Matt Cash Cash is usually collected on Fridays > $12,000,000 Bob Cash and is delivered to the bank in an > armored car. Note that the driver is > armed with a bazooka, > so attempting a robbery would be > ill-advised. > $12,000 Joe Credit Card Credit Card payments are handled instantly > $14,000 Happy Credit Card via our supplier. > $15,000 Gilmore Credit Card > > I was thinking I could do something like this: > > let #num_in_type = 0 > > begin-select > dollar_amount > name > type on-break print=never after=get_type_description > > add 1 to #num_in_type > > from foo > ... > end-select > > procedure get_type_description > > position (-#num_in_type ) > let #num_in_type = 0 > > end-procedure > > There are two things wrong with this. First off, I can't use negative > positioning with any sort of a variable. If I use position (-#num_in_type) > I get: > (SQR 4501) Use '+' and negate variable for reverse relative placement. > -#num_in_type > > Ok, so I changed it to be: > let #num_in_type = #num_in_type * -1 > position (#num_in_type) > > Which, unsurprisingly, does not work either: > (SQR 4503) Fixed line placement #num_in_type must be > 0. Use relative > positioning, (+#line,10,0). > > Is there no way to dynamically use position with a negative variable? I > can do position (-1) just fine! > > Secondly, if the description for 'cash' is more rows than the number of > cash items, then it bleeds into the description for credit card, so I'd > need a way to create whitespace in between cash and credit card as shown in > the example. I can't seem to make this work. Any ideas!? > > > Thanks, > > -- > Matt Rogish - rogishmn@muohio.edu > Programmer > Advancement Services > Miami University > 513.529.1420 > > Do not meddle in the affairs of SQR, > for it is subtle and quick to anger! > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users ---------------------------------------------------------------------- Ray Ontko rayo@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 14 07:54:57 2004 Date: Wed, 14 Jan 2004 07:53:24 -0500 From: Matt Rogish Subject: Re: [sqr-users] Having trouble with negative line positioning Ray, Thanks for the reply -- it works! The only problem I have now is when I have so many rows that it splits over a page: (SQR 5402) String cannot be placed on page: -- placement specified is out of range. (-24,0,0) The line number it specifies is the "position (+#num_in_type)". So obviously the text needs to start on the previous page, but I can't find anything like 'previous-page' in the docs. I think I might be sunk and have to include it later on in the report as a legend or a footnote, although we like the idea of having it off to the right. >From: Ray Ontko >Date: Tue, 13 Jan 2004 17:51:31 -0500 >To: sqr-users@sqrug.org >Subject: Re: [sqr-users] Having trouble with negative line positioning > >Matt, > >Use this: > let #num_in_type = #num_in_type * -1 > position (+#num_in_type) > >Also, you may be interested in the KEEP-TOP modifier for PRINT WRAP. > >Ray Thanks, -- Matt Rogish - rogishmn@muohio.edu Programmer Advancement Services Miami University 513.529.1420 Do not meddle in the affairs of SQR, for it is subtle and quick to anger! _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 14 08:30:13 2004 From: Ray Ontko Date: Wed, 14 Jan 2004 08:29:00 -0500 Subject: Re: [sqr-users] Having trouble with negative line positioning Matt, For that you'll want to use a function called wrapdepth() to determine how many lines the wrap text will require before you attempt to print it. You may also wish to write a procedure called wrapsplit() that splits the wrap text into two pieces: what will fit in a specified depth, and what must be deferred to the next page (or pages). Ray On Wed, Jan 14, 2004 at 07:53:24AM -0500, Matt Rogish wrote: > Ray, > > Thanks for the reply -- it works! > > The only problem I have now is when I have so many rows that it splits over > a page: > (SQR 5402) String cannot be placed on page: > -- placement specified is out of range. (-24,0,0) > > The line number it specifies is the "position (+#num_in_type)". > > So obviously the text needs to start on the previous page, but I can't find > anything like 'previous-page' in the docs. I think I might be sunk and > have to include it later on in the report as a legend or a footnote, > although we like the idea of having it off to the right. > > >From: Ray Ontko > >Date: Tue, 13 Jan 2004 17:51:31 -0500 > >To: sqr-users@sqrug.org > >Subject: Re: [sqr-users] Having trouble with negative line positioning > > > >Matt, > > > >Use this: > > let #num_in_type = #num_in_type * -1 > > position (+#num_in_type) > > > >Also, you may be interested in the KEEP-TOP modifier for PRINT WRAP. > > > >Ray > > Thanks, > > -- > Matt Rogish - rogishmn@muohio.edu > Programmer > Advancement Services > Miami University > 513.529.1420 > > Do not meddle in the affairs of SQR, > for it is subtle and quick to anger! > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users ---------------------------------------------------------------------- Ray Ontko rayo@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 14 13:24:42 2004 From: AmiPatel@loews.com Date: Wed, 14 Jan 2004 13:22:23 -0500 Subject: [sqr-users] SPF file question I am creating SPF file with -KEEP flag. but for some reason it is giving me all junk in SPF file. Its not formatted and readable. .LIS file is OK . How can somebody print SPF file? and what exactly we use it for? AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 14 13:32:11 2004 Subject: RE: [sqr-users] Multiple Reports - one to printer and one to specific location. From: AmiPatel@loews.com Date: Wed, 14 Jan 2004 13:31:02 -0500 Thank you Richard , It looks like your idea will work. I tried your idea but with .LIS reports. I created 2 .lis reports and send one report to printer with CALL command at the end of report, but in that case it is printing report from previous run , because current reports is not ready yet I guess. Is that anything we can do to overcome this problem ....? As last option I will write to PRN files , close it and print using CALL . I am not sure that you can print PRN file to different printer without re-formatting it again or not... ? I am trying it out but if you know please help..... Thanks My solution to this is to create a print file with write commands, close the file and then send the operating system command (via call system) to print the file. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of AmiPatel@loews.com Sent: Tuesday, January 13, 2004 8:26 AM To: sqr-users@sqrug.org Subject: [sqr-users] Multiple Reports - one to printer and one to specific location. Hello , I want to create 2 reports out of my SQR program , one should go to printer or file ( depending upon whatever is selected from peoplesoft PANEL) and other should go to specific file location. Contents in both reports are not same. I tried NEW-REPORT and also tried USE-REPORT . Now trying both in same program , lets see how it goes....... Please give your suggestions. Please Help.... Thanks AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 14 13:40:16 2004 Date: Wed, 14 Jan 2004 13:39:12 -0500 From: "Larry Roux" Subject: Re: [sqr-users] SPF file question You have to use the SPF viewer to read it (supplied with your SQR installation. Look for SQRVW.exe. The SPF file shows the formatted output where the Lis file is the raw data with the printer codes etc thrown in. ****************************** Larry Roux Syracuse University lroux@syr.edu ******************************* >>> AmiPatel@loews.com 01/14/04 01:22PM >>> I am creating SPF file with -KEEP flag. but for some reason it is giving me all junk in SPF file. Its not formatted and readable. .LIS file is OK . How can somebody print SPF file? and what exactly we use it for? AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 14 14:25:26 2004 Subject: RE: [sqr-users] Multiple Reports - one to printer and one to specific location. Date: Wed, 14 Jan 2004 13:24:24 -0600 From: "Knapp, Richard" I would name the print files something like xxxxx.txt so as not to get confused with the .lis files SQR is producing. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of AmiPatel@loews.com Sent: Wednesday, January 14, 2004 12:31 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Multiple Reports - one to printer and one to specific location. Thank you Richard , It looks like your idea will work. I tried your idea but with .LIS reports. I created 2 .lis reports and send one report to printer with CALL command at the end of report, but in that case it is printing report from previous run , because current reports is not ready yet I guess. Is that anything we can do to overcome this problem ....? As last option I will write to PRN files , close it and print using CALL . I am not sure that you can print PRN file to different printer without re-formatting it again or not... ? I am trying it out but if you know please help..... Thanks My solution to this is to create a print file with write commands, close the file and then send the operating system command (via call system) to print the file. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of AmiPatel@loews.com Sent: Tuesday, January 13, 2004 8:26 AM To: sqr-users@sqrug.org Subject: [sqr-users] Multiple Reports - one to printer and one to specific location. Hello , I want to create 2 reports out of my SQR program , one should go to printer or file ( depending upon whatever is selected from peoplesoft PANEL) and other should go to specific file location. Contents in both reports are not same. I tried NEW-REPORT and also tried USE-REPORT . Now trying both in same program , lets see how it goes....... Please give your suggestions. Please Help.... Thanks AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 14 22:44:00 2004 Subject: RE: [sqr-users] SPF file question Date: Thu, 15 Jan 2004 11:41:28 +0800 From: v.prabhakaran@accenture.com This is a multi-part message in MIME format... ------_=_NextPart_001_01C3DB19.75483BB1 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="iso-8859-1" Hi, =20 We can publish an spf file by mentioning different output types that we req= uired. I mean, if our requirement is to generate pdf out, csv output only,= while publishing an spf file, we can mention true for pdf and csv (refer = one api to loadspfset) only and we do not need to generate and publish the = pdf and csv separately. =20 The above way of publishing the spf and the users can view the brio portal = page for this report with the required output type ... meaning... in the re= ports page.. will show the output line with pdf and csv only.=20=20 =20 Does this answer your q? =20 cheers prabha =20 =20 -----Original Message-----=20 From: sqr-users-admin@sqrug.org on behalf of AmiPatel@loews.com=20 Sent: Thu 1/15/2004 2:22 AM=20 To: sqr-users@sqrug.org=20 Cc:=20 Subject: [sqr-users] SPF file question =09 =09 I am creating SPF file with -KEEP flag. but for some reason it is giving me all junk in SPF file. Its not formatted and readable. .LIS file is OK . How can somebody print SPF file? and what exactly we use it for? =09 AMI PATEL =09 =09 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users =09 This message is for the designated recipient only and may contain privilege= d, proprietary, or otherwise private information. If you have received it = in error, please notify the sender immediately and delete the original. An= y other use of the email by you is prohibited. ------_=_NextPart_001_01C3DB19.75483BB1 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'application/ms-tnef' MIME type. It has been automatically discarded before sending the message to the list. ------_=_NextPart_001_01C3DB19.75483BB1-- _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 09:04:58 2004 Date: Thu, 15 Jan 2004 09:03:27 -0500 From: "White, Denise" Subject: [sqr-users] RE: Multiple Reports - one to printer and one to specific I would try issuing a NEW-REPORT just before your CALL command. When you are writing a report, it actually writes an .spf file and then when the program ends, converts it to a .lis file and deletes the .spf (unless you use a flag to keep it). Therefore, the .lis file is not available until the program has completed. When you use a NEW-REPORT, it immediately closes the current report and creates the .lis file for it, making it available before the end of the program. Since you will not be writing anything to the new report, you do not need to specify a name for it. HTH, Denise M. White Sr. Software Engineer Vicor --__--__-- Message: 4 Subject: RE: [sqr-users] Multiple Reports - one to printer and one to specific location. To: sqr-users@sqrug.org From: AmiPatel@loews.com Date: Wed, 14 Jan 2004 13:31:02 -0500 Reply-To: sqr-users@sqrug.org Thank you Richard , It looks like your idea will work. I tried your idea but with .LIS reports. I created 2 .lis reports and send one report to printer with CALL command at the end of report, but in that case it is printing report from previous run , because current reports is not ready yet I guess. Is that anything we can do to overcome this problem ....? As last option I will write to PRN files , close it and print using CALL . I am not sure that you can print PRN file to different printer without re-formatting it again or not... ? I am trying it out but if you know please help..... Thanks _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 09:36:19 2004 Subject: Re: [sqr-users] RE: Multiple Reports - one to printer and one to specific From: AmiPatel@loews.com Date: Thu, 15 Jan 2004 09:35:30 -0500 Denise , you are so right, I tried and it worked .... !!! and report with New-Report is just dummy and has nothing in it so it did not even created that. Just perfect. I combined yours and Richards solution. Its working for me. One more change I made is I included setup32.sqc instead of setup02.sqc , b/c 32 has all declare statements that is compatible with Declare Report in my program. Thanks very much all of you... AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 10:41:27 2004 From: AmiPatel@loews.com Date: Thu, 15 Jan 2004 10:40:04 -0500 Subject: [sqr-users] Call system command question Hello friends, I am using following code from my SQR program & it looks like its working fine on my machine. Do you see any harm if program runs from scheduler on server....I am not sure about "cmd /c @start /min /wait cmd /c" part.. I will change my printer accordingly. I have no knowledge for this commands... let $cmd = 'cmd /c @start /min /wait cmd /c "copy /b c:\winnt\temp\sal0110t.lis lpt1"' let $cmd1 = 'cmd /c @start /min /wait cmd /c "move /y c:\winnt\temp\sal0110t_01.lis c:\sal0110t_moved.lis"' Call System using $cmd #status call system using $cmd1 #status1 AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 11:04:11 2004 Subject: RE: [sqr-users] Call system command question Date: Thu, 15 Jan 2004 10:03:10 -0600 From: "Knapp, Richard" Those commands will only work for a standalone windows OS (your desktop machine). You will need to change the commands and the file locations depending on the OS of your server Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of AmiPatel@loews.com Sent: Thursday, January 15, 2004 9:40 AM To: sqr-users@sqrug.org Subject: [sqr-users] Call system command question Hello friends, I am using following code from my SQR program & it looks like its working fine on my machine. Do you see any harm if program runs from scheduler on server....I am not sure about "cmd /c @start /min /wait cmd /c" part.. I will change my printer accordingly. I have no knowledge for this commands... let $cmd = 'cmd /c @start /min /wait cmd /c "copy /b c:\winnt\temp\sal0110t.lis lpt1"' let $cmd1 = 'cmd /c @start /min /wait cmd /c "move /y c:\winnt\temp\sal0110t_01.lis c:\sal0110t_moved.lis"' Call System using $cmd #status call system using $cmd1 #status1 AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 11:38:07 2004 Date: Thu, 15 Jan 2004 10:37:14 -0600 From: "Hallmark, Robert J" Subject: [sqr-users] Printing Vertically Brio 6.0 Oracle 8i Database UNIX Server Is there any way have a portion of an SQR report, such as column headings, to print vertically? Thanks Bob H _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 11:46:10 2004 Subject: RE: [sqr-users] Printing Vertically Date: Thu, 15 Jan 2004 10:44:57 -0600 From: "Knapp, Richard" Carefully work it out line by line? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Hallmark, Robert J Sent: Thursday, January 15, 2004 10:37 AM To: 'sqr-users@sqrug.org' Subject: [sqr-users] Printing Vertically Brio 6.0 Oracle 8i Database UNIX Server Is there any way have a portion of an SQR report, such as column headings, to print vertically? Thanks Bob H _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 12:19:17 2004 Date: Thu, 15 Jan 2004 12:18:37 -0500 From: "Steve Wowelko" Subject: Re: [sqr-users] Printing Vertically Bob, If you have your printer-type defined as hp you can send escape sequences to the printer to rotate text. Here are some snippets from one of my programs that do column headings vertically. Stephen Wowelko Information Systems Syracuse University .. .. .. !---------------- print rotated font ---------------- alter-printer point-size=9 font=5 encode '<27>&a90P' into $rotatefont Print-Direct Nolf Printer = hp $rotatefont ! rotate text to 90 degrees print 'Completely' (32,26) print 'Satisfactory' (33,26) print 'Attendance' (36,26) print 'Participation' (39,26) print 'Assignments' (42,26) print 'Exams/Quizzes' (45,26) print 'Mid-Semester Grade' (48,26) print '(optional)' (49,26) !---------------- end print rotated font ---------------- .. .. .. encode '<27>&a0P' into $rotatefont Print-Direct Nolf Printer = hp $rotatefont ! rotate text back to 0 new-page ! done with this back page .. .. .. >>> robert.j.hallmark@lmco.com 01/15/04 11:37AM >>> Brio 6.0 Oracle 8i Database UNIX Server Is there any way have a portion of an SQR report, such as column headings, to print vertically? Thanks Bob H _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 12:21:05 2004 From: AmiPatel@loews.com Date: Thu, 15 Jan 2004 12:18:59 -0500 Subject: [sqr-users] Multiple reports using use-Reports , 2nd report going to SQR directory Hello , I am running SQR reports from PS Panel . 1) When I select 'CLIENT' and selecting 'FILE' (Specifying %%temp%% in file box). Two reports are available in TEMP of my machine - SAL0110.lis and SAL0110_01.lis which is good. 2) But when I select 'SERVER' and selecting 'FILE' (Specifying %%temp%% in file box) . My first report SAL1_1234.lis(1234 is automatic counter..when any lis file is created ) is getting created in TEMP directory on server. and My second report SAL1_01.lis is going to directory where my SQR program is sitting. I want it to go to same location as first one... Thanks in advance for any input...Please help... AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 12:28:08 2004 From: Al H Date: Thu, 15 Jan 2004 09:28:28 -0800 Subject: [sqr-users] #sql-status and unique constraint Has anyone encountered issues with testing #sql-status for unique constraint and not getting correct results? Oracle's return code for unique contstraint is -9. I've found that for some reason, #sql-status doesn't always contain this value. It seems to be inconsistant, as I ran one program today that had repeated unique constraint errors and it worked for awhile, then suddenly failed (the program is supposed to ignore unique constraint errors and abort on an error other than unique constraint). I have found a more dependable alternative in using an instr for the value of "unique constraint" in $sql-error, but it is mystifying to me why the #sql-status seems to be unreliable. Any ideas? Note this is an SQR 4.x installation, on Oracle 8. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 12:23:06 2004 From: "Paul Hoyte" Date: Thu, 15 Jan 2004 10:29:02 -0700 Subject: [sqr-users] SQR Graphs on UNIX... This is a multi-part message in MIME format... ------=_NextPart_000_0007_01C3DB52.6527BC50 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello all: Would like to know if anyone is publishing graphs using sqr on a UNIX platform. Looks like the graphs are being squeezed vertically the more months that are requested. Has anyone experienced this and come up with a work around? Thanks for the help. Paul Hoyte Sharidionne Inc. (248) 752-6970 paul.hoyte@sharidionne.com ------=_NextPart_000_0007_01C3DB52.6527BC50 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'image/gif' MIME type. It has been automatically discarded before sending the message to the list. ------=_NextPart_000_0007_01C3DB52.6527BC50-- _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 13:16:19 2004 Date: Thu, 15 Jan 2004 12:14:17 -0600 From: "Hallmark, Robert J" Subject: RE: [sqr-users] Printing Vertically Stephen, Thanks a lot. That did the trick. Bob H ************************************ Bob, If you have your printer-type defined as hp you can send escape sequences to the printer to rotate text. Here are some snippets from one of my programs that do column headings vertically. Stephen Wowelko Information Systems Syracuse University .. .. .. !---------------- print rotated font ---------------- alter-printer point-size=9 font=5 encode '<27>&a90P' into $rotatefont Print-Direct Nolf Printer = hp $rotatefont ! rotate text to 90 degrees print 'Completely' (32,26) print 'Satisfactory' (33,26) print 'Attendance' (36,26) print 'Participation' (39,26) print 'Assignments' (42,26) print 'Exams/Quizzes' (45,26) print 'Mid-Semester Grade' (48,26) print '(optional)' (49,26) !---------------- end print rotated font ---------------- .. .. .. encode '<27>&a0P' into $rotatefont Print-Direct Nolf Printer = hp $rotatefont ! rotate text back to 0 new-page ! done with this back page .. .. .. >>> robert.j.hallmark@lmco.com 01/15/04 11:37AM >>> Brio 6.0 Oracle 8i Database UNIX Server Is there any way have a portion of an SQR report, such as column headings, to print vertically? Thanks Bob H _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 15:08:55 2004 Subject: Re: [sqr-users] Multiple reports using use-Reports , 2nd report going to SQR directory From: Ramzi_Salameh@daveandbusters.com Date: Thu, 15 Jan 2004 14:06:32 -0600 When you run the SQR on the client it's doing just that - being ran on the client (or local) machine, so the output is going to the local temp directory. When you run it on the server it's sending the output to the temp directory on the server because the SQR is being ran on THAT machine (the server machine). It doesn't know about your local temp directory. A solution is to specify a network folder in your output parameter whenever your run it on the server. Hope that makes sense. Ramzi AmiPatel@loews.co m To: sqr-users@sqrug.org Sent by: cc: sqr-users-admin@s Subject: [sqr-users] Multiple reports using use-Reports , 2nd report going to SQR qrug.org directory 01/15/04 11:18 AM Please respond to sqr-users Hello , I am running SQR reports from PS Panel . 1) When I select 'CLIENT' and selecting 'FILE' (Specifying %%temp%% in file box). Two reports are available in TEMP of my machine - SAL0110.lis and SAL0110_01.lis which is good. 2) But when I select 'SERVER' and selecting 'FILE' (Specifying %%temp%% in file box) . My first report SAL1_1234.lis(1234 is automatic counter..when any lis file is created ) is getting created in TEMP directory on server. and My second report SAL1_01.lis is going to directory where my SQR program is sitting. I want it to go to same location as first one... Thanks in advance for any input...Please help... AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 15:13:07 2004 Date: Thu, 15 Jan 2004 15:12:42 -0500 From: "White, Denise" Subject: [sqr-users] Weird problem with load-lookup Hi, This makes no sense at all. I have a program that has been running in production every weeknight since late October, and as of last Friday, it will no longer run. I have managed to narrow the problem down to a load-lookup (when I remove the load-lookup, it runs successfully). The job is run in PeopleSoft and retrieves data from ADP through a database link. When the job is run from a PeopleSoft panel, it remains in Initiated status and never runs. When I run it from SQRW, I get the icon for SQR in my task bar, but the DOS window never appears (it should appear with that message about hitting enter without entering anything that is standard for PSoft jobs and is generated by one of the copybooks). Because of this, I am assuming that the job is failing to compile, and so never gets to the point where it starts to run. I have made no modifications to the program recently. I have confirmed that the database link to ADP works, and that I can do a regular begin-select of the same data using it and successfully display the data retrieved. Does anyone have any idea why my load-lookup would suddenly fail to compile, when it has been doing so successfully for months? Here is the code: BEGIN-SETUP #Include 'setup01a.sqc' ! Load shift differentials from ADP database load-lookup name=shift_differential table=reports.v_employee@pay4win key=file# return_value=substr(datacontrol,4,1) rows=9999 where='status in (''A'',''L'')' END-SETUP I guess I will have to give up and rewrite it using an array, since I find I can still select data with the database link, but I hate to admit defeat! (SQR 4.3.2) TIA! Denise M. White Sr. Software Engineer Vicor _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 15:43:19 2004 Subject: RE: [sqr-users] Weird problem with load-lookup Date: Thu, 15 Jan 2004 14:42:15 -0600 From: "Knapp, Richard" The 'stuck in initiated' sounds like the process scheduler is not running. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of White, Denise Sent: Thursday, January 15, 2004 2:13 PM To: SQR Users Group (E-mail) Subject: [sqr-users] Weird problem with load-lookup Hi, This makes no sense at all. I have a program that has been running in production every weeknight since late October, and as of last Friday, it will no longer run. I have managed to narrow the problem down to a load-lookup (when I remove the load-lookup, it runs successfully). The job is run in PeopleSoft and retrieves data from ADP through a database link. When the job is run from a PeopleSoft panel, it remains in Initiated status and never runs. When I run it from SQRW, I get the icon for SQR in my task bar, but the DOS window never appears (it should appear with that message about hitting enter without entering anything that is standard for PSoft jobs and is generated by one of the copybooks). Because of this, I am assuming that the job is failing to compile, and so never gets to the point where it starts to run. I have made no modifications to the program recently. I have confirmed that the database link to ADP works, and that I can do a regular begin-select of the same data using it and successfully display the data retrieved. Does anyone have any idea why my load-lookup would suddenly fail to compile, when it has been doing so successfully for months? Here is the code: BEGIN-SETUP #Include 'setup01a.sqc' ! Load shift differentials from ADP database load-lookup name=shift_differential table=reports.v_employee@pay4win key=file# return_value=substr(datacontrol,4,1) rows=9999 where='status in (''A'',''L'')' END-SETUP I guess I will have to give up and rewrite it using an array, since I find I can still select data with the database link, but I hate to admit defeat! (SQR 4.3.2) TIA! Denise M. White Sr. Software Engineer Vicor _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 15:52:06 2004 Subject: RE: [sqr-users] Multiple reports using use-Reports , 2nd report going to SQR directory Date: Thu, 15 Jan 2004 14:51:04 -0600 From: "Knapp, Richard" I've had this problem. The only solution I could find was to modify pssqr.ini. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of AmiPatel@loews.com Sent: Thursday, January 15, 2004 11:19 AM To: sqr-users@sqrug.org Subject: [sqr-users] Multiple reports using use-Reports , 2nd report going to SQR directory Hello , I am running SQR reports from PS Panel . 1) When I select 'CLIENT' and selecting 'FILE' (Specifying %%temp%% in file box). Two reports are available in TEMP of my machine - SAL0110.lis and SAL0110_01.lis which is good. 2) But when I select 'SERVER' and selecting 'FILE' (Specifying %%temp%% in file box) . My first report SAL1_1234.lis(1234 is automatic counter..when any lis file is created ) is getting created in TEMP directory on server. and My second report SAL1_01.lis is going to directory where my SQR program is sitting. I want it to go to same location as first one... Thanks in advance for any input...Please help... AMI PATEL _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 15:53:05 2004 Date: Thu, 15 Jan 2004 15:52:32 -0500 From: "Larry Roux" Subject: RE: [sqr-users] Weird problem with load-lookup Yes, I agree 100% with this one. Either the process scheduler is stuck or not running. ****************************** Larry Roux Syracuse University lroux@syr.edu ******************************* >>> KnappR@umsystem.edu 01/15/04 03:42PM >>> The 'stuck in initiated' sounds like the process scheduler is not running. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of White, Denise Sent: Thursday, January 15, 2004 2:13 PM To: SQR Users Group (E-mail) Subject: [sqr-users] Weird problem with load-lookup Hi, This makes no sense at all. I have a program that has been running in production every weeknight since late October, and as of last Friday, it will no longer run. I have managed to narrow the problem down to a load-lookup (when I remove the load-lookup, it runs successfully). The job is run in PeopleSoft and retrieves data from ADP through a database link. When the job is run from a PeopleSoft panel, it remains in Initiated status and never runs. When I run it from SQRW, I get the icon for SQR in my task bar, but the DOS window never appears (it should appear with that message about hitting enter without entering anything that is standard for PSoft jobs and is generated by one of the copybooks). Because of this, I am assuming that the job is failing to compile, and so never gets to the point where it starts to run. I have made no modifications to the program recently. I have confirmed that the database link to ADP works, and that I can do a regular begin-select of the same data using it and successfully display the data retrieved. Does anyone have any idea why my load-lookup would suddenly fail to compile, when it has been doing so successfully for months? Here is the code: BEGIN-SETUP #Include 'setup01a.sqc' ! Load shift differentials from ADP database load-lookup name=shift_differential table=reports.v_employee@pay4win key=file# return_value=substr(datacontrol,4,1) rows=9999 where='status in (''A'',''L'')' END-SETUP I guess I will have to give up and rewrite it using an array, since I find I can still select data with the database link, but I hate to admit defeat! (SQR 4.3.2) TIA! Denise M. White Sr. Software Engineer Vicor _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 16:26:25 2004 From: "Lynds,Rick" Subject: RE: [sqr-users] Weird problem with load-lookup Date: Thu, 15 Jan 2004 13:25:11 -0800 Denise, I might try doing some things like... 1. Using -s and praying that something shows up on the log - especially the generated SQL 2. Placing quotes around the table name (why that would become a problem, I have NO idea) 3. Having a DBA check to see if there is activity on the database while it is running. 4. Changing the number or rows (another one I can't see having an effect) This seems weird - Good Luck! Rick -----Original Message----- From: White, Denise [mailto:DEWhite@vicr.com] Sent: Thursday, January 15, 2004 12:13 PM To: SQR Users Group (E-mail) Subject: [sqr-users] Weird problem with load-lookup Hi, This makes no sense at all. I have a program that has been running in production every weeknight since late October, and as of last Friday, it will no longer run. I have managed to narrow the problem down to a load-lookup (when I remove the load-lookup, it runs successfully). The job is run in PeopleSoft and retrieves data from ADP through a database link. When the job is run from a PeopleSoft panel, it remains in Initiated status and never runs. When I run it from SQRW, I get the icon for SQR in my task bar, but the DOS window never appears (it should appear with that message about hitting enter without entering anything that is standard for PSoft jobs and is generated by one of the copybooks). Because of this, I am assuming that the job is failing to compile, and so never gets to the point where it starts to run. I have made no modifications to the program recently. I have confirmed that the database link to ADP works, and that I can do a regular begin-select of the same data using it and successfully display the data retrieved. Does anyone have any idea why my load-lookup would suddenly fail to compile, when it has been doing so successfully for months? Here is the code: BEGIN-SETUP #Include 'setup01a.sqc' ! Load shift differentials from ADP database load-lookup name=shift_differential table=reports.v_employee@pay4win key=file# return_value=substr(datacontrol,4,1) rows=9999 where='status in (''A'',''L'')' END-SETUP I guess I will have to give up and rewrite it using an array, since I find I can still select data with the database link, but I hate to admit defeat! (SQR 4.3.2) TIA! Denise M. White Sr. Software Engineer Vicor _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 16:28:06 2004 Date: Thu, 15 Jan 2004 16:26:27 -0500 From: "George Jansen" Subject: Re: [sqr-users] #sql-status and unique constraint Why would you want to ignore such errors? It is straightforward to encapsulate your DDL in PL/SQL code that will insert OR update. >>> maillists@starbuckk.net 01/15/04 12:28PM >>> Has anyone encountered issues with testing #sql-status for unique constraint and not getting correct results? Oracle's return code for unique contstraint is -9. I've found that for some reason, #sql-status doesn't always contain this value. It seems to be inconsistant, as I ran one program today that had repeated unique constraint errors and it worked for awhile, then suddenly failed (the program is supposed to ignore unique constraint errors and abort on an error other than unique constraint). I have found a more dependable alternative in using an instr for the value of "unique constraint" in $sql-error, but it is mystifying to me why the #sql-status seems to be unreliable. Any ideas? Note this is an SQR 4.x installation, on Oracle 8. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 17:02:07 2004 Subject: RE: [sqr-users] Weird problem with load-lookup From: rwulfson@bost.com Date: Thu, 15 Jan 2004 15:05:11 -0700 I would suspect that something has changed with dblink and that this is either not connecting or really slow. Thanks, Russ Wulfson Developer Boston Market Corporation Office: 303-216-5614 Fax: 303 -216- 8727 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 15 22:47:53 2004 From: Al H Subject: Re: [sqr-users] #sql-status and unique constraint Date: Thu, 15 Jan 2004 19:48:10 -0800 To reduce database hits and network traffic. 99% of the time, there will NOT be a record out there. But out of maybe 200,000 records, I might have 5 or six repeats. Using a query ahead of time, or a "where not exists" subquiery in this particular case simply generates unnecessary database traffic. On Thu, 15 Jan 2004 16:26:27 -0500, you wrote: >Why would you want to ignore such errors? > >It is straightforward to encapsulate your DDL in PL/SQL code that will >insert OR update. > >>>> maillists@starbuckk.net 01/15/04 12:28PM >>> >Has anyone encountered issues with testing #sql-status for unique >constraint and not getting correct results? > >Oracle's return code for unique contstraint is -9. I've found that for >some reason, #sql-status doesn't always contain this value. It seems >to be inconsistant, as I ran one program today that had repeated >unique constraint errors and it worked for awhile, then suddenly >failed (the program is supposed to ignore unique constraint errors and >abort on an error other than unique constraint). > >I have found a more dependable alternative in using an instr for the >value of "unique constraint" in $sql-error, but it is mystifying to me >why the #sql-status seems to be unreliable. > >Any ideas? > >Note this is an SQR 4.x installation, on Oracle 8. > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 16 05:22:05 2004 From: "Steve Cavill" Subject: RE: [sqr-users] Weird problem with load-lookup Date: Fri, 16 Jan 2004 21:19:35 +1100 Hi Denise, you should try the exact select that the load-lookup is trying: begin-select loops=9999 file# (1,1,10) substr(datacontrol,4,1) () from reports.v_employee@pay4win where status in ('A','L') end-select and see what happens. Steve. -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of White, Denise Sent: Friday, 16 January 2004 7:13 AM To: SQR Users Group (E-mail) Subject: [sqr-users] Weird problem with load-lookup Hi, This makes no sense at all. I have a program that has been running in production every weeknight since late October, and as of last Friday, it will no longer run. I have managed to narrow the problem down to a load-lookup (when I remove the load-lookup, it runs successfully). The job is run in PeopleSoft and retrieves data from ADP through a database link. When the job is run from a PeopleSoft panel, it remains in Initiated status and never runs. When I run it from SQRW, I get the icon for SQR in my task bar, but the DOS window never appears (it should appear with that message about hitting enter without entering anything that is standard for PSoft jobs and is generated by one of the copybooks). Because of this, I am assuming that the job is failing to compile, and so never gets to the point where it starts to run. I have made no modifications to the program recently. I have confirmed that the database link to ADP works, and that I can do a regular begin-select of the same data using it and successfully display the data retrieved. Does anyone have any idea why my load-lookup would suddenly fail to compile, when it has been doing so successfully for months? Here is the code: BEGIN-SETUP #Include 'setup01a.sqc' ! Load shift differentials from ADP database load-lookup name=shift_differential table=reports.v_employee@pay4win key=file# return_value=substr(datacontrol,4,1) rows=9999 where='status in (''A'',''L'')' END-SETUP I guess I will have to give up and rewrite it using an array, since I find I can still select data with the database link, but I hate to admit defeat! (SQR 4.3.2) TIA! Denise M. White Sr. Software Engineer Vicor _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 16 09:10:55 2004 Subject: RE: [sqr-users] Weird problem with load-lookup Date: Fri, 16 Jan 2004 09:08:51 -0500 From: "White, Denise" Thanks Richard and Larry, the process scheduler had actually gone down when this problem first started, but it has since been restored and the job still would not run. All other jobs, both scheduled and ad hoc, are running successfully on the process scheduler. Also, that would not explain why the same thing happens with SQRW, since that does not use the process scheduler at all. I should also say that I have the same problem whether running on the server or the client. Denise M. White Sr. Software Engineer Vicor --__--__-- Message: 16 Date: Thu, 15 Jan 2004 15:52:32 -0500 From: "Larry Roux" To: , Subject: RE: [sqr-users] Weird problem with load-lookup Reply-To: sqr-users@sqrug.org Yes, I agree 100% with this one. Either the process scheduler is stuck or not running. ****************************** Larry Roux Syracuse University lroux@syr.edu ******************************* >>> KnappR@umsystem.edu 01/15/04 03:42PM >>> The 'stuck in initiated' sounds like the process scheduler is not running. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of White, Denise Sent: Thursday, January 15, 2004 2:13 PM To: SQR Users Group (E-mail) Subject: [sqr-users] Weird problem with load-lookup Hi, This makes no sense at all. I have a program that has been running in production every weeknight since late October, and as of last Friday, it will no longer run. I have managed to narrow the problem down to a load-lookup (when I remove the load-lookup, it runs successfully). The job is run in PeopleSoft and retrieves data from ADP through a database link. When the job is run from a PeopleSoft panel, it remains in Initiated status and never runs. When I run it from SQRW, I get the icon for SQR in my task bar, but the DOS window never appears (it should appear with that message about hitting enter without entering anything that is standard for PSoft jobs and is generated by one of the copybooks). Because of this, I am assuming that the job is failing to compile, and so never gets to the point where it starts to run. I have made no modifications to the program recently. I have confirmed that the database link to ADP works, and that I can do a regular begin-select of the same data using it and successfully display the data retrieved. Does anyone have any idea why my load-lookup would suddenly fail to compile, when it has been doing so successfully for months? Here is the code: BEGIN-SETUP #Include 'setup01a.sqc' ! Load shift differentials from ADP database load-lookup name=shift_differential table=reports.v_employee@pay4win key=file# return_value=substr(datacontrol,4,1) rows=9999 where='status in (''A'',''L'')' END-SETUP I guess I will have to give up and rewrite it using an array, since I find I can still select data with the database link, but I hate to admit defeat! (SQR 4.3.2) TIA! Denise M. White Sr. Software Engineer Vicor _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 16 09:14:30 2004 Subject: RE: [sqr-users] Weird problem with load-lookup Date: Fri, 16 Jan 2004 09:08:53 -0500 From: "White, Denise" Thanks, Russ, since it is apparently failing in the compile phase, I don't think it would even be trying to connect at that point; I think it would do that once it has compiled and started running. Actually, there had been a problem at the time this originally started where the account used in the dblink had lost its password, but that has since been restored. I am able to run anything using that dblink except this program. Denise M. White Sr. Software Engineer Vicor --__--__-- Message: 19 Subject: RE: [sqr-users] Weird problem with load-lookup To: sqr-users@sqrug.org From: rwulfson@bost.com Date: Thu, 15 Jan 2004 15:05:11 -0700 Reply-To: sqr-users@sqrug.org I would suspect that something has changed with dblink and that this is either not connecting or really slow. Thanks, Russ Wulfson Developer Boston Market Corporation Office: 303-216-5614 Fax: 303 -216- 8727 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 16 09:16:21 2004 Subject: RE: [sqr-users] Weird problem with load-lookup Date: Fri, 16 Jan 2004 09:08:56 -0500 From: "White, Denise" Thanks Steve, as I mentioned, I did that and it worked fine. Rewriting the program to do that select and populate an array instead of using the load-lookup is succesful; it's just the load-lookup that won't compile. Denise M. White Sr. Software Engineer Vicor --__--__-- Message: 21 From: "Steve Cavill" To: Subject: RE: [sqr-users] Weird problem with load-lookup Date: Fri, 16 Jan 2004 21:19:35 +1100 Reply-To: sqr-users@sqrug.org Hi Denise, you should try the exact select that the load-lookup is trying: begin-select loops=9999 file# (1,1,10) substr(datacontrol,4,1) () from reports.v_employee@pay4win where status in ('A','L') end-select and see what happens. Steve. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 16 09:19:08 2004 From: RHudgins@genencor.com Date: Fri, 16 Jan 2004 09:16:46 -0500 Subject: RE: [sqr-users] Weird problem with load-lookup Denise - I'm only guessing, but has the amount of data increased? I've seen funny things happen when the amount of data loaded in to arrays or look up tables excees the limits. good luck. Thanks! Rhonda Hudgins @}---'----,---- Genencor International, Inc office - 585/256-5266 rhudgins@genencor.com -------------------------------------------------------------------------- This message (including any attachments) may contain information which is confidential or privileged. Use, dissemination, distribution, or reproduction of this message by unintended recipients is not authorized and may be unlawful. If you are not the intended recipient, please advise the sender immediately by reply e-mail and delete this message and any attachments without retaining a copy. -------------------------------------------------------------------------- _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 16 10:00:19 2004 Date: Fri, 16 Jan 2004 09:59:08 -0500 From: "White, Denise" Subject: [sqr-users] Load-lookup problem - solved??? Thanks to all who replied to my problem with the load-lookup that suddenly would not compile, after running successfully for months. I have now narrowed it down to what in the load-lookup is specifically causing the problem. It is the Oracle substr() function. Again, the following was my code: ! Load shift differentials from ADP database load-lookup name=shift_differential table=reports.v_employee@pay4win key=file# return_value=substr(datacontrol,4,1) rows=9999 where='status in (''A'',''L'')' When I removed the substr() function and just returned the datacontrol field, this worked fine. I was then able to use the SQR substr() function in a LET command to get the character I wanted. However, now I am confused as to why the Oracle substr() function, which had been working fine, all of a sudden ceased to function (no pun intended!). I confirmed that I was still able to use it successfully in a regular BEGIN-SELECT; it only is in the load-lookup that I am having this problem. Any ideas? At this point, it is really only curiosity, as I can now get the program to run successfully. Thanks! Denise M. White Sr. Software Engineer Vicor _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 16 11:04:13 2004 Date: Fri, 16 Jan 2004 11:03:44 -0500 From: "George Jansen" Subject: Re: [sqr-users] #sql-status and unique constraint You wrote >>> maillists@starbuckk.net 01/15/04 10:48PM >>> To reduce database hits and network traffic. 99% of the time, there will NOT be a record out there. But out of maybe 200,000 records, I might have 5 or six repeats. Using a query ahead of time, or a "where not exists" subquiery in this particular case simply generates unnecessary database traffic. >>> Well, you know the business requirements. But do you know what this overhead amounts to? In my quick and dirty testing, encapsulating with PL/SQL ups the runtime about 40% if I just trap the DUP_VAL_ON_INDEX error, and about 80% if I apply the data in the duplicate record. Pretty grim, until you consider that the baseline is about 1000 records per second to SCOTT.EMP on not a very fast machine. So if I had 200,000 records and my alternatives were a. running in 3 min 20 sec and praying that the #sql-status would come back -9 b. running in 6 min and knowing that I had trapped my primary key conflicts maybe I'd choose option b) and make a coffe run. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 16 15:16:37 2004 Date: Fri, 16 Jan 2004 14:11:16 -0600 From: "Bob Helm" Subject: [sqr-users] computing string size with proportional fonts Is there anything in SQR (or any tools you know of) that computes the size of a string before it's printed? I didn't see anything at sqrtools.com or any other searches. Hyperion says it's a "possible future enhancement". Thanks for any info, B The Programmer formerly known as Bob Mediware Information Systems, Inc. bob.helm@mediware.com 913-307-1045 This message (including any attachments) constitutes a confidential communication and/or contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, please notify the sender by return email and delete this message and any attachments. Any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 16 15:30:12 2004 Subject: RE: [sqr-users] computing string size with proportional fonts Date: Fri, 16 Jan 2004 14:25:28 -0600 From: "Bob Helm" I should be a little more clear. I don't mean length of characters like the length() function returns, I meant length in inches, mm, even dots. I have reports with columns of data and I'm using proportional font. I'd like to truncate stings in one column before they spill over into the next column. Simply using the length() function is inconsistent at best since strings with X number of characters are different physical lengths in mm, in, etc. So, one string might truncate nicely, while another is cut way too short and still another may spill over. Thanks for any help. -----Original Message----- From: Bob Helm Sent: Friday, January 16, 2004 2:11 PM To: SQR Users (E-mail) Subject: [sqr-users] computing string size with proportional fonts Is there anything in SQR (or any tools you know of) that computes the size of a string before it's printed? I didn't see anything at sqrtools.com or any other searches. Hyperion says it's a "possible future enhancement". Thanks for any info, B The Programmer formerly known as Bob Mediware Information Systems, Inc. bob.helm@mediware.com 913-307-1045 This message (including any attachments) constitutes a confidential communication and/or contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, please notify the sender by return email and delete this message and any attachments. Any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 16 15:59:10 2004 From: Ray Ontko Date: Fri, 16 Jan 2004 15:58:24 -0500 Subject: Re: [sqr-users] computing string size with proportional fonts Hi Bob, We have a proportional fonts library, written in SQR, that reads Adobe Font Metrics (AFM) files and calculates the size (in points) for strings. The library does many common proportional font functions like wrapping and centering. Getting the length of a string is certainly one of the supported procedures. We have found the library to be VERY useful for working with proportional fonts. We've got enough time in it that we'd like to recoup some of our investment, but we haven't really decided to make it into a product. Is this important enough that you might consider paying a small but reasonable fee to license the software from us? Ray On Fri, Jan 16, 2004 at 02:25:28PM -0600, Bob Helm wrote: > I should be a little more clear. I don't mean length of characters like the length() function returns, I meant length in inches, mm, even dots. > > I have reports with columns of data and I'm using proportional font. I'd like to truncate stings in one column before they spill over into the next column. Simply using the length() function is inconsistent at best since strings with X number of characters are different physical lengths in mm,