From owner-sqr-users@list.iex.net Thu Apr 1 00:19:01 1999 Date: Thu, 1 Apr 1999 00:00:01 -0500 From: Nathan Treadway Subject: Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Treadway, Ray Ontko & Co. (nathant@ontko.com) $Revision: 1.3 $ $Date: 1999-03-09 23:51:52-05 $ This document gathers in one location information about the sqr-users mail list. Directions for common list-server commands are given, as are guidelines for list usage. ______________________________________________________________________ Table of Contents 1. Change 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 Posting a Message 4.2 Replying to a Message 5. Job Announcements ______________________________________________________________________ 1. Change Since Previous Version Changes since the version dated 1998/10/10: o Changed listserver machine name from "usa.net" to "list.iex.net". o Added info on "psusers" mailing list. 2. Introduction 2.1. sqr-users Mailing List The sqr-users mailing list was created to enable discussion related to the SQR database language (and to other products from SQRIBE Technologies). 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 Nathan Treadway. 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". 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 and list server will continue to work for some period in the future, but you should start using the new addresses as soon as possible. The old address sqr-users-request@usa.net no longer works. 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. 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 SQRIBE 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 code, since the posting will be placed in a public web archive.) Note that your question should relate to a SQRIBE 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.2. 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 orginal 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. 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 Thu Apr 1 02:36:23 1999 Date: Thu, 1 Apr 1999 17:21:50 +1000 From: Paul Schattling Subject: Connecting to remote d/base through SQR Okay, I'm sorry if this appears very basic so please be understanding. We have peoplesoft Assets but not their full Financials (don't ask!). Through an SQR (launched via peoplesoft) we are able to select data from the remote Finance database and insert into the P/soft Asset database. We now need to insert data back to our finance system. I can't write to a remote d/base so I want to logon to the finance database and do my select and insert from there. If anyone can make sense of my dribble, can you advise of the commands/procedures to use in SQR to obtain this. Thanks in Advance Paul Schattling From owner-sqr-users@list.iex.net Thu Apr 1 07:27:01 1999 Date: Thu, 1 Apr 1999 07:12:26 -0500 From: Bob Buford Subject: Re: Connecting to remote d/base through SQR Hi Paul, Go to URL http://www.sqrug.com/sqr-users/ and search archive volumes for keyword connect. Several popular choices seem to be using ODBC or having your dba set up a listener to one of the databases. Perhaps ODBC might be better so you can write in same process instance to several different databases. Anyway, once you are setup and connect like that, just use your BEGIN-SELECT and BEGIN-SQL paragraphs to manipulate data as you would directly in SQL. Someone else had suggested an approach of having two different .INI files with shortcuts to your desktop, which would enable you to SELECT your data in one session, perhaps writing it to a file, then launching an SQR session into the other database and using the data in the file to insert into that database. HTH, Bob >>> Paul Schattling 04/01/99 02:21AM >>> Okay, I'm sorry if this appears very basic so please be understanding. We have peoplesoft Assets but not their full Financials (don't ask!). Through an SQR (launched via peoplesoft) we are able to select data from the remote Finance database and insert into the P/soft Asset database. We now need to insert data back to our finance system. I can't write to a remote d/base so I want to logon to the finance database and do my select and insert from there. If anyone can make sense of my dribble, can you advise of the commands/procedures to use in SQR to obtain this. Thanks in Advance Paul Schattling From owner-sqr-users@list.iex.net Thu Apr 1 16:09:14 1999 Date: Thu, 1 Apr 1999 12:07:48 -0800 From: Bryan Richey Subject: Syntax for Count(*) Hello all! I am trying to populate the dynamic headings that follow ....I was wondering if someone has any insight to the correct syntax for the count(*)...I am currently getting a mess of SQL errors... Thanks, Bryan !****************************************************** begin-procedure Determine-Sequence !****************************************************** create-array name=ERNmtx size=11 field=ERNttl:char do erncd-detail end-procedure !******************************************************begin-procedure erncd-detail !****************************************************** let #ERNctr = 0 begin-select b.erncd count(*) &b.count let $E.ERNCD = &b.erncd let #ERNctr = #ERNctr + 1 let ERNmtx.ERNttl(#ERNctr) = &b.count if #ERNctr >= 10 exit-select end-if from ps_dhm_emp_detail b where b.DEPTID = $V_DEPTID and b.paygroup = $V_PayGroup and b.pay_end_dt = $V_Pay_End_dt and b.DHM_WEEK_NBR = #V_Week_Nbr order by 2 desc, 1 !group by $E.ERNCD !group by b.erncd end-select let $H01 = ERNmtx.ERNttl(1) let $H02 = ERNmtx.ERNttl(2) let $H03 = ERNmtx.ERNttl(3) let $H04 = ERNmtx.ERNttl(4) let $H05 = ERNmtx.ERNttl(5) let $H06 = ERNmtx.ERNttl(6) let $H07 = ERNmtx.ERNttl(7) let $H08 = ERNmtx.ERNttl(8) let $H09 = ERNmtx.ERNttl(9) let $H10 = ERNmtx.ERNttl(10) display $h01 end-procedure _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Thu Apr 1 16:35:46 1999 Date: Thu, 1 Apr 1999 15:31:09 -0500 From: Jim Hardesty Subject: Syntax for Count(*) -Reply count(*) &b.count should be count(*) &count? From owner-sqr-users@list.iex.net Wed Apr 7 03:34:09 1999 Date: Thu, 1 Apr 1999 13:15:53 PST From: Parijat Sahai Subject: Re: Syntax for Count(*) Remove the dot from &b.count, i.e., you should have the following syntax: begin-select b.erncd count(b.*) &b_count let $E_ERNCD = &b.erncd ... let ERNmtx.ERNttl(#ERNctr) = &b_count ..... end-select The ampersand variable should be one word without any dots. I guess same holds for $ and # variables in SQR. You can have "-" and "_" as separators to make variable names more readable if you like. Parijat. >From: Bryan Richey >Reply-To: SQR-USERS@list.iex.net >To: Multiple recipients of list SQR-USERS >Subject: Syntax for Count(*) >Date: Thu, 1 Apr 1999 12:07:48 -0800 > >Hello all! > >I am trying to populate the dynamic headings that follow ....I was >wondering if someone has any insight to the correct syntax for the >count(*)...I am currently getting a mess of SQL errors... > >Thanks, > >Bryan > > >!****************************************************** >begin-procedure Determine-Sequence >!****************************************************** >create-array name=ERNmtx size=11 field=ERNttl:char > >do erncd-detail > >end-procedure > >!******************************************************begin-procedure >erncd-detail >!****************************************************** >let #ERNctr = 0 > >begin-select > >b.erncd >count(*) &b.count > > let $E.ERNCD = &b.erncd > let #ERNctr = #ERNctr + 1 > let ERNmtx.ERNttl(#ERNctr) = &b.count > if #ERNctr >= 10 > exit-select > end-if > > from ps_dhm_emp_detail b >where b.DEPTID = $V_DEPTID > and b.paygroup = $V_PayGroup > and b.pay_end_dt = $V_Pay_End_dt > and b.DHM_WEEK_NBR = #V_Week_Nbr >order by 2 desc, 1 >!group by $E.ERNCD >!group by b.erncd > >end-select > >let $H01 = ERNmtx.ERNttl(1) >let $H02 = ERNmtx.ERNttl(2) >let $H03 = ERNmtx.ERNttl(3) >let $H04 = ERNmtx.ERNttl(4) >let $H05 = ERNmtx.ERNttl(5) >let $H06 = ERNmtx.ERNttl(6) >let $H07 = ERNmtx.ERNttl(7) >let $H08 = ERNmtx.ERNttl(8) >let $H09 = ERNmtx.ERNttl(9) >let $H10 = ERNmtx.ERNttl(10) > >display $h01 > >end-procedure > > >_________________________________________________________ >Do You Yahoo!? >Get your free @yahoo.com address at http://mail.yahoo.com Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Thu Apr 1 16:48:17 1999 Date: Thu, 1 Apr 1999 16:28:09 -0500 From: Sam Spritzer Subject: Re: Syntax for Count(*) you need to replace the * with something at least for starts. Sam <<< Bryan Richey 4/ 1 3:07p >>> Hello all! I am trying to populate the dynamic headings that follow ....I was wondering if someone has any insight to the correct syntax for the count(*)...I am currently getting a mess of SQL errors... Thanks, Bryan !****************************************************** begin-procedure Determine-Sequence !****************************************************** create-array name=ERNmtx size=11 field=ERNttl:char do erncd-detail end-procedure !******************************************************begin-procedure erncd-detail !****************************************************** let #ERNctr = 0 begin-select b.erncd count(*) &b.count let $E.ERNCD = &b.erncd let #ERNctr = #ERNctr 1 let ERNmtx.ERNttl(#ERNctr) = &b.count if #ERNctr >= 10 exit-select end-if from ps_dhm_emp_detail b where b.DEPTID = $V_DEPTID and b.paygroup = $V_PayGroup and b.pay_end_dt = $V_Pay_End_dt and b.DHM_WEEK_NBR = #V_Week_Nbr order by 2 desc, 1 !group by $E.ERNCD !group by b.erncd end-select let $H01 = ERNmtx.ERNttl(1) let $H02 = ERNmtx.ERNttl(2) let $H03 = ERNmtx.ERNttl(3) let $H04 = ERNmtx.ERNttl(4) let $H05 = ERNmtx.ERNttl(5) let $H06 = ERNmtx.ERNttl(6) let $H07 = ERNmtx.ERNttl(7) let $H08 = ERNmtx.ERNttl(8) let $H09 = ERNmtx.ERNttl(9) let $H10 = ERNmtx.ERNttl(10) display $h01 end-procedure _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Thu Apr 1 18:15:53 1999 Date: Thu, 1 Apr 1999 17:52:11 -0400 From: Navneet Chandra Subject: (Out of Topic) Help needed in my research I'm sorry to bring in a non technical subject into this list but i need some help in my research on the Peoplesoft ERP market - it's current trend and what is to be expected in the near future. I would like to especially hear from people who are involved in upgrades or have plans to start an upgrade soon. The two main questions around which my research is based on is WHY - An upgrade?? and WHAT NEXT ?? All help in this regard would be appreciated. Thanx in advance Navneet From owner-sqr-users@list.iex.net Thu Apr 1 17:49:30 1999 Date: Thu, 1 Apr 1999 17:38:55 -0500 From: Tony DeLia Subject: Re: Syntax for Count(*) The syntax: count(*) &b.count is correct... I use this frequently... You should have: group by b.erncd order by 2 desc, 1 The GROUP BY comes before the ORDER BY... I don't know why you're moving the COUNT into the Heading... You should be sorting by the count(*) for each erncd then move the ERNCD to the ERNmtx.ERNttl (#ERNctr) array element... the COUNT itself is irrelevant for headings... All you need is... let #ERNctr = #ERNctr + 1 let ERNmtx.ERNttl(#ERNctr) = &b.erncd if #ERNctr >= 10 exit-select end-if ...within the select loop... -Tony DeLia PS - Another e-mail address???? Sam Spritzer wrote: > > you need to replace the * with something at least for starts. > > Sam > > <<< Bryan Richey 4/ 1 3:07p >>> > Hello all! > > I am trying to populate the dynamic headings that follow ....I was > wondering if someone has any insight to the correct syntax for the > count(*)...I am currently getting a mess of SQL errors... > > Thanks, > > Bryan > > !****************************************************** > begin-procedure Determine-Sequence > !****************************************************** > create-array name=ERNmtx size=11 field=ERNttl:char > > do erncd-detail > > end-procedure > > !******************************************************begin-procedure > erncd-detail > !****************************************************** > let #ERNctr = 0 > > begin-select > > b.erncd > count(*) &b.count > > let $E.ERNCD = &b.erncd > let #ERNctr = #ERNctr 1 > let ERNmtx.ERNttl(#ERNctr) = &b.count > if #ERNctr >= 10 > exit-select > end-if > > from ps_dhm_emp_detail b > where b.DEPTID = $V_DEPTID > and b.paygroup = $V_PayGroup > and b.pay_end_dt = $V_Pay_End_dt > and b.DHM_WEEK_NBR = #V_Week_Nbr > order by 2 desc, 1 > !group by $E.ERNCD > !group by b.erncd > > end-select > > let $H01 = ERNmtx.ERNttl(1) > let $H02 = ERNmtx.ERNttl(2) > let $H03 = ERNmtx.ERNttl(3) > let $H04 = ERNmtx.ERNttl(4) > let $H05 = ERNmtx.ERNttl(5) > let $H06 = ERNmtx.ERNttl(6) > let $H07 = ERNmtx.ERNttl(7) > let $H08 = ERNmtx.ERNttl(8) > let $H09 = ERNmtx.ERNttl(9) > let $H10 = ERNmtx.ERNttl(10) > > display $h01 > > end-procedure > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com From owner-sqr-users@list.iex.net Thu Apr 1 20:14:37 1999 Date: Thu, 1 Apr 1999 16:58:36 -0800 From: "Kibler, Daniel R - CFC" Subject: Re: Syntax for Count(*) Is the layout correct? You can't have non-SQL lines (eg, let ...) start in the 1st column. You also need to group on erncd. Daniel Kibler Cambridge Technology Partners 925 543-4612 503 471-5111 Client site -----Original Message----- From: Bryan Richey [mailto:b_richey@YAHOO.COM] Sent: Thursday, April 01, 1999 12:08 PM To: Multiple recipients of list SQR-USERS Subject: Syntax for Count(*) Hello all! I am trying to populate the dynamic headings that follow ....I was wondering if someone has any insight to the correct syntax for the count(*)...I am currently getting a mess of SQL errors... Thanks, Bryan !****************************************************** begin-procedure Determine-Sequence !****************************************************** create-array name=ERNmtx size=11 field=ERNttl:char do erncd-detail end-procedure !******************************************************begin-procedure erncd-detail !****************************************************** let #ERNctr = 0 begin-select b.erncd count(*) &b.count let $E.ERNCD = &b.erncd let #ERNctr = #ERNctr + 1 let ERNmtx.ERNttl(#ERNctr) = &b.count if #ERNctr >= 10 exit-select end-if from ps_dhm_emp_detail b where b.DEPTID = $V_DEPTID and b.paygroup = $V_PayGroup and b.pay_end_dt = $V_Pay_End_dt and b.DHM_WEEK_NBR = #V_Week_Nbr order by 2 desc, 1 !group by $E.ERNCD !group by b.erncd end-select let $H01 = ERNmtx.ERNttl(1) let $H02 = ERNmtx.ERNttl(2) let $H03 = ERNmtx.ERNttl(3) let $H04 = ERNmtx.ERNttl(4) let $H05 = ERNmtx.ERNttl(5) let $H06 = ERNmtx.ERNttl(6) let $H07 = ERNmtx.ERNttl(7) let $H08 = ERNmtx.ERNttl(8) let $H09 = ERNmtx.ERNttl(9) let $H10 = ERNmtx.ERNttl(10) display $h01 end-procedure _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Thu Apr 1 22:44:51 1999 Date: Thu, 1 Apr 1999 22:32:49 EST From: Terri Jenkins Subject: Re: (Out of Topic) Help needed in my research Regarding the Why....PeopleSoft only supports (that is, tries to help you when you call) a release for a limited time. So, usually you want to upgrade to the next supported release. PeopleSoft supports a release for 18 months after the next major release is out. So, they support release 7.whatever for 18 months after 8.whatever is out. It kind of seems like they have changed that spread recently, but that has been their trend. As for What next...go to the Customer Connection and get as much info as you can - like the upgrade kit. Keep searching the web site until you have some guidelines on how to get started. Helping you plan is also a key task for your PeopleSoft site rep. Call your rep. Then get approval for 3-4 dedicated FTE's, and borrow a couple more as you need them. There are also consulting companies that specialize in upgrades, but I have had minimal experience with them so I can't steer you in that direction. It is scarey at first, but invest a good amount of time in planning and you will get through it. Like, get any major system changes in before you start so you can freeze your instance during the upgrade. Hang in there... From owner-sqr-users@list.iex.net Fri Apr 2 07:15:18 1999 Date: Fri, 2 Apr 1999 07:02:17 -0500 From: Sam Spritzer Subject: Re: (Out of Topic) Help needed in my research I think one place you will find very useful is www.erpcentral.com >From my own experience, upgrades are mostly for two reasons. The first is functionality. Each upgrade not only represents a functional improvement to the product as a whole but more to the customer. They see it as an opportunity to improve their own business processes. The second is because "they have to". Y2K is an example; technology is another. And while technology may also be classified as a functionality because it allows the customer to improve or create a business process such as e-commerce. From a technical aspect, perhaps it fits in with a hardware or infra-structure upgrade, at the same time. Hope this helps and good luck, Sam <<< Navneet Chandra 4/ 1 4:52p >>> I'm sorry to bring in a non technical subject into this list but i need some help in my research on the Peoplesoft ERP market - it's current trend and what is to be expected in the near future. I would like to especially hear from people who are involved in upgrades or have plans to start an upgrade soon. The two main questions around which my research is based on is WHY - An upgrade?? and WHAT NEXT ?? All help in this regard would be appreciated. Thanx in advance Navneet From owner-sqr-users@list.iex.net Fri Apr 2 07:43:28 1999 Date: Fri, 2 Apr 1999 07:33:17 -0500 From: Tony DeLia Subject: Re: Syntax for Count(*) Clarification: You can't have non-SQL lines (eg, let ...) start in the 1st 'position' WITHIN the begin-select and end-select... The first position is for select columns... and subsequent SQL (eg, from, where, ...) ... ! Below is outside of select (Position 1) - OKAY let #ERNctr = 0 begin-select b.erncd count(*) &b.count ! Below is indented (Not in Position 1) - OKAY let #ERNctr = #ERNctr + 1 let ERNmtx.ERNttl(#ERNctr) = &b.erncd if #ERNctr >= 10 exit-select end-if from s_dhm_emp_detail b where ... group by b.erncd order by 2 desc, 1 end-select ... Tony DeLia Kibler, Daniel R - CFC wrote: > > Is the layout correct? You can't have non-SQL lines (eg, let ...) start in > the 1st column. > > You also need to group on erncd. > Daniel Kibler -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com From owner-sqr-users@list.iex.net Fri Apr 2 08:00:07 1999 Date: Fri, 2 Apr 1999 07:49:30 -0500 From: Sam Spritzer Subject: Re: Syntax for Count(*) In almost all cases, the first line is usually "begin-Procedure" so it seems that this rule is redundant anyway. Sam <<< Tony DeLia 4/ 2 7:33a >>> Clarification: You can't have non-SQL lines (eg, let ...) start in the 1st 'position' WITHIN the begin-select and end-select... The first position is for select columns... and subsequent SQL (eg, from, where, ...) ... ! Below is outside of select (Position 1) - OKAY let #ERNctr = 0 begin-select b.erncd count(*) &b.count ! Below is indented (Not in Position 1) - OKAY let #ERNctr = #ERNctr 1 let ERNmtx.ERNttl(#ERNctr) = &b.erncd if #ERNctr >= 10 exit-select end-if from s_dhm_emp_detail b where ... group by b.erncd order by 2 desc, 1 end-select ... Tony DeLia Kibler, Daniel R - CFC wrote: > > Is the layout correct? You can't have non-SQL lines (eg, let ...) start in > the 1st column. > > You also need to group on erncd. > Daniel Kibler -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com From owner-sqr-users@list.iex.net Fri Apr 2 08:51:14 1999 Date: Fri, 2 Apr 1999 07:39:52 -0600 From: Carlton Conley Subject: Re: Syntax for Count(*) No as Tony stated it the restriction is in between the begin and end selects or other SQL statements (i.e. update, delete) not the begin-report, program or procedure. Though for clarity and formating I indent all my non-sql lines within those headings. -----Original Message----- From: Discussion of SQR, SQRIBE Technologies's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Sam Spritzer Sent: Friday, April 02, 1999 6:50 AM To: Multiple recipients of list SQR-USERS Subject: Re: Syntax for Count(*) In almost all cases, the first line is usually "begin-Procedure" so it seems that this rule is redundant anyway. Sam <<< Tony DeLia 4/ 2 7:33a >>> Clarification: You can't have non-SQL lines (eg, let ...) start in the 1st 'position' WITHIN the begin-select and end-select... The first position is for select columns... and subsequent SQL (eg, from, where, ...) ... ! Below is outside of select (Position 1) - OKAY let #ERNctr = 0 begin-select b.erncd count(*) &b.count ! Below is indented (Not in Position 1) - OKAY let #ERNctr = #ERNctr 1 let ERNmtx.ERNttl(#ERNctr) = &b.erncd if #ERNctr >= 10 exit-select end-if from s_dhm_emp_detail b where ... group by b.erncd order by 2 desc, 1 end-select ... Tony DeLia Kibler, Daniel R - CFC wrote: > > Is the layout correct? You can't have non-SQL lines (eg, let ...) start in > the 1st column. > > You also need to group on erncd. > Daniel Kibler -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com From owner-sqr-users@list.iex.net Fri Apr 2 09:10:43 1999 Date: Fri, 2 Apr 1999 09:00:52 -0500 From: Sam Spritzer Subject: Re: Syntax for Count(*) I gotcha....usually I put my "lets" right after the begin-procedure otherwise I do my selects. Sometimes I put my "lets" after the field...others, I group them after all the fields. Sam <<< Carlton Conley 4/ 2 8:39a >>> No as Tony stated it the restriction is in between the begin and end selects or other SQL statements (i.e. update, delete) not the begin-report, program or procedure. Though for clarity and formating I indent all my non-sql lines within those headings. -----Original Message----- From: Discussion of SQR, SQRIBE Technologies's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Sam Spritzer Sent: Friday, April 02, 1999 6:50 AM To: Multiple recipients of list SQR-USERS Subject: Re: Syntax for Count(*) In almost all cases, the first line is usually "begin-Procedure" so it seems that this rule is redundant anyway. Sam <<< Tony DeLia 4/ 2 7:33a >>> Clarification: You can't have non-SQL lines (eg, let ...) start in the 1st 'position' WITHIN the begin-select and end-select... The first position is for select columns... and subsequent SQL (eg, from, where, ...) ... ! Below is outside of select (Position 1) - OKAY let #ERNctr = 0 begin-select b.erncd count(*) &b.count ! Below is indented (Not in Position 1) - OKAY let #ERNctr = #ERNctr 1 let ERNmtx.ERNttl(#ERNctr) = &b.erncd if #ERNctr >= 10 exit-select end-if from s_dhm_emp_detail b where ... group by b.erncd order by 2 desc, 1 end-select ... Tony DeLia Kibler, Daniel R - CFC wrote: > > Is the layout correct? You can't have non-SQL lines (eg, let ...) start in > the 1st column. > > You also need to group on erncd. > Daniel Kibler -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com From owner-sqr-users@list.iex.net Fri Apr 2 10:53:53 1999 Date: Fri, 2 Apr 1999 07:24:52 -0800 From: "Kibler, Daniel R - CFC" Subject: Re: Syntax for Count(*) You are right Tony. I fired that off hastily and wasn't as clear as I should have been. Daniel Kibler Cambridge Technology Partners 925 543-4612 503 471-5111 Client site -----Original Message----- From: Tony DeLia [mailto:tdelia@EROLS.COM] Sent: Friday, April 02, 1999 4:33 AM To: Multiple recipients of list SQR-USERS Subject: Re: Syntax for Count(*) Clarification: You can't have non-SQL lines (eg, let ...) start in the 1st 'position' WITHIN the begin-select and end-select... The first position is for select columns... and subsequent SQL (eg, from, where, ...) ... ! Below is outside of select (Position 1) - OKAY let #ERNctr = 0 begin-select b.erncd count(*) &b.count ! Below is indented (Not in Position 1) - OKAY let #ERNctr = #ERNctr + 1 let ERNmtx.ERNttl(#ERNctr) = &b.erncd if #ERNctr >= 10 exit-select end-if from s_dhm_emp_detail b where ... group by b.erncd order by 2 desc, 1 end-select ... Tony DeLia Kibler, Daniel R - CFC wrote: > > Is the layout correct? You can't have non-SQL lines (eg, let ...) start in > the 1st column. > > You also need to group on erncd. > Daniel Kibler -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com From owner-sqr-users@list.iex.net Fri Apr 2 13:48:27 1999 Date: Fri, 2 Apr 1999 10:36:16 -0600 From: Lily Van Subject: logo Hi all, If there's already a resolution to this, please let me know and I do apologize for revisiting the topic. The problem I'm encountering is when I print Purchase Order(SQR) w/ the company's logo. If I route it to a .spf file and then print it then the logo is fine. If I route it directly from PS's process to a printer then the logo only appears as a box w/ small fined dots. I've tried .bmp file as well as .gif file and the results are the same. This problem occurs w/ PS's delivered program and PS's logo as well. I've placed a call into PS but I was hoping this would provide faster response. Any help is truly appreciated. Lily From owner-sqr-users@list.iex.net Fri Apr 2 12:16:28 1999 Date: Fri, 2 Apr 1999 12:05:27 -0500 From: Joe Wittrock Subject: Communication Box with PeopleSoft We are installing a new PeopleSoft HRMS. Our problem is that every time we run an SQR from the PeopleSoft menu a communication box opens up and asks us to hit enter and then put in our parameters. I know that -XCB suppresses the window, and have checked our process type panel to make sure that this is in there for SQR flags. I?ve even put the -XCB in the Configuration Manager for SQR flags, but it still won?t suppress the box. We just did a SQR upgrade from 3.x to 4.x, but I?m not sure if this was happening prior to that. Any help would be appreciated. Joe Wittrock joe.wittrock@smed.com From owner-sqr-users@list.iex.net Fri Apr 2 12:41:34 1999 Date: Fri, 2 Apr 1999 09:29:16 PST From: Parijat Sahai Subject: Re: Communication Box with PeopleSoft Check your Process Type for "SQR Process" and "SQR Report" in Process Scheduler, and also the process definitions in process scheduler. Chances are that you're not providing the input parameters that SQR is asking for, more specifically the process instance, oprid and run cntl id. You can verify that more easily by looking at run control parameter tab when you double-click the process in process monitor window. -XCB will not help in this case if SQR requires an "INPUT" parameter and it hasn't been provided at the command prompt. > > We are installing a new PeopleSoft HRMS. Our problem is that >every time we run an SQR from the PeopleSoft menu a communication box opens >up and asks us to hit enter and then put in our parameters. I know that >-XCB suppresses the window, and have checked our process type panel to make >sure that this is in there for SQR flags. I?ve even put the -XCB in the >Configuration Manager for SQR flags, but it still won?t suppress the box. >We just did a SQR upgrade from 3.x to 4.x, but I?m not sure if this was >happening prior to that. Any help would be appreciated. > >Joe Wittrock >joe.wittrock@smed.com Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Fri Apr 2 13:44:36 1999 Date: Fri, 2 Apr 1999 12:30:46 -0600 From: "Korbel, Gordon E." Subject: Unusual Error -- memory fault Sybase 11.x Peoplesoft 7 HP-UX SQR:V3.0.12.3 We are creating some customized Invoice forms in SQR, and we are encountering an unusual error -- the message is PeopleSoft prcs_sqr Started Process instance is 6520 SQR Output file is /opt/psoftfs/ps_output/biivcpn_6520.lis SQR: Structured Query Report Writer V3.0.12.3 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. prcs_sqr[292]: 6536 Memory fault prcs_sqr: ------------------- Error ------------------- prcs_sqr: Command failure prcs_sqr: SQR Command Terminated abnormally prcs_sqr: Return code : 139 prcs_sqr: ------------------- Error ------------------- This program has a large number of #include files, and in general is fairly lengthy, but is otherwise 'correct' -- I can say this with confidence because when run from the Windows environment the same program executes correctly. It seems as though we are passing some critical threshold when ANY code is added to the SQR. The error occurs prior to any processing occuring -- it seems as though it blows when SQR parses and 'compiles' the source code. Any Ideas? Thanks, -Gordon From owner-sqr-users@list.iex.net Fri Apr 2 14:12:07 1999 Date: Fri, 2 Apr 1999 10:55:29 PST From: Pramod Bhavanam Subject: How to set cursor position to a particular field in a panel in Peoplesoft ? Hi all This is not a SQR question but hoping that there are PS users as well in this list I am posting this. In Peoplesoft 7.5 using Peoplecode Is there a way to set the cursor position to a particular editable field on a panel of a panel group irrespective of the panel layout order of fields ? I know that if we put that editable field first in the layout order we will always have the cursor in that field if we are in that panel anytime. But without changing the tab sequence order I want the cursor to be placed on a particular field which is other than the first editable field. How can we do it using Peoplecode ? I would appreciate if anyone could reply with a solution / possible solution if any. Thanks. Pramod. Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Fri Apr 2 14:34:41 1999 Date: Fri, 2 Apr 1999 14:12:33 -0500 From: Bob Buford Subject: Re: How to set cursor position to a particular field in a panel inPeoplesoft ? Hi Pramod, SetCursorPos (Panel.panelname, ... [recordname.] fieldname, target_row). It's specific to the panelname you use in the command. >>> Pramod Bhavanam 04/02/99 01:55PM >>> Hi all This is not a SQR question but hoping that there are PS users as well in this list I am posting this. In Peoplesoft 7.5 using Peoplecode Is there a way to set the cursor position to a particular editable field on a panel of a panel group irrespective of the panel layout order of fields ? I know that if we put that editable field first in the layout order we will always have the cursor in that field if we are in that panel anytime. But without changing the tab sequence order I want the cursor to be placed on a particular field which is other than the first editable field. How can we do it using Peoplecode ? I would appreciate if anyone could reply with a solution / possible solution if any. Thanks. Pramod. Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Fri Apr 2 15:15:57 1999 Date: Fri, 2 Apr 1999 11:14:51 PST From: Parijat Sahai Subject: Re: How to set cursor position to a particular field in a panel inPeoplesoft ? Hi Pramod, You should subsribe to psusers@egroups.com for Peoplesoft related queries, or go to http://www.egroups.com/list/psusers for more info. As for your question, use Peoplecode function "SetCursorPos()" to set the cursor to a particular editable field. You can find the exact syntax in Peoplebooks (go to Chapter on Peoplecode - Peoplecode Reference - Function Catagories). Parijat. > >Hi all > >This is not a SQR question but hoping that there are PS users as well in >this list I am posting this. > >In Peoplesoft 7.5 using Peoplecode Is there a way to set the cursor >position to a particular editable field on a panel of a panel group >irrespective of the panel layout order of fields ? > >I know that if we put that editable field first in the layout order we >will always have the cursor in that field if we are in that panel >anytime. > >But without changing the tab sequence order I want the cursor to be >placed on a particular field which is other than the first editable >field. > >How can we do it using Peoplecode ? > >I would appreciate if anyone could reply with a solution / possible >solution if any. > >Thanks. > >Pramod. > >Get Your Private, Free Email at http://www.hotmail.com Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Fri Apr 2 14:43:08 1999 Date: Fri, 2 Apr 1999 11:29:59 -0800 From: Greg Gargett Subject: Re: How to set cursor position to a particular field in a panel inPeoplesoft ? Pramod Bhavanam wrote: > Hi all > > This is not a SQR question but hoping that there are PS users as well in > this list I am posting this. > > In Peoplesoft 7.5 using Peoplecode Is there a way to set the cursor > position to a particular editable field on a panel of a panel group > irrespective of the panel layout order of fields ? > check out the setcursor peoplecode function. -- Greg "I never have more than one drink before dinner. But I do like that one to be large and very strong and very cold and very well-made." - Ian Fleming From owner-sqr-users@list.iex.net Fri Apr 2 15:46:54 1999 Date: Fri, 2 Apr 1999 11:35:52 PST From: Pramod Bhavanam Subject: Re: How to set cursor position to a particular field in a panelinPeoplesoft ? Hi I used SetCursorPos in RowInit but what happens is when I open that particular panel in that panelgroup directly it will set the cursor position to that field but once I go to another panel and come back the cursor is at the first field in the layout order. If there was a command like PanelChanged I would have used it and checked if the panel has changed and again reset the cursor position back to the required field. But we don't have any command like PanelChanged in Peoplecode. If there was a command like SetNextField I would have used it to achieve the task and placed the cursor at what ever fields the user is supposed to enter and skip all other fields which need no updation. But we don't have any command like SetNextField in Peoplecode. Is there any other way to achieve the above task. Thanks. Pramod. >From: Bob Buford >Reply-To: SQR-USERS@list.iex.net >To: Multiple recipients of list SQR-USERS >Subject: Re: How to set cursor position to a particular field in a panelinPeoplesoft ? >Date: Fri, 2 Apr 1999 14:12:33 -0500 > >Hi Pramod, > >SetCursorPos (Panel.panelname, ... [recordname.] fieldname, target_row). > >It's specific to the panelname you use in the command. > >>>> Pramod Bhavanam 04/02/99 01:55PM >>> >Hi all > >This is not a SQR question but hoping that there are PS users as well in >this list I am posting this. > >In Peoplesoft 7.5 using Peoplecode Is there a way to set the cursor >position to a particular editable field on a panel of a panel group >irrespective of the panel layout order of fields ? > >I know that if we put that editable field first in the layout order we >will always have the cursor in that field if we are in that panel >anytime. > >But without changing the tab sequence order I want the cursor to be >placed on a particular field which is other than the first editable >field. > >How can we do it using Peoplecode ? > >I would appreciate if anyone could reply with a solution / possible >solution if any. > >Thanks. > >Pramod. > >Get Your Private, Free Email at http://www.hotmail.com Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Fri Apr 2 16:13:37 1999 Date: Fri, 2 Apr 1999 13:45:32 -0600 From: "Korbel, Gordon E." Subject: Re: Unusual Error -- memory fault I tried the ulimit -- it returned "unlimited". Running the program as root exhibited the same symptom. Thanks for the suggestions -- any other ideas? -Gordon -----Original Message----- From: Franck Masson [mailto:franck.masson@SQRIBE.COM] Sent: Friday, April 02, 1999 1:55 PM To: Multiple recipients of list SQR-USERS Subject: Re: Unusual Error -- memory fault hi, this will check if it is an OS limitation. execute ulimit command from the unix prompt to determine if any memory limitation is set. try to execute your sqr program as root. If it run correctly, check the value of ulimit from HP-UX Franck, Korbel, Gordon E. wrote: > > Sybase 11.x > Peoplesoft 7 > HP-UX > SQR:V3.0.12.3 > > We are creating some customized Invoice forms in SQR, and we are > encountering an unusual error -- the message is > > PeopleSoft prcs_sqr Started > Process instance is 6520 > SQR Output file is /opt/psoftfs/ps_output/biivcpn_6520.lis > SQR: Structured Query Report Writer V3.0.12.3 > Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. > > prcs_sqr[292]: 6536 Memory fault > prcs_sqr: ------------------- Error ------------------- > prcs_sqr: Command failure > prcs_sqr: SQR Command Terminated abnormally > prcs_sqr: Return code : 139 > prcs_sqr: ------------------- Error ------------------- > > This program has a large number of #include files, and in general is fairly > lengthy, but is otherwise 'correct' -- I can say this with confidence > because when run from the Windows environment the same program executes > correctly. It seems as though we are passing some critical threshold when > ANY code is added to the SQR. > > The error occurs prior to any processing occuring -- it seems as though it > blows when SQR parses and 'compiles' the source code. > > Any Ideas? > > Thanks, > > -Gordon From owner-sqr-users@list.iex.net Fri Apr 2 13:58:24 1999 Date: Fri, 2 Apr 1999 20:47:51 +0100 From: Franck Masson Subject: Re: logo what is your System : Unix or NT ? what is your Printer: Postcript or PCL ? If postcript use the eps format for your logo franck, Lily Van wrote: > > Hi all, > > If there's already a resolution to this, please let me know and I do apologize for revisiting the topic. The problem I'm encountering is when I print Purchase Order(SQR) w/ the company's logo. If I route it to a .spf file and then print it then the logo is fine. If I route it directly from PS's process to a printer then the logo only appears as a box w/ small fined dots. > > I've tried .bmp file as well as .gif file and the results are the same. > This problem occurs w/ PS's delivered program and PS's logo as well. I've placed a call into PS but I was hoping this would provide faster response. > > Any help is truly appreciated. > > Lily From owner-sqr-users@list.iex.net Fri Apr 2 14:07:50 1999 Date: Fri, 2 Apr 1999 20:54:56 +0100 From: Franck Masson Subject: Re: Unusual Error -- memory fault hi, this will check if it is an OS limitation. execute ulimit command from the unix prompt to determine if any memory limitation is set. try to execute your sqr program as root. If it run correctly, check the value of ulimit from HP-UX Franck, Korbel, Gordon E. wrote: > > Sybase 11.x > Peoplesoft 7 > HP-UX > SQR:V3.0.12.3 > > We are creating some customized Invoice forms in SQR, and we are > encountering an unusual error -- the message is > > PeopleSoft prcs_sqr Started > Process instance is 6520 > SQR Output file is /opt/psoftfs/ps_output/biivcpn_6520.lis > SQR: Structured Query Report Writer V3.0.12.3 > Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. > > prcs_sqr[292]: 6536 Memory fault > prcs_sqr: ------------------- Error ------------------- > prcs_sqr: Command failure > prcs_sqr: SQR Command Terminated abnormally > prcs_sqr: Return code : 139 > prcs_sqr: ------------------- Error ------------------- > > This program has a large number of #include files, and in general is fairly > lengthy, but is otherwise 'correct' -- I can say this with confidence > because when run from the Windows environment the same program executes > correctly. It seems as though we are passing some critical threshold when > ANY code is added to the SQR. > > The error occurs prior to any processing occuring -- it seems as though it > blows when SQR parses and 'compiles' the source code. > > Any Ideas? > > Thanks, > > -Gordon From owner-sqr-users@list.iex.net Fri Apr 2 15:17:41 1999 Date: Fri, 2 Apr 1999 13:18:48 -0700 From: Christy Zigich Subject: Re: Communication Box with PeopleSoft I believe you are having trouble with your placement of the stdapi.sqc function calls. In the beginning of your program you should call : do Stdapi-Init and then you should also have a terminating call: do Stdapi-Term I hope that this helps! Christy Zigich -----Original Message----- From: Discussion of SQR, SQRIBE Technologies's database reporting language [mailto:SQR-USERS@list.iex.net] On Behalf Of Joe Wittrock Sent: Friday, April 02, 1999 10:05 AM To: Multiple recipients of list SQR-USERS Subject: Communication Box with PeopleSoft We are installing a new PeopleSoft HRMS. Our problem is that every time we run an SQR from the PeopleSoft menu a communication box opens up and asks us to hit enter and then put in our parameters. I know that -XCB suppresses the window, and have checked our process type panel to make sure that this is in there for SQR flags. I?ve even put the -XCB in the Configuration Manager for SQR flags, but it still won?t suppress the box. We just did a SQR upgrade from 3.x to 4.x, but I?m not sure if this was happening prior to that. Any help would be appreciated. Joe Wittrock joe.wittrock@smed.com From owner-sqr-users@list.iex.net Fri Apr 2 18:10:13 1999 Date: Fri, 2 Apr 1999 17:57:10 -0500 From: "Gina M. Bencke" Subject: Unusual Error -- memory fault Gordon, You may be running into a bug that I discovered in version 3.X of SQR. I too was developing a very large SQR program that, as it grew, became more and more unstable until finally it simply wouldn't run. The problem wound up being in the SQR product itself. Apparently certain variables in the C code were defined as 16 bit instead of 32 bit and memory was getting stomped on. There was no work around, SQRibe had to correct the problem and produce a new release of SQR. We were then forced to upgrade to version 4.x. Gina Bencke Bencke Consulting Corporation http://www.bencke.com -----Original Message----- From: Discussion of SQR, SQRIBE Technologies's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Korbel, Gordon E. Sent: Friday, April 02, 1999 1:31 PM To: Multiple recipients of list SQR-USERS Subject: Unusual Error -- memory fault Sybase 11.x Peoplesoft 7 HP-UX SQR:V3.0.12.3 We are creating some customized Invoice forms in SQR, and we are encountering an unusual error -- the message is PeopleSoft prcs_sqr Started Process instance is 6520 SQR Output file is /opt/psoftfs/ps_output/biivcpn_6520.lis SQR: Structured Query Report Writer V3.0.12.3 Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved. prcs_sqr[292]: 6536 Memory fault prcs_sqr: ------------------- Error ------------------- prcs_sqr: Command failure prcs_sqr: SQR Command Terminated abnormally prcs_sqr: Return code : 139 prcs_sqr: ------------------- Error ------------------- This program has a large number of #include files, and in general is fairly lengthy, but is otherwise 'correct' -- I can say this with confidence because when run from the Windows environment the same program executes correctly. It seems as though we are passing some critical threshold when ANY code is added to the SQR. The error occurs prior to any processing occuring -- it seems as though it blows when SQR parses and 'compiles' the source code. Any Ideas? Thanks, -Gordon From owner-sqr-users@list.iex.net Fri Apr 2 18:53:37 1999 Date: Fri, 2 Apr 1999 15:46:01 -0800 From: "Ying, Chenter" Subject: how to create a download file with long raw field > sqr 4.3.2 > solaris 2.6 > oracle 7.3.4 > peoplesoft 7.0 > > hello gurus: > is there a way that i can dump a file or take a flat file from another > database which has a long raw field? > for example, if i want to develop an interface between our badging system > (informix db) with our HR system (oracle) and bring over the employee > photos. how should i best do this? > > thx in advance. From owner-sqr-users@list.iex.net Mon Apr 5 18:11:31 1999 Date: Mon, 5 Apr 1999 17:06:43 -0500 From: Dan Harmuth Subject: Emailing a Report from within an SQR on Unix with MS Outlook Emai l SQR/4.3.2/PC/Windows NT 4.0/Oracle 7.3.4/Sep 14 1998 ((run from client; running HP Unix)) Has anyone ever created an SQR that can email a report generated from within. My environment is HP Unix with MS Outlook Email. Dan Harmuth Project Lead - PeopleSoft HRMS 817-415-2224 dharmu1@tandy.com From owner-sqr-users@list.iex.net Mon Apr 5 17:55:10 1999 Date: Mon, 5 Apr 1999 17:36:00 -0500 From: Shankar Veerashetty Subject: Re: Emailing a Report from within an SQR on Unix with MS Outlook Emai l E-Mail the report from SQR. The server address has to be known and have to declare the output to be 80000 characters per line so that the text wouldn't be wrapping. Eg. Begin-setup Declare-layout default Paper-size = (80000,10) Right-margin=0 ... end-setup let $host_string='$HOME/famil2.pl'||$hr_email||' ' || $email_subject || '<' || $out_file let $directory = getenv('HOME') let $outfile = $directory || '/outmail/new_hire.lis' new-report $outfile call system using $host_string #mail_status Thanks, Shankar Veerashetty Senior Consultant Software Associates Inc. (734)678-6696 -----Original Message----- From: Dan Harmuth [SMTP:DHarmu1@TANDY.COM] Sent: Monday, April 05, 1999 5:07 PM To: Multiple recipients of list SQR-USERS Subject: Emailing a Report from within an SQR on Unix with MS Outlook Emai l SQR/4.3.2/PC/Windows NT 4.0/Oracle 7.3.4/Sep 14 1998 ((run from client; running HP Unix)) Has anyone ever created an SQR that can email a report generated from within. My environment is HP Unix with MS Outlook Email. Dan Harmuth Project Lead - PeopleSoft HRMS 817-415-2224 dharmu1@tandy.com From owner-sqr-users@list.iex.net Tue Apr 6 09:53:50 1999 Date: Tue, 6 Apr 1999 07:29:13 -0700 From: Chris Molitor Subject: Calling DLLs from SQR on Win95 or NT I need to call a 3rd party dll from within an SQR. This SQR will always be run on an NT App Server. It looks like the Interoperability section of the SQR User Guide for version 4.3 (pages 165-166) describes different rules for windows based calls? Do I just need to add a line to the [SQR Extension] section of my SQR.INI to name my dll and I can make calls to any functions in it? I have searched the list archives and have only found references to extufunc.c, which sounds like it would require re-compiling the SQR executables. Any help would be appreciated. -CM === _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Tue Apr 6 18:34:40 1999 Date: Tue, 6 Apr 1999 16:23:15 PDT From: Everry Man Subject: (Off Subject) Make money surfing the web Sorry to hit this list with this message, but I thought some of you would want to partake in this opportunity. Here's the short of it: A company called Alladvantage is changing the way advertising is done on the web (they pay directly to the consumer). This is the talk of Silicon Valley, and quite a few people are jumping onboard. You can get more details and sign up at http://www.alladvantage.com Please use my referral number: AMC-317 Here are more details: As a member, AllAdvantage.com will pay you 50 cents an hour while you surf the Web for up to 40 hours per month. That's up to $20 per month just for browsing the Internet the way you normally do. All members download the AllAdvantage.com Viewbar™. The bar is standard ad height, about one-half inch on most screens. The bar can be minimized with a single click to eliminate it from the screen at any time. Refer friends to AllAdvantage.com and receive 10 cents per hour while they surf the Web. And receive an additional 5 cents per hour from the extended referrals that come in from your referrals (for new members extended as far as four referrals from your original referrals!) Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Tue Apr 6 18:59:38 1999 Date: Tue, 6 Apr 1999 18:48:14 -0500 From: "Buchanan, Timothy" Subject: Re: (Off Subject) Make money surfing the web This is why this should be a moderated list. A guy with a free and anonymous email account hits us with Spam. Sigh... On Tuesday, April 06, 1999 6:23 PM, Everry Man [SMTP:everryman@HOTMAIL.COM] wrote: > Sorry to hit this list with this message, but I thought some of you > would want to partake in this opportunity. > > Here's the short of it: A company called Alladvantage is changing the > way advertising is done on the web (they pay directly to the consumer). > This is the talk of Silicon Valley, and quite a few people are jumping > onboard. > > You can get more details and sign up at http://www.alladvantage.com > > Please use my referral number: AMC-317 > > Here are more details: > As a member, AllAdvantage.com will pay you 50 cents an hour while you > surf the Web for up to 40 hours per month. That's up to $20 per month > just for browsing the Internet the way you normally do. All members > download the AllAdvantage.com Viewbar(tm). The bar is standard ad height, > about one-half inch on most screens. The bar can be minimized with a > single click to eliminate it from the screen at any time. > > Refer friends to AllAdvantage.com and receive 10 cents per hour while > they surf the Web. And receive an additional 5 cents per hour from the > extended referrals that come in from your referrals (for new members > extended as far as four referrals from your original referrals!) > Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Tue Apr 6 19:16:09 1999 Date: Tue, 6 Apr 1999 16:58:09 -0700 From: Paul Westcott Subject: Re: (Off Subject) Make money surfing the web everryman is a loser, no doubt. he's also clueless. by using hotmail he left his originating IP address on the mail header, belonging to Columbia College in Chicago (www.colum.edu). i forwarded the message to the university's sysadmin, asking if the college is in the spam business. and, if not, to fix their problem. -----Original Message----- From: Buchanan, Timothy To: Multiple recipients of list SQR-USERS Date: Tuesday, April 06, 1999 4:50 PM Subject: Re: (Off Subject) Make money surfing the web |This is why this should be a moderated list. A guy with a free and anonymous |email account hits us with Spam. Sigh... | |On Tuesday, April 06, 1999 6:23 PM, Everry Man [SMTP:everryman@HOTMAIL.COM] |wrote: |> Sorry to hit this list with this message, but I thought some of you |> would want to partake in this opportunity. |> |> Here's the short of it: A company called Alladvantage is changing the |> way advertising is done on the web (they pay directly to the consumer). |> This is the talk of Silicon Valley, and quite a few people are jumping |> onboard. |> |> You can get more details and sign up at http://www.alladvantage.com |> |> Please use my referral number: AMC-317 |> |> Here are more details: |> As a member, AllAdvantage.com will pay you 50 cents an hour while you |> surf the Web for up to 40 hours per month. That's up to $20 per month |> just for browsing the Internet the way you normally do. All members |> download the AllAdvantage.com Viewbar(tm). The bar is standard ad height, |> about one-half inch on most screens. The bar can be minimized with a |> single click to eliminate it from the screen at any time. |> |> Refer friends to AllAdvantage.com and receive 10 cents per hour while |> they surf the Web. And receive an additional 5 cents per hour from the |> extended referrals that come in from your referrals (for new members |> extended as far as four referrals from your original referrals!) |> Get Your Private, Free Email at http://www.hotmail.com | From owner-sqr-users@list.iex.net Tue Apr 6 19:25:19 1999 Date: Wed, 7 Apr 1999 01:13:16 +0100 From: "Allen, Dave" Subject: Re: (Off Subject) Make money surfing the web Thanks !! -----Original Message----- From: Paul Westcott [mailto:paul.westcott@SQRIBE.COM] Sent: Tuesday, April 06, 1999 7:58 PM To: Multiple recipients of list SQR-USERS Subject: Re: (Off Subject) Make money surfing the web everryman is a loser, no doubt. he's also clueless. by using hotmail he left his originating IP address on the mail header, belonging to Columbia College in Chicago (www.colum.edu). i forwarded the message to the university's sysadmin, asking if the college is in the spam business. and, if not, to fix their problem. -----Original Message----- From: Buchanan, Timothy To: Multiple recipients of list SQR-USERS Date: Tuesday, April 06, 1999 4:50 PM Subject: Re: (Off Subject) Make money surfing the web |This is why this should be a moderated list. A guy with a free and anonymous |email account hits us with Spam. Sigh... | |On Tuesday, April 06, 1999 6:23 PM, Everry Man [SMTP:everryman@HOTMAIL.COM] |wrote: |> Sorry to hit this list with this message, but I thought some of you |> would want to partake in this opportunity. |> |> Here's the short of it: A company called Alladvantage is changing the |> way advertising is done on the web (they pay directly to the consumer). |> This is the talk of Silicon Valley, and quite a few people are jumping |> onboard. |> |> You can get more details and sign up at http://www.alladvantage.com |> |> Please use my referral number: AMC-317 |> |> Here are more details: |> As a member, AllAdvantage.com will pay you 50 cents an hour while you |> surf the Web for up to 40 hours per month. That's up to $20 per month |> just for browsing the Internet the way you normally do. All members |> download the AllAdvantage.com Viewbar(tm). The bar is standard ad height, |> about one-half inch on most screens. The bar can be minimized with a |> single click to eliminate it from the screen at any time. |> |> Refer friends to AllAdvantage.com and receive 10 cents per hour while |> they surf the Web. And receive an additional 5 cents per hour from the |> extended referrals that come in from your referrals (for new members |> extended as far as four referrals from your original referrals!) |> Get Your Private, Free Email at http://www.hotmail.com | From owner-sqr-users@list.iex.net Tue Apr 6 20:04:35 1999 Date: Tue, 6 Apr 1999 17:51:37 -0700 From: Boris Nadler Subject: Re: (Off Subject) Make money surfing the web Elunia, Check it, Looks like interesting ? Love. Borisex. At 04:23 PM 4/6/99 -0700, you wrote: >Sorry to hit this list with this message, but I thought some of you >would want to partake in this opportunity. > >Here's the short of it: A company called Alladvantage is changing the >way advertising is done on the web (they pay directly to the consumer). >This is the talk of Silicon Valley, and quite a few people are jumping >onboard. > >You can get more details and sign up at http://www.alladvantage.com > >Please use my referral number: AMC-317 > >Here are more details: >As a member, AllAdvantage.com will pay you 50 cents an hour while you >surf the Web for up to 40 hours per month. That's up to $20 per month >just for browsing the Internet the way you normally do. All members >download the AllAdvantage.com Viewbar™. The bar is standard ad height, >about one-half inch on most screens. The bar can be minimized with a >single click to eliminate it from the screen at any time. > >Refer friends to AllAdvantage.com and receive 10 cents per hour while >they surf the Web. And receive an additional 5 cents per hour from the >extended referrals that come in from your referrals (for new members >extended as far as four referrals from your original referrals!) >Get Your Private, Free Email at http://www.hotmail.com > From owner-sqr-users@list.iex.net Tue Apr 6 20:49:38 1999 Date: Wed, 7 Apr 1999 11:31:09 +1000 From: Steve Cavill Subject: Re: (Off Subject) Make money surfing the web I forrwarded this to hotmail for action as they don't allow their users to send spam. Steve. ------------------------------------------------------------------------- Steve Cavill Technical Services Manager, Asia Pacific SQRIBE Technologies 1/14 Aquatic Drive Frenchs Forest NSW 2086 Australia ph: 02 9975 7600 mobile: 0417 439 160 fax: 02 9975 7602 mailto:steve.cavill@sqribe.com.au http://www.sqribe.com -----Original Message----- From: Discussion of SQR, SQRIBE Technologies's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Everry Man Sent: Wednesday, 7 April 1999 9:45 To: Multiple recipients of list SQR-USERS Subject: (Off Subject) Make money surfing the web <..snip..> From owner-sqr-users@list.iex.net Wed Apr 7 08:57:00 1999 Date: Wed, 7 Apr 1999 09:47:46 -0400 From: Tony DeLia Subject: Re: Syntax for Count(*) - DOTS!!! WITHOUT ANY DOTS??? You CAN and SHOULD take advantage of "dots" whenever possible... The statement: "The ampersand variable should be one word without any dots" SHOULD BE changed to: "The ampersand variable should be one word AND CAN have a dot" "Ampersand variable" referring to the column synonym... whether implicit or explicit... You may have noticed all "implied" column synonyms have "dots" when the select list has "dots"... I use this syntax frequently: begin-select decode(a.flag,'X',1,2) &a.flag sum(a.amount) &a.amount sum(b.amount) &b.amount count(*) &b.count a.something &a.something_with_dot etc... Combining the table alias with the column synonym is sensible, improves readability and remains consistent with any other columns in the select list... In the example above the source table is always identified using the alias... It doesn't get much simpler... -Tony DeLia PS - Each database has their own individual rules. The original request was for Oracle and this pertains to Oracle (and similar databases). One more thing: let ERNmtx.ERNttl(#ERNctr) = &b.erncd DO NOT move the "count" to the Earnings Code Heading!!!! Parijat Sahai wrote: > > Remove the dot from &b.count, i.e., you should have the following > syntax: > > begin-select > > b.erncd > count(b.*) &b_count > > let $E_ERNCD = &b.erncd > ... > let ERNmtx.ERNttl(#ERNctr) = &b_count > ..... > end-select > > The ampersand variable should be one word without any dots. I guess same > holds for $ and # variables in SQR. You can have "-" and "_" as > separators to make variable names more readable if you like. > > Parijat. > > >From: Bryan Richey > >Reply-To: SQR-USERS@list.iex.net > >To: Multiple recipients of list SQR-USERS > >Subject: Syntax for Count(*) > >Date: Thu, 1 Apr 1999 12:07:48 -0800 > > > >Hello all! > > > >I am trying to populate the dynamic headings that follow ....I was > >wondering if someone has any insight to the correct syntax for the > >count(*)...I am currently getting a mess of SQL errors... > > > >Thanks, > > > >Bryan > > > > > >!****************************************************** > >begin-procedure Determine-Sequence > >!****************************************************** > >create-array name=ERNmtx size=11 field=ERNttl:char > > > >do erncd-detail > > > >end-procedure > > > >!******************************************************begin-procedure > >erncd-detail > >!****************************************************** > >let #ERNctr = 0 > > > >begin-select > > > >b.erncd > >count(*) &b.count > > > > let $E.ERNCD = &b.erncd > > let #ERNctr = #ERNctr + 1 > > let ERNmtx.ERNttl(#ERNctr) = &b.count > > if #ERNctr >= 10 > > exit-select > > end-if > > > > from ps_dhm_emp_detail b > >where b.DEPTID = $V_DEPTID > > and b.paygroup = $V_PayGroup > > and b.pay_end_dt = $V_Pay_End_dt > > and b.DHM_WEEK_NBR = #V_Week_Nbr > >order by 2 desc, 1 > >!group by $E.ERNCD > >!group by b.erncd > > > >end-select > > > >let $H01 = ERNmtx.ERNttl(1) > >let $H02 = ERNmtx.ERNttl(2) > >let $H03 = ERNmtx.ERNttl(3) > >let $H04 = ERNmtx.ERNttl(4) > >let $H05 = ERNmtx.ERNttl(5) > >let $H06 = ERNmtx.ERNttl(6) > >let $H07 = ERNmtx.ERNttl(7) > >let $H08 = ERNmtx.ERNttl(8) > >let $H09 = ERNmtx.ERNttl(9) > >let $H10 = ERNmtx.ERNttl(10) > > > >display $h01 > > > >end-procedure > > > > > >_________________________________________________________ > >Do You Yahoo!? > >Get your free @yahoo.com address at http://mail.yahoo.com > > Get Your Private, Free Email at http://www.hotmail.com -- Tony DeLia AnswerThink Consulting Group PeopleSoft Solutions Practice - Delphi Partners tdelia@erols.com From owner-sqr-users@list.iex.net Wed Apr 7 09:35:47 1999 Date: Wed, 7 Apr 1999 09:16:12 -0500 From: Wes Williams Subject: How are dates handled in SQR with SQL Server Maximo 3.02 SQL Server 6.5 SQR 3.0.13.3 Reportmate 3.5 I'm having a difficult time converting my SQLBase reports to now run on SQL Server. My main problem is with dates. Chris Wanko was kind enough to provide an SQC that he uses on Sybase, but unfortunately it doesn't work here. It looks as though the SQR checks the syntax prior to running and fails since there is no value for $fromdate until actual execution (I never get prompted). example begin-program Input $FromDate type = date Let $FromDate = UPPER($FromDate) do check-valid-date . . . Begin-procedure check-valid-date begin-select convert(varchar(10),convert(datetime, $FromDate),101) &FromDate end-select end-procedure Here is the error (SQR 1303) Error in SQL (perhaps missing &name after expression): select convert(varchar(10),convert(datetime, ?),101) (SQR 1304) Check SELECT columns, expressions and 'where' clause for syntax. In addition, I try different combinations for date entry(those that work in ISql) and they don't want to work in SQR. TIA Wes Wes Williams Sundstrand Aerospace From owner-sqr-users@list.iex.net Wed Apr 7 10:58:50 1999 Date: Wed, 7 Apr 1999 09:32:59 -0500 From: Gracen Duffield Subject: Re: (Off Subject) Make money surfing the web ads@alladvantage.com I went out to the alladvantage site and it looks like the most significant email address ( i.e., the one that generates money for the site ) is the address for advertisers listed above. I forwarded a copy of this message to that address, since they are the beneficiaries of the spam. Gracen Duffield Texas Department of Housing and Community Affairs 475-3839 -----Original Message----- From: Paul Westcott [mailto:paul.westcott@SQRIBE.COM] Sent: Tuesday, April 06, 1999 6:58 PM To: Multiple recipients of list SQR-USERS Subject: Re: (Off Subject) Make money surfing the web everryman is a loser, no doubt. he's also clueless. by using hotmail he left his originating IP address on the mail header, belonging to Columbia College in Chicago (www.colum.edu). i forwarded the message to the university's sysadmin, asking if the college is in the spam business. and, if not, to fix their problem. -----Original Message----- From: Buchanan, Timothy To: Multiple recipients of list SQR-USERS Date: Tuesday, April 06, 1999 4:50 PM Subject: Re: (Off Subject) Make money surfing the web |This is why this should be a moderated list. A guy with a free and anonymous |email account hits us with Spam. Sigh... | |On Tuesday, April 06, 1999 6:23 PM, Everry Man [SMTP:everryman@HOTMAIL.COM] |wrote: |> Sorry to hit this list with this message, but I thought some of you |> would want to partake in this opportunity. |> |> Here's the short of it: A company called Alladvantage is changing the |> way advertising is done on the web (they pay directly to the consumer). |> This is the talk of Silicon Valley, and quite a few people are jumping |> onboard. |> |> You can get more details and sign up at http://www.alladvantage.com |> |> Please use my referral number: AMC-317 |> |> Here are more details: |> As a member, AllAdvantage.com will pay you 50 cents an hour while you |> surf the Web for up to 40 hours per month. That's up to $20 per month |> just for browsing the Internet the way you normally do. All members |> download the AllAdvantage.com Viewbar(tm). The bar is standard ad height, |> about one-half inch on most screens. The bar can be minimized with a |> single click to eliminate it from the screen at any time. |> |> Refer friends to AllAdvantage.com and receive 10 cents per hour while |> they surf the Web. And receive an additional 5 cents per hour from the |> extended referrals that come in from your referrals (for new members |> extended as far as four referrals from your original referrals!) |> Get Your Private, Free Email at http://www.hotmail.com | From owner-sqr-users@list.iex.net Wed Apr 7 12:25:32 1999 Date: Wed, 7 Apr 1999 08:16:54 -0700 From: Tai Luong Subject: Re: How are dates handled in SQR with SQL Server Bill, It appears to me the 'from' clause is missing. Add 'from dual' to your query and see if it'll work. Hope this will help. Tai Luong. On Wed, 7 Apr 1999, Wes Williams wrote: > Maximo 3.02 > SQL Server 6.5 > SQR 3.0.13.3 > Reportmate 3.5 > > I'm having a difficult time converting my SQLBase reports to now run on SQL Server. My main problem is with dates. Chris Wanko was kind enough to provide an SQC that he uses on Sybase, but unfortunately it doesn't work here. > > It looks as though the SQR checks the syntax prior to running and fails since there is no value for $fromdate until actual execution (I never get prompted). > > example > > begin-program > Input $FromDate type = date > Let $FromDate = UPPER($FromDate) > do check-valid-date > . > . > . > Begin-procedure check-valid-date > begin-select > convert(varchar(10),convert(datetime, $FromDate),101) &FromDate > end-select > end-procedure > > Here is the error > (SQR 1303) Error in SQL (perhaps missing &name after expression): > select convert(varchar(10),convert(datetime, ?),101) > (SQR 1304) Check SELECT columns, expressions and 'where' clause for syntax. > > In addition, I try different combinations for date entry(those that work in ISql) and they don't want to work in SQR. > > TIA > > Wes > > Wes Williams > Sundstrand Aerospace > From owner-sqr-users@list.iex.net Wed Apr 7 10:56:40 1999 Date: Wed, 7 Apr 1999 10:38:27 -0500 From: "Korbel, Gordon E." Subject: SQR, WTS printing and Peoplesoft Sybase 11.9.2 Peoplesoft 7.0 SQR 3.0.18.1.1 (Windows) All, Many of our users commnly access Peoplesoft via WTS. However, we have been unable to come up with an acceptable SQR-printing solution for this setup. Printer:WP appears to ignore many report formatting tags -- landscape, fonts, etc. Any ideas? Thanks, -Gordon Korbel From owner-sqr-users@list.iex.net Wed Apr 7 12:05:30 1999 Date: Wed, 7 Apr 1999 09:19:19 -0700 From: Vimce Mancino Subject: Check print outsource Has anyone used a provider to JUST print checks, create direct deposit tape, tax reporting and tax filling from PeopleSoft? I have a client that only wants to run the gross to net. Thank you From owner-sqr-users@list.iex.net Wed Apr 7 12:46:14 1999 Date: Wed, 7 Apr 1999 13:29:04 -0400 From: Marc Steele Subject: Re: Check print outsource Vince, I work for a company called reSource Partner, Inc.. We do outsourcing, implementations and consultation for PeopleSoft HRMS and Financials. We are a certified PeopleSoft Select and Outsourcing Partner and we provide the payroll services that you have specified. If you are interested in how our company can be of assistance, please e-mail me at msteele@resourcepartner.com. I will be more than happy assist you. Regards. Marcus Steele Systems Analyst/Implementation Specialist reSOURCE PARTNER, Inc. Vimce Mancino on 04/07/99 12:19:19 PM Please respond to SQR-USERS@list.iex.net To: Multiple recipients of list SQR-USERS cc: (bcc: Marc Steele/HRMS/rSP) Subject: Check print outsource Has anyone used a provider to JUST print checks, create direct deposit tape, tax reporting and tax filling from PeopleSoft? I have a client that only wants to run the gross to net. Thank you From owner-sqr-users@list.iex.net Wed Apr 7 13:05:37 1999 Date: Wed, 7 Apr 1999 12:41:07 -0500 From: Wes Williams Subject: Re: How are dates handled in SQR with SQL Server Well I figured out (sqribe mentioned a bug #586)I have to make it a dynamic variable to get dates to work e.g. INPUT $FROMDATE type = date LET $FROMDATE = UPPER($FROMDATE) let $fromdate = 'reportdate >= '||''''||$fromdate||'''' . . . FROM workorder WHERE status = 'INPRG' AND ESTLABHRS >= 1 AND LEADCRAFT LIKE '583%' and [$fromdate] Wes >>> Wes Williams 04/07 9:16 AM >>> Maximo 3.02 SQL Server 6.5 SQR 3.0.13.3 Reportmate 3.5 I'm having a difficult time converting my SQLBase reports to now run on SQL Server. My main problem is with dates. Chris Wanko was kind enough to provide an SQC that he uses on Sybase, but unfortunately it doesn't work here. It looks as though the SQR checks the syntax prior to running and fails since there is no value for $fromdate until actual execution (I never get prompted). example begin-program Input $FromDate type = date Let $FromDate = UPPER($FromDate) do check-valid-date . . . Begin-procedure check-valid-date begin-select convert(varchar(10),convert(datetime, $FromDate),101) &FromDate end-select end-procedure Here is the error (SQR 1303) Error in SQL (perhaps missing &name after expression): select convert(varchar(10),convert(datetime, ?),101) (SQR 1304) Check SELECT columns, expressions and 'where' clause for syntax. In addition, I try different combinations for date entry(those that work in ISql) and they don't want to work in SQR. TIA Wes Wes Williams Sundstrand Aerospace Wes Williams Sundstrand Aerospace From owner-sqr-users@list.iex.net Wed Apr 7 16:03:48 1999 Date: Wed, 7 Apr 1999 13:32:09 -0700 From: Joan Hudson Subject: Re: Calling DLLs from SQR on Win95 or NT If the 3rd party dll was made specifically to be used with sqr, putting the location in the ini file will work. However, it seems unlikely that this would be the case. You will probably need to make a "translator" dll which tells sqr about the calls you will be making. See the examples in ufunc.c (unix) or extufunc.c (win/nt). joan -----Original Message----- From: Chris Molitor To: Multiple recipients of list SQR-USERS Date: Tuesday, April 06, 1999 7:50 AM Subject: Calling DLLs from SQR on Win95 or NT >I need to call a 3rd party dll from within an SQR. This SQR will >always be run on an NT App Server. It looks like the Interoperability >section of the SQR User Guide for version 4.3 (pages 165-166) describes >different rules for windows based calls? Do I just need to add a line >to the [SQR Extension] section of my SQR.INI to name my dll and I can >make calls to any functions in it? > >I have searched the list archives and have only found references to >extufunc.c, which sounds like it would require re-compiling the SQR >executables. Any help would be appreciated. > >-CM >=== > >_________________________________________________________ >Do You Yahoo!? >Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Wed Apr 7 16:06:06 1999 Date: Wed, 7 Apr 1999 13:51:08 -0700 From: Joan Hudson Subject: Re: Calling DLLs from SQR on Win95 or NT A couple more things about those dll's -- 1) Of course it's not unix -- it's a dll -- am not concentrating ok here :) 2) You don't need to recompile everything, but you will need to put together and compile that translator dll. joan -----Original Message----- From: Chris Molitor To: Multiple recipients of list SQR-USERS Date: Tuesday, April 06, 1999 7:50 AM Subject: Calling DLLs from SQR on Win95 or NT >I need to call a 3rd party dll from within an SQR. This SQR will >always be run on an NT App Server. It looks like the Interoperability >section of the SQR User Guide for version 4.3 (pages 165-166) describes >different rules for windows based calls? Do I just need to add a line >to the [SQR Extension] section of my SQR.INI to name my dll and I can >make calls to any functions in it? > >I have searched the list archives and have only found references to >extufunc.c, which sounds like it would require re-compiling the SQR >executables. Any help would be appreciated. > >-CM >=== > >_________________________________________________________ >Do You Yahoo!? >Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Wed Apr 7 19:19:21 1999 Date: Wed, 7 Apr 1999 20:00:51 -0400 From: Ghislaine Brouillette Subject: Re: Check print outsource Yes we did that for a client. We used a third party party printing company and had better than expected results. If you would like more information, please e-mail me directly. Regards, GB Vimce Mancino wrote: > Has anyone used a provider to JUST print checks, create direct deposit > tape, tax reporting and tax filling from PeopleSoft? > I have a client that only wants to run the gross to net. > Thank you From owner-sqr-users@list.iex.net Wed Apr 7 23:12:27 1999 Date: Thu, 8 Apr 1999 10:53:02 +0700 From: Hartono Sutirman Subject: Simple Query Hi... Could anyone tell me what is the difference between these two Query and why they produce a different result ? I only want to get the current record from the JOB record. any help would very appreciated thanks in advance regards, Hartono Sutirman This one I use alias A in the SubQuery ************************************** SQL> ED Wrote file afiedt.buf 1 SELECT EMPLID, EFFDT 2 FROM PS_JOB 3* WHERE EFFDT = (SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID = EMPLID) SQL> / EMPLID EFFDT ----------- --------- 680000643 01-OCT-98 This one I use alias A in the main Query **************************************** SQL> ED Wrote file afiedt.buf 1 SELECT A.EMPLID, A.EFFDT 2 FROM PS_JOB A 3* WHERE A.EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = A.EMPLID) SQL> / EMPLID EFFDT ----------- --------- 130344940 07-AUG-98 680000643 01-OCT-98 T68456789 06-APR-92 680000136 09-AUG-98 From owner-sqr-users@list.iex.net Thu Apr 8 00:06:41 1999 Date: Wed, 7 Apr 1999 21:55:52 -0700 From: David Donnelly Subject: Re: Simple Query Dear Hartono, Your second query is a coordinated subquery and gets you the latest record for each EMPLID. The first example is not a coordinated subquery, as it does not refer back into the main query. The subquery is evaluated once, and is interpreted as "A.EMPLID = A.EMPLID". This finds one record, the one with the most recent date in the database. If you write "WHERE A.EMPLID = PS_JOB.EMPLID", I think you will have the same thing as your second example. Hope that helps Dave At 10:53 AM 4/8/1999 +0700, you wrote: >Hi... >Could anyone tell me what is the difference between these two Query and >why they produce a different result ? >I only want to get the current record from the JOB record. >any help would very appreciated >thanks in advance > >regards, >Hartono Sutirman > >This one I use alias A in the SubQuery >************************************** >SQL> ED >Wrote file afiedt.buf > 1 SELECT EMPLID, EFFDT > 2 FROM PS_JOB > 3* WHERE EFFDT = (SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID = >EMPLID) >SQL> / > >EMPLID EFFDT >----------- --------- >680000643 01-OCT-98 > > >This one I use alias A in the main Query >**************************************** >SQL> ED >Wrote file afiedt.buf > 1 SELECT A.EMPLID, A.EFFDT > 2 FROM PS_JOB A > 3* WHERE A.EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = >A.EMPLID) >SQL> / > >EMPLID EFFDT >----------- --------- >130344940 07-AUG-98 >680000643 01-OCT-98 >T68456789 06-APR-92 >680000136 09-AUG-98 > From owner-sqr-users@list.iex.net Thu Apr 8 01:45:21 1999 Date: Wed, 7 Apr 1999 23:37:53 -0700 From: Michael Lee Subject: Re: Simple Query Hartono. If you want to obtain the "CURRENT" JOB record, then your SQL is incorrect. Here's a query that should work, substituting [current date] with your DB's alias to the current date: SELECT A.EMPLID, A.EFFDT FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_JOB B WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD# = A.EMPL_RCD# AND B.EFFDT <= [current date]); You need to be aware of future dated rows in PeopleSoft. PeopleSoft is an effective-dated system and future dated rows are legal. Hope this helps. Good luck. Michael K. Lee mlee@agconsult.com Consultant AG Consulting, an ADP Company http://www.agconsult.com Hartono Sutirman on 04/07/99 08:53:02 PM Please respond to SQR-USERS@list.iex.net To: Multiple recipients of list SQR-USERS cc: (bcc: Michael Lee/Application Group) Subject: Simple Query Hi... Could anyone tell me what is the difference between these two Query and why they produce a different result ? I only want to get the current record from the JOB record. any help would very appreciated thanks in advance regards, Hartono Sutirman This one I use alias A in the SubQuery ************************************** SQL> ED Wrote file afiedt.buf 1 SELECT EMPLID, EFFDT 2 FROM PS_JOB 3* WHERE EFFDT = (SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID = EMPLID) SQL> / EMPLID EFFDT ----------- --------- 680000643 01-OCT-98 This one I use alias A in the main Query **************************************** SQL> ED Wrote file afiedt.buf 1 SELECT A.EMPLID, A.EFFDT 2 FROM PS_JOB A 3* WHERE A.EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = A.EMPLID) SQL> / EMPLID EFFDT ----------- --------- 130344940 07-AUG-98 680000643 01-OCT-98 T68456789 06-APR-92 680000136 09-AUG-98 From owner-sqr-users@list.iex.net Thu Apr 8 03:49:27 1999 Date: Thu, 8 Apr 1999 15:41:24 +0700 From: Hartono Sutirman Subject: How to Schedule SQR ? Hi... I'm new in this mailing list and SQR, yet the responses I get is great, I get the answers from the expert very Quickly and solved my problems. To everyone I want to say thank you ! I have another problem, I've already check the PeopleBook, but I cannot find what I want to do. 1. I want to schedule a report to run every month. I've already set the Run Occurence to Monthly. The problem is what should I use when I define a Process Type in the process scheduler, is it a SQR PROCESS, SQR REPORT or something else ?? what the difference between the two of them ? 2. I want to run this report automatically every month, so in my thinking I don't need to create a new panel group and place it on the peoplesoft Menu (We don't have to click on a menu to run this process), is it possible ?? If we can schedule a report/process to run automatically without asking user to click on the menu and run button, can anyone tell me how ?? any help would very appreciated thanks in advance regards, Hartono Sutirman From owner-sqr-users@list.iex.net Thu Apr 8 07:00:41 1999 Date: Thu, 8 Apr 1999 07:48:52 -0400 From: Doug Kinney Subject: Re: Check print outsource We use ADP to print our checks and advices. They were kind enough to provide us with a set of SQR's to extract the appropriate info. We also create an ACH file for transmission to the Fed Reserve. We also use ADP to print W2's and create the IRS tape. Doug Kinney doug.kinney@usa.net -----Original Message----- From: Vimce Mancino [SMTP:e5d4@EARTHLINK.NET] Sent: Wednesday, April 07, 1999 12:19 PM To: Multiple recipients of list SQR-USERS Subject: Check print outsource Has anyone used a provider to JUST print checks, create direct deposit tape, tax reporting and tax filling from PeopleSoft? I have a client that only wants to run the gross to net. Thank you From owner-sqr-users@list.iex.net Thu Apr 8 08:09:21 1999 Date: Thu, 8 Apr 1999 08:00:55 -0500 From: Lisa Prokopeak Subject: Re: Simple Query Make sure to add the EFFSEQ also, as a person can have multiple rows for the same effdt. select j.emplid, j.effdt from ps_job j and j.effdt = (select max(effdt) from ps_job where emplid = j.emplid and empl_rcd# = j.empl_rcd# and effdt <= [current date]) and j.effseq = (select max(effseq) from ps_job where emplid = j.emplid and empl_rcd# = j.empl_rcd# and effdt = j.effdt); -Lisa MLee@AGCONSULT.COM on 04/08/99 02:40:45 AM Please respond to SQR-USERS@list.iex.net@Internet To: SQR-USERS@list.iex.net@Internet cc: Subject: Re: Simple Query Hartono. If you want to obtain the "CURRENT" JOB record, then your SQL is incorrect. Here's a query that should work, substituting [current date] with your DB's alias to the current date: SELECT A.EMPLID, A.EFFDT FROM PS_JOB A WHERE A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_JOB B WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD# = A.EMPL_RCD# AND B.EFFDT <= [current date]); You need to be aware of future dated rows in PeopleSoft. PeopleSoft is an effective-dated system and future dated rows are legal. Hope this helps. Good luck. Michael K. Lee mlee@agconsult.com Consultant AG Consulting, an ADP Company http://www.agconsult.com Hartono Sutirman on 04/07/99 08:53:02 PM Please respond to SQR-USERS@list.iex.net To: Multiple recipients of list SQR-USERS cc: (bcc: Michael Lee/Application Group) Subject: Simple Query Hi... Could anyone tell me what is the difference between these two Query and why they produce a different result ? I only want to get the current record from the JOB record. any help would very appreciated thanks in advance regards, Hartono Sutirman This one I use alias A in the SubQuery ************************************** SQL> ED Wrote file afiedt.buf 1 SELECT EMPLID, EFFDT 2 FROM PS_JOB 3* WHERE EFFDT = (SELECT MAX(A.EFFDT) FROM PS_JOB A WHERE A.EMPLID = EMPLID) SQL> / EMPLID EFFDT ----------- --------- 680000643 01-OCT-98 This one I use alias A in the main Query **************************************** SQL> ED Wrote file afiedt.buf 1 SELECT A.EMPLID, A.EFFDT 2 FROM PS_JOB A 3* WHERE A.EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = A.EMPLID) SQL> / EMPLID EFFDT ----------- --------- 130344940 07-AUG-98 680000643 01-OCT-98 T68456789 06-APR-92 680000136 09-AUG-98 From owner-sqr-users@list.iex.net Thu Apr 8 08:34:29 1999 Date: Thu, 8 Apr 1999 09:23:22 -0400 From: Sherry Jin Subject: Print Signature through SQR We are thinking about printing signature on PO so that it can be faxed to vendor directly. Does anyone have any experience about printing signature through SQR? Thanks in advance for your suggestion Sherry From owner-sqr-users@list.iex.net Thu Apr 8 10:46:55 1999 Date: Thu, 8 Apr 1999 10:19:47 -0400 From: Eric Dimick Eastman Subject: Re: SQR as data transfer tool Redleaf, My highly unscientific experience shows that as far as performance there is virtually no difference. I have gotten better than 110 records per second on several data load programs using SQR. These programs did some parsing, manipulation and key lookups in addition to loading the records. The cost of executing the SQL statements total swamps the time spent processing the data in the program. SQR also has a few features like "load-lookup" that are highly efficient and easy to use. The big difference comes in programming and maintenance time. C is a pain. Well written SQR programs read like english and are quick to write compared to C. Unless you have to calculate 500 digits of pi between each record I'd use SQR. Good luck, Eric "redleaf@mindspring.com" wrote: > > The company I am currently working for may be considering using SQR > instead of C as a tool for data transfers from source tables to target > tables. Does anyone have any thoughts and/or statistics about the > efficiency of SQR vs. C for this? Has anyone performed any tests along > these lines? -- ------------------------------------------------------------------------ Eric Dimick Eastman | To match what we [humans] can do, there would Ray Ontko & Co. | have to 3 billion of them [computers] . . . erice@ontko.com | wired together, . . . talking incessantly. http://www.ontko.com | Lewis Thomas, The Lives of a Cell 1974 From owner-sqr-users@list.iex.net Thu Apr 8 09:36:02 1999 Date: Thu, 8 Apr 1999 10:21:49 -0400 From: "redleaf@mindspring.com" Subject: SQR as data transfer tool The company I am currently working for may be considering using SQR instead of C as a tool for data transfers from source tables to target tables. Does anyone have any thoughts and/or statistics about the efficiency of SQR vs. C for this? Has anyone performed any tests along these lines? From owner-sqr-users@list.iex.net Thu Apr 8 10:42:39 1999 Date: Thu, 8 Apr 1999 10:18:11 -0500 From: "Gottdenker, Jay S" Subject: Re: Print Signature through SQR We had to do this for printing signatures on payroll checks. Basically the process you will follow is like this (there may be other variations as well): 1) load your signature font on the printer from which you will be printing your POs, using a utility like "fload" - you probably want to secure this font (one possible way is to load the font into RAM rather than a hard drive, and power off the printer when you're done, then reload the next time). 2) get the appropriate code to invoke the font from the printer (most laser printers will have some kind of control panel option to print the available fonts and their associated codes). 3) add a line to your SQR to invoke the signature font on the printer like, "encode '<39>&l0O<39>(1Q<39>(s0p1.00h72.0v0s0b0T' into $SigFont" !this will be the code you get from the !printer 4) Add some lines to print the signature print $SigFont (+2,1) code print '!!H' (,+2) !this line will contain whatever character your signature font uses to invoke the !signature Hope this helps! Jay Gottdenker > -----Original Message----- > From: Sherry Jin [SMTP:sjin@WINSTAR.COM] > Sent: Thursday, April 08, 1999 8:23 AM > To: Multiple recipients of list SQR-USERS > Subject: Print Signature through SQR > > We are thinking about printing signature on PO so that it can be faxed to > vendor directly. Does anyone have any experience about printing signature > through SQR? > > Thanks in advance for your suggestion > > Sherry From owner-sqr-users@list.iex.net Thu Apr 8 11:43:44 1999 Date: Thu, 8 Apr 1999 11:33:57 -0400 From: "Dray, Adam" Subject: Re: SQR as data transfer tool I have thoughts, but no statistics to back them. My instinct is that C will be more efficient in terms of run time, but that SQR will be much more efficient to write. However, without knowing more about the problem, I can't give an informed answer. If you're transferring between tables, why can't you use a database tool, such as an INSERT statement, replication, CIS, or the likes of bcpxfer (for Sybase, at least)? Adam Dray > -----Original Message----- > From: redleaf@mindspring.com [SMTP:redleaf@MINDSPRING.COM] > Sent: Thursday, April 08, 1999 10:22 AM > To: Multiple recipients of list SQR-USERS > Subject: SQR as data transfer tool > > The company I am currently working for may be considering using SQR > instead of C as a tool for data transfers from source tables to target > tables. Does anyone have any thoughts and/or statistics about the > efficiency of SQR vs. C for this? Has anyone performed any tests along > these lines? From owner-sqr-users@list.iex.net Thu Apr 8 10:59:04 1999 Date: Thu, 8 Apr 1999 08:47:35 -0700 From: Aboukr Sadikh Hi Gurus, Oracle:7.3.4 Unix PeopleSoft Public Sector 7.04 I'm to run one SQR in which the user has to input the pay_end_dt, and I get the following error message: Please press ENTER (Do not input a value): Report Began: 09:01:47 Update PaySheets with Pay Input Data Enter Payroll Run ID: 902 There is more than on Pay Calendar for the Run ID. You must identify the Pay Calendar. Enter Company ID (e.g. ccb): cob Enter Pay Group (e.g. sm1): gen Enter Pay End Date (e.g. 15-Nov-89): 17/01/1999 (SQR 1943) The date '17/01/1999' is not in one of the accepted formats listed below: MM/DD/YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] Enter Pay End Date (e.g. 15-Nov-89): (SQR 2514) Enter a date in one of the following formats: MM/DD/YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] I changed the date setting in Contro Panel, but I still have the same error. Any ideas??? Thanks in advance. BS _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Thu Apr 8 10:56:39 1999 Date: Thu, 8 Apr 1999 11:49:39 -0400 From: Steve Krim Subject: Editor info Has anyone created a Multi-Edit tmplate for SQR? If so, would you mind sharing it? Thanks, Steve From owner-sqr-users@list.iex.net Thu Apr 8 11:06:18 1999 Date: Thu, 8 Apr 1999 10:52:46 -0500 From: Krishnan Sarangarajan Subject: Re: How do I know What Qtr of Year it is ! > ---------- > From: Dominick LoGiudice[SMTP:dlogiudice@PINE.VPCC.SUNYSB.EDU] > Sent: Thursday, April 08, 1999 1:31 PM > To: Multiple recipients of list SQR-USERS > Subject: How do I know What Qtr of Year it is ! > > I am writing an sqr that needs to look for qtr data. Example if > I ran today the run date I have is formatted like this 19990408. I can > substr out > the month as 04. Using 04 I need to look on a table if the date is '01' > or '02' or > '03'. Based on the run date the qtrs I need to look for will always > change. I next > run this sqr in July so my qtr rundate will be 07 so I need to look for > '04' , '05' or > '06'. Is there a delivered sqr to do this. > I guess the short story is I need to know what 3 months are the previous > qtr. > thanks > Dear Mr. Logiudice, With regards to what period ending is tied with what quarter, if you have a pay calendar, you could use it ? If I am not mistaken, sometimes the pay calendar are preset or pre-defined and other times one may have to define it and maintain it on an on-going basis. As for the pay calendar, the following is what we have at Ruan : SQL> desc ps_pay_calendar; Name Null? Type ------------------------------- -------- ---- COMPANY NOT NULL VARCHAR2(3) PAYGROUP NOT NULL VARCHAR2(3) PAY_END_DT NOT NULL DATE RUN_ID NOT NULL VARCHAR2(3) PAY_BEGIN_DT NOT NULL DATE CHECK_DT NOT NULL DATE PERIOD_WEEKS NOT NULL NUMBER(4) CALENDAR_YEAR NOT NULL NUMBER(4) QTRCD NOT NULL VARCHAR2(1) MONTHCD NOT NULL VARCHAR2(2) PAY_PERIOD NOT NULL VARCHAR2(1) PAY_PDS_PER_YEAR NOT NULL NUMBER(4) ACCRUAL_PCT NOT NULL NUMBER(5,3) REVERSE_FLAG NOT NULL VARCHAR2(1) PAY_SHEETS_RUN NOT NULL VARCHAR2(1) PAY_PRECALC_RUN NOT NULL VARCHAR2(1) PAY_CALC_RUN NOT NULL VARCHAR2(1) PAY_CONFIRM_START NOT NULL VARCHAR2(1) PAY_CONFIRM_RUN NOT NULL VARCHAR2(1) PAY_CHECKS_RUN NOT NULL VARCHAR2(1) PAY_OFF_CYCLE NOT NULL VARCHAR2(1) PAY_OFF_CYCLE_CLS NOT NULL VARCHAR2(1) GL_INTERFACE_RUN NOT NULL VARCHAR2(1) SINGLE_CHECK NOT NULL VARCHAR2(1) TF_TAX_FILE_BUILT NOT NULL VARCHAR2(1) TF_TAX_FILE_XMIT NOT NULL VARCHAR2(1) Hope it helps. Krishnan Sarangarajan ksaranga@ruan.com Ruan Transportation 3200 Ruan Center (DSM 6th Floor) Des Moines, IA 50309 [O] (515) 245-5655 [F] (515) 247-2284 From owner-sqr-users@list.iex.net Thu Apr 8 11:08:38 1999 Date: Thu, 8 Apr 1999 08:55:44 -0700 From: Chris Molitor Subject: Re: SQR as data transfer tool On a recent implementation we replaced a c interface program with one written in SQR for ease of maintenance. The initial decision was made to go with c for performance reasons, but the program quickly got very complicated and "buggy". The performance was as good or better when re-written in SQR mainly because the delivered functionality in SQR made it easy to code it simply. --- "redleaf@mindspring.com" wrote: > The company I am currently working for may be considering using SQR > instead of C as a tool for data transfers from source tables to > target > tables. Does anyone have any thoughts and/or statistics about the > efficiency of SQR vs. C for this? Has anyone performed any tests > along > these lines? > === _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Thu Apr 8 11:08:50 1999 Date: Thu, 8 Apr 1999 10:57:49 -0500 From: Krishnan Sarangarajan > Enter Pay End Date (e.g. 15-Nov-89): > (SQR 2514) Enter a date in one of the following formats: > MM/DD/YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]] > MM-DD-YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]] > MM.DD.YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]] > SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] > > I changed the date setting in Contro Panel, but I still have the same > error. Any ideas??? > > Thanks in advance. > > BS > Dear Mr. Sadikh, With regards to the date format, setting the date format using the control panel may in fact only alter the date format, the way Windows or the work-station looks at it. If you would like to alter the manner in which the database displays the date, or extracts it, you could do the following : begin-sql alter session set nls_date_format = 'DD-MON-YYYY' end-sql commit The above code can reside within your SQR or be called using an include file but in any case, it will alter the date format only for the session but hopefully, it helps. Sincerely, Krishnan Krishnan Sarangarajan ksaranga@ruan.com Ruan Transportation 3200 Ruan Center (DSM 6th Floor) Des Moines, IA 50309 [O] (515) 245-5655 [F] (515) 247-2284 From owner-sqr-users@list.iex.net Thu Apr 8 11:12:24 1999 Date: Thu, 8 Apr 1999 10:58:54 -0500 From: "Kidd, Kelli" 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_01BE81D8.B5CDEEE6 Content-Type: text/plain; charset="iso-8859-1" Try entering the date in the format specified in the input statement: 17-Jan-99 -----Original Message----- From: Aboukr Sadikh [mailto:bsadikh@YAHOO.COM] Sent: Thursday, April 08, 1999 10:48 AM To: Multiple recipients of list SQR-USERS Subject: Hi Gurus, Oracle:7.3.4 Unix PeopleSoft Public Sector 7.04 I'm to run one SQR in which the user has to input the pay_end_dt, and I get the following error message: Please press ENTER (Do not input a value): Report Began: 09:01:47 Update PaySheets with Pay Input Data Enter Payroll Run ID: 902 There is more than on Pay Calendar for the Run ID. You must identify the Pay Calendar. Enter Company ID (e.g. ccb): cob Enter Pay Group (e.g. sm1): gen Enter Pay End Date (e.g. 15-Nov-89): 17/01/1999 (SQR 1943) The date '17/01/1999' is not in one of the accepted formats listed below: MM/DD/YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] Enter Pay End Date (e.g. 15-Nov-89): (SQR 2514) Enter a date in one of the following formats: MM/DD/YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] I changed the date setting in Contro Panel, but I still have the same error. Any ideas??? Thanks in advance. BS _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ------_=_NextPart_001_01BE81D8.B5CDEEE6 Content-Type: text/html; charset="iso-8859-1" RE:

Try entering the date in the format specified in the input statement:

        17-Jan-99


-----Original Message-----
From: Aboukr Sadikh [mailto:bsadikh@YAHOO.COM]
Sent: Thursday, April 08, 1999 10:48 AM
To: Multiple recipients of list SQR-USERS
Subject:


Hi Gurus,

Oracle:7.3.4
Unix
PeopleSoft Public Sector 7.04

I'm to run one SQR in which the user has to input the pay_end_dt, and I
get the following error message:

Please press ENTER (Do not input a value):
Report Began: 09:01:47
Update PaySheets with Pay Input Data

Enter Payroll Run ID: 902
There is more than on Pay Calendar for the Run ID.
You must identify the Pay Calendar.
Enter Company ID (e.g. ccb): cob
Enter Pay Group (e.g. sm1): gen
Enter Pay End Date (e.g. 15-Nov-89): 17/01/1999
   (SQR 1943) The date '17/01/1999' is not in one of the accepted
formats listed below:
        MM/DD/YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]]
        MM-DD-YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]]
        MM.DD.YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]]
        SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]

Enter Pay End Date (e.g. 15-Nov-89):
(SQR 2514)    Enter a date in one of the following formats:
        MM/DD/YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]]
        MM-DD-YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]]
        MM.DD.YYYY [HH:MI[:SS[.NNNNNN]] [AM | PM]]
        SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]

I changed the date setting in Contro Panel, but I still have the same
error. Any ideas???

Thanks in advance.

BS
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

------_=_NextPart_001_01BE81D8.B5CDEEE6-- From owner-sqr-users@list.iex.net Thu Apr 8 12:08:24 1999 Date: Thu, 8 Apr 1999 12:50:47 -0400 From: "Bergeron, Marc, Herndon" Subject: Re: Print Signature through SQR use the print-image commmand. make the signature a bitmap file. the type is bmp-file and the file itself must have a .bmp extension. -----Original Message----- From: Sherry Jin [mailto:sjin@WINSTAR.COM] Sent: Thursday, April 08, 1999 9:23 AM To: Multiple recipients of list SQR-USERS Subject: Print Signature through SQR We are thinking about printing signature on PO so that it can be faxed to vendor directly. Does anyone have any experience about printing signature through SQR? Thanks in advance for your suggestion Sherry From owner-sqr-users@list.iex.net Thu Apr 8 16:52:36 1999 Date: Thu, 8 Apr 1999 13:49:53 -0400 From: Steve Krim Subject: Re: Editor info Franck, Is visualSqribe freeware? or part of a bundle? Thanks, Steve At 07:27 PM 4/8/99 +0100, you wrote: >try the visualsqribe product from sqribe. >it does what you want and more. > >franck, > >Steve Krim wrote: >> >> Has anyone created a Multi-Edit tmplate for SQR? If so, would you mind >> sharing it? >> >> Thanks, >> >> Steve > From owner-sqr-users@list.iex.net Thu Apr 8 13:08:22 1999 Date: Thu, 8 Apr 1999 10:55:24 -0700 From: Aboukr Sadikh Subject: help with date format Hi Guys, I have tried different ways of entering the date, but it looks like any of the formats is recognized as a valid date format. Can the problem be an installation one?? Thanks in advance for your hints. BS. *************************************************************************************** Please press ENTER (Do not input a value): Report Began: 01:06:57 Update PaySheets with Pay Input Data Enter Payroll Run ID: 902 There is more than on Pay Calendar for the Run ID. You must identify the Pay Calendar. Enter Company ID (e.g. ccb): cob Enter Pay Group (e.g. sm1): gen Enter Pay End Date (e.g. 15-Nov-89): 17-Jan-99 (SQR 1943) The date '17-Jan-99' is not in one of the accepted formats listed below: MM/DD/YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] Enter Pay End Date (e.g. 15-Nov-89): 17-JAN-99 (SQR 1943) The date '17-JAN-99' is not in one of the accepted formats listed below: MM/DD/YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] Enter Pay End Date (e.g. 15-Nov-89): 17-01-99 (SQR 1943) The date '17-01-99' is not in one of the accepted formats listed below: MM/DD/YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] Enter Pay End Date (e.g. 15-Nov-89): 17-01-1999 (SQR 1943) The date '17-01-1999' is not in one of the accepted formats listed below: MM/DD/YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] Enter Pay End Date (e.g. 15-Nov-89): 17-Jan-1999 (SQR 1943) The date '17-Jan-1999' is not in one of the accepted formats listed below: MM/DD/YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] Enter Pay End Date (e.g. 15-Nov-89): 1999-01-17 (SQR 1943) The date '1999-01-17' is not in one of the accepted formats listed below: MM/DD/YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] Enter Pay End Date (e.g. 15-Nov-89): 99-01-17 (SQR 1943) The date '99-01-17' is not in one of the accepted formats listed below: MM/DD/YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM-DD-YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] MM.DD.YYYY [BC | AD] [HH:MI[:SS[.NNNNNN]] [AM | PM]] SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] Enter Pay End Date (e.g. 15-Nov-89): 01-17-1999 (SQR 5528) ORACLE OFEN error 1843 in cursor 7: ORA-01843: not a valid month Error on line 966: (SQR 3725) Bad return fetching row from database. SQR: Program Aborting. _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Thu Apr 8 13:10:44 1999 Date: Thu, 8 Apr 1999 13:57:24 -0400 From: "Dray, Adam" Subject: Re: How do I know What Qtr of Year it is ! Without going into all the details, let me talk about one trick you can use: Set up two strings: Let $months = '010203040506070809101112' Let $month_map = '101010010101040404070707' In your SQL use a charindex or substr or whatever to determine the position of the month (01, 02, ..., 12) in $months. Use that index to grab a 2-character substring out of $month_map. Those two characters are the starting month for the quarter on which you need to report. /ad/ > -----Original Message----- > From: Dominick LoGiudice [SMTP:dlogiudice@PINE.VPCC.SUNYSB.EDU] > Sent: Thursday, April 08, 1999 2:32 PM > To: Multiple recipients of list SQR-USERS > Subject: How do I know What Qtr of Year it is ! > > I am writing an sqr that needs to look for qtr data. Example if > I ran today the run date I have is formatted like this 19990408. I can > substr out > the month as 04. Using 04 I need to look on a table if the date is '01' > or '02' or > '03'. Based on the run date the qtrs I need to look for will always > change. I next > run this sqr in July so my qtr rundate will be 07 so I need to look for > '04' , '05' or > '06'. Is there a delivered sqr to do this. > I guess the short story is I need to know what 3 months are the previous > qtr. > thanks From owner-sqr-users@list.iex.net Thu Apr 8 12:45:51 1999 Date: Thu, 8 Apr 1999 19:27:22 +0100 From: Franck Masson Subject: Re: Editor info try the visualsqribe product from sqribe. it does what you want and more. franck, Steve Krim wrote: > > Has anyone created a Multi-Edit tmplate for SQR? If so, would you mind > sharing it? > > Thanks, > > Steve From owner-sqr-users@list.iex.net Thu Apr 8 10:54:40 1999 Date: Thu, 8 Apr 1999 11:31:55 -0700 From: Dominick LoGiudice Subject: How do I know What Qtr of Year it is ! I am writing an sqr that needs to look for qtr data. Example if I ran today the run date I have is formatted like this 19990408. I can substr out the month as 04. Using 04 I need to look on a table if the date is '01' or '02' or '03'. Based on the run date the qtrs I need to look for will always change. I next run this sqr in July so my qtr rundate will be 07 so I need to look for '04' , '05' or '06'. Is there a delivered sqr to do this. I guess the short story is I need to know what 3 months are the previous qtr. thanks From owner-sqr-users@list.iex.net Thu Apr 8 14:38:49 1999 Date: Thu, 8 Apr 1999 15:22:54 -0400 From: "Holly N. Reid" Subject: Search directories for SQR on MVS Is there a way to first search one directory, then if its not found there search another directory for an SQR? I've heard that you can't do this on MVS. You can do it on every other environment, so I'm hoping this is not true. Please respond. Thanks, Holly N. Reid Elleon Dier, Inc. elleond@bellsouth.net From owner-sqr-users@list.iex.net Thu Apr 8 15:03:47 1999 Date: Thu, 8 Apr 1999 15:49:16 EDT From: Ken Look Subject: Re: Search directories for SQR on MVS Holly, I assume you mean searching PDS libraries when executing an SQR on the MVS mainframe. It's seems to be an accurate statement you made. We specify the PDS library in our SQRIN ddname. I tried using a DD concatenation of PDS libraries, removing the 1st member on purpose and the job returned an error message stating it couldn't open the 1st PDS library and gave the error "File not found", which means it didn't even bother using the 2nd PDS library. Hope this answers your question. Ken Look Look Consulting From owner-sqr-users@list.iex.net Thu Apr 8 15:19:48 1999 Date: Thu, 8 Apr 1999 16:07:26 -0400 From: Wade Wells Subject: Re: Year 2000 Vicki, Although human beings may consider the way Oracle handles YY to be in error, Oracle thinks of it as a feature. The YY format means current century prepended to the year. There is an alternative RR format, which has a complicated definition.Read about it in * Oracle SQL Reference Manual, Chapter 3, date format elements * Oracle magazine, Jan/Feb 1999 * chapter 8 of "High Performance Oracle8 Tuning" by Don Burleson RR uses a 2x2 decision matrix, comparing how current year (as YY) and the 2-digit year specified by RR relate in terms of 00-49 (low) or 50-99 (high). The 4 possibilities are: 1. current low, RR low -> this century 2. current low, RR high -> previous century 3. current high, RR low -> next century 4. current high, RR high -> this century RR is not a panacea. You still need YYYY to specifiy dates outside these assumptions. - Wade Wells Deepak Shirguppi wrote: > Vicki, > I tried doing this directly on the Oracle database. > I created a dummy table with name xyz and one column "trial", datatype > as date. > If you do a insert as follows : > insert into xyz values('29-FEB-00'); > I got an error. Basically Oracle thinks it to be 1900 and not 2000. > > So, > insert into xyz values (to_date('02-29-2000','mm-dd-yyyy')); > worked and on doing a select I got the following result. > > SQL> select to_char(trial,'mm-dd-yyyy') from xyz; > > TO_CHAR(TRIAL,'MM-DD-YYYY') > --------------------------------------------------------------------- > 02-29-2000 > > HTH, > > -- Deepak. > > >From: Victoria Lo > >Reply-To: SQR-USERS@list.iex.net > >To: Multiple recipients of list SQR-USERS > >Subject: Year 2000 > >Date: Thu, 18 Mar 1999 12:39:02 EST > > > >Hello everyone; > > > >Has anyone had problem to insert date of "29-FEB-00" into PeopleSoft > tables > >on Oracle platform? Is there any resolution? Please let me know. Thank > you. > > > >Vicki > > > > > > Get Your Private, Free Email at http://www.hotmail.com From owner-sqr-users@list.iex.net Thu Apr 8 15:31:56 1999 Date: Thu, 8 Apr 1999 13:18:46 -0700 From: Sivaram Gowkanapalli Subject: Re: How to Schedule SQR ? Hi, as far as my knowledgde goes, the process class in the PS Process scheduler panel would end up to be sqr report in either scenarios. To schedule a process/report from PS, it must have a run control Id and the scheduling is done from the Process scheduler request dialog box which props up when u try to run the report, so the process/ report must be attached to a panel. If u feel that a panel is not necssary then depending on ur operationg system u can schedule the process outside PS. like a cronjob in unix, etc., I hope this helps, siva --- Hartono Sutirman wrote: > Hi... > > I'm new in this mailing list and SQR, yet the > responses I get is great, > I get the answers from the expert very Quickly and > solved my problems. > To everyone I want to say thank you ! > > I have another problem, I've already check the > PeopleBook, but I cannot > find what I want to do. > 1. I want to schedule a report to run every month. > I've already set the > Run Occurence to Monthly. The problem is what should > I use when I define > a Process Type in the process scheduler, is it a SQR > PROCESS, SQR REPORT > or something else ?? > what the difference between the two of them ? > 2. I want to run this report automatically every > month, so in my > thinking I don't need to create a new panel group > and place it on the > peoplesoft Menu (We don't have to click on a menu to > run this process), > is it possible ?? > If we can schedule a report/process to run > automatically without asking > user to click on the menu and run button, can anyone > tell me how ?? > > any help would very appreciated > thanks in advance > > regards, > Hartono Sutirman > _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com From owner-sqr-users@list.iex.net Thu Apr 8 16:04:01 1999 Date: Thu, 8 Apr 1999 13:38:54 -0700 From: Keri Palko Subject: printing on the mainframe We have just installed PeopleSoft HRMS 7.5 with MVS/DB2 as our Database Server (DB2 v5.1). This is our first experience with SQR (v 4.3). I thought I could just run SQRs on the mainframe and they would come out formatted correctly. This didn't happen. If I use SQROUT DD SYSOUT=9 (which is a mainframe printer class for a Xerox OCE impact printer), the report comes out. The problems are 1. the first line is the garbase printer-init characters (E (0N &l0O &l8D &l88F (s16.66H &a9L &k2G). 2. There is a 1 on each of the subsequent pages where the top of page should be (like I would use if coding a COBOL report and using control characters 1-top of page, 0-double space... and then I would also use RECFM=FBA). 3. The pages are not breaking where they should be. The 1 mentioned above is never at the top of the page. This all also happens when I send the output to the mainframe line printer. When I try using SQROUT DD SYSOUT=L,DEST=U37 (which is a HP network laserjet printer that is also a VPS printer), the report never comes out. Instead a page comes out with Error Detected and the printer goes into EDRAINED status. I would be so HAPPY if someone could help me out. Thanks, Keri From owner-sqr-users@list.iex.net Thu Apr 8 15:58:07 1999 Date: Thu, 8 Apr 1999 16:40:09 -0400 From: Anthony Leung-New York Subject: Re: How do I know What Qtr of Year it is ! Quick alogrithm: 1. Get the Month 2. subtract month by 1 3. divide the result from step 2 by 3 4. get the integer part and add 1 to it. That will be your qtr. > -----Original Message----- > From: Krishnan Sarangarajan [SMTP:ksaranga@RUAN.COM] > Sent: April 08, 1999 11:53 AM > To: Multiple recipients of list SQR-USERS > Subject: Re: How do I know What Qtr of Year it is ! > > > ---------- > > From: Dominick LoGiudice[SMTP:dlogiudice@PINE.VPCC.SUNYSB.EDU] > > Sent: Thursday, April 08, 1999 1:31 PM > > To: Multiple recipients of list SQR-USERS > > Subject: How do I know What Qtr of Year it is ! > > > > I am writing an sqr that needs to look for qtr data. Example if > > I ran today the run date I have is formatted like this 19990408. I can > > substr out > > the month as 04. Using 04 I need to look on a table if the date is '01' > > or '02' or > > '03'. Based on the run date the qtrs I need to look for will always > > change. I next > > run this sqr in July so my qtr rundate will be 07 so I need to look for > > '04' , '05' or > > '06'. Is there a delivered sqr to do this. > > I guess the short story is I need to know what 3 months are the previous > > qtr. > > thanks > > > Dear Mr. Logiudice, > With regards to what period ending is tied with what quarter, if > you > have a pay calendar, you could use it ? If I am not mistaken, sometimes > the > pay calendar are preset or pre-defined and other times one may have to > define it and maintain it on an on-going basis. > > As for the pay calendar, the following is what we have at Ruan : > > SQL> desc ps_pay_calendar; > > Name Null? Type > ------------------------------- -------- ---- > COMPANY NOT NULL VARCHAR2(3) > PAYGROUP NOT NULL VARCHAR2(3) > PAY_END_DT NOT NULL DATE > RUN_ID NOT NULL VARCHAR2(3) > PAY_BEGIN_DT NOT NULL DATE > CHECK_DT NOT NULL DATE > PERIOD_WEEKS NOT NULL NUMBER(4) > CALENDAR_YEAR NOT NULL NUMBER(4) > QTRCD NOT NULL VARCHAR2(1) > MONTHCD NOT NULL VARCHAR2(2) > PAY_PERIOD NOT NULL VARCHAR2(1) > PAY_PDS_PER_YEAR NOT NULL NUMBER(4) > ACCRUAL_PCT NOT NULL NUMBER(5,3) > REVERSE_FLAG NOT NULL VARCHAR2(1) > PAY_SHEETS_RUN NOT NULL VARCHAR2(1) > PAY_PRECALC_RUN NOT NULL VARCHAR2(1) > PAY_CALC_RUN NOT NULL VARCHAR2(1) > PAY_CONFIRM_START NOT NULL VARCHAR2(1) > PAY_CONFIRM_RUN NOT NULL VARCHAR2(1) > PAY_CHECKS_RUN NOT NULL VARCHAR2(1) > PAY_OFF_CYCLE NOT NULL VARCHAR2(1) > PAY_OFF_CYCLE_CLS NOT NULL VARCHAR2(1) > GL_INTERFACE_RUN NOT NULL