From owner-sqr-users@list.iex.net Mon Jan 5 12:09:08 1998 Date: Mon, 5 Jan 1998 11:42:53 +0000 From: David Stanley SICAS Center Subject: INTERACTIVE AND BATCH modes Hi... I'm working with an Alpha with VMS 6.1 with SQR 3.12... The problem I'm having is this: I would like to call the system to retrieve the F$MODE() status to find if the env is "Ineractive or Batch". I've played around with this a bit and seam to be getting puzzled. Has anyone else worked thru this??? My code so far goes as follows: let $system_call = 'IF F$MODE() .NES. 'BATCH' THEN MODE_TEST:="INTERACTIVE" ' call system using $system_call #status let $mode = getenv('MODE_TEST') .. .. .. I've also tried let $system_call = 'MODE_TEST:=F$MODE()' The main issue is that the SYMBOL MODE_TEST doesn't seam to be getting assigned. I've debuged the code to see what's getting what and everything appears to be fine. Thanx for any input in advance David Stanley Lead Programmer SICAS Center SUNY Oneonta If you take a shovel and start digging, you will eventaully be standing in a hole. stanledd@oneonta.edu http://www.oneonta.edu/~stanledd/ From owner-sqr-users@list.iex.net Mon Jan 5 15:01:53 1998 Date: Mon, 5 Jan 1998 11:26:28 -0700 From: Robert Goshko Subject: Re: INTERACTIVE AND BATCH modes ------ =_NextPart_000_01BD19CC.F6C26640 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable My guess David, is that when you execute your CALL SYSTEM command, you = are envoking a secondary command shell and once this shell exits (when = control is returned to SQR) the secondary environment with you variable = get blown away and when you do the GETENV, you are querying the primary = command shell, which never had the environment variable MODE_TEST. Have you tried to set the MODE_TEST environment variable within your = default environment profile so that it is set all the time? (I'm no VMS = guru, but I have had this problem on other platforms). Later... Rob ----------------------------------------------------------- Through the NIC... Over the twisted pair...=20 Off the Router... >>SWISH<<... Nothing but NET! -------------------+-------------------+------------------- Robert Goshko | rgoshko@ibm.net | (403) 417-AXIS Axis Developments | | Sherwood Park, AB -----Original Message----- From: David Stanley SICAS Center [SMTP:stanledd@SNYONEVA.CC.ONEONTA.EDU] Sent: Monday, January 05, 1998 4:43 AM To: Multiple recipients of list SQR-USERS Subject: INTERACTIVE AND BATCH modes Hi... I'm working with an Alpha with VMS 6.1 with SQR 3.12... The problem I'm having is this: I would like to call the system to retrieve the F$MODE() status to find if the env is "Ineractive or Batch". I've played around with this a bit and seam to be getting puzzled. Has anyone else worked thru this??? My code so far goes as follows: let $system_call =3D 'IF F$MODE() .NES. 'BATCH' THEN MODE_TEST:=3D"INTERACTIVE" ' call system using $system_call #status let $mode =3D getenv('MODE_TEST') .. .. .. I've also tried let $system_call =3D 'MODE_TEST:=3DF$MODE()' The main issue is that the SYMBOL MODE_TEST doesn't seam to be getting assigned. I've debuged the code to see what's getting what and everything appears to be fine. Thanx for any input in advance David Stanley Lead Programmer SICAS Center SUNY Oneonta If you take a shovel and start digging, you will eventaully be standing = in a hole. stanledd@oneonta.edu http://www.oneonta.edu/~stanledd/ ------ =_NextPart_000_01BD19CC.F6C26640 Content-Type: application/ms-tnef Content-Transfer-Encoding: base64 eJ8+IjYSAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEEkAYAkAEAAAEAAAAQAAAAAwAAMAIAAAAL AA8OAAAAAAIB/w8BAAAAQQAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAAAAFNRUi1VU0VSU0BVU0Eu TkVUAFNNVFAAU1FSLVVTRVJTQFVTQS5ORVQAAAAAHgACMAEAAAAFAAAAU01UUAAAAAAeAAMwAQAA ABIAAABTUVItVVNFUlNAVVNBLk5FVAAAAAMAFQwBAAAAAwD+DwYAAAAeAAEwAQAAABQAAAAnU1FS LVVTRVJTQFVTQS5ORVQnAAIBCzABAAAAFwAAAFNNVFA6U1FSLVVTRVJTQFVTQS5ORVQAAAMAADkA AAAACwBAOgEAAAAeAPZfAQAAABIAAABTUVItVVNFUlNAVVNBLk5FVAAAAAIB918BAAAAQQAAAAAA AACBKx+kvqMQGZ1uAN0BD1QCAAAAAFNRUi1VU0VSU0BVU0EuTkVUAFNNVFAAU1FSLVVTRVJTQFVT QS5ORVQAAAAAAwD9XwEAAAADAP9fAAAAAAIB9g8BAAAABAAAAAAAAALhQwEEgAEAIAAAAFJFOiBJ TlRFUkFDVElWRSBBTkQgQkFUQ0ggbW9kZXMA3AgBBYADAA4AAADOBwEABQALABoAHAABAB0BASCA AwAOAAAAzgcBAAUACwATADcAAQAxAQEJgAEAIQAAADYxOUJFM0YxRjg4NUQxMTE5NTg2QjBEQjc3 MDAwMDAwAPUGAQOQBgCwCQAAIQAAAAsAAgABAAAACwAjAAAAAAADACYAAAAAAAsAKQAAAAAAAwAu AAAAAAADADYAAAAAAEAAOQCwMzVwBxq9AR4AcAABAAAAIAAAAFJFOiBJTlRFUkFDVElWRSBBTkQg QkFUQ0ggbW9kZXMAAgFxAAEAAAAWAAAAAb0aB3As8eObY4X4EdGVhrDbdwAAAAAAHgAeDAEAAAAF AAAAU01UUAAAAAAeAB8MAQAAABAAAAByZ29zaGtvQGlibS5uZXQAAwAGEKp6+oYDAAcQZQYAAB4A CBABAAAAZQAAAE1ZR1VFU1NEQVZJRCxJU1RIQVRXSEVOWU9VRVhFQ1VURVlPVVJDQUxMU1lTVEVN Q09NTUFORCxZT1VBUkVFTlZPS0lOR0FTRUNPTkRBUllDT01NQU5EU0hFTExBTkRPTkNFVEgAAAAA AgEJEAEAAACRBgAAjQYAAL0KAABMWkZ1UjKmIwMACgByY3BnMTI1FjIA+Atgbg4QMDMznQH3IAKk A+MCAGNoCsDgc2V0MCAHEwKDAFB/BFUQ2QhVB7ICgw5QEHZwaHJxMhF2fQqACMggNjsJbw4wNQKA CoF1YzsAUAsDYxISC8QF0HkgBmcKUAQRRGF2aWRkLCAEACB0EQAFQHcSaAnwIHkIYCBleDEFkHV0 ZRtSBcBDQQRMTAYAWVNURU36IAWgbQOBGmEbYgrAHABRCfB2b2sLgGcd8CD/ETAFoB2ACsAZoB01 HuAbIO5sAyAf0gIgYxwAGsAakdMgFBugaXQEICgbEx8RXnQDYAMgGpEJcHQIcG6LCYAasG8GAFFS KRqx1xwAHvgeQWkDYG4HgAIw6xsAIdBoG1N2CsAHMAJglRwAZxFAIAJgb3cDoLhhd2EZoB/SGxdk I8BJJDJHRRzwTlYdqHH/ClAfYB6SJDIVAAdwH18acO8bEA3gJgAjcHYTsREAI5EHJEElGiZnTU9E RV+7HPAc4C4KogqECoBIGjD/HAMasAiBI5MRMSQjLvctnx8moyXSC4AcFAEBYXVs5wVAJRoVAG9m AxAkUSjCfxrhIdAagjFyB0ADICQydIkHcT8gIgBJJ20ssE0jwFYF4RnAcnUacGL5G+AgSS0RMHEt JBqRNeH/JrE4oAIgIKAkMQXAC1EAMHEFsG1zKS+LGHYRYEz7GuAEkC4+QC+UOFA+0ggARwwwL6QS EnMxNi+ULZ9Ar0G/Qs9DRT54VGgDYIx1ZyYAJDJOSUM+QfwgTyziN8MD8RvwH/AKsP8lQEXiPnpG EAEgJCMIABvhAUdjPj5TV0lTSHQ8PEXiTjuBHpI5kk7tKTAhQE9L9itL/0zvRAynPzEEkAVAR28g EGsjwCU4UHw4UHJnUJNAaThibS4jcAVAUQIoNAMPUCQQNDE3LUFYe0ngPnVBIcAaASzRCQBwXyWC BCBREVUfURBTO6F3Um8EcCBQCsBrGnBBDkI8+hRRPQlsaTM2twFANeEb8GMFQE5zTwUQ3mcLgAdA BdAZ4WEm8EQJH1lWWUELE1l0AgBpLTHGNFKQWPExODABQAzQUV2DYiBGA2E6DINibxFgGiMGAAGQ bibAGaBTNUXAQQXwQyWRE7FbUzBNVFA6RuBgImRkIEBTTllPSzBWQVwuQ0XQYnFicFRisEX4RFVd L5VesAZgAjBfF6pNHyJ5GnBKAHB1KzKEMDUacDE5OThS0Cg6NDMRcE1jx1Rv32SYNOEFICbBCXBj BSAIkDtUsjYAIFkARuAj0i1VGFNFUlNVZCJ1YmobWaFfF0ljQGqAQUNUCElWRRFwTkQgQuBBVENI IARiEMBbn+9cqVkEC7YwBGk+SjiCVpCPVwAekiXTA5FBbHARAGMlxDjyNi4xJcQj4SD6M3QQMj5K RPAq0jr0OIL/OfEekhqTBABfEEfKOcFWkLc04B/wWQBrIOEjwGM3hvxzeUbhOKAjsSMhCIEwcckk MkYkLvIoKS+UYbH/I0AaoSPANhAf4QaQLVYagvQiSSNwcgDQOAAwcQWx9kIa4BDwIkYAOIAwcQtR /nkJgC+UCsAIYCfyJeIhA80e0GI24R/TZWF6g1AwhybiOAAeoXB1enph8fdGADBQgcFuG2AjcC+U IDC/ETByAyOCRQAwwSERP4aQ/y+aGZEFoAEANlI0wAXAUVD/B5GEUQIQIEAnUHeoJsAFQAokejRf eaM9ICdJUkZ7lyAuSzBTRgAndW1DJ0TgSClQhyUvBjr2PX5QbFgiixAvmjhQeaPvejV8wB6Sijsj fISPHInzTwRiivEm8R5BKCcu9yf/fBUvhZWMPnV/wwdANmIw8/+J74ryjXl7po8NddIAwDQhHwQB ClAahyQyHMBNQk/PHKAx+CiwB5BuJwVAgojvL5SDNoRQAJBnI3F/pQEAfzmQJvAtRIezMUMzwRrR J78EIIM2ouKAdR/hLNJ5SpR4YXBwgpARIILFfSFl9z5pRPAAcHiI4QXAhIEagO5ug7A2wSdxZCZg IMEvlP1frEyCkFbBA2AJwIKgB4Dmci+UYItTVWJQRhAjcLUCIWEvlElIoDCjYXlBeR7RaG9UQYI0 AZBQUWT7WlBaYWcdpAPwIYIs4K0B/TTgbBmggwFhsq+AdxIncb8tEAbwpnZht4SxrPIuCYA/DHAv pBjSNOAtEAJAcDooLy93taAus6kvfn1hti89orTxcIUvlBXBAAG4wAAAAAMAEBAAAAAAAwAREAAA AAADAIAQ/////0AABzCQ86iFBhq9AUAACDCQ86iFBhq9AQsAAIAIIAYAAAAAAMAAAAAAAABGAAAA AAOFAAAAAAAAAwACgAggBgAAAAAAwAAAAAAAAEYAAAAAEIUAAAAAAAADAAWACCAGAAAAAADAAAAA AAAARgAAAABShQAAtw0AAB4AJYAIIAYAAAAAAMAAAAAAAABGAAAAAFSFAAABAAAABAAAADguMAAD ACaACCAGAAAAAADAAAAAAAAARgAAAAABhQAAAAAAAAsAL4AIIAYAAAAAAMAAAAAAAABGAAAAAA6F AAAAAAAAAwAwgAggBgAAAAAAwAAAAAAAAEYAAAAAEYUAAAAAAAADADKACCAGAAAAAADAAAAAAAAA RgAAAAAYhQAAAAAAAB4AQYAIIAYAAAAAAMAAAAAAAABGAAAAADaFAAABAAAAAQAAAAAAAAAeAEKA CCAGAAAAAADAAAAAAAAARgAAAAA3hQAAAQAAAAEAAAAAAAAAHgBDgAggBgAAAAAAwAAAAAAAAEYA AAAAOIUAAAEAAAABAAAAAAAAAB4APQABAAAABQAAAFJFOiAAAAAAAwANNP03AACQqw== ------ =_NextPart_000_01BD19CC.F6C26640-- From owner-sqr-users@list.iex.net Mon Jan 5 17:18:08 1998 Date: Mon, 5 Jan 1998 13:54:46 PST From: Bill Bowers Subject: Help with SQL I am running the following sql within an sqr and it runs, and runs and runs. Anybody (familiar with PeopleSoft) have any ideas? select c.deptid, q.union_cd, b.pay_end_dt, b.erncd sum(b.oth_hrs), sum(b.oth_pay) from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' and b.company = c.company and b.paygroup = c.paygroup and b.pay_end_dt = c.pay_end_dt and b.page# = c.page# and b.line# = c.line# and b.addl# = c.addl# group by c.deptid, q.union_cd, b.pay_end_dt, b.Erncd Thanks in advance Bill Bowers St. Peters Hospital Olympia, Washington (360) 393-5437 From owner-sqr-users@list.iex.net Mon Jan 5 17:27:12 1998 Date: Mon, 5 Jan 1998 14:06:48 -0800 From: Vicki Lo Subject: To print the Trade Mark symbol Hello everyone! Is there a trick to use SQR to print the Trade Mark symbol (an R in the circle)? I did "encode <174> into $trade_mark" to get the symbol. When I did a "display", I can see it is printed perfectly, however, when I do a "print", Just a space is printed. Do we need to do something special to print the symbols? Victoria Lo Email address: VICKIL@remedystaff.com From owner-sqr-users@list.iex.net Mon Jan 5 17:34:17 1998 Date: Mon, 5 Jan 1998 16:16:40 -0600 From: Samantha Huffstetler Subject: Landscape Legal Output Has anyone done any reports using legal paper? I need to create a report on legal paper with landscape orientation. I have tried changing the paper size parameters to (11,14) but the document created is still letter size. Any suggestions would be appreciated. Thanks- Sam From owner-sqr-users@list.iex.net Mon Jan 5 17:35:32 1998 Date: Mon, 5 Jan 1998 15:22:08 -0700 From: "Johnson, Kate" Subject: Re: Help with SQL What are your indexes on these 3 tables? 1) ps_pay_oth_earns 2) ps_employment 3) ps_pay_earnings > ---------- > From: Bill Bowers[SMTP:bowersb@PSPH.PROVIDENCE.ORG] > Reply To: SQR-USERS@USA.NET > Sent: Monday, January 05, 1998 2:54 PM > To: Multiple recipients of list SQR-USERS > Subject: Help with SQL > > I am running the following sql within an sqr and it runs, and > runs and > runs. Anybody (familiar with PeopleSoft) have any ideas? > > > select > c.deptid, > q.union_cd, > b.pay_end_dt, > b.erncd > sum(b.oth_hrs), > sum(b.oth_pay) > from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c > where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' > and b.company = c.company > and b.paygroup = c.paygroup > and b.pay_end_dt = c.pay_end_dt > and b.page# = c.page# > and b.line# = c.line# > and b.addl# = c.addl# > group by c.deptid, > q.union_cd, > b.pay_end_dt, > b.Erncd > > Thanks in advance > > Bill Bowers > St. Peters Hospital > Olympia, Washington > (360) 393-5437 > From owner-sqr-users@list.iex.net Mon Jan 5 18:23:37 1998 Date: Mon, 5 Jan 1998 15:49:21 -0700 From: Robert Goshko Subject: Re: Help with SQL ------ =_NextPart_000_01BD19F1.A636BDA0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Form the looks of it Bill you have a Cartesian join going on, you forgot = to join the employment table with the pay earnings table (you are = joining every row in the employment table with every combination of pay = earnings/pay other earnings), you also missed a key field between pay = earnings and pay other earnings (off_cycle) Try this: select c.deptid, q.union_cd, b.pay_end_dt, b.erncd sum(b.oth_hrs), sum(b.oth_pay) from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' and b.company =3D c.company and b.paygroup =3D c.paygroup and b.pay_end_dt =3D c.pay_end_dt and b.off_cycle =3D c.off_cycle and b.page# =3D c.page# and b.line# =3D c.line# and b.addl# =3D c.addl# and q.emplid =3D c.emplid and q.empl_rcd# =3D c.empl_rcd# group by c.deptid, q.union_cd, b.pay_end_dt, b.Erncd Later... Rob ----------------------------------------------------------- Through the NIC... Over the twisted pair...=20 Off the Router... >>SWISH<<... Nothing but NET! -------------------+-------------------+------------------- Robert Goshko | rgoshko@ibm.net | (403) 417-AXIS Axis Developments | | Sherwood Park, AB -----Original Message----- From: Bill Bowers [SMTP:bowersb@PSPH.PROVIDENCE.ORG] Sent: Monday, January 05, 1998 2:55 PM To: Multiple recipients of list SQR-USERS Subject: Help with SQL I am running the following sql within an sqr and it runs, and runs = and runs. Anybody (familiar with PeopleSoft) have any ideas? select c.deptid, q.union_cd, b.pay_end_dt, b.erncd sum(b.oth_hrs), sum(b.oth_pay) from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' and b.company =3D c.company and b.paygroup =3D c.paygroup and b.pay_end_dt =3D c.pay_end_dt and b.page# =3D c.page# and b.line# =3D c.line# and b.addl# =3D c.addl# group by c.deptid, q.union_cd, b.pay_end_dt, b.Erncd Thanks in advance Bill Bowers St. Peters Hospital Olympia, Washington (360) 393-5437 ------ =_NextPart_000_01BD19F1.A636BDA0 Content-Type: application/ms-tnef Content-Transfer-Encoding: base64 eJ8+Ih4WAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEEkAYAkAEAAAEAAAAQAAAAAwAAMAIAAAAL AA8OAAAAAAIB/w8BAAAAQQAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAAAAFNRUi1VU0VSU0BVU0Eu TkVUAFNNVFAAU1FSLVVTRVJTQFVTQS5ORVQAAAAAHgACMAEAAAAFAAAAU01UUAAAAAAeAAMwAQAA ABIAAABTUVItVVNFUlNAVVNBLk5FVAAAAAMAFQwBAAAAAwD+DwYAAAAeAAEwAQAAABQAAAAnU1FS LVVTRVJTQFVTQS5ORVQnAAIBCzABAAAAFwAAAFNNVFA6U1FSLVVTRVJTQFVTQS5ORVQAAAMAADkA AAAACwBAOgEAAAAeAPZfAQAAABIAAABTUVItVVNFUlNAVVNBLk5FVAAAAAIB918BAAAAQQAAAAAA AACBKx+kvqMQGZ1uAN0BD1QCAAAAAFNRUi1VU0VSU0BVU0EuTkVUAFNNVFAAU1FSLVVTRVJTQFVT QS5ORVQAAAAAAwD9XwEAAAADAP9fAAAAAAIB9g8BAAAABAAAAAAAAALhQwEEgAEAEgAAAFJFOiBI ZWxwIHdpdGggU1FMAGYFAQWAAwAOAAAAzgcBAAUADwAxABUAAQAxAQEggAMADgAAAM4HAQAFAA8A JQAHAAEAFwEBCYABACEAAAA3NjlCRTNGMUY4ODVEMTExOTU4NkIwREI3NzAwMDAwMAD7BgEDkAYA jAgAACEAAAALAAIAAQAAAAsAIwAAAAAAAwAmAAAAAAALACkAAAAAAAMALgAAAAAAAwA2AAAAAABA ADkAcM9fKSwavQEeAHAAAQAAABIAAABSRTogSGVscCB3aXRoIFNRTAAAAAIBcQABAAAAFgAAAAG9 GiwpR/Hjm3eF+BHRlYaw23cAAAAAAB4AHgwBAAAABQAAAFNNVFAAAAAAHgAfDAEAAAAQAAAAcmdv c2hrb0BpYm0ubmV0AAMABhC9HpZYAwAHEAQGAAAeAAgQAQAAAGUAAABGT1JNVEhFTE9PS1NPRklU QklMTFlPVUhBVkVBQ0FSVEVTSUFOSk9JTkdPSU5HT04sWU9VRk9SR09UVE9KT0lOVEhFRU1QTE9Z TUVOVFRBQkxFV0lUSFRIRVBBWUVBUk5JTkdTAAAAAAIBCRABAAAAegUAAHYFAAD4CwAATFpGdUsG wXd3AAoBAwH3IAKkA+MCAGOCaArAc2V0MCAHE38CgwBQBFUPWQhVB7ICgzKhDudwcnEyD/Z9CoCJ CMggOwlvMjU1AoDZCoF1YwBQCwNjAEELYEBuZzEwMzMLpiACRgWwbSB0aGUgCQkAb2sEIG9mIGmt BUBCAxADIHkIYCAPgNp2GRBhEdAKwHQHkAcwSQOgam8LgCBnG6Fn2RmAbiwaMwIQchvgBUBMdG8b hBjyZW0LUG/vBsAJ8B0RAaBsGRAD8Bjw+xjjCrB5HeAKwAMAF7AEIH0epCgaQgrAGRAbkhwCZf0a oHIfsANgB+Adjx6aIdTHBaAG0AuAYXRpAiAZgn0fmi8fkh0AGQAFwB/WKescZAdAcx1AbQQBCYAa wVRrZR+wZgiQbCiQYv0PwHcJ4R+MAHAokCZvBCCCKBmQZl9jeWMe0O4pCqIKhAqAVCIBGPAEAIY6 LOoK9GxpMzYBQJ8XUA/gL+MPsB7QY3Qs5Bkv5GMuAQAFMGlkLPEwqnEudQMAAiAscDHcFGIuH5Ff CfBkX2QedDNdBJEzMDCqc3Vt5ig0ICahX2gPoCeANi+/NzMfkSzVL+QDUh+Aczki7l83Qh/SBCBi HGA6gSLpvnE7ozRDH+YA4DC5dybBBxkQNCopZycwMS1KQEFOLTk3JyqzJ+IzQHBERUNAwjCqKsLX NCAkoQqwbh+wPTFRQyXvQg80MgnACGBwQ5NFdkRf/z8aRgU0dSzkQsQsR0OTLEfjRs80MWdlI0X1 TOFLrzouL1BuTPVO002/LmH4ZGRsTQRQ40l4MrAi4s8xwEOTUpRR3l9yMzBNBPdUZzCqRaRiJIEx f1gbMr/vWB40L1oPWxFFNegW+F3b7Q/gTCUABJAuYAAwqggARwwwLPQQknMxNizkLd9ib2N/ZI9l BTCoVDeACGAiZx80TklDYAEgT78h4RjjKZAEABsgKuJpX/K7CuNcN08BIBjjCAB1X9QAID4+U1dJ U0i0PDxnok4moRwCYmrgsQewRVQhYg9ttittvx9ur2XMYPEEkAVAR29z7GhrHUAv4Hwv4BzhcmLg QGlibS5O8AVAcsIEKDQX4CkgNDE3mC1BWGugMKVBeAQA7CBEIdEJAHAeUgQgctFrdt9y0FMmwXcZ QCiQUOUKwGscYEFCLpoS0S6vVxOAHQAwcSBwM08FEGfrJOEDIE0HkHNM0WXJexYPewELE3s0AgBp LTE043RQL0ExODABQAzQf0PeYhiQA2EuIAySYg/gGfMOQiJABJAEIFtTTVQMUDoG4IHSYkBQUwBQ SC5QUk9WSQFBkE5DRS5PUke+XSzlgHAGYAIwgNdNAiDWZB+gHGBKAHB1CsAfsAQwNRxgMTk5OCAs MjoWUHiQTYRHVG95hRh1bCUQC1AZEAlwY38FIAiQdnIZkS9QaKAGAFHAUi1VU0VSdRWEoth1Ymow cYDXSCkwReD7HwOKoEx9T35aL1QLtjC49kkawBjQclkwIYMY8gIQ5xoQIkAcAnNxAyAfAhuxvwOR kpAFwCrCGcGRMXMcYP8qwpQiKrIwqZQiZ8AP8ENw5QbgZB+wKGaRAAMQBzCzBcAfA1BldjAe0FMZ kD50dIAahENxMcAf0HM//y4+MOUwT1cfWM8zf1uPNZ//Nq83vzjPOd867zv/PQ8+H/8/L0A/QU9C X0NvS89Fj0af/0evSL9L/00PTh9PL1A/UU3/Vj+cP1hfnb9af58/XJ9dqp+blWawAHAZYZMSZHYA cJ5jS5WbWYFpm1lTdGfA95fQX9EEIEhyUImwAZAJUFmbaE9sBsCJsGEcYFffmVBsYh0wC5CbaCgv cXSAADM5My01NDM3CyzqFEEAzHAAAAMAEBAAAAAAAwAREAAAAAADAIAQ/////0AABzBg1Ct0Khq9 AUAACDBg1Ct0Khq9AQsAAIAIIAYAAAAAAMAAAAAAAABGAAAAAAOFAAAAAAAAAwACgAggBgAAAAAA wAAAAAAAAEYAAAAAEIUAAAAAAAADAAWACCAGAAAAAADAAAAAAAAARgAAAABShQAAtw0AAB4AJYAI IAYAAAAAAMAAAAAAAABGAAAAAFSFAAABAAAABAAAADguMAADACaACCAGAAAAAADAAAAAAAAARgAA AAABhQAAAAAAAAsAL4AIIAYAAAAAAMAAAAAAAABGAAAAAA6FAAAAAAAAAwAwgAggBgAAAAAAwAAA AAAAAEYAAAAAEYUAAAAAAAADADKACCAGAAAAAADAAAAAAAAARgAAAAAYhQAAAAAAAB4AQYAIIAYA AAAAAMAAAAAAAABGAAAAADaFAAABAAAAAQAAAAAAAAAeAEKACCAGAAAAAADAAAAAAAAARgAAAAA3 hQAAAQAAAAEAAAAAAAAAHgBDgAggBgAAAAAAwAAAAAAAAEYAAAAAOIUAAAEAAAABAAAAAAAAAB4A PQABAAAABQAAAFJFOiAAAAAAAwANNP03AADSUw== ------ =_NextPart_000_01BD19F1.A636BDA0-- From owner-sqr-users@list.iex.net Mon Jan 5 18:26:02 1998 Date: Mon, 5 Jan 1998 17:07:24 -0600 From: Ravi Varadarajan Subject: Re: Help with SQL First try giving one date just to test it. secondly join from c to b than b-> ie. c.xx = b.xxx Ravi From owner-sqr-users@list.iex.net Mon Jan 5 18:47:46 1998 Date: Mon, 5 Jan 1998 15:29:52 -0700 From: Ben Le Subject: Re: Help with SQL Bill, I can see you are missing a comma after 'b.erncd'. I don't see any joint with 'ps_employment q' table with other tables at where clause (this may be a performance's problem). Hope will help. Happy New Year! At 01:54 PM 1/5/98 PST, you wrote: > I am running the following sql within an sqr and it runs, and runs and > runs. Anybody (familiar with PeopleSoft) have any ideas? > > > select > c.deptid, > q.union_cd, > b.pay_end_dt, > b.erncd > sum(b.oth_hrs), > sum(b.oth_pay) > from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c > where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' > and b.company = c.company > and b.paygroup = c.paygroup > and b.pay_end_dt = c.pay_end_dt > and b.page# = c.page# > and b.line# = c.line# > and b.addl# = c.addl# > group by c.deptid, > q.union_cd, > b.pay_end_dt, > b.Erncd > > Thanks in advance > > Bill Bowers > St. Peters Hospital > Olympia, Washington > (360) 393-5437 > > Benjamin Le System Development, ITS Portland Community College PO Box 19000 Portland, OR. 97280-0990 U.S.A. Phone: (503)-977-4970 Fax:(503)-977-4987 E-mail: Mailto:ble@pcc.edu PCC Web: http://www.pcc.edu From owner-sqr-users@list.iex.net Mon Jan 5 18:57:11 1998 Date: Tue, 6 Jan 1998 09:29:27 +1100 From: Steven Baldwin Subject: Re: Help with SQL I'm no peoplesoft expert, but it would appear you are not joining the ps_employment table (alias q) with anything else, giving a cartesian join against this table. If it contains a lot of rows, and from the name it sounds like it might, this could explain the unexpectedly long execution time. Hope this helps. Steve ---------------------------------------------- Steve Baldwin Phone : +61 3 9840 3829 Fax : +61 3 9840 3824 E-Mail : Steven.Baldwin@hancorp.com.au ---------------------------------------------- > ---------- > From: Bill Bowers[SMTP:bowersb@PSPH.PROVIDENCE.ORG] > Sent: Tuesday, 6 January 1998 8:54 > To: Multiple recipients of list SQR-USERS > Subject: Help with SQL > > I am running the following sql within an sqr and it runs, and > runs and > runs. Anybody (familiar with PeopleSoft) have any ideas? > > > select > c.deptid, > q.union_cd, > b.pay_end_dt, > b.erncd > sum(b.oth_hrs), > sum(b.oth_pay) > from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c > where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' > and b.company = c.company > and b.paygroup = c.paygroup > and b.pay_end_dt = c.pay_end_dt > and b.page# = c.page# > and b.line# = c.line# > and b.addl# = c.addl# > group by c.deptid, > q.union_cd, > b.pay_end_dt, > b.Erncd > > Thanks in advance > > Bill Bowers > St. Peters Hospital > Olympia, Washington > (360) 393-5437 > From owner-sqr-users@list.iex.net Mon Jan 5 19:36:03 1998 Date: Mon, 5 Jan 1998 17:16:39 -0600 From: Ravi Varadarajan Subject: Re: Landscape Legal Output Sam, Try changing the printer-init statement in the setup sqcs you are using. There was a discussion before on this topic. Please refer that atricle. Ravi From owner-sqr-users@list.iex.net Mon Jan 5 20:03:04 1998 Date: Mon, 5 Jan 1998 14:46:12 PST From: Bill Bowers Subject: Re[2]: Help with SQL Index information: ps_employment: emplid, empl_rcd# ps_pay_earnings: company, paygroup, pay_end_dt, off_cycle, page#, line#, addl# ps_pay_oth_earns:company, paygroup, pay_end_dt, off_cycle, page#, line#, addl#, erncd My e-mail address is bowersb@psph.providence.org Thanks! ______________________________ Reply Separator _________________________________ Subject: Re: Help with SQL Author: SQR-USERS@USA.NET at INTERNET Date: 1/5/98 2:43 PM What are your indexes on these 3 tables? 1) ps_pay_oth_earns 2) ps_employment 3) ps_pay_earnings > ---------- > From: Bill Bowers[SMTP:bowersb@PSPH.PROVIDENCE.ORG] > Reply To: SQR-USERS@USA.NET > Sent: Monday, January 05, 1998 2:54 PM > To: Multiple recipients of list SQR-USERS > Subject: Help with SQL > > I am running the following sql within an sqr and it runs, and > runs and > runs. Anybody (familiar with PeopleSoft) have any ideas? > > > select > c.deptid, > q.union_cd, > b.pay_end_dt, > b.erncd > sum(b.oth_hrs), > sum(b.oth_pay) > from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c > where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' > and b.company = c.company > and b.paygroup = c.paygroup > and b.pay_end_dt = c.pay_end_dt > and b.page# = c.page# > and b.line# = c.line# > and b.addl# = c.addl# > group by c.deptid, > q.union_cd, > b.pay_end_dt, > b.Erncd > > Thanks in advance > > Bill Bowers > St. Peters Hospital > Olympia, Washington > (360) 393-5437 > From owner-sqr-users@list.iex.net Mon Jan 5 20:12:23 1998 Date: Tue, 6 Jan 1998 10:58:08 +1000 From: "MORRIS, Dean" Subject: Julian dates in PeopleSoft Dear All, I'm currently working on a PeopleSoft Financials v7 implementation on an Oracle database. We're trying to find a standard function that will translate a Julian date into standard date format. ie. a number between 1 and 365 and convert it to a 31-12-1997 type date. This logic is fairly easy to write, but I was hoping to be able to use a standard SQR function to do it. There does not seem to be one in the standard SQC files. Can anyone help? Thanks in advance. Dean. From owner-sqr-users@list.iex.net Mon Jan 5 20:50:09 1998 Date: Mon, 5 Jan 1998 15:07:02 -0700 From: "Jensen, Ray - Broomfield" Subject: Re: Help with SQL Bill - I don't see a join to the ps_employment table in you where clause. Ray Jensen The Ultimate Software Consultants Senior Consultant 303-664-2821 ray.jensen@cexp.com jensenr@tusc.com >-----Original Message----- >From: Bill Bowers [SMTP:bowersb@PSPH.PROVIDENCE.ORG] >Sent: Monday, January 05, 1998 2:55 PM >To: Multiple recipients of list SQR-USERS >Subject: Help with SQL > > I am running the following sql within an sqr and it runs, and runs and > runs. Anybody (familiar with PeopleSoft) have any ideas? > > > select > c.deptid, > q.union_cd, > b.pay_end_dt, > b.erncd > sum(b.oth_hrs), > sum(b.oth_pay) > from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c > where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' > and b.company = c.company > and b.paygroup = c.paygroup > and b.pay_end_dt = c.pay_end_dt > and b.page# = c.page# > and b.line# = c.line# > and b.addl# = c.addl# > group by c.deptid, > q.union_cd, > b.pay_end_dt, > b.Erncd > > Thanks in advance > > Bill Bowers > St. Peters Hospital > Olympia, Washington > (360) 393-5437 From owner-sqr-users@list.iex.net Mon Jan 5 23:50:07 1998 Date: Tue, 6 Jan 1998 15:29:49 +1100 From: Julian Stevens Subject: Wrapping Proportional Fonts Does anyone have a solution to wrapping of proportional fonts ( mixed case and all uppercase )??? Any ideas! In anticipation of responses, Cheers, Julian Stevens. From owner-sqr-users@list.iex.net Tue Jan 6 00:06:17 1998 Date: Mon, 5 Jan 1998 20:40:55 -0800 From: Grant Chappell Subject: Re: To print the Trade Mark symbol Vicki, My guess is that the printer doesn't understand what code 174 is. I would look at the printer and see what symbol set it is using (Roman 8, pc 8, etc.). Then see if that symbol set includes the circled R. If it does, use that number, if not, you can change the printer symbol set at run time or manualy. Grant At 02:06 PM 1/5/98 -0800, you wrote: >Hello everyone! >Is there a trick to use SQR to print the Trade Mark symbol (an R in >the circle)? >I did "encode <174> into >$trade_mark" to get the symbol. >When I did a "display", I can see it is printed perfectly, however, >when I do a "print", >Just a space is printed. Do we need to do something special to print >the symbols? > >Victoria Lo >Email address: VICKIL@remedystaff.com > If people never did silly things, nothing intelligent would ever happen - Andrew Grove. From owner-sqr-users@list.iex.net Tue Jan 6 00:32:01 1998 Date: Mon, 5 Jan 1998 20:15:59 -0800 From: Grant Chappell Subject: Re: Landscape Legal Output Sam, I have done this two ways. First, on the client, you can set up sqrw to send the report to sqrv (viewer), then when you print, select legal paper (and landscape orientation). The second is to create a new set of printer sqc's, with the additional parameter for legal paper. If you are using an hp 4 or newer printer the command is: <27>&l3A . Depending on your printer, you may have to tell the printer which tray has legal paper: <27>&lxH (where x= source tray). At 04:16 PM 1/5/98 -0600, you wrote: >Has anyone done any reports using legal paper? I need to create a report on legal paper with landscape orientation. I have tried changing the paper size parameters to (11,14) but the document created is still letter size. > >Any suggestions would be appreciated. > >Thanks- >Sam > If people never did silly things, nothing intelligent would ever happen - Andrew Grove. From owner-sqr-users@list.iex.net Tue Jan 6 07:51:19 1998 Date: Tue, 6 Jan 1998 07:46:26 -0500 From: Gary Lane Subject: Re: Julian dates in PeopleSoft Dean, Look in DATEMATH.SQC. There are several routines that may be of help. 1. dtu-ser-to-ymd - This routine will take a Julian date and return year, month and day. You then can use dtu-format-date which takes year, month and day and returns the ddate in yyyy-mm-dd format. From this point the format-datetime routine which is found in DATETIME.SQC can be used to format the date in native format or other desired formats. Hope this helps. From owner-sqr-users@list.iex.net Tue Jan 6 08:10:01 1998 Date: Tue, 6 Jan 1998 08:01:23 -0500 From: Lynn Francis Subject: Re: Help with SQL Bill, It would appear that your where clause does not include any qualification = of the join to ps_employment q. This would result in a Cartesion product of = all the qualifying joins from b and c with every record of q. ***************************************************************************= ** from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' and b.company =3D c.company and b.paygroup =3D c.paygroup and b.pay_end_dt =3D c.pay_end_dt and b.page# =3D c.page# and b.line# =3D c.line# and b.addl# =3D c.addl# *************************************************************************= **** Lynn Francis From owner-sqr-users@list.iex.net Tue Jan 6 11:58:13 1998 Date: Tue, 6 Jan 1998 05:25:24 -0700 From: Robert Goshko Subject: Re: Julian dates in PeopleSoft ------ =_NextPart_000_01BD1A71.3307FCB0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Dean, I wrote a function that converts full Julian dates (number of days since = January 1, 0001) to an actual date, you could use this (along with the = function I wrote to convert the other way). =20 You could convert December 31 of the prior year to a Julian date, add = your number and then convert it back to a Gregorian date. you can look = at the functions on the SQR user group web page (http://www.sqrug.com/) = in the Library section... I believe it is in the #include section. Hope this helps. Later... Rob ----------------------------------------------------------- Through the NIC... Over the twisted pair...=20 Off the Router... >>SWISH<<... Nothing but NET! -------------------+-------------------+------------------- Robert Goshko | rgoshko@ibm.net | (403) 417-AXIS Axis Developments | | Sherwood Park, AB -----Original Message----- From: MORRIS, Dean [SMTP:Dean.MORRIS@SUNCORP-METWAY.COM.AU] Sent: Monday, January 05, 1998 5:58 PM To: Multiple recipients of list SQR-USERS Subject: Julian dates in PeopleSoft Dear All, I'm currently working on a PeopleSoft Financials v7 implementation on an Oracle database. We're trying to find a standard function that will translate a Julian date into standard date format. ie. a number between 1 and 365 and convert it to a 31-12-1997 type date. This logic is fairly easy to write, but I was hoping to be able to use a standard SQR function to do it. There does not seem to be one in the standard SQC files. Can anyone help? Thanks in advance. Dean. ------ =_NextPart_000_01BD1A71.3307FCB0 Content-Type: application/ms-tnef Content-Transfer-Encoding: base64 eJ8+IiAOAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEEkAYAkAEAAAEAAAAQAAAAAwAAMAIAAAAL AA8OAAAAAAIB/w8BAAAAQQAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAAAAFNRUi1VU0VSU0BVU0Eu TkVUAFNNVFAAU1FSLVVTRVJTQFVTQS5ORVQAAAAAHgACMAEAAAAFAAAAU01UUAAAAAAeAAMwAQAA ABIAAABTUVItVVNFUlNAVVNBLk5FVAAAAAMAFQwBAAAAAwD+DwYAAAAeAAEwAQAAABQAAAAnU1FS LVVTRVJTQFVTQS5ORVQnAAIBCzABAAAAFwAAAFNNVFA6U1FSLVVTRVJTQFVTQS5ORVQAAAMAADkA AAAACwBAOgEAAAAeAPZfAQAAABIAAABTUVItVVNFUlNAVVNBLk5FVAAAAAIB918BAAAAQQAAAAAA AACBKx+kvqMQGZ1uAN0BD1QCAAAAAFNRUi1VU0VSU0BVU0EuTkVUAFNNVFAAU1FSLVVTRVJTQFVT QS5ORVQAAAAAAwD9XwEAAAADAP9fAAAAAAIB9g8BAAAABAAAAAAAAALhQwEEgAEAHwAAAFJFOiBK dWxpYW4gZGF0ZXMgaW4gUGVvcGxlU29mdACdCgEFgAMADgAAAM4HAQAGAAUAGQAYAAIAFAEBIIAD AA4AAADOBwEABgAFABUAAgACAPoAAQmAAQAhAAAANzA1MjQ1QUI4Rjg2RDExMTk1ODk0MjAxODYw MDAwMDAAsQYBA5AGAFAIAAAhAAAACwACAAEAAAALACMAAAAAAAMAJgAAAAAACwApAAAAAAADAC4A AAAAAAMANgAAAAAAQAA5AOBfySmeGr0BHgBwAAEAAAAfAAAAUkU6IEp1bGlhbiBkYXRlcyBpbiBQ ZW9wbGVTb2Z0AAACAXEAAQAAABYAAAABvRqeKb2rRVJxho8R0ZWJQgGGAAAAAAAeAB4MAQAAAAUA AABTTVRQAAAAAB4AHwwBAAAAEAAAAHJnb3Noa29AaWJtLm5ldAADAAYQyGG2DgMABxC2BAAAHgAI EAEAAABlAAAAREVBTixJV1JPVEVBRlVOQ1RJT05USEFUQ09OVkVSVFNGVUxMSlVMSUFOREFURVMo TlVNQkVST0ZEQVlTU0lOQ0VKQU5VQVJZMSwwMDAxKVRPQU5BQ1RVQUxEQVRFLFlPVUNPVQAAAAAC AQkQAQAAADMFAAAvBQAAWggAAExaRnVfzULoAwAKAHJjcGcxMjUWMgD4C2BuDhAwMzOdAfcgAqQD 4wIAY2gKwOBzZXQwIAcTAoMAUH8EVRDZCFUHsgKDDlAQdnBocnEyEXZ9CoAIyCA2OwlvDjA1AoAK gXVjGwBQCwNjEhILxCBEZR0AcCwKogqECoBJIHcBA2B0ZSBhIGZ1kG5jdGkCICB0EQDTBUAFoG52 BJB0BCAbQLpsAyBKHMAHMAOgZBvwMQeRKG51BtATsW9mvR1heQQgAJAbYBsASgBwAnUKwHkgMSwg MOkf4DEpG8BvGxADoADQ3nQfYAMgHXIfwHkIYBwR8RzAZCB1ETAbwQQAHcDrB0ACIGcasGkb0BvB GwD/G0capiBBHCUjUxrgI3AFwGJ3HpApLiAK4xomWZ8hhyTmGZAfAB4DMzEeQu8jYhUAG5AFwHkZ oAXAIELzHPofwGFkIfAhcQXAHeU/AHAh8CNhA6Ak5iMgIGLVANBrKiRHCXBnBbAdNu8mQSFzA5EJ AG8twBvxI2rHBCAboxsAU1FSIgIFwEsJwAhgcBqwZWIpcGFaZxsAKAxgAyBoAkBwEDovL3cz0C5z cZBydWcuBaBtLzMhDxFgICALgCNTTGlicvcfchEwG3MuNoAkIR4QHSDeZRxQLVIicTUlIx7hCkDH AQA2BxnqSG9wIjUjcLhscHM5Oxh2EWBMHYH+cjaBGeQmUD1CCAAMMBn00RISczE2GeQtPx9AL6dB P0G1POhUaDIxZyNEaE5JQzaCTxxRI1N07wPxGvAh8AqwaTyiJmU9RC9EgAEgI1MIAHU8hCA+AD5T V0lTSDw83TaCTiWhC4Ai8GJHkAew2EVUIT6/SmYrSm9LXwdCfD2hJSJHb3Noa2sgUCZQfCZQci5g TxJAMTWgbS5uEUBPYyg0Aw9QICA0MTctQVjzSFA85UF4InEZkBxQCQB+cAeAAjAEIE+BU49PgFOt JcF3L9Ah8FAKwGsfwDxBQjtqFFE7eR0gMzavAUAVABrhG3AgTONPBRDWZwuAIOFNB5BzMtFCeR9X xlexCxNX5AIAaS0x5jRRAFdhMTgf4AzBW/NVMqBGA2E6DINiEWBNOE9SUkhQH8AZkiBboFNNVFA6 GZIuXhQgQFNVTkNeIFAtQk1JsFdBWS5gME3wLkFVXRnlMqAGYAIwn12IAiAegR/AHzYwNR/AADE5 OTggNTo1yWQwUE1hZ1RvXYgcwO8bgAtQGwAJcGMFIAiQUyIPHlEdIEVQMYItVVNFxlJRxWHCdWJq NiFdh7MdCzUhUGU6EGZgUx5Q/1ugWg9bGVd0C7YZ8xmRBcDmQRzQGdwnbRwQCHAJcP0CMGwfkFUA VXBJIhuhGyA/auhdMFjhG2AHMQQgdjf9LVBtZlFTAhvwG5JxMguQvxnzWKAA0GZhHXEBoGERMPE5 O1dlJwlwG8AfgEki/yBBW8AsYRsgRVAsUQsRGz3/A/Ac0XbQAHELYBrzHQQZ5L8dcjURIEF353sj AhByAMD+dCZBCJAmQBsgHeUeEEUg7wnhH6AsQ1eQNSxCGeQs6XcqMyjgW+AyW+BkEHLwdH55OiEu 1UNgInEJAFjQY903gmZFsXCBGaBzH5AgQP8Z5BrAIyAhQUliGqF1cDNQ/zoQdwUeEBsQAmAkkyIS d8n3MZIbSCBQZIPVIyCB8wSQ9XUBbweRbhrgNgEogIW17wIge1IjU4cJQ3dhZmA67LpDIHJuIXCL ETqiPxnq20NgAHBrN6MrUHZyYXWcF19DGeoVwQCSkAADABAQAAAAAAMAERABAAAAAwCAEP////9A AAcw0Cq1jZ0avQFAAAgw0Cq1jZ0avQELAACACCAGAAAAAADAAAAAAAAARgAAAAADhQAAAAAAAAMA AoAIIAYAAAAAAMAAAAAAAABGAAAAABCFAAAAAAAAAwAFgAggBgAAAAAAwAAAAAAAAEYAAAAAUoUA ALcNAAAeACWACCAGAAAAAADAAAAAAAAARgAAAABUhQAAAQAAAAQAAAA4LjAAAwAmgAggBgAAAAAA wAAAAAAAAEYAAAAAAYUAAAAAAAALAC+ACCAGAAAAAADAAAAAAAAARgAAAAAOhQAAAAAAAAMAMIAI IAYAAAAAAMAAAAAAAABGAAAAABGFAAAAAAAAAwAygAggBgAAAAAAwAAAAAAAAEYAAAAAGIUAAAAA AAAeAEGACCAGAAAAAADAAAAAAAAARgAAAAA2hQAAAQAAAAEAAAAAAAAAHgBCgAggBgAAAAAAwAAA AAAAAEYAAAAAN4UAAAEAAAABAAAAAAAAAB4AQ4AIIAYAAAAAAMAAAAAAAABGAAAAADiFAAABAAAA AQAAAAAAAAAeAD0AAQAAAAUAAABSRTogAAAAAAMADTT9NwAAgR0= ------ =_NextPart_000_01BD1A71.3307FCB0-- From owner-sqr-users@list.iex.net Tue Jan 6 13:07:23 1998 Date: Tue, 6 Jan 1998 12:48:51 -0500 From: Nathan Treadway Subject: Re: INTERACTIVE AND BATCH modes > My code so far goes as follows: > let $system_call = 'IF F$MODE() .NES. 'BATCH' THEN > MODE_TEST:="INTERACTIVE" ' > > call system using $system_call #status > > let $mode = getenv('MODE_TEST') > .. > .. > .. > > I've also tried let $system_call = 'MODE_TEST:=F$MODE()' > > The main issue is that the SYMBOL MODE_TEST doesn't seam to be > getting assigned. I've debuged the code to see what's getting what The first "trick" is that, under VMS, the "getenv" function looks at logicals, not symbols. Thus, something like this should work: let $system_call = 'define/job/nolog MODE_TEST ''F$MODE()' You need the /job so that the logical define is not tied to the spawned subprocess but rather to the whole job; otherwise it would be vanish before execution returned to your SQR program. The /nolog prevents a warning message from being printed if MODE_TEST already has a value (such as if you ran the program more than once). However, my tests show that when you use CALL SYSTEM USING, the F$MODE() is not evaluated they way it would be on the command line or in a .COM file. A work-around would be to use your "if" idea: let $system_call = 'if f$mode() .eqs. "INTERACTIVE" ' || 'then define/job/nolog "INTERACTIVE" ' You could also create a .com file that contained the line $ define/job/nolog MODE_TEST 'F$MODE() and call that .com file from the CALL SYSTEM USING. (It might be easier to simply put $ define MODE_TEST 'F$MODE() login.com or something like that; then you wouldn't need to spawn from within the SQR, and the define would run only once per login.) Nathan ---------------------------------------------------------------------------- Nathan Treadway | Ray Ontko & Co. | info@ontko.com (auto-reply server) nathant@ontko.com | Richmond, IN | ftp.ontko.com, http://www.ontko.com/ From owner-sqr-users@list.iex.net Tue Jan 6 14:46:02 1998 Date: Tue, 6 Jan 1998 11:26:07 -0800 From: Vicki Lo Subject: Draw a double box Has anyone use SQR to draw a slightly larger box outside a smaller box? If you do, please let me know how. Because I am not able to do it. Thank you. Vicki Email address: VICKIL@remedystaff.com From owner-sqr-users@list.iex.net Tue Jan 6 14:57:34 1998 Date: Tue, 6 Jan 1998 09:02:43 -0600 From: Frank Ellington Subject: Re: Help with SQL Bill, try this SQL-PLUS code: select c.deptid, q.union_cd, b.pay_end_dt, b.erncd, sum(b.oth_hrs), sum(b.oth_pay) from ps_pay_oth_earns b, ps_employment q, ps_job j, ps_pay_earnings c where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' and b.company = c.company and b.paygroup = c.paygroup and b.pay_end_dt = c.pay_end_dt and b.off_cycle = c.off_cycle and b.page# = c.page# and b.line# = c.line# and b.addl# = c.addl# and q.emplid = c.emplid and q.empl_rcd# = c.empl_rcd# and j.emplid = q.emplid and j.empl_rcd# = j.empl_rcd# and j.EFFDT = (SELECT MAX(JJ.EFFDT) from PS_JOB JJ where JJ.EMPLID = J.EMPLID AND JJ.EMPL_RCD# = J.EMPL_RCD# AND JJ.EFFDT <= b.pay_end_dt) and j.EFFSEQ = (SELECT MAX(JJJ.EFFSEQ) from PS_JOB JJJ where JJJ.EMPLID = J.EMPLID AND JJJ.EMPL_RCD# = J.EMPL_RCD# AND JJJ.EFFDT = J.EFFDT) group by c.deptid, q.union_cd, b.pay_end_dt, b.Erncd / gave me 55 rows in less than 30 seconds in test environment Good Luck Frank ______________________________ Reply Separator _________________________________ Subject: Help with SQL Author: SQR-USERS@USA.NET at MEMCPSMTP Date: 1/5/98 1:54 PM I am running the following sql within an sqr and it runs, and runs and runs. Anybody (familiar with PeopleSoft) have any ideas? select c.deptid, q.union_cd, b.pay_end_dt, b.erncd sum(b.oth_hrs), sum(b.oth_pay) from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' and b.company = c.company and b.paygroup = c.paygroup and b.pay_end_dt = c.pay_end_dt and b.page# = c.page# and b.line# = c.line# and b.addl# = c.addl# group by c.deptid, q.union_cd, b.pay_end_dt, b.Erncd Thanks in advance Bill Bowers St. Peters Hospital Olympia, Washington (360) 393-5437 From owner-sqr-users@list.iex.net Tue Jan 6 15:17:57 1998 Date: Tue, 6 Jan 1998 11:04:33 -0500 From: "Connaghan, Rebecca" Subject: Re: Help with SQL Bill, Shouldn't you have join criteria tying your employment table to the others in your WHERE clause? Rebecca > -----Original Message----- > From: Bill Bowers [SMTP:bowersb@PSPH.PROVIDENCE.ORG] > Sent: Monday, January 05, 1998 4:55 PM > To: Multiple recipients of list SQR-USERS > Subject: Help with SQL > > I am running the following sql within an sqr and it runs, and > runs and > runs. Anybody (familiar with PeopleSoft) have any ideas? > > > select > c.deptid, > q.union_cd, > b.pay_end_dt, > b.erncd > sum(b.oth_hrs), > sum(b.oth_pay) > from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c > where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' > and b.company = c.company > and b.paygroup = c.paygroup > and b.pay_end_dt = c.pay_end_dt > and b.page# = c.page# > and b.line# = c.line# > and b.addl# = c.addl# > group by c.deptid, > q.union_cd, > b.pay_end_dt, > b.Erncd > > Thanks in advance > > Bill Bowers > St. Peters Hospital > Olympia, Washington > (360) 393-5437 From owner-sqr-users@list.iex.net Tue Jan 6 17:32:05 1998 Date: Tue, 6 Jan 1998 12:47:06 PST From: Bill Bowers Subject: Re[2]: Help with SQL Thanks Frank Why did you add ps_job to the sql-plus code? Bill ______________________________ Reply Separator _________________________________ Subject: Re: Help with SQL Author: SQR-USERS@USA.NET at INTERNET Date: 1/6/98 12:06 PM Bill, try this SQL-PLUS code: select c.deptid, q.union_cd, b.pay_end_dt, b.erncd, sum(b.oth_hrs), sum(b.oth_pay) from ps_pay_oth_earns b, ps_employment q, ps_job j, ps_pay_earnings c where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' and b.company = c.company and b.paygroup = c.paygroup and b.pay_end_dt = c.pay_end_dt and b.off_cycle = c.off_cycle and b.page# = c.page# and b.line# = c.line# and b.addl# = c.addl# and q.emplid = c.emplid and q.empl_rcd# = c.empl_rcd# and j.emplid = q.emplid and j.empl_rcd# = j.empl_rcd# and j.EFFDT = (SELECT MAX(JJ.EFFDT) from PS_JOB JJ where JJ.EMPLID = J.EMPLID AND JJ.EMPL_RCD# = J.EMPL_RCD# AND JJ.EFFDT <= b.pay_end_dt) and j.EFFSEQ = (SELECT MAX(JJJ.EFFSEQ) from PS_JOB JJJ where JJJ.EMPLID = J.EMPLID AND JJJ.EMPL_RCD# = J.EMPL_RCD# AND JJJ.EFFDT = J.EFFDT) group by c.deptid, q.union_cd, b.pay_end_dt, b.Erncd / gave me 55 rows in less than 30 seconds in test environment Good Luck Frank ______________________________ Reply Separator _________________________________ Subject: Help with SQL Author: SQR-USERS@USA.NET at MEMCPSMTP Date: 1/5/98 1:54 PM I am running the following sql within an sqr and it runs, and runs and runs. Anybody (familiar with PeopleSoft) have any ideas? select c.deptid, q.union_cd, b.pay_end_dt, b.erncd sum(b.oth_hrs), sum(b.oth_pay) from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' and b.company = c.company and b.paygroup = c.paygroup and b.pay_end_dt = c.pay_end_dt and b.page# = c.page# and b.line# = c.line# and b.addl# = c.addl# group by c.deptid, q.union_cd, b.pay_end_dt, b.Erncd Thanks in advance Bill Bowers St. Peters Hospital Olympia, Washington (360) 393-5437 From owner-sqr-users@list.iex.net Tue Jan 6 19:22:17 1998 Date: Tue, 6 Jan 1998 15:32:57 -0800 From: Ken Gartman Subject: Re: Available: SQR Tree Utility (Attempt 2) Hi there, Wayne! I'd also like a copy of your Software. Happy Computing, Ken Gartman Maryland, USA Wayne Ivory wrote: > > Since I posted my original message I've had a few replies and noticed the example I had pasted into the message got all scrunched together (I hate that - so much for WYSIWYG). I'll try pasting it here using a different method - unfortunately I won't know if it works until after I've done it. In case it doesn't, imagine a Windows-Explorer type display. > > TAX016AU.SQR > > 35 Init-DateTime () > 36 Init-Number () > 37 Init-Report (45) > 51 Stdapi-Init () > 54 Prompt (1389) > 56 Select-Parameters () > 57 Get-Values (1444) > 60 Process-Parameters (1413) > 1431 Format-DateTime () > 1432 Format-DateTime () > 61 Array-Init (100) > 38 Process-Main (135) > 145 Get-Group-Name (1338) > 146 Build-company-list (1260) > 1277 Get-Company-Data () > 147 Get-Totals (157) > 169 Cmpny-State-Data (177) > 178 Pay-Earnings (192) > 223 Get-PayGroup-Erncd (726) > 739 Chk-Exempt-Erncd (771) > > ******** Original Message follows **************** > If anyone is interested I've written a Unix shell script that produces a Procedure-Call tree for an SQR program. It shows which procedures call which procedures, the line they are called from, and the line the called procedure is on. Here's a sample: > > TAX016AU.SQR > > 35 Init-DateTime () > 36 Init-Number () > 37 Init-Report (45) > 51 Stdapi-Init () > 54 Prompt (1389) > 56 Select-Parameters () > 57 Get-Values (1444) > 60 Process-Parameters (1413) > 1431 Format-DateTime () > 1432 Format-DateTime () > 61 Array-Init (100) > 38 Process-Main (135) > 145 Get-Group-Name (1338) > 146 Build-company-list (1260) > 1277 Get-Company-Data () > 147 Get-Totals (157) > 169 Cmpny-State-Data (177) > 178 Pay-Earnings (192) > 223 Get-PayGroup-Erncd (726) > 739 Chk-Exempt-Erncd (771) > > If you'd like a copy send me an email. > > Wayne Ivory > Information Services > Westralian Sands Limited > > Wayne Ivory > Information Services > Westralian Sands Limited > > > ! > ! > > ! > ! From owner-sqr-users@list.iex.net Tue Jan 6 19:41:08 1998 Date: Tue, 6 Jan 1998 15:21:53 -0800 From: Ken Gartman Subject: Re: Help with SQL Bill Bowers wrote: > > I am running the following sql within an sqr and it runs, and runs and > runs. Anybody (familiar with PeopleSoft) have any ideas? > > select > c.deptid, > q.union_cd, > b.pay_end_dt, > b.erncd > sum(b.oth_hrs), > sum(b.oth_pay) > from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c > where b.pay_end_dt between '01-JAN-97' and '31-DEC-97' > and b.company = c.company > and b.paygroup = c.paygroup > and b.pay_end_dt = c.pay_end_dt > and b.page# = c.page# > and b.line# = c.line# > and b.addl# = c.addl# > group by c.deptid, > q.union_cd, > b.pay_end_dt, > b.Erncd > > Thanks in advance > > Bill Bowers > St. Peters Hospital > Olympia, Washington > (360) 393-5437 Hi, Bill! There doesn't seem to be a join condition for the "q" table. You're getting a Cartesian product. This is not a problem of SQR or PeopleSoft, but of SQL. Happy Computing, Ken Gartman From owner-sqr-users@list.iex.net Tue Jan 6 20:55:10 1998 Date: Tue, 6 Jan 1998 16:00:42 -0800 From: Ken Gartman Subject: Re: Julian dates in PeopleSoft Hi there, Dean! Please don't be casual in differentiating "Julian Day" (the number of a day in a year) from "Julian Date" (the number of days since a specifice starting date.) Julian days range from 1 to 366. February 28th is always Julian day 59. March 1st is Julian day 60 some years, and Julian day 61 during other years. For any day of the year after February, the Julian day cannot be determined without knowing if it is a leap year or a regular year. There is no need to write your own function. Peoplesoft has a good set of date manipulation routines, and ORACLE has a better one. Happy computing, Ken Gartman MORRIS, Dean wrote: > > Dear All, > > I'm currently working on a PeopleSoft Financials v7 implementation on an > Oracle database. > > We're trying to find a standard function that will translate a Julian > date into standard date format. ie. a number between 1 and 365 and > convert it to a 31-12-1997 type date. This logic is fairly easy to > write, but I was hoping to be able to use a standard SQR function to do > it. There does not seem to be one in the standard SQC files. > > Can anyone help? > > Thanks in advance. > > Dean. From owner-sqr-users@list.iex.net Tue Jan 6 21:33:21 1998 Date: Wed, 7 Jan 1998 10:24:03 +0800 From: Wayne Ivory Subject: Re: Available: SQR Tree Utility (Attempt 2) This is a MIME message. If you are reading this text, you may want to consider changing to a mail reader or gateway that understands how to properly handle MIME multipart messages. --=_7B2EF073.71107C32 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline With compliments. And could you please do me a favour - I have sent it to = several people and asked them to let me know what they think. But apart = from one guy who said there was no attachment and asked me to resend, I = haven=27t heard anything. Could you please advise if you got it and if it works (and if you like = it). Wayne Ivory Information Services Westralian Sands Limited >>> Ken Gartman 7/1/98 7:32:57 am >>> Hi there, Wayne=21 I=27d also like a copy of your Software. Happy Computing, Ken Gartman Maryland, USA Wayne Ivory wrote: > > Since I posted my original message I=27ve had a few replies and noticed = the example I had pasted into the message got all scrunched together (I = hate that - so much for WYSIWYG). I=27ll try pasting it here using a = different method - unfortunately I won=27t know if it works until after = I=27ve done it. In case it doesn=27t, imagine a Windows-Explorer type = display. > > TAX016AU.SQR > > 35 Init-DateTime () > 36 Init-Number () > 37 Init-Report (45) > 51 Stdapi-Init () > 54 Prompt (1389) > 56 Select-Parameters () > 57 Get-Values (1444) > 60 Process-Parameters (1413) > 1431 Format-DateTime () > 1432 Format-DateTime () > 61 Array-Init (100) > 38 Process-Main (135) > 145 Get-Group-Name (1338) > 146 Build-company-list (1260) > 1277 Get-Company-Data () > 147 Get-Totals (157) > 169 Cmpny-State-Data (177) > 178 Pay-Earnings (192) > 223 Get-PayGroup-Erncd (726) > 739 Chk-Exempt-Erncd (771) > > ******** Original Message follows **************** > If anyone is interested I=27ve written a Unix shell script that produces = a Procedure-Call tree for an SQR program. It shows which procedures call = which procedures, the line they are called from, and the line the called = procedure is on. Here=27s a sample: > > TAX016AU.SQR > > 35 Init-DateTime () > 36 Init-Number () > 37 Init-Report (45) > 51 Stdapi-Init () > 54 Prompt (1389) > 56 Select-Parameters () > 57 Get-Values (1444) > 60 Process-Parameters (1413) > 1431 Format-DateTime () > 1432 Format-DateTime () > 61 Array-Init (100) > 38 Process-Main (135) > 145 Get-Group-Name (1338) > 146 Build-company-list (1260) > 1277 Get-Company-Data () > 147 Get-Totals (157) > 169 Cmpny-State-Data (177) > 178 Pay-Earnings (192) > 223 Get-PayGroup-Erncd (726) > 739 Chk-Exempt-Erncd (771) > > If you=27d like a copy send me an email. > > Wayne Ivory > Information Services > Westralian Sands Limited > > Wayne Ivory > Information Services > Westralian Sands Limited > > > =21 > =21 > > =21 > =21 --=_7B2EF073.71107C32 Content-Type: application/octet-stream; name="sqrtree" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="sqrtree" IyBQcmludCB0aGUgY2FsbC10cmVlIHN0cnVjdHVyZSBvZiBhbiBTUVIgcHJvZ3JhbQ0KIyBDb3B5 cmlnaHQgMTk5NyBieSBXYXluZSBJdm9yeSAtIHdpdm9yeUB3c2wuY29tLmF1DQojIFRoaXMgc2hl bGwgc2NyaXB0IG1heSBiZSBmcmVlbHkgY29waWVkIGFuZCBkaXN0cmlidXRlZCBhcyBsb25nIGFz IHRoZQ0KIyBjb3B5cmlnaHQgbm90aWNlIHJlbWFpbnMgaW50YWN0Lg0KDQppZiBbICQjIC1uZSAx IF0NCnRoZW4NCgllY2hvICJVc2FnZTogICQwIHNxcmZpbGUiDQoJZXhpdCAxDQpmaQ0KDQphd2sg Jw0KCWZ1bmN0aW9uIHByaW50dHJlZShQTkFNRSxJTkRFTlQsZikgew0KCQlwcmludCBJTkRFTlQg UE5BTUUgIiAoIiBQUk9DTElORVtQTkFNRV0gIikiDQoJCWZvciAoZj0xIDsgZjw9Q0FMTENPVU5U W1BOQU1FXSA7IGYrKykge3ByaW50ZigiJS02cyIsQ0FMTExJTkVbUE5BTUUsZl0pIDsgcHJpbnR0 cmVlKENBTExFRFBST0NbUE5BTUUsZl0sSU5ERU5UICIgICAgIil9DQoJfQ0KCUJFR0lOe0ZOQU1F PUZJTEVOQU1FIDsgd2hpbGUgKGk9aW5kZXgoRk5BTUUsIi8iKSkgRk5BTUU9c3Vic3RyKEZOQU1F LGkrMSkgOyBwcmludCBGTkFNRSA7IHByaW50ICIgIn0NCnsNCglpZiAoJDEgPT0gImJlZ2luLXBy b2NlZHVyZSIpIHsNCgkJUE9TPW1hdGNoKCQyLCJbXi1BLVphLXowLTldIikNCgkJaWYgKFBPUyA9 PSAwKSBQT1M9bGVuZ3RoKCQyKSsxDQoJCVBST0NFRFVSRT1zdWJzdHIoJDIsMSxQT1MtMSkNCgkJ UFJPQ0xJTkVbUFJPQ0VEVVJFXT1OUg0KCX0gDQoJaT1tYXRjaCgkMCwiW0RkXVtPb10gIikNCglp ZiAoaSA+IDAgJiYgc3Vic3RyKCQxLDEsMSkgIT0gIiEiICYmIHN1YnN0cigkMSwxLDYpICE9ICIj ZGVidWciKSB7DQoJCWo9bWF0Y2goc3Vic3RyKCQwLGkrMyksIlteIF0iKQ0KCQlpPWkraisyDQoJ CVBPUz1tYXRjaChzdWJzdHIoJDAsaSksIlteLUEtWmEtejAtOV0iKQ0KCQlpZiAoUE9TID09IDAp IFBPUz1sZW5ndGgoJDApLWkrMg0KCQlDQUxMQ09VTlRbUFJPQ0VEVVJFXSsrDQoJCUNBTExFRFBS T0NbUFJPQ0VEVVJFLENBTExDT1VOVFtQUk9DRURVUkVdXT1zdWJzdHIoJDAsaSxQT1MtMSkNCgkJ Q0FMTExJTkVbUFJPQ0VEVVJFLENBTExDT1VOVFtQUk9DRURVUkVdXT1OUg0KCX0NCn0gRU5Ee2Zv ciAoaT0xIDsgaTw9Q0FMTENPVU5UWyIiXSA7IGkrKykge3ByaW50ZigiJS02cyIsQ0FMTExJTkVb IiIsaV0pIDsgcHJpbnR0cmVlKENBTExFRFBST0NbIiIsaV0sIiIpfX0nICQxDQo= --=_7B2EF073.71107C32-- From owner-sqr-users@list.iex.net Wed Jan 7 00:29:59 1998 Date: Wed, 7 Jan 1998 11:17:26 +1100 From: Steve Cavill Subject: Re: Draw a double box This is a multi-part message in MIME format. --------------73155ACCA4C186B14DF49592 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit This should be fairly straightforward. Here's a sample. Let me know if it doesn't work. Cheers, Steve. Vicki Lo wrote: > Has anyone use SQR to draw a slightly larger box outside a smaller box? > If you do, please let me know how. Because I am not able to do it. > Thank you. > > Vicki > Email address: VICKIL@remedystaff.com --------------73155ACCA4C186B14DF49592 Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Steve Cavill Content-Disposition: attachment; filename="vcard.vcf" begin: vcard fn: Steve Cavill n: Cavill;Steve org: SQRIBE Technologies adr;dom: 1/14 Aquatic Drive;;;Frenchs Forest;;; email;internet: steve.cavill@sqribe.com.au title: Customer Services Manager x-mozilla-cpt: ;0 x-mozilla-html: TRUE version: 2.1 end: vcard --------------73155ACCA4C186B14DF49592-- From owner-sqr-users@list.iex.net Wed Jan 7 00:50:24 1998 Date: Tue, 6 Jan 1998 16:35:11 -0700 From: Ben Le Subject: Re: Available: SQR Tree Utility (Attempt 2) Wayne, I'd like to have one too. Thanks. My e-mail: ble@pcc.edu At 03:32 PM 1/6/98 -0800, you wrote: >Hi there, Wayne! > I'd also like a copy of your Software. > > Happy Computing, > Ken Gartman > Maryland, USA > > >Wayne Ivory wrote: >> >> Since I posted my original message I've had a few replies and noticed the example I had pasted into the message got all scrunched together (I hate that - so much for WYSIWYG). I'll try pasting it here using a different method - unfortunately I won't know if it works until after I've done it. In case it doesn't, imagine a Windows-Explorer type display. >> >> TAX016AU.SQR >> >> 35 Init-DateTime () >> 36 Init-Number () >> 37 Init-Report (45) >> 51 Stdapi-Init () >> 54 Prompt (1389) >> 56 Select-Parameters () >> 57 Get-Values (1444) >> 60 Process-Parameters (1413) >> 1431 Format-DateTime () >> 1432 Format-DateTime () >> 61 Array-Init (100) >> 38 Process-Main (135) >> 145 Get-Group-Name (1338) >> 146 Build-company-list (1260) >> 1277 Get-Company-Data () >> 147 Get-Totals (157) >> 169 Cmpny-State-Data (177) >> 178 Pay-Earnings (192) >> 223 Get-PayGroup-Erncd (726) >> 739 Chk-Exempt-Erncd (771) >> >> ******** Original Message follows **************** >> If anyone is interested I've written a Unix shell script that produces a Procedure-Call tree for an SQR program. It shows which procedures call which procedures, the line they are called from, and the line the called procedure is on. Here's a sample: >> >> TAX016AU.SQR >> >> 35 Init-DateTime () >> 36 Init-Number () >> 37 Init-Report (45) >> 51 Stdapi-Init () >> 54 Prompt (1389) >> 56 Select-Parameters () >> 57 Get-Values (1444) >> 60 Process-Parameters (1413) >> 1431 Format-DateTime () >> 1432 Format-DateTime () >> 61 Array-Init (100) >> 38 Process-Main (135) >> 145 Get-Group-Name (1338) >> 146 Build-company-list (1260) >> 1277 Get-Company-Data () >> 147 Get-Totals (157) >> 169 Cmpny-State-Data (177) >> 178 Pay-Earnings (192) >> 223 Get-PayGroup-Erncd (726) >> 739 Chk-Exempt-Erncd (771) >> >> If you'd like a copy send me an email. >> >> Wayne Ivory >> Information Services >> Westralian Sands Limited >> >> Wayne Ivory >> Information Services >> Westralian Sands Limited >> >> >> ! >> ! >> >> ! >> ! > > Ben ITS - 977-4970 Mailto:ble@pcc.edu From owner-sqr-users@list.iex.net Wed Jan 7 13:25:44 1998 Date: Wed, 7 Jan 1998 07:36:17 +0000 From: David Stanley SICAS Center Subject: Batch VS Intractive and output directory Platform: Alpha OS: VMS 6.1 SQR: 3.12.... My thanx to everyone in there response to this topic. The team feels after researching this that we will go with my original idea to make this an input parameter. On another subject... I would like to redirect the default directory to the current directory that I'm in rather than SYS$LOGIN or the directory in which the SQR resides. We use the new-report option, which will direct the SQR directory as a default, or we can easily change the default to SYS$LOGIN. Is there a way in which the current directory can be made default??? Thanx in advance.... David Stanley Lead Programmer SICAS Center SUNY Oneonta If you take a shovel and start digging, you will eventaully be standing in a hole. stanledd@oneonta.edu http://www.oneonta.edu/~stanledd/ From owner-sqr-users@list.iex.net Wed Jan 7 14:51:33 1998 Date: Wed, 7 Jan 1998 11:22:06 -0800 From: Vicki Lo Subject: Typeface value of Arial and Symbol fonts Does anyone has any idea of the typeface value of Arial and Symbol fonts? To select different fonts on the same report in SQR, I believe we could either Use "declare printer" or "graphic font" to change fonts. However, we need To know the typeface value of each font before we can select it. The font number Listed in the SQR manual is differ from the PCL Font List from the HP printer and The manual does not list all the fonts that's being commonly used. Anyone has A list of the font number, please send it to me. I am greatly appreciated for your help. Vicki Lo Email: VICKIL@remedystaff.com From owner-sqr-users@list.iex.net Thu Jan 8 02:16:19 1998 Date: Wed, 7 Jan 1998 16:31:40 -0800 From: Scott Gunter Subject: SQLBase & SQR Help I need to know how to access the current date in an SQR Batch program that is connecting to a SQLBase database. I use SYSDATE in "where" clauses, but it doesn't work in other contexts. Thanks in advance for any help. Scott ksgunter@tdyryan.com From owner-sqr-users@list.iex.net Thu Jan 8 05:16:59 1998 Date: Thu, 8 Jan 1998 00:26:23 -0800 From: Chris Snow Subject: Re: SQLBase & SQR Help This is a multi-part message in MIME format. ------=_NextPart_000_0041_01BD1BCC.0CCD4760 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Scott, Each of the database platforms handle datefields differently. For SQLBase, if you are selecting a date field from a table, one possible syntax would be: begin-select @datetochar(datefield, 'dd-Mon-yy') &datefield from sometable end-select where "datefield" is the column name of the field you're selecting and "&datefield" is any variable name for the converted value. The string, 'dd-Mon-yy' can of course be any acceptable date format and edits the results. The value can be printed at the select by including the print expression at the end of the variable, such as @datetochar(datefield, 'dd-Mon-yy') &datefield (+1,1) or, print it somewhere else in the program as usual. Again, this only works for SQLBase. Oracle, Sybase, and SQLServer (ODBC) are all different. There are also lots of other ways to select and manipulate these date fields, but this one selects and converts all in one fail swoop. Hope this helps. __________________________________ Chris Snow, Integrity Information Services 800-724-5939 pgr. chris_snow@msn.com 'As knowledge increases, wonder deepens' Charles Morgan -----Original Message----- From: Scott Gunter To: Multiple recipients of list SQR-USERS Date: Wednesday, January 07, 1998 11:20 PM Subject: SQLBase & SQR Help I need to know how to access the current date in an SQR Batch program that is connecting to a SQLBase database. I use SYSDATE in "where" clauses, but it doesn't work in other contexts. Thanks in advance for any help. Scott ksgunter@tdyryan.com ------=_NextPart_000_0041_01BD1BCC.0CCD4760 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Scott,
 
Each of the database platforms handle datefields=20 differently.  For SQLBase, if you are selecting a date field from a = table,=20 one possible syntax would be:
 
begin-select
@datetochar(datefield, 'dd-Mon-yy')=20 &datefield
from sometable 
end-select 
 
where "datefield" is the column name of = the field=20 you're selecting and "&datefield" is any variable name for = the=20 converted value.  The string, 'dd-Mon-yy' can of course be any = acceptable=20 date format and edits the results.  The value can be printed at the = select=20 by including the print expression at the end of the variable, such=20 as
 
@datetochar(datefield, 'dd-Mon-yy') = &datefield (+1,1)
 
or, print it somewhere else in the = program as=20 usual.
 
Again, this only works for SQLBase.  Oracle, = Sybase, and=20 SQLServer (ODBC) are all different.  There are also lots of other = ways to=20 select and manipulate these date fields, but this one selects and = converts all=20 in one fail swoop.
Hope this helps. 
 
__________________________________
Chris=20 Snow, Integrity Information Services
800-724-5939 pgr.
chris_snow@msn.com
'As = knowledge=20 increases, wonder=20 deepens'
          &= nbsp;           &n= bsp;           &nb= sp;       =20 Charles Morgan 
 
-----Original = Message-----
From:=20 Scott Gunter <ksgunter@TDYRYAN.COM>
T= o:=20 Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
= Date:=20 Wednesday, January 07, 1998 11:20 PM
Subject: SQLBase = & SQR=20 Help

I need to know how to access the current date = in=20 an
SQR Batch program that is connecting to a = SQLBase
database.

I=20 use SYSDATE in "where" clauses, but it doesn't work
in = other=20 contexts.

Thanks in advance for any help.

Scott
ksgunter@tdyryan.com
<= /HTML> ------=_NextPart_000_0041_01BD1BCC.0CCD4760-- From owner-sqr-users@list.iex.net Thu Jan 8 06:24:38 1998 Date: Thu, 8 Jan 1998 11:12:39 +0000 From: Karen Wickham Subject: Expansion/Cross-referencing tool Platform: SQL Server OS: NT SQR: v3 We are currently customising PeopleSoft delivered SQRs. Some of which have 30+ #include statements. Have you used/know of an expansion/cross-referencing tool which will document the progra Karen Wickham, Anchor Trust. From owner-sqr-users@list.iex.net Thu Jan 8 08:33:53 1998 Date: Thu, 8 Jan 1998 08:22:26 -0400 From: Kelly Korb Subject: SQR Training We are a new group of SQR users. We are using SQR with Peoplesoft and have attended the classes given by PeopleSoft. Does anyone know of additional training classes that are available for SQR? Thanks in advance for help in this matter. Kelly From owner-sqr-users@list.iex.net Thu Jan 8 10:57:25 1998 Date: Thu, 8 Jan 1998 06:55:47 -0600 From: Doug Harper Subject: Re: SQR Training For additional classes on SQR check out the SQRIBE web site. www.sqribe.com -----Original Message----- From: Kelly Korb To: Multiple recipients of list SQR-USERS Date: Thursday, January 08, 1998 7:34 AM Subject: SQR Training >We are a new group of SQR users. We are using SQR with Peoplesoft and have >attended the classes given by > PeopleSoft. Does anyone know of additional training classes that are >available for SQR? > > Thanks in advance for help in this matter. > > Kelly > From owner-sqr-users@list.iex.net Thu Jan 8 11:21:08 1998 Date: Thu, 8 Jan 1998 10:21:59 -0500 From: CSC Consulting Subject: PCL to PS Does anyone know of a PCL to PostScript converter/translator besides "lj2ps", "psroff3.0", or "hp2pbm". (I have had difficulty getting any of these to work properly). Essentially, I have some old SQR's in PCL that I need to fax out or email in PDF format. The old PCL files either have "all" PCL or ASCII with a one line PCL init header. For the faxes, I need either ASCII or PostScript. For email, I need PostScript since I already use the Adobe Acrobat Distiller to covert PS to PDF. Thanks in advance. Tom R. Loden CSC Consulting From owner-sqr-users@list.iex.net Thu Jan 8 13:22:18 1998 Date: Thu, 8 Jan 1998 08:40:27 -0600 From: "Cooper, Marcus" Subject: Re: SQLBase & SQR Help You might want to try this Begin-select @DateToChar(sysdate,'MM/DD/YYYY') &YourVar from TableName end-select Where TableName has only 1 or 2 rows so it doesn't take any time. Then, of course, you can use &YourVar as your variable for the current date I hope this helps Marcus Cooper Grant Thornton LLP mcooper@apl.gt-wis.com -----Original Message----- From: Scott Gunter [SMTP:ksgunter@TDYRYAN.COM] Sent: Wednesday, January 07, 1998 6:32 PM To: Multiple recipients of list SQR-USERS Subject: SQLBase & SQR Help I need to know how to access the current date in an SQR Batch program that is connecting to a SQLBase database. I use SYSDATE in "where" clauses, but it doesn't work in other contexts. Thanks in advance for any help. Scott ksgunter@tdyryan.com From owner-sqr-users@list.iex.net Thu Jan 8 13:51:38 1998 Date: Thu, 8 Jan 1998 10:20:52 -0500 From: Eric Hoffman Subject: Re: Expansion/Cross-referencing tool http://www.nau.edu/hr/hris/readme.htm Try the url above. Eric ______________________________ Reply Separator _________________________________ Subject: Expansion/Cross-referencing tool Author: SQR-USERS@USA.NET at Internet Date: 1/8/98 11:12 AM Platform: SQL Server OS: NT SQR: v3 We are currently customising PeopleSoft delivered SQRs. Some of which have 30+ #include statements. Have you used/know of an expansion/cross-referencing tool which will document the progra Karen Wickham, Anchor Trust. From owner-sqr-users@list.iex.net Thu Jan 8 13:58:40 1998 Date: Thu, 8 Jan 1998 09:34:14 -0600 From: "Jeremy C. Radwan" Subject: Re: SQLBase & SQR Help At 04:31 PM 1/7/98 -0800, you wrote: >I need to know how to access the current date in an >SQR Batch program that is connecting to a SQLBase >database. You can just write a select statement to give you the date right from SQLBase. I'm not familiar with SQLBase per se, but there is probably a table with one row, or a dummy table (like DUAL in Oracle). You can right a select in a procedure like: begin-select SYSDATE &Date move &Date to $Date from DUAL end-select Hope this helps, Jeremy --- Jeremy C. Radwan - http://www.concentric.net/~jradwan/ "Although it is not exactly 'real,' 'cyberspace' is a genuine place. Things happen there that have genuine consequences." - Bruce Sterling, "The Hacker Crackdown" From owner-sqr-users@list.iex.net Thu Jan 8 14:17:13 1998 Date: Thu, 8 Jan 1998 09:36:25 -0600 From: "Jeremy C. Radwan" Subject: Re: SQR Training At 08:22 AM 1/8/98 -0400, you wrote: > PeopleSoft. Does anyone know of additional training classes that are >available for SQR? Before I started using PeopleSoft and SQR I was sent to a training company in Detroit that taught straight SQR (non-PeopleSoft related) that went into a lot more detail than the PeopleSoft class. I don't remember the name of the company, but I do know they were an approved MITI training center. MITI is now SQRIBE, but I'm sure if you contact them (or check the website) they should be able to point you in the direction of training companies that teach SQR. Jeremy --- Jeremy C. Radwan - http://www.concentric.net/~jradwan/ "Although it is not exactly 'real,' 'cyberspace' is a genuine place. Things happen there that have genuine consequences." - Bruce Sterling, "The Hacker Crackdown" From owner-sqr-users@list.iex.net Thu Jan 8 15:21:19 1998 Date: Thu, 8 Jan 1998 08:34:44 -0700 From: "Jensen, Ray - Broomfield" Subject: Re: SQR Training Kelly - The company I work for offers training classes for SQR. For more information you can contact myself or call 800-755-8872 and ask for Kathleen Rinker, or you can check or page on the internet - www.tusc.com Ray Jensen The Ultimate Software Consultants Senior Consultant 303-664-2821 ray.jensen@cexp.com jensenr@tusc.com >-----Original Message----- >From: Kelly Korb [SMTP:Kelly_D._Korb@WESTFIELD-COS.COM] >Sent: Thursday, January 08, 1998 5:22 AM >To: Multiple recipients of list SQR-USERS >Subject: SQR Training > >We are a new group of SQR users. We are using SQR with Peoplesoft and have >attended the classes given by > PeopleSoft. Does anyone know of additional training classes that are >available for SQR? > > Thanks in advance for help in this matter. > > Kelly From owner-sqr-users@list.iex.net Thu Jan 8 17:55:46 1998 Date: Thu, 8 Jan 1998 17:38:11 -0500 From: Maury Davis Subject: Call WINEXEC from SQR? Can anyone help with the following: Trying to call a Windows executable (WINEXEC) from within an SQR? Any help would be appreciated. Thanks, Maury Davis From owner-sqr-users@list.iex.net Thu Jan 8 20:19:07 1998 Date: Thu, 8 Jan 1998 08:58:00 -0500 From: Wendy Stern Subject: Re: SQR Training I went to the SQRibe web site. They offer education courses by different groups. I took Advanced SQR a 2 day course from Context Intergration. I found it very helpful. I had been coding SQR for about a year when I took it. > ---------- > From: Kelly Korb[SMTP:Kelly_D._Korb@WESTFIELD-COS.COM] > Sent: Thursday, January 08, 1998 7:22 AM > To: Multiple recipients of list SQR-USERS > Subject: SQR Training > > We are a new group of SQR users. We are using SQR with Peoplesoft and > have > attended the classes given by > PeopleSoft. Does anyone know of additional training classes that > are > available for SQR? > > Thanks in advance for help in this matter. > > Kelly > From owner-sqr-users@list.iex.net Thu Jan 8 21:34:14 1998 Date: Thu, 8 Jan 1998 08:39:51 -0500 From: Sam Spritzer Subject: SQR Training -Reply Hi Kelly, You might want to check the SQRIBE web page for certified trainers. The URL is www.SQRIBE.com. I have found that nothing beats "hand-on" experience training along with working with someone who is extremely knowledgeable in SQR. Good luck! Sam >>> Kelly Korb 01/08/98 07:22am >>> We are a new group of SQR users. We are using SQR with Peoplesoft and have attended the classes given by PeopleSoft. Does anyone know of additional training classes that are available for SQR? Thanks in advance for help in this matter. Kelly From owner-sqr-users@list.iex.net Fri Jan 9 02:50:51 1998 Date: Thu, 8 Jan 1998 15:39:40 -0800 From: Scott Gunter Subject: Re: SQLBase & SQR Help Jeremy, Thanks for the help. I don't know if there is a table in SQLBase like the one you describe in Oracle. However the following worked for me: date-time () YYYY &curryear !Get current year from SQLBase let $currentyear = &curryear I placed this code in my Init-Report procedure (not within a begin-select). It returns the current year from SQLBase. Now I am trying to select records from a table based on whether the column "termination_dt" has a year value equal to $currentyear. I tried using the "substr" function on the termination_dt (i.e. substr(C.termination_dt,7,4) = $currentyear) but this only causes a "missing right parentheses" error). If you have any suggestions I'm all ears. Here is the (trimmed down version) Select statement: begin-SELECT A.emplid A.orig_hire_dt A.name B.empl_status A.ssn B.File_nbr C.termination_dt add 1 to #TotalRecords Do WriteRecordOut FROM PS_Personal_Data A, PS_Job B, PS_Employment C WHERE A.emplid = B.emplid AND C.emplid = B.emplid AND C.empl_rcd_nbr = B.empl_rcd_nbr AND (B.empl_status in ('A','L','P') OR (B.empl_status in ('D','R','T') AND substr(C.termination_dt,7,4) = $currentyear)) AND B.Effdt = (SELECT MAX(Effdt) FROM PS_Job B1 WHERE B1.Emplid = B.Emplid AND B1.Empl_Rcd_Nbr = B.Empl_Rcd_Nbr AND B1.Effdt <= SYSDATE) AND B.EffSeq = (SELECT MAX(EffSeq) FROM PS_JOB B2 WHERE B2.Emplid = B.Emplid AND B2.Empl_Rcd_Nbr = B.Empl_Rcd_Nbr AND B2.Effdt = B.Effdt) ORDER BY A.emplid end-SELECT Thanks again. Scott Gunter ksgunter@tdyryan.com Teledyne Ryan Aeronautical 619/291-7311 x1741 At 09:34 AM 1/8/98 -0600, you wrote: >At 04:31 PM 1/7/98 -0800, you wrote: > >>I need to know how to access the current date in an >>SQR Batch program that is connecting to a SQLBase >>database. > >You can just write a select statement to give >you the date right from SQLBase. I'm not familiar >with SQLBase per se, but there is probably a table >with one row, or a dummy table (like DUAL in Oracle). >You can right a select in a procedure like: > >begin-select >SYSDATE &Date > move &Date to $Date >from DUAL >end-select > >Hope this helps, >Jeremy > > >--- >Jeremy C. Radwan - >http://www.concentric.net/~jradwan/ >"Although it is not exactly 'real,' 'cyberspace' is a genuine place. Things >happen > there that have genuine consequences." - Bruce Sterling, "The Hacker >Crackdown" > > Scott Gunter IS Representative SR Teledyne Ryan Aeronautical 619/291-7311 x1741 From owner-sqr-users@list.iex.net Fri Jan 9 05:33:50 1998 Date: Fri, 9 Jan 1998 08:13:53 -0200 From: Romeu Finoti Sciotta Subject: Re: Typeface value of Arial and Symbol fonts This is a multi-part message in MIME format. ------=_NextPart_000_001D_01BD1CD6.85FEF6A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Vicki. You have to verify the "SQR Fonts" session in WIN.INI file and you = should include others fonts later. Example: [SQR Fonts] 3=3DCourier New,fixed 300=3DCourier New,fixed,bold 4=3DArial,proportional 400=3DArial,proportional,bold 5=3DTimes New Roman,proportional 500=3DTimes New Roman,proportional,bold 6=3DAvantGarde,proportional 8=3DPalatino,proportional 800=3DPalatino,proportional,bold 11=3DSymbol,symbol After it, you should include in your program following instructions: Example: Begin-setup #declare Courier 3 #declare Arial 4 End-setup Begin-Program . . Alter-Printer Font=3D{Arial} Point-Size=3D12 . . Alter-Printer Font=3D{Courier} Point-Size=3D10 . . End-Program I expect that I could help you. Regards. Romeu F. Sciotta Alcan Aluminio do Brasil Ltda. romeufs@mandic.com.br -----Mensagem original----- De: Vicki Lo Para: Multiple recipients of list SQR-USERS Data: Quarta-feira, 7 de Janeiro de 1998 17:54 Assunto: Typeface value of Arial and Symbol fonts >Does anyone has any idea of the typeface value of Arial and Symbol >fonts? >To select different fonts on the same report in SQR, I believe we >could either >Use "declare printer" or "graphic font" to change fonts. However, we >need >To know the typeface value of each font before we can select it. The >font number >Listed in the SQR manual is differ from the PCL Font List from the HP >printer and >The manual does not list all the fonts that's being commonly used. >Anyone has >A list of the font number, please send it to me. I am greatly >appreciated for your help. > >Vicki Lo >Email: VICKIL@remedystaff.com > ------=_NextPart_000_001D_01BD1CD6.85FEF6A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi Vicki.

You have to verify the "SQR Fonts" = session in=20 WIN.INI file and you should
include others fonts=20 later.

Example:
[SQR Fonts]
3=3DCourier = New,fixed
300=3DCourier=20 New,fixed,bold
4=3DArial,proportional
400=3DArial,proportional,bold=
5=3DTimes=20 New Roman,proportional
500=3DTimes New=20 Roman,proportional,bold
6=3DAvantGarde,proportional
8=3DPalatino,pr= oportional
800=3DPalatino,proportional,bold
11=3DSymbol,symbol
<= BR>After=20 it, you should include in your program following=20 instructions:

Example:
Begin-setup
    = #declare=20 Courier 3
    #declare Arial=20 4
End-setup

Begin-Program
.
.
   = Alter-Printer=20 Font=3D{Arial} Point-Size=3D12
.
.
   Alter-Printer=20 Font=3D{Courier} Point-Size=3D10
.
.
End-Program

I = expect that I=20 could help you.

Regards.

Romeu F. Sciotta
Alcan = Aluminio do=20 Brasil Ltda.
romeufs@mandic.com.br

--= ---Mensagem=20 original-----
De: Vicki Lo <VICKIL@REMEDYSTAFF.COM>
= Para:=20 Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
= Data:=20 Quarta-feira, 7 de Janeiro de 1998 17:54
Assunto: Typeface value of = Arial and=20 Symbol fonts


>Does anyone has any idea of the typeface = value of=20 Arial and Symbol
>fonts?
>To select different fonts on the = same=20 report in SQR, I believe we
>could either
>Use "declare = printer" or "graphic font"  to change fonts. = However,=20 we
>need
>To know the typeface value of each font before we = can=20 select it. The
>font number
>Listed in the SQR manual is = differ from=20 the PCL Font List from the HP
>printer and
>The manual does = not list=20 all the fonts that's being commonly used.
>Anyone has
>A = list of the=20 font number, please send it to me. I am greatly
>appreciated for = your=20 help.
>
>Vicki Lo
>Email: VICKIL@remedystaff.com
>=
 
------=_NextPart_000_001D_01BD1CD6.85FEF6A0-- From owner-sqr-users@list.iex.net Fri Jan 9 05:53:22 1998 Date: Thu, 8 Jan 1998 19:43:42 -0600 From: Rakesh Kunta Subject: Re: SQR Training Hi Kelly, Here is the site that you might want to look at: http://www.sqribe.com/SERVER/servind.htm I took the training with Source Information two months back. I took both the Part 1and 2 for SQR Programmers. It was really very nice. Can you do me a favour. Actually, right now there is not a whole lot of SQR development in my company which is where i want to be. I also have PeopleTools certification. So, if you guys are looking for any SQR people w/ Peoplesoft, can you let me know. I can give you more details in case you need. I really appreciate your time. Thanks in advance. Rakesh. Sr. Systems Analyst. 817-258-1625. Kelly_D._Korb@WESTFIELD-COS.COM on 01/08/98 06:22:26 AM Please respond to SQR-USERS@USA.NET To: SQR-USERS@list.iex.net cc: (bcc: Rakesh Kunta/HQ/PCS PrimeCo) Subject: SQR Training We are a new group of SQR users. We are using SQR with Peoplesoft and have attended the classes given by PeopleSoft. Does anyone know of additional training classes that are available for SQR? Thanks in advance for help in this matter. Kelly From owner-sqr-users@list.iex.net Fri Jan 9 07:37:30 1998 Date: Thu, 8 Jan 1998 19:36:43 -0500 From: "Matthew F. Reagan" Subject: Re: SQR Training Kelly, My company (I am the instructor) also offers certified SQR training specializing in on-site customized classes. I was teaching SQR back when the product was owned by SQC (then D&N, then SQL*Solutions, then Sybase, then MITI, now called SQRIBE). Please call me at (610)651-6259 if you wish to discuss details. Matt... At 08:22 AM 1/8/98 -0400, you wrote: >We are a new group of SQR users. We are using SQR with Peoplesoft and have >attended the classes given by > PeopleSoft. Does anyone know of additional training classes that are >available for SQR? > > Thanks in advance for help in this matter. > > Kelly > > From owner-sqr-users@list.iex.net Fri Jan 9 13:09:11 1998 Date: Fri, 9 Jan 1998 08:16:32 -0600 From: Wes Williams Subject: Re: SQLBase & SQR Help -Reply Scott try the following: @yearno(C.termination_dt) = $currentyear Wes >>> Scott Gunter 01/08/98 05:39pm >>> Jeremy, Thanks for the help. I don't know if there is a table in SQLBase like the one you describe in Oracle. However the following worked for me: date-time () YYYY &curryear !Get current year from SQLBase let $currentyear = &curryear I placed this code in my Init-Report procedure (not within a begin-select). It returns the current year from SQLBase. Now I am trying to select records from a table based on whether the column "termination_dt" has a year value equal to $currentyear. I tried using the "substr" function on the termination_dt (i.e. substr(C.termination_dt,7,4) = $currentyear) but this only causes a "missing right parentheses" error). If you have any suggestions I'm all ears. Here is the (trimmed down version) Select statement: begin-SELECT A.emplid A.orig_hire_dt A.name B.empl_status A.ssn B.File_nbr C.termination_dt add 1 to #TotalRecords Do WriteRecordOut FROM PS_Personal_Data A, PS_Job B, PS_Employment C WHERE A.emplid = B.emplid AND C.emplid = B.emplid AND C.empl_rcd_nbr = B.empl_rcd_nbr AND (B.empl_status in ('A','L','P') OR (B.empl_status in ('D','R','T') AND substr(C.termination_dt,7,4) = $currentyear)) AND B.Effdt = (SELECT MAX(Effdt) FROM PS_Job B1 WHERE B1.Emplid = B.Emplid AND B1.Empl_Rcd_Nbr = B.Empl_Rcd_Nbr AND B1.Effdt <= SYSDATE) AND B.EffSeq = (SELECT MAX(EffSeq) FROM PS_JOB B2 WHERE B2.Emplid = B.Emplid AND B2.Empl_Rcd_Nbr = B.Empl_Rcd_Nbr AND B2.Effdt = B.Effdt) ORDER BY A.emplid end-SELECT Thanks again. Scott Gunter ksgunter@tdyryan.com Teledyne Ryan Aeronautical 619/291-7311 x1741 At 09:34 AM 1/8/98 -0600, you wrote: >At 04:31 PM 1/7/98 -0800, you wrote: > >>I need to know how to access the current date in an >>SQR Batch program that is connecting to a SQLBase >>database. > >You can just write a select statement to give >you the date right from SQLBase. I'm not familiar >with SQLBase per se, but there is probably a table >with one row, or a dummy table (like DUAL in Oracle). >You can right a select in a procedure like: > >begin-select >SYSDATE &Date > move &Date to $Date >from DUAL >end-select > >Hope this helps, >Jeremy > > >--- >Jeremy C. Radwan - >http://www.concentric.net/~jradwan/ >"Although it is not exactly 'real,' 'cyberspace' is a genuine place. Things >happen > there that have genuine consequences." - Bruce Sterling, "The Hacker >Crackdown" > > Scott Gunter IS Representative SR Teledyne Ryan Aeronautical 619/291-7311 x1741 From owner-sqr-users@list.iex.net Fri Jan 9 17:37:26 1998 Date: Fri, 9 Jan 1998 09:27:24 -0800 From: Scott Gunter Subject: Re: SQLBase & SQR Help I want to thank everyone for there help. I was finally able to select records from a table based on whether the column "termination_dt" has a year value equal to $currentyear. The key was that you can use SQLBase functions (described in the SQLBase SQL Reference Manual) in the Where clause of a Begin-Select. The following Select statement is a trimmed down version of what I finally used. begin-SELECT A.emplid B.deptid A.orig_hire_dt A.name B.action B.action_reason B.action_dt B.empl_status A.ssn B.File_nbr C.termination_dt FROM PS_Personal_Data A, PS_Job B, PS_Employment C WHERE A.emplid = B.emplid AND C.emplid = B.emplid AND C.empl_rcd_nbr = B.empl_rcd_nbr AND B.paygroup = '&XQ' AND (B.empl_status in ('A','L','P') OR (B.empl_status in ('D','R','T') AND @yearno(C.termination_dt) = $currentyear) OR (B.empl_status = 'S' AND @yearno(B.effdt) = $currentyear)) AND B.Effdt = (SELECT MAX(Effdt) FROM PS_Job B1 WHERE B1.Emplid = B.Emplid AND B1.Empl_Rcd_Nbr= B.Empl_Rcd_Nbr AND B1.Effdt <=SYSDATE) AND B.EffSeq = (SELECT MAX(EffSeq) FROM PS_JOB B2 WHERE B2.Emplid = B.Emplid AND B2.Empl_Rcd_Nbr= B.Empl_Rcd_Nbr AND B2.Effdt = B.Effdt) ORDER BY A.emplid end-SELECT Scott Gunter IS Representative SR Teledyne Ryan Aeronautical 619/291-7311 x1741 From owner-sqr-users@list.iex.net Fri Jan 9 22:45:58 1998 Date: Fri, 9 Jan 1998 12:58:42 -0400 From: David Cholaghian Subject: Re: SQLBase & SQR Help -Reply Scott, Instead of using substr(C.termination_dt,7,4) = $currentyear in your SQL Try TO_CHAR(C.termination_dt,'YYYY') = $currentyear David Cholaghian Bentley College From owner-sqr-users@list.iex.net Mon Jan 12 04:39:24 1998 Date: Mon, 12 Jan 1998 10:27:57 +0100 From: Fredrik Mansfeld Subject: Print with italic font Hi, I have a very short question which I hope someone can give me a quick answer to: How do I do to print text in my reports with italics. I use SQRW version 4.0 and print on a Postscript printer. Regards, Fredrik Mansfeld, Abaris AB, Sweden From owner-sqr-users@list.iex.net Mon Jan 12 10:43:57 1998 Date: Mon, 12 Jan 1998 10:23:32 -0500 From: Paul Siem Subject: Barcode Printing Hi, I am printing out barcodes using the print-barcode command. Although there is a parameter for determining the height of the barcode, there isn't one to change the width of the barcode. For some of the larger barcodes I'm having problems fitting it on one label. Does anyone know of any way to 'squeeze' the barcode graphics ?? Any help is appreciated... Thanx Paul Siem Bay Resources 11880 28th Street North St. Petersburg, FL 33716 psiem@bay.com From owner-sqr-users@list.iex.net Mon Jan 12 11:18:33 1998 Date: Mon, 12 Jan 1998 07:14:00 -0700 From: Donald Raikes Subject: Re: Barcode Printing Paul, I worked with the print-barcode last year and discovered a couple of problems with it as well. I was able to modify the width, but I had to do it the hard way. I actually went into the postscript .lis file and modified the code for printing the barcode. Just as a warning, I also kept getting extra characters printed in the barcode so beware. From owner-sqr-users@list.iex.net Mon Jan 12 15:02:52 1998 Date: Mon, 12 Jan 1998 09:57:13 -0600 From: Randle Boyd Subject: Re: Barcode Printing Paul, we ran into a similar problem. The best solution we could find was to switch to 'TYPE = 8'. While 'Type= 12 ' produced a shorter barcode, our barcode reader couldn't read it. If there is a way to further compress a barcode, I would be very interested. Randle >---------- >From: Paul Siem[SMTP:psiem@BAY.COM] >Sent: Monday, January 12, 1998 9:23 AM >To: Multiple recipients of list SQR-USERS >Subject: Barcode Printing > >Hi, > >I am printing out barcodes using the print-barcode command. Although there >is a parameter for determining the height of the barcode, there isn't one >to change the width of the barcode. >For some of the larger barcodes I'm having problems fitting it on one >label. > >Does anyone know of any way to 'squeeze' the barcode graphics ?? > >Any help is appreciated... > >Thanx > > >Paul Siem >Bay Resources >11880 28th Street North >St. Petersburg, FL 33716 >psiem@bay.com > From owner-sqr-users@list.iex.net Mon Jan 12 19:42:59 1998 Date: Mon, 12 Jan 1998 16:18:11 -0800 From: Tai Luong Subject: Re: Available: SQR Tree Utility (Attempt 2) Hi Wayne, I've received your mail the second time. I can't view your the attachment sqrtree. Pine gives me the error message: [Don't know how to display attachment format Application/OCTET-STREAM] What should I do? Thank you very much for trying! Tai Luong. On Wed, 7 Jan 1998, Wayne Ivory wrote: > With compliments. And could you please do me a favour - I have sent it to = > several people and asked them to let me know what they think. But apart = > from one guy who said there was no attachment and asked me to resend, I = > haven=27t heard anything. > > Could you please advise if you got it and if it works (and if you like = > it). > > Wayne Ivory > Information Services > Westralian Sands Limited > > > >>> Ken Gartman 7/1/98 7:32:57 am >>> > Hi there, Wayne=21 > I=27d also like a copy of your Software. > > Happy Computing, > Ken Gartman > Maryland, USA > > > Wayne Ivory wrote: > > > > Since I posted my original message I=27ve had a few replies and noticed = > the example I had pasted into the message got all scrunched together (I = > hate that - so much for WYSIWYG). I=27ll try pasting it here using a = > different method - unfortunately I won=27t know if it works until after = > I=27ve done it. In case it doesn=27t, imagine a Windows-Explorer type = > display. > > > > TAX016AU.SQR > > > > 35 Init-DateTime () > > 36 Init-Number () > > 37 Init-Report (45) > > 51 Stdapi-Init () > > 54 Prompt (1389) > > 56 Select-Parameters () > > 57 Get-Values (1444) > > 60 Process-Parameters (1413) > > 1431 Format-DateTime () > > 1432 Format-DateTime () > > 61 Array-Init (100) > > 38 Process-Main (135) > > 145 Get-Group-Name (1338) > > 146 Build-company-list (1260) > > 1277 Get-Company-Data () > > 147 Get-Totals (157) > > 169 Cmpny-State-Data (177) > > 178 Pay-Earnings (192) > > 223 Get-PayGroup-Erncd (726) > > 739 Chk-Exempt-Erncd (771) > > > > ******** Original Message follows **************** > > If anyone is interested I=27ve written a Unix shell script that produces = > a Procedure-Call tree for an SQR program. It shows which procedures call = > which procedures, the line they are called from, and the line the called = > procedure is on. Here=27s a sample: > > > > TAX016AU.SQR > > > > 35 Init-DateTime () > > 36 Init-Number () > > 37 Init-Report (45) > > 51 Stdapi-Init () > > 54 Prompt (1389) > > 56 Select-Parameters () > > 57 Get-Values (1444) > > 60 Process-Parameters (1413) > > 1431 Format-DateTime () > > 1432 Format-DateTime () > > 61 Array-Init (100) > > 38 Process-Main (135) > > 145 Get-Group-Name (1338) > > 146 Build-company-list (1260) > > 1277 Get-Company-Data () > > 147 Get-Totals (157) > > 169 Cmpny-State-Data (177) > > 178 Pay-Earnings (192) > > 223 Get-PayGroup-Erncd (726) > > 739 Chk-Exempt-Erncd (771) > > > > If you=27d like a copy send me an email. > > > > Wayne Ivory > > Information Services > > Westralian Sands Limited > > > > Wayne Ivory > > Information Services > > Westralian Sands Limited > > > > > > =21 > > =21 > > > > =21 > > =21 > > From owner-sqr-users@list.iex.net Tue Jan 13 01:08:22 1998 Date: Tue, 13 Jan 1998 08:55:58 +0800 From: Wayne Ivory Subject: Re: Available: SQR Tree Utility (Attempt 2) This is a Unix Shell script. It=27s not designed to be used in Windows = and hence has no extension, which is probably confusing your mail reader. = It is in fact however just a text file. You should be able to just save = the attachment to a file (if it hasn=27t done it already), port it to = Unix, give it execute permission, and run it. Good Luck. Wayne Ivory Information Services Westralian Sands Limited >>> Tai Luong 13/1/98 8:18:11 am >>> Hi Wayne, I=27ve received your mail the second time. I can=27t view your the = attachment sqrtree. Pine gives me the error message: =5BDon=27t know how to display attachment format Application/OCTET-STREAM= =5D What should I do? Thank you very much for trying=21 Tai Luong. On Wed, 7 Jan 1998, Wayne Ivory wrote: > With compliments. And could you please do me a favour - I have sent it = to =3D > several people and asked them to let me know what they think. But apart = =3D > from one guy who said there was no attachment and asked me to resend, I = =3D > haven=3D27t heard anything. > > Could you please advise if you got it and if it works (and if you like = =3D > it). > > Wayne Ivory > Information Services > Westralian Sands Limited > > > >>> Ken Gartman 7/1/98 7:32:57 am >>> > Hi there, Wayne=3D21 > I=3D27d also like a copy of your Software. > > Happy Computing, > Ken Gartman > Maryland, USA > > > Wayne Ivory wrote: > > > > Since I posted my original message I=3D27ve had a few replies and = noticed =3D > the example I had pasted into the message got all scrunched together (I = =3D > hate that - so much for WYSIWYG). I=3D27ll try pasting it here using a = =3D > different method - unfortunately I won=3D27t know if it works until = after =3D > I=3D27ve done it. In case it doesn=3D27t, imagine a Windows-Explorer = type =3D > display. > > > > TAX016AU.SQR > > > > 35 Init-DateTime () > > 36 Init-Number () > > 37 Init-Report (45) > > 51 Stdapi-Init () > > 54 Prompt (1389) > > 56 Select-Parameters () > > 57 Get-Values (1444) > > 60 Process-Parameters (1413) > > 1431 Format-DateTime () > > 1432 Format-DateTime () > > 61 Array-Init (100) > > 38 Process-Main (135) > > 145 Get-Group-Name (1338) > > 146 Build-company-list (1260) > > 1277 Get-Company-Data () > > 147 Get-Totals (157) > > 169 Cmpny-State-Data (177) > > 178 Pay-Earnings (192) > > 223 Get-PayGroup-Erncd (726) > > 739 Chk-Exempt-Erncd (771) > > > > ******** Original Message follows **************** > > If anyone is interested I=3D27ve written a Unix shell script that = produces =3D > a Procedure-Call tree for an SQR program. It shows which procedures = call =3D > which procedures, the line they are called from, and the line the called = =3D > procedure is on. Here=3D27s a sample: > > > > TAX016AU.SQR > > > > 35 Init-DateTime () > > 36 Init-Number () > > 37 Init-Report (45) > > 51 Stdapi-Init () > > 54 Prompt (1389) > > 56 Select-Parameters () > > 57 Get-Values (1444) > > 60 Process-Parameters (1413) > > 1431 Format-DateTime () > > 1432 Format-DateTime () > > 61 Array-Init (100) > > 38 Process-Main (135) > > 145 Get-Group-Name (1338) > > 146 Build-company-list (1260) > > 1277 Get-Company-Data () > > 147 Get-Totals (157) > > 169 Cmpny-State-Data (177) > > 178 Pay-Earnings (192) > > 223 Get-PayGroup-Erncd (726) > > 739 Chk-Exempt-Erncd (771) > > > > If you=3D27d like a copy send me an email. > > > > Wayne Ivory > > Information Services > > Westralian Sands Limited > > > > Wayne Ivory > > Information Services > > Westralian Sands Limited > > > > > > =3D21 > > =3D21 > > > > =3D21 > > =3D21 > > From owner-sqr-users@list.iex.net Tue Jan 13 19:42:08 1998 Date: Wed, 14 Jan 1998 10:33:12 +1000 From: "MORRIS, Dean" Subject: Viewing Report files containing printer codes. Dear All, I'm currently having a problem viewing some reports. These reports contain printer control characters. Obviously, when they are viewed from a text editor, they appear as a meaningless stream of escaped characters. What I'm after is a filter of some kind to convert these printer codes into plan ASCII viewable text. Ideally, I'd like a filter to install into Microsoft Word. Such a filter would also allow you to view the report, along with the formatting. Does anyone know if such a filter exists? Any help would be greatly appreciated. Regards, Dean. PS: Thank you to everyone who supplied some help on my Julian date problem. Dean Morris Price Waterhouse Urwick Suncorp~Metway Financial Integration Project (Project SAMBUCA) Phone (07) 3362 2413 0417 742 335 From owner-sqr-users@list.iex.net Wed Jan 14 13:47:31 1998 Date: Wed, 14 Jan 1998 08:30:41 -0500 From: Ray Ontko Subject: Re: Viewing Report files containing printer codes. Dean, There are other ways to view reports which are more than text-only: 1) Use -keep to preserve the .SPF file which can then be viewed using the SPF viewer SQRWV. This requires that the SQR viewer be installed on all machines that might be used to view the reports. 2) Use -printer:ps to generate a PostScript file which is then run through Adobe Acrobat Distiller. This produces a .PDF file, which can then be viewed with the Acrobat Reader. This requires that you purchase a license for Acrobat (less than $295) and that you install the Acrobat reader on all machines that might be used to view the reports, but this is not as bad as it sounds. Adobe has been distributing its reader free of charge for many years, and web users in your organization have likely downloaded it and are familiar with it. Hope this helps. Ray > Dear All, > > I'm currently having a problem viewing some reports. These reports > contain printer control characters. Obviously, when they are viewed > from a text editor, they appear as a meaningless stream of escaped > characters. > > What I'm after is a filter of some kind to convert these printer codes > into plan ASCII viewable text. Ideally, I'd like a filter to install > into Microsoft Word. Such a filter would also allow you to view the > report, along with the formatting. Does anyone know if such a filter > exists? > > Any help would be greatly appreciated. > > Regards, > > Dean. > > PS: Thank you to everyone who supplied some help on my Julian date > problem. > > Dean Morris > Price Waterhouse Urwick > Suncorp~Metway Financial Integration Project (Project SAMBUCA) > Phone (07) 3362 2413 0417 742 335 > ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | "Time for a new signature line." rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ From owner-sqr-users@list.iex.net Wed Jan 14 13:57:10 1998 Date: Wed, 14 Jan 1998 09:18:52 -0500 From: Ray Ontko Subject: Re: Print with italic font Fredrik, > I have a very short question which I hope someone can give me a quick > answer to: > How do I do to print text in my reports with italics. > I use SQRW version 4.0 and print on a Postscript printer. Unfortunately, SQR doesn't support italics directly. If you can get away with it, consider using UNDERLINE instead. If you _must_ have italics, and you're willing to embed some postscript in the report, then you'll need to do something like this: print 'Here is some normal text' (1,1) print-direct printer=ps 'gsave' print-direct printer=ps '/Courier-Oblique findfont 12 scalefont setfont' print 'Here is some italic text' (2,1) print-direct printer=ps 'grestore' print 'Here is some normal text' (3,1) Note that this will show as plain text in the SQR Viewer, but will show correctly on the printer. Hope this helps. Ray ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | "Time for a new signature line." rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ From owner-sqr-users@list.iex.net Wed Jan 14 17:06:14 1998 Date: Wed, 14 Jan 1998 10:59:45 -0500 From: Ray Ontko Subject: SQRIBE Promotion Howdy SQR-USERS, I just received a copy of the SQRIBE Forum, their quarterly newsletter. On the front page is a box that talks about joining SQRUG, (which may not be the same as www.sqrug.com) by registering at www.sqribe.com. I went to http://www.sqribe.com and clicked on "Promotion" and added my name to their list, which I encourage you to do as well. This apparently adds you to the mailing list for a SQRIBE-sponsored SQRIBE International User Group conference to be held Nov 4-6 in Monterrey, CA, and registers you to win a free trip to any SQRIBE office, worldwide (and a life size chocolate bunny ;-). Mark your calendars and "join" SQRIBE's list by registering at the site. Ray P.S.: A note to the people at SQRIBE and www.sqrug.com: Please clarify the relationship between SQRIBE Internation User Group and SQR Users Group. ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | "Ask me about SQR and Linux" rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ From owner-sqr-users@list.iex.net Wed Jan 14 23:47:41 1998 Date: Wed, 14 Jan 1998 18:48:52 -0500 From: Ray Ontko Subject: Re: Wrapping Proportional Fonts Julian, > Does anyone have a solution to wrapping of proportional fonts ( mixed > case and all uppercase )??? This requires a little work. The basic problem is that SQR doesn't know the widths of each character of a proportional font. There are solutions available for SQR, but they require you to obtain or produce your own font metrics. Take a look at: http://www.ontko.com/sqr/hp.html which is a collection of routines that I wrote for handling proportional fonts for hp printers, mostly for truncation. I did not attempt to address the wrap problem, however. For that, you'll need to look at: http://www.sqrug.com/~ftp/wrap/ Unfortunately, it doesn't address the problem of how to come up with the font metric information. Hope this helps. Ray ---------------------------------------------------------------------- Ray Ontko | Ray Ontko & Co | "Ask me about SQR for Linux" rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/ From owner-sqr-users@list.iex.net Fri Jan 16 03:10:03 1998 Date: Thu, 15 Jan 1998 16:10:16 -0800 From: Tai Luong Subject: Add table to select paragraph Hello Gurus! Is there a way to add a 'dynamic' table name in a SELECT paragraph? For example, in the following code, I would like to include a table called 'tab_x' in the selection, only when it is called for ($include_tab_x = 'Y'). The code below doesn't work: Begin-Program Do Init Do Test End-Program Begin Procedure Init move '' to $extra_tab move '' to $and_clause if upper($include_tab_x) = 'Y' end-if End-Procedure From owner-sqr-users@list.iex.net Fri Jan 16 14:11:23 1998 Date: Fri, 16 Jan 1998 13:56:18 -0500 From: YMCKINNE Subject: dat output files I am a new user of sqr and I am trying to create a flat file. I am opening and writing to a file within my sqr code. When I run my code, my output is sent to my temp file in my c directory. When I run my code, I am not finding my .dat file in the temp file on my c directory or anywhere on my c drive. I was wondering if someone could give me some advice on what I might try to fix the problem. My code is as follows: begin-procedure init do create_file end-procedure !init !************************************************************** !Create File !************************************************************** begin-procedure create_file open '{filename.dat}'as 1 for-writing stat=#status record=2050 end-procedure !create_file Thanks, Yolanda From owner-sqr-users@list.iex.net Fri Jan 16 16:08:56 1998 Date: Fri, 16 Jan 1998 13:07:54 -0600 From: "Cooper, Marcus" Subject: Unloading a table in SQLBase Hello everyone, I am trying to unload a table to a filename that is entered into a variable using SQLBase and SQR3. When I try this is doesn't like the variable as part of my unload statement. Here is what I am doing: Begin-procedure Begin-SQL Unload data SQL $FileName ps_health_benefit End-SQL End-procedure (where $FileName was entered through an input statement.) It says it can't bind to my variable. I hope someone can help Thanks Marcus Cooper Grant Thornton LLP Mcooper@apl.gt-wis.com From owner-sqr-users@list.iex.net Fri Jan 16 18:02:33 1998 Date: Fri, 16 Jan 1998 11:26:44 -0500 From: CSC Consulting Subject: Re: Add table to select paragraph You can try the following code to dynamically specify the table name in the select paragraph of the procedure test Begin-Program Do Init Do Test End-Program Begin Procedure Init move '' to $extra_tab move '' to $and_clause if upper($include_tab_x) = 'Y' move 'tab_x' to $table_name end-if End-Procedure begin-procedure test begin-select select a, b, c from [$table_name] end-select end-procedure ______________________________ Reply Separator _________________________________ Subject: Add table to select paragraph Author: SQR-USERS@USA.NET at INTERNET Date: 1/15/98 4:10 PM Hello Gurus! Is there a way to add a 'dynamic' table name in a SELECT paragraph? For example, in the following code, I would like to include a table called 'tab_x' in the selection, only when it is called for ($include_tab_x = 'Y'). The code below doesn't work: Begin-Program Do Init Do Test End-Program Begin Procedure Init move '' to $extra_tab move '' to $and_clause if upper($include_tab_x) = 'Y' end-if End-Procedure From owner-sqr-users@list.iex.net Fri Jan 16 18:23:48 1998 Date: Fri, 16 Jan 1998 09:00:17 -0800 From: Scott Gunter Subject: Re: Add table to select paragraph Have you considered putting the select in a paragraph and copying it so there are two paragraphs: one including your table and one not. then you could call the paragraphs depending on the value of $include_tab_x. From owner-sqr-users@list.iex.net Fri Jan 16 21:41:00 1998 Date: Fri, 16 Jan 1998 12:37:48 -0800 From: Scott Gunter Subject: Re: Unloading a table in SQLBase Hello Marcus, I'm not real experienced with SQR3, but I think that if you could issue an unload statement, and you wanted to use a bind variable in the parameter list you would need to place the variable name in braces {FileName}. Sorry, I don't have time to test this out myself, but maybe you could try it. Good Luck, Scott Gunter IS Representative SR Teledyne Ryan Aeronautical 619/291-7311 x1741 From owner-sqr-users@list.iex.net Fri Jan 16 22:40:57 1998 Date: Fri, 16 Jan 1998 14:06:01 -0500 From: Wendy Stern Subject: Re: dat output files 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_01BD2287.E2525DA0 Content-Type: text/plain This program reads a flat file and creates another one. Hope this helps. Wendy > ---------- > From: YMCKINNE[SMTP:YMCKINNE@LANIER.COM] > Sent: Friday, January 16, 1998 1:56 PM > To: Multiple recipients of list SQR-USERS > Subject: dat output files > > I am a new user of sqr and I am trying to create a flat file. I > am > opening and writing to a file within my sqr code. When I run my > code, > my output is sent to my temp file in my c directory. > > When I run my code, I am not finding my .dat file in the temp > file on > my c directory or anywhere on my c drive. I was wondering if > someone > could give me some advice on what I might try to fix the problem. > > > My code is as follows: > > begin-procedure init > do create_file > end-procedure !init > > !************************************************************** > !Create File > !************************************************************** > > begin-procedure create_file > open '{filename.dat}'as 1 for-writing stat=#status > record=2050 > end-procedure !create_file > > > Thanks, > > Yolanda > ------ =_NextPart_000_01BD2287.E2525DA0 Content-Type: application/octet-stream; name="conversn.sqr" Content-Disposition: attachment; filename="conversn.sqr" #include 'setenv.sqc' !Set environment #Include 'setup10.sqc' !Printer and page-size initialization begin-report do Init-DateTime do Init-Number do Stdapi-Init move 'CONVERT' to $reportid move 'Elkton Conversion file' to $reporttitle display $ReportTitle do Get-Current-DateTime display 'ReportDate: ' display $reportdate do Report close 1 close 10 display ' ' date-time () hh:mi:ss &timeended display 'count ' noline display #count display 'Report Ended: ' noline display &timeended commit do Stdapi-Term end-report begin-procedure REPORT open 'c:\UNZIPPED\OUTPUT\OUTPUT.DAT' as 1 for-reading record=1786:fixed status = #open1 if #open1 = -1 display 'Open for c:\UNZIPPED\OUTPUT\OUTPUT.DAT FAILED' stop end-if open 'Y:\PUBLIC\ELKMMS.DAT' as 10 for-writing record=47:fixed status = #open10 if #open10 = -1 display 'Open for Y:\PUBLIC\ELKMMS.DAT FAILED' stop end-if while 1 = 1 read 1 into $record:1786 if #end-file = 1 break !exit the "while" end-if do Process-Data end-while end-procedure begin-procedure Process-DATA add 1 to #count extract $account from $record 0 6 extract $make from $record 1305 4 extract $meter from $record 1310 9 extract $size from $record 1328 5 extract $start from $record 1335 6 extract $setindex from $record 1343 9 extract $removal from $record 1354 6 extract $dials from $record 1374 1 extract $temperature from $record 1587 1 write 10 from - $account:6 - $make:4 - $meter:9 - $size:5 - $start:6 - $setindex:9 - $removal:6 - $dials:1 - $temperature:1 end-procedure #Include 'getdatcd.sqc' !Get-Date-Codes procedure #Include 'datetime.sqc' !Routines for date and time formatting #Include 'curdttim.sqc' !Get-Current-DateTime procedure #Include 'number.sqc' !Routines to format numbers #include 'stdapi.sqc' !Routine to update Run status ------ =_NextPart_000_01BD2287.E2525DA0-- From owner-sqr-users@list.iex.net Sat Jan 17 00:18:17 1998 Date: Fri, 16 Jan 1998 12:34:03 -0800 From: Scott Gunter Subject: Re: dat output files Yolanda, I've included some code that might help you get started. begin-procedure InitRoutine . . . !Assign filename and path to variable !and call procedure to handle opening your file(s) let $output_file = 'c:\temp\filename.dat' Do Open-files . . . end-procedure begin-procedure Open-files OPEN $output_file AS 1 FOR-WRITING RECORD=050:FIXED STATUS = #Open1 IF #Open1 = -1 DISPLAY 'Error opening ' || $output_file DISPLAY 'Status is ' NOLINE DISPLAY #Open1 STOP QUIET END-if end-procedure ! Once you open the file you must select your data ! and move it to the output fields (defined in your ! write statement.) move &a.emplid to $0field1 move &a.name to $0field2 move &a.misc to $0field3 ! Call the write procedure for each record ! you want to write. Do WriteRecordOut !Write procedure Begin-Procedure WriteRecordOut WRITE 1 FROM $Ofield1:6 $Ofield2:20 $Ofield3:20 $Ofiller:4 End-Procedure I don't use an explicit close statement. I assume SQRW handles that at program termination. But you can include one if you like !close output file close 1 Hope this helps you get going. Scott Gunter IS Representative SR Teledyne Ryan Aeronautical 619/291-7311 x1741 From owner-sqr-users@list.iex.net Sat Jan 17 01:09:27 1998 Date: Fri, 16 Jan 1998 15:17:45 -0600 From: Wes Williams Subject: dat output files -Reply Hi Yolanda I write flatfiles by selecting the columns is wish to be included in the file and then format them into fixed lengths through the print command. This will create a .LIS file you can specify the filename if you so choose. Example: !fORMAT FOR FLAT FILE print $lastname (0,0,015) ! NAME print $div (0,0,002) ! DIVISION print &dept (0,0,003) ! DEPT print &pltsite (0,0,002) ! PLT SITE print $location (0,0,010) ! LOCATION print $Phone (0,0,013) ! PHONE print $dcode (0,0,001) ! D CODE print $device (0,0,005) ! DEVICE DESCRIPTION print &changedte (0,0,008) edit mm/dd/yy print $hnum (0,0,008) ! H NUMBER print &port (0,0,004) ! PORT print &harm (0,0,005) ! HARMONICA print &idf (0,0,008) ! IDF print &node (0,0,008) ! NODE NEW-PAGE FROM COMPANY WHERE COMPANY.COMPANY = &ENAME END-SELECT END-PROCEDURE begin-setup page-size 1 0297 no-formfeed end-setup Hope this helps Wes Wes Williams Sundstrand Aerospace 815.226.6196 wwilliams@snds.com >>> YMCKINNE 01/16/98 12:56pm >>> I am a new user of sqr and I am trying to create a flat file. I am opening and writing to a file within my sqr code. When I run my code, my output is sent to my temp file in my c directory. When I run my code, I am not finding my .dat file in the temp file on my c directory or anywhere on my c drive. I was wondering if someone could give me some advice on what I might try to fix the problem. My code is as follows: begin-procedure init do create_file end-procedure !init !************************************************************** !Create File !************************************************************** begin-procedure create_file open '{filename.dat}'as 1 for-writing stat=#status record=2050 end-procedure !create_file Thanks, Yolanda From owner-sqr-users@list.iex.net Sat Jan 17 01:18:25 1998 Date: Fri, 16 Jan 1998 17:12:38 -0500 From: Al Patel Subject: Re: Unloading a table in SQLBase Depending on what SQL too you're using, you should be able to capture all the output to a file. You may want to investigate those utility preferences. It may be as simple as checking a box that says capture to file, and you can specify the filename and directory. Al From owner-sqr-users@list.iex.net Sat Jan 17 02:32:10 1998 Date: Fri, 16 Jan 1998 13:50:26 -0700 From: Robert Goshko Subject: Re: Unloading a table in SQLBase ------ =_NextPart_000_01BD2285.B5D7DEA0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Marcus, When you are using variable in SQL that you want to be used as part of = the SQL command statement, you have to use the square brackets (e.g. = [$FileName]), change your SQL to: Begin-procedure Begin-SQL Unload data SQL [$FileName] ps_health_benefit End-SQL End-procedure Later... Rob -----Original Message----- From: Cooper, Marcus [SMTP:MCooper@APL.GT-WIS.COM] Sent: Friday, January 16, 1998 12:08 PM To: Multiple recipients of list SQR-USERS Subject: Unloading a table in SQLBase Hello everyone, I am trying to unload a table to a filename that is entered into a variable using SQLBase and SQR3. When I try this is doesn't like the variable as part of my unload statement. Here is what I am doing: Begin-procedure Begin-SQL Unload data SQL $FileName ps_health_benefit End-SQL End-procedure (where $FileName was entered through an input statement.) It says it can't bind to my variable. I hope someone can help Thanks Marcus Cooper Grant Thornton LLP Mcooper@apl.gt-wis.com ------ =_NextPart_000_01BD2285.B5D7DEA0 Content-Type: application/ms-tnef Content-Transfer-Encoding: base64 eJ8+Ih8UAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEEkAYAkAEAAAEAAAAQAAAAAwAAMAIAAAAL AA8OAAAAAAIB/w8BAAAAQQAAAAAAAACBKx+kvqMQGZ1uAN0BD1QCAAAAAFNRUi1VU0VSU0BVU0Eu TkVUAFNNVFAAU1FSLVVTRVJTQFVTQS5ORVQAAAAAHgACMAEAAAAFAAAAU01UUAAAAAAeAAMwAQAA ABIAAABTUVItVVNFUlNAVVNBLk5FVAAAAAMAFQwBAAAAAwD+DwYAAAAeAAEwAQAAABQAAAAnU1FS LVVTRVJTQFVTQS5ORVQnAAIBCzABAAAAFwAAAFNNVFA6U1FSLVVTRVJTQFVTQS5ORVQAAAMAADkA AAAACwBAOgEAAAAeAPZfAQAAABIAAABTUVItVVNFUlNAVVNBLk5FVAAAAAIB918BAAAAQQAAAAAA AACBKx+kvqMQGZ1uAN0BD1QCAAAAAFNRUi1VU0VSU0BVU0EuTkVUAFNNVFAAU1FSLVVTRVJTQFVT QS5ORVQAAAAAAwD9XwEAAAADAP9fAAAAAAIB9g8BAAAABAAAAAAAAALhQwEEgAEAIQAAAFJFOiBV bmxvYWRpbmcgYSB0YWJsZSBpbiBTUUxCYXNlAL0KAQWAAwAOAAAAzgcBABAADQAyABoABQBEAQEg gAMADgAAAM4HAQAQAA0ALgA5AAUAXwEBCYABACEAAABDQTZBQjU1Rjg4OEVEMTExOUVFQzAwNjA5 NzM3QTVFMABCBwEDkAYAyAYAACEAAAALAAIAAQAAAAsAIwAAAAAAAwAmAAAAAAALACkAAAAAAAMA LgAAAAAAAwA2AAAAAABAADkAINQPX8AivQEeAHAAAQAAACEAAABSRTogVW5sb2FkaW5nIGEgdGFi bGUgaW4gU1FMQmFzZQAAAAACAXEAAQAAABYAAAABvSLAXw5ftWrNjogR0Z7sAGCXN6XgAAAeAB4M AQAAAAUAAABTTVRQAAAAAB4AHwwBAAAAEAAAAHJnb3Noa29AaWJtLm5ldAADAAYQ1YIHSgMABxAl AwAAHgAIEAEAAABlAAAATUFSQ1VTLFdIRU5ZT1VBUkVVU0lOR1ZBUklBQkxFSU5TUUxUSEFUWU9V V0FOVFRPQkVVU0VEQVNQQVJUT0ZUSEVTUUxDT01NQU5EU1RBVEVNRU5ULFlPVUhBVkVUT1VTRVRI RQAAAAACAQkQAQAAAKUDAAChAwAAAgYAAExaRnXnD9jddwAKAQMB9yACpAPjAgBjgmgKwHNldDAg BxOHAoMAUA72cHJxMg/2Jn0KgAjIIDsJbzI1ZjUCgAqBdWMAUAsDYwMAQQtgbmcxMDMzhwumBdAK wGN1cywKopMKhAqAV2gJ8CB5CGAyIArAZSAWoAuAZyB+dgrABzACYBhAC4AGAFG4TCB0D4AFQBfi dwBw4QVAdG8gYhhCCYAYEOcEIAqxBUBvZhmxGEAZgi0FoG0DgRswcwGQdGXbB4ACMCwX0w+AdhhA GqEjGwEb83NxdRgiYnINANBrD8AEIChlLmcgLiBbJEYDEGVO8mEHgF0pHZAPcRVwGEBnF+EFwBmD bzoW2gr0bDhpMzYBQBUQD+BCZcpnC4AtEUBvYwmACHCeZRbUJCQZgRbUVW4JAOphGzBkGeBhGXMg WRtwXHNfF6AHQBnAXxrQbukBEGl0FtRFHNAlyCmSlySNFLgW40wdIXIuLND9FtQgLWMIAAwwIn8j hwqgGwNgHTBjKSAQkzE2IPotMLJPBRAkQQdABdAHkPxzYSFwMLMW1i/EL5ELE8EvxmktMTQ0AUAj gDgxODABQAzQNFNiIG5GA2EiUAySYg/gCFBvHnAEkB2QFmQgQFNNVAhQOk02dEBBUEwALkdULVdJ Uy7wQ09NXRbVNYAGYAIwqzXnNaBpJtB5HZBKAHAZHxF5IDCAHZAxOTkiODuAMjowPABQTc05N1Qi QTYFTXUogAUg+xkhCXBjBSAIkAIwBCAb0QcjgB0AGXFSLVVTRcRSUzk4dWJqL/E1598mZBiCJwAB kRknQhtQKycfMt8v8iOEC7YW40hlbLkJACBlHhA7YAIgZRbL+kkYEG0ZsDtgGIIeQiZ0/0JmGqEn ACkAGSAxUAeAGbT/BABG0AIwBJAbIQuAShIW1L8YxxhkQxUYEBzRP6EzIDB+IBeTSDBIgRmxS0FL QWTybweQbicFQCOAH6Ab8vtMXRtZbTtwSSUdB06hRoD7GDFLQXcZ0kgzUAAYgSJb9yQvJT8mTyAg ZygfKS8qP/0W6ShUgFQSWYgaUEtYGcAxA2B1Z2hOERlBcHX7BUBTSClHe2CxOtBPsQVA9mMAcFBR YguAX6EasFKhdxjGTqFIMGg2kRzwA3Blr0cxYuId4EaQcBbaVCFBvmsPQBbpNvU2dBbURx9wHxpx ZuAFsEwBA6BMTFA3Z6UFoDfzYQtQIBB0LV0D8S4cgRbaEgEAbRAAAAADABAQAAAAAAMAERAAAAAA AwCAEP////9AAAcw0GXm4r8ivQFAAAgw0GXm4r8ivQELAACACCAGAAAAAADAAAAAAAAARgAAAAAD hQAAAAAAAAMAAoAIIAYAAAAAAMAAAAAAAABGAAAAABCFAAAAAAAAAwAFgAggBgAAAAAAwAAAAAAA AEYAAAAAUoUAALcNAAADAAmACCAGAAAAAADAAAAAAAAARgAAAAABhQAAAAAAAB4AEoAIIAYAAAAA AMAAAAAAAABGAAAAAFSFAAABAAAABAAAADguMAALABaACCAGAAAAAADAAAAAAAAARgAAAAAOhQAA AAAAAAMAF4AIIAYAAAAAAMAAAAAAAABGAAAAABGFAAAAAAAAAwAZgAggBgAAAAAAwAAAAAAAAEYA AAAAGIUAAAAAAAAeACiACCAGAAAAAADAAAAAAAAARgAAAAA2hQAAAQAAAAEAAAAAAAAAHgApgAgg BgAAAAAAwAAAAAAAAEYAAAAAN4UAAAEAAAABAAAAAAAAAB4AKoAIIAYAAAAAAMAAAAAAAABGAAAA ADiFAAABAAAAAQAAAAAAAAAeAD0AAQAAAAUAAABSRTogAAAAAAMADTT9NwAAkZc= ------ =_NextPart_000_01BD2285.B5D7DEA0-- From owner-sqr-users@list.iex.net Sat Jan 17 07:42:11 1998 Date: Sat, 17 Jan 1998 08:29:29 -0400 From: Aya Fall Subject: Getting hours worked from previous pay periods. Hi there: I want to create a procedure that would determine the hours worked by an employee x pay periods prior to enrollment in SDB. The enrollment date is known. What I need to determine is the hours worked let's say 15 pay periods prior to enrollment in Sick and Disability Benefits. Hope someone can help. Thanks. Aya. From owner-sqr-users@list.iex.net Sat Jan 17 08:28:44 1998 Date: Sat, 17 Jan 1998 08:11:24 -0500 From: Sam Spritzer Subject: Re: Getting hours worked from previous pay periods. Look for a table which contains all of the hours information and then you can do a SELECT on that table with a WHERE clause of all hours that are least than (or equal to the enrollment date. If you just want 15 payperiods or the 15th payperiod, you can use COUNT to get exactly what you want. Hope this helps. Sam At 08:29 AM 1/17/98 -0400, you wrote: >Hi there: > >I want to create a procedure that would determine the hours worked by an >employee x pay periods prior to enrollment in SDB. The enrollment date >is known. What I need to determine is the hours worked let's say 15 pay >periods prior to enrollment in Sick and Disability Benefits. Hope >someone can help. > >Thanks. > >Aya. > spritzer@localnet.com "This space perpetually under construction." From owner-sqr-users@list.iex.net Sat Jan 17 23:14:11 1998 Date: Sat, 17 Jan 1998 22:44:11 EST From: BELLARY Subject: Re: dat output files I think your syntax is wrong. Remove curley brackets in " {filename.dat} " if you have used it really like this in your program and not #defined filename.dat or used 'ask filename.dat' . Also check for value of #status after open statement to verify if there was any error in opening the file. I will get back to you after I run this code practically at my workplace. Goodluck. -arun In a message dated 98-01-16 14:11:36 EST, you write: << I am a new user of sqr and I am trying to create a flat file. I am opening and writing to a file within my sqr code. When I run my code, my output is sent to my temp file in my c directory. When I run my code, I am not finding my .dat file in the temp file on my c directory or anywhere on my c drive. I was wondering if someone could give me some advice on what I might try to fix the problem. My code is as follows: begin-procedure init do create_file end-procedure !init !************************************************************** !Create File !************************************************************** begin-procedure create_file open '{filename.dat}'as 1 for-writing stat=#status record=2050 end-procedure !create_file >> From owner-sqr-users@list.iex.net Sat Jan 17 23:46:23 1998 Date: Fri, 16 Jan 1998 11:04:49 +0100 From: Jean-Luc Peyret Subject: Re: dat output files --0__=svLYTpLraIPTGu7ze2O8oKvxdSSetLlYPU51sDIaiy8FwVaStcxIaXjJ Content-type: text/plain; charset=us-ascii Hi. See responses in your code + here's an example which works for me. !directories for file writing #define outpath C:\user\sqr\ #define tempdir C:\temp\ #define NATIVE_DATE_LEN 10 #define MAX_PROG_LINEWIDTH 255 somewhere else, I open: !opening of RL SQL Audit file let #file_audit_rl = 2 let $file_audit_rl = '{outpath}AUUCFRRL.SQL' print 'open 2' (+1,1) open $file_audit_rl as #file_audit_rl for-writing record={MAX_PROG_LINEWIDTH} JLuc (Embedded image moved YMCKINNE to file: 01/16/98 07:56 PM PIC13307.PCX) Please respond to SQR-USERS@USA.NET To: Multiple recipients of list SQR-USERS cc: (bcc: Jean-Luc Peyret) Subject: dat output files I am a new user of sqr and I am trying to create a flat file. I am opening and writing to a file within my sqr code. When I run my code,