From sqr-users-admin@sqrug.org Wed Jan 1 15:18:15 2003 Date: Wed, 1 Jan 2003 00:00:02 -0500 From: Nathan Stratton Treadway Subject: [sqr-users] Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Stratton Treadway, Ray Ontko & Co. (nathanst@ontko.com) $Revision: 1.14 $ $Date: 2002-11-13 12:55:07-05 $ This document gathers in one location information about the sqr-users mail list. It includes instructions for changing your subscription settings (including unsubscribing), as well are guidelines for using the list. ______________________________________________________________________ Table of Contents 1. Changes Since Previous Version 2. Introduction 2.1 sqr-users Mailing List 2.2 sqr-users Hints and Guidelines Document 2.3 Related Mailing Lists 3. Interacting with the list server 3.1 Subscribing to the list 3.2 Unsubscribing from the list 3.3 Digests 3.4 Obtaining your password 3.5 Problems 4. Posting to the sqr-users List 4.1 General Guidelines 4.2 Posting a Message 4.3 Replying to a Message 5. Job Announcements ______________________________________________________________________ 1. Changes Since Previous Version Changes since the version dated 2002/04/17: o Reflect the new location of the list and the new list server software being used. 2. Introduction 2.1. sqr-users Mailing List The sqr-users mailing list was created to enable discussion related to the SQR database language from Brio Software (and to other products in the SQR product suite). Note: SQR used to be published by SQRIBE Technologies, which was merged into Brio Technology on August 3, 1999. Brio Technology changed its name to Brio Software in October 2001. SQR and related applications have also been called "Brio.Report" and "Brio Reports". The mailing list is provided as a service of Ray Ontko & Co. A searchable archive of past postings to the mailing list is available from the SQR Users Group web site . 2.2. sqr-users Hints and Guidelines Document This document gives various hints and guidelines about the sqr-users mailing list. It will be posted approximately monthly to the mailing list so that new users can see it (and existing users can find it easily). An HTML version of the document is always available at http://www.sqrug.org/sqr-users/sqr-users-hints.html. If you have comments or suggestions about this document, please contact me directly at the address listed in the title section. This document is Copyright (C) 1999-2002 by Ray Ontko & Co. It may be freely copied and distributed provided it is not modified in any way and it retains the original copyright notice. 2.3. Related Mailing Lists Here are some mailing lists that cover topics relating to SQR in some way. sqr-jobs : SQR Jobs This list may be used for posting SQR-related resumes, recruitments, job announcements, project announcments, help- wanted, work-wanted ads, etc. To join, follow the link above, or send any message to "sqr-jobs-subscribe@yahoogroups.com". peoplesoft-fans : PeopleSoft Fans Moderated list covering technical and functional aspects of PeopleSoft. To join, follow the link above, or send any message to "peoplesoft-fans-subscribe@yahoogroups.com". psusers : PeopleSoft Users For questions related to any aspect of PeopleSoft (including PeopleSoft-specific SQR issues). This list has merged with the Peoplesoft Fans mailing list and is no longer active, but the message achive can still be found at the link above. BrioSQRReport : Brio SQR Report List for discussion of SQR and SQR Developer (formerly called Brio.Report and Report Builder). To join, follow the link above, or send any message to "BrioSQRReport- subscribe@yahoogroups.com". rm-users : Report- Mart Users List for discussion of Brio Portal (previously called ReportMart). To join, follow the link above, or send a message containing the word "subscribe" in the body to "rm-users- request@sqrug.org". brioportalusersgroup : Brio Portal Users Group list List for discussion of Brio Portal. To join, follow the link above, or send any message to "brioportalusersgroup- subscribe@yahoogroups.com". brio-l : OpenITx Brio List List for technical and functional discussion of all Brio products (but most messages are currently about Brio Intelligence). To join, follow the link above. briousers : Brio.Enter- prise Users List for discussion of the Brio.Enterprise (now called Intelligence) product. To join, follow the link above, or send any message to "briousers-subscribe@yahoogroups.com". briolist : Brio List List for discussion of Brio Intelligence. To join, follow the link above, or send any message to "briolist- subscribe@yahoogroups.com". 3. Interacting with the list server The sqr-users mailing list is hosted using the MailMan list server package. All of the MailMan functions described below are accessed from the sqr-users List Info page, "http://www.sqrug.org/mailman/listinfo/sqr-users". 3.1. Subscribing to the list To subscribe to the mailing list, enter your e-mail address in the box at the top of the "Subscribing to sqr-users" section on the sqr-users List Info page, then choose a password to assign to your subscription and enter it in both "Password" boxes. This password is used to allow you to securely make changes to your subscription over the web. This password will be e-mailed back to you at varous times, so you should not use the same password as you are using elsewhere. Once you have filled in the password, click the "Subscribe" button. You should soon receive an e-mail message from the list server requesting confirmation of your subscription request. Once you have replied to this message and your subscription has been processed, you will receive a "welcome" message giving you more information on the list server, and as well as a reminder of the subscription password you chose. 3.2. Unsubscribing from the list To unsubscribe from the list, go to the sqr-users List Info page, enter your e-mail address in the "sqr-users Subscribers" section, and click the "Edit Options" button. In the page that comes up, there will be an "Unsubscribing from sqr-users" section; simply enter your password in the box there and click the "Unsubscribe" button. Assuming you enter the correct password, you will recieve a message stating "You have been unsubscribed." and the removal will take effect immediately (there is no need to send in a confirmation e-mail message). (If you have forgotton your password, see the instructions below.) 3.3. Digests You can request to get your messages in digest form (one large message containing all of the posts made each day). To do this, enter your address in the "sqr-users Subscribers" section of the sqr-users List Info page and click the "Edit Options" button. At the bottom of the resulting page, you will see a section titled "Your sqr-users Subscription Options". Change the "Set Digest Mode" option to "On", enter your subscription password in the box provided, and click the "Submit My Changes" button. (If you have forgotton your password, see the instructions below.) You actually have a choice of two different digest formats, "MIME" or "Plain Text". The MIME format sends each individual list message as a separate MIME attachment to the digest; the plain text format consists of one large text message, with all of the individual messages run together. Which format you will want to use depends on your mail- reading software (and personal preference). If you are not sure which format will work best, you can just leave the default setting of "MIME" and see if the next morning's digest is easy to read in your mail reader -- you can change to the other format at any time using that same web page. If you use digests, you should not just "reply" to the digest mailing. Instead, be sure your message's subject header matches the subject in the original posting to which you are replying, and that you don't include a quoted copy of the entire digest in your post. To restore normal distribution, simply follow the above instructions choosing the "Off" setting for the Digest option. 3.4. Obtaining your password You will not be able to unsubscribe or change your subscription options without your password. If you have forgotten your password, simply click the "Email My Password to Me" button on the Edit Options page and your password will be e-mailed to you. You will also be e- mailed a copy of your password at the beginning of each month, as part of a "mailing list memberships reminder" message from the MailMan software. 3.5. Problems If the above instructions do not work, please contact the list manager at "sqr-users-manager@sqrug.org". (Do not send a message to the list itself -- no one there can help you with your problem!) Please include a description of what you were trying to do and what exactly happened when you made the attempt. 4. Posting to the sqr-users List In order to post to the mailing list, you must be subscribed to it (see directions above). 4.1. General Guidelines For any message you send to the list, please: o make sure your mail software is not requesting a "Return Receipt". (If a "Return Receipt Requested" message is sent to the list, several dozen receipt messages will be sent out to the mailing list in reply from other subscribers.) o do not send messages in HTML or other special formats. (These are not usable by many subscribers, especially those who receive their messages in digest format.) The list server will delete these special attachments from your message and send you a warning notice. o keep in mind that over 500 people will receive your message, and that it will appear in the list archives. Try to keep your messages on-topic and relatively short. Consider sending your message directly to particular people if it won't interest many of the list's subscribers. o ignore abusive and inappropriate messages that get sent to the list, rather than getting drawn into a debate. The very nature of these messages will cause most subscribers to automatically discredit them -- no "response" is necessary. To the contrary, any response will only "add fule to the fire," prolonging the controversy and further interupting the normal conversation on the list. 4.2. Posting a Message Once you are subscribed, you can post to the list by sending a message to "sqr-users@sqrug.org". Be sure your subject heading describes your specific problem or question (i.e. don't just use "SQR Question"). In the body of your message, please include the following information: o Brio product and version number (see notes below) o Operating system and version number o Database name and version number o Application name (i.e. PeopleSoft) and a version number, if any Also, include a short code sample whenever possible. It's much easier for other people to understand what you are trying to do when they can see the actual code. (At the same time, be careful not to post confidential or proprietary code, since the posting will be placed in a public web archive.) Note that your question should relate to SQR or other Brio Report product in some way. Don't ask questions about PeopleSoft, for example, unless you are having SQR-related issues. (For PeopleSoft- specific questions, see other lists mentioned in the ``Related Mailing Lists'' section.) In 2001, PeopleSoft purchased from Brio the rights to the source code for SQR and began releasing its own line of SQR. PeopleSoft's SQR version numbering is different from Brio's, so it's very important to mention whether you are using Brio's "SQR" or "SQR for PeopleSoft". (This info is included automatically if you include the full SQR version-id string as described below.) To get the version-id string for your copy of SQR, you can run the following program: begin-program show $sqr-ver end-program This will produce a line that looks something like this: SQR/3.0.15/Intel/SCO Unix R3.2 V5.0/Oracle 7.2.2.3/Sep 25 1996 Include the full line of output in your message. Note that the oper- ating system version number listed here is the one under which this copy of SQR was compiled, so you'll still need to include this infor- mation for your own environment. In a command-line environment (Unix, etc.), you can also get this version-id string by saying sqr -id 4.3. Replying to a Message Messages sent out on the list have a Reply-To header pointing to the list. This means that you should be able to simply use your mail reader's "reply" function to send a reply to the list. Generally, all answers to questions sent out on the list should be public replies, so that other subscribers can benefit. In those cases where a private reply is appropriate, be sure to over-ride the default Reply-To address. If your message is not actually a reply to the earlier message, please do not use the "reply" function. Instead, address a new e-mail message to the list as described in the previous section. (This is because the list archives will incorrectly show the new message in the original thread if you use "reply".) You should quote enough lines from the original message that later readers can understand your answer even if they missed the original question. At the same time, you should remove any unnecessary lines from the quote in order to make your message shorter and easier to understand. (Remember that some people read the mailing list in digest form, and the digests become much larger if unnecessary lines are quoted.) 5. Job Announcements Please do not post job announcements on the sqr-users list, but use the sqr-jobs list instead. (See ``Related Mailing Lists'' for more information on this list.) A large number of sqr-users subscribers do not want to receive job announcements (for various reasons) and such posts tend to cause a lot of disruption of the discussion on the list. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Wed Jan 1 15:45:05 2003 From: Nathan Stratton Treadway Date: Wed, 1 Jan 2003 15:42:40 -0500 Subject: [sqr-users] Re: Globals in Local Procedures On Tue, Dec 31, 2002 at 05:40:10PM -0800, Alexander, Steve wrote: > I know I can refer to global variables within a local procedure by using > #_var and $_var. It also seems possible to refer to database columns as > &_ALIAS.FIELD. Is it possible to refer to arrays? Actually, arrays are _always_ global -- so you just refer to them by their name (with no "_") in either local or global contexts. (Unfortunately, this also means that you can't create a local array, so there is no way to "protect" an array from use elsewhere in your program....) Nathan ---------------------------------------------------------------------------- Nathan Stratton Treadway | nathanst@ontko.com | Mid-Atlantic region Software consulting services | Ray Ontko & Co. | http://www.ontko.com/ _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 11:52:25 2003 Date: Thu, 2 Jan 2003 11:51:44 -0500 From: "Braswell, Michael" Subject: [sqr-users] Tuning for inserts after each field selected Hello, We are processing records from a data file and inserting them into PS payroll. The data file is arranged in a fashion similar to the following: "emplid","hoursforearningscode1","hoursforearningscode2","hoursforearnin gscode3".... (there are 18 earning codes!) so data looks like 0123,8,4,5,... 0234,7,3,6,... We have to do validation on each earnings code and then insert into the pay_others table (which is setup as having one earnings code per record). So the file does not really match the database structure but we are trying not to change the datafile. Please look at the following piece code and you will see that after we select for instance "hoursforearningscode1" we do an insert into pay_others same thing for all the other earning codes. So we may do a total of 18 inserts for one select if the person has hours in each earnings code. I've only included a piece of it but the code continues in the same fashion for all 18 earning codes. Typically the SQR's I've written have the file so that you do not have to do inserts after selecting a particular field. Rather you would select the emplid and earnings code and do an insert. Does anyone know if the way we are having to write the SQR (below) is causing it to run in 3-4 hours? Has anyone written a program using the inserts after each field that performed efficiently. We are just going over our options and wondering if this is causing the problem and maybe we should change the file layout. The procedures called below for validation run instantly so we have eliminated this as the bottleneck. Thanks in advance for any help. Begin-Select TP.EMPLID TP.NEX_LEGAL_ENTITY TP.NAME PD.NAME JOB.EMPL_STATUS JOB.FULL_PART_TIME JOB.LOCATION let $EMPLID = &TP.EMPLID let $NAME = &TP.NAME let $LEGAL_ENTITY = &TP.NEX_LEGAL_ENTITY let $FULL_PART_TIME = &JOB.FULL_PART_TIME let $EMPL_STATUS = &JOB.EMPL_STATUS let $NAME_IN_PS = &PD.NAME let $LOCATION = &JOB.LOCATION let $message = '' let #Errors-This-Row = 0 let $flags = ' ' if $EMPL_STATUS = 'T' let $flags = 'T' else let $flags = ' ' end-if add 1 to #Total-Records-Read ! let #Row = Edit(#Total-Records-Read,'99999') ! Validate values if #Errors-This-Row = 0 do Validate-Payline end-if if #Errors-This-Row >= 1 add 1 to #Total-Error-Rows add 1 to #array_count Put $Legal_entity $location $emplid $Name_in_ps $Name ' ' 0 $Message Into Errors(#array_count) else Do Pay-Ern-Update end-if TP.NEX_HRS_REG Let $erncd = 'REG' Let #Hours_in = &TP.NEX_HRS_REG let #O_amount = round(#Hours_in, 2) #debug show 'amount= ' #O_amount Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'F' !full/part time enhancement validation 9/25/02 do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SF1 Let $erncd = 'SF1' Let #Hours_in = &TP.NEX_HRS_SF1 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SD0 Let $erncd = 'SDO' Let #Hours_in = &TP.NEX_HRS_SD0 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 12:38:06 2003 From: "Alexander, Steve" Subject: RE: [sqr-users] Tuning for inserts after each field selected Date: Thu, 2 Jan 2003 09:38:15 -0800 You've left out the from and where clause of your selection, which may be the place most open to improvement. There's not much you can improve with inserts. One thing I'd suggest is to not join PS_PERSONAL_DATA just to pick up the "name in Peoplesoft". Is it so important to have that in addition to the name in your input table? If you absolutely need it in the error reporting, and you don't have many errors, call a subroutine to select PS_PERSONAL_DATA for just the employees with errors. Another improvement is to say "begin-select -b1000". That will buffer 1000 rows at a time rather than the default of 10. Also, are you using "on-error" with your insert? If so, you may be getting errors (i.e. duplicate rows), which take much longer to process than successful inserts. -----Original Message----- From: Braswell, Michael [mailto:Michael.Braswell@usi.net] Sent: Thursday, January 02, 2003 8:52 AM To: sqr-users@sqrug.org Subject: [sqr-users] Tuning for inserts after each field selected Hello, We are processing records from a data file and inserting them into PS payroll. The data file is arranged in a fashion similar to the following: "emplid","hoursforearningscode1","hoursforearningscode2","hoursforearnin gscode3".... (there are 18 earning codes!) so data looks like 0123,8,4,5,... 0234,7,3,6,... We have to do validation on each earnings code and then insert into the pay_others table (which is setup as having one earnings code per record). So the file does not really match the database structure but we are trying not to change the datafile. Please look at the following piece code and you will see that after we select for instance "hoursforearningscode1" we do an insert into pay_others same thing for all the other earning codes. So we may do a total of 18 inserts for one select if the person has hours in each earnings code. I've only included a piece of it but the code continues in the same fashion for all 18 earning codes. Typically the SQR's I've written have the file so that you do not have to do inserts after selecting a particular field. Rather you would select the emplid and earnings code and do an insert. Does anyone know if the way we are having to write the SQR (below) is causing it to run in 3-4 hours? Has anyone written a program using the inserts after each field that performed efficiently. We are just going over our options and wondering if this is causing the problem and maybe we should change the file layout. The procedures called below for validation run instantly so we have eliminated this as the bottleneck. Thanks in advance for any help. Begin-Select TP.EMPLID TP.NEX_LEGAL_ENTITY TP.NAME PD.NAME JOB.EMPL_STATUS JOB.FULL_PART_TIME JOB.LOCATION let $EMPLID = &TP.EMPLID let $NAME = &TP.NAME let $LEGAL_ENTITY = &TP.NEX_LEGAL_ENTITY let $FULL_PART_TIME = &JOB.FULL_PART_TIME let $EMPL_STATUS = &JOB.EMPL_STATUS let $NAME_IN_PS = &PD.NAME let $LOCATION = &JOB.LOCATION let $message = '' let #Errors-This-Row = 0 let $flags = ' ' if $EMPL_STATUS = 'T' let $flags = 'T' else let $flags = ' ' end-if add 1 to #Total-Records-Read ! let #Row = Edit(#Total-Records-Read,'99999') ! Validate values if #Errors-This-Row = 0 do Validate-Payline end-if if #Errors-This-Row >= 1 add 1 to #Total-Error-Rows add 1 to #array_count Put $Legal_entity $location $emplid $Name_in_ps $Name ' ' 0 $Message Into Errors(#array_count) else Do Pay-Ern-Update end-if TP.NEX_HRS_REG Let $erncd = 'REG' Let #Hours_in = &TP.NEX_HRS_REG let #O_amount = round(#Hours_in, 2) #debug show 'amount= ' #O_amount Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'F' !full/part time enhancement validation 9/25/02 do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SF1 Let $erncd = 'SF1' Let #Hours_in = &TP.NEX_HRS_SF1 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SD0 Let $erncd = 'SDO' Let #Hours_in = &TP.NEX_HRS_SD0 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 12:49:07 2003 Subject: RE: [sqr-users] Tuning for inserts after each field selected Date: Thu, 2 Jan 2003 12:48:35 -0500 From: "Braswell, Michael" Hi, Below is the from and where clause, sorry about that. I can double check and see if they can live without the name from Peoplesoft in the report. I suppose they just wanted to do a visual validation at this time. I suppose my concern was how we do an insert after each field selected. Usually I would select all fields then do an insert. I thought because this is not the way it's normally done it may cause SQR to perform poorly. If anyone had run into this before I would suggest that we change the data file and go from there. The b-1000 is a good suggestion. In fact I learned about this a couple weeks ago and tried it, unfortunately no significant improvement was made in the completion time. I do use an on-error routine, in which I write out the errors to a report if anything happens. We are not getting any errors like this but I will double check the report. Here is the from and where clause. Thanks. >From PS_NEX_TM_LOAD_TMP TP, PS_JOB JOB, PS_PERSONAL_DATA PD where JOB.EMPLID = PD.EMPLID and PD.EMPLID = TP.Emplid and JOB.EMPL_RCD# = 0 and JOB.EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = JOB.EMPLID and EMPL_RCD# = JOB.EMPL_RCD# and EFFDT <= $Pay_End_Dt) and JOB.EFFSEQ = (select max(EFFSEQ) from PS_JOB where EMPLID = JOB.EMPLID and EMPL_RCD# = JOB.EMPL_RCD# and EFFDT = JOB.EFFDT) order by TP.NEX_LEGAL_ENTITY, JOB.LOCATION,TP.EMPLID -----Original Message----- From: Alexander, Steve [mailto:Steve.Alexander@ci.sj.ca.us] Sent: Thursday, January 02, 2003 12:38 PM To: 'sqr-users@sqrug.org' Subject: RE: [sqr-users] Tuning for inserts after each field selected You've left out the from and where clause of your selection, which may be the place most open to improvement. There's not much you can improve with inserts. One thing I'd suggest is to not join PS_PERSONAL_DATA just to pick up the "name in Peoplesoft". Is it so important to have that in addition to the name in your input table? If you absolutely need it in the error reporting, and you don't have many errors, call a subroutine to select PS_PERSONAL_DATA for just the employees with errors. Another improvement is to say "begin-select -b1000". That will buffer 1000 rows at a time rather than the default of 10. Also, are you using "on-error" with your insert? If so, you may be getting errors (i.e. duplicate rows), which take much longer to process than successful inserts. -----Original Message----- From: Braswell, Michael [mailto:Michael.Braswell@usi.net] Sent: Thursday, January 02, 2003 8:52 AM To: sqr-users@sqrug.org Subject: [sqr-users] Tuning for inserts after each field selected Hello, We are processing records from a data file and inserting them into PS payroll. The data file is arranged in a fashion similar to the following: "emplid","hoursforearningscode1","hoursforearningscode2","hoursforearnin gscode3".... (there are 18 earning codes!) so data looks like 0123,8,4,5,... 0234,7,3,6,... We have to do validation on each earnings code and then insert into the pay_others table (which is setup as having one earnings code per record). So the file does not really match the database structure but we are trying not to change the datafile. Please look at the following piece code and you will see that after we select for instance "hoursforearningscode1" we do an insert into pay_others same thing for all the other earning codes. So we may do a total of 18 inserts for one select if the person has hours in each earnings code. I've only included a piece of it but the code continues in the same fashion for all 18 earning codes. Typically the SQR's I've written have the file so that you do not have to do inserts after selecting a particular field. Rather you would select the emplid and earnings code and do an insert. Does anyone know if the way we are having to write the SQR (below) is causing it to run in 3-4 hours? Has anyone written a program using the inserts after each field that performed efficiently. We are just going over our options and wondering if this is causing the problem and maybe we should change the file layout. The procedures called below for validation run instantly so we have eliminated this as the bottleneck. Thanks in advance for any help. Begin-Select TP.EMPLID TP.NEX_LEGAL_ENTITY TP.NAME PD.NAME JOB.EMPL_STATUS JOB.FULL_PART_TIME JOB.LOCATION let $EMPLID = &TP.EMPLID let $NAME = &TP.NAME let $LEGAL_ENTITY = &TP.NEX_LEGAL_ENTITY let $FULL_PART_TIME = &JOB.FULL_PART_TIME let $EMPL_STATUS = &JOB.EMPL_STATUS let $NAME_IN_PS = &PD.NAME let $LOCATION = &JOB.LOCATION let $message = '' let #Errors-This-Row = 0 let $flags = ' ' if $EMPL_STATUS = 'T' let $flags = 'T' else let $flags = ' ' end-if add 1 to #Total-Records-Read ! let #Row = Edit(#Total-Records-Read,'99999') ! Validate values if #Errors-This-Row = 0 do Validate-Payline end-if if #Errors-This-Row >= 1 add 1 to #Total-Error-Rows add 1 to #array_count Put $Legal_entity $location $emplid $Name_in_ps $Name ' ' 0 $Message Into Errors(#array_count) else Do Pay-Ern-Update end-if TP.NEX_HRS_REG Let $erncd = 'REG' Let #Hours_in = &TP.NEX_HRS_REG let #O_amount = round(#Hours_in, 2) #debug show 'amount= ' #O_amount Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'F' !full/part time enhancement validation 9/25/02 do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SF1 Let $erncd = 'SF1' Let #Hours_in = &TP.NEX_HRS_SF1 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SD0 Let $erncd = 'SDO' Let #Hours_in = &TP.NEX_HRS_SD0 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 12:55:13 2003 Subject: RE: [sqr-users] Tuning for inserts after each field selected Date: Thu, 2 Jan 2003 11:53:56 -0600 From: "Knapp, Richard" If it's all inserts and no updates and you can run this during off hours, you may want to drop the indexes first, do your inserts, then rebuild the indexes. Bound to save you time. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: Braswell, Michael [mailto:Michael.Braswell@usi.net] Sent: Thursday, January 02, 2003 11:49 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Tuning for inserts after each field selected Hi, Below is the from and where clause, sorry about that. I can double check and see if they can live without the name from Peoplesoft in the report. I suppose they just wanted to do a visual validation at this time. I suppose my concern was how we do an insert after each field selected. Usually I would select all fields then do an insert. I thought because this is not the way it's normally done it may cause SQR to perform poorly. If anyone had run into this before I would suggest that we change the data file and go from there. The b-1000 is a good suggestion. In fact I learned about this a couple weeks ago and tried it, unfortunately no significant improvement was made in the completion time. I do use an on-error routine, in which I write out the errors to a report if anything happens. We are not getting any errors like this but I will double check the report. Here is the from and where clause. Thanks. >From PS_NEX_TM_LOAD_TMP TP, PS_JOB JOB, PS_PERSONAL_DATA PD where JOB.EMPLID = PD.EMPLID and PD.EMPLID = TP.Emplid and JOB.EMPL_RCD# = 0 and JOB.EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = JOB.EMPLID and EMPL_RCD# = JOB.EMPL_RCD# and EFFDT <= $Pay_End_Dt) and JOB.EFFSEQ = (select max(EFFSEQ) from PS_JOB where EMPLID = JOB.EMPLID and EMPL_RCD# = JOB.EMPL_RCD# and EFFDT = JOB.EFFDT) order by TP.NEX_LEGAL_ENTITY, JOB.LOCATION,TP.EMPLID -----Original Message----- From: Alexander, Steve [mailto:Steve.Alexander@ci.sj.ca.us] Sent: Thursday, January 02, 2003 12:38 PM To: 'sqr-users@sqrug.org' Subject: RE: [sqr-users] Tuning for inserts after each field selected You've left out the from and where clause of your selection, which may be the place most open to improvement. There's not much you can improve with inserts. One thing I'd suggest is to not join PS_PERSONAL_DATA just to pick up the "name in Peoplesoft". Is it so important to have that in addition to the name in your input table? If you absolutely need it in the error reporting, and you don't have many errors, call a subroutine to select PS_PERSONAL_DATA for just the employees with errors. Another improvement is to say "begin-select -b1000". That will buffer 1000 rows at a time rather than the default of 10. Also, are you using "on-error" with your insert? If so, you may be getting errors (i.e. duplicate rows), which take much longer to process than successful inserts. -----Original Message----- From: Braswell, Michael [mailto:Michael.Braswell@usi.net] Sent: Thursday, January 02, 2003 8:52 AM To: sqr-users@sqrug.org Subject: [sqr-users] Tuning for inserts after each field selected Hello, We are processing records from a data file and inserting them into PS payroll. The data file is arranged in a fashion similar to the following: "emplid","hoursforearningscode1","hoursforearningscode2","hoursforearnin gscode3".... (there are 18 earning codes!) so data looks like 0123,8,4,5,... 0234,7,3,6,... We have to do validation on each earnings code and then insert into the pay_others table (which is setup as having one earnings code per record). So the file does not really match the database structure but we are trying not to change the datafile. Please look at the following piece code and you will see that after we select for instance "hoursforearningscode1" we do an insert into pay_others same thing for all the other earning codes. So we may do a total of 18 inserts for one select if the person has hours in each earnings code. I've only included a piece of it but the code continues in the same fashion for all 18 earning codes. Typically the SQR's I've written have the file so that you do not have to do inserts after selecting a particular field. Rather you would select the emplid and earnings code and do an insert. Does anyone know if the way we are having to write the SQR (below) is causing it to run in 3-4 hours? Has anyone written a program using the inserts after each field that performed efficiently. We are just going over our options and wondering if this is causing the problem and maybe we should change the file layout. The procedures called below for validation run instantly so we have eliminated this as the bottleneck. Thanks in advance for any help. Begin-Select TP.EMPLID TP.NEX_LEGAL_ENTITY TP.NAME PD.NAME JOB.EMPL_STATUS JOB.FULL_PART_TIME JOB.LOCATION let $EMPLID = &TP.EMPLID let $NAME = &TP.NAME let $LEGAL_ENTITY = &TP.NEX_LEGAL_ENTITY let $FULL_PART_TIME = &JOB.FULL_PART_TIME let $EMPL_STATUS = &JOB.EMPL_STATUS let $NAME_IN_PS = &PD.NAME let $LOCATION = &JOB.LOCATION let $message = '' let #Errors-This-Row = 0 let $flags = ' ' if $EMPL_STATUS = 'T' let $flags = 'T' else let $flags = ' ' end-if add 1 to #Total-Records-Read ! let #Row = Edit(#Total-Records-Read,'99999') ! Validate values if #Errors-This-Row = 0 do Validate-Payline end-if if #Errors-This-Row >= 1 add 1 to #Total-Error-Rows add 1 to #array_count Put $Legal_entity $location $emplid $Name_in_ps $Name ' ' 0 $Message Into Errors(#array_count) else Do Pay-Ern-Update end-if TP.NEX_HRS_REG Let $erncd = 'REG' Let #Hours_in = &TP.NEX_HRS_REG let #O_amount = round(#Hours_in, 2) #debug show 'amount= ' #O_amount Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'F' !full/part time enhancement validation 9/25/02 do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SF1 Let $erncd = 'SF1' Let #Hours_in = &TP.NEX_HRS_SF1 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SD0 Let $erncd = 'SDO' Let #Hours_in = &TP.NEX_HRS_SD0 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 13:31:07 2003 From: "Alexander, Steve" Subject: RE: [sqr-users] Tuning for inserts after each field selected Date: Thu, 2 Jan 2003 10:31:22 -0800 That's "-b1000", not "b-1000". What do you mean by "an insert after each field selected"? The selection returns your entire row at once. You may be doing an insert for each field, but you'd be doing the same number of inserts even if your source table were normalized and had one row for each ERNCD and OTH_HOURS. Another thought - I'm familiar with the PS_PAY_OTH_EARNS table. How are you getting the keys other than ERNCD (COMPANY, PAYGROUP, PAY_END_DT, OFF_CYCLE, PAGE_NUM, and LINE_NUM, ADDL_NUM, ERNCD, and SEPCHK)? Are you do other selections, one for each insert? That will really slow you down. -----Original Message----- From: Knapp, Richard [mailto:KnappR@umsystem.edu] Sent: Thursday, January 02, 2003 9:54 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Tuning for inserts after each field selected If it's all inserts and no updates and you can run this during off hours, you may want to drop the indexes first, do your inserts, then rebuild the indexes. Bound to save you time. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: Braswell, Michael [mailto:Michael.Braswell@usi.net] Sent: Thursday, January 02, 2003 11:49 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Tuning for inserts after each field selected Hi, Below is the from and where clause, sorry about that. I can double check and see if they can live without the name from Peoplesoft in the report. I suppose they just wanted to do a visual validation at this time. I suppose my concern was how we do an insert after each field selected. Usually I would select all fields then do an insert. I thought because this is not the way it's normally done it may cause SQR to perform poorly. If anyone had run into this before I would suggest that we change the data file and go from there. The b-1000 is a good suggestion. In fact I learned about this a couple weeks ago and tried it, unfortunately no significant improvement was made in the completion time. I do use an on-error routine, in which I write out the errors to a report if anything happens. We are not getting any errors like this but I will double check the report. Here is the from and where clause. Thanks. >From PS_NEX_TM_LOAD_TMP TP, PS_JOB JOB, PS_PERSONAL_DATA PD where JOB.EMPLID = PD.EMPLID and PD.EMPLID = TP.Emplid and JOB.EMPL_RCD# = 0 and JOB.EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = JOB.EMPLID and EMPL_RCD# = JOB.EMPL_RCD# and EFFDT <= $Pay_End_Dt) and JOB.EFFSEQ = (select max(EFFSEQ) from PS_JOB where EMPLID = JOB.EMPLID and EMPL_RCD# = JOB.EMPL_RCD# and EFFDT = JOB.EFFDT) order by TP.NEX_LEGAL_ENTITY, JOB.LOCATION,TP.EMPLID -----Original Message----- From: Alexander, Steve [mailto:Steve.Alexander@ci.sj.ca.us] Sent: Thursday, January 02, 2003 12:38 PM To: 'sqr-users@sqrug.org' Subject: RE: [sqr-users] Tuning for inserts after each field selected You've left out the from and where clause of your selection, which may be the place most open to improvement. There's not much you can improve with inserts. One thing I'd suggest is to not join PS_PERSONAL_DATA just to pick up the "name in Peoplesoft". Is it so important to have that in addition to the name in your input table? If you absolutely need it in the error reporting, and you don't have many errors, call a subroutine to select PS_PERSONAL_DATA for just the employees with errors. Another improvement is to say "begin-select -b1000". That will buffer 1000 rows at a time rather than the default of 10. Also, are you using "on-error" with your insert? If so, you may be getting errors (i.e. duplicate rows), which take much longer to process than successful inserts. -----Original Message----- From: Braswell, Michael [mailto:Michael.Braswell@usi.net] Sent: Thursday, January 02, 2003 8:52 AM To: sqr-users@sqrug.org Subject: [sqr-users] Tuning for inserts after each field selected Hello, We are processing records from a data file and inserting them into PS payroll. The data file is arranged in a fashion similar to the following: "emplid","hoursforearningscode1","hoursforearningscode2","hoursforearnin gscode3".... (there are 18 earning codes!) so data looks like 0123,8,4,5,... 0234,7,3,6,... We have to do validation on each earnings code and then insert into the pay_others table (which is setup as having one earnings code per record). So the file does not really match the database structure but we are trying not to change the datafile. Please look at the following piece code and you will see that after we select for instance "hoursforearningscode1" we do an insert into pay_others same thing for all the other earning codes. So we may do a total of 18 inserts for one select if the person has hours in each earnings code. I've only included a piece of it but the code continues in the same fashion for all 18 earning codes. Typically the SQR's I've written have the file so that you do not have to do inserts after selecting a particular field. Rather you would select the emplid and earnings code and do an insert. Does anyone know if the way we are having to write the SQR (below) is causing it to run in 3-4 hours? Has anyone written a program using the inserts after each field that performed efficiently. We are just going over our options and wondering if this is causing the problem and maybe we should change the file layout. The procedures called below for validation run instantly so we have eliminated this as the bottleneck. Thanks in advance for any help. Begin-Select TP.EMPLID TP.NEX_LEGAL_ENTITY TP.NAME PD.NAME JOB.EMPL_STATUS JOB.FULL_PART_TIME JOB.LOCATION let $EMPLID = &TP.EMPLID let $NAME = &TP.NAME let $LEGAL_ENTITY = &TP.NEX_LEGAL_ENTITY let $FULL_PART_TIME = &JOB.FULL_PART_TIME let $EMPL_STATUS = &JOB.EMPL_STATUS let $NAME_IN_PS = &PD.NAME let $LOCATION = &JOB.LOCATION let $message = '' let #Errors-This-Row = 0 let $flags = ' ' if $EMPL_STATUS = 'T' let $flags = 'T' else let $flags = ' ' end-if add 1 to #Total-Records-Read ! let #Row = Edit(#Total-Records-Read,'99999') ! Validate values if #Errors-This-Row = 0 do Validate-Payline end-if if #Errors-This-Row >= 1 add 1 to #Total-Error-Rows add 1 to #array_count Put $Legal_entity $location $emplid $Name_in_ps $Name ' ' 0 $Message Into Errors(#array_count) else Do Pay-Ern-Update end-if TP.NEX_HRS_REG Let $erncd = 'REG' Let #Hours_in = &TP.NEX_HRS_REG let #O_amount = round(#Hours_in, 2) #debug show 'amount= ' #O_amount Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'F' !full/part time enhancement validation 9/25/02 do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SF1 Let $erncd = 'SF1' Let #Hours_in = &TP.NEX_HRS_SF1 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SD0 Let $erncd = 'SDO' Let #Hours_in = &TP.NEX_HRS_SD0 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 13:33:05 2003 Subject: RE: [sqr-users] Tuning for inserts after each field selected Date: Thu, 2 Jan 2003 13:32:55 -0500 From: "Braswell, Michael" Yes, that is true. We are only inserting around 5000 records however and we have other programs inserting similar quantities of data into this table with no real time issues. Because of this I was thinking the issue was possibly more centered around the layout of the data file and having to do processing after each field vs doing processing after the select has been run. thanks Michael Braswell -----Original Message----- From: Knapp, Richard [mailto:KnappR@umsystem.edu] Sent: Thursday, January 02, 2003 12:54 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Tuning for inserts after each field selected If it's all inserts and no updates and you can run this during off hours, you may want to drop the indexes first, do your inserts, then rebuild the indexes. Bound to save you time. Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: Braswell, Michael [mailto:Michael.Braswell@usi.net] Sent: Thursday, January 02, 2003 11:49 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Tuning for inserts after each field selected Hi, Below is the from and where clause, sorry about that. I can double check and see if they can live without the name from Peoplesoft in the report. I suppose they just wanted to do a visual validation at this time. I suppose my concern was how we do an insert after each field selected. Usually I would select all fields then do an insert. I thought because this is not the way it's normally done it may cause SQR to perform poorly. If anyone had run into this before I would suggest that we change the data file and go from there. The b-1000 is a good suggestion. In fact I learned about this a couple weeks ago and tried it, unfortunately no significant improvement was made in the completion time. I do use an on-error routine, in which I write out the errors to a report if anything happens. We are not getting any errors like this but I will double check the report. Here is the from and where clause. Thanks. >From PS_NEX_TM_LOAD_TMP TP, PS_JOB JOB, PS_PERSONAL_DATA PD where JOB.EMPLID = PD.EMPLID and PD.EMPLID = TP.Emplid and JOB.EMPL_RCD# = 0 and JOB.EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = JOB.EMPLID and EMPL_RCD# = JOB.EMPL_RCD# and EFFDT <= $Pay_End_Dt) and JOB.EFFSEQ = (select max(EFFSEQ) from PS_JOB where EMPLID = JOB.EMPLID and EMPL_RCD# = JOB.EMPL_RCD# and EFFDT = JOB.EFFDT) order by TP.NEX_LEGAL_ENTITY, JOB.LOCATION,TP.EMPLID -----Original Message----- From: Alexander, Steve [mailto:Steve.Alexander@ci.sj.ca.us] Sent: Thursday, January 02, 2003 12:38 PM To: 'sqr-users@sqrug.org' Subject: RE: [sqr-users] Tuning for inserts after each field selected You've left out the from and where clause of your selection, which may be the place most open to improvement. There's not much you can improve with inserts. One thing I'd suggest is to not join PS_PERSONAL_DATA just to pick up the "name in Peoplesoft". Is it so important to have that in addition to the name in your input table? If you absolutely need it in the error reporting, and you don't have many errors, call a subroutine to select PS_PERSONAL_DATA for just the employees with errors. Another improvement is to say "begin-select -b1000". That will buffer 1000 rows at a time rather than the default of 10. Also, are you using "on-error" with your insert? If so, you may be getting errors (i.e. duplicate rows), which take much longer to process than successful inserts. -----Original Message----- From: Braswell, Michael [mailto:Michael.Braswell@usi.net] Sent: Thursday, January 02, 2003 8:52 AM To: sqr-users@sqrug.org Subject: [sqr-users] Tuning for inserts after each field selected Hello, We are processing records from a data file and inserting them into PS payroll. The data file is arranged in a fashion similar to the following: "emplid","hoursforearningscode1","hoursforearningscode2","hoursforearnin gscode3".... (there are 18 earning codes!) so data looks like 0123,8,4,5,... 0234,7,3,6,... We have to do validation on each earnings code and then insert into the pay_others table (which is setup as having one earnings code per record). So the file does not really match the database structure but we are trying not to change the datafile. Please look at the following piece code and you will see that after we select for instance "hoursforearningscode1" we do an insert into pay_others same thing for all the other earning codes. So we may do a total of 18 inserts for one select if the person has hours in each earnings code. I've only included a piece of it but the code continues in the same fashion for all 18 earning codes. Typically the SQR's I've written have the file so that you do not have to do inserts after selecting a particular field. Rather you would select the emplid and earnings code and do an insert. Does anyone know if the way we are having to write the SQR (below) is causing it to run in 3-4 hours? Has anyone written a program using the inserts after each field that performed efficiently. We are just going over our options and wondering if this is causing the problem and maybe we should change the file layout. The procedures called below for validation run instantly so we have eliminated this as the bottleneck. Thanks in advance for any help. Begin-Select TP.EMPLID TP.NEX_LEGAL_ENTITY TP.NAME PD.NAME JOB.EMPL_STATUS JOB.FULL_PART_TIME JOB.LOCATION let $EMPLID = &TP.EMPLID let $NAME = &TP.NAME let $LEGAL_ENTITY = &TP.NEX_LEGAL_ENTITY let $FULL_PART_TIME = &JOB.FULL_PART_TIME let $EMPL_STATUS = &JOB.EMPL_STATUS let $NAME_IN_PS = &PD.NAME let $LOCATION = &JOB.LOCATION let $message = '' let #Errors-This-Row = 0 let $flags = ' ' if $EMPL_STATUS = 'T' let $flags = 'T' else let $flags = ' ' end-if add 1 to #Total-Records-Read ! let #Row = Edit(#Total-Records-Read,'99999') ! Validate values if #Errors-This-Row = 0 do Validate-Payline end-if if #Errors-This-Row >= 1 add 1 to #Total-Error-Rows add 1 to #array_count Put $Legal_entity $location $emplid $Name_in_ps $Name ' ' 0 $Message Into Errors(#array_count) else Do Pay-Ern-Update end-if TP.NEX_HRS_REG Let $erncd = 'REG' Let #Hours_in = &TP.NEX_HRS_REG let #O_amount = round(#Hours_in, 2) #debug show 'amount= ' #O_amount Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'F' !full/part time enhancement validation 9/25/02 do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SF1 Let $erncd = 'SF1' Let #Hours_in = &TP.NEX_HRS_SF1 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SD0 Let $erncd = 'SDO' Let #Hours_in = &TP.NEX_HRS_SD0 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 14:04:26 2003 From: "Ward, Daniel" Date: Thu, 2 Jan 2003 14:00:30 -0500 Subject: [sqr-users] Report Viewer running under Windows2000 I have a kinda technical question for the group. My company just moved to the Windows 2000 environment and we Are having problems with Report view running inside one of our PowerBuilder 7 applications. When Report viewer Opens in our application and we try to use the e-mail function, our application crashes. Does anybody know if There are any settings or upgrades we may need for report viewer to operate under Windows 2000. The problem May be with the ocx called sqrvc.ocx or it may have something to do with permissions. If anybody has Any information on the subject, please let me know. >From what I see we are running ReportViewer version 6.0.0.0 Thanks Dan _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 16:29:29 2003 From: "John Berk" Date: Thu, 02 Jan 2003 15:28:11 -0600 Subject: [sqr-users] Call System thru NT Process Schedular Hello all, I'm trying to execute a .bat file in an SQR using the Call System command. code: let $COMSPEC=getenv('COMSPEC') let $Copy_String = $COMSPEC || ' /C d:\temp\copy.bat' Call System Using $Copy_String #Status show $Copy_String show #Status If #Status<>0 Show 'Error' End-if The .bat file executes fine when the SQR is run manually through SQRW; however when I run through the Windows 2000 Process Schedular the .bat file does not execute but I receive a successful #Status (0.00). What am I missing? Thanks, John Berk _________________________________________________________________ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 16:55:06 2003 From: rwulfson@bost.com Date: Thu, 2 Jan 2003 14:56:00 -0700 Subject: [sqr-users] WEIRD SQL STATEMENTS I have an sqr that was provided to me from another company - via a new employee, who is now on vacation. So I can't ask him about it. It contains some syntax that I've never seen. I was wondering if anyone knows what this refers to: It looks like a form of dynamic sql, but I've never seen the slashes used this way or the "with" clause???? FROM \$recname\ WHERE \$fieldname\ IN (\$Search_String\) WITH UR Thanks, Russ Wulfson _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 17:36:19 2003 Date: Thu, 2 Jan 2003 14:35:11 -0800 (PST) From: Jim Wyant Subject: Re: [sqr-users] WEIRD SQL STATEMENTS I believe the slashes are for DB2 specific dynamic SQL and the "with" references an index. Jim --- rwulfson@bost.com wrote: > I have an sqr that was provided to me from another company - via a > new > employee, who is now on vacation. So I can't ask him about it. It > contains > some syntax that I've never seen. I was wondering if anyone knows > what > this refers to: > It looks like a form of dynamic sql, but I've never seen the slashes > used > this way or the "with" clause???? > > FROM \$recname\ > WHERE \$fieldname\ IN (\$Search_String\) > WITH UR > > Thanks, > > Russ Wulfson > > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 2 17:47:07 2003 Subject: Re: [sqr-users] WEIRD SQL STATEMENTS From: rwulfson@bost.com Date: Thu, 2 Jan 2003 15:48:34 -0700 Jim - Thanks - I thought it might be platform specific. I'll modify it for my database. Thanks, Russ Wulfson Jim Wyant cc: Sent by: Subject: Re: [sqr-users] WEIRD SQL STATEMENTS sqr-users-admin@s qrug.org 01/02/2003 03:35 PM Please respond to sqr-users I believe the slashes are for DB2 specific dynamic SQL and the "with" references an index. Jim --- rwulfson@bost.com wrote: > I have an sqr that was provided to me from another company - via a > new > employee, who is now on vacation. So I can't ask him about it. It > contains > some syntax that I've never seen. I was wondering if anyone knows > what > this refers to: > It looks like a form of dynamic sql, but I've never seen the slashes > used > this way or the "with" clause???? > > FROM \$recname\ > WHERE \$fieldname\ IN (\$Search_String\) > WITH UR > > Thanks, > > Russ Wulfson > > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 09:23:52 2003 Date: Fri, 3 Jan 2003 09:19:21 -0500 From: dewhite@vicr.com Subject: [sqr-users] RE: Tuning for inserts after each field selected Hi, The thing that is confusing me is that you keep talking about a 'data file' and its layout, but it appears to me that your data is in a table. If you are really reading a flat file, loading it into a temporary table, and then joining that table to others, perhaps you would get better performance if you just processed the flat file as you read each record, instead of using a temporary table at all. You have a few very minor things that would not cause any significant performance problem: setting $flags to ' ', and then performing an if with an else that resets it to the same value (you could leave either the let statement or the else clause out); setting #Errors-This-Row to 0, and then having an if check to see if it is 0 (just do Validate-Payline; no need for an if statement), etc. But these are so minor they would not cause any performance hits. To find out where you are having slow performance, you might want to put show or display statements at certain points in your logic to see how long various procedures are taking. Also, do an explain plan on each SQL statement, including any selects, and tune accordingly. HTH, Denise White Sr. Software Engineer Vicor -----Original Message----- From: Braswell, Michael [mailto:Michael.Braswell@usi.net] Sent: Thursday, January 02, 2003 8:52 AM To: sqr-users@sqrug.org Subject: [sqr-users] Tuning for inserts after each field selected Hello, We are processing records from a data file and inserting them into PS payroll. The data file is arranged in a fashion similar to the following: "emplid","hoursforearningscode1","hoursforearningscode2","hoursforearnin gscode3".... (there are 18 earning codes!) so data looks like 0123,8,4,5,... 0234,7,3,6,... We have to do validation on each earnings code and then insert into the pay_others table (which is setup as having one earnings code per record). So the file does not really match the database structure but we are trying not to change the datafile. Please look at the following piece code and you will see that after we select for instance "hoursforearningscode1" we do an insert into pay_others same thing for all the other earning codes. So we may do a total of 18 inserts for one select if the person has hours in each earnings code. I've only included a piece of it but the code continues in the same fashion for all 18 earning codes. Typically the SQR's I've written have the file so that you do not have to do inserts after selecting a particular field. Rather you would select the emplid and earnings code and do an insert. Does anyone know if the way we are having to write the SQR (below) is causing it to run in 3-4 hours? Has anyone written a program using the inserts after each field that performed efficiently. We are just going over our options and wondering if this is causing the problem and maybe we should change the file layout. The procedures called below for validation run instantly so we have eliminated this as the bottleneck. Thanks in advance for any help. Begin-Select TP.EMPLID TP.NEX_LEGAL_ENTITY TP.NAME PD.NAME JOB.EMPL_STATUS JOB.FULL_PART_TIME JOB.LOCATION let $EMPLID = &TP.EMPLID let $NAME = &TP.NAME let $LEGAL_ENTITY = &TP.NEX_LEGAL_ENTITY let $FULL_PART_TIME = &JOB.FULL_PART_TIME let $EMPL_STATUS = &JOB.EMPL_STATUS let $NAME_IN_PS = &PD.NAME let $LOCATION = &JOB.LOCATION let $message = '' let #Errors-This-Row = 0 let $flags = ' ' if $EMPL_STATUS = 'T' let $flags = 'T' else let $flags = ' ' end-if add 1 to #Total-Records-Read ! let #Row = Edit(#Total-Records-Read,'99999') ! Validate values if #Errors-This-Row = 0 do Validate-Payline end-if if #Errors-This-Row >= 1 add 1 to #Total-Error-Rows add 1 to #array_count Put $Legal_entity $location $emplid $Name_in_ps $Name ' ' 0 $Message Into Errors(#array_count) else Do Pay-Ern-Update end-if TP.NEX_HRS_REG Let $erncd = 'REG' Let #Hours_in = &TP.NEX_HRS_REG let #O_amount = round(#Hours_in, 2) #debug show 'amount= ' #O_amount Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'F' !full/part time enhancement validation 9/25/02 do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SF1 Let $erncd = 'SF1' Let #Hours_in = &TP.NEX_HRS_SF1 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if TP.NEX_HRS_SD0 Let $erncd = 'SDO' Let #Hours_in = &TP.NEX_HRS_SD0 let #O_amount = round(#Hours_in, 2) Let #Hours = #O_amount if #Errors-This-Row = 0 and #Hours <> 0 If $FULL_PART_TIME = 'P' do Write-full-part-time-error else let #Row = #Row + 1 do Insert-Pay-Oth-Earns end-if end-if _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 11:43:16 2003 From: "Dinesh" Date: Fri, 3 Jan 2003 08:41:30 -0800 Subject: [sqr-users] question I am having problem with the following statement. cust_id ( ) on-break skiplines=0 after = call_another_procedure what I ideally wanted to do was not print cust_id and just stored it into &cust_id variable. Is there a way to do that with on-break statement. To be more clear what I want is the following, cust_id on-break skiplines=0 after = call_another_procedure move &cust_id to $cust_id let me know if you have the workaround. thanks in advance, dinesh _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 12:12:05 2003 From: "Patel, Samir (CITS)" Subject: RE: [sqr-users] question Date: Fri, 3 Jan 2003 12:11:08 -0500 add PRINT=NEVER... cust_id ( ) on-break PRINT=NEVER skiplines=0 after = call_another_procedure save =$cust_id Samir -----Original Message----- From: Dinesh [mailto:dinesh@redwoodtoxicology.com] Sent: Friday, January 03, 2003 12:07 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] question looks like it still prints out the cust_id. Any idea? dinesh -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Ramzi_Salameh@daveandbusters.com Sent: Friday, January 03, 2003 9:12 AM To: sqr-users@sqrug.org Subject: Re: [sqr-users] question Use the 'save' parameter with the on-break. For example: cust_id ( ) on-break skiplines=0 after = call_another_procedure save =$cust_id HTH ------------------------------------------------ Ramzi Salameh PeopleSoft Technical Analyst Dave & Busters, Inc. Voice (214) 904-2550 Fax (214) 350-0941 ------------------------------------------------ "Dinesh" cology.com> cc: Sent by: Subject: [sqr-users] question sqr-users-admin@sqr ug.org 01/03/03 10:41 AM Please respond to sqr-users I am having problem with the following statement. cust_id ( ) on-break skiplines=0 after = call_another_procedure what I ideally wanted to do was not print cust_id and just stored it into &cust_id variable. Is there a way to do that with on-break statement. To be more clear what I want is the following, cust_id on-break skiplines=0 after = call_another_procedure move &cust_id to $cust_id let me know if you have the workaround. thanks in advance, dinesh _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 12:24:37 2003 From: "Dinesh" Subject: RE: [sqr-users] question Date: Fri, 3 Jan 2003 09:21:52 -0800 it works now! thanks everbody for ur help. dinesh -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Patel, Samir (CITS) Sent: Friday, January 03, 2003 9:11 AM To: 'sqr-users@sqrug.org' Subject: RE: [sqr-users] question add PRINT=NEVER... cust_id ( ) on-break PRINT=NEVER skiplines=0 after = call_another_procedure save =$cust_id Samir -----Original Message----- From: Dinesh [mailto:dinesh@redwoodtoxicology.com] Sent: Friday, January 03, 2003 12:07 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] question looks like it still prints out the cust_id. Any idea? dinesh -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Ramzi_Salameh@daveandbusters.com Sent: Friday, January 03, 2003 9:12 AM To: sqr-users@sqrug.org Subject: Re: [sqr-users] question Use the 'save' parameter with the on-break. For example: cust_id ( ) on-break skiplines=0 after = call_another_procedure save =$cust_id HTH ------------------------------------------------ Ramzi Salameh PeopleSoft Technical Analyst Dave & Busters, Inc. Voice (214) 904-2550 Fax (214) 350-0941 ------------------------------------------------ "Dinesh" cology.com> cc: Sent by: Subject: [sqr-users] question sqr-users-admin@sqr ug.org 01/03/03 10:41 AM Please respond to sqr-users I am having problem with the following statement. cust_id ( ) on-break skiplines=0 after = call_another_procedure what I ideally wanted to do was not print cust_id and just stored it into &cust_id variable. Is there a way to do that with on-break statement. To be more clear what I want is the following, cust_id on-break skiplines=0 after = call_another_procedure move &cust_id to $cust_id let me know if you have the workaround. thanks in advance, dinesh _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 11:55:19 2003 Subject: Re: [sqr-users] question From: Ramzi_Salameh@daveandbusters.com Date: Fri, 3 Jan 2003 11:12:14 -0600 Use the 'save' parameter with the on-break. For example: cust_id ( ) on-break skiplines=0 after = call_another_procedure save =$cust_id HTH ------------------------------------------------ Ramzi Salameh PeopleSoft Technical Analyst Dave & Busters, Inc. Voice (214) 904-2550 Fax (214) 350-0941 ------------------------------------------------ "Dinesh" cology.com> cc: Sent by: Subject: [sqr-users] question sqr-users-admin@sqr ug.org 01/03/03 10:41 AM Please respond to sqr-users I am having problem with the following statement. cust_id ( ) on-break skiplines=0 after = call_another_procedure what I ideally wanted to do was not print cust_id and just stored it into &cust_id variable. Is there a way to do that with on-break statement. To be more clear what I want is the following, cust_id on-break skiplines=0 after = call_another_procedure move &cust_id to $cust_id let me know if you have the workaround. thanks in advance, dinesh _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 12:09:07 2003 From: "Dinesh" Subject: RE: [sqr-users] question Date: Fri, 3 Jan 2003 09:07:26 -0800 looks like it still prints out the cust_id. Any idea? dinesh -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Ramzi_Salameh@daveandbusters.com Sent: Friday, January 03, 2003 9:12 AM To: sqr-users@sqrug.org Subject: Re: [sqr-users] question Use the 'save' parameter with the on-break. For example: cust_id ( ) on-break skiplines=0 after = call_another_procedure save =$cust_id HTH ------------------------------------------------ Ramzi Salameh PeopleSoft Technical Analyst Dave & Busters, Inc. Voice (214) 904-2550 Fax (214) 350-0941 ------------------------------------------------ "Dinesh" cology.com> cc: Sent by: Subject: [sqr-users] question sqr-users-admin@sqr ug.org 01/03/03 10:41 AM Please respond to sqr-users I am having problem with the following statement. cust_id ( ) on-break skiplines=0 after = call_another_procedure what I ideally wanted to do was not print cust_id and just stored it into &cust_id variable. Is there a way to do that with on-break statement. To be more clear what I want is the following, cust_id on-break skiplines=0 after = call_another_procedure move &cust_id to $cust_id let me know if you have the workaround. thanks in advance, dinesh _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 12:18:04 2003 Date: Fri, 3 Jan 2003 12:17:39 -0500 (EST) From: Don Mellen Subject: RE: [sqr-users] question Use the print=never option of on-break. On Fri, 3 Jan 2003, Dinesh wrote: > looks like it still prints out the cust_id. Any idea? > > dinesh > > -----Original Message----- > From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On > Behalf Of Ramzi_Salameh@daveandbusters.com > Sent: Friday, January 03, 2003 9:12 AM > To: sqr-users@sqrug.org > Subject: Re: [sqr-users] question > > > > Use the 'save' parameter with the on-break. For example: > > cust_id ( ) on-break skiplines=0 after = call_another_procedure save > =$cust_id > > > HTH > > ------------------------------------------------ > Ramzi Salameh > PeopleSoft Technical Analyst > Dave & Busters, Inc. > > Voice (214) 904-2550 > Fax (214) 350-0941 > ------------------------------------------------ > > > > "Dinesh" > > cology.com> cc: > Sent by: Subject: [sqr-users] > question > sqr-users-admin@sqr > ug.org > > > 01/03/03 10:41 AM > Please respond to > sqr-users > > > > > > > I am having problem with the following statement. > > cust_id ( ) on-break skiplines=0 after = call_another_procedure > > what I ideally wanted to do was not print cust_id and just stored it into > &cust_id variable. Is there a way to do that with on-break statement. > To be more clear what I want is the following, > > cust_id on-break skiplines=0 after = > call_another_procedure > move &cust_id to $cust_id > > let me know if you have the workaround. > > thanks in advance, > > dinesh > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > ----------------------------------------------------------------------- Donald Mellen | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/ donm@ontko.com | "In the beginning, there was nothing, which exploded" _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 12:15:04 2003 From: "Jones, Barbra" Subject: RE: [sqr-users] question Date: Fri, 3 Jan 2003 11:14:28 -0600 Use print=never after the ()on-break -----Original Message----- From: Dinesh [mailto:dinesh@redwoodtoxicology.com] Sent: Friday, January 03, 2003 11:07 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] question looks like it still prints out the cust_id. Any idea? dinesh -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Ramzi_Salameh@daveandbusters.com Sent: Friday, January 03, 2003 9:12 AM To: sqr-users@sqrug.org Subject: Re: [sqr-users] question Use the 'save' parameter with the on-break. For example: cust_id ( ) on-break skiplines=0 after = call_another_procedure save =$cust_id HTH ------------------------------------------------ Ramzi Salameh PeopleSoft Technical Analyst Dave & Busters, Inc. Voice (214) 904-2550 Fax (214) 350-0941 ------------------------------------------------ "Dinesh" cology.com> cc: Sent by: Subject: [sqr-users] question sqr-users-admin@sqr ug.org 01/03/03 10:41 AM Please respond to sqr-users I am having problem with the following statement. cust_id ( ) on-break skiplines=0 after = call_another_procedure what I ideally wanted to do was not print cust_id and just stored it into &cust_id variable. Is there a way to do that with on-break statement. To be more clear what I want is the following, cust_id on-break skiplines=0 after = call_another_procedure move &cust_id to $cust_id let me know if you have the workaround. thanks in advance, dinesh _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Sat Jan 4 07:15:51 2003 From: Ramanarao_Pokuri Subject: RE: [sqr-users] question Date: Sat, 4 Jan 2003 17:43:51 +0530 yes, use print=never. > cust_id ( ) on-break skiplines=0 after = call_another_procedure > print=never > regards, ramana > ---------- > From: Dinesh[SMTP:dinesh@redwoodtoxicology.com] > Reply To: sqr-users@sqrug.org > Sent: Friday, January 03, 2003 10:11 PM > To: sqr-users@sqrug.org > Subject: [sqr-users] question > > I am having problem with the following statement. > > cust_id ( ) on-break skiplines=0 after = call_another_procedure > > what I ideally wanted to do was not print cust_id and just stored it into > &cust_id variable. Is there a way to do that with on-break statement. > To be more clear what I want is the following, > > cust_id on-break skiplines=0 after = > call_another_procedure > move &cust_id to $cust_id > > let me know if you have the workaround. > > thanks in advance, > > dinesh > > > ************************************************************************** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ************************************************************************** _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 12:00:06 2003 Date: Fri, 3 Jan 2003 10:59:46 -0600 From: "Lindquist, Maria" Subject: [sqr-users] Wildcards with Evaluate Statements Is there any way to use Wildcards with Evaluate-When statements in SQR? I wanted to find class sections that begin with a certain letter and do something. For example: EVALUATE &CLSFEE2.CLASS_SECTION When = '%B%' Let $ClassSection = &CLSFEE2.CLASS_SECTION Let $MND_ItemType = '152100000000' Let $MND_CrseRate = ' ' Let $MND_AdjTerm = $Term || 'STD' Let $MND_Waiver = ' ' Let $TUT_ItemType = '151000000000' Let $TUT_CrseRate = ' ' Let $TUT_AdjTerm = $Term || 'STD' Let $TUT_Waiver = ' ' Do Check-for-Prev-Acct-Type IF &A.ACCOUNT_TYPE_SF = 'MND' Do Insert-TUT-Sub-Fee Else Do Insert-MND-Sub-Fee End-if When = 'D%' Let $ClassSection = &CLSFEE2.CLASS_SECTION Let $MND_ItemType = '202100000000' Let $MND_CrseRate = ' ' Let $MND_AdjTerm = $Term || 'STD' Let $MND_Waiver = ' ' Let $TUT_ItemType = '201000000000' Let $TUT_CrseRate = ' ' Let $TUT_AdjTerm = $Term || 'STD' Let $TUT_Waiver = ' ' Do Check-for-Prev-Acct-Type IF &A.ACCOUNT_TYPE_SF = 'MND' Do Insert-TUT-Sub-Fee Else Do Insert-MND-Sub-Fee End-if etc............ *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ Maria K Lindquist mlindqui@uwc.edu IS Programmer/Analyst - Business Office University of Wisconsin Colleges 780 Regent St, PO Box 8680, Madison, WI 53708-8680 Phone: 608.265.2677 / Fax: 608.265.5770 *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 12:32:25 2003 Date: Fri, 3 Jan 2003 12:31:36 -0500 (EST) From: Don Mellen Subject: Re: [sqr-users] Wildcards with Evaluate Statements On Fri, 3 Jan 2003, Lindquist, Maria wrote: > Is there any way to use Wildcards with Evaluate-When statements in SQR? Nope. The "evaluate" command and the "when" both on;y accept values, not expressions. > I wanted to find class sections that begin with a certain letter and do > something. For example: If the above is what you want to do, you could say.... let $begins_with = substr(&CLSFEE2.CLASS_SECTION, 1, 1) evaluate $begins_with when = 'B' ..... when = 'D' ..... If you want to do checks like below, for "has a 'B' in it" and "starts with 'D'", then seperate "if" statements would probably be best. > EVALUATE &CLSFEE2.CLASS_SECTION > > When = '%B%' > .... > When = 'D%' > .... > > etc............ > > > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ > Maria K Lindquist > mlindqui@uwc.edu > IS Programmer/Analyst - Business Office > University of Wisconsin Colleges > 780 Regent St, PO Box 8680, Madison, WI 53708-8680 > Phone: 608.265.2677 / Fax: 608.265.5770 > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > ----------------------------------------------------------------------- Donald Mellen | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/ donm@ontko.com | "In the beginning, there was nothing, which exploded" _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Fri Jan 3 12:38:30 2003 Subject: RE: [sqr-users] Wildcards with Evaluate Statements Date: Fri, 3 Jan 2003 11:37:48 -0600 From: "Lindquist, Maria" I just tried this before you sent this message and that's how I'm going to do it. Thanks anyways for the suggestions! -----Original Message----- From: Don Mellen [mailto:donm@ontko.com] Sent: Friday, January 03, 2003 11:32 AM To: sqr-users@sqrug.org Subject: Re: [sqr-users] Wildcards with Evaluate Statements On Fri, 3 Jan 2003, Lindquist, Maria wrote: > Is there any way to use Wildcards with Evaluate-When statements in > SQR? Nope. The "evaluate" command and the "when" both on;y accept values, not expressions. > I wanted to find class sections that begin with a certain letter and > do something. For example: If the above is what you want to do, you could say.... let $begins_with = substr(&CLSFEE2.CLASS_SECTION, 1, 1) evaluate $begins_with when = 'B' ..... when = 'D' ..... If you want to do checks like below, for "has a 'B' in it" and "starts with 'D'", then seperate "if" statements would probably be best. > EVALUATE &CLSFEE2.CLASS_SECTION > > When = '%B%' > .... > When = 'D%' > .... > > etc............ > > > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ > Maria K Lindquist > mlindqui@uwc.edu > IS Programmer/Analyst - Business Office > University of Wisconsin Colleges > 780 Regent St, PO Box 8680, Madison, WI 53708-8680 > Phone: 608.265.2677 / Fax: 608.265.5770 > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > ----------------------------------------------------------------------- Donald Mellen | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/ donm@ontko.com | "In the beginning, there was nothing, which exploded" _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Jan 6 10:40:30 2003 Date: Mon, 06 Jan 2003 10:37:56 -0500 From: "Dan Brinkley" Subject: [sqr-users] Recomputing Peoplesoft Balance Tables YTD, QTD amounts from MTD amounts Does anyone know of an SQR (or some procedure) that recalculate the QTD and YTD amounts from the MTD amounts in the Payroll balance tables. We have some inconsistency because, during conversion of PS_EARNINGS_BAL, periods 5 and 6 (may, June) were written with QTR=1 (should be 2). We just noticed this now and also caused the YTD totals to not jive with the MTD totals. Fortunately, a small set of earnings codes are affected. I can easily fix the bad QUARTER, but fixing the numbers would take me more time. Surely someone has had to write something to fix (or at least report) this kind of table inconsistency. Peoplesoft support has stated they provide nothing, but to use TAX015.sqr as a guide. Thanks in advance for anyone's help. Maybe this is really easy. I just hate to miss something, and really screw up these tables. Daniel Brinkley Techneglas, Inc. Daniel Brinkley Programmer/Analyst Techneglas, Inc. dbrinkley@techneglas.com phone: (614) 445-4721 fax: (614) 445-4723 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Jan 6 13:07:23 2003 Date: Mon, 06 Jan 2003 10:06:25 -0800 From: "Yingqi Cui" Subject: [sqr-users] SQR Tools for PS 8.18 We are evaluating SQR tools for PeopleSoft Tools 8.18. Which one is compatible? SQR Plus, Brio SQR Builder, SQR Workbench? Which one are you using? Do you like it? Any info is greatly appreciated! Yingqi _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 18:58:47 2003 Subject: Re: [sqr-users] SQR Tools for PS 8.18 From: gopal_krishan@peoplesoft.com Date: Wed, 8 Jan 2003 09:38:35 +1000 I have been using SqrPlus in PeopleSoft environment. It is very good and convenient to work with. "Yingqi Cui" To: "<" Sent by: cc: sqr-users-admin@s Subject: [sqr-users] SQR Tools for PS 8.18 qrug.org 01/07/2003 05:06 AM Please respond to sqr-users We are evaluating SQR tools for PeopleSoft Tools 8.18. Which one is compatible? SQR Plus, Brio SQR Builder, SQR Workbench? Which one are you using? Do you like it? Any info is greatly appreciated! Yingqi _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 9 10:51:20 2003 From: "Karver" Subject: RE: [sqr-users] SQR Tools for PS 8.18 Date: Thu, 9 Jan 2003 23:53:27 +0800 Hi Yingqi, Pardon me if I give my own product a plug. SQR Tree is a utility for showing a tree structure of the procedure calls and #include files in an SQR program. It uses a Windows Explorer type interface with expandable branches on the left, and a listing of the program on the right. Clicking on a branch in the tree causes the listing to jump to the line calling the procedure or #include file. Double clicking on a branch causes the listing to jump to the line where the procedure or #include file begins. I've had plenty of positive comments from people who've used it. See if you like it! I have a very basic web page you can download it from at: http://www.angelfire.com/biz7/sqrtree Regards Wayne Ivory -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Yingqi Cui Sent: Tuesday, 7 January 2003 2:06 AM To: < Subject: [sqr-users] SQR Tools for PS 8.18 We are evaluating SQR tools for PeopleSoft Tools 8.18. Which one is compatible? SQR Plus, Brio SQR Builder, SQR Workbench? Which one are you using? Do you like it? Any info is greatly appreciated! Yingqi _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 9 11:15:15 2003 From: Bob Helm Subject: RE: [sqr-users] SQR Tools for PS 8.18 Date: Thu, 9 Jan 2003 10:14:09 -0600 Then please, allow me to plug SQR TREE ;-) I have SQR Tree and I highly recommend it to anyone working with SQR. It's most useful, very affordable and has saved me hours of time. Sincerely, B The Programmer formerly known as Bob Mediware Information Systems Inc. (913) 307-1045 Bob.Helm@Mediware.com This message has been sent using 100% recycled electrons -----Original Message----- From: Karver [mailto:basic@gateway.net.au] Sent: Thursday, January 09, 2003 9:53 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] SQR Tools for PS 8.18 Hi Yingqi, Pardon me if I give my own product a plug. SQR Tree is a utility for showing a tree structure of the procedure calls and #include files in an SQR program. It uses a Windows Explorer type interface with expandable branches on the left, and a listing of the program on the right. Clicking on a branch in the tree causes the listing to jump to the line calling the procedure or #include file. Double clicking on a branch causes the listing to jump to the line where the procedure or #include file begins. I've had plenty of positive comments from people who've used it. See if you like it! I have a very basic web page you can download it from at: http://www.angelfire.com/biz7/sqrtree Regards Wayne Ivory -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Yingqi Cui Sent: Tuesday, 7 January 2003 2:06 AM To: < Subject: [sqr-users] SQR Tools for PS 8.18 We are evaluating SQR tools for PeopleSoft Tools 8.18. Which one is compatible? SQR Plus, Brio SQR Builder, SQR Workbench? Which one are you using? Do you like it? Any info is greatly appreciated! Yingqi _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 9 11:18:07 2003 Subject: RE: [sqr-users] SQR Tools for PS 8.18 From: Rick_Creel@aoncons.com Date: Thu, 9 Jan 2003 11:17:30 -0500 Affordable? You mean it's not free?!?! Bob Helm e.com> cc: Sent by: Subject: RE: [sqr-users] SQR Tools for PS 8.18 sqr-users-admin@s qrug.org 01/09/2003 11:14 AM Please respond to sqr-users Then please, allow me to plug SQR TREE ;-) I have SQR Tree and I highly recommend it to anyone working with SQR. It's most useful, very affordable and has saved me hours of time. Sincerely, B The Programmer formerly known as Bob Mediware Information Systems Inc. (913) 307-1045 Bob.Helm@Mediware.com This message has been sent using 100% recycled electrons -----Original Message----- From: Karver [mailto:basic@gateway.net.au] Sent: Thursday, January 09, 2003 9:53 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] SQR Tools for PS 8.18 Hi Yingqi, Pardon me if I give my own product a plug. SQR Tree is a utility for showing a tree structure of the procedure calls and #include files in an SQR program. It uses a Windows Explorer type interface with expandable branches on the left, and a listing of the program on the right. Clicking on a branch in the tree causes the listing to jump to the line calling the procedure or #include file. Double clicking on a branch causes the listing to jump to the line where the procedure or #include file begins. I've had plenty of positive comments from people who've used it. See if you like it! I have a very basic web page you can download it from at: http://www.angelfire.com/biz7/sqrtree Regards Wayne Ivory -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Yingqi Cui Sent: Tuesday, 7 January 2003 2:06 AM To: < Subject: [sqr-users] SQR Tools for PS 8.18 We are evaluating SQR tools for PeopleSoft Tools 8.18. Which one is compatible? SQR Plus, Brio SQR Builder, SQR Workbench? Which one are you using? Do you like it? Any info is greatly appreciated! Yingqi _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Thu Jan 9 11:41:06 2003 From: "Karver" Subject: RE: [sqr-users] SQR Tools for PS 8.18 Date: Fri, 10 Jan 2003 00:43:35 +0800 SQR Tree is shareware. You are free to download and try it. If you like it you can pay $25. Regards Wayne Ivory -----Original Message----- From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On Behalf Of Rick_Creel@aoncons.com Sent: Friday, 10 January 2003 12:18 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] SQR Tools for PS 8.18 Affordable? You mean it's not free?!?! _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Jan 6 15:28:28 2003 From: "Poonam Auluck" Date: Mon, 6 Jan 2003 15:26:02 -0500 Subject: [sqr-users] Auto-commits Hi All - I was wondering if anyone came across turning the auto-commit functionality off in SQR. I would like to insert some rows into a table, but I don't want to commit it. I just want the data to be temporarily sitting in there. I thought there was a flag or maybe someplace that I can turn this off, but I can't find it. I thought the flag was -XC, which should suppress the database commit when the report has finished running, only when I use this in the command line, I get an error saying it doesn't understand it. Should I be typing in something else along w/ '-XC'...Do I have to put 'False' in there somewhere? Any help is appreciated! Thanks! _________________ Poonam Auluck AMS, Inc. 703-227-5576 . _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Jan 6 16:38:29 2003 Subject: Re: [sqr-users] Auto-commits From: rwulfson@bost.com Date: Mon, 6 Jan 2003 13:55:53 -0700 The book cites this flag for a "callable" sqr? But couldn't you do a rollback before the program ends? Thanks, Russ Wulfson Developer Boston Market Corporation 303-216-5614 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Jan 6 16:54:08 2003 From: "Genady Sosnovsky" Subject: Re: [sqr-users] Auto-commits Date: Mon, 6 Jan 2003 15:53:03 -0600 Hi, Binary SQR modules do not have (-XC) flag available. Only Callable SQR ( sqr invoked via sqr API) has this option. Here is the work around I can suggest: If you determine ( within you report logic) that there is a need to rollback inserted/deleted/updated data you can just explicitly issue ROLLBACK command or its equivalent depends on what database you use. For Oracle: BEGIN-PROCEDURE ora_rollback BEGIN-SQL Begin Rollback;; End;; END-SQL END-PROCEDURE ! ora_rollback Also, sqr will not commit data if terminated with an error. Genady Sosnovsky genadys@ontko.com ----- Original Message ----- From: "Poonam Auluck" To: Sent: Monday, January 06, 2003 2:26 PM Subject: [sqr-users] Auto-commits > Hi All - I was wondering if anyone came across turning the auto-commit > functionality off in SQR. I would like to insert some rows into a table, > but I don't want to commit it. I just want the data to be temporarily > sitting in there. > > I thought there was a flag or maybe someplace that I can turn this off, but > I can't find it. I thought the flag was -XC, which should suppress the > database commit when the report has finished running, only when I use this > in the command line, I get an error saying it doesn't understand it. > Should I be typing in something else along w/ '-XC'...Do I have to put > 'False' in there somewhere? > > Any help is appreciated! > Thanks! > > > _________________ > Poonam Auluck > AMS, Inc. > 703-227-5576 > > > . > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Mon Jan 6 16:54:24 2003 Subject: Re: [sqr-users] Auto-commits From: amy.harvel@phoenix.gov Date: Mon, 6 Jan 2003 14:53:49 -0700 Place a Rollback in your program before the end of the program. If you are using PeopleSoft put the rollback before the reset and sttapi-term Rollback Show 'Rolling Back Database' do RESET do STDAPI-TERM do Get-Current-DateTime show $ProgName ' Completed Successfully at ' $AsOfNow ' on ' $AsOfToday Amy M. Harvel City Of Phoenix Information Technology Department Programmer III 602-261-8974 "Poonam Auluck" Sent by: sqr-users-admin@sqrug.org 01/06/2003 01:26 PM Please respond to sqr-users To: "'sqr-users@sqrug.org'" cc: Subject: [sqr-users] Auto-commits Hi All - I was wondering if anyone came across turning the auto-commit functionality off in SQR. I would like to insert some rows into a table, but I don't want to commit it. I just want the data to be temporarily sitting in there. I thought there was a flag or maybe someplace that I can turn this off, but I can't find it. I thought the flag was -XC, which should suppress the database commit when the report has finished running, only when I use this in the command line, I get an error saying it doesn't understand it. Should I be typing in something else along w/ '-XC'...Do I have to put 'False' in there somewhere? Any help is appreciated! Thanks! _________________ Poonam Auluck AMS, Inc. 703-227-5576 . _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 06:33:32 2003 Date: Tue, 07 Jan 2003 06:30:47 -0500 From: "Jeffrey Bedell" Subject: Re: [sqr-users] Auto-commits As the last step in your program, do a rollback. Jeffrey K. Bedell Senior Systems Analyst Information Systems Syracuse University (315) 443-9273 >>> poonam.auluck@ams.com 01/06/03 03:26PM >>> Hi All - I was wondering if anyone came across turning the auto-commit functionality off in SQR. I would like to insert some rows into a table, but I don't want to commit it. I just want the data to be temporarily sitting in there. I thought there was a flag or maybe someplace that I can turn this off, but I can't find it. I thought the flag was -XC, which should suppress the database commit when the report has finished running, only when I use this in the command line, I get an error saying it doesn't understand it. Should I be typing in something else along w/ '-XC'...Do I have to put 'False' in there somewhere? Any help is appreciated! Thanks! _________________ Poonam Auluck AMS, Inc. 703-227-5576 . _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 10:35:05 2003 From: "Turner, Ivan" Subject: RE: [sqr-users] Auto-commits Date: Tue, 7 Jan 2003 10:34:16 -0500 If you are on a Oracle database you can create a GLOBAL TEMPORARY TABLE. Part of the syntax of creating this table is either ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS. This may be your best solution. -----Original Message----- From: Genady Sosnovsky [mailto:genadys@ontko.com] Sent: Monday, January 06, 2003 4:53 PM To: sqr-users@sqrug.org Subject: Re: [sqr-users] Auto-commits Hi, Binary SQR modules do not have (-XC) flag available. Only Callable SQR ( sqr invoked via sqr API) has this option. Here is the work around I can suggest: If you determine ( within you report logic) that there is a need to rollback inserted/deleted/updated data you can just explicitly issue ROLLBACK command or its equivalent depends on what database you use. For Oracle: BEGIN-PROCEDURE ora_rollback BEGIN-SQL Begin Rollback;; End;; END-SQL END-PROCEDURE ! ora_rollback Also, sqr will not commit data if terminated with an error. Genady Sosnovsky genadys@ontko.com ----- Original Message ----- From: "Poonam Auluck" To: Sent: Monday, January 06, 2003 2:26 PM Subject: [sqr-users] Auto-commits > Hi All - I was wondering if anyone came across turning the auto-commit > functionality off in SQR. I would like to insert some rows into a table, > but I don't want to commit it. I just want the data to be temporarily > sitting in there. > > I thought there was a flag or maybe someplace that I can turn this off, but > I can't find it. I thought the flag was -XC, which should suppress the > database commit when the report has finished running, only when I use this > in the command line, I get an error saying it doesn't understand it. > Should I be typing in something else along w/ '-XC'...Do I have to put > 'False' in there somewhere? > > Any help is appreciated! > Thanks! > > > _________________ > Poonam Auluck > AMS, Inc. > 703-227-5576 > > > . > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 10:05:15 2003 Date: Tue, 07 Jan 2003 10:02:43 -0500 From: "James Lanter" Subject: [sqr-users] Save As for Text Files This one seems easy, but I'm at a lost. Basically, I need to create a new text file each time the SQR is run. The output has to be a fixed length text file and I use the date in the file name. The problem is I can't open a text file that is not there and I don't want to overwrite a previous run's output. Am I missing something simple here? Thanks! Jamie _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 10:26:09 2003 Date: Tue, 07 Jan 2003 10:24:40 -0500 From: "Prasanna Menta" Subject: Re: [sqr-users] Save As for Text Files If you are using Peoplesoft, you can use your process instance concatenated with the date which might be a good solution. If not you need to initialise a counter which needs to be stored at a back end table and update the counter each time when a new process is run and use this to get the file name Hope this helps Prasanna James Lanter wrote: > > This one seems easy, but I'm at a lost. > > Basically, I need to create a new text file each time the SQR is run. > The output has to be a fixed length text file and I use the date in the > file name. The problem is I can't open a text file that is not there > and I don't want to overwrite a previous run's output. Am I missing > something simple here? > > Thanks! > > Jamie > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 10:40:27 2003 Subject: Re: [sqr-users] Save As for Text Files From: Ramzi_Salameh@daveandbusters.com Date: Tue, 7 Jan 2003 09:43:08 -0600 You could add the hours and minutes (and maybe even seconds) to the file name, along with the date, just in case you need to run the SQR several times in 1 day. By adding the date & time to the filename you should always get a unique name. The file doesn't have to already exist in order to open it for writing. HTH ------------------------------------------ Ramzi Salameh PeopleSoft Technical Analyst Dave & Busters, Inc. ------------------------------------------ "Prasanna Menta" To: sqr-users@sqrug.org Sent by: cc: sqr-users-admin@s Subject: Re: [sqr-users] Save As for Text Files qrug.org 01/07/03 09:24 AM Please respond to sqr-users If you are using Peoplesoft, you can use your process instance concatenated with the date which might be a good solution. If not you need to initialise a counter which needs to be stored at a back end table and update the counter each time when a new process is run and use this to get the file name Hope this helps Prasanna James Lanter wrote: > > This one seems easy, but I'm at a lost. > > Basically, I need to create a new text file each time the SQR is run. > The output has to be a fixed length text file and I use the date in the > file name. The problem is I can't open a text file that is not there > and I don't want to overwrite a previous run's output. Am I missing > something simple here? > > Thanks! > > Jamie > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 11:30:12 2003 From: "Stephen Ratliff" Subject: Re: [sqr-users] Save As for Text Files Date: Tue, 7 Jan 2003 10:27:09 -0600 Or, you can even create a new subfolder that is dated to put the new file with the same filename. You can do this by using command line commands while the SQR is running. Something like: let $FileDT = $AsOfToday let $Make_Dir = 'command.com /c mkdir T:\Groups\Custom\' || $FileDT call system using $Make_Dir #Dir_Return_Value It's an option if the filename must be consistant and you cannot concatenate process_instance, counter or time to the filename. Hope this helps. Stephen ----- Original Message ----- From: "James Lanter" To: Sent: Tuesday, January 07, 2003 9:02 AM Subject: [sqr-users] Save As for Text Files > This one seems easy, but I'm at a lost. > > Basically, I need to create a new text file each time the SQR is run. > The output has to be a fixed length text file and I use the date in the > file name. The problem is I can't open a text file that is not there > and I don't want to overwrite a previous run's output. Am I missing > something simple here? > > Thanks! > > Jamie > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 12:15:07 2003 Date: Tue, 7 Jan 2003 11:14:11 -0600 From: "Lindquist, Maria" Subject: [sqr-users] Images in PDF Is there something special you have to do to get images to show up in PDF? I changed my image to a jpeg and it still didn't work. print-image (0,78) type=jpeg-file image-size=(30,5) source='F:\sa80\testsqr\uwc_logo.jpg' *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ Maria K Lindquist mlindqui@uwc.edu IS Programmer/Analyst - Business Office University of Wisconsin Colleges 780 Regent St, PO Box 8680, Madison, WI 53708-8680 Phone: 608.265.2677 / Fax: 608.265.5770 *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 13:55:27 2003 Date: Tue, 7 Jan 2003 10:54:01 -0800 (PST) From: D Patel Subject: Re: [sqr-users] Images in PDF Nothing is wrong, change 0 to 1 and try to generate report. D Patel --- "Lindquist, Maria" wrote: > Is there something special you have to do to get > images to show up in > PDF? I changed my image to a jpeg and it still > didn't work. > > print-image (0,78) > type=jpeg-file > image-size=(30,5) > source='F:\sa80\testsqr\uwc_logo.jpg' > > > > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ > Maria K Lindquist > mlindqui@uwc.edu > IS Programmer/Analyst - Business Office > University of Wisconsin Colleges > 780 Regent St, PO Box 8680, Madison, WI 53708-8680 > Phone: 608.265.2677 / Fax: 608.265.5770 > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* > > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 14:33:26 2003 Subject: RE: [sqr-users] Images in PDF Date: Tue, 7 Jan 2003 13:31:53 -0600 From: "Lindquist, Maria" The image just shows up as a box with a X in it. -----Original Message----- From: D Patel [mailto:dgpinfo@yahoo.com] Sent: Tuesday, January 07, 2003 12:54 PM To: sqr-users@sqrug.org Subject: Re: [sqr-users] Images in PDF Nothing is wrong, change 0 to 1 and try to generate report. D Patel --- "Lindquist, Maria" wrote: > Is there something special you have to do to get > images to show up in > PDF? I changed my image to a jpeg and it still > didn't work. > > print-image (0,78) > type=jpeg-file > image-size=(30,5) > source='F:\sa80\testsqr\uwc_logo.jpg' > > > > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ > Maria K Lindquist > mlindqui@uwc.edu > IS Programmer/Analyst - Business Office > University of Wisconsin Colleges > 780 Regent St, PO Box 8680, Madison, WI 53708-8680 > Phone: 608.265.2677 / Fax: 608.265.5770 > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* > > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-admin@sqrug.org Tue Jan 7 15:16:38 2003 From: "Jones, Barbra" Subject: RE: [sqr-users] Images in PDF Date: Tue, 7 Jan 2003 14:15:34 -0600 Your code below is how ours is set up, and the images appear in the PDF. The only difference is the image is in the same directory as the sqr, so the source line references the file name only (ex: 'source=uwc_logo.jpg') You may also try declaring the image (declare-image) in the setup section - you shouldn't have to though. Barbra -----Original Message----- From: Lindquist, Maria [mailto:mlindqui@uwc.edu] Sent: Tuesday, January 07, 2003 1:32 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Images in PDF The image just shows up as a box with a X in it. -----Original Message----- From: D Patel [mailto:dgpinfo@yahoo.com] Sent: Tuesday, January 07, 2003 12:54 PM To: sqr-users@sqrug.org Subject: Re: [sqr-users] Images in PDF Nothing is wrong, change 0 to 1 and try to generate report. D Patel --- "Lindquist, Maria" wrote: > Is there something special you have to do to get > images to show up in > PDF? I changed my image to a jpeg and it still > didn't work. > > print-image (0,78) > type=jpeg-file > image-size=(30,5) > source='F:\sa80\testsqr\uwc_logo.jpg' > > > > *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ > Maria K Lindquist > mlindqui@uwc.edu > IS Programmer/Analyst - Business Office > University of Wisconsin Colleges > 780 Regent St, PO Box 86