From owner-sqr-users@list.iex.net Fri Oct 1 00:09:06 1999 Date: Fri, 1 Oct 1999 00:00:00 -0500 From: Nathan Stratton Treadway Subject: Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Stratton Treadway, Ray Ontko & Co. (nathant@ontko.com) $Revision: 1.5 $ $Date: 1999-08-31 18:59:00-04 $ This document gathers in one location information about the sqr-users mail list. Directions for common list-server commands (including unsubscribing) are given, as are guidelines for list usage. ______________________________________________________________________ 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. Basic List Server Commands 3.1 Subscribing to the List 3.2 Unsubscribing from the List 3.3 Digests 3.4 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 1999/04/30: o Reflected the takeover of SQRIBE Technologies by Brio Technology and the resulting new product names (throughout document). o Added additional hints to the "Posting to the sqr-users List" section. 2. Introduction 2.1. sqr-users Mailing List The sqr-users mailing list was created to enable discussion related to the SQR database language from Brio Technology (and to other products in the Brio.Report line). Note: SQR used to be published by SQRIBE Technologies, which was merged into Brio Technology on August 3, 1999. The Brio website has more information on the merger. 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.com/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 (C)Copyright 1999 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 psusers : PeopleSoft Users For questions related to any aspect of PeopleSoft (including PeopleSoft-specific SQR issues). To join, follow the link above, or send any message to "psusers-subscribe@egroups.com" . rm-users : ReportMart 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.com". 3. Basic List Server Commands Note: The list used to be hosted on usa.net , but the list-hosting service was moved to list.iex.net . The old addresses for the list, list server, and list owner no longer work. A few basic list server commands are listed below. Send these commands as the only line in the body (not the subject line) of an e- mail message sent to "listserv@list.iex.net". 3.1. Subscribing to the List To subscribe to the mailing list, the command is subscribe sqr-users Your Name Note that you do not need to provide your e-mail address as it is taken from your message's header. You will be e-mailed a confirmation number, which you must then send back to the list server software before the command goes into effect. 3.2. Unsubscribing from the List To unsubscribe, the command is simply unsubscribe sqr-users Once again, you will need to confirm your command before it goes into effect. 3.3. Digests Once you are subscribed, you can request to get your messages in digest form (one large message containing all of the posts made each day) by sending the command set sqr-users digests 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. Use "nodigests" instead of "digests" to restore normal distribution. 3.4. Problems If the above commands do not work, please contact the list manager at "sqr-users-request@list.iex.net". (Do not send a message to the list itself -- no one there can help you with your problem!) Please include a copy of the error message you received from the list server. 4. Posting to the sqr-users List In order to post to the mailing list, you must be subscribed to it (see directions above). 4.1. General Guidelines For any message you send to the list, please: o make sure your mail software is not requesting a "Return Receipt". (If a "Return Receipt Requested" message is sent to the list, several dozen receipt messages will be sent out to the mailing list in reply from other subscribers.) o do not send messages in HTML or other special formats. (These are not usable by many subscribers, especially those who receive their messages in digest format.) 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. 4.2. Posting a Message Once you are subscribed, you can post to the list by sending a message to "sqr-users@list.iex.net". 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 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. To find out your version 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 your own information. 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.) In particular, in order to prevent mail loops, the list server will not accept messages that contain From:, Sender:, etc. lines pointing back to the sqr-users list, even if these lines are quoted (i.e. with ">" characters). Be sure to delete these from your reply message before sending it. 5. Job Announcements SQR-related job announcements are currently allowed on the list, subject to the following restrictions: o The subject line must begin with the words "JOB ANNOUNCEMENT:". o The message should be very short (fewer than 20 lines), giving brief details of the job and a way for those interested to get more information directly (i.e. web page or phone number). o The message should specifically remind people interested in the job to respond directly to the message author (and not to use their mailer's "reply" function, since that would send the reply out on the list). o Post job announcements as infrequently as possible. From owner-sqr-users@list.iex.net Fri Oct 1 07:08:09 1999 Date: Fri, 1 Oct 1999 08:01:22 -0400 From: "Love, Kristin" Subject: Re: -M Max File Question Actually, all you do is create a text file (for example in WordPad). Here is a copy of one of mine: !--------------------------------------------------------------------------- !! !Report : Revenue Report ! File : GBMSM020.DAT ! Report Area : Sales !********************************************************************* !Changing the SQR defaults. QUERIES=100 SQLSIZE=33400 !Anything larger than this causes an Oracle error DYNAMICARGS=200 PROGLINES=4000 PROGLINEPARS=12000 Store your -M file in the same directory as your program. I usually name mine the same name as the SQR and then add .DAT. Other people have different extensions to name theirs... HTH :) Kristin > -----Original Message----- > From: Jason Maurer [SMTP:JMaurer@WIREDEMPIRE.COM] > Sent: Thursday, September 30, 1999 4:45 PM > To: Multiple recipients of list SQR-USERS > Subject: -M Max File Question > > Does anyone know where I can find information about how to construct a > -M (startup file)? Every now and then SQR tells me that I need to add > random lines to this file or increase my value size. I would like to be > able to find some information about this file so I can set it up now > with good sized variable limits so we don't keep running into this > problem. > > Thanks!! From owner-sqr-users@list.iex.net Fri Oct 1 11:43:00 1999 Date: Fri, 1 Oct 1999 16:18:01 +0200 From: Jason Wainwright Subject: Re: SQA Robot Hi list-dwellers I need to print in a garamondi font, in the sqr 4.1 documentation it seems to say that sqr supports the garamondi font in hp. I would very much like to print in this font on an hp, hence I would be ver much grateful if an esteemed list-dweller would please explain to the workings of a "soft font", as I'm lead to believe this is my only chance. thanks in advance From owner-sqr-users@list.iex.net Fri Oct 1 11:41:07 1999 Date: Fri, 1 Oct 1999 12:14:41 -0400 From: Dominick LoGiudice Subject: Creating Excel File from SQR (numeric text) I am trying to create a .csv file from SQR. My problem is fields like ssn which could have a leading 0. No matter what I try it when I open the .csv file in excel it drops the leading 0. "012345678" becomes 12345678. How do you write out numeric text fields? From owner-sqr-users@list.iex.net Fri Oct 1 11:43:56 1999 Date: Fri, 1 Oct 1999 11:25:02 -0500 From: Todd Becker Subject: Re: Creating Excel File from SQR (numeric text) Excel needs to know that the field is to be treated like a text field. This can be done during the import into Excel when each columns data types can be identified. You might also try surrounding the value in double quotes. This is the default text identifier for Excel imports. Good luck. Todd Dominick LoGiudice on 10/01/99 11:14:41 AM Please respond to SQR-USERS@list.iex.net To: Multiple recipients of list SQR-USERS cc: (bcc: Todd W Becker/IS/CCC) Subject: Creating Excel File from SQR (numeric text) I am trying to create a .csv file from SQR. My problem is fields like ssn which could have a leading 0. No matter what I try it when I open the .csv file in excel it drops the leading 0. "012345678" becomes 12345678. How do you write out numeric text fields? From owner-sqr-users@list.iex.net Fri Oct 1 11:45:36 1999 Date: Fri, 1 Oct 1999 11:30:39 -0500 From: "Johnson, Joe" Subject: Re: Creating Excel File from SQR (numeric text) Dominick, I had a similar problem with a txt file delimited by semi-colons. I had a salesperson ID that would be '00000706' In Excel, I had to format that column as Text, not as General. Then, the leading 0's were included -- in General format, the leading 0's were lost. HTH Joe Johnson Snelling and Snelling Corporation 972-776-1332 -----Original Message----- From: Dominick LoGiudice [mailto:dlogiudice@PINE.VPCC.SUNYSB.EDU] Sent: Friday, October 01, 1999 11:15 AM To: Multiple recipients of list SQR-USERS Subject: Creating Excel File from SQR (numeric text) I am trying to create a .csv file from SQR. My problem is fields like ssn which could have a leading 0. No matter what I try it when I open the .csv file in excel it drops the leading 0. "012345678" becomes 12345678. How do you write out numeric text fields? From owner-sqr-users@list.iex.net Fri Oct 1 11:42:51 1999 Date: Fri, 1 Oct 1999 09:34:20 -0700 From: Robert Stojkovic Subject: Re: Creating Excel File from SQR (numeric text) Hope this helps Have the column be set as text in the import definition in excel Robert > -----Original Message----- > From: Discussion of SQR, SQRIBE Technologies's database reporting > language [mailto:SQR-USERS@list.iex.net]On Behalf Of Dominick LoGiudice > Sent: Friday, October 01, 1999 9:15 AM > To: Multiple recipients of list SQR-USERS > Subject: Creating Excel File from SQR (numeric text) > > > I am trying to create a .csv file from SQR. > My problem is fields like ssn which could have a leading 0. No > matter what I > try it > when I open the .csv file in excel it drops the leading 0. > "012345678" becomes 12345678. > How do you write out numeric text fields? > From owner-sqr-users@list.iex.net Fri Oct 1 11:44:18 1999 Date: Fri, 1 Oct 1999 11:34:50 -0500 From: "Rao, Sam" Subject: Re: Creating Excel File from SQR (numeric text) Preface your numeric text fields with a Single Quote (tick). This forces Excel to treat the entry as Text. HTH, Sam Rao > -----Original Message----- > From: Dominick LoGiudice [SMTP:dlogiudice@PINE.VPCC.SUNYSB.EDU] > Sent: Friday, October 01, 1999 11:15 AM > To: Multiple recipients of list SQR-USERS > Subject: Creating Excel File from SQR (numeric text) > > I am trying to create a .csv file from SQR. > My problem is fields like ssn which could have a leading 0. No matter > what I > try it > when I open the .csv file in excel it drops the leading 0. > "012345678" becomes 12345678. > How do you write out numeric text fields? From owner-sqr-users@list.iex.net Fri Oct 1 11:46:38 1999 Date: Fri, 1 Oct 1999 12:38:16 -0400 From: Pam Lantzy Subject: Re: Creating Excel File from SQR (numeric text) I just discovered a solution earlier this week myself. When write the .csv datafile in sqr if you know the field is going to be numeric you can string an equal sign with double quotes around the field Example: ="0001234" This indicates in excel that its a numeric and not to strip the leading zeros. One problem with this though is if the user resaves this .csv with the same name in excel this notation is gone and when you reopen the .csv again in Excel the leading zeros will disappear. Pam Dominick LoGiudice wrote: > I am trying to create a .csv file from SQR. > My problem is fields like ssn which could have a leading 0. No matter what I > try it > when I open the .csv file in excel it drops the leading 0. > "012345678" becomes 12345678. > How do you write out numeric text fields? From owner-sqr-users@list.iex.net Fri Oct 1 11:47:53 1999 Date: Fri, 1 Oct 1999 10:38:32 -0600 From: "Duckworth, Jerry" Subject: Re: Creating Excel File from SQR (numeric text) It doesn't sound like the problem is with your csv file. This is a problem with Excel. Excel doesn't seem to notice the quotes around the text fields that are numeric and will always drop the leading 0's unless you specify the column as text when you are opening the file. > -----Original Message----- > From: Dominick LoGiudice [SMTP:dlogiudice@PINE.VPCC.SUNYSB.EDU] > Sent: Friday, October 01, 1999 10:15 AM > Subject: Creating Excel File from SQR (numeric text) > > I am trying to create a .csv file from SQR. > My problem is fields like ssn which could have a leading 0. No matter > what I > try it > when I open the .csv file in excel it drops the leading 0. > "012345678" becomes 12345678. > How do you write out numeric text fields? From owner-sqr-users@list.iex.net Fri Oct 1 12:02:20 1999 Date: Fri, 1 Oct 1999 12:52:05 -0400 From: Kimberly Lawrence Subject: Re: Creating Excel File from SQR (numeric text) --0__=VSCmEQZzeatL0d5VqZtjsECMkpaZLwt536FW1930sPvhDgkIXIsh0PRD Content-type: text/plain; charset=us-ascii Content-Disposition: inline I create my files as Silk (.slk). Then you can include formatting instructions in the file itself. You can check the archives for examples of silk files or contact me directly and I would be happy to provide you with an example. (Embedded image moved Dominick LoGiudice to file: pic17202.pcx) 10/01/99 12:14 PM Please respond to SQR-USERS@list.iex.net To: Multiple recipients of list SQR-USERS cc: (bcc: Kimberly Lawrence/MIS/Circuit City) Subject: Creating Excel File from SQR (numeric text) I am trying to create a .csv file from SQR. My problem is fields like ssn which could have a leading 0. No matter what I try it when I open the .csv file in excel it drops the leading 0. "012345678" becomes 12345678. How do you write out numeric text fields? --0__=VSCmEQZzeatL0d5VqZtjsECMkpaZLwt536FW1930sPvhDgkIXIsh0PRD Content-type: application/octet-stream; name="pic17202.pcx" Content-Disposition: attachment; filename="pic17202.pcx" Content-transfer-encoding: base64 CgUBCAAAAABoACwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAABaQABAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAD1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPH E8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sT zRPHE8MTwhPwEwzIBgzYE8wTxhPDE8IT7hPOBtcTzBPGE8MTE+wTwgbCBwbCEgbCEgbCEsUG1hPL E8YTwxMT6hMMwgYHwgLCAwISwgfEEsMCwwbVE8sTxRPDExPpE8MGAwcCBwMCwhLDB8ISwgISwgLD BtUTyhPFE8MTE+gTwgIHA8ICEw4DDgLDE8USwwLCEMIG1BPKE8UTwxMT5xMCAwcDAg4TDgITwgIS D8ISD8ISBRICEcICwwbUE8oTxRPCExPmEwYCBwMCDgIOwgLDExITEhPCEg8GxgLDBtMMDAfJE8QT whMT5hMGwwITBgMCDhLFEw8SE8ISBgIDwhIDEsMGB9MDxwwHxRPDExPlEwYHAhESAg8CwhMPwhMP xBMPxRIQwgIDAgMCBtMDxwPEDAfDE8IT4RMHwwzCBgLCEhMCDxLIE8MSD8MSwwIQAwIDBgfSDMkD wgPCDAfCExPbEwfGDMIDDAIHERITEhMSwxMPwxMPwxPDEgIDAgMCwwMCBgzREwfHDMYDDMITE9YT B8UMyAMGB8ICBhLDAsYTEhMSExIPwhIHAgcCAwUQAgYRBgfSE8UTB8QMwgMMwhMT0hMHxAzLA8IM BsISDxESExITAw4DxBMSExITwxICBwPCAsMDDMIGB9ITyRMHwwzCExPPEwfDDMkDxQwHwhMGBxIT AhECEwMOAg7DExITDxMPwxIDAgMCBwMCDAYRBgfSE8kTwhPCDMITE8wTB8MMxwPEDMIHxxMGxBLD Ag4DDgIGwg/IEgIDwgIDAgwCEMIGB9ITyRMHDAcMwhMTyhMHwgzGA8MMwgfMEwYHwhLCEAIOAg4C DhDDAhIPxhIFAgXDAgUCEQYH0hPHEwfCDAcPDMITE8gTB8IMxQPDDAfQEwbDEhDEAhAOEA4QwgLG EgcSBhIGBcMCBcIGB9ATB8UMEwfCDA8HDwwHwhMTxhMHwgzEA8MMB9MTBgfCEhADEMICDhAOEMIC EQIDxxIGBwbCAgUCEQYHyxMHxAwHwhMHEwzCEwcPBw8MB8MTE8UTBwzEA8IMB9YTBsQSEAMCA8UC EQIDAgPDEgcSBgfCBgUQAhDCBgfGEwfEDAfGE8INEwzCEw8HwgwHwxPCE8QTBwzDA8IMB9gTBgfE EhACEMYCEQIDAsQSBhLDBsICEALCBgfCEwfDDAfKEwfCDRMHwhPCDAfEE8ITE8MTBwzCA8IMB9oT DBIHwxLDDBEDxQIDAgPDEgYSBgfCBgIQAhAGDAfCEwzDE8MHyRMHwhPCBxMHxRPDExPDEwzCAwwH 3RMGxxICEQPDAgMCA8MSBhIGBwYMBhACEAIGDMMTDBPCB8YTwwfHEwfGE8MTwhPDEwwDDAfeEwYH xxICEQPDAgMCwhIGEgYHBgwGEAIQAsIGB8MTDMYTwwfKEwzGE8MTwhPDE8IMB98TDBLCB8USAgMR xAISB8ISBgcGDAYQBhAGEAYMB8MMB8kTwwfHEwzGE8MTwhPDEwwPwgzfEwYSB8ISB8ISAhECAwID EgcSBwYHBgwGEAYQxgzDD8IHxRPDB8kTBwzGE8MTwhPDEwzDD8QM3BPCBhIGwxIGAhECAwIHBgcG yAzJDxMHzRMHwwwHxxPDE8ITwxMHDMYPxwwH1BMGEgYSBhLLDM4PwwwTDMcTwgfEDAfJE8QTwhMT xBMHwgzLD9sM0w/GDAfDEwzDEwfEDAfLE8YTwxMTxhMHxAztD8gMBgfIE8QMB84TxxPDE8ITyhMH xwzbD8sMEAUMBcIMwgYH1RPKE8UTwxMT0RMH2wwGEAYQBhACBQwFDAUMBgwHBgfWE8sTxRPDExPu EwYMBhAGEAIGDAYMwwYH1xPLE8YTwxMT8BPKBgfYE8wTxhPDExP1E9sTzRPHE8MTwhP1E9sTzRPH E8MTwhMMAAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A//8AAAD/ /wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A//8A AAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A //8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAA AP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA /wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCk gICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vw oKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw //vwoKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzA psrw//vwoKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDA wNzApsrw//vwoKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICA wMDAwNzApsrw//vwoKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACA AICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACA gACA//vwoKCkgICA/wAAAP8A//8AAAD//wD/AP////// --0__=VSCmEQZzeatL0d5VqZtjsECMkpaZLwt536FW1930sPvhDgkIXIsh0PRD-- From owner-sqr-users@list.iex.net Fri Oct 1 12:02:31 1999 Date: Fri, 1 Oct 1999 12:54:01 -0400 From: Sam Spritzer Subject: Re: Creating Excel File from SQR (numeric text) Dom, Chances are that you are sending the field with the leading zeroes but when you import the file you have to specify that column as text not number. Sam <<< Dominick LoGiudice 10/ 1 12:14p >>> I am trying to create a .csv file from SQR. My problem is fields like ssn which could have a leading 0. No matter what I try it when I open the .csv file in excel it drops the leading 0. "012345678" becomes 12345678. How do you write out numeric text fields? From owner-sqr-users@list.iex.net Fri Oct 1 12:03:30 1999 Date: Fri, 1 Oct 1999 11:57:03 -0500 From: Kevin Burke Subject: Re: Creating Excel File from SQR (numeric text) In the past I have formatted the cells in Excel using 'Format/Cells/custom' you can then specify 000000000 or 000-00-0000 and it will keep the data with zero's on the left. -Kevin Burke PeopleSoft System Administrator Metropolitian Community Colleges (816) 759-1414 mailto:burke@kcmetro.cc.mo.us -----Original Message----- From: Discussion of SQR, SQRIBE Technologies's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Johnson, Joe Sent: Friday, October 01, 1999 11:31 AM To: Multiple recipients of list SQR-USERS Subject: Re: Creating Excel File from SQR (numeric text) Dominick, I had a similar problem with a txt file delimited by semi-colons. I had a salesperson ID that would be '00000706' In Excel, I had to format that column as Text, not as General. Then, the leading 0's were included -- in General format, the leading 0's were lost. HTH Joe Johnson Snelling and Snelling Corporation 972-776-1332 -----Original Message----- From: Dominick LoGiudice [mailto:dlogiudice@PINE.VPCC.SUNYSB.EDU] Sent: Friday, October 01, 1999 11:15 AM To: Multiple recipients of list SQR-USERS Subject: Creating Excel File from SQR (numeric text) I am trying to create a .csv file from SQR. My problem is fields like ssn which could have a leading 0. No matter what I try it when I open the .csv file in excel it drops the leading 0. "012345678" becomes 12345678. How do you write out numeric text fields? From owner-sqr-users@list.iex.net Fri Oct 1 13:06:42 1999 Date: Fri, 1 Oct 1999 13:25:23 -0400 From: Greg Hesla Subject: Re: Creating Excel File from SQR (numeric text) The quickest way to fix this is to change the extension from .csv to .txt, then import it into Excel as a delimited file, with commas being the delimiter. When you get to the part where you designate field types, designate this field as a text field and it will include the preceeding zeros. Greg ----- Original Message ----- From: Dominick LoGiudice To: Multiple recipients of list SQR-USERS Sent: Friday, October 01, 1999 12:14 PM Subject: Creating Excel File from SQR (numeric text) > I am trying to create a .csv file from SQR. > My problem is fields like ssn which could have a leading 0. No matter what I > try it > when I open the .csv file in excel it drops the leading 0. > "012345678" becomes 12345678. > How do you write out numeric text fields? > From owner-sqr-users@list.iex.net Fri Oct 1 12:44:35 1999 Date: Fri, 1 Oct 1999 13:26:41 -0400 From: Greg Hesla Subject: Re: Creating Excel File from SQR (numeric text) This will just make it look like it has the leading zeros. If you attempt to perform a lookup or comparison against another spreadsheet containing text-formatted values, they will never be equal. Greg ----- Original Message ----- From: Kevin Burke To: Multiple recipients of list SQR-USERS Sent: Friday, October 01, 1999 12:57 PM Subject: Re: Creating Excel File from SQR (numeric text) > In the past I have formatted the cells in Excel using 'Format/Cells/custom' > you can then specify 000000000 or 000-00-0000 and it will keep the data with > zero's on the left. > > > -Kevin Burke > PeopleSoft System Administrator > Metropolitian Community Colleges > (816) 759-1414 > mailto:burke@kcmetro.cc.mo.us > > -----Original Message----- > From: Discussion of SQR, SQRIBE Technologies's database reporting language > [mailto:SQR-USERS@list.iex.net]On Behalf Of Johnson, Joe > Sent: Friday, October 01, 1999 11:31 AM > To: Multiple recipients of list SQR-USERS > Subject: Re: Creating Excel File from SQR (numeric text) > > Dominick, > > I had a similar problem with a txt file delimited by semi-colons. > > I had a salesperson ID that would be '00000706' > > In Excel, I had to format that column as Text, not as General. > > Then, the leading 0's were included -- in General format, the leading 0's > were lost. > > HTH > > Joe Johnson > Snelling and Snelling Corporation > 972-776-1332 > > > -----Original Message----- > From: Dominick LoGiudice [mailto:dlogiudice@PINE.VPCC.SUNYSB.EDU] > Sent: Friday, October 01, 1999 11:15 AM > To: Multiple recipients of list SQR-USERS > Subject: Creating Excel File from SQR (numeric text) > > I am trying to create a .csv file from SQR. > My problem is fields like ssn which could have a leading 0. No matter what > I > try it > when I open the .csv file in excel it drops the leading 0. > "012345678" becomes 12345678. > How do you write out numeric text fields? > From owner-sqr-users@list.iex.net Fri Oct 1 16:46:40 1999 Date: Fri, 1 Oct 1999 17:34:54 EDT From: Norman Dolph Subject: Clueless questions 3, 4 & 5 + HP LaserJet Thots First thanks to all who were helpful with 1 and 2 3. The documentation and other code I have examined often sets flags to ' ' (that is two apostrophies separated by one space). Is this functionally equivalent to setting it = to '' (two apostrophies with no space between them) Is this ' ' setting the value to: NULL, An empty String, or a string that holds exactly one space? -------------- 4. What are the hard rules for what MUST ALWAYS BE INDENTED, and MUST NEVER BE INDENTED 5. Is the command MOVE 'FOO' to $BAR fully equivalent to the statement LET $BAR = 'FOO' If you had to code a lot of these, which is the more elegant way to do it. Many thanks Norman Dolph PS every body with HP Printer questions should get, what i assume is still free, from HP -- the HP HPCL Technical Manual. When you open a New laserjet, the USER manual inside comes with a coupon for the TECHNICAL manual. Mail it in and they send you one. At least they did with my model 4 and 5's. All of the strings for rules, shading, fonts, duplex printing and character sets are there. It is very helpful. The User manual only scratches the surface. From owner-sqr-users@list.iex.net Sun Oct 3 12:05:30 1999 Date: Sun, 3 Oct 1999 12:07:39 -0500 From: Gopal Nandakumar Subject: Change of Email Address How do I change my email address for SQR Users Group? Gopal. From owner-sqr-users@list.iex.net Sun Oct 3 13:53:42 1999 Date: Sun, 3 Oct 1999 13:42:07 -0500 From: Ray Ontko Subject: Re: Clueless questions 3, 4 & 5 + HP LaserJet Thots Norman, 3) These two are NOT the same: move '' to $var move ' ' to $var 4) Select expressions (column names) and the FROM keyword in a select must not be indented. SQR commands inside a begin-select must be indented. 5) These two statements are functionally equivalent, but the first is faster than the second: move 'FOO' to $bar let $bar = 'FOO' Ray > 3. The documentation and other code I have examined often sets flags to ' ' > (that is two apostrophies separated by one space). > Is this functionally equivalent to setting it = to '' (two apostrophies with > no space between them) > > Is this ' ' setting the value to: NULL, An empty String, or a string > that holds exactly one space? > -------------- > 4. What are the hard rules for what MUST ALWAYS BE INDENTED, and > MUST NEVER BE INDENTED > > 5. Is the command MOVE 'FOO' to $BAR fully equivalent to > the statement LET $BAR = 'FOO' > If you had to code a lot of these, which is the more elegant way to do > it. > > Many thanks > Norman Dolph > > PS every body with HP Printer questions should get, what i assume is still > free, from HP -- the HP HPCL Technical Manual. When you open a New laserjet, > the USER manual inside comes with a coupon for the TECHNICAL manual. Mail it > in and they send you one. At least they did with my model 4 and 5's. > > All of the strings for rules, shading, fonts, duplex printing and character > sets are there. It is very helpful. The User manual only scratches the > surface. > ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | Software Consulting Services rayo@ontko.com | Richmond, In | http://www.ontko.com/ From owner-sqr-users@list.iex.net Mon Oct 4 10:43:38 1999 Date: Mon, 4 Oct 1999 10:30:34 -0500 From: "Ross, Steven" Subject: Show vs. Display Does anyone know the difference between the Show command and the Display command? The SQR manual isn't too clear. I've got an SQR that uses both, and for some reason, the Show command doesn't display anything. TIA, Steven Ross Programmer/Analyst sross@kcm.org From owner-sqr-users@list.iex.net Mon Oct 4 10:54:42 1999 Date: Mon, 4 Oct 1999 08:44:38 -0700 From: Joe Johnson Subject: Re: Show vs. Display On Mon, 04 October 1999, "Ross, Steven" wrote: > > Does anyone know the difference between the Show command and the Display > command? The SQR manual isn't too clear. > > I've got an SQR that uses both, and for some reason, the Show command > doesn't display anything. > > TIA, > Steven Ross > Programmer/Analyst > sross@kcm.org Steven, Show gives more power than Display -- with Show, you can display multiple fields with one Show statement as well as letting you control cursor placement on the screen and do things like send beep signals or underline/bold screen fields. When there is nothing displayed, that means the Select statement isn't right. HTH ;{) __________________________________________ $14.95 a month Unlimited Internet Access. Get your first month for $1.00. http://www.surfree.com/promo From owner-sqr-users@list.iex.net Mon Oct 4 10:55:19 1999 Date: Mon, 4 Oct 1999 08:45:19 -0700 From: Joe Johnson Subject: Re: Show vs. Display On Mon, 04 October 1999, "Ross, Steven" wrote: > > Does anyone know the difference between the Show command and the Display > command? The SQR manual isn't too clear. > > I've got an SQR that uses both, and for some reason, the Show command > doesn't display anything. > > TIA, > Steven Ross > Programmer/Analyst > sross@kcm.org Steven, Show gives more power than Display -- Show allows you to display multiple fields with one Show statement as well as letting you control cursor placement on the screen and do things like send beep signals or underline/bold screen fields. When there is nothing displayed, that means the Select statement isn't right. HTH ;{) __________________________________________ $14.95 a month Unlimited Internet Access. Get your first month for $1.00. http://www.surfree.com/promo From owner-sqr-users@list.iex.net Mon Oct 4 11:05:55 1999 Date: Mon, 4 Oct 1999 10:55:01 -0500 From: "Ross, Steven" Subject: Re: Show vs. Display Joe, Okay, thanks for the "multiple" answer...that clears that up. But as far as it not working goes, there isn't a Select statement involved: show 'Begin Job: ' $AsOfToday ' ' $AsOfNow display ' ' show $ReportTitle display ' ' This is in the Init-Report procedure, before any Selects are done. Now, I think the displays are working, as there are several blank lines in the SQRW window... Thanks, Steven Ross Programmer/Analyst sross@kcm.org > -----Original Message----- > From: Joe Johnson [mailto:jejohn1216@SURFREE.COM] > Sent: Monday, October 04, 1999 10:45 AM > Subject: Re: Show vs. Display > > On Mon, 04 October 1999, "Ross, Steven" wrote: > > > > Does anyone know the difference between the Show command and the Display > > command? The SQR manual isn't too clear. > > > > I've got an SQR that uses both, and for some reason, the Show command > > doesn't display anything. > > Steven, > > Show gives more power than Display -- with Show, you can > display multiple fields with one Show statement as well as > letting you control cursor placement on the screen and do > things like send beep signals or underline/bold screen fields. > > When there is nothing displayed, that means the Select > statement isn't right. From owner-sqr-users@list.iex.net Mon Oct 4 11:12:34 1999 Date: Mon, 4 Oct 1999 09:04:49 -0700 From: Joe Johnson Subject: Re: Show vs. Display On Mon, 04 October 1999, "Ross, Steven" wrote: > > Joe, > > Okay, thanks for the "multiple" answer...that clears that up. But as far as > it not working goes, there isn't a Select statement involved: > > show 'Begin Job: ' $AsOfToday ' ' $AsOfNow > display ' ' > show $ReportTitle > display ' ' > > This is in the Init-Report procedure, before any Selects are done. Now, I > think the displays are working, as there are several blank lines in the SQRW > window... > > Thanks, > > Steven Ross > Programmer/Analyst > sross@kcm.org Steven, Is there a Let $ReportTitle = 'Whatever I'm Calling My Report' statement? Or, a Move 'Whatever I'm Calling My Report' to $ReportTitle ? - Joe Johnson ;{) __________________________________________ $14.95 a month Unlimited Internet Access. Get your first month for $1.00. http://www.surfree.com/promo From owner-sqr-users@list.iex.net Mon Oct 4 11:15:46 1999 Date: Mon, 4 Oct 1999 11:09:11 -0500 From: "Ross, Steven" Subject: Re: Show vs. Display > On Mon, 04 October 1999, "Ross, Steven" wrote: > > > But as far as it not working goes, there isn't a Select statement involved: > > > > show 'Begin Job: ' $AsOfToday ' ' $AsOfNow > > display ' ' > > show $ReportTitle > > display ' ' > > > > This is in the Init-Report procedure, before any Selects > are done. Now, I > > Steven, > > Is there a > Move 'Whatever I'm Calling My Report' > to $ReportTitle > ? Aha! That's it! None of those variables have been initialized at that point! Thanks! Steven Ross Programmer/Analyst sross@kcm.org From owner-sqr-users@list.iex.net Mon Oct 4 11:20:21 1999 Date: Mon, 4 Oct 1999 11:13:23 -0500 From: Carlton Conley Subject: Re: Show vs. Display There are some fairly meaningless difference including making the screen beep but the main difference lies in the ability to display multiple fields with one command for example Show 'Returned Value 1 = ' &COLUMN1 ' Returned Value 2 = ' &COLUMN2 vs Display 'Returned Value 1 = ' noline Display &COLUMN1 noline Display 'Returned Value 2 = ' noline Display '&COLUMN2 As for not seeing anything with the display command I always make sure I have some test and some way to set off the data for Example using Display Display 'my value ' no line Display '*" noline Display $whatIwantToDisplay noline Display '*' that way if I get my value * * then I know there was not a value in what I was trying to display, so if you see nothing there may not be a problem with the Show or Display but your select. > -----Original Message----- > From: Discussion of SQR, SQRIBE Technologies's database reporting > language [mailto:SQR-USERS@list.iex.net]On Behalf Of Ross, Steven > Sent: Monday, October 04, 1999 10:31 AM > To: Multiple recipients of list SQR-USERS > Subject: Show vs. Display > > > Does anyone know the difference between the Show command and the Display > command? The SQR manual isn't too clear. > > I've got an SQR that uses both, and for some reason, the Show command > doesn't display anything. > > TIA, > Steven Ross > Programmer/Analyst > sross@kcm.org > From owner-sqr-users@list.iex.net Mon Oct 4 11:43:07 1999 Date: Mon, 4 Oct 1999 11:32:09 -0500 From: "Veso M. Lukovic" Subject: Re: SQR 4701 Error Hey all.. I am having a similar problem. My user is actually able to run process scheduler (on the client) and have the SQR run and print, but when running from the SQR icon, gets the following message: (SQR 5528) ORACLE ORLON error -3121 in cursor 0: Error while trying to retrieve text for error ORA-03121 (SQR 4701) Can't log on to the database The Oracle client tnsnames file and client softwar are correct (can use SQLPlus fine). I am mostly baffled as to why process scheduler worked, but not the SQR icon.. I tried cutting and pasting the parameters from the process scheduler job, but still have same error. I am running on Oracle 7.3.2.4, PeopleSoft HRMS 7.02. Novell client.. by the way, this error appeared after the workstation folks did a Y2K downgrade.. sorry, I mean upgrade :-) I am not sure exactly what they did, but I will try to find out, and add any other information to this mailing.. Thanks in advance for your help! I do really need it an appreciate all in this group! ============================= Veso Lukovic PeopleSoft Systems St. Vincent Hospital Indianapolis IN. vmlukovi@stvincent.org ============================= >>> "John A. Hayes" 09/29/99 03:43PM >>> Mark, Just an FYI: You can add a tns_admin key to the Oracle section of the registry to point to a central tnsnames.ora file. This way you get out of maintaining the tnsnames.ora on each workstation. ---------------------------------------------- Hayes Systems Solutions, Inc. Canoga Park, CA mailto:owner@hss-usa.com http://www.hss-usa.com ---------------------------------------------- At 02:52 PM 9/29/99 -0500, you wrote: >[Mark A. Clements] > >SQR's launched from process scheduler can run either on the server or the >client. The way we do things here, the user logs into a three tier >environment >to do all of their PeopleSoft processing, but when they launch an SQR, it >actually spins off a two-tier process on their workstation unless they specify >that the process run on the server. I have a hunch that is what is happening >to you. In that case, the sql*net connection and tns names must be maintained >on each workstation. > >Could that be the problem? > >-Mark > >They run it from within PeopleSoft, from the menu. I assume it is three >tier, the database resides in Ottawa, and we are 3500 miles west of there. >The error happens after the PeopleSoft login, the users are able to >edit/retrieve data within the form, it is running the 'canned' reports that >generates the error. > >Lorne Gottschewski >Programmer >WTIS >19 Wing. CFB Comox >----- Original Message ----- >From: Mark Clements >To: Multiple recipients of list SQR-USERS >Sent: Wednesday, September 29, 1999 11:53 AM >Subject: Re: SQR 4701 Error > > > > [Mark A. Clements] > > > > Are they running the report from a PeopleSoft panel or from workbench? It >also > > makes a difference if they are running in 2 or 3 tier modes. > > > > > > -Mark > > > > I have several work stations that use PeopleSoft (v7) and I have recently >added > > an Oracle 8.0.5 Application written with Oracle Developer 6 on those >machines. > > Ever scince, the user can loggon to peopleSoft, but is unable to run a >report > > without getting the following: > > SQRW V3.0.18.1.1 (SQR 4701) Can't logon to database. ---------------------------------------------- Hayes Systems Solutions, Inc. Canoga Park, CA mailto:owner@hss-usa.com http://www.hss-usa.com ---------------------------------------------- From owner-sqr-users@list.iex.net Mon Oct 4 12:30:45 1999 Date: Mon, 4 Oct 1999 12:16:39 -0500 From: Cory Condiff Subject: Re: SQR 4701 Error >(SQR 5528) ORACLE ORLON error -3121 in cursor 0: > Error while trying to retrieve text for error ORA-03121 Make sure you are pointing to your oracle directory, ie. check your process scheduler in Configuration Manager. If those are correct...check your drive mappings. -Cory From owner-sqr-users@list.iex.net Mon Oct 4 12:36:04 1999 Date: Mon, 4 Oct 1999 13:41:27 -0400 From: "Johnson, Dan" Subject: Re: Show vs. Display All of this display/show talk had me curious so I wrote this test program. begin-program move 'test one' to $test1 move 'test two' to $test2 show 'Begin test:'$test1''$test2 end-program and the output was Begin test: The values of $test1 and $test2 do not display. Should they? Is this a version thing? I am on version 3. -----Original Message----- From: Carlton Conley [SMTP:cconley@CDG-INC.COM] Sent: Monday, October 04, 1999 12:13 PM To: Multiple recipients of list SQR-USERS Subject: Re: Show vs. Display There are some fairly meaningless difference including making the screen beep but the main difference lies in the ability to display multiple fields with one command for example Show 'Returned Value 1 = ' &COLUMN1 ' Returned Value 2 = ' &COLUMN2 vs Display 'Returned Value 1 = ' noline Display &COLUMN1 noline Display 'Returned Value 2 = ' noline Display '&COLUMN2 As for not seeing anything with the display command I always make sure I have some test and some way to set off the data for Example using Display Display 'my value ' no line Display '*" noline Display $whatIwantToDisplay noline Display '*' that way if I get my value * * then I know there was not a value in what I was trying to display, so if you see nothing there may not be a problem with the Show or Display but your select. > -----Original Message----- > From: Discussion of SQR, SQRIBE Technologies's database reporting > language [mailto:SQR-USERS@list.iex.net]On Behalf Of Ross, Steven > Sent: Monday, October 04, 1999 10:31 AM > To: Multiple recipients of list SQR-USERS > Subject: Show vs. Display > > > Does anyone know the difference between the Show command and the Display > command? The SQR manual isn't too clear. > > I've got an SQR that uses both, and for some reason, the Show command > doesn't display anything. > > TIA, > Steven Ross > Programmer/Analyst > sross@kcm.org > From owner-sqr-users@list.iex.net Mon Oct 4 12:52:29 1999 Date: Mon, 4 Oct 1999 17:42:08 GMT From: eddy vasile Subject: Re: SQR 4701 Error The process scheduler has SYSADM rights. The client login "should have" limited rights. Any SQR that refrences USER_TABLES instead of ALL_TABLES will return null results when run from the SQR icon and valid results when run from the proc scheduler. There are other issues as well (e.g. the proc scheduler may define the -i switch differently, thus other SQCs are included which your -i path may be ignoring). >From: "Veso M. Lukovic" >Reply-To: SQR-USERS@list.iex.net >To: Multiple recipients of list SQR-USERS >Subject: Re: SQR 4701 Error >Date: Mon, 4 Oct 1999 11:32:09 -0500 > >Hey all.. I am having a similar problem. My user is actually able to run >process scheduler (on the client) and have the SQR run and print, but when >running from the SQR icon, gets the following message: > >(SQR 5528) ORACLE ORLON error -3121 in cursor 0: > Error while trying to retrieve text for error ORA-03121 >(SQR 4701) Can't log on to the database > >The Oracle client tnsnames file and client softwar are correct (can use >SQLPlus fine). > >I am mostly baffled as to why process scheduler worked, but not the SQR >icon.. I tried cutting and pasting the parameters from the process >scheduler job, but still have same error. > > >I am running on Oracle 7.3.2.4, PeopleSoft HRMS 7.02. Novell client.. by >the way, this error appeared after the workstation folks did a Y2K >downgrade.. sorry, I mean upgrade :-) > >I am not sure exactly what they did, but I will try to find out, and add >any other information to this mailing.. > >Thanks in advance for your help! I do really need it an appreciate all in >this group! > > > >============================= >Veso Lukovic >PeopleSoft Systems >St. Vincent Hospital >Indianapolis IN. >vmlukovi@stvincent.org >============================= > > > >>> "John A. Hayes" 09/29/99 03:43PM >>> >Mark, > >Just an FYI: You can add a tns_admin key to the Oracle section of the >registry to point to a central tnsnames.ora file. This way you get out of >maintaining the tnsnames.ora on each workstation. > >---------------------------------------------- >Hayes Systems Solutions, Inc. Canoga Park, CA >mailto:owner@hss-usa.com >http://www.hss-usa.com >---------------------------------------------- > >At 02:52 PM 9/29/99 -0500, you wrote: > >[Mark A. Clements] > > > >SQR's launched from process scheduler can run either on the server or the > >client. The way we do things here, the user logs into a three tier > >environment > >to do all of their PeopleSoft processing, but when they launch an SQR, it > >actually spins off a two-tier process on their workstation unless they >specify > >that the process run on the server. I have a hunch that is what is >happening > >to you. In that case, the sql*net connection and tns names must be >maintained > >on each workstation. > > > >Could that be the problem? > > > >-Mark > > > >They run it from within PeopleSoft, from the menu. I assume it is three > >tier, the database resides in Ottawa, and we are 3500 miles west of >there. > >The error happens after the PeopleSoft login, the users are able to > >edit/retrieve data within the form, it is running the 'canned' reports >that > >generates the error. > > > >Lorne Gottschewski > >Programmer > >WTIS > >19 Wing. CFB Comox > >----- Original Message ----- > >From: Mark Clements > >To: Multiple recipients of list SQR-USERS > >Sent: Wednesday, September 29, 1999 11:53 AM > >Subject: Re: SQR 4701 Error > > > > > > > [Mark A. Clements] > > > > > > Are they running the report from a PeopleSoft panel or from workbench? > It > >also > > > makes a difference if they are running in 2 or 3 tier modes. > > > > > > > > > -Mark > > > > > > I have several work stations that use PeopleSoft (v7) and I have >recently > >added > > > an Oracle 8.0.5 Application written with Oracle Developer 6 on those > >machines. > > > Ever scince, the user can loggon to peopleSoft, but is unable to run >a > >report > > > without getting the following: > > > SQRW V3.0.18.1.1 (SQR 4701) Can't logon to database. > >---------------------------------------------- >Hayes Systems Solutions, Inc. Canoga Park, CA >mailto:owner@hss-usa.com >http://www.hss-usa.com >---------------------------------------------- ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Mon Oct 4 13:07:05 1999 Date: Mon, 4 Oct 1999 13:49:15 -0400 From: Bob Buford-Abba Systems Subject: Re: Show vs. Display This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BF0E90.CA03AD02 Content-Type: text/plain; charset="iso-8859-1" Dan, Try it again with spaces between the elements of the show, e.g. show 'Begin test:' $test1 '' $test2 For readability, a further improvement: show 'Begin test: ' $test1 ' ' $test2 Bob -----Original Message----- From: Johnson, Dan [mailto:Dan_Johnson@WRIGHTEXPRESS.COM] Sent: Monday, October 04, 1999 1:41 PM To: Multiple recipients of list SQR-USERS Subject: Re: Show vs. Display All of this display/show talk had me curious so I wrote this test program. begin-program move 'test one' to $test1 move 'test two' to $test2 show 'Begin test:'$test1''$test2 end-program and the output was Begin test: The values of $test1 and $test2 do not display. Should they? Is this a version thing? I am on version 3. -----Original Message----- From: Carlton Conley [SMTP:cconley@CDG-INC.COM] Sent: Monday, October 04, 1999 12:13 PM To: Multiple recipients of list SQR-USERS Subject: Re: Show vs. Display There are some fairly meaningless difference including making the screen beep but the main difference lies in the ability to display multiple fields with one command for example Show 'Returned Value 1 = ' &COLUMN1 ' Returned Value 2 = ' &COLUMN2 vs Display 'Returned Value 1 = ' noline Display &COLUMN1 noline Display 'Returned Value 2 = ' noline Display '&COLUMN2 As for not seeing anything with the display command I always make sure I have some test and some way to set off the data for Example using Display Display 'my value ' no line Display '*" noline Display $whatIwantToDisplay noline Display '*' that way if I get my value * * then I know there was not a value in what I was trying to display, so if you see nothing there may not be a problem with the Show or Display but your select. > -----Original Message----- > From: Discussion of SQR, SQRIBE Technologies's database reporting > language [mailto:SQR-USERS@list.iex.net]On Behalf Of Ross, Steven > Sent: Monday, October 04, 1999 10:31 AM > To: Multiple recipients of list SQR-USERS > Subject: Show vs. Display > > > Does anyone know the difference between the Show command and the Display > command? The SQR manual isn't too clear. > > I've got an SQR that uses both, and for some reason, the Show command > doesn't display anything. > > TIA, > Steven Ross > Programmer/Analyst > sross@kcm.org > ------_=_NextPart_001_01BF0E90.CA03AD02 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Show vs. Display

Dan,

Try it again with spaces between the elements of the = show, e.g.

show 'Begin test:' $test1 '' $test2

For readability, a further improvement:
show 'Begin test: ' $test1 ' ' $test2

Bob


-----Original Message-----
From: Johnson, Dan [mailto:Dan_Johnson@WRIGHTE= XPRESS.COM]
Sent: Monday, October 04, 1999 1:41 PM
To: Multiple recipients of list SQR-USERS
Subject: Re: Show vs. Display


All of this display/show talk had me curious so I = wrote this test program.

begin-program

move 'test one' to $test1
move 'test two' to $test2

show 'Begin test:'$test1''$test2
end-program

and the output was

Begin test:

The values of $test1 and $test2 do not display. = Should they? Is this a
version thing? I am on version 3.

        = -----Original Message-----
        = From:   Carlton Conley [SMTP:cconley@CDG-INC.COM]
        = Sent:   Monday, October 04, 1999 12:13 PM
        = To:     Multiple recipients of list = SQR-USERS
        = Subject:        Re: Show vs. = Display

        There are = some fairly meaningless difference including making the
screen
        beep but = the main difference lies in the ability to display multiple
fields
        with one = command

        for = example

        Show = 'Returned Value 1 =3D ' &COLUMN1 ' Returned Value 2 =3D ' = &COLUMN2

        vs

        Display = 'Returned Value 1 =3D ' noline
        Display = &COLUMN1 noline
        Display = 'Returned Value 2 =3D ' noline
        Display = '&COLUMN2

        As for not = seeing anything with the display command I always make
sure I
        have some = test and some way to set off the data for Example using
Display

        Display = 'my value ' no line
        Display = '*" noline
        Display = $whatIwantToDisplay noline
        Display = '*'

        that way = if I get my value * *

        then I = know there was not a value in what I was trying to display,
so if you
        see = nothing there may not be a problem with the Show or Display but
your
        = select.


        > = -----Original Message-----
        > = From: Discussion of SQR, SQRIBE Technologies's database = reporting
        > = language [mailto:SQR-USERS@list.iex.net= ]On Behalf Of Ross, Steven
        > = Sent: Monday, October 04, 1999 10:31 AM
        > To: = Multiple recipients of list SQR-USERS
        > = Subject: Show vs. Display
        = >
        = >
        > Does = anyone know the difference between the Show command and the
Display
        > = command?  The SQR manual isn't too clear.
        = >
        > I've = got an SQR that uses both, and for some reason, the Show
command
        > = doesn't display anything.
        = >
        > = TIA,
        > = Steven Ross
        > = Programmer/Analyst
        = >  <mailto:sross@kcm.org> = sross@kcm.org
        = >

------_=_NextPart_001_01BF0E90.CA03AD02-- From owner-sqr-users@list.iex.net Mon Oct 4 13:53:04 1999 Date: Mon, 4 Oct 1999 14:37:19 -0400 From: Vivek Pandian Subject: ziv flag Hi all, The solution i want is if the user runs the sqr in the client the program should use -ziv flag. If he is running the same program in the server, the process should not use the -ziv flag. I do not want two process definitions. Any input is greatly appreciated. Thanks From owner-sqr-users@list.iex.net Mon Oct 4 13:05:19 1999 Date: Mon, 4 Oct 1999 12:48:53 -0600 From: Sunil Kanderi Subject: Re[2]: SQR 4701 Error Veso, I think it has to do with the permissions and access priviliges of the log on you are using to run the SQR from SQR icon. It is correct that process scheduler has sysadm rights.... Try running the sqr from the sqr icon using a log on that has sysadm priviliges....... sunil. ______________________________ Reply Separator _________________________________ Subject: Re: SQR 4701 Error Author: at IPNET Date: 10/4/99 5:42 PM The process scheduler has SYSADM rights. The client login "should have" limited rights. Any SQR that refrences USER_TABLES instead of ALL_TABLES will return null results when run from the SQR icon and valid results when run from the proc scheduler. There are other issues as well (e.g. the proc scheduler may define the -i switch differently, thus other SQCs are included which your -i path may be ignoring). >From: "Veso M. Lukovic" >Reply-To: SQR-USERS@list.iex.net >To: Multiple recipients of list SQR-USERS >Subject: Re: SQR 4701 Error >Date: Mon, 4 Oct 1999 11:32:09 -0500 > >Hey all.. I am having a similar problem. My user is actually able to run >process scheduler (on the client) and have the SQR run and print, but when >running from the SQR icon, gets the following message: > >(SQR 5528) ORACLE ORLON error -3121 in cursor 0: > Error while trying to retrieve text for error ORA-03121 >(SQR 4701) Can't log on to the database > >The Oracle client tnsnames file and client softwar are correct (can use >SQLPlus fine). > >I am mostly baffled as to why process scheduler worked, but not the SQR >icon.. I tried cutting and pasting the parameters from the process >scheduler job, but still have same error. > > >I am running on Oracle 7.3.2.4, PeopleSoft HRMS 7.02. Novell client.. by >the way, this error appeared after the workstation folks did a Y2K >downgrade.. sorry, I mean upgrade :-) > >I am not sure exactly what they did, but I will try to find out, and add >any other information to this mailing.. > >Thanks in advance for your help! I do really need it an appreciate all in >this group! > > > >============================= >Veso Lukovic >PeopleSoft Systems >St. Vincent Hospital >Indianapolis IN. >vmlukovi@stvincent.org >============================= > > > >>> "John A. Hayes" 09/29/99 03:43PM >>> >Mark, > >Just an FYI: You can add a tns_admin key to the Oracle section of the >registry to point to a central tnsnames.ora file. This way you get out of >maintaining the tnsnames.ora on each workstation. > >---------------------------------------------- >Hayes Systems Solutions, Inc. Canoga Park, CA >mailto:owner@hss-usa.com >http://www.hss-usa.com >---------------------------------------------- > >At 02:52 PM 9/29/99 -0500, you wrote: > >[Mark A. Clements] > > > >SQR's launched from process scheduler can run either on the server or the > >client. The way we do things here, the user logs into a three tier > >environment > >to do all of their PeopleSoft processing, but when they launch an SQR, it > >actually spins off a two-tier process on their workstation unless they >specify > >that the process run on the server. I have a hunch that is what is >happening > >to you. In that case, the sql*net connection and tns names must be >maintained > >on each workstation. > > > >Could that be the problem? > > > >-Mark > > > >They run it from within PeopleSoft, from the menu. I assume it is three > >tier, the database resides in Ottawa, and we are 3500 miles west of >there. > >The error happens after the PeopleSoft login, the users are able to > >edit/retrieve data within the form, it is running the 'canned' reports >that > >generates the error. > > > >Lorne Gottschewski > >Programmer > >WTIS > >19 Wing. CFB Comox > >----- Original Message ----- > >From: Mark Clements > >To: Multiple recipients of list SQR-USERS > >Sent: Wednesday, September 29, 1999 11:53 AM > >Subject: Re: SQR 4701 Error > > > > > > > [Mark A. Clements] > > > > > > Are they running the report from a PeopleSoft panel or from workbench? > It > >also > > > makes a difference if they are running in 2 or 3 tier modes. > > > > > > > > > -Mark > > > > > > I have several work stations that use PeopleSoft (v7) and I have >recently > >added > > > an Oracle 8.0.5 Application written with Oracle Developer 6 on those > >machines. > > > Ever scince, the user can loggon to peopleSoft, but is unable to run >a > >report > > > without getting the following: > > > SQRW V3.0.18.1.1 (SQR 4701) Can't logon to database. > >---------------------------------------------- >Hayes Systems Solutions, Inc. Canoga Park, CA >mailto:owner@hss-usa.com >http://www.hss-usa.com >---------------------------------------------- ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Mon Oct 4 14:05:02 1999 Date: Mon, 4 Oct 1999 11:55:20 -0700 From: Robert Stojkovic Subject: Re: Show vs. Display Hi . Try this begin-program move 'test one' to $test1 move 'test two' to $test2 display 'Begin test:' $test1 ' ' $test2 end-program Robert > -----Original Message----- > From: Discussion of SQR, SQRIBE Technologies's database reporting > language [mailto:SQR-USERS@list.iex.net]On Behalf Of Johnson, Dan > Sent: Monday, October 04, 1999 10:41 AM > To: Multiple recipients of list SQR-USERS > Subject: Re: Show vs. Display > > > All of this display/show talk had me curious so I wrote this test program. > > begin-program > > move 'test one' to $test1 > move 'test two' to $test2 > > show 'Begin test:'$test1''$test2 > end-program > > and the output was > > Begin test: > > The values of $test1 and $test2 do not display. Should they? Is this a > version thing? I am on version 3. > > -----Original Message----- > From: Carlton Conley [SMTP:cconley@CDG-INC.COM] > Sent: Monday, October 04, 1999 12:13 PM > To: Multiple recipients of list SQR-USERS > Subject: Re: Show vs. Display > > There are some fairly meaningless difference including making the > screen > beep but the main difference lies in the ability to > display multiple > fields > with one command > > for example > > Show 'Returned Value 1 = ' &COLUMN1 ' Returned Value 2 = > ' &COLUMN2 > > vs > > Display 'Returned Value 1 = ' noline > Display &COLUMN1 noline > Display 'Returned Value 2 = ' noline > Display '&COLUMN2 > > As for not seeing anything with the display command I always make > sure I > have some test and some way to set off the data for Example using > Display > > Display 'my value ' no line > Display '*" noline > Display $whatIwantToDisplay noline > Display '*' > > that way if I get my value * * > > then I know there was not a value in what I was trying to display, > so if you > see nothing there may not be a problem with the Show or > Display but > your > select. > > > > -----Original Message----- > > From: Discussion of SQR, SQRIBE Technologies's database > reporting > > language [mailto:SQR-USERS@list.iex.net]On Behalf Of > Ross, Steven > > Sent: Monday, October 04, 1999 10:31 AM > > To: Multiple recipients of list SQR-USERS > > Subject: Show vs. Display > > > > > > Does anyone know the difference between the Show command and the > Display > > command? The SQR manual isn't too clear. > > > > I've got an SQR that uses both, and for some reason, the Show > command > > doesn't display anything. > > > > TIA, > > Steven Ross > > Programmer/Analyst > > sross@kcm.org > > > From owner-sqr-users@list.iex.net Mon Oct 4 14:06:46 1999 Date: Mon, 4 Oct 1999 13:58:48 -0500 From: "Veso M. Lukovic" Subject: Re: SQR 4701 Error Thanks for the responses :-) I noticed that the C:\orawin95\bin path statement was missing from the autoexec.bat file. This did the trick. Thanks again! >>> "Veso M. Lukovic" 10/04/99 11:32AM >>> Hey all.. I am having a similar problem. My user is actually able to run process scheduler (on the client) and have the SQR run and print, but when running from the SQR icon, gets the following message: (SQR 5528) ORACLE ORLON error -3121 in cursor 0: Error while trying to retrieve text for error ORA-03121 (SQR 4701) Can't log on to the database The Oracle client tnsnames file and client softwar are correct (can use SQLPlus fine). I am mostly baffled as to why process scheduler worked, but not the SQR icon.. I tried cutting and pasting the parameters from the process scheduler job, but still have same error. I am running on Oracle 7.3.2.4, PeopleSoft HRMS 7.02. Novell client.. by the way, this error appeared after the workstation folks did a Y2K downgrade.. sorry, I mean upgrade :-) I am not sure exactly what they did, but I will try to find out, and add any other information to this mailing.. Thanks in advance for your help! I do really need it an appreciate all in this group! ============================= Veso Lukovic PeopleSoft Systems St. Vincent Hospital Indianapolis IN. vmlukovi@stvincent.org ============================= >>> "John A. Hayes" 09/29/99 03:43PM >>> Mark, Just an FYI: You can add a tns_admin key to the Oracle section of the registry to point to a central tnsnames.ora file. This way you get out of maintaining the tnsnames.ora on each workstation. ---------------------------------------------- Hayes Systems Solutions, Inc. Canoga Park, CA mailto:owner@hss-usa.com http://www.hss-usa.com ---------------------------------------------- At 02:52 PM 9/29/99 -0500, you wrote: >[Mark A. Clements] > >SQR's launched from process scheduler can run either on the server or the >client. The way we do things here, the user logs into a three tier >environment >to do all of their PeopleSoft processing, but when they launch an SQR, it >actually spins off a two-tier process on their workstation unless they specify >that the process run on the server. I have a hunch that is what is happening >to you. In that case, the sql*net connection and tns names must be maintained >on each workstation. > >Could that be the problem? > >-Mark > >They run it from within PeopleSoft, from the menu. I assume it is three >tier, the database resides in Ottawa, and we are 3500 miles west of there. >The error happens after the PeopleSoft login, the users are able to >edit/retrieve data within the form, it is running the 'canned' reports that >generates the error. > >Lorne Gottschewski >Programmer >WTIS >19 Wing. CFB Comox >----- Original Message ----- >From: Mark Clements >To: Multiple recipients of list SQR-USERS >Sent: Wednesday, September 29, 1999 11:53 AM >Subject: Re: SQR 4701 Error > > > > [Mark A. Clements] > > > > Are they running the report from a PeopleSoft panel or from workbench? It >also > > makes a difference if they are running in 2 or 3 tier modes. > > > > > > -Mark > > > > I have several work stations that use PeopleSoft (v7) and I have recently >added > > an Oracle 8.0.5 Application written with Oracle Developer 6 on those >machines. > > Ever scince, the user can loggon to peopleSoft, but is unable to run a >report > > without getting the following: > > SQRW V3.0.18.1.1 (SQR 4701) Can't logon to database. ---------------------------------------------- Hayes Systems Solutions, Inc. Canoga Park, CA mailto:owner@hss-usa.com http://www.hss-usa.com ---------------------------------------------- From owner-sqr-users@list.iex.net Mon Oct 4 15:45:33 1999 Date: Mon, 4 Oct 1999 15:35:29 -0500 From: "Manning, Shinta" Subject: Re: Deleting a file created by an sqr. #status = delete($filename) > -----Original Message----- > From: Sunil Kanderi [SMTP:sunil_kanderi_at_doit-mail4@CCMAIL.ADP.WISC.EDU] > Sent: Monday, October 04, 1999 4:26 PM > To: Multiple recipients of list SQR-USERS > Subject: Deleting a file created by an sqr. > > Is there a way to delete a file that was created by the sqr from > within the sqr. If so please let me know the syntax. > > Thanks, > sunil. From owner-sqr-users@list.iex.net Mon Oct 4 15:49:20 1999 Date: Mon, 4 Oct 1999 16:36:38 -0400 From: Bob Buford-Abba Systems Subject: Re: Deleting a file created by an sqr. This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BF0EA8.2877A50C Content-Type: text/plain; charset="iso-8859-1" Sunil, In addition to the suggestions by John and Manning, remember that if you created the file in current sqr (or sqc called in current instance of program), you need to close that file to do file operations (move, delete, rename, etc.). Bob -----Original Message----- From: Sunil Kanderi [mailto:sunil_kanderi_at_doit-mail4@CCMAIL.ADP.WISC.EDU] Sent: Monday, October 04, 1999 5:26 PM To: Multiple recipients of list SQR-USERS Subject: Deleting a file created by an sqr. Is there a way to delete a file that was created by the sqr from within the sqr. If so please let me know the syntax. Thanks, sunil. ------_=_NextPart_001_01BF0EA8.2877A50C Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Deleting a file created by an sqr.

Sunil,

In addition to the suggestions by John and Manning, = remember that if you created the file in current sqr (or sqc called in = current instance of program), you need to close that file to do file = operations (move, delete, rename, etc.).

Bob

-----Original Message-----
From: Sunil Kanderi
[mailto:s= unil_kanderi_at_doit-mail4@CCMAIL.ADP.WISC.EDU]
Sent: Monday, October 04, 1999 5:26 PM
To: Multiple recipients of list SQR-USERS
Subject: Deleting a file created by an sqr.


     Is there a way to delete a = file that was created by the sqr from
     within the sqr. If so = please let me know the syntax.

     Thanks,
     sunil.

------_=_NextPart_001_01BF0EA8.2877A50C-- From owner-sqr-users@list.iex.net Mon Oct 4 15:46:10 1999 Date: Mon, 4 Oct 1999 16:38:20 -0400 From: John Milardovic Subject: Re: Deleting a file created by an sqr. Hi Sunil. ----------------------------------- SQR help = "delete" Deletes the file filename. The function returns either a zero (0) to indicate success or the value returned from the operating system to indicate an error. Syntax: stat_var = delete(filename) filename = text literal, column, variable, or expression stat_var = decimal, float, or integer variable Example: let #fstatus = delete($filename) Function arguments are enclosed in parentheses and can be nested. Arguments referenced as x, y, and z indicate the first, second, and third argument to a function. Otherwise, functions take a single argument. Use parentheses to override the normal precedence rules. --------------------------------------------- Or else use call system/call system using. HTH John Milardovic > -----Original Message----- > From: Sunil Kanderi [SMTP:sunil_kanderi_at_doit-mail4@CCMAIL.ADP.WISC.EDU] > Sent: Monday, October 04, 1999 5:26 PM > To: Multiple recipients of list SQR-USERS > Subject: Deleting a file created by an sqr. > > Is there a way to delete a file that was created by the sqr from > within the sqr. If so please let me know the syntax. > > Thanks, > sunil. From owner-sqr-users@list.iex.net Mon Oct 4 15:58:05 1999 Date: Mon, 4 Oct 1999 16:49:38 -0400 From: Vivek Pandian Subject: Re: Deleting a file created by an sqr. delete filename At 05:26 PM 10/4/99 , you wrote: > Is there a way to delete a file that was created by the sqr from > within the sqr. If so please let me know the syntax. > > Thanks, > sunil. > From owner-sqr-users@list.iex.net Mon Oct 4 15:38:44 1999 Date: Mon, 4 Oct 1999 15:26:24 -0600 From: Sunil Kanderi Subject: Deleting a file created by an sqr. Is there a way to delete a file that was created by the sqr from within the sqr. If so please let me know the syntax. Thanks, sunil. From owner-sqr-users@list.iex.net Mon Oct 4 18:00:29 1999 Date: Mon, 4 Oct 1999 18:51:35 -0400 From: Peter Alan Burton Subject: Re: Deleting a file created by an sqr. Sunil, 2 == File does not exist 13 == Permission denied Peter Sunil Kanderi wrote: > Thanks for all the responses. I should have looked up my sqr language > reference before jumping on the user's list for a solution. > > When I try to delete the file created after closing it,I get a file > #status of either 2 or 13. I am running the sqr on the client and my > operating system is Win NT. > > Anybody knows what the #status of 2 or 13 mean?? > > Thanks again, > sunil. > > ______________________________ Reply Separator _________________________________ > Subject: Re: Deleting a file created by an sqr. > Author: at IPNET > Date: 10/4/99 4:36 PM > > Sunil, > > In addition to the suggestions by John and Manning, remember that if you > created the file in current sqr (or sqc called in current instance of > program), you need to close that file to do file operations (move, delete, > rename, etc.). > > Bob > > -----Original Message----- > From: Sunil Kanderi > [mailto:sunil_kanderi_at_doit-mail4@CCMAIL.ADP.WISC.EDU] > Sent: Monday, October 04, 1999 5:26 PM > To: Multiple recipients of list SQR-USERS > Subject: Deleting a file created by an sqr. > > Is there a way to delete a file that was created by the sqr from > within the sqr. If so please let me know the syntax. > > Thanks, > sunil. From owner-sqr-users@list.iex.net Mon Oct 4 17:44:09 1999 Date: Mon, 4 Oct 1999 17:33:30 -0600 From: Sunil Kanderi Subject: Re[2]: Deleting a file created by an sqr. Thanks for all the responses. I should have looked up my sqr language reference before jumping on the user's list for a solution. When I try to delete the file created after closing it,I get a file #status of either 2 or 13. I am running the sqr on the client and my operating system is Win NT. Anybody knows what the #status of 2 or 13 mean?? Thanks again, sunil. ______________________________ Reply Separator _________________________________ Subject: Re: Deleting a file created by an sqr. Author: at IPNET Date: 10/4/99 4:36 PM Sunil, In addition to the suggestions by John and Manning, remember that if you created the file in current sqr (or sqc called in current instance of program), you need to close that file to do file operations (move, delete, rename, etc.). Bob -----Original Message----- From: Sunil Kanderi [mailto:sunil_kanderi_at_doit-mail4@CCMAIL.ADP.WISC.EDU] Sent: Monday, October 04, 1999 5:26 PM To: Multiple recipients of list SQR-USERS Subject: Deleting a file created by an sqr. Is there a way to delete a file that was created by the sqr from within the sqr. If so please let me know the syntax. Thanks, sunil. From owner-sqr-users@list.iex.net Mon Oct 4 18:42:18 1999 Date: Mon, 4 Oct 1999 16:34:07 -0700 From: Jim Wyant Subject: Calling SQRW.DLL via Access or VB5 Group, There was some activity here which dealt with calling sqrw.dll via Access or VB5. Has anyone managed to successfully do it without getting an "illegal operation" in KRNL386? VB5 crashes repeatedly after running the test sqr. BTW, when run as a shell process I have no problems. Thanks for your input, Jim ===== __________________________________________________ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com From owner-sqr-users@list.iex.net Tue Oct 5 08:00:02 1999 Date: Tue, 5 Oct 1999 08:52:21 -0400 From: "Love, Kristin" Subject: Finding Children in a nonbinary tree-- a recursive puzzle? I have been working on this puzzle for a while and would like to throw it open to some larger brains out there.... I am trying to find all the relatives when given a particular unit. I have a table which looks like this: <<...>> Role =0 means the unit is a parent, role 1 means the unit is a child. So, for example, when given unit 171054, I want to know that its ancestors were 176040 ("father") and 162281 ("grandfather"). This I can do successfully with a recursive function: BEGIN-PROCEDURE FindAncestor(#usn,:#oldest) BEGIN-SELECT SerialNo show 'Parent ' $spaces &SerialNo Do FindAncestor(&SerialNo,#temp) move &SerialNo to #oldest FROM tablename m WHERE m.relateid IN ( SELECT relateid FROM tablename WHERE serialno = #usn) AND role=0 AND m.serialno <> #usn END-SELECT END-PROCEDURE FindAncestor But I seem to be having difficulty going down the tree to find descendants. The problem is, that when I have a parent with multiple children, the recursive call only seems to return the set of children, but doesn't take each child and find its children. Perhaps this is not the best approach for finding the children. Does anyone have a fresh idea? Really, I want to print ALL the relatives on the report, grouped by relateid. So the report would look like this: RelateId SerialNo Role 1 162281 0 170640 1 ........................(more data) 6 240277 0 240278 0 243341 1 243342 1 Of course, there would be more info from other tables on this report, like the unit's name, etc. but I know I need at least this much to start..... I initially tried to solve this algorithm with connect by, but, as you see, the unit's parent is not on the table...AND a unit can have multiple parents. :) Kristin From owner-sqr-users@list.iex.net Tue Oct 5 08:13:51 1999 Date: Tue, 5 Oct 1999 09:12:20 -0400 From: "Fay, Brannon" Subject: Re: Finding Children in a nonbinary tree-- a recursive puzzle? If you are using Oracle, look at using START WITH and CONNECT BY PRIOR. I've used it in the past for this type of hierarchical query, but I don't understand it well enough to give advice on it. It should give you what you need though. Brannon Fay -----Original Message----- From: Love, Kristin [mailto:klove@MSA.COM] Sent: Tuesday, October 05, 1999 8:52 AM To: Multiple recipients of list SQR-USERS Subject: Finding Children in a nonbinary tree-- a recursive puzzle? I have been working on this puzzle for a while and would like to throw it open to some larger brains out there.... I am trying to find all the relatives when given a particular unit. I have a table which looks like this: <<...>> Role =0 means the unit is a parent, role 1 means the unit is a child. So, for example, when given unit 171054, I want to know that its ancestors were 176040 ("father") and 162281 ("grandfather"). This I can do successfully with a recursive function: BEGIN-PROCEDURE FindAncestor(#usn,:#oldest) BEGIN-SELECT SerialNo show 'Parent ' $spaces &SerialNo Do FindAncestor(&SerialNo,#temp) move &SerialNo to #oldest FROM tablename m WHERE m.relateid IN ( SELECT relateid FROM tablename WHERE serialno = #usn) AND role=0 AND m.serialno <> #usn END-SELECT END-PROCEDURE FindAncestor But I seem to be having difficulty going down the tree to find descendants. The problem is, that when I have a parent with multiple children, the recursive call only seems to return the set of children, but doesn't take each child and find its children. Perhaps this is not the best approach for finding the children. Does anyone have a fresh idea? Really, I want to print ALL the relatives on the report, grouped by relateid. So the report would look like this: RelateId SerialNo Role 1 162281 0 170640 1 ........................(more data) 6 240277 0 240278 0 243341 1 243342 1 Of course, there would be more info from other tables on this report, like the unit's name, etc. but I know I need at least this much to start..... I initially tried to solve this algorithm with connect by, but, as you see, the unit's parent is not on the table...AND a unit can have multiple parents. :) Kristin From owner-sqr-users@list.iex.net Tue Oct 5 08:19:40 1999 Date: Tue, 5 Oct 1999 09:14:18 -0400 From: Sam Spritzer Subject: Re: Finding Children in a nonbinary tree-- a recursive puzzle? Kristin... I am biting my tongue on this one and haven't tried it yet but would it be possible to pass the 'child and find its children' into your WHERE clause? Sam <<< "Love, Kristin" 10/ 5 8:52a >>> I have been working on this puzzle for a while and would like to throw it open to some larger brains out there.... I am trying to find all the relatives when given a particular unit. I have a table which looks like this: <<...>> Role =0 means the unit is a parent, role 1 means the unit is a child. So, for example, when given unit 171054, I want to know that its ancestors were 176040 ("father") and 162281 ("grandfather"). This I can do successfully with a recursive function: BEGIN-PROCEDURE FindAncestor(#usn,:#oldest) BEGIN-SELECT SerialNo show 'Parent ' $spaces &SerialNo Do FindAncestor(&SerialNo,#temp) move &SerialNo to #oldest FROM tablename m WHERE m.relateid IN ( SELECT relateid FROM tablename WHERE serialno = #usn) AND role=0 AND m.serialno <> #usn END-SELECT END-PROCEDURE FindAncestor But I seem to be having difficulty going down the tree to find descendants. The problem is, that when I have a parent with multiple children, the recursive call only seems to return the set of children, but doesn't take each child and find its children. Perhaps this is not the best approach for finding the children. Does anyone have a fresh idea? Really, I want to print ALL the relatives on the report, grouped by relateid. So the report would look like this: RelateId SerialNo Role 1 162281 0 170640 1 ........................(more data) 6 240277 0 240278 0 243341 1 243342 1 Of course, there would be more info from other tables on this report, like the unit's name, etc. but I know I need at least this much to start..... I initially tried to solve this algorithm with connect by, but, as you see, the unit's parent is not on the table...AND a unit can have multiple parents. :) Kristin From owner-sqr-users@list.iex.net Tue Oct 5 09:49:14 1999 Date: Tue, 5 Oct 1999 10:36:20 -0400 From: "Bencke, Gina" Subject: Using Xerox DocuPrint 4890 NPS Printer Does anyone have experience implementing SQR reports using the Xerox DocuPrint 4890 NPS Printer? If so I would like to discuss implementation options. Thanks. Gina Bencke From owner-sqr-users@list.iex.net Tue Oct 5 10:11:55 1999 Date: Tue, 5 Oct 1999 11:11:47 -0400 From: Tony DeLia Subject: Re: Finding Children in a nonbinary tree-- a recursive puzzle? Hi Kristin, I posted something similar on my website... http://www.sqrtools.com (OR http://www.sqrtools.com/tdsvc.htm for direct access to the page) In my example I establish the "tree" hierarchy using the EMPLID->SUPERVISOR_ID relationship found in the EMPLOYMENT table in HR... Here's a sample of the output (HR Demo data)... 8001 - Schumacher,Simon 8102 - Sullivan,Theresa 8406 - Davis,William D. 8517 - Holden,Peter 8664 - Carroll,Brucest 8668 - O'Brien,Michael 8730 - Smithers,Jasmine 8761 - Vallero,Joaquin G010 - Masters,Jason 8409 - Westlund,Julie 8516 - Akers,Danielle 8317 - Norton,Terry 8421 - Jaegar,Annette 8832 - Tanner,Doris ...etc.... The high-level id '8001' was entered and all the sub-ordinate or 'child' rows were produced in tree format... No 'true' recursive procedures were required to accomplish this... This could be easily modified for your purposes... your relationships are a little different but can be accounted for in the first portion of the program (Process-Phase1)... Hope this helps, Tony DeLia PS - I expanded this procedure last week (not on website) to produce a Tree Structure (pstreestrct, pstreedefn, pstreenode, etc...) for use by an external Lotus Notes application for security... it allows Supervisors to access Salary Planning panels for all subordinates... the tree structure can be refreshed any time due to a change in Supervisors... works great! Love, Kristin wrote: > > I have been working on this puzzle for a while and would like to throw it > open to some larger brains out there.... > > I am trying to find all the relatives when given a particular unit. > > I have a table which looks like this: > <<...>> > > Role =0 means the unit is a parent, role 1 means the unit is a child. So, > for example, when given unit 171054, I want to know that its ancestors were > 176040 ("father") and 162281 ("grandfather"). This I can do successfully > with a recursive function: > BEGIN-PROCEDURE FindAncestor(#usn,:#oldest) > BEGIN-SELECT > SerialNo > show 'Parent ' $spaces &SerialNo > Do FindAncestor(&SerialNo,#temp) > move &SerialNo to #oldest > FROM tablename m > WHERE m.relateid IN ( SELECT relateid > FROM tablename > WHERE serialno = #usn) > AND role=0 > AND m.serialno <> #usn > END-SELECT > > END-PROCEDURE FindAncestor > > But I seem to be having difficulty going down the tree to find descendants. > The problem is, that when I have a parent with multiple children, the > recursive call only seems to return the set of children, but doesn't take > each child and find its children. > > Perhaps this is not the best approach for finding the children. Does anyone > have a fresh idea? > > Really, I want to print ALL the relatives on the report, grouped by > relateid. So the report would look like this: > > RelateId SerialNo Role > 1 162281 0 > 170640 1 > ........................(more data) > 6 240277 0 > 240278 0 > 243341 1 > 243342 1 > > Of course, there would be more info from other tables on this report, like > the unit's name, etc. but I know I need at least this much to start..... > > I initially tried to solve this algorithm with connect by, but, as you see, > the unit's parent is not on the table...AND a unit can have multiple > parents. > :) Kristin -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com http://www.sqrtools.com From owner-sqr-users@list.iex.net Tue Oct 5 10:24:04 1999 Date: Tue, 5 Oct 1999 08:17:23 -0700 From: Deb Maynard Subject: Finding minumums Hello, I am new to the SQR arena and I am pretty lost. I can do your basic selects and data manipulation, but I've just been assigned to a report modification that may be a little out of my league, so any and all help would be extremely appreciated. The client I'm working for has captured time data from one system and interfaced it into a 'comments' field in a PeopleSoft table. A typical row looks something like this: 800-1500--TRC: REG--HRS: 7 (start time, end time, time reporting code, number of hours). What I need to do is capture the start and end times and print them on the report. I have almost successfully done this using substrings (it gets tricky because some start times are 3 digits, some 4). The problem is, sometimes an employee has multiple rows, one containing the actual start time (the minimum of all of the start times) and another containing the actual end time (the maximum of all of the end times). Is there a way I can evaluate the substring variable and only select and print the one I need? I assume there is, but it definitely wasn't covered in my SQR class! I hope my explanation was clear. Please offer any help you can! Thanks. Deb From owner-sqr-users@list.iex.net Tue Oct 5 10:44:05 1999 Date: Tue, 5 Oct 1999 11:39:41 -0400 From: Vivek Pandian Subject: Re: Finding minumums There are two simple solutions. 1. From the Main Employee Select call two procedures which returns min and max time like select subtr(to_char(min(time))) from ps_tablename where emplid = &emplid and ... 2. Use an array and store all times for an employee and do a break on emplid and do the arthmetics on the array and get the value you want and print it. Hope this helps Regards Vivek At 11:17 AM 10/5/99 , you wrote: >Hello, > >I am new to the SQR arena and I am pretty lost. I can do your basic selects and >data manipulation, but I've just been assigned to a report modification that may >be a little out of my league, so any and all help would be extremely >appreciated. The client I'm working for has captured time data from one system >and interfaced it into a 'comments' field in a PeopleSoft table. A typical row >looks something like this: 800-1500--TRC: REG--HRS: 7 (start time, end time, >time reporting code, number of hours). What I need to do is capture the start >and end times and print them on the report. I have almost successfully done this >using substrings (it gets tricky because some start times are 3 digits, some 4). >The problem is, sometimes an employee has multiple rows, one containing the >actual start time (the minimum of all of the start times) and another containing >the actual end time (the maximum of all of the end times). Is there a way I can >evaluate the substring variable and only select and print the one I need? I >assume there is, but it definitely wasn't covered in my SQR class! > >I hope my explanation was clear. Please offer any help you can! Thanks. > >Deb > From owner-sqr-users@list.iex.net Tue Oct 5 10:48:33 1999 Date: Tue, 5 Oct 1999 11:45:57 -0400 From: Tony DeLia Subject: Re: Using Xerox DocuPrint 4890 NPS Printer Gina, I've worked in the past with the Xerox 'intelligent' printers that require JDT files (Job Description Tickets) and embedded print control commands... STARTLM, SETJDT, etc... I don't recall the exact printer model... You can include custom codes for each line of data that is passed to the print server... the codes are defined on the server with the position of the data in the file along with the mapping on the form (data placement)... You create a from image (using PowerPoint, for example) and the from data (from the file) is placed on top of the image in the appropriate locations... Is this what you mean? If so e-mail and I'll try to dig up what I can (this was a while ago)... Tony DeLia PS - I used this to create custom statements in PS A/R 7.5 (Beginning/Ending balances, Aging, Detailed item activity in period, discount summary, Correspondence Customer Roll-ups, etc... Bencke, Gina wrote: > > Does anyone have experience implementing SQR reports using the Xerox > DocuPrint 4890 NPS Printer? If so I would like to discuss > implementation options. > > Thanks. > > Gina Bencke -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com http://www.sqrtools.com From owner-sqr-users@list.iex.net Tue Oct 5 12:00:53 1999 Date: Tue, 5 Oct 1999 12:48:18 -0400 From: "Bencke, Gina" Subject: Re: Using Xerox DocuPrint 4890 NPS Printer Tony, It's nice to hear from you again! Yes we will be using JDT. Our hope is that we can set up either JDT files that essentially match the PeopleSoft setup01 and setup02 declare-printer/declare-layout and then use the printer-init string to specify the appropriate JDT to use when printing, or set up print queues that will use the appropriate JDT. Does this sound like a viable approach? Gina -----Original Message----- From: Tony DeLia [mailto:tdelia@EROLS.COM] Sent: Tuesday, October 05, 1999 11:46 AM To: Multiple recipients of list SQR-USERS Subject: Re: Using Xerox DocuPrint 4890 NPS Printer Gina, I've worked in the past with the Xerox 'intelligent' printers that require JDT files (Job Description Tickets) and embedded print control commands... STARTLM, SETJDT, etc... I don't recall the exact printer model... You can include custom codes for each line of data that is passed to the print server... the codes are defined on the server with the position of the data in the file along with the mapping on the form (data placement)... You create a from image (using PowerPoint, for example) and the from data (from the file) is placed on top of the image in the appropriate locations... Is this what you mean? If so e-mail and I'll try to dig up what I can (this was a while ago)... Tony DeLia PS - I used this to create custom statements in PS A/R 7.5 (Beginning/Ending balances, Aging, Detailed item activity in period, discount summary, Correspondence Customer Roll-ups, etc... Bencke, Gina wrote: > > Does anyone have experience implementing SQR reports using the Xerox > DocuPrint 4890 NPS Printer? If so I would like to discuss > implementation options. > > Thanks. > > Gina Bencke -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com http://www.sqrtools.com From owner-sqr-users@list.iex.net Tue Oct 5 12:00:52 1999 Date: Tue, 5 Oct 1999 11:53:50 -0500 From: itzgee Subject: Re: Finding minumums Firstly: You do not have an sqr problem, you have a data entry and interface design problem. Having said that, my next question is: Are each of the segments in the comments field the same size? 800-1500--TRC: REG--HRS: 7 in other words, can you always assume that the first four digits are the start time? Can you assume that all of the comments are time related? If so, try this : select substr(comments, (length(comments)- 22), 4) as final_end_time what this does is takes a substring from an absolute position in the substring. If you have more than just time comments -- and you probably do -- you're in for some real coding fun. Here is what I would do : get the length of the comments. create a loop and start at the end of the comments move length(comments) to #len_var move 0 to #found while #found = 0 let #len_var = #len_var -20 #found = instr(&comments, '--TRC', #len_var) if #len_var < 0 move -1 to #found end-if end-while At this point, if #found is negative, it didn't work. If #found is positive, then it is the location of the last digit of time. ltrim ((rtrim(( substr(&comments, #found-4, 4)), '-')), '-') should give you the last checkout time. From owner-sqr-users@list.iex.net Tue Oct 5 12:03:57 1999 Date: Tue, 5 Oct 1999 12:55:43 -0400 From: Michael Nerren Subject: REMOVE MICHAEL_NERREN@PEOPLESOFT.COM MICHAEL_NERREN@PEOPLESOFT.COM remove from list From owner-sqr-users@list.iex.net Tue Oct 5 15:09:56 1999 Date: Tue, 5 Oct 1999 14:59:25 -0500 From: Kenny Melton Subject: Date Variables Hello Everyone, I am using SQR 4.3.4 and am having a strange problem (or a severe brain burp). Can anyone tell me why the following works the way it does?: BEGIN-SETUP DECLARE-VARIABLE DATE $MINDATE DATE $MAXDATE END-DECLARE END-SETUP BEGIN-PROCEDURE COMPARE-DATES LET $MAXDATE = '01-SEP-1999' LET $MINDATE = '31-AUG-1999' SHOW 'MAX DATE = ' $MAXDATE SHOW 'MIN DATE = ' $MINDATE IF $MAXDATE = $MINDATE SHOW 'MAXDATE = MINDATE' END-IF IF $MAXDATE <> $MINDATE SHOW 'MAXDATE <> MINDATE' END-IF IF $MAXDATE > $MINDATE SHOW 'MAXDATE > MINDATE' END-IF IF $MAXDATE < $MINDATE SHOW 'MAXDATE < MINDATE' END-IF END-PROCEDURE The log file output looks like this: MAX DATE = 01-SEP-1999 MIN DATE = 31-AUG-1999 MAXDATE = MINDATE I'm kind of stumped on this one. I think it has something to do with the internal "string" representation of the date variables, but all my juggling with the SQR.INI file settings didn't have any impact. I have also tried it with the "MOVE" command in place of the "LET" command above and, again, no difference. Oh, one more thing -- this happens consistently when one of the dates is an end-of-month date, and the other date is the following first-of-month date. OK Gurus, what am I missing? Thanks in advance, Kenny Melton Sr. Technical Architect Tandy Information Services PeopleSoft Financial Systems kmelto1@tandy.com From owner-sqr-users@list.iex.net Tue Oct 5 15:17:09 1999 Date: Tue, 5 Oct 1999 16:01:45 -0400 From: "Love, Kristin" Subject: Re: Finding Children in a nonbinary tree-- a recursive puzzle? Thanks, Tony!!!!!!!!!!!!! Your tree algorithm showed me how to solve the puzzle in an iterative fashion. Here it is (names are changed to protect the innocent............) !*************************************************************************** * ! ! PROCEDURE MAIN: ! !*************************************************************************** * BEGIN-PROCEDURE MAIN move 240278 to #serialno !171054 show 'Original ' #serialno edit {serialnoedit} DO FindAncestorA ! You have to start at the top of the tree to get all the children show 'ancestor is ' #oldest edit {serialnoedit} let #relative_max= {max_relative}-1 move 0 to #thisrow move 0 to #counter ! Set up first row of the array move 0 to #relative_start put #relative_start #oldest into relative (0) parent child ! First row reads: parent:0 child:192071(or whatever number you entered) do FillUpArray End-Procedure MAIN !*************************************************************************** * ! ! PROCEDURE FillUpArray: Fills array with parents and children ! Parameters: none ! !*************************************************************************** * BEGIN-PROCEDURE FillUpArray WHILE #thisrow <= #counter and #thisrow < {max_relative} let #lastchild = relative.child(#thisrow) do populate-array add 1 to #thisrow !If populate-array fails, #thisrow will become > #counter END-WHILE END-PROCEDURE FillUpArray !*************************************************************************** * ! ! PROCEDURE Populate-Array: !*************************************************************************** * BEGIN-PROCEDURE Populate-Array BEGIN-SELECT serialno add 1 to #counter if #counter > {max_relative} -1 exit-select end-if let relative.parent(#counter)=#lastchild let relative.child(#counter)=&serialno FROM thetable m WHERE m.relateid IN ( SELECT relateid FROM thetable WHERE serialno = #lastchild AND code=0) !only relations where #lastchild is parent AND code=1 !1=child, 0=parent AND m.serialno <> #lastchild ORDER BY serialno END-SELECT END-PROCEDURE Populate-Array !*************************************************************************** * ! ! PROCEDURE PrintResults ! !*************************************************************************** * BEGIN-PROCEDURE PrintResults move 1 to #I !The first element of the array shows the oldest ancestor WHILE #I <= #thisrow - 1 ! When you added 1 to #thisrow, it stopped the loop let $parent= relative.parent(#I) let $child=relative.child(#i) show #I edit 9999 ' Parent:' $parent edit {serialnoedit} ' child ' $child edit {serialnoedit} add 1 to #I END-WHILE END-PROCEDURE PrintResults The key to your program was the WHILE loop.......I would have never thought of that. :) Kristin > -----Original Message----- > From: Tony DeLia [SMTP:tdelia@EROLS.COM] > Sent: Tuesday, October 05, 1999 11:12 AM > To: Multiple recipients of list SQR-USERS > Subject: Re: Finding Children in a nonbinary tree-- a recursive > puzzle? > > Hi Kristin, > I posted something similar on my website... http://www.sqrtools.com > (OR http://www.sqrtools.com/tdsvc.htm for direct access to the page) > > From owner-sqr-users@list.iex.net Tue Oct 5 15:12:34 1999 Date: Tue, 5 Oct 1999 16:01:53 -0400 From: Paresh Patel Subject: .SPF printing I recently came across several reports which require the use of the graphic statement (horz-line, vert-line, box) in the SQR. From what I understand, the only way to display/print these "graphics" on the reports is to generate a .spf file. I tried using SQR Viewer and SQR Print to print the .spf file, but the report does not print properly. If anyone can assist me in any manner, I'd appreciate it. Thanks. Paresh Patel Software Engineer Bell Atlantic NSI From owner-sqr-users@list.iex.net Tue Oct 5 15:19:33 1999 Date: Tue, 5 Oct 1999 13:12:13 -0700 From: Reinier de Ruiter Subject: Re: Date Variables Use strtodate for your assignment: LET $MAXDATE = strtodate('01-SEP-1999','DD-MON-YYYY') LET $MINDATE = strtodate('31-AUG-1999','DD-MON-YYYY') Reinier de Ruiter Programmer/Analyst Apollo Group, University of Phoenix Phoenix, Arizona tel:480-557-1158 email: reinier.deruiter@apollogrp.edu -----Original Message----- From: Kenny Melton [mailto:KMELTO1@TANDY.COM] Sent: Tuesday, October 05, 1999 12:59 PM To: Multiple recipients of list SQR-USERS Subject: Date Variables Hello Everyone, I am using SQR 4.3.4 and am having a strange problem (or a severe brain burp). Can anyone tell me why the following works the way it does?: BEGIN-SETUP DECLARE-VARIABLE DATE $MINDATE DATE $MAXDATE END-DECLARE END-SETUP BEGIN-PROCEDURE COMPARE-DATES LET $MAXDATE = '01-SEP-1999' LET $MINDATE = '31-AUG-1999' SHOW 'MAX DATE = ' $MAXDATE SHOW 'MIN DATE = ' $MINDATE IF $MAXDATE = $MINDATE SHOW 'MAXDATE = MINDATE' END-IF IF $MAXDATE <> $MINDATE SHOW 'MAXDATE <> MINDATE' END-IF IF $MAXDATE > $MINDATE SHOW 'MAXDATE > MINDATE' END-IF IF $MAXDATE < $MINDATE SHOW 'MAXDATE < MINDATE' END-IF END-PROCEDURE The log file output looks like this: MAX DATE = 01-SEP-1999 MIN DATE = 31-AUG-1999 MAXDATE = MINDATE I'm kind of stumped on this one. I think it has something to do with the internal "string" representation of the date variables, but all my juggling with the SQR.INI file settings didn't have any impact. I have also tried it with the "MOVE" command in place of the "LET" command above and, again, no difference. Oh, one more thing -- this happens consistently when one of the dates is an end-of-month date, and the other date is the following first-of-month date. OK Gurus, what am I missing? Thanks in advance, Kenny Melton Sr. Technical Architect Tandy Information Services PeopleSoft Financial Systems kmelto1@tandy.com From owner-sqr-users@list.iex.net Tue Oct 5 15:27:30 1999 Date: Tue, 5 Oct 1999 16:17:18 -0400 From: Rick Creel Subject: Re: Date Variables I dont know why it works that way... using the relational operators like that. I use the DATEDIFF function to compare dates, and it works quite well. Kenny Melton on 10/05/99 03:59:25 PM Please respond to SQR-USERS@list.iex.net To: Multiple recipients of list SQR-USERS cc: (bcc: Rick Creel/IT/Aon Consulting) Subject: Date Variables Hello Everyone, I am using SQR 4.3.4 and am having a strange problem (or a severe brain burp). Can anyone tell me why the following works the way it does?: BEGIN-SETUP DECLARE-VARIABLE DATE $MINDATE DATE $MAXDATE END-DECLARE END-SETUP BEGIN-PROCEDURE COMPARE-DATES LET $MAXDATE = '01-SEP-1999' LET $MINDATE = '31-AUG-1999' SHOW 'MAX DATE = ' $MAXDATE SHOW 'MIN DATE = ' $MINDATE IF $MAXDATE = $MINDATE SHOW 'MAXDATE = MINDATE' END-IF IF $MAXDATE <> $MINDATE SHOW 'MAXDATE <> MINDATE' END-IF IF $MAXDATE > $MINDATE SHOW 'MAXDATE > MINDATE' END-IF IF $MAXDATE < $MINDATE SHOW 'MAXDATE < MINDATE' END-IF END-PROCEDURE The log file output looks like this: MAX DATE = 01-SEP-1999 MIN DATE = 31-AUG-1999 MAXDATE = MINDATE I'm kind of stumped on this one. I think it has something to do with the internal "string" representation of the date variables, but all my juggling with the SQR.INI file settings didn't have any impact. I have also tried it with the "MOVE" command in place of the "LET" command above and, again, no difference. Oh, one more thing -- this happens consistently when one of the dates is an end-of-month date, and the other date is the following first-of-month date. OK Gurus, what am I missing? Thanks in advance, Kenny Melton Sr. Technical Architect Tandy Information Services PeopleSoft Financial Systems kmelto1@tandy.com From owner-sqr-users@list.iex.net Tue Oct 5 15:30:02 1999 Date: Tue, 5 Oct 1999 16:20:12 -0400 From: "Love, Kristin" Subject: Re: Date Variables The date stored is actually dd-mon-yyyy hh24:mi:ss That is, it is a date and a time together. So perhaps when you are storing it, it is storing a midnight value???? :) Kristin > -----Original Message----- > From: Kenny Melton [SMTP:KMELTO1@TANDY.COM] > Sent: Tuesday, October 05, 1999 3:59 PM > To: Multiple recipients of list SQR-USERS > Subject: Date Variables > > Hello Everyone, > > I am using SQR 4.3.4 and am having a strange problem (or a severe brain > burp). Can anyone tell me why the following works the way it does?: > > BEGIN-SETUP > DECLARE-VARIABLE > DATE $MINDATE > DATE $MAXDATE > END-DECLARE > END-SETUP > > BEGIN-PROCEDURE COMPARE-DATES > LET $MAXDATE = '01-SEP-1999' > LET $MINDATE = '31-AUG-1999' > > SHOW 'MAX DATE = ' $MAXDATE > SHOW 'MIN DATE = ' $MINDATE > > IF $MAXDATE = $MINDATE > SHOW 'MAXDATE = MINDATE' > END-IF > > IF $MAXDATE <> $MINDATE > SHOW 'MAXDATE <> MINDATE' > END-IF > > IF $MAXDATE > $MINDATE > SHOW 'MAXDATE > MINDATE' > END-IF > > IF $MAXDATE < $MINDATE > SHOW 'MAXDATE < MINDATE' > END-IF > > END-PROCEDURE > > The log file output looks like this: > > MAX DATE = 01-SEP-1999 > MIN DATE = 31-AUG-1999 > MAXDATE = MINDATE > > I'm kind of stumped on this one. I think it has something to do with the > internal "string" representation of the date variables, but all my > juggling > with the SQR.INI file settings didn't have any impact. I have also tried > it > with the "MOVE" command in place of the "LET" command above and, again, no > difference. Oh, one more thing -- this happens consistently when one of > the > dates is an end-of-month date, and the other date is the following > first-of-month date. OK Gurus, what am I missing? > > Thanks in advance, > Kenny Melton > Sr. Technical Architect > Tandy Information Services > PeopleSoft Financial Systems > kmelto1@tandy.com From owner-sqr-users@list.iex.net Tue Oct 5 15:23:31 1999 Date: Tue, 5 Oct 1999 16:20:32 -0400 From: "Fay, Brannon" Subject: Re: Date Variables Kenny You either want to use strtodate, i.e. LET $MAXDATE = strtodate( '01-SEP-1999', 'DD-MON-YYYY' ) or change the default date format via: ALTER-LOCALE DATE-EDIT-MASK = 'DD-MON-YYYY' I'm not quite sure if that latter will work or not but the strtodate will. HTH, Brannon Fay -----Original Message----- From: Kenny Melton [mailto:KMELTO1@TANDY.COM] Sent: Tuesday, October 05, 1999 3:59 PM To: Multiple recipients of list SQR-USERS Subject: Date Variables Hello Everyone, I am using SQR 4.3.4 and am having a strange problem (or a severe brain burp). Can anyone tell me why the following works the way it does?: BEGIN-SETUP DECLARE-VARIABLE DATE $MINDATE DATE $MAXDATE END-DECLARE END-SETUP BEGIN-PROCEDURE COMPARE-DATES LET $MAXDATE = '01-SEP-1999' LET $MINDATE = '31-AUG-1999' SHOW 'MAX DATE = ' $MAXDATE SHOW 'MIN DATE = ' $MINDATE IF $MAXDATE = $MINDATE SHOW 'MAXDATE = MINDATE' END-IF IF $MAXDATE <> $MINDATE SHOW 'MAXDATE <> MINDATE' END-IF IF $MAXDATE > $MINDATE SHOW 'MAXDATE > MINDATE' END-IF IF $MAXDATE < $MINDATE SHOW 'MAXDATE < MINDATE' END-IF END-PROCEDURE The log file output looks like this: MAX DATE = 01-SEP-1999 MIN DATE = 31-AUG-1999 MAXDATE = MINDATE I'm kind of stumped on this one. I think it has something to do with the internal "string" representation of the date variables, but all my juggling with the SQR.INI file settings didn't have any impact. I have also tried it with the "MOVE" command in place of the "LET" command above and, again, no difference. Oh, one more thing -- this happens consistently when one of the dates is an end-of-month date, and the other date is the following first-of-month date. OK Gurus, what am I missing? Thanks in advance, Kenny Melton Sr. Technical Architect Tandy Information Services PeopleSoft Financial Systems kmelto1@tandy.com From owner-sqr-users@list.iex.net Tue Oct 5 15:34:13 1999 Date: Tue, 5 Oct 1999 16:24:34 -0400 From: "Love, Kristin" Subject: Re: .SPF printing You wrote: From what I understand, the only way to display/print these "graphics" on the reports is to generate a .spf file. Not true. When you create a .lis file, the graphics look like a bunch of weird numbers. But when you send it to the printer (dos command copy filename.lis>lpt2 for example) the graphics come out. 1) Make sure you declare the printer 2) I found that if I tried to say "print" before I used a graphic command, for some reason my graphics wouldn't work (SQR 3.5) I only vaguely remember this, as it was last year..... :) Kristin From owner-sqr-users@list.iex.net Tue Oct 5 15:40:35 1999 Date: Tue, 5 Oct 1999 20:26:42 GMT From: eddy vasile Subject: Re: Date Variables Couple of things: 1) I assume the database is Oracle > 7.XX because of the date format (dd-mon-yyyy). Otherwise, you need to play with the date mask. 2) The procedure is local (otherwise global variables have to be $_maxdate, $mindate ------------- If these assumptions are correct, the program should work fine. Here's an adaptation that covers all possiblities and produces the correct output ASSUMING YOU ARE WORKING WITH ORACLE! (AND YOU RUN WITH / -XL) BEGIN-SETUP DECLARE-VARIABLE DATE $MINDATE DATE $MAXDATE END-DECLARE END-SETUP begin-program do compare-dates0 end-program BEGIN-PROCEDURE COMPARE-DATES0 LET $MAXDATE = '01-SEP-1999' LET $MINDATE = '31-AUG-1999' SHOW 'MAX DATE = ' $MAXDATE SHOW 'MIN DATE = ' $MINDATE IF $MAXDATE = $MINDATE SHOW 'MAXDATE = MINDATE' END-IF IF $MAXDATE <> $MINDATE SHOW 'MAXDATE <> MINDATE' END-IF IF $MAXDATE > $MINDATE SHOW 'MAXDATE > MINDATE' END-IF IF $MAXDATE < $MINDATE SHOW 'MAXDATE < MINDATE' END-IF do compare-dates1 END-PROCEDURE BEGIN-PROCEDURE COMPARE-DATES1 LET $_MAXDATE = '01-SEP-1999' LET $_MINDATE = '31-AUG-1999' SHOW 'MAX DATE = ' $_MAXDATE SHOW 'MIN DATE = ' $_MINDATE IF $_MAXDATE = $_MINDATE SHOW 'MAXDATE = MINDATE' END-IF IF $_MAXDATE <> $_MINDATE SHOW 'MAXDATE <> MINDATE' END-IF IF $_MAXDATE > $_MINDATE SHOW 'MAXDATE > MINDATE' END-IF IF $_MAXDATE < $_MINDATE SHOW 'MAXDATE < MINDATE' END-IF END-PROCEDURE >From: Kenny Melton >Reply-To: SQR-USERS@list.iex.net >To: Multiple recipients of list SQR-USERS >Subject: Date Variables >Date: Tue, 5 Oct 1999 14:59:25 -0500 > >Hello Everyone, > >I am using SQR 4.3.4 and am having a strange problem (or a severe brain >burp). Can anyone tell me why the following works the way it does?: > >BEGIN-SETUP > DECLARE-VARIABLE > DATE $MINDATE > DATE $MAXDATE > END-DECLARE >END-SETUP > >BEGIN-PROCEDURE COMPARE-DATES > LET $MAXDATE = '01-SEP-1999' > LET $MINDATE = '31-AUG-1999' > > SHOW 'MAX DATE = ' $MAXDATE > SHOW 'MIN DATE = ' $MINDATE > > IF $MAXDATE = $MINDATE > SHOW 'MAXDATE = MINDATE' > END-IF > > IF $MAXDATE <> $MINDATE > SHOW 'MAXDATE <> MINDATE' > END-IF > > IF $MAXDATE > $MINDATE > SHOW 'MAXDATE > MINDATE' > END-IF > > IF $MAXDATE < $MINDATE > SHOW 'MAXDATE < MINDATE' > END-IF > >END-PROCEDURE > >The log file output looks like this: > >MAX DATE = 01-SEP-1999 >MIN DATE = 31-AUG-1999 >MAXDATE = MINDATE > >I'm kind of stumped on this one. I think it has something to do with the >internal "string" representation of the date variables, but all my juggling >with the SQR.INI file settings didn't have any impact. I have also tried >it >with the "MOVE" command in place of the "LET" command above and, again, no >difference. Oh, one more thing -- this happens consistently when one of >the >dates is an end-of-month date, and the other date is the following >first-of-month date. OK Gurus, what am I missing? > >Thanks in advance, >Kenny Melton >Sr. Technical Architect >Tandy Information Services >PeopleSoft Financial Systems >kmelto1@tandy.com ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Tue Oct 5 15:45:31 1999 Date: Tue, 5 Oct 1999 16:34:27 EDT From: Jeff_Way OneWay_Consulting_Group Subject: Using SQR to INSERT a row with a LONG Column Hello all, The metrics: ========= SQR Version: 4.3.2 Oracle Version: 7.3.4 OS: Windows 95/98 (tried on two client machines) The problem: ========= I am attempting to INSERT into table 'A' from table 'A' while substituting KEY values based on user input. The SQL works in SQL-Plus, but causes an error when executed in SQR. Error received: (SQR 5528) ORACLE OEXEC error -1401 in cursor 8: ORA-01401: inserted value too large for column The code: ======= BEGIN-SQL INSERT INTO PS_GARN_SPEC ( EMPLID ,COMPANY ,GARNID {....fields deleted...} ,PRORATE_OVRD_AMT ,DEDCD ,GARN_REMARKS ) SELECT ( '[$VALUE1]' ,A.COMPANY ,A.GARNID {....fields deleted...} ,A.PRORATE_OVRD_AMT ,A.DEDCD ,A.GARN_REMARKS FROM PS_GARN_SPEC A WHERE {...blah-blah-blah...} END-SQL - - - - - - - - - - - - - - - - - - - - - - - - I am very confident that the error is with the GARN_REMARKS column, which in this case is defined in Oracle with a TYPE of [LONG]. As you can see, I am simply attempting to copy one row of data to the next. I have tried many permutations...all with various forms of error (or the same error). Some of the things I have tried: - Not specifying the LONG column in the VALUES clause and the SELECT clause - Using a NULL in the SELECT - Using '' - Using ' ' - Using an encoded value - Using a literal 'X' I have looked in various 'books' and 'manuals' at my disposal, and can't seem to find the answer. I have also searched other SQR libraries and cannot find an example of this anywhere. My guess is that this should be straightforward...but I fear that I am missing something (other than significant amounts of sleep and old friends). If anyone has ever INSERT'ed into a table from another table (or the same table), and the target table has a LONG column. Please let me know how you did it. Thanks in advance. ~Jeff Way OneWay Consulting Group, Inc. Phone: 978-764-2928 Email: jjway@aol.com "The reputation of a thousand years may be determined by the conduct of one hour." -Japanese Proverb From owner-sqr-users@list.iex.net Tue Oct 5 15:48:29 1999 Date: Tue, 5 Oct 1999 16:43:58 -0400 From: "Fay, Brannon" Subject: Re: Using SQR to INSERT a row with a LONG Column Have you tried replacing '[$Value1]' with just $Value1? -----Original Message----- From: Jeff_Way OneWay_Consulting_Group [mailto:JJWAY@AOL.COM] Sent: Tuesday, October 05, 1999 4:34 PM To: Multiple recipients of list SQR-USERS Subject: Using SQR to INSERT a row with a LONG Column Hello all, The metrics: ========= SQR Version: 4.3.2 Oracle Version: 7.3.4 OS: Windows 95/98 (tried on two client machines) The problem: ========= I am attempting to INSERT into table 'A' from table 'A' while substituting KEY values based on user input. The SQL works in SQL-Plus, but causes an error when executed in SQR. Error received: (SQR 5528) ORACLE OEXEC error -1401 in cursor 8: ORA-01401: inserted value too large for column The code: ======= BEGIN-SQL INSERT INTO PS_GARN_SPEC ( EMPLID ,COMPANY ,GARNID {....fields deleted...} ,PRORATE_OVRD_AMT ,DEDCD ,GARN_REMARKS ) SELECT ( '[$VALUE1]' ,A.COMPANY ,A.GARNID {....fields deleted...} ,A.PRORATE_OVRD_AMT ,A.DEDCD ,A.GARN_REMARKS FROM PS_GARN_SPEC A WHERE {...blah-blah-blah...} END-SQL - - - - - - - - - - - - - - - - - - - - - - - - I am very confident that the error is with the GARN_REMARKS column, which in this case is defined in Oracle with a TYPE of [LONG]. As you can see, I am simply attempting to copy one row of data to the next. I have tried many permutations...all with various forms of error (or the same error). Some of the things I have tried: - Not specifying the LONG column in the VALUES clause and the SELECT clause - Using a NULL in the SELECT - Using '' - Using ' ' - Using an encoded value - Using a literal 'X' I have looked in various 'books' and 'manuals' at my disposal, and can't seem to find the answer. I have also searched other SQR libraries and cannot find an example of this anywhere. My guess is that this should be straightforward...but I fear that I am missing something (other than significant amounts of sleep and old friends). If anyone has ever INSERT'ed into a table from another table (or the same table), and the target table has a LONG column. Please let me know how you did it. Thanks in advance. ~Jeff Way OneWay Consulting Group, Inc. Phone: 978-764-2928 Email: jjway@aol.com "The reputation of a thousand years may be determined by the conduct of one hour." -Japanese Proverb From owner-sqr-users@list.iex.net Tue Oct 5 16:00:38 1999 Date: Tue, 5 Oct 1999 16:45:13 -0400 From: Bob Buford-Abba Systems Subject: Re: Using SQR to INSERT a row with a LONG Column This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BF0F72.861625B0 Content-Type: text/plain; charset="iso-8859-1" Hi Jeff, I found, using SQR v.3 and I forget what flavor Oracle (PS 5, 6, 7 depending on which module was in use) that we had to use DataMover to handle Long columns. Bob -----Original Message----- From: Jeff_Way OneWay_Consulting_Group [mailto:JJWAY@AOL.COM] Sent: Tuesday, October 05, 1999 4:34 PM To: Multiple recipients of list SQR-USERS Subject: Using SQR to INSERT a row with a LONG Column Hello all, The metrics: ========= SQR Version: 4.3.2 Oracle Version: 7.3.4 OS: Windows 95/98 (tried on two client machines) The problem: ========= I am attempting to INSERT into table 'A' from table 'A' while substituting KEY values based on user input. The SQL works in SQL-Plus, but causes an error when executed in SQR. Error received: (SQR 5528) ORACLE OEXEC error -1401 in cursor 8: ORA-01401: inserted value too large for column The code: ======= BEGIN-SQL INSERT INTO PS_GARN_SPEC ( EMPLID ,COMPANY ,GARNID {....fields deleted...} ,PRORATE_OVRD_AMT ,DEDCD ,GARN_REMARKS ) SELECT ( '[$VALUE1]' ,A.COMPANY ,A.GARNID {....fields deleted...} ,A.PRORATE_OVRD_AMT ,A.DEDCD ,A.GARN_REMARKS FROM PS_GARN_SPEC A WHERE {...blah-blah-blah...} END-SQL - - - - - - - - - - - - - - - - - - - - - - - - I am very confident that the error is with the GARN_REMARKS column, which in this case is defined in Oracle with a TYPE of [LONG]. As you can see, I am simply attempting to copy one row of data to the next. I have tried many permutations...all with various forms of error (or the same error). Some of the things I have tried: - Not specifying the LONG column in the VALUES clause and the SELECT clause - Using a NULL in the SELECT - Using '' - Using ' ' - Using an encoded value - Using a literal 'X' I have looked in various 'books' and 'manuals' at my disposal, and can't seem to find the answer. I have also searched other SQR libraries and cannot find an example of this anywhere. My guess is that this should be straightforward...but I fear that I am missing something (other than significant amounts of sleep and old friends). If anyone has ever INSERT'ed into a table from another table (or the same table), and the target table has a LONG column. Please let me know how you did it. Thanks in advance. ~Jeff Way OneWay Consulting Group, Inc. Phone: 978-764-2928 Email: jjway@aol.com "The reputation of a thousand years may be determined by the conduct of one hour." -Japanese Proverb ------_=_NextPart_001_01BF0F72.861625B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Using SQR to INSERT a row with a LONG Column

Hi Jeff,

I found, using SQR v.3 and I forget what flavor = Oracle (PS 5, 6, 7 depending on which module was in use) that we had to = use DataMover to h