From owner-sqr-users@list.iex.net Thu Jul 1 02:08:57 1999 Date: Thu, 1 Jul 1999 16:55:51 +1000 From: Adrian Clayfield Subject: Re: Is there anyway to determine what panel group was used to launch an sqr without creating a specialized runcntl record? Hi, Can you add LASTUPDDTM to your parameter table, then select PNLGRPNAME from PSPNLGRPDEFN where LASTUPDDTM matches it, or something along those lines.... ----------------------------------------------------- SGRUG, I have an sqr where I want to due conditional processing based on what panel group launched the sqr. I have a parameter table and don't want to create a run control record. I can select the parameter based on the panel group but can I get the panel group name from within SQR, if yes how? Pamela From owner-sqr-users@list.iex.net Thu Jul 1 06:01:13 1999 Date: Thu, 1 Jul 1999 06:42:04 EDT From: Steffon Johnson Subject: Re: Is there anyway to determine what panel group was used to lau... That would only tell you the last operator id that updated the panel group defn. You may, however, add the information to your run control table/file by capturing the information via P'Code on SaveEdit, or SavePostChange via SQLExec() (last choice, though). Email me directly and I'll give you my phone number and we talk real-time. HTH, Steffon From owner-sqr-users@list.iex.net Thu Jul 1 07:32:27 1999 Date: Thu, 1 Jul 1999 06:59:57 -0500 From: "Wendel, Robbi" Subject: Re: Print last page I apologize if this is something that you have already addressed or answered previously, but have you considered trying to use NEW-REPORT instead? It resets the internal page counter and permits multiple reports from same SQR. I didn't backtrack to look at all information regarding this posting to date, so you may have already addressed this or ruled this out. Have a great day, Robbi -----Original Message----- From: Kris Narravula [mailto:kris_narravula@HOTMAIL.COM] Sent: Wednesday, June 30, 1999 3:57 PM To: Multiple recipients of list SQR-USERS Subject: Re: Print last page Hi John, Sherry and Every Body, I got the same problem and wanted to post it. At the same time I saw the request on the list and looking for the outcome. I am getting the following error when I use LAST-PAGE command to print the last page number of the report on every page header as 'page 1 of 99' and run it to a printer from PeopleSoft. It works fine when I run the report to a file from the same PeopleSoft. (SQR 6002) Cannot open the printer file: \\cairo\infosrvs.spf (2): No such file or directory When I remove the LAST-PAGE command and run the report it works fine with out any error to the file as well as to the printer from PeopleSoft. We are on Oracle 7.3.4.3.1 and is on HPUX 10.20 & WinNT 4.0 File Server, SQR 4.3.2 PeopleSoft HRMS 7.50.00.000 PeopleTools 7.54.10 I am sorry, I may be giving a bit of inconvenience to the only SQR users. I am posing this here as similar issue is in discussion. Any body has any clues. Thanks in advance Kris ----- Original Message ----- From: Sherry Jin Sent: Wednesday, June 30, 1999 12:55 PM Subject: Re: Print last page John, Thanks for the response. The problem is resolved if I add -printer:WP into the SQR flag in config manager and specify file as the output destination. What happens is that whenever printer (Say LPT2) is selected as the output, peoplesoft generates -flpt2 into the process parameter. I believe "last-page" tries to generate a temp buffer file and considers lpt2 as the output directory/file. Sherry At 03:02 PM 6/30/99 -0400, John Milardovic wrote: >Do you mean 'print-direct'? or -printer:WP? or are you piping to the printer >in UNIX? > >If print-direct the problem might be that print-direct sends output directly >to the printer (i.e.. bypassing the SQR buffer) and so SQR can't calculate >the number of pages in the report. > >John Milardovic > >> -----Original Message----- >> From: Sherry Jin [SMTP:sjin@WINSTAR.COM] >> Sent: Wednesday, June 30, 1999 1:54 PM >> To: Multiple recipients of list SQR-USERS >> Subject: Print last page >> >> I am trying to use last-page to print out the total numbers of pages on >> each page. It works when I use file as the output destination, but >> receives >> error message 'Error reading the printer file (22) Invalid argument' when >> printer is used as the output destination. >> >> This is what I added to the code: >> >> page-number (1,1) 'Page ' >> last-page () ' Of ' >> >> Adding command last-page causes the error when output is printer. >> Has anyone been able to invoke last-page and send the output directly to >> printer? >> >> Thanks for any help, >> Sherry > > From owner-sqr-users@list.iex.net Thu Jul 1 16:55:39 1999 Date: Thu, 1 Jul 1999 10:02:47 -0400 From: "Morgan, Michael" Subject: Re: [psusers] Employee name format With rubber gloves. -----Original Message----- From: Kenny Melton [SMTP:KMELTO1@TANDY.COM] Sent: Wednesday, June 30, 1999 6:15 PM To: Multiple recipients of list SQR-USERS Subject: Re: [psusers] Employee name format Better yet, how would you handle The Artist Formerly Known As Prince? Kenny -----Original Message----- From: Gracen Duffield [mailto:gduffiel@TDHCA.STATE.TX.US] Sent: Wednesday, June 30, 1999 4:59 PM To: Multiple recipients of list SQR-USERS Subject: Re: [psusers] Employee name format When I was born, my parents decided that since I was a girl, I only needed two names. That way, when I got married, I would add my husband's name and then I would have a complete name. Since then, the seeds of my feminist rebellion have slowly twisted and turned on the injustice of not having a middle name. Why should my brother get a middle name, but not me? At my highschool graduation, the person who graduated in front of me had four!! names ( Megan Campbell Fitzhugh Wallace -- figure out which o' them is the "middle" name ). I have spent hours obsessing about this, but now, having read Jim's post, my spirit is lifted. Maybe my parents were on to something. I don't know, but when the rest of you don't get paid because your names were accidentally wiped out of the system by someone trying to fix the middle initial problem (MIP), my money will be sittin' pretty in the bank. You know, maybe we'd all be better off (albeit, possibly unemployed) if users weren't actually allowed to enter any data in the system. Barring that, can I see a show of hands for the "Buchanan ID" proposed last week ..... Gracen Duffield Texas Department of Housing and Community Affairs 475-3839 -----Original Message----- From: Jim Hardesty [mailto:jhardest@lmberry.com] Sent: Wednesday, June 30, 1999 2:50 PM To: psusers@egroups.com Subject: [psusers] Employee name format Here's a fun problem: PeopleSoft defines employee name as Last,First MI. (one letter MI). And many of us have found that PeopleSoft has a problem with the rotate name routine (rotname3.sqc) because it will not correctly handle names with spaces in the first name. 'Leone-Chipman,Mona Lisa T' comes out as last=Leone-Chipman, first = Mona and MI=L. and it ignores the T altogether. (We modified rotate name to correctly look for space and initial in the last two characters of name and whatever remains is first name). But here's a new one. I just found out that our users have been entering full middle names for some time. So we apparently have hundreds of employees with names like 'Wallis,Brandy Lee' instead of 'Wallis,Brandy L'. Which is kinda scary. Because now we have no way to get middle initial for those people. There is no way now to know if 'Wallis,Brandy Lee' has a middle name of Lee or a first name of Brandy Lee with no middle name (it happens). Wow. Big fun. The payroll manager says that they have been hiring people like this for years (ouch!) because that is the name on the Social Security Card. Uh oh. So if Betsy Ann Smith shows me a social security card, I have no way of knowing if she has a middle name or not. Is she "Betsy Ann" Smith or "Betsy" "Ann" Smith? Or worse, Mona Lisa Teresa Leone-Chipman. Is her middle name "Teresa" or is it "Lisa Teresa"? You could pretty much fix the rotate name routines to grab last name and shove everything else into first name by looking for the comma. This would work fine for JRs, IIs, etc. But there wouldn't be any way to get middle initial. What would you do, grab the first letter off the last 'word' in the first name if they have more than one 'word' in the first name? And what if someone has two first names and no middle name? Or one first name and two middle names? Yuk. So, what to do...what to do? The SQR programming isn't so much an issue. I doubt there is a single time we NEED middle initial so it is a moot point. Although who knows on some tax interfaces? PeopleCode could be an issue, but probably not. The real issue is functionally, what the heck should we be putting in the Name field? Inconsistent data is not good. We can't have 1/2 our employee names with a middle name and 1/2 with only initial. But what if we are required to use the Social Security Card name and old people like me have a card that says "James E Hardesty"? But maybe my wife has a new card that says "Joanne Lynn Hardesty"? This has "endless fiasco" written all over it. Before you laugh, go run a query and see if you have anybody out there with a bad name... jim ------------------------------------------------------------------------ eGroups Spotlight: "Egg and Egg Products" - There's more to eggs than eating them for breakfast! If you are a producer or a user this group is for you. http://clickhere.egroups.com/click/114 eGroups.com home: http://www.egroups.com/group/psusers http://www.egroups.com - Simplifying group communications From owner-sqr-users@list.iex.net Thu Jul 1 16:50:58 1999 Date: Thu, 1 Jul 1999 09:58:19 -0700 From: Kris Narravula Subject: Re: Print last page Robbi, I am not trying to create multiple reports and I am also not trying to do any manipulation of Page Numbers, etc. My question is very simple, I am getting the error while I am trying to run the report to the printer via PeopleSoft with SQR's LAST-PAGE command in the report. It works fine when I run it to the file from the same PeopleSoft Menu. The error I am getting is (SQR 6002) Cannot open the printer file: \\cairo\infosrvs.spf (2): No such file or directory It is not addressed yet. Thanks in advance. Kris ----- Original Message ----- From: Wendel, Robbi Sent: Thursday, July 01, 1999 4:59 AM Subject: Re: Print last page I apologize if this is something that you have already addressed or answered previously, but have you considered trying to use NEW-REPORT instead? It resets the internal page counter and permits multiple reports from same SQR. I didn't backtrack to look at all information regarding this posting to date, so you may have already addressed this or ruled this out. Have a great day, Robbi -----Original Message----- From: Kris Narravula [mailto:kris_narravula@HOTMAIL.COM] Sent: Wednesday, June 30, 1999 3:57 PM To: Multiple recipients of list SQR-USERS Subject: Re: Print last page Hi John, Sherry and Every Body, I got the same problem and wanted to post it. At the same time I saw the request on the list and looking for the outcome. I am getting the following error when I use LAST-PAGE command to print the last page number of the report on every page header as 'page 1 of 99' and run it to a printer from PeopleSoft. It works fine when I run the report to a file from the same PeopleSoft. (SQR 6002) Cannot open the printer file: \\cairo\infosrvs.spf (2): No such file or directory When I remove the LAST-PAGE command and run the report it works fine with out any error to the file as well as to the printer from PeopleSoft. We are on Oracle 7.3.4.3.1 and is on HPUX 10.20 & WinNT 4.0 File Server, SQR 4.3.2 PeopleSoft HRMS 7.50.00.000 PeopleTools 7.54.10 I am sorry, I may be giving a bit of inconvenience to the only SQR users. I am posing this here as similar issue is in discussion. Any body has any clues. Thanks in advance Kris ----- Original Message ----- From: Sherry Jin Sent: Wednesday, June 30, 1999 12:55 PM Subject: Re: Print last page John, Thanks for the response. The problem is resolved if I add -printer:WP into the SQR flag in config manager and specify file as the output destination. What happens is that whenever printer (Say LPT2) is selected as the output, peoplesoft generates -flpt2 into the process parameter. I believe "last-page" tries to generate a temp buffer file and considers lpt2 as the output directory/file. Sherry At 03:02 PM 6/30/99 -0400, John Milardovic wrote: >Do you mean 'print-direct'? or -printer:WP? or are you piping to the printer >in UNIX? > >If print-direct the problem might be that print-direct sends output directly >to the printer (i.e.. bypassing the SQR buffer) and so SQR can't calculate >the number of pages in the report. > >John Milardovic > >> -----Original Message----- >> From: Sherry Jin [SMTP:sjin@WINSTAR.COM] >> Sent: Wednesday, June 30, 1999 1:54 PM >> To: Multiple recipients of list SQR-USERS >> Subject: Print last page >> >> I am trying to use last-page to print out the total numbers of pages on >> each page. It works when I use file as the output destination, but >> receives >> error message 'Error reading the printer file (22) Invalid argument' when >> printer is used as the output destination. >> >> This is what I added to the code: >> >> page-number (1,1) 'Page ' >> last-page () ' Of ' >> >> Adding command last-page causes the error when output is printer. >> Has anyone been able to invoke last-page and send the output directly to >> printer? >> >> Thanks for any help, >> Sherry > > From owner-sqr-users@list.iex.net Thu Jul 1 14:32:10 1999 Date: Thu, 1 Jul 1999 11:22:37 -0700 From: Pankaj Bedekar Subject: SQL-SERVER how to run stored procedures or SELECT INTO statement in SQL paragraph This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BEC3EE.B257DB0C Content-Type: text/plain; charset="iso-8859-1" I am using MS SQL-SERVER 6.5 AND SQR 3. I need to create a table on the fly using a SELECT statement. I tried using SELECT INTO in BEGIN-SQL ...END-SQL block but it never executed the statement. I created a stored procedure which does the same but now I cannot execute the stored procedure using EXECUTE or using 'BEGIN-SQL...END-SQL' anyone have done this before ? plea let me know if anyone have more info. on this . Thank you, Pankaj ------_=_NextPart_001_01BEC3EE.B257DB0C Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable SQL-SERVER how to run stored procedures or SELECT INTO statement = in SQL paragraph

I am using MS SQL-SERVER 6.5 AND SQR = 3. I need to create a table on the fly using a SELECT statement.
I tried using SELECT INTO in = BEGIN-SQL ...END-SQL block but it never executed the statement.
I created a stored procedure which = does the same but now I cannot execute the stored procedure using = EXECUTE
or using 'BEGIN-SQL...END-SQL' =

anyone have done this before ? plea = let me know if anyone have more info. on this .
 
Thank you,

Pankaj

------_=_NextPart_001_01BEC3EE.B257DB0C-- From owner-sqr-users@list.iex.net Thu Jul 1 14:05:38 1999 Date: Thu, 1 Jul 1999 11:46:22 -0700 From: Joe Subject: Selecting a date that is 4 weeks prior to report date Hi all, I need to select Date_A from a record where Date_A is no more than 4 weeks prior to Date_Report. The logic is "select Date_A from Record_A where Date_A = Date_Report + 28 days" What's the most efficient way to do this? Thanks in Advance ~~ JEJ ~~ ;{) -------------------------------------------------------- $14.95 Unlimited Internet Access, http://www.surfree.com From owner-sqr-users@list.iex.net Thu Jul 1 17:09:13 1999 Date: Thu, 1 Jul 1999 17:01:49 -0400 From: "Sarabudla, Raji R." Subject: Re: Selecting a date that is 4 weeks prior to report date Hi Joe, Is the Date_ Report is Current Date ? If it is Current date, the following method is easy. Declare a date variable in Setup section and Use the dateadd function to add or subtract the no. of days. And use the newdate in your where clause. Make sure the date format is the same with your Database date format. You can accomplish the Date from the record not more than 4 weeks prior to the Report_Date (Current Date) with following example Ex: Begin-setup Declare-variable Date $datenew,$date_report End-delcare End-setup Begin-program Do main-processing End-program Begin-procedure main-processing -- if Date_report is Current_Date, use datenow() function let $Date_report = datenow() Let $datenew = dateadd($Date_Report, 'day',-28) - subtracts 4 weeks from current date Begin-select Date_A from Record_A where Date_A >= $datenew -- Make sure the Date format here. If necessary format it, -- as ex: TO_DATE($datenew,'DD-MON-YY') or whatever end-select end-proceduire Hope this helps raji ---------- From: Joe [SMTP:jejohn1216@SURFREE.COM] Sent: Thursday, July 01, 1999 2:46 PM To: Multiple recipients of list SQR-USERS Subject: Selecting a date that is 4 weeks prior to report date Hi all, I need to select Date_A from a record where Date_A is no more than 4 weeks prior to Date_Report. The logic is "select Date_A from Record_A where Date_A = Date_Report + 28 days" What's the most efficient way to do this? Thanks in Advance ~~ JEJ ~~ ;{) -------------------------------------------------------- $14.95 Unlimited Internet Access, http://www.surfree.com From owner-sqr-users@list.iex.net Thu Jul 1 16:55:55 1999 Date: Thu, 1 Jul 1999 15:15:51 -0600 From: Tim Green Subject: Re: SQL-SERVER how to run stored procedures or SELECT INTO statement in SQL paragraph --0__=pcU9TRtkwC7FuWpUrtBjlFVXge4SWxMIFi2Hhj3hJPMbHJrfve6iUSQZ Content-type: text/plain; charset=us-ascii Content-Disposition: inline I don't have enough info about your situation to be sure, but this sounds familiar. Are you doing the "Select... Into" into a temporary table, of the #tablename variety? You sure it's not being executed? SQR will run SQL statements on multiple connections to the server, and a temporary table is only visible on the connection it was created on. Also, temp tables created within stored procedures are dropped when the stored procedure terminates. Try using the -Cn parameters on the Begin-SQL line that creates the table, and on any Begin-Select or Begin-SQL paragraphs that reference it. And of course, verify that the Begin-SQL paragraph is being executed within your program flow with a display or show statement just before the Begin-SQL paragraph. Good luck, Tim Pankaj Bedekar on 07/01/99 02:22:37 PM Please respond to SQR-USERS@list.iex.net To: Multiple recipients of list SQR-USERS cc: (bcc: Tim Green/HWRD/ASG_Louisville/NAD) Subject: SQL-SERVER how to run stored procedures or SELECT INTO statement in SQL paragraph I am using MS SQL-SERVER 6.5 AND SQR 3. I need to create a table on the fly using a SELECT statement. I tried using SELECT INTO in BEGIN-SQL ...END-SQL block but it never executed the statement. I created a stored procedure which does the same but now I cannot execute the stored procedure using EXECUTE or using 'BEGIN-SQL...END-SQL' anyone have done this before ? plea let me know if anyone have more info. on this . Thank you, Pankaj --0__=pcU9TRtkwC7FuWpUrtBjlFVXge4SWxMIFi2Hhj3hJPMbHJrfve6iUSQZ Content-type: text/html; name="att1.htm" Content-Disposition: attachment; filename="att1.htm" Content-transfer-encoding: base64 Content-Description: Internet HTML PCFET0NUWVBFIEhUTUwgUFVCTElDICItLy9XM0MvL0RURCBIVE1MIDMuMi8vRU4iPg0KPEhUTUw+ DQo8SEVBRD4NCjxNRVRBIEhUVFAtRVFVSVY9IkNvbnRlbnQtVHlwZSIgQ09OVEVOVD0idGV4dC9o dG1sOyBjaGFyc2V0PWlzby04ODU5LTEiPg0KPE1FVEEgTkFNRT0iR2VuZXJhdG9yIiBDT05URU5U PSJNUyBFeGNoYW5nZSBTZXJ2ZXIgdmVyc2lvbiA1LjUuMjQ0OC4wIj4NCjxUSVRMRT5TUUwtU0VS VkVSIGhvdyB0byBydW4gc3RvcmVkIHByb2NlZHVyZXMgb3IgU0VMRUNUIElOVE8gc3RhdGVtZW50 IGluIFNRTCBwYXJhZ3JhcGg8L1RJVExFPg0KPC9IRUFEPg0KPEJPRFk+DQoNCjxQPjxGT05UIFNJ WkU9MiBGQUNFPSJBcmlhbCI+SSBhbSB1c2luZyBNUyBTUUwtU0VSVkVSIDYuNSBBTkQgU1FSIDMu IEkgbmVlZCB0byBjcmVhdGUgYSB0YWJsZSBvbiB0aGUgZmx5IHVzaW5nIGEgU0VMRUNUIHN0YXRl bWVudC48L0ZPTlQ+DQo8QlI+PEZPTlQgU0laRT0yIEZBQ0U9IkFyaWFsIj5JIHRyaWVkIHVzaW5n IFNFTEVDVCBJTlRPIGluIEJFR0lOLVNRTCAuLi5FTkQtU1FMIGJsb2NrIGJ1dCBpdCBuZXZlciBl eGVjdXRlZCB0aGUgc3RhdGVtZW50LjwvRk9OVD4NCjxCUj48Rk9OVCBTSVpFPTIgRkFDRT0iQXJp YWwiPkkgY3JlYXRlZCBhIHN0b3JlZCBwcm9jZWR1cmUgd2hpY2ggZG9lcyB0aGUgc2FtZSBidXQg bm93IEkgY2Fubm90IGV4ZWN1dGUgdGhlIHN0b3JlZCBwcm9jZWR1cmUgdXNpbmcgRVhFQ1VURSA8 L0ZPTlQ+DQo8QlI+PEZPTlQgU0laRT0yIEZBQ0U9IkFyaWFsIj5vciB1c2luZyAnQkVHSU4tU1FM Li4uRU5ELVNRTCcgPC9GT05UPg0KPC9QPg0KDQo8UD48Rk9OVCBTSVpFPTIgRkFDRT0iQXJpYWwi PmFueW9uZSBoYXZlIGRvbmUgdGhpcyBiZWZvcmUgPyBwbGVhIGxldCBtZSBrbm93IGlmIGFueW9u ZSBoYXZlIG1vcmUgaW5mby4gb24gdGhpcyAuPC9GT05UPg0KPEJSPjxGT05UIFNJWkU9MiBGQUNF PSJBcmlhbCI+Jm5ic3A7PC9GT05UPg0KPEJSPjxGT05UIFNJWkU9MiBGQUNFPSJBcmlhbCI+VGhh bmsgeW91LDwvRk9OVD4NCjwvUD4NCg0KPFA+PEZPTlQgU0laRT0yIEZBQ0U9IkFyaWFsIj5QYW5r YWo8L0ZPTlQ+DQo8L1A+DQoNCjwvQk9EWT4NCjwvSFRNTD4NCg== --0__=pcU9TRtkwC7FuWpUrtBjlFVXge4SWxMIFi2Hhj3hJPMbHJrfve6iUSQZ-- From owner-sqr-users@list.iex.net Fri Jul 2 05:26:58 1999 Date: Fri, 2 Jul 1999 22:12:43 +1200 From: "Wallach, Jarod" Subject: PeopleSoft Object fax for sending Purchase Orders This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_000_01BEC473.7797AEE8 Content-Type: text/plain; charset="iso-8859-1" Hi all, we are installing object fax 6 for a client, they are on the following platform Application Financials 7.51 Tools 7.53 Database Oracle 8.04 OpSystem NT4 sp4 the fax server is also on a NT4 sp4 compaq they are using this software to fax there purchase orders in the first instance, this might grow in the future, with the installation we have hit a problem as follows. when printing purchase orders we print a logo in the top left hand corner, this logo is a .bmp file. the purchase order print fine when printer from peoplesoft in both 2 and 3-tier when using the -printer:wp command the problem is this, when you send this through the fax server, the following happens. 1) the logo does not come out correctly, we get a greyed out box where the logo should, I know this usually means that the logo can't be printed. 2) the fonts are for the most part correct however a few of the fields have shifted to the right. 3) the entire document is shifted about 1cm to the right of the page does anyone have any idea as to the cause of the problems, we suspect that this might be related to the print drivers/ font types on the server, but are not sure what to check. we are not sending this in post script mode. any help would be appreciated Jarod Wallach kpmg Enabling Technologies e-mail jwallach@kpmg.co.nz Consultant, KPMG 135 Victoria Street P.O. Box 996 KPMG Wellington Phone +64 4 3828800 x8886 New Zealand Fax +64 4 8021221 Disclaimer: The information in this electronic mail message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this Internet electronic mail message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. When addressed to our clients any opinions or advice contained in this Internet electronic mail message are subject to the terms and conditions expressed in the governing KPMG client engagement letter ------_=_NextPart_000_01BEC473.7797AEE8 Content-Type: application/octet-stream; name="Jarod Wallach (E-mail).vcf" Content-Disposition: attachment; filename="Jarod Wallach (E-mail).vcf" BEGIN:VCARD VERSION:2.1 N:Wallach;Jarod FN:Jarod Wallach (E-mail) ORG:KPMG New Zealand;Enabling Technologies TITLE:Consultant TEL;WORK;VOICE:+64 (4) 382 8800 TEL;WORK;FAX:+64 (4) 802 1221 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;KPMG Centre=0D=0A135 Victoria Street=0D=0A(PO Box 996);Wellington;;;New Ze= aland LABEL;WORK;ENCODING=QUOTED-PRINTABLE:KPMG Centre=0D=0A135 Victoria Street=0D=0A(PO Box 996)=0D=0AWellington=0D= =0ANew Zealand EMAIL;PREF;INTERNET:jwallach@kpmg.co.nz REV:19990505T035936Z END:VCARD ------_=_NextPart_000_01BEC473.7797AEE8-- From owner-sqr-users@list.iex.net Fri Jul 2 07:49:05 1999 Date: Fri, 2 Jul 1999 08:34:19 -0400 From: Sherry Jin Subject: Re: Print last page Kris, I believe this is a problem generated within peoplesoft. SQRIBE doesn't recommend attaching printer name after -f (The recommendation is to attach file directory/file after -f). In Peoplesoft, When printer is specified as the output, peoplesoft generates -fLPT2 into the parameter list. Last-Page tries to generate a buffer file to be able to compute last page. Mostly likely, it considers LPT2 as the directory file and attempts to open it as a file. I left a voice mail for peoplesoft regarding this case. Sherry At 09:58 AM 7/1/99 -0700, Kris Narravula wrote: >Robbi, > >I am not trying to create multiple reports and I am also not trying to do >any manipulation of Page Numbers, etc. My question is very simple, I am >getting the error while I am trying to run the report to the printer via >PeopleSoft with SQR's LAST-PAGE command in the report. It works fine when I >run it to the file from the same PeopleSoft Menu. > >The error I am getting is >(SQR 6002) Cannot open the printer file: \\cairo\infosrvs.spf >(2): No such file or directory > >It is not addressed yet. > >Thanks in advance. >Kris > >----- Original Message ----- >From: Wendel, Robbi >Sent: Thursday, July 01, 1999 4:59 AM >Subject: Re: Print last page > > >I apologize if this is something that you have already addressed or answered >previously, but have you considered trying to use NEW-REPORT instead? It >resets the internal page counter and permits multiple reports from same SQR. >I didn't backtrack to look at all information regarding this posting to >date, so you may have already addressed this or ruled this out. > >Have a great day, >Robbi > >-----Original Message----- >From: Kris Narravula [mailto:kris_narravula@HOTMAIL.COM] >Sent: Wednesday, June 30, 1999 3:57 PM >To: Multiple recipients of list SQR-USERS >Subject: Re: Print last page > > >Hi John, Sherry and Every Body, > >I got the same problem and wanted to post it. At the same time I saw the >request on the list and looking for the outcome. > >I am getting the following error when I use LAST-PAGE command to print the >last page number of the report on every page header as 'page 1 of 99' and >run it to a printer from PeopleSoft. It works fine when I run the report to >a file from the same PeopleSoft. > >(SQR 6002) Cannot open the printer file: \\cairo\infosrvs.spf >(2): No such file or directory > >When I remove the LAST-PAGE command and run the report it works fine with >out any error to the file as well as to the printer from PeopleSoft. > >We are on >Oracle 7.3.4.3.1 and is on HPUX 10.20 & >WinNT 4.0 File Server, >SQR 4.3.2 >PeopleSoft HRMS 7.50.00.000 >PeopleTools 7.54.10 > >I am sorry, I may be giving a bit of inconvenience to the only SQR users. I >am posing this here as similar issue is in discussion. > > >Any body has any clues. > > >Thanks in advance > >Kris > >----- Original Message ----- >From: Sherry Jin >Sent: Wednesday, June 30, 1999 12:55 PM >Subject: Re: Print last page > > >John, > >Thanks for the response. > >The problem is resolved if I add -printer:WP into the SQR flag in config >manager and specify file as the output destination. > >What happens is that whenever printer (Say LPT2) is selected as the output, >peoplesoft generates -flpt2 into the process parameter. I believe >"last-page" tries to generate a temp buffer file and considers lpt2 as the >output directory/file. > >Sherry > >At 03:02 PM 6/30/99 -0400, John Milardovic wrote: >>Do you mean 'print-direct'? or -printer:WP? or are you piping to the >printer >>in UNIX? >> >>If print-direct the problem might be that print-direct sends output >directly >>to the printer (i.e.. bypassing the SQR buffer) and so SQR can't calculate >>the number of pages in the report. >> >>John Milardovic >> >>> -----Original Message----- >>> From: Sherry Jin [SMTP:sjin@WINSTAR.COM] >>> Sent: Wednesday, June 30, 1999 1:54 PM >>> To: Multiple recipients of list SQR-USERS >>> Subject: Print last page >>> >>> I am trying to use last-page to print out the total numbers of pages on >>> each page. It works when I use file as the output destination, but >>> receives >>> error message 'Error reading the printer file (22) Invalid argument' when >>> printer is used as the output destination. >>> >>> This is what I added to the code: >>> >>> page-number (1,1) 'Page ' >>> last-page () ' Of ' >>> >>> Adding command last-page causes the error when output is printer. >>> Has anyone been able to invoke last-page and send the output directly to >>> printer? >>> >>> Thanks for any help, >>> Sherry >> >> > > From owner-sqr-users@list.iex.net Fri Jul 2 08:50:19 1999 Date: Fri, 2 Jul 1999 09:37:21 -0400 From: "White, Denise" Subject: Upward Compatibility of SQR Versions I'm hoping someone can save us some time in our upgrade testing by either assuring me of upward compatibility between versions 3 and 4, or pointing me towards potential trouble spots. We currently are running SQR 3.0.18.1.1. We are an Oracle shop, and have two systems that use SQR - PeopleSoft HRMS, and a non-PeopleSoft ERP system (Point.Man). Our ERP system is so heavily customized that a decision has been made to never upgrade it (I'd like to state that I support HRMS and not ERP!). We need to upgrade PeopleSoft to Tools 7.05.10 and HRMS 7.02. This involves an upgrade to SQR 4.3.2. Our DBA strongly feels that he wants only one version of SQR running for both systems. He plans to do some testing of the ERP SQRs, but he assumes that any SQR written in version 2 or 3 will run successfully in version 4. I have heard at a PeopleSoft User Group meeting that it is extremely important to thoroughly test every SQR program, which leads me to believe there may be some incompatibilities. Can anyone shed some light on this issue? One thing I have heard is that in SQR 4, you cannot do "alter session set nls_date_format = 'DD-MON-YYYY';". Unfortunately, I have just recently modified most of my SQRs to use this, after having found that my date math was not working correctly and was not including century in the calculations (another interesting decision had been made here to leave the Oracle default at DD-MON-YY and just program around it for Y2K compliance)! This had seemed by far the easiest solution - how do I do it in SQR 4? Thanks in advance! Denise White Sr IT Application Developer Textron Systems From owner-sqr-users@list.iex.net Fri Jul 2 10:39:33 1999 Date: Fri, 2 Jul 1999 09:58:52 -0400 From: Sam Spritzer Subject: Re: Upward Compatibility of SQR Versions Denise... I don't have the complete URL but if you head over to www.ontko.com you'll find a chart that shows the various commands over the all the versions of SQR. Hope this helps, Sam <<< "White, Denise" 7/ 2 9:37a >>> I'm hoping someone can save us some time in our upgrade testing by either assuring me of upward compatibility between versions 3 and 4, or pointing me towards potential trouble spots. We currently are running SQR 3.0.18.1.1. We are an Oracle shop, and have two systems that use SQR - PeopleSoft HRMS, and a non-PeopleSoft ERP system (Point.Man). Our ERP system is so heavily customized that a decision has been made to never upgrade it (I'd like to state that I support HRMS and not ERP!). We need to upgrade PeopleSoft to Tools 7.05.10 and HRMS 7.02. This involves an upgrade to SQR 4.3.2. Our DBA strongly feels that he wants only one version of SQR running for both systems. He plans to do some testing of the ERP SQRs, but he assumes that any SQR written in version 2 or 3 will run successfully in version 4. I have heard at a PeopleSoft User Group meeting that it is extremely important to thoroughly test every SQR program, which leads me to believe there may be some incompatibilities. Can anyone shed some light on this issue? One thing I have heard is that in SQR 4, you cannot do "alter session set nls_date_format = 'DD-MON-YYYY';". Unfortunately, I have just recently modified most of my SQRs to use this, after having found that my date math was not working correctly and was not including century in the calculations (another interesting decision had been made here to leave the Oracle default at DD-MON-YY and just program around it for Y2K compliance)! This had seemed by far the easiest solution - how do I do it in SQR 4? Thanks in advance! Denise White Sr IT Application Developer Textron Systems From owner-sqr-users@list.iex.net Fri Jul 2 09:47:19 1999 Date: Fri, 2 Jul 1999 09:35:31 -0500 From: Nathan Stratton Treadway Subject: Re: Print last page On Wed, Jun 30, 1999 at 03:55:58PM -0400, Sherry Jin wrote: > The problem is resolved if I add -printer:WP into the SQR flag in config > manager and specify file as the output destination. > > What happens is that whenever printer (Say LPT2) is selected as the output, > peoplesoft generates -flpt2 into the process parameter. I believe > "last-page" tries to generate a temp buffer file and considers lpt2 as the > output directory/file. Yes, you are correct: "last-pages causes SQR to generate an .SPF file, a process which fails if you've specified -flpt2 (because lpt2.spf is an invalid file). The -printer:wp is probably your best workaround, though it can take a bit of work to set up PeopleSoft to handle it. Hopefully, later versions of PeopleSoft will have "native" support, rather than trying to use "-flpt2". (Also, -printer:wp doesn't work under Windows 95 with most versions of SQRW 3.x, which is a problem for sites who haven't upgraded yet.) I wrote a rather lengthy document that explains this problem in more detail (including a list of "SPF File Triggers"). See http://www.sqrug.com/ftp/docs/sqr-windows-printing-howto.html Nathan ---------------------------------------------------------------------------- Nathan Stratton Treadway | Ray Ontko & Co. | Software consulting services nathant@ontko.com | Richmond, IN | http://www.ontko.com/ From owner-sqr-users@list.iex.net Fri Jul 2 10:51:31 1999 Date: Fri, 2 Jul 1999 08:39:20 -0700 From: Pankaj Bedekar Subject: Re: Print last page This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BEC4A1.0D7E7E48 Content-Type: text/plain; charset="iso-8859-1" Kris Sherry is right. when you use LAST-PAGE SQR will put all the output in buffer cause it need to compute total page count before printing. SQR tries to save this buffer file (same name as output destinationin directory where SQRs are resided and if that directory (\\cairo\) does not have permission for users to write the SQR will raise error saying cannot save the file or whatever. if u make that directory allowed to write no error will come but u end up users tampering that files ..... all the best Pankaj Bedekar -----Original Message----- From: Sherry Jin [mailto:sjin@WINSTAR.COM] Sent: Friday, July 02, 1999 5:34 AM To: Multiple recipients of list SQR-USERS Subject: Re: Print last page Kris, I believe this is a problem generated within peoplesoft. SQRIBE doesn't recommend attaching printer name after -f (The recommendation is to attach file directory/file after -f). In Peoplesoft, When printer is specified as the output, peoplesoft generates -fLPT2 into the parameter list. Last-Page tries to generate a buffer file to be able to compute last page. Mostly likely, it considers LPT2 as the directory file and attempts to open it as a file. I left a voice mail for peoplesoft regarding this case. Sherry At 09:58 AM 7/1/99 -0700, Kris Narravula wrote: >Robbi, > >I am not trying to create multiple reports and I am also not trying to do >any manipulation of Page Numbers, etc. My question is very simple, I am >getting the error while I am trying to run the report to the printer via >PeopleSoft with SQR's LAST-PAGE command in the report. It works fine when I >run it to the file from the same PeopleSoft Menu. > >The error I am getting is >(SQR 6002) Cannot open the printer file: \\cairo\infosrvs.spf >(2): No such file or directory > >It is not addressed yet. > >Thanks in advance. >Kris > >----- Original Message ----- >From: Wendel, Robbi >Sent: Thursday, July 01, 1999 4:59 AM >Subject: Re: Print last page > > >I apologize if this is something that you have already addressed or answered >previously, but have you considered trying to use NEW-REPORT instead? It >resets the internal page counter and permits multiple reports from same SQR. >I didn't backtrack to look at all information regarding this posting to >date, so you may have already addressed this or ruled this out. > >Have a great day, >Robbi > >-----Original Message----- >From: Kris Narravula [mailto:kris_narravula@HOTMAIL.COM] >Sent: Wednesday, June 30, 1999 3:57 PM >To: Multiple recipients of list SQR-USERS >Subject: Re: Print last page > > >Hi John, Sherry and Every Body, > >I got the same problem and wanted to post it. At the same time I saw the >request on the list and looking for the outcome. > >I am getting the following error when I use LAST-PAGE command to print the >last page number of the report on every page header as 'page 1 of 99' and >run it to a printer from PeopleSoft. It works fine when I run the report to >a file from the same PeopleSoft. > >(SQR 6002) Cannot open the printer file: \\cairo\infosrvs.spf >(2): No such file or directory > >When I remove the LAST-PAGE command and run the report it works fine with >out any error to the file as well as to the printer from PeopleSoft. > >We are on >Oracle 7.3.4.3.1 and is on HPUX 10.20 & >WinNT 4.0 File Server, >SQR 4.3.2 >PeopleSoft HRMS 7.50.00.000 >PeopleTools 7.54.10 > >I am sorry, I may be giving a bit of inconvenience to the only SQR users. I >am posing this here as similar issue is in discussion. > > >Any body has any clues. > > >Thanks in advance > >Kris > >----- Original Message ----- >From: Sherry Jin >Sent: Wednesday, June 30, 1999 12:55 PM >Subject: Re: Print last page > > >John, > >Thanks for the response. > >The problem is resolved if I add -printer:WP into the SQR flag in config >manager and specify file as the output destination. > >What happens is that whenever printer (Say LPT2) is selected as the output, >peoplesoft generates -flpt2 into the process parameter. I believe >"last-page" tries to generate a temp buffer file and considers lpt2 as the >output directory/file. > >Sherry > >At 03:02 PM 6/30/99 -0400, John Milardovic wrote: >>Do you mean 'print-direct'? or -printer:WP? or are you piping to the >printer >>in UNIX? >> >>If print-direct the problem might be that print-direct sends output >directly >>to the printer (i.e.. bypassing the SQR buffer) and so SQR can't calculate >>the number of pages in the report. >> >>John Milardovic >> >>> -----Original Message----- >>> From: Sherry Jin [SMTP:sjin@WINSTAR.COM] >>> Sent: Wednesday, June 30, 1999 1:54 PM >>> To: Multiple recipients of list SQR-USERS >>> Subject: Print last page >>> >>> I am trying to use last-page to print out the total numbers of pages on >>> each page. It works when I use file as the output destination, but >>> receives >>> error message 'Error reading the printer file (22) Invalid argument' when >>> printer is used as the output destination. >>> >>> This is what I added to the code: >>> >>> page-number (1,1) 'Page ' >>> last-page () ' Of ' >>> >>> Adding command last-page causes the error when output is printer. >>> Has anyone been able to invoke last-page and send the output directly to >>> printer? >>> >>> Thanks for any help, >>> Sherry >> >> > > ------_=_NextPart_001_01BEC4A1.0D7E7E48 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Print last page

Kris

Sherry is right. when you use LAST-PAGE SQR will put = all the output in buffer cause it need to compute total page count = before printing. SQR tries to save this buffer file (same name as = output destinationin directory where SQRs are resided and if that = directory (\\cairo\) does not have permission for users to write the = SQR will raise error saying cannot save the file or whatever. if u make = that directory allowed to write no error will come but u end up users = tampering that files .....

all the best

Pankaj Bedekar

-----Original Message-----
From: Sherry Jin [mailto:sjin@WINSTAR.COM]
Sent: Friday, July 02, 1999 5:34 AM
To: Multiple recipients of list SQR-USERS
Subject: Re: Print last page


Kris,

I believe this is a problem generated within = peoplesoft. SQRIBE doesn't
recommend attaching printer name after -f (The = recommendation is to attach
file directory/file after -f). In Peoplesoft,  = When printer is specified as
the output, peoplesoft generates -fLPT2 into the = parameter list. Last-Page
tries to generate a buffer file to be able to = compute last page. Mostly
likely, it considers LPT2 as the directory file and = attempts to open it as
a file.

I left a voice mail for peoplesoft regarding this = case.

Sherry
At 09:58 AM 7/1/99 -0700, Kris Narravula = wrote:
>Robbi,
>
>I am not trying to create multiple reports and I = am also not trying to do
>any manipulation of Page Numbers, etc. My = question is very simple, I am
>getting the error while I am trying to run the = report to the printer via
>PeopleSoft with SQR's LAST-PAGE command in the = report. It works fine when I
>run it to the file from the same PeopleSoft = Menu.
>
>The error I am getting is
>(SQR 6002) Cannot open the printer file: = \\cairo\infosrvs.spf
>(2): No such file or directory
>
>It is not addressed yet.
>
>Thanks in advance.
>Kris
>
>----- Original Message -----
>From: Wendel, Robbi = <rwendel@NESPOWER.COM>
>Sent: Thursday, July 01, 1999 4:59 AM
>Subject: Re: Print last page
>
>
>I apologize if this is something that you have = already addressed or answered
>previously, but have you considered trying to = use NEW-REPORT instead? It
>resets the internal page counter and permits = multiple reports from same SQR.
>I didn't backtrack to look at all information = regarding this posting to
>date, so you may have already addressed this or = ruled this out.
>
>Have a great day,
>Robbi
>
>-----Original Message-----
>From: Kris Narravula [mailto:kris_narravula@HOTMAIL= .COM]
>Sent: Wednesday, June 30, 1999 3:57 PM
>To: Multiple recipients of list SQR-USERS
>Subject: Re: Print last page
>
>
>Hi  John, Sherry and Every Body,
>
>I got the same problem and wanted to post it. At = the same time I saw the
>request on the list and looking for the = outcome.
>
>I am getting the following error when I use = LAST-PAGE command to print the
>last page number of the report on every page = header as 'page 1 of  99' and
>run it to a printer from PeopleSoft. It works = fine when I run the report to
>a file from the same PeopleSoft.
>
>(SQR 6002) Cannot open the printer file: = \\cairo\infosrvs.spf
>(2): No such file or directory
>
>When I remove the LAST-PAGE command and run the = report it works fine with
>out any error to  the file as well as to = the printer from PeopleSoft.
>
>We are on
>Oracle 7.3.4.3.1 and is on HPUX 10.20 = &
>WinNT 4.0 File Server,
>SQR 4.3.2
>PeopleSoft HRMS 7.50.00.000
>PeopleTools 7.54.10
>
>I am sorry, I may be giving a bit of = inconvenience to the only SQR users. I
>am posing this here as similar issue is in = discussion.
>
>
>Any body has any clues.
>
>
>Thanks in advance
>
>Kris
>
>----- Original Message -----
>From: Sherry Jin <sjin@WINSTAR.COM>
>Sent: Wednesday, June 30, 1999 12:55 PM
>Subject: Re: Print last page
>
>
>John,
>
>Thanks for the response.
>
>The problem is resolved if I add -printer:WP = into the SQR flag in config
>manager and specify file as the output = destination.
>
>What happens is that whenever printer (Say LPT2) = is selected as the output,
>peoplesoft generates -flpt2 into the process = parameter. I believe
>"last-page" tries to generate a temp = buffer file and considers lpt2 as the
>output directory/file.
>
>Sherry
>
>At 03:02 PM 6/30/99 -0400, John Milardovic = wrote:
>>Do you mean 'print-direct'? or -printer:WP? = or are you piping to the
>printer
>>in UNIX?
>>
>>If print-direct the problem might be that = print-direct sends output
>directly
>>to the printer (i.e.. bypassing the SQR = buffer) and so SQR can't calculate
>>the number of pages in the report.
>>
>>John Milardovic
>>
>>> -----Original Message-----
>>> From: Sherry Jin = [SMTP:sjin@WINSTAR.COM]
>>> Sent: Wednesday, June 30, 1999 1:54 = PM
>>> To:   Multiple recipients of = list SQR-USERS
>>> Subject:      = Print last page
>>>
>>> I am trying to use last-page to print = out the total numbers of pages on
>>> each page. It works when I use file as = the output destination, but
>>> receives
>>> error message 'Error reading the = printer file (22) Invalid argument' when
>>> printer is used as the output = destination.
>>>
>>> This is what I added to the = code:
>>>
>>> page-number (1,1) 'Page '
>>> last-page   = ()    ' Of '
>>>
>>> Adding command last-page causes the = error when output is printer.
>>> Has anyone been able to invoke = last-page and send the output directly to
>>> printer?
>>>
>>> Thanks for any help,
>>> Sherry
>>
>>
>
>

------_=_NextPart_001_01BEC4A1.0D7E7E48-- From owner-sqr-users@list.iex.net Fri Jul 2 12:53:31 1999 Date: Fri, 2 Jul 1999 12:41:10 -0500 From: Nathan Stratton Treadway Subject: Re: Upward Compatibility of SQR Versions On Fri, Jul 02, 1999 at 09:37:21AM -0400, White, Denise wrote: > one version of SQR running for both systems. He plans to do some testing of > the ERP SQRs, but he assumes that any SQR written in version 2 or 3 will run > successfully in version 4. I have heard at a PeopleSoft User Group meeting > that it is extremely important to thoroughly test every SQR program, which > leads me to believe there may be some incompatibilities. Can anyone shed > some light on this issue? Yes, there are some incompatibilities, and you should plan to spend a little time making the switch. Most of the changes are very simple, and involve compile-time error messages. Thus, you can simply set up a batch job to do an "sqr -rs" on all of your source programs and look for the programs that don't compile. For example, if you have a move $string_var to #num_var 9,999 command (that is, an edit mask on a move TO a #num_var), the bogus edit mask will now cause a compile-time error. You can simple delete the edit mask and recompile the program. If you are doing a lot of date manipulations, moving those to SQR 4 is a little harder because sometimes the problems don't show up until run time. It's well worth upgrading to use SQR v4's new date features, but if you don't do thorough pre-testing you may stumble across problems as your users run certain programs for the first time under SQR 4. In very rare situations you may run into trouble with the new, more precise numerice variables in SQR 4 (if your program somehow relied on rounding errors, for example). You may need to look at DEFAULT-NUMERIC in SQR.INI. > One thing I have heard is that in SQR 4, you cannot do "alter session set > nls_date_format = 'DD-MON-YYYY';". Unfortunately, I have just recently > modified most of my SQRs to use this, after having found that my date math > was not working correctly and was not including century in the calculations > (another interesting decision had been made here to leave the Oracle default > at DD-MON-YY and just program around it for Y2K compliance)! This had > seemed by far the easiest solution - how do I do it in SQR 4? You can still use nls_date_format; it's just that SQR 4 now does its own conversions from date columns, ignoring this setting when you directly select a date column into an SQR &variable. (Thenls_date_format would still be used if you did a begin-select .... from XXXX where to_char(DATE_COLUMN) like '%-1997' end-select Note that I'd never recommend this particular where clause; the point is that nls_date_format might still be needed in some situations.) The answer to your specific question is that you want to look at the SQR.INI setting SQR_DB_DATE_FORMAT. Nathan ---------------------------------------------------------------------------- Nathan Stratton Treadway | Ray Ontko & Co. | Software consulting services nathant@ontko.com | Richmond, IN | http://www.ontko.com/ From owner-sqr-users@list.iex.net Fri Jul 2 14:20:52 1999 Date: Fri, 2 Jul 1999 12:03:33 -0700 From: Kris Narravula Subject: Re: Print last page This is a multi-part message in MIME format. ------=_NextPart_000_0070_01BEC482.E860E790 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Print last pageThanks for everybody responded. I am opening a case = with PeopleSoft to rectify the problem. It seems it is a bug in = PeopleSoft. They might have over looked it. Thanks again Kris ------=_NextPart_000_0070_01BEC482.E860E790 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Print last page
Thanks for everybody responded. I am = opening a case=20 with PeopleSoft to rectify the problem. It seems it is a bug in = PeopleSoft. They=20 might have over looked it.
 
Thanks again
 
Kris
 
 

 

------=_NextPart_000_0070_01BEC482.E860E790-- From owner-sqr-users@list.iex.net Sat Jul 3 11:01:18 1999 Date: Sat, 3 Jul 1999 21:22:21 +0530 From: Shankar Subject: Info Needed... Hi all, I am glad to join you all. Can anybody explain me more about the following in SQR Report (since I hv just using SQR Reports) : print &sYearInstal () ON-BREAK PRINT=NEVER AFTER=YEAR_BREAK LEVEL=1 SAVE=$sLastYearInstal BEFORE=PRINT_TITLE print &sMinorCause () ON-BREAK PRINT=NEVER AFTER=MINOR_BREAK LEVEL=2 SAVE=$sLastMnCause How the break is fired , about the Level ...etc Thanks in Advance, Shankar Swamy. From owner-sqr-users@list.iex.net Wed Jul 7 01:43:16 1999 Date: Mon, 5 Jul 1999 08:34:58 +0100 From: Franck Masson Subject: Re: HTML Table of Contents examples I will recommand you to use visualsqribe or to upgrade your visualsqribe. It is a feature that it does by just clicking on the mouse. franck, !-------------------------------------------------------------------------------- ! Generated on Mon Jul 05 08:32:49 1999 by VisualSQRIBE 4.4.0.17 ! ! Filename: C:\sqribe\VisualSQRIBE\PROGRAM\sans.sqr ! Format : Tabular ! Username: S !-------------------------------------------------------------------------------- Begin-Setup Declare-Layout Default Orientation = Portrait Paper-Size = (Letter) Top-Margin = 0.50 Bottom-Margin = 0.50 Left-Margin = 0.50 Right-Margin = 0.50 Line-Height = 1 Char-Width = 1 End-Declare Declare-TOC Default Entry = VS_TOC_Proc End-Declare End-Setup Begin-Heading 22 For-Tocs=(default) Graphic (21,1,540) Horz-Line 20 Alter-Printer Font=4 Point-Size=16 Print 'Table of Contents' (15,415,17) Print-Direct printer=html '%%TOC-Title Table of Contents' Alter-Printer Font=5 Point-Size=10 End-Heading Begin-Procedure VS_TOC_Proc Alter-Printer Font=4 Point-Size=10 Let #indent-size = 24 Let #indentation = 1 + (#indent-size * (#sqr-toc-level - 1)) Print $sqr-toc-text (12,#indentation) Print #sqr-toc-page (12,523) Next-Listing Skiplines=4 Need=14 End-Procedure Begin-Program Do Main End-Program Begin-Procedure Main Position (1,1) Do CreateCSV_Main Print-Direct printer=html '%%ResetColor' Print-Direct printer=html '%%ResetBorder' Begin-Select Alter-Printer Font=4 Point-Size=10 ENAME (10,1,10) Toc-Entry text=&ename level=1 COMM (10,128) Edit 9999999999999999na SAL (10,288) Edit 99999.99na let $COMM_num = to_char(&COMM) let $COMM_num_tmp = edit(&COMM,'999999999999999999999999999999999999999') let $COMM_num = ltrim($COMM_num_tmp,' ') let $SAL_num = to_char(&SAL) let $SAL_num_tmp = edit(&SAL,'99999.99') let $SAL_num = ltrim($SAL_num_tmp,' ') write 1 from '"' &ENAME '"' ',' $COMM_num ',' $SAL_num Next-Listing SkipLines=2 Need=12 >From EMP End-Select Next-Listing End-Procedure Begin-Procedure CreateCSV_Main Add 1 To #_CSVINDEX_Main Let $_CSVFILE_Main='C:/sqribe/VisualSQRIBE/PROGRAM/html/sans_Main_' || to_char(#_CSVINDEX_Main) || '.csv' Open $_CSVFILE_Main as 1 for-writing record=255:vary write 1 from '"ENAME"' ',' '"COMM"' ',' '"SAL"' End-Procedure Begin-Heading 45 Print-Direct printer=html '%%ResetColor' Print-Direct printer=html '%%ResetBorder' Alter-Printer Font=5 Point-Size=10 Print $current-date (10,1) edit 'MM/DD/YY' Page-Number (10,523) Alter-Printer Font=4 Point-Size=10 Print 'Ename' (33,1,5) Underline Bold Print 'Comm' (33,128,4) Underline Bold Print 'Sal' (33,288,3) Underline Bold End-Heading Tonie Salzano wrote: > > Does anyone have an example of an SQR that creates a > Table of Contents that shows a link on a data item, > e.g. deptid, instead of page? > > We have a check distribution report that we need > to make available to our remote campuses. > > Thanks > Tonie Salzano > Maricopa Community College District From owner-sqr-users@list.iex.net Wed Jul 7 18:06:00 1999 Date: Tue, 6 Jul 1999 01:00:15 +0100 From: Franck Masson Subject: Re: processing a .dbf file have you try to use SQR with ODBC driver ? It can be a solution if you have ODBC driver for foxpro. Franck, S+A Sills wrote: > > take a look at the stuff in > > http://www.egroups.com/docvault/psusers/ > > folder named > > sqr to write dbf file for excel , access > > this will ident how to write a dbf file. it also has a descr of a dbf > version VI file format. > > this amy give some hints on how to read. you will be able to address the > field descriptor array and the start of data. From owner-sqr-users@list.iex.net Tue Jul 6 09:41:28 1999 Date: Tue, 6 Jul 1999 10:23:49 -0400 From: "Dray, Adam" Subject: Re: Info Needed... [Shankar Swamy asks how breaks are processed in SQR...] The SQR Language Reference (version 4), page 219, in the section for the Print command includes these details on the On-Break field: "Following is the sequence of events for a query containing ON-BREAK fields: 1. Any BEFORE procedures are processed in ascending LEVEL sequence before the first row of the query is retrieved. 2. When a break occurs in the query, the following happens: a. AFTER procedures are processed in descending sequence from the highest level to the level of the current break field. b. SAVE variables are set with the new value. c. BEFORE procedures are processed in ascending sequence from the current level to the highest level break. d. Any breaks with the same or higher level numbers are cleared so they will not break on the next value. e. If a PROCEDURE has been declared, it is invoked. f. If SKIPLINES was specified, the current line position is advanced. h. The value is printed (unless PRINT=NEVER was specified). 3. After the query finishes (at END-SELECT) any AFTER procedures are processed in descending level sequence." The manual continues with some good examples. I strongly suggest getting access to a copy of the SQR Language Reference and the SQR User's Guide. Many questions that SQR novitiates have can be answered in a few seconds with a quick thumbing of the manuals. Adam Dray From owner-sqr-users@list.iex.net Tue Jul 6 10:13:52 1999 Date: Tue, 6 Jul 1999 11:00:39 -0400 From: "Weaver, Judith R" Subject: Re: Info Needed - SQR on-break logic. I agree with everything you say except item 1. Unless I'm doing something wrong, it appears to me that all BEFORE procedures are processed immediately after the return of the first row of data. I wrote this little sqr to demonstrate for myself when things happen. I've also created a bmp (800x600) and a jpg image of a flow chart if anyone is interested. This particular sqr accesses the PeopleSoft HRMS demo database (version 6.x). If you want to test it out, you'll have to modify it as appropriate. I then uploaded the sqr.log into Excel. (It's a tab delimited file) > ---------- > From: Dray, Adam[SMTP:Adam.Dray@PHH.COM] > Sent: Tuesday, July 06, 1999 10:23 AM > Subject: Re: Info Needed... > > [Shankar Swamy asks how breaks are > processed in SQR...] > > > The SQR Language Reference (version 4), page 219, in the section for > the Print command includes these details on the On-Break field: > > "Following is the sequence of events for a query containing ON-BREAK > fields: > > 1. Any BEFORE procedures are processed in ascending LEVEL sequence > before the first row of the query is retrieved. > > 2. When a break occurs in the query, the following happens: > a. AFTER procedures are processed in descending sequence from the > highest level to the level of the current break field. > b. SAVE variables are set with the new value. > c. BEFORE procedures are processed in ascending sequence from the > current level to the highest level break. > d. Any breaks with the same or higher level numbers are cleared so > they will not break on the next value. > e. If a PROCEDURE has been declared, it is invoked. > f. If SKIPLINES was specified, the current line position is > advanced. > h. The value is printed (unless PRINT=NEVER was specified). > 3. After the query finishes (at END-SELECT) any AFTER procedures are > processed in descending level sequence." > > > The manual continues with some good examples. > > I strongly suggest getting access to a copy of the SQR Language Reference > and the SQR User's Guide. Many questions that SQR novitiates have can > be answered in a few seconds with a quick thumbing of the manuals. > > Adam Dray > From owner-sqr-users@list.iex.net Tue Jul 6 10:26:03 1999 Date: Tue, 6 Jul 1999 11:05:42 -0400 From: "Weaver, Judith R" Subject: Re: Info Needed - SQR on-break logic. This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_000_01BEC7C1.0501D7D8 Content-Type: text/plain Ooops! Forgot the attachment. <> > ---------- > From: Weaver, Judith > R[SMTP:WeaverJR@USAFOO.UNITEDSPACEALLIANCE.COM] > Sent: Tuesday, July 06, 1999 11:00 AM > Subject: Re: Info Needed - SQR on-break logic. > > I agree with everything you say except item 1. Unless I'm doing something > wrong, it appears to me that all BEFORE procedures are processed > immediately > after the return of the first row of data. > > I wrote this little sqr to demonstrate for myself when things happen. > I've > also created a bmp (800x600) and a jpg image of a flow chart if anyone is > interested. This particular sqr accesses the PeopleSoft HRMS demo > database > (version 6.x). If you want to test it out, you'll have to modify it as > appropriate. I then uploaded the sqr.log into Excel. (It's a tab > delimited > file) > > > ---------- > > From: Dray, Adam[SMTP:Adam.Dray@PHH.COM] > > Sent: Tuesday, July 06, 1999 10:23 AM > > Subject: Re: Info Needed... > > > > [Shankar Swamy asks how breaks are > > processed in SQR...] > > > > > > The SQR Language Reference (version 4), page 219, in the section for > > the Print command includes these details on the On-Break field: > > > > "Following is the sequence of events for a query containing ON-BREAK > > fields: > > > > 1. Any BEFORE procedures are processed in ascending LEVEL sequence > > before the first row of the query is retrieved. > > > > 2. When a break occurs in the query, the following happens: > > a. AFTER procedures are processed in descending sequence from the > > highest level to the level of the current break field. > > b. SAVE variables are set with the new value. > > c. BEFORE procedures are processed in ascending sequence from the > > current level to the highest level break. > > d. Any breaks with the same or higher level numbers are cleared so > > they will not break on the next value. > > e. If a PROCEDURE has been declared, it is invoked. > > f. If SKIPLINES was specified, the current line position is > > advanced. > > h. The value is printed (unless PRINT=NEVER was specified). > > 3. After the query finishes (at END-SELECT) any AFTER procedures are > > processed in descending level sequence." > > > > > > The manual continues with some good examples. > > > > I strongly suggest getting access to a copy of the SQR Language > Reference > > and the SQR User's Guide. Many questions that SQR novitiates have can > > be answered in a few seconds with a quick thumbing of the manuals. > > > > Adam Dray > > > ------_=_NextPart_000_01BEC7C1.0501D7D8 Content-Type: application/octet-stream; name="sqronbrk1.sqr" Content-Disposition: attachment; filename="sqronbrk1.sqr" !***********************************************************************! ! Program Number: sqronbrk.sqr ! ! Program Name: flow of break logic in sqr ! ! Programmer: Judith R. Weaver ! !-----------------------------------------------------------------------! ! Program Description: This process tests the flow of logic in sqr ! ! using the PeopleSoft HRMS demo database. ! !-----------------------------------------------------------------------! #include 'setenv.sqc' !set environment #include 'setup01.sqc' !set up printer ! portrait begin-program let $tab = chr(9) let $col_hdngs = 'procedure' ||$tab|| '&state' ||$tab|| '&city' ||$tab|| '&zip' ||$tab|| '&name' ||$tab|| '$state' ||$tab|| '$city' ||$tab|| '$zip' ||$tab|| '$name' ||$tab|| '$save_1' ||$tab|| '$save_2' ||$tab|| '$save_3' display $col_hdngs do procedure_1 end-program !--------------------------- begin-procedure procedure_1 begin-select state () on-break level=1 print=never before=before_1 after=after_1 save=$save_1 city () on-break level=2 print=never before=before_2 after=after_2 save=$save_2 zip () on-break level=3 print=never before=before_3 after=after_3 save=$save_3 name do do_stuff from ps_employees where country = 'USA' and city <> ' ' and zip <> ' ' and empl_rcd# = 0 and (state in ('CT','CO') or (state = 'CA' and city like 'A%') ) order by state, city, zip, name end-select end-procedure procedure_1 !--------------------------------- begin-procedure do_stuff move 'do_stuff' to $proc_name move &state to $state move &city to $city move &zip to $zip move &name to $name do display_values end-procedure do_stuff !--------------------------------- begin-procedure before_1 move 'before_1' to $proc_name do display_values end-procedure before_1 !--------------------------------- begin-procedure before_2 move 'before_2' to $proc_name do display_values end-procedure before_2 !--------------------------------- begin-procedure before_3 move 'before_3' to $proc_name do display_values end-procedure before_3 !--------------------------------- begin-procedure after_1 move 'after_1' to $proc_name do display_values end-procedure after_1 !--------------------------------- begin-procedure after_2 move 'after_2' to $proc_name do display_values end-procedure after_2 !--------------------------------- begin-procedure after_3 move 'after_3' to $proc_name do display_values end-procedure after_3 !--------------------------------- begin-procedure display_values string $proc_name &state &city &zip &name $state $city $zip $name $save_1 $save_2 $save_3 by $tab into $log_msg display $log_msg end-procedure display_values ------_=_NextPart_000_01BEC7C1.0501D7D8-- From owner-sqr-users@list.iex.net Tue Jul 6 11:19:59 1999 Date: Tue, 6 Jul 1999 11:04:38 -0500 From: Ray Ontko Subject: Re: Info Needed - SQR on-break logic. Judith, Technically, you are correct. The BEFORE procedures "fire" after the first row of the query is retrieved from the database but before the record is processed as the "current" row in SQR. In other words, all applicable BEFORE procedures are executed before any of the current row logic is applied (explicit SQR commands or implied print statements, for example). Ray > I agree with everything you say except item 1. Unless I'm doing something > wrong, it appears to me that all BEFORE procedures are processed immediately > after the return of the first row of data. > > I wrote this little sqr to demonstrate for myself when things happen. I've > also created a bmp (800x600) and a jpg image of a flow chart if anyone is > interested. This particular sqr accesses the PeopleSoft HRMS demo database > (version 6.x). If you want to test it out, you'll have to modify it as > appropriate. I then uploaded the sqr.log into Excel. (It's a tab delimited > file) > > > ---------- > > From: Dray, Adam[SMTP:Adam.Dray@PHH.COM] > > Sent: Tuesday, July 06, 1999 10:23 AM > > Subject: Re: Info Needed... > > > > [Shankar Swamy asks how breaks are > > processed in SQR...] > > > > > > The SQR Language Reference (version 4), page 219, in the section for > > the Print command includes these details on the On-Break field: > > > > "Following is the sequence of events for a query containing ON-BREAK > > fields: > > > > 1. Any BEFORE procedures are processed in ascending LEVEL sequence > > before the first row of the query is retrieved. > > > > 2. When a break occurs in the query, the following happens: > > a. AFTER procedures are processed in descending sequence from the > > highest level to the level of the current break field. > > b. SAVE variables are set with the new value. > > c. BEFORE procedures are processed in ascending sequence from the > > current level to the highest level break. > > d. Any breaks with the same or higher level numbers are cleared so > > they will not break on the next value. > > e. If a PROCEDURE has been declared, it is invoked. > > f. If SKIPLINES was specified, the current line position is > > advanced. > > h. The value is printed (unless PRINT=NEVER was specified). > > 3. After the query finishes (at END-SELECT) any AFTER procedures are > > processed in descending level sequence." > > > > > > The manual continues with some good examples. > > > > I strongly suggest getting access to a copy of the SQR Language Reference > > and the SQR User's Guide. Many questions that SQR novitiates have can > > be answered in a few seconds with a quick thumbing of the manuals. > > > > Adam Dray > > > ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | "RO&C: data movers and shakers." rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ From owner-sqr-users@list.iex.net Tue Jul 6 11:51:27 1999 Date: Tue, 6 Jul 1999 11:31:43 -0500 From: Zubin Shroff Subject: Loading Employee data into PS_JOB on AS400 We are trying to load employee data into PS_JOB and other tables using Import Manager. Import Manager errors out when trying to insert into PS_JOB, and we were told to use the JOB_IMPORT view for loading. We are running PS on an AS400, and we apparently cannot update or insert on views on the 400. Just wondering if someone else has run into a similar problem and how they resolved it. Also, any suggestions of alternative methods of loading employee data will be appreciated. Thanks. From owner-sqr-users@list.iex.net Tue Jul 6 13:43:02 1999 Date: Tue, 6 Jul 1999 11:28:39 PDT From: Ben Lawrence Subject: Converting from ASCII to EBCDIC Hi all, I am working on an interface which reports deductions for state retirement. The deduction field must be EBCDIC and it can only be 6 characters in length. I am looking for a way to convert the deduction field from ASCII to EBCDIC within SQR. Has anyone written an SQR that does this conversion? Any help would be greatly appreciated. Thanks, Ben _______________________________________________________________ Get Free Email and Do More On The Web. Visit http://www.msn.com From owner-sqr-users@list.iex.net Tue Jul 6 13:52:14 1999 Date: Tue, 6 Jul 1999 11:31:55 -0700 From: Clara Carter Subject: SQR report names Hi, Does anyone know if there is a length limit to the name of the .sqr files. We are on unix. Thanks Clara From owner-sqr-users@list.iex.net Tue Jul 6 13:56:23 1999 Date: Tue, 6 Jul 1999 14:32:13 -0400 From: Rosie ODonnell Subject: Special Characters in a string Hi, Hopefully, this is an easy one. Here is an example: update ps_dept_tble set descr = 'Research & Development' where deptid like '1847%' The ampersand(&) is looked at as a replacement variable. What do I need to surround it with so that the program ignores the ampersand and considers it part of the string? Thanks, Joe Patton Marconi Communications From owner-sqr-users@list.iex.net Tue Jul 6 13:59:47 1999 Date: Tue, 6 Jul 1999 14:48:25 -0400 From: Peter Alan Burton Subject: Re: SQR report names Clara, 1024 characters Peter On 6 Jul 99, at 11:31, Clara Carter wrote: Date sent: Tue, 6 Jul 1999 11:31:55 -0700 Send reply to: SQR-USERS@list.iex.net From: Clara Carter Subject: SQR report names To: Multiple recipients of list SQR-USERS > Hi, > > Does anyone know if there is a length limit to the name of the > .sqr files. We are on unix. > > Thanks > Clara > From owner-sqr-users@list.iex.net Tue Jul 6 14:06:07 1999 Date: Tue, 6 Jul 1999 14:52:52 -0400 From: John Milardovic Subject: Re: Special Characters in a string Hi Rosie. To escape a character just repeat it. && '' ## etc. John Milardovic > -----Original Message----- > From: Rosie ODonnell [SMTP:joe.patton@NA.MARCONICOMMS.COM] > Sent: Tuesday, July 06, 1999 2:32 PM > To: Multiple recipients of list SQR-USERS > Subject: Special Characters in a string > > Hi, > > Hopefully, this is an easy one. Here is an example: > > update ps_dept_tble > set descr = 'Research & Development' > where deptid like '1847%' > > The ampersand(&) is looked at as a replacement variable. What do I need > to > surround it with so that the program ignores the ampersand and considers > it part > of the string? > > Thanks, > > Joe Patton > Marconi Communications From owner-sqr-users@list.iex.net Tue Jul 6 14:09:04 1999 Date: Tue, 6 Jul 1999 13:56:07 CDT From: the dragon Subject: Re: Special Characters in a string Joe, How about taking the easy way out? Replace the & with AND. :-) Special charcaters are a pain in the butt. clark ----Original Message Follows---- Hi, Hopefully, this is an easy one. Here is an example: update ps_dept_tble set descr = 'Research & Development' where deptid like '1847%' The ampersand(&) is looked at as a replacement variable. What do I need to surround it with so that the program ignores the ampersand and considers it part of the string? Thanks, Joe Patton Marconi Communications _______________________________________________________________ Get Free Email and Do More On The Web. Visit http://www.msn.com From owner-sqr-users@list.iex.net Tue Jul 6 14:10:26 1999 Date: Tue, 6 Jul 1999 11:57:06 -0700 From: Pankaj Bedekar Subject: Re: Special Characters in a string This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BEC7E1.57B8CB10 Content-Type: text/plain; charset="iso-8859-1" Just use a single quote (') before & sign . pankaj -----Original Message----- From: Rosie ODonnell [mailto:joe.patton@NA.MARCONICOMMS.COM] Sent: Tuesday, July 06, 1999 11:32 AM To: Multiple recipients of list SQR-USERS Subject: Special Characters in a string Hi, Hopefully, this is an easy one. Here is an example: update ps_dept_tble set descr = 'Research & Development' where deptid like '1847%' The ampersand(&) is looked at as a replacement variable. What do I need to surround it with so that the program ignores the ampersand and considers it part of the string? Thanks, Joe Patton Marconi Communications ------_=_NextPart_001_01BEC7E1.57B8CB10 Content-Type: text/html; charset="iso-8859-1" RE: Special Characters in a string

Just use a single quote (') before & sign .

pankaj

-----Original Message-----
From: Rosie ODonnell [mailto:joe.patton@NA.MARCONICOMMS.COM]
Sent: Tuesday, July 06, 1999 11:32 AM
To: Multiple recipients of list SQR-USERS
Subject: Special Characters in a string


Hi,

Hopefully, this is an easy one.  Here is an example:

update ps_dept_tble
set descr = 'Research & Development'
where deptid like '1847%'

The ampersand(&) is looked at as a replacement variable.  What do I need to
surround it with so that the program ignores the ampersand and considers it part
of the string?

Thanks,

Joe Patton
Marconi Communications

------_=_NextPart_001_01BEC7E1.57B8CB10-- From owner-sqr-users@list.iex.net Tue Jul 6 14:09:19 1999 Date: Tue, 6 Jul 1999 14:00:28 -0500 From: "Wendel, Robbi" Subject: Re: Special Characters in a string You might try two single quotes -- ''&'' Robbi -----Original Message----- From: Rosie ODonnell [mailto:joe.patton@NA.MARCONICOMMS.COM] Sent: Tuesday, July 06, 1999 1:32 PM To: Multiple recipients of list SQR-USERS Subject: Special Characters in a string Hi, Hopefully, this is an easy one. Here is an example: update ps_dept_tble set descr = 'Research & Development' where deptid like '1847%' The ampersand(&) is looked at as a replacement variable. What do I need to surround it with so that the program ignores the ampersand and considers it part of the string? Thanks, Joe Patton Marconi Communications From owner-sqr-users@list.iex.net Tue Jul 6 14:20:04 1999 Date: Tue, 6 Jul 1999 14:06:48 -0500 From: Julie Waggoner Subject: Re: Special Characters in a string You can use the Chr() function which returns a character based on the ASCII number you provide. The ampersand is Chr(38). For instance: update ps_dept_tble set descr = 'Research ' || Chr(38) || ' Development' where deptid like '1847%' Julie -----Original Message----- From: Rosie ODonnell [mailto:joe.patton@NA.MARCONICOMMS.COM] Sent: Tuesday, July 06, 1999 1:32 PM To: Multiple recipients of list SQR-USERS Subject: Special Characters in a string Hi, Hopefully, this is an easy one. Here is an example: update ps_dept_tble set descr = 'Research & Development' where deptid like '1847%' The ampersand(&) is looked at as a replacement variable. What do I need to surround it with so that the program ignores the ampersand and considers it part of the string? Thanks, Joe Patton Marconi Communications From owner-sqr-users@list.iex.net Tue Jul 6 14:28:06 1999 Date: Tue, 6 Jul 1999 15:15:36 -0400 From: Arshad Pervez Subject: Re: Special Characters in a string > -----Original Message----- > From: Julie Waggoner [SMTP:WAGGO000@MAIL.GENMILLS.COM] > Sent: Tuesday, July 06, 1999 3:07 PM > To: Multiple recipients of list SQR-USERS > Subject: Re: Special Characters in a string > > You can use the Chr() function which returns a character based on the > ASCII > number you provide. The ampersand is Chr(38). > > For instance: > update ps_dept_tble > set descr = 'Research ' || Chr(38) || ' Development' > where deptid like '1847%' > > Julie > > -----Original Message----- > From: Rosie ODonnell [mailto:joe.patton@NA.MARCONICOMMS.COM] > Sent: Tuesday, July 06, 1999 1:32 PM > To: Multiple recipients of list SQR-USERS > Subject: Special Characters in a string > > > Hi, > > Hopefully, this is an easy one. Here is an example: > > update ps_dept_tble > set descr = 'Research & Development' > where deptid like '1847%' > > The ampersand(&) is looked at as a replacement variable. What do I need > to > surround it with so that the program ignores the ampersand and considers > it > part > of the string? > > Thanks, > > Joe Patton > Marconi Communications From owner-sqr-users@list.iex.net Tue Jul 6 14:29:30 1999 Date: Tue, 6 Jul 1999 15:15:52 -0400 From: John Milardovic Subject: Re: Converting from ASCII to EBCDIC Found this on the net. I'm sure there are hundreds of others. Read Chapter 22 of the Users Guide for instructions. Then again there are probably a dozen easier ways. :-) John Milardovic --------------------------------------------------------------------------- /* ** ASCII <=> EBCDIC conversion functions */ static unsigned char a2e[256] = { 0, 1, 2, 3, 55, 45, 46, 47, 22, 5, 37, 11, 12, 13, 14, 15, 16, 17, 18, 19, 60, 61, 50, 38, 24, 25, 63, 39, 28, 29, 30, 31, 64, 79,127,123, 91,108, 80,125, 77, 93, 92, 78,107, 96, 75, 97, 240,241,242,243,244,245,246,247,248,249,122, 94, 76,126,110,111, 124,193,194,195,196,197,198,199,200,201,209,210,211,212,213,214, 215,216,217,226,227,228,229,230,231,232,233, 74,224, 90, 95,109, 121,129,130,131,132,133,134,135,136,137,145,146,147,148,149,150, 151,152,153,162,163,164,165,166,167,168,169,192,106,208,161, 7, 32, 33, 34, 35, 36, 21, 6, 23, 40, 41, 42, 43, 44, 9, 10, 27, 48, 49, 26, 51, 52, 53, 54, 8, 56, 57, 58, 59, 4, 20, 62,225, 65, 66, 67, 68, 69, 70, 71, 72, 73, 81, 82, 83, 84, 85, 86, 87, 88, 89, 98, 99,100,101,102,103,104,105,112,113,114,115,116,117, 118,119,120,128,138,139,140,141,142,143,144,154,155,156,157,158, 159,160,170,171,172,173,174,175,176,177,178,179,180,181,182,183, 184,185,186,187,188,189,190,191,202,203,204,205,206,207,218,219, 220,221,222,223,234,235,236,237,238,239,250,251,252,253,254,255 }; static unsigned char e2a[256] = { 0, 1, 2, 3,156, 9,134,127,151,141,142, 11, 12, 13, 14, 15, 16, 17, 18, 19,157,133, 8,135, 24, 25,146,143, 28, 29, 30, 31, 128,129,130,131,132, 10, 23, 27,136,137,138,139,140, 5, 6, 7, 144,145, 22,147,148,149,150, 4,152,153,154,155, 20, 21,158, 26, 32,160,161,162,163,164,165,166,167,168, 91, 46, 60, 40, 43, 33, 38,169,170,171,172,173,174,175,176,177, 93, 36, 42, 41, 59, 94, 45, 47,178,179,180,181,182,183,184,185,124, 44, 37, 95, 62, 63, 186,187,188,189,190,191,192,193,194, 96, 58, 35, 64, 39, 61, 34, 195, 97, 98, 99,100,101,102,103,104,105,196,197,198,199,200,201, 202,106,107,108,109,110,111,112,113,114,203,204,205,206,207,208, 209,126,115,116,117,118,119,120,121,122,210,211,212,213,214,215, 216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231, 123, 65, 66, 67, 68, 69, 70, 71, 72, 73,232,233,234,235,236,237, 125, 74, 75, 76, 77, 78, 79, 80, 81, 82,238,239,240,241,242,243, 92,159, 83, 84, 85, 86, 87, 88, 89, 90,244,245,246,247,248,249, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57,250,251,252,253,254,255 }; char ASCIItoEBCDIC(const unsigned char c) { return a2e[c]; } char EBCDICtoASCII(const unsigned char c) { return e2a[c]; } ---------------------------------------------------------------------------- ---------------------------------------- > -----Original Message----- > From: Ben Lawrence [SMTP:ben_lawrence69@HOTMAIL.COM] > Sent: Tuesday, July 06, 1999 2:29 PM > To: Multiple recipients of list SQR-USERS > Subject: Converting from ASCII to EBCDIC > > Hi all, > I am working on an interface which reports deductions for state > retirement. > The deduction field must be EBCDIC and it can only be 6 characters in > length. I am looking for a way to convert the deduction field from ASCII > to > EBCDIC within SQR. Has anyone written an SQR that does this conversion? > Any help would be greatly appreciated. > > Thanks, > Ben > > > _______________________________________________________________ > Get Free Email and Do More On The Web. Visit http://www.msn.com From owner-sqr-users@list.iex.net Tue Jul 6 14:35:18 1999 Date: Tue, 6 Jul 1999 12:20:03 -0700 From: Pankaj Bedekar Subject: Re: Special Characters in a string This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BEC7E4.8C542BF0 Content-Type: text/plain; charset="iso-8859-1" just use 'Research '& Development' use that extra single quote (') before &. Pankaj -----Original Message----- From: Rosie ODonnell [mailto:joe.patton@NA.MARCONICOMMS.COM] Sent: Tuesday, July 06, 1999 11:32 AM To: Multiple recipients of list SQR-USERS Subject: Special Characters in a string Hi, Hopefully, this is an easy one. Here is an example: update ps_dept_tble set descr = 'Research & Development' where deptid like '1847%' The ampersand(&) is looked at as a replacement variable. What do I need to surround it with so that the program ignores the ampersand and considers it part of the string? Thanks, Joe Patton Marconi Communications ------_=_NextPart_001_01BEC7E4.8C542BF0 Content-Type: text/html; charset="iso-8859-1" RE: Special Characters in a string

just use
   'Research '& Development'

use that extra single quote (') before &.

Pankaj


-----Original Message-----
From: Rosie ODonnell [mailto:joe.patton@NA.MARCONICOMMS.COM]
Sent: Tuesday, July 06, 1999 11:32 AM
To: Multiple recipients of list SQR-USERS
Subject: Special Characters in a string


Hi,

Hopefully, this is an easy one.  Here is an example:

update ps_dept_tble
set descr = 'Research & Development'
where deptid like '1847%'

The ampersand(&) is looked at as a replacement variable.  What do I need to
surround it with so that the program ignores the ampersand and considers it part
of the string?

Thanks,

Joe Patton
Marconi Communications

------_=_NextPart_001_01BEC7E4.8C542BF0-- From owner-sqr-users@list.iex.net Tue Jul 6 14:41:38 1999 Date: Tue, 6 Jul 1999 15:29:24 -0400 From: John Milardovic Subject: Re: Special Characters in a string I tried basically the same code and didn't have any problem. begin-sql update tableX set columnY = 'Res & Dev' where columnZ = 100; commit; end-sql My table was updated correctly using Oracle 8 and SQR 4. John Milardovic > -----Original Message----- > From: Rosie ODonnell [SMTP:joe.patton@NA.MARCONICOMMS.COM] > Sent: Tuesday, July 06, 1999 2:32 PM > To: Multiple recipients of list SQR-USERS > Subject: Special Characters in a string > > Hi, > > Hopefully, this is an easy one. Here is an example: > > update ps_dept_tble > set descr = 'Research & Development' > where deptid like '1847%' > > The ampersand(&) is looked at as a replacement variable. What do I need > to > surround it with so that the program ignores the ampersand and considers > it part > of the string? > > Thanks, > > Joe Patton > Marconi Communications From owner-sqr-users@list.iex.net Tue Jul 6 14:44:17 1999 Date: Tue, 6 Jul 1999 12:32:17 -0700 From: Joe Subject: select works in SQL but not in SQR Greetings SQRUG Gurus! Can anybody help me see what is not working here? In SQLPlus, I can use select proc_we_dt, prod_comm_cur from ps_dist_summary where project_id = '10001' and proc_we_dt >= '22-JAN-99' and proc_we_dt < '19-FEB-99' and I get the following data: PROC_WE PROD_COMM_CUR --------- --------------- 22-JAN-99 193.20 29-JAN-99 361.34 05-FEB-99 361.54 12-FEB-99 240.92 22-JAN-99 -9.06 29-JAN-99 -5.06 05-FEB-99 -9.06 12-FEB-99 -9.08 8 rows selected. but in my SQR, I have: begin-select to_char(proc_we_dt,'mm/dd/yy') &proc_we_dt_fm round(sum(prod_comm_cur), 0) &prod_comm_cur from ps_dist_summary where project_id = $project_id and dist_dt > to_date($asof_dt,'mm/dd/yyyy') and to_date($proc_we_dt,'mm/dd/yyyy') >= to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy') and to_date($proc_we_dt,'mm/dd/yyyy') < to_date($asof_dt,'mm/dd/yyyy') group by to_char(proc_we_dt,'mm/dd/yy') order by to_char(proc_we_dt,'mm/dd/yy') end-select And I get no data. I use '10001' for $project_id '22-JAN-99' for to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy') and '19-FEB-99' for to_date($asof_dt,'mm/dd/yyyy') AGGGGHHHH!!!!!!! Maybe my brain is fried from looking at the monitor for so long, but that shoe sales job at the mall is looking better and better! TIA Joe Johnson jejohn1216@surfree.com ~~ JEJ ~~ ;{) -------------------------------------------------------- $14.95 Unlimited Internet Access, http://www.surfree.com From owner-sqr-users@list.iex.net Tue Jul 6 14:48:12 1999 Date: Tue, 6 Jul 1999 19:35:23 GMT From: Dhananjay Nikkam Subject: reading from a flat file Hi guys, I am trying to read from a flat file, which consists of data for the BI_HDR and the BI_LINE tables. That is Header and detail info. So one line of HDR can consist of one or more detail lines. They are linked by a transaction ID. The flat file looks like something below. Can anyone suggest the most efficient way of acheiving this? Rec TransID. Date CustID Cust# counts totalamt type --- ------- ---- ------ ----- ------ -------- A 11788 06/11/99 678 1 AXDDFF 7675 B 11788 50 4000) B 11788 100 3675) line info Rec. type A,is the header info and Rec type B, will be the line info and linked by the TransID. Thanks DJ _______________________________________________________________ Get Free Email and Do More On The Web. Visit http://www.msn.com From owner-sqr-users@list.iex.net Tue Jul 6 15:04:24 1999 Date: Tue, 6 Jul 1999 12:54:33 -0700 From: Clara Carter Subject: Re: select works in SQL but not in SQR I think it has to do with your date formats in the to_date functions. You are telling it to expect it in the 'mm/dd/yyyy' format but you are passing it '22-JAN-99'. So then the conversion is wrong. This is what it's reading in: and to_date('22-JAN-99','mm/dd/yyyy') when I think it should be and to_date('22-JAN-99','MM-MON-YY') Just a start.... Clara where project_id = $project_id > and dist_dt > to_date($asof_dt,'mm/dd/yyyy') > and to_date($proc_we_dt,'mm/dd/yyyy') >= >to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy') > and to_date($proc_we_dt,'mm/dd/yyyy') < to_date($asof_dt,'mm/dd/yyyy') At 12:32 PM 7/6/99 -0700, you wrote: >Greetings SQRUG Gurus! > >Can anybody help me see what is not working here? > >In SQLPlus, I can use > > select proc_we_dt, prod_comm_cur > from ps_dist_summary > where project_id = '10001' > and proc_we_dt >= '22-JAN-99' > and proc_we_dt < '19-FEB-99' > >and I get the following data: > > PROC_WE PROD_COMM_CUR > --------- --------------- > 22-JAN-99 193.20 > 29-JAN-99 361.34 > 05-FEB-99 361.54 > 12-FEB-99 240.92 > 22-JAN-99 -9.06 > 29-JAN-99 -5.06 > 05-FEB-99 -9.06 > 12-FEB-99 -9.08 > > 8 rows selected. > >but in my SQR, I have: > > begin-select > to_char(proc_we_dt,'mm/dd/yy') &proc_we_dt_fm > round(sum(prod_comm_cur), 0) &prod_comm_cur > from ps_dist_summary > where project_id = $project_id > and dist_dt > to_date($asof_dt,'mm/dd/yyyy') > and to_date($proc_we_dt,'mm/dd/yyyy') >= >to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy') > and to_date($proc_we_dt,'mm/dd/yyyy') < to_date($asof_dt,'mm/dd/yyyy') > > group by to_char(proc_we_dt,'mm/dd/yy') > > order by to_char(proc_we_dt,'mm/dd/yy') > > end-select > >And I get no data. > >I use '10001' for $project_id > '22-JAN-99' for to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy') > and '19-FEB-99' for to_date($asof_dt,'mm/dd/yyyy') > >AGGGGHHHH!!!!!!! > >Maybe my brain is fried from looking at the monitor for so long, but that >shoe sales job at the mall is looking better and better! > >TIA > >Joe Johnson >jejohn1216@surfree.com > >~~ JEJ ~~ ;{) > >-------------------------------------------------------- >$14.95 Unlimited Internet Access, http://www.surfree.com From owner-sqr-users@list.iex.net Tue Jul 6 16:01:06 1999 Date: Tue, 6 Jul 1999 16:26:56 -0400 From: "Sarabudla, Raji R." Subject: Re: reading from a flat file Hi Dhananjay, You can achieve this by reading the flat file into variables and check the Rec Type, if the Rec Type is A insert into BI_HDR table, else insert into BI_LINE Table. Make sure the Field Lengths,Date Formats etc. Read all the Values into String Variables and Convert them into Numbers later. The following example makes you easy to understand. Example: begin-procedure main let $filename = 'C:\temp\temp.dat' open $filename as 1 for-reading record=100 status=#filestat while 1 read 1 into $RectYPE:2 $TransID:10 $Date:10 $CustID:10 $Cust#:10 $counts:4 $totalamt:12 ! convert necessary string variables into number variables before inserting. let $RecType = rtrim($RecType,'') let #counts = edit($counts,'99') let #totalamt = edit($totalamt,'9999999.99') etc. if $RecType = 'A' --- When even the Rectype A reads, it inserts into Header Table, otherwise, inserts into detail table. insert into BI_HDR(RecType,TransID,TDATe etc.) Values ($RecType,$TransID,#counts,#totalamt etc.) ! make sure the syntax here. else insert into BI_LINE(RecType,TransID,TDATe etc.) Values ($RecType,$TransID,#counts,#totalamt etc.) end-if if #end-file break end-if end-while end-procedure Hope this helps, Raji ---------- From: Dhananjay Nikkam [SMTP:nikkam@hotmail.com] Sent: Tuesday, July 06, 1999 3:35 PM To: Multiple recipients of list SQR-USERS Subject: reading from a flat file Hi guys, I am trying to read from a flat file, which consists of data for the BI_HDR and the BI_LINE tables. That is Header and detail info. So one line of HDR can consist of one or more detail lines. They are linked by a transaction ID. The flat file looks like something below. Can anyone suggest the most efficient way of acheiving this? Rec TransID. Date CustID Cust# counts totalamt type --- ------- ---- ------ ----- ------ -------- A 11788 06/11/99 678 1 AXDDFF 7675 B 11788 50 4000) B 11788 100 3675) line info Rec. type A,is the header info and Rec type B, will be the line info and linked by the TransID. Thanks DJ _______________________________________________________________ Get Free Email and Do More On The Web. Visit http://www.msn.com From owner-sqr-users@list.iex.net Tue Jul 6 15:47:20 1999 Date: Tue, 6 Jul 1999 13:27:10 -0700 From: "Lynds,Rick" Subject: Re: select works in SQL but not in SQR Joe, Looking at your code, did you really mean '$proc_we_dt' - if your code fragment is complete, that would be an empty string - hence no data. Rick rlynds@mwd.dst.ca.us -----Original Message----- From: Joe [mailto:jejohn1216@SURFREE.COM] Sent: Tuesday, July 06, 1999 12:32 PM To: Multiple recipients of list SQR-USERS Subject: select works in SQL but not in SQR Greetings SQRUG Gurus! Can anybody help me see what is not working here? In SQLPlus, I can use select proc_we_dt, prod_comm_cur from ps_dist_summary where project_id = '10001' and proc_we_dt >= '22-JAN-99' and proc_we_dt < '19-FEB-99' and I get the following data: PROC_WE PROD_COMM_CUR --------- --------------- 22-JAN-99 193.20 29-JAN-99 361.34 05-FEB-99 361.54 12-FEB-99 240.92 22-JAN-99 -9.06 29-JAN-99 -5.06 05-FEB-99 -9.06 12-FEB-99 -9.08 8 rows selected. but in my SQR, I have: begin-select to_char(proc_we_dt,'mm/dd/yy') &proc_we_dt_fm round(sum(prod_comm_cur), 0) &prod_comm_cur from ps_dist_summary where project_id = $project_id and dist_dt > to_date($asof_dt,'mm/dd/yyyy') and to_date($proc_we_dt,'mm/dd/yyyy') >= to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy') and to_date($proc_we_dt,'mm/dd/yyyy') < to_date($asof_dt,'mm/dd/yyyy') group by to_char(proc_we_dt,'mm/dd/yy') order by to_char(proc_we_dt,'mm/dd/yy') end-select And I get no data. I use '10001' for $project_id '22-JAN-99' for to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy') and '19-FEB-99' for to_date($asof_dt,'mm/dd/yyyy') AGGGGHHHH!!!!!!! Maybe my brain is fried from looking at the monitor for so long, but that shoe sales job at the mall is looking better and better! TIA Joe Johnson jejohn1216@surfree.com ~~ JEJ ~~ ;{) -------------------------------------------------------- $14.95 Unlimited Internet Access, http://www.surfree.com From owner-sqr-users@list.iex.net Tue Jul 6 16:06:30 1999 Date: Tue, 6 Jul 1999 16:50:48 -0400 From: Krisjanis Gale Subject: neat way of parsing delimited list of strings... necessity is the mother invention. working on an SQR that parses values in a table and then generates an SQR that acts on those values (don't ask! it's ugly what i'm doing!) i ran into the problem of not having an easy way to pull strings from a comma-delimited string, one by one. so i decided to "make it so." here's a working SQR ditty that does just what i thought it would. it first counts the number of words in the string ($S), then lists them invidually. perhaps some of you will find this useful. -- begin-program let $S = 'this,is,a,comma,delimited,string' let $S = $S || ',@' let $tmp = $S let $head = '' let #wordcount = 0 while $tmp <> '@' let #pos = instr($tmp,',',1) let $tmp = substr($tmp,#pos+1,length($tmp)-#pos) let #wordcount = #wordcount + 1 end-while display #wordcount let $tmp = $S let $head = '' while $tmp <> '@' let #pos = instr($tmp,',',1) let $head = substr($tmp,1,#pos - 1) display $head let $tmp = substr($tmp,#pos+1,length($S)-#pos) end-while end-program -- (kris)janis p. gale hrsd - federal reserve bank of new york x8163 From owner-sqr-users@list.iex.net Tue Jul 6 16:33:00 1999 Date: Tue, 6 Jul 1999 16:53:35 -0400 From: "Sarabudla, Raji R." Subject: Re: select works in SQL but not in SQR Hi Joe, The Select Syntax in SQR is not correct, you need not format the Database columns in where clause. you can simply use those columns and format String Dates (SQR Date Variables) against the Database Columns in where clause. May be the following Select clause works for you, Begin-procedure main begin-select to_char(proc_we_dt,'mm/dd/yyyy') &proc_we_dt_fm round(prod_comm_cur), 0) &prod_comm_cur from ps_dist_summary where project_id = $project_id and dist_dt > to_date($asof_dt,'mm/dd/yyyy') and proc_we_dt >= to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy') and proc_we_dt < to_date($asof_dt,'mm/dd/yyyy') order by proc_we_dt end-procedure main If you want use the Group by Columns, the syntax has to be changed and the Group columns have to be verified before using any Group columns. Hope this may atleast help. Thanks, Raji ---------- From: Joe [SMTP:jejohn1216@SURFREE.COM] Sent: Tuesday, July 06, 1999 3:32 PM To: Multiple recipients of list SQR-USERS Subject: select works in SQL but not in SQR Greetings SQRUG Gurus! Can anybody help me see what is not working here? In SQLPlus, I can use select proc_we_dt, prod_comm_cur from ps_dist_summary where project_id = '10001' and proc_we_dt >= '22-JAN-99' and proc_we_dt < '19-FEB-99' and I get the following data: PROC_WE PROD_COMM_CUR --------- --------------- 22-JAN-99 193.20 29-JAN-99 361.34 05-FEB-99 361.54 12-FEB-99 240.92 22-JAN-99 -9.06 29-JAN-99 -5.06 05-FEB-99 -9.06 12-FEB-99 -9.08 8 rows selected. but in my SQR, I have: begin-select to_char(proc_we_dt,'mm/dd/yy') &proc_we_dt_fm round(sum(prod_comm_cur), 0) &prod_comm_cur from ps_dist_summary where project_id = $project_id and dist_dt > to_date($asof_dt,'mm/dd/yyyy') and to_date($proc_we_dt,'mm/dd/yyyy') >= to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy') and to_date($proc_we_dt,'mm/dd/yyyy') < to_date($asof_dt,'mm/dd/yyyy') group by to_char(proc_we_dt,'mm/dd/yy') order by to_char(proc_we_dt,'mm/dd/yy') end-select And I get no data. I use '10001' for $project_id '22-JAN-99' for to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy') and '19-FEB-99' for to_date($asof_dt,'mm/dd/yyyy') AGGGGHHHH!!!!!!! Maybe my brain is fried from looking at the monitor for so long, but that shoe sales job at the mall is looking better and better! TIA Joe Johnson jejohn1216@surfree.com ~~ JEJ ~~ ;{) -------------------------------------------------------- $14.95 Unlimited Internet Access, http://www.surfree.com From owner-sqr-users@list.iex.net Tue Jul 6 17:22:18 1999 Date: Tue, 6 Jul 1999 18:09:20 -0400 From: S+A Sills Subject: Re: reading from a flat file you could in a read loop read the entire record into $rec then test the first byte of $rec if = A do parse-into-header-fields do header-stuff else do parse-into-detail-fields do detail-stuff end_if this should work unless I am missing something >Hi guys, > >I am trying to read from a flat file, which consists of data for the BI_HDR >and the BI_LINE tables. That is Header and detail info. So one line of HDR >can consist of one or more detail lines. They are linked by a transaction >ID. The flat file looks like something below. Can anyone suggest the most >efficient way of acheiving this? > >Rec TransID. Date CustID Cust# counts totalamt >type >--- ------- ---- ------ ----- ------ -------- >A 11788 06/11/99 678 1 AXDDFF 7675 > >B 11788 50 4000) >B 11788 100 3675) line info > >Rec. type A,is the header info and Rec type B, will be the line >info and linked by the TransID. > >Thanks >DJ > > > > >_______________________________________________________________ >Get Free Email and Do More On The Web. Visit http://www.msn.com > > From owner-sqr-users@list.iex.net Tue Jul 6 17:23:10 1999 Date: Wed, 7 Jul 1999 08:12:00 +1000 From: Paul Schattling Subject: Sqr Barcodes Hi gang, Using SQR 3.0 for PeopleSoft 6.0 on Oracle & Unix Has anyone had any sucess with the creation and/or is currently using sqr to produce barcodes. We want to print off barcodes for our asset management system but are having major troubles. I was hoping to produce 4 * 7 barcodes on an A4 sheet. I am able to produce a somewhat distorted replica of a barcode but am far from printing them out as I would have liked. Perhaps someone might have an sqr in place that is similiar???? Hoping for help, Paul From owner-sqr-users@list.iex.net Tue Jul 6 17:30:25 1999 Date: Tue, 6 Jul 1999 18:19:11 -0400 From: S+A Sills Subject: Re: Loading Employee data into PS_JOB on AS400 using the application designer save as job_import to myjob_import. answer yes to the peoplecode copy prompt ( I am not sure if there is any pc behind job_import ). change myjob_import from a view to a table and then do a build. import into myjob_import. then use sql to copy to ps_job. ps never done it, can't even spell AS400, but this might work. > We are trying to load employee data into PS_JOB and other tables using > Import Manager. Import Manager errors out when trying to insert into > PS_JOB, and we were told to use the JOB_IMPORT view for loading. We > are running PS on an AS400, and we apparently cannot update or insert > on views on the 400. Just wondering if someone else has run into a > similar problem and how they resolved it. > > Also, any suggestions of alternative methods of loading employee data > will be appreciated. > > Thanks. > > From owner-sqr-users@list.iex.net Tue Jul 6 17:37:11 1999 Date: Tue, 6 Jul 1999 15:24:58 -0700 From: Pankaj Bedekar Subject: Re: Sqr Barcodes This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BEC7FE.651D6B40 Content-Type: text/plain; charset="iso-8859-1" Paul Need to declare printer on your own.... dont use senenv01/02 . sqc declare printer ! Declare printer characteristics type=HPLASERJET ! Types: POSTSCRIPT, HPLASERJET, or LINEPRINTER left-margin=.50 top-margin=.25 font=6 ! Font number font-style=fixed lines-inch=6 ! Lines per inch point-size=6 orientation=portrait ! portrait or landscape following statements prints bar code very well for us ... type I used is "3 of 9".... let $pack_no = edit(#pack_nbr,'000009') print-bar-code (+1,90) type=5 height= 0.4 text=$pack_no pankaj -----Original Message----- From: Paul Schattling [mailto:P.Schattling@MAILBOX.GU.EDU.AU] Sent: Tuesday, July 06, 1999 3:12 PM To: Multiple recipients of list SQR-USERS Subject: Sqr Barcodes Hi gang, Using SQR 3.0 for PeopleSoft 6.0 on Oracle & Unix Has anyone had any sucess with the creation and/or is currently using sqr to produce barcodes. We want to print off barcodes for our asset management system but are having major troubles. I was hoping to produce 4 * 7 barcodes on an A4 sheet. I am able to produce a somewhat distorted replica of a barcode but am far from printing them out as I would have liked. Perhaps someone might have an sqr in place that is similiar???? Hoping for help, Paul ------_=_NextPart_001_01BEC7FE.651D6B40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Sqr Barcodes

Paul

Need to declare printer on your own.... dont use = senenv01/02 . sqc

   declare = printer        ! Declare printer = characteristics
   = type=3DHPLASERJET        ! Types: = POSTSCRIPT, HPLASERJET, or LINEPRINTER
   left-margin=3D.50
   top-margin=3D.25
   = font=3D6          &nbs= p;      ! Font number
   font-style=3Dfixed
   = lines-inch=3D6         &nbs= p; ! Lines per inch
   point-size=3D6
   orientation=3Dportrait   ! = portrait or landscape

following statements prints bar code very well for us = ...
type I used is "3 of 9"....

   let $pack_no =3D = edit(#pack_nbr,'000009')
   print-bar-code (+1,90)
      type=3D5   =
      height=3D 0.4
      = text=3D$pack_no

pankaj


-----Original Message-----
From: Paul Schattling [mailto:P.Schattling@MAILB= OX.GU.EDU.AU]
Sent: Tuesday, July 06, 1999 3:12 PM
To: Multiple recipients of list SQR-USERS
Subject: Sqr Barcodes


Hi gang,

Using SQR 3.0 for PeopleSoft 6.0 on Oracle & = Unix

Has anyone had any sucess with the creation and/or is = currently using sqr
to produce barcodes.
We want to print off barcodes for our asset = management system but are
having major troubles.
I was hoping to produce 4 * 7 barcodes on an A4 = sheet.
I am able to produce a somewhat distorted replica of = a barcode but am far
from printing them out as I would have liked.
Perhaps someone might have an sqr in place that is = similiar????

Hoping for help,
Paul

------_=_NextPart_001_01BEC7FE.651D6B40-- From owner-sqr-users@list.iex.net Tue Jul 6 17:56:03 1999 Date: Tue, 6 Jul 1999 17:40:40 -0500 From: Zubin Shroff Subject: Error adding subrecord to PS_JOB We added a subrecord to the PS_JOB table. The fields from the subrecord were added to the Hire panels. However, when trying to hire an employee, before any of the panels load, the following PeopleCode error occurs: "Invalid parameter 1 for CurrentRowNumber function" - Is there some peoplecode I need to add/remove/modify when adding a subrecord to job? - Do fields from a subrecord need to be on a subpanel? They are currently on the main hire panels, and we were wondering if that had something to do with the error. Any help will be appreciated. Thanks. From owner-sqr-users@list.iex.net Tue Jul 6 17:56:35 1999 Date: Tue, 6 Jul 1999 17:40:40 -0500 From: Zubin Shroff Subject: Missing views when rebuilding PS_JOB We had added a couple of fields to the PS_JOB table through App Designer. When rebuilding the table, I checked the options to recreate all views. There are over 50 different views for the JOB record, and the rebuild process did not seem to recreate any of these views. We had to run a DDL audit on the database and recreate each view. Any ideas/explanations? Thanks. From owner-sqr-users@list.iex.net Tue Jul 6 20:42:48 1999 Date: Tue, 6 Jul 1999 18:32:24 -0700 From: Tonie Salzano Subject: HTML Table of Contents examples Does anyone have an example of an SQR that creates a Table of Contents that shows a link on a data item, e.g. deptid, instead of page? We have a check distribution report that we need to make available to our remote campuses. Thanks Tonie Salzano Maricopa Community College District From owner-sqr-users@list.iex.net Tue Jul 6 21:15:36 1999 Date: Tue, 6 Jul 1999 19:04:23 PDT From: Stephen Ratliff Subject: Re: [Sqr Barcodes] What type of printer are you using? I have found that using the SQR command of print-bar-code does not work for lineprinter, but I have had success with 2 of 5 and Extended Code 39. Does the 3.0 SQR allow for the command print-bar-code? Give an example of your barcode code. I'd like to see what it looks like. Stephen Ratliff Paul Schattling wrote: Hi gang, Using SQR 3.0 for PeopleSoft 6.0 on Oracle & Unix Has anyone had any sucess with the creation and/or is currently using sqr to produce barcodes. We want to print off barcodes for our asset management system but are having major troubles. I was hoping to produce 4 * 7 barcodes on an A4 sheet. I am able to produce a somewhat distorted replica of a barcode but am far from printing them out as I would have liked. Perhaps someone might have an sqr in place that is similiar???? Hoping for help, Paul ____________________________________________________________________ Get your own FREE, personal Netscape WebMail account today at http://webmail.netscape.com. From owner-sqr-users@list.iex.net Wed Jul 7 05:40:57 1999 Date: Wed, 7 Jul 1999 06:25:21 EDT From: Steffon Johnson Subject: Re: Error adding subrecord to PS_JOB Dear Zubin, you may want to check the fields on the subrecord that you added for any RowInit and/or RowSelect PeopleCode that is not encapsulated by %Panel and/or %PanelGroup. That is, unless your code reads ... ...If %Panel = PANEL.YOURPANEL, etc you might have envoked code that should fire elsewhere. Additioanlly, the error message "Invalid parameter 1 for CurrentRowNumber function" suggests that the function CurrentRowNumber() is not used correctly. Trace the code via App Reviewer. HTH, Steffon From owner-sqr-users@list.iex.net Wed Jul 7 07:18:03 1999 Date: Wed, 7 Jul 1999 07:51:15 -0400 From: Jim Hardesty Subject: Crossposting Just a gentle reminder that this is the SQR mailing list. While PeopleSoft specific SQR questions are appropriate here, PeopleTools questions are not. PeopleSoft questions are more appropriately addressed on the psusers list (http://www.egroups.com/group/psusers/). momentarily usurping the admin role... jim From owner-sqr-users@list.iex.net Wed Jul 7 08:45:20 1999 Date: Wed, 7 Jul 1999 08:57:59 -0400 From: Anthony Leung-New York Subject: Question on CALL SYSTEM Dear all, I have a sqr that is running on a Windows 95 environment. I need to do a CALL SYSTEM to copy files from one directory to another. According to the SQR Reference book the syntax should be as follow: CALL SYSTEM USING 'copy c:\source\*.* c:\target\' #s where #s returns the status of the call. I ran the sqr but the return status is always 2 (which indicates an error) and nothing gets copied over to the target Can someone shed some light in this area. Thanks From owner-sqr-users@list.iex.net Wed Jul 7 08:57:10 1999 Date: Wed, 7 Jul 1999 09:35:16 -0400 From: "Fehl, Douglas" Subject: Re: Question on CALL SYSTEM I believe the problem may be with your DOS command. For the target directory, either leave the last backslash off: c:\target or add the wildcards: c:\target\*.* > ---------- > From: Anthony Leung-New York[SMTP:ALeung@RUSSREYN.COM] > Sent: Wednesday, July 07, 1999 8:57 AM > To: Multiple recipients of list SQR-USERS > Subject: Question on CALL SYSTEM > > Dear all, > > I have a sqr that is running on a Windows 95 environment. I need to do a > CALL SYSTEM to copy files from one directory to another. According to the > SQR Reference book the syntax should be as follow: > > CALL SYSTEM USING 'copy c:\source\*.* c:\target\' #s > > > where #s returns the status of the call. > > I ran the sqr but the return status is always 2 (which indicates an error) > and nothing gets copied over to the target > > Can someone shed some light in this area. > > Thanks > From owner-sqr-users@list.iex.net Wed Jul 7 09:00:31 1999 Date: Wed, 7 Jul 1999 09:37:53 -0400 From: Bill Milano Subject: Re: Question on CALL SYSTEM I ran into the same exact problem a few weeks ago. One of the guys in our group (Moorthy Saga) called SQR and found out what the story is. Executing a system command under Windows is a lot more difficult than I thought. The documentation makes it sound really simple, but unless you are running on top of UNIX, it's a pain in the ass. The code should be relatively self explanatory. The only tricky thing is that if you do this in a local procedure, you'll need to test the variable "$_sqr-platform" instead of "$sqr-platform". Here is an multi-platform example for doing a sort. !-------------------------------------------------------------------------- ! Sort the temporary file created earlier using Operating System's SORT !-------------------------------------------------------------------------- move 99 to #cmd_status evaluate $sqr-platform when = 'WINDOWS' call system using 'SORT tempvms1.txt tempvms2.txt' #cmd_status WAIT break when = 'WINDOWS-NT' if instr(lower(getenv('COMSPEC')), 'command.com', 1) !------------------------------------------------------------- ! Windows 95 !------------------------------------------------------------- let $cmd = getenv('COMSPEC') || ' /c SORT tempvms1.txt > tempvms2.txt' else !------------------------------------------------------------- ! Windows NT !------------------------------------------------------------- let $cmd = 'cmd /c @start /min /wait cmd /c "SORT < tempvms1.txt > tempvms2.txt"' end-if call system using $cmd #cmd_status WAIT break when = 'UNIX' call system using 'SORT tempvms1.txt > tempvms2.txt' #cmd_status WAIT break end-evaluate if #cmd_status != 0 let $error_string = 'Platform: ' || $sqr-platform || ' - Sort returned error code: ' || to_char(#cmd_status) do issue_error($error_string, 8, 'Y') end-if Please respond to SQR-USERS@list.iex.net To: Multiple recipients of list SQR-USERS cc: (bcc: Bill Milano/AMS/AMSINC) Subject: Question on CALL SYSTEM Dear all, I have a sqr that is running on a Windows 95 environment. I need to do a CALL SYSTEM to copy files from one directory to another. According to the SQR Reference book the syntax should be as follow: CALL SYSTEM USING 'copy c:\source\*.* c:\target\' #s where #s returns the status of the call. I ran the sqr but the return status is always 2 (which indicates an error) and nothing gets copied over to the target Can someone shed some light in this area. Thanks From owner-sqr-users@list.iex.net Wed Jul 7 10:03:58 1999 Date: Wed, 7 Jul 1999 09:59:59 -0400 From: "David M. Thelen" Subject: Re: Question on CALL SYSTEM I don't recall all the details behind the system call but this was the suggestion I received a while back and has worked well for me. LET $COMMAND = GETENV('COMSPEC') || ' /c copy c:\source\*.* c:\target\*.*' call system using $COMMAND #s I think you could probably get more details on this from the archives but I'm have a CRS day. Anthony Leung-New York wrote: > Dear all, > > I have a sqr that is running on a Windows 95 environment. I need to do a > CALL SYSTEM to copy files from one directory to another. According to the > SQR Reference book the syntax should be as follow: > > CALL SYSTEM USING 'copy c:\source\*.* c:\target\' #s > > where #s returns the status of the call. > > I ran the sqr but the return status is always 2 (which indicates an error) > and nothing gets copied over to the target > > Can someone shed some light in this area. > > Thanks From owner-sqr-users@list.iex.net Wed Jul 7 09:53:28 1999 Date: Wed, 7 Jul 1999 07:31:47 -0700 From: Vince Mancino Subject: Re: Error adding subrecord to PS_JOB If the sub record has any peoplecode you must bypass it. The most common way is to add a panelgroup test at the top of the current peoplecode and bypass if not equal. If you have any peoplecode test add it after the else. Also review the sub record for any required fields. Zubin Shroff wrote: > We added a subrecord to the PS_JOB table. The fields from the > subrecord were added to the Hire panels. However, when trying to hire > an employee, before any of the panels load, the following PeopleCode > error occurs: > > "Invalid parameter 1 for CurrentRowNumber function" > > - Is there some peoplecode I need to add/remove/modify when adding a > subrecord to job? > > - Do fields from a subrecord need to be on a subpanel? They are > currently on the main hire panels, and we were wondering if that had > something to do with the error. > > Any help will be appreciated. > > Thanks. -- Vince Mancino - Consultant E5D4@EARTHLINK.NET From owner-sqr-users@list.iex.net Wed Jul 7 09:58:16 1999 Date: Wed, 7 Jul 1999 07:47:06 -0700 From: Joe Subject: Re: select works in SQL but not in SQR Thanks to all for your help! The problem was addressed by your collective suggestions and some pesky date formats in other parts of the SQR. Long live SQRUG! ~~ JEJ ~~ ;{) -------------------------------------------------------- $14.95 Unlimited Internet Access, http://www.surfree.com From owner-sqr-users@list.iex.net Wed Jul 7 11:43:50 1999 Date: Wed, 7 Jul 1999 09:35:54 -0700 From: Kris Narravula Subject: Re: Crossposting This is a multi-part message in MIME format. ------=_NextPart_000_004A_01BEC85C.1C46A1D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi everybody, I am getting the postings twice. Anybody else is also receiving the = postings twice or it is to me only. If so what should I do to get rid of = them. Thanks in advance Kris ----