From owner-sqr-users@list.iex.net Sun Jul 1 00:20:37 2001 Date: Sun, 1 Jul 2001 00:00:02 -0500 From: Nathan Stratton Treadway Subject: Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Stratton Treadway, Ray Ontko & Co. (nathant@ontko.com) $Revision: 1.9 $ $Date: 2001-06-17 13:23:18-04 $ This document gathers in one location information about the sqr-users mail list. Directions for common list-server commands (including unsubscribing) are given, as are guidelines for list usage. ______________________________________________________________________ Table of Contents 1. Changes Since Previous Version 2. Introduction 2.1 sqr-users Mailing List 2.2 sqr-users Hints and Guidelines Document 2.3 Related Mailing Lists 3. Basic List Server Commands 3.1 Subscribing to the List 3.2 Unsubscribing from the List 3.3 Digests 3.4 Problems 4. Posting to the sqr-users List 4.1 General Guidelines 4.2 Posting a Message 4.3 Replying to a Message 5. Job Announcements ______________________________________________________________________ 1. Changes Since Previous Version Changes since the version dated 2001/01/26: o Announce new sqr-jobs mailing list; Job Announcements section updated to reflect this new list. 2. Introduction 2.1. sqr-users Mailing List The sqr-users mailing list was created to enable discussion related to the SQR database language from Brio Technology (and to other products in the Brio.Report line). Note: SQR used to be published by SQRIBE Technologies, which was merged into Brio Technology on August 3, 1999. The mailing list is provided as a service of Ray Ontko & Co. A searchable archive of past postings to the mailing list is available from the SQR Users Group web site . 2.2. sqr-users Hints and Guidelines Document This document gives various hints and guidelines about the sqr-users mailing list. It will be posted approximately monthly to the mailing list so that new users can see it (and existing users can find it easily). An HTML version of the document is always available at http://www.sqrug.com/sqr-users/sqr-users-hints.html. If you have comments or suggestions about this document, please contact me directly at the address listed in the title section. This document is Copyright (C) 1999-2001 by Ray Ontko & Co. It may be freely copied and distributed provided it is not modified in any way and it retains the original copyright notice. 2.3. Related Mailing Lists sqr-jobs : SQR Jobs This list may be used for posting SQR-related resumes, recruitments, job announcements, project announcments, help- wanted, work-wanted ads, etc. To join, follow the link above, or send any message to "sqr-jobs-subscribe@yahoogroups.com". peoplesoft-fans : PeopleSoft Fans Moderated list covering technical and functional aspects of PeopleSoft. To join, follow the link above, or send any message to "peoplesoft-fans-subscribe@yahoogroups.com". psusers : PeopleSoft Users For questions related to any aspect of PeopleSoft (including PeopleSoft-specific SQR issues). This list has merged with the Peoplesoft Fans mailing list and is no longer active, but the message achive can still be found at the link above. rm-users : ReportMart Users List for discussion of Brio.Portal (previously called ReportMart). To join, follow the link above, or send a message containing the word "subscribe" in the body to "rm-users- request@sqrug.com". 3. Basic List Server Commands Note: The list used to be hosted on usa.net , but the list-hosting service was moved to list.iex.net . The old addresses for the list, list server, and list owner no longer work. A few basic list server commands are listed below. Send these commands as the only line in the body (not the subject line) of an e- mail message sent to "listserv@list.iex.net". 3.1. Subscribing to the List To subscribe to the mailing list, the command is subscribe sqr-users Your Name Note that you do not need to provide your e-mail address as it is taken from your message's header. You will be e-mailed a confirmation number, which you must then send back to the list server software before the command goes into effect. 3.2. Unsubscribing from the List To unsubscribe, the command is simply unsubscribe sqr-users Once again, you will need to confirm your command before it goes into effect. 3.3. Digests Once you are subscribed, you can request to get your messages in digest form (one large message containing all of the posts made each day) by sending the command set sqr-users digests If you use digests you should not just "reply" to the digest mailing. Instead, be sure your message's subject header matches the subject in the original posting to which you are replying, and that you don't include a quoted copy of the entire digest in your post. Use "nodigests" instead of "digests" to restore normal distribution. 3.4. Problems If the above commands do not work, please contact the list manager at "sqr-users-request@list.iex.net". (Do not send a message to the list itself -- no one there can help you with your problem!) Please include a copy of the error message you received from the list server. 4. Posting to the sqr-users List In order to post to the mailing list, you must be subscribed to it (see directions above). 4.1. General Guidelines For any message you send to the list, please: o make sure your mail software is not requesting a "Return Receipt". (If a "Return Receipt Requested" message is sent to the list, several dozen receipt messages will be sent out to the mailing list in reply from other subscribers.) o do not send messages in HTML or other special formats. (These are not usable by many subscribers, especially those who receive their messages in digest format.) o keep in mind that over 500 people will receive your message, and that it will appear in the list archives. Try to keep your messages on-topic and relatively short. Consider sending your message directly to particular people if it won't interest many of the list's subscribers. 4.2. Posting a Message Once you are subscribed, you can post to the list by sending a message to "sqr-users@list.iex.net". Be sure your subject heading describes your specific problem or question (i.e. don't just use "SQR Question"). In the body of your message, please include the following information: o Brio product and version number o Operating system and version number o Database name and version number o Application name (i.e. PeopleSoft) and a version number, if any Also, include a short code sample whenever possible. It's much easier for other people to understand what you are trying to do when they can see the actual code. (At the same time, be careful not to post confidential or proprietary code, since the posting will be placed in a public web archive.) Note that your question should relate to SQR or other Brio Report product in some way. Don't ask questions about PeopleSoft, for example, unless you are having SQR-related issues. To find out your version of SQR, you can run the following program: begin-program show $sqr-ver end-program This will produce a line that looks something like this: SQR/3.0.15/Intel/SCO Unix R3.2 V5.0/Oracle 7.2.2.3/Sep 25 1996 Include the full line of output in your message. Note that the oper- ating system version number listed here is the one under which this copy of SQR was compiled, so you'll still need to include your own information. 4.3. Replying to a Message Messages sent out on the list have a Reply-To header pointing to the list. This means that you should be able to simply use your mail reader's "reply" function to send a reply to the list. Generally, all answers to questions sent out on the list should be public replies, so that other subscribers can benefit. In those cases where a private reply is appropriate, be sure to over-ride the default Reply-To address. If your message is not actually a reply to the earlier message, please do not use the "reply" function. Instead, address a new e-mail message to the list as described in the previous section. (This is because the list archives will incorrectly show the new message in the original thread if you use "reply".) You should quote enough lines from the original message that later readers can understand your answer even if they missed the original question. At the same time, you should remove any unnecessary lines from the quote in order to make your message shorter and easier to understand. (Remember that some people read the mailing list in digest form, and the digests become much larger if unnecessary lines are quoted.) In particular, in order to prevent mail loops, the list server will not accept messages that contain From:, Sender:, etc. lines pointing back to the sqr-users list, even if these lines are quoted (i.e. with ">" characters). Be sure to delete these from your reply message before sending it. 5. Job Announcements Please do not post job announcements on the sqr-users list, but use the sqr-jobs list instead. (See ``Related Mailing Lists'' for more information on this list.) A large number of sqr-users subscribers do not want to receive job announcements (for various reasons) and such posts tend to cause a lot of disruption of the discussion on the list. From owner-sqr-users@list.iex.net Mon Jul 2 02:37:28 2001 Date: Mon, 2 Jul 2001 15:15:48 +0800 From: Emil Galicinao Subject: Re: How can SQR generate an excel file with numeric data being displayed as a string? Hi Rod, Arjan, Enclosing a numeric data by a double quote works !!! BTW, I tried using the second option but I was not able to get the same output as in option 1. (maybe it depends on the excel version) Any way, Thanks for the options/suggestions. Best Regards, Emil Rod Wright wrote: > You can get around this in two ways. The first is to place =" in front of > the number and " at the end of the number. So, 1234567890123 becomes > ="1234567890123". Excel will then treat this as text. This method is > particularly useful if you want to retain leading zeroes. (By the way, they > are double quotes.) > > The second method is to leave the number as is, and widen the column in > Excel. My experience has been that Excel switches to scientific notation > when the column is not wide enough to display the number. > > HTH, > Rod > > Emil Galicinao @list.iex.net> on 06/29/2001 > 06:27:01 AM > > Please respond to sqr-users@list.iex.net > > Sent by: "Discussion of SQR, Brio Technology's database > reporting language" > > To: SQR-USERS@list.iex.net > cc: > > Subject: How can SQR generate an excel file with numeric data being > displayed as a string? > > Hi Everyone, > > Does anybody know how to convert a numeric data into a string such that > when I generate the report in excel, that data will no longer be treated > as numeric? Right now, when a numeric data is more than 11 digits > long, excel usually convert the data into an exponential form which is > something Im trying to avoid. > > An example would be: > The number 123456789012 becomes 1.23457E+11 when viewed from excel. > > I was thinking of appending a special character on that numeric data but > unfortunately, that special charater also appear in the report. > > Any suggestions? > > Best Regards, > Emil From owner-sqr-users@list.iex.net Mon Jul 2 09:07:59 2001 Date: Mon, 2 Jul 2001 13:44:59 +0100 From: Margaret-Anne Bett Subject: Re: Data being truncated when converting from a flat file --0__=SkdkmNdHeuBtWEiApsNIG3JGdqXuHtnppccRpLEbzpvmqaJrLXeXAB5F Content-type: text/plain; charset=us-ascii Content-Disposition: inline (See attached file: log.txt) I've attached part of the log file, showing 3 vendors. In the first one the data in 'address3' is truncated to 'New Ci' during the load. The second vendor loads fine. 'Address1' is truncated to '309-1228 Ol' on the third vendor. As you can see, it happens on different fields on different vendors! Margaret-Anne ANDERSD2 on 29/06/2001 17:36:30 Please respond to sqr-users@list.iex.net To: SQR-USERS@list.iex.net cc: (bcc: Margaret-Anne Bett/LearningTree/GB) Subject: Re: Data being truncated when converting from a flat file I see you have a SHOW command. Could you attach a snippet of your LOG so we can see what the variable actually holds. Also, if you would, add additional SHOW statements so we can see the values of the variables after you UNSTRING the initial variable. Thanks, d -----Original Message----- From: Margaret-Anne Bett [mailto:Margaret-Anne_Bett@LEARNINGTREE.COM] Sent: Friday, June 29, 2001 11:52 AM To: SQR-USERS@list.iex.net Subject: Re: Data being truncated when converting from a flat file I've amended the code to only populate 2 tables - Vendors and Vendor Addresses. (See attached file: AP_Vend_Conv.sqr) Margaret-Anne ANDERSD2 on 29/06/2001 16:38:20 Please respond to sqr-users@list.iex.net To: SQR-USERS@list.iex.net cc: (bcc: Margaret-Anne Bett/LearningTree/GB) Subject: Re: Data being truncated when converting from a flat file yes, attach, copy&paste, retype, whatever :) -----Original Message----- From: Margaret-Anne Bett [mailto:Margaret-Anne_Bett@LEARNINGTREE.COM] Sent: Friday, June 29, 2001 11:22 AM To: SQR-USERS@list.iex.net Subject: Re: Data being truncated when converting from a flat file I'm using '|' to delimit the file. I'm new to this user group so I'm not sure where to post sample data and code. Should I just attach it to my email? Margaret-Anne Jamie Harris on 29/06/2001 15:54:51 Please respond to sqr-users@list.iex.net To: SQR-USERS@list.iex.net cc: (bcc: Margaret-Anne Bett/LearningTree/GB) Subject: Re: Data being truncated when converting from a flat file What delimiter are you using to unstring? Are you able to post any code or sample data? That may help us find the problem. ----------------------------------------------------- Jamie Harris Junior Programmer/Analyst/Software Integrator Office of Information Technology Frederick Community College ----------------------------------------------------- >>> Margaret-Anne_Bett@LEARNINGTREE.COM 6/28/2001 12:04:08 PM >>> I'm using the unstring command to split the data into various data fields. I then use rtrim to remove any spaces from the end of the data. There are no special characters. Thanks for all the suggestions so far, but I'm still having problems! Margaret-Anne Rhonda Hudgins on 26/06/2001 12:49:13 Please respond to sqr-users@list.iex.net To: SQR-USERS@list.iex.net cc: (bcc: Margaret-Anne Bett/LearningTree/GB) Subject: Re: Data being truncated when converting from a flat file How are you defining your input (flat) file? If you are using "substr" to move the information to a field check the format. for example: LET $Address = substr ($RECIN, 48, 35) This starts in column 48 of the input record and includes 35 characters. Thanks! Rhonda Hudgins @}---'----,---- Genencor International, Inc 716/256-5266 rhudgins@genencor.com Margaret-Anne Bett cc: Sent by: "Discussion of Subject: Re: Data being truncated when converting from a flat file SQR,Brio Technology's database reporting language" 06/25/2001 08:20 PM Please respond to sqr-users The data will definitely fit into the fields - the text file field has 30 characters and the database is 55. A lot of the time the data is truncated to just one character. Margaret-Anne cshelton on 25/06/2001 22:30:40 Please respond to sqr-users@list.iex.net To: SQR-USERS@list.iex.net cc: (bcc: Margaret-Anne Bett/LearningTree/GB) Subject: Re: Data being truncated when converting from a flat file Have you verified that the fields that are being truncated are defined large enough in the database to be able to accept the largest values from your flat files? I haven't worked much with SQL server, but in Sybase (the grandparent of SQL server) if you attempt to insert a value too large to fit in a column, the database will silently truncate your value to the size of the column in the database. chris On Mon, 25 Jun 2001, Margaret-Anne Bett wrote: > PeopleSoft 8.13 > Microsoft Sequel Server 2000 > Windows 2000 > > I am trying to convert data from a flat file into the PS_VENDOR_ADDR table. > Several of the data fields are being truncated. This happens on different > fields on different vendors, but is consistent each time I run the conversion > SQR. There doesn't seem to be any logic as to which field is truncated on which > vendor. I have show statements in the SQR which return the correct value. > > Margaret-Anne > > --0__=SkdkmNdHeuBtWEiApsNIG3JGdqXuHtnppccRpLEbzpvmqaJrLXeXAB5F Content-type: application/octet-stream; name="log.txt" Content-Disposition: attachment; filename="log.txt" Content-transfer-encoding: base64 Content-Description: Text - character set unknown I3ZlbmRvcmNvZGU6IDEwMDAwMjc1OTMuMDAwMDAwDQokdmVuZG9yX2lkOiAxMDAwMDI3NTkzDQpI ZWFkZXI6IFVTMTUwfChVU00gKXxVLlMuIE1PTklUT1IgU0VSVklDRXxTfDg2IE1hcGxlIEF2ZW51 ZXwgfE5ldyBDaXR5fE5ZfDEwOTU2LTUwOTJ8VVNBfCB8OTE0NjM0MTMzMXwwfCB8ICAgICAgICAg MHwgfCB8MzB8WXwuMDB8QXxNfE58IHwgfCB8T0xJVkVPfE1BTkFHRVJ8MjAwMTAyMDV8MjAwMTA2 MjJ8LjAwfDIwMDEwMjA3fExVICAwMDAwMzA3NHwxMTM4LjEwfDAwMTEzMHwwMDExMjA1NXxOfCB8 IHwgfDB8MHwgfCB8IHwgfDkxNC02MzQtOTYxOHwgfDB8Tnw5MTQ2MzQxMzMxDQpJbnNlcnQtVmVu ZG9yDQpTRVQgSUQ6IENBTjAxDQpQUyBWZW5kb3IgQ29kZTogMTAwMDAyNzU5Mw0KVkVORE9SOiAN ClVTMTUwDQpWRU5ET1IgU0hPUlQgTkFNRTogKFVTTSApLTAwMQ0KVkVORE9SIFNIT1JUOiAoVVNN ICkNCk5BTUU6IFUuUy4gTU9OSVRPUiBTRVJWSUNFDQpUeXBlOiBTDQpBRERSRVNTMTogODYgTWFw bGUgQXZlbnVlDQpBRERSRVNTMjogIA0KQUREUkVTUzM6IE5ldyBDaXR5DQpSRUdJT046IE5ZDQpQ T1NUQUwgQ09ERTogMTA5NTYtNTA5Mg0KQ09VTlRSWTogVVNBDQpDT05UQUNUOiAgDQpQSE9ORTog OTE0NjM0MTMzMQ0KUEhPTkUgRVhUOiAwDQpUNEFfMTA5OTogIA0KVEFYIElERU5UIE5POiAgICAg ICAgICAwDQpVU0UgVEFYIENPREU6ICANCkZVTEwgQUNDT1VOVDogIA0KVGVybXM6IDMwDQpIb2xk OiBZDQpNQVggSU5WIEFNVDogLjAwDQpWRU5ET1IgU1RBVFVTOiBBDQpDaGVxL0ludiBDb2RlOiBN DQpUYWtlIERpc2M6IE4NClZlbmRvciBNYXN0ZXIgR3JvdXA6ICANCkdyb3VwIFByb2M6ICANCkFS IEN1c3RvbWVyIENvZGU6ICANCkVudHJ5IE9QIElEOiBPTElWRU8NCk1haW50YWluIE9QIElEOiBN QU5BR0VSDQpDcmVhdGlvbiBEYXRlOiAyMDAxMDIwNQ0KRGF0ZSBNYWludGFpbmVkOiAyMDAxMDYy Mg0KQ3VycmVudCBCYWw6IC4wMA0KTGFzdCBQYXltZW50IERhdGU6IDIwMDEwMjA3DQpMYXN0IENo ZXF1ZTogTFUgIDAwMDAzMDc0DQpMYXN0IEFtb3VudDogMTEzOC4xMA0KRGF0ZSBvZiBsYXN0IElu dm9pY2U6IDAwMTEzMA0KTGFzdCBJbnZvaWNlOiAwMDExMjA1NQ0KRGVsZXRlIEZsYWQ6IE4NCkFs bG9jIENvZGU6ICANCkNoZXF1ZSBHcm91cDogIA0KQmFuayBDb2RlOiAgDQpEZWxheSBEYXlzOiAw DQpHcmFjZSBEYXlzOiAwDQpTaW46ICANClVzZXIgVGFiMTogdXNlcl90YSAxDQpVc2VyIFZhbDE6 ICANClVzZXIgVGFiMjogIA0KVXNlciBWYWwyOiA5MTQtNjM0LTk2MTgNClRhYmxlIERlc2M6ICAN ClRJTiBOb3RpY2U6IDANCkZvcmVpZ246IE4NClBob25lIENoYXI6IDkxNDYzNDEzMzENCioqKioq KioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqDQpQU19WRU5ET1INClBTX1ZFTkRP Ul9BRERSDQpQU19WRU5ET1JfTE9DDQpQU19WRU5ET1JfUEFZDQpQU19WTkRSX0FERFJfU0NST0wN ClBTX1ZORFJfQ05UQ1RfU0NSDQpQU19WRU5ET1JfQ05UQ1QNCiANClBTX1ZORFJfTE9DX1NDUk9M TA0KUFNfVkVORE9SX1dUSEQNClBTX1ZFTkRPUl9BRERSX1BITg0KUFNfVkVORE9SX0FERFJfUEhO DQojdmVuZG9yY29kZTogMTAwMDAyNzU5NC4wMDAwMDANCiR2ZW5kb3JfaWQ6IDEwMDAwMjc1OTQN CkhlYWRlcjogWEU3MDB8IHxYRVJPWCBDQU5BREEgTFRELnxTfFBPIEJveCA1MDAwfCB8QnVybGlu Z3RvbnxPTnxMN1IgNFMyfENBTnxOQURJQXw4ODgyODI4MDg4fDB8IHwgICAgICAgICAwfCB8IHwz MHxZfC4wMHxBfE18TnwgfCB8IHxPTElWRU98SlVMSUVNfDIwMDEwMjI2fDIwMDEwNjI1fDEzMTE1 LjA0fDIwMDEwNTI5fExDICAwMDA3MzQ5Mnw4NTk2LjI1fDAxMDYwN3xGMzI4MTA3MDl8TnwgfCB8 IHwwfDB8IHwgfFIxMDU3NTY1ODl8IHw0MTYtNzMzLTMwODZ8IHwwfE58ODAwMzg3MTM2NQ0KSW5z ZXJ0LVZlbmRvcg0KU0VUIElEOiBDQU4wMQ0KUFMgVmVuZG9yIENvZGU6IDEwMDAwMjc1OTQNClZF TkRPUjogDQpYRTcwMA0KVkVORE9SIFNIT1JUIE5BTUU6IFhFUk9YIENBTkEtMDAxDQpWRU5ET1Ig U0hPUlQ6IFhFUk9YIENBTkENCk5BTUU6IFhFUk9YIENBTkFEQSBMVEQuDQpUeXBlOiBTDQpBRERS RVNTMTogUE8gQm94IDUwMDANCkFERFJFU1MyOiAgDQpBRERSRVNTMzogQnVybGluZ3Rvbg0KUkVH SU9OOiBPTg0KUE9TVEFMIENPREU6IEw3UiA0UzINCkNPVU5UUlk6IENBTg0KQ09OVEFDVDogTkFE SUENClBIT05FOiA4ODgyODI4MDg4DQpQSE9ORSBFWFQ6IDANClQ0QV8xMDk5OiAgDQpUQVggSURF TlQgTk86ICAgICAgICAgIDANClVTRSBUQVggQ09ERTogIA0KRlVMTCBBQ0NPVU5UOiAgDQpUZXJt czogMzANCkhvbGQ6IFkNCk1BWCBJTlYgQU1UOiAuMDANClZFTkRPUiBTVEFUVVM6IEENCkNoZXEv SW52IENvZGU6IE0NClRha2UgRGlzYzogTg0KVmVuZG9yIE1hc3RlciBHcm91cDogIA0KR3JvdXAg UHJvYzogIA0KQVIgQ3VzdG9tZXIgQ29kZTogIA0KRW50cnkgT1AgSUQ6IE9MSVZFTw0KTWFpbnRh aW4gT1AgSUQ6IEpVTElFTQ0KQ3JlYXRpb24gRGF0ZTogMjAwMTAyMjYNCkRhdGUgTWFpbnRhaW5l ZDogMjAwMTA2MjUNCkN1cnJlbnQgQmFsOiAxMzExNS4wNA0KTGFzdCBQYXltZW50IERhdGU6IDIw MDEwNTI5DQpMYXN0IENoZXF1ZTogTEMgIDAwMDczNDkyDQpMYXN0IEFtb3VudDogODU5Ni4yNQ0K RGF0ZSBvZiBsYXN0IEludm9pY2U6IDAxMDYwNw0KTGFzdCBJbnZvaWNlOiBGMzI4MTA3MDkNCkRl bGV0ZSBGbGFkOiBODQpBbGxvYyBDb2RlOiAgDQpDaGVxdWUgR3JvdXA6ICANCkJhbmsgQ29kZTog IA0KRGVsYXkgRGF5czogMA0KR3JhY2UgRGF5czogMA0KU2luOiAgDQpVc2VyIFRhYjE6IHVzZXJf dGEgMQ0KVXNlciBWYWwxOiBSMTA1NzU2NTg5DQpVc2VyIFRhYjI6ICANClVzZXIgVmFsMjogNDE2 LTczMy0zMDg2DQpUYWJsZSBEZXNjOiAgDQpUSU4gTm90aWNlOiAwDQpGb3JlaWduOiBODQpQaG9u ZSBDaGFyOiA4MDAzODcxMzY1DQoqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioq KioqKg0KUFNfVkVORE9SDQpQU19WRU5ET1JfQUREUg0KUFNfVkVORE9SX0xPQw0KUFNfVkVORE9S X1BBWQ0KUFNfVk5EUl9BRERSX1NDUk9MDQpQU19WTkRSX0NOVENUX1NDUg0KUFNfVkVORE9SX0NO VENUDQpOQURJQQ0KUFNfVk5EUl9MT0NfU0NST0xMDQpQU19WRU5ET1JfV1RIRA0KUFNfVkVORE9S X0FERFJfUEhODQpQU19WRU5ET1JfQUREUl9QSE4NCiN2ZW5kb3Jjb2RlOiAxMDAwMDI3NTk1LjAw MDAwMA0KJHZlbmRvcl9pZDogMTAwMDAyNzU5NQ0KSGVhZGVyOiBZTzEwMHwgfFlPUksgQURWRVJU SVNJTkcvTUFJTElOR1MgTFRELnxTfDMwOS0xMjI4IE9sZCBJbm5lcyBSb2FkfCB8T3R0YXdhfE9O fEsxQiAzVjN8Q0FOfCB8NjEzNzQ1MjE3MXwwfCB8ICAgICAgICAgMHwgfCB8MzB8WXwuMDB8QXxN fE58IHwgfCB8SlVMSUVNfE1BTkFHRVJ8MjAwMTA2MjV8MjAwMTA2MjV8MTIxMTEuODF8MjAwMTA1 Mjl8TEMgIDAwMDczNDkzfDQwMDEuNDd8MDEwNTE1fDM5NjYxfE58IHwgfCB8MHwwfCB8IHxSMTA1 NzY3NzAxfCB8NjEzLTc0NS00OTM3fCB8MHxOfDYxMzc0NTIxNzENCkluc2VydC1WZW5kb3INClNF VCBJRDogQ0FOMDENClBTIFZlbmRvciBDb2RlOiAxMDAwMDI3NTk1DQpWRU5ET1I6IA0KWU8xMDAN ClZFTkRPUiBTSE9SVCBOQU1FOiBZT1JLIEFEVkVSLTAwMQ0KVkVORE9SIFNIT1JUOiBZT1JLIEFE VkVSDQpOQU1FOiBZT1JLIEFEVkVSVElTSU5HL01BSUxJTkdTIExURC4NClR5cGU6IFMNCkFERFJF U1MxOiAzMDktMTIyOCBPbGQgSW5uZXMgUm9hZA0KQUREUkVTUzI6ICANCkFERFJFU1MzOiBPdHRh d2ENClJFR0lPTjogT04NClBPU1RBTCBDT0RFOiBLMUIgM1YzDQpDT1VOVFJZOiBDQU4NCkNPTlRB Q1Q6ICANClBIT05FOiA2MTM3NDUyMTcxDQpQSE9ORSBFWFQ6IDANClQ0QV8xMDk5OiAgDQpUQVgg SURFTlQgTk86ICAgICAgICAgIDANClVTRSBUQVggQ09ERTogIA0KRlVMTCBBQ0NPVU5UOiAgDQpU ZXJtczogMzANCkhvbGQ6IFkNCk1BWCBJTlYgQU1UOiAuMDANClZFTkRPUiBTVEFUVVM6IEENCkNo ZXEvSW52IENvZGU6IE0NClRha2UgRGlzYzogTg0KVmVuZG9yIE1hc3RlciBHcm91cDogIA0KR3Jv dXAgUHJvYzogIA0KQVIgQ3VzdG9tZXIgQ29kZTogIA0KRW50cnkgT1AgSUQ6IEpVTElFTQ0KTWFp bnRhaW4gT1AgSUQ6IE1BTkFHRVINCkNyZWF0aW9uIERhdGU6IDIwMDEwNjI1DQpEYXRlIE1haW50 YWluZWQ6IDIwMDEwNjI1DQpDdXJyZW50IEJhbDogMTIxMTEuODENCkxhc3QgUGF5bWVudCBEYXRl OiAyMDAxMDUyOQ0KTGFzdCBDaGVxdWU6IExDICAwMDA3MzQ5Mw0KTGFzdCBBbW91bnQ6IDQwMDEu NDcNCkRhdGUgb2YgbGFzdCBJbnZvaWNlOiAwMTA1MTUNCkxhc3QgSW52b2ljZTogMzk2NjENCkRl bGV0ZSBGbGFkOiBODQpBbGxvYyBDb2RlOiAgDQpDaGVxdWUgR3JvdXA6ICANCkJhbmsgQ29kZTog IA0KRGVsYXkgRGF5czogMA0KR3JhY2UgRGF5czogMA0KU2luOiAgDQpVc2VyIFRhYjE6IHVzZXJf dGEgMQ0KVXNlciBWYWwxOiBSMTA1NzY3NzAxDQpVc2VyIFRhYjI6ICANClVzZXIgVmFsMjogNjEz LTc0NS00OTM3DQpUYWJsZSBEZXNjOiAgDQpUSU4gTm90aWNlOiAwDQpGb3JlaWduOiBODQpQaG9u ZSBDaGFyOiA2MTM3NDUyMTcxDQoqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioq KioqKg0KUFNfVkVORE9SDQpQU19WRU5ET1JfQUREUg0KUFNfVkVORE9SX0xPQw0KUFNfVkVORE9S X1BBWQ0KUFNfVk5EUl9BRERSX1NDUk9MDQpQU19WTkRSX0NOVENUX1NDUg0KUFNfVkVORE9SX0NO VENUDQogDQpQU19WTkRSX0xPQ19TQ1JPTEwNClBTX1ZFTkRPUl9XVEhEDQpQU19WRU5ET1JfQURE Ul9QSE4NClBTX1ZFTkRPUl9BRERSX1BITg0KDQpTUVI6IEVuZCBvZiBSdW4uDQo= --0__=SkdkmNdHeuBtWEiApsNIG3JGdqXuHtnppccRpLEbzpvmqaJrLXeXAB5F-- From owner-sqr-users@list.iex.net Mon Jul 2 09:55:28 2001 Date: Mon, 2 Jul 2001 10:41:40 -0400 From: ANDERSD2 Subject: Re: Data being truncated when converting from a flat file It is curious to see it work sometimes, and othertimes not. My (lame) suggestion, and this isn't much, change your INSERT statements so they are only inserting the variable. Move your ISNULL tests into a procedure so the INSERTs only have variables and no SQL. No real hope with this suggestion, but SQR is sometimes a little flaky. Add a procedure to SELECT from the table you are inserting just after the INSERT. SELECT only that row and SHOW the values. I am interested to see what is actually making it into the database. Also, and this is a personal preference, you may want to investigate the SHOW command instead of DISPLAY. The SHOW allows you to put multiple items (text, numeric/character variables) on one line. The following is an example... FROM... Display 'SET ID: ' Noline Display $set_id TO Show 'Set ID: ' $Set_ID Also, when showing/displaying variables (especially character variables), I use the "batwing" approach so I can see if the variable is null, space, or something else... Show 'Set ID >' $Set_ID '<' This way, if $Set_ID is null it will show... Set ID >< Good luck, Dave From owner-sqr-users@list.iex.net Mon Jul 2 14:39:14 2001 Date: Mon, 2 Jul 2001 12:58:04 -0600 From: jim nowlin Subject: Dateadd function error Brio product and version number: SQR V4.3.4 Operating system and version number: True64 (Compaq) 5.0A Database name and version number: Oracle 7.3.4.0.0 This is the first time I tried to use "dateadd". The code I'm using follows: let $sunday = dateadd($parm-end-dt,'DAY',6) I get the following error in the sqrlog: Error on line 118: (SQR 4045) Function or operator 'dateadd' requires date argument. Any ideas on what I'm doing wrong? If I can get this to work, can I use a negitive number to subtract six days? Thanks for the help, Jim Nowlin jim_nowlin@navigators.org From owner-sqr-users@list.iex.net Mon Jul 2 14:47:45 2001 Date: Mon, 2 Jul 2001 15:29:56 -0400 From: "Gagnon, Dave" Subject: Re: Dateadd function error If you are using SQR with Peoplesoft... Peoplesoft provides some custom SQC's , one specifically for date math,,they have both Add-days and a Subtract-Days functions. Dave Gagnon Fletcher Allen Health Care -----Original Message----- From: jim nowlin [mailto:jim_nowlin@NAVIGATORS.ORG] Sent: Monday, July 02, 2001 2:58 PM To: SQR-USERS@list.iex.net Subject: Dateadd function error Brio product and version number: SQR V4.3.4 Operating system and version number: True64 (Compaq) 5.0A Database name and version number: Oracle 7.3.4.0.0 This is the first time I tried to use "dateadd". The code I'm using follows: let $sunday = dateadd($parm-end-dt,'DAY',6) I get the following error in the sqrlog: Error on line 118: (SQR 4045) Function or operator 'dateadd' requires date argument. Any ideas on what I'm doing wrong? If I can get this to work, can I use a negitive number to subtract six days? Thanks for the help, Jim Nowlin jim_nowlin@navigators.org From owner-sqr-users@list.iex.net Mon Jul 2 14:59:21 2001 Date: Mon, 2 Jul 2001 13:47:16 -0600 From: Thyleen Tenney Subject: Re: Dateadd function error Usually fixed by declaring your variable as a date. I find that if I need a date variable in SQR, I declare it so in the beginning. T -----Original Message----- From: Discussion of SQR, Brio Technology's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of jim nowlin Sent: Monday, July 02, 2001 12:58 PM To: SQR-USERS@list.iex.net Subject: Dateadd function error Brio product and version number: SQR V4.3.4 Operating system and version number: True64 (Compaq) 5.0A Database name and version number: Oracle 7.3.4.0.0 This is the first time I tried to use "dateadd". The code I'm using follows: let $sunday = dateadd($parm-end-dt,'DAY',6) I get the following error in the sqrlog: Error on line 118: (SQR 4045) Function or operator 'dateadd' requires date argument. Any ideas on what I'm doing wrong? If I can get this to work, can I use a negitive number to subtract six days? Thanks for the help, Jim Nowlin jim_nowlin@navigators.org From owner-sqr-users@list.iex.net Mon Jul 2 14:59:04 2001 Date: Mon, 2 Jul 2001 14:47:44 -0500 From: Bob Helm Subject: Re: Dateadd function error I believe you have to declare $sunday as a date variable, like: declare-variable date $sunday end-declare That ought to do it, hopefully. B The Programmer formerly known as Bob Mediware Information Systems Inc. (913) 307-1045 Bob.Helm@Mediware.com -----Original Message----- From: jim nowlin [mailto:jim_nowlin@NAVIGATORS.ORG] Sent: Monday, July 02, 2001 1:58 PM To: SQR-USERS@list.iex.net Subject: Dateadd function error Brio product and version number: SQR V4.3.4 Operating system and version number: True64 (Compaq) 5.0A Database name and version number: Oracle 7.3.4.0.0 This is the first time I tried to use "dateadd". The code I'm using follows: let $sunday = dateadd($parm-end-dt,'DAY',6) I get the following error in the sqrlog: Error on line 118: (SQR 4045) Function or operator 'dateadd' requires date argument. Any ideas on what I'm doing wrong? If I can get this to work, can I use a negitive number to subtract six days? Thanks for the help, Jim Nowlin jim_nowlin@navigators.org From owner-sqr-users@list.iex.net Mon Jul 2 17:10:53 2001 Date: Mon, 2 Jul 2001 13:23:18 -0700 From: Carlton Conley Subject: Re: Dateadd function error in the begin-setup section of the SQR you must declare the $ variable as a date, by default it is treated as a string and while will insert if in the proper format or be treated as a date inside a query, to SQR it is not a date so begin-setup declare-variable date $sunday end-declare end-setup This should solve your problem -----Original Message----- From: Discussion of SQR, Brio Technology's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of jim nowlin Sent: Monday, July 02, 2001 11:58 AM To: SQR-USERS@list.iex.net Subject: Dateadd function error Brio product and version number: SQR V4.3.4 Operating system and version number: True64 (Compaq) 5.0A Database name and version number: Oracle 7.3.4.0.0 This is the first time I tried to use "dateadd". The code I'm using follows: let $sunday = dateadd($parm-end-dt,'DAY',6) I get the following error in the sqrlog: Error on line 118: (SQR 4045) Function or operator 'dateadd' requires date argument. Any ideas on what I'm doing wrong? If I can get this to work, can I use a negitive number to subtract six days? Thanks for the help, Jim Nowlin jim_nowlin@navigators.org From owner-sqr-users@list.iex.net Mon Jul 2 15:45:43 2001 Date: Mon, 2 Jul 2001 16:35:59 -0400 From: Rick_Creel@AONCONS.COM Subject: Re: Dateadd function error Both variables ($sunday and $parm-end-dt) need to be explicitly declared as date. begin-setup declare-variable date $sunday, $parm-end-dt end-declare end-setup jim nowlin on 07/02/2001 02:58:04 PM Please respond to sqr-users@list.iex.net To: SQR-USERS@list.iex.net cc: (bcc: Rick Creel/IT/Aon Consulting) Subject: Dateadd function error Brio product and version number: SQR V4.3.4 Operating system and version number: True64 (Compaq) 5.0A Database name and version number: Oracle 7.3.4.0.0 This is the first time I tried to use "dateadd". The code I'm using follows: let $sunday = dateadd($parm-end-dt,'DAY',6) I get the following error in the sqrlog: Error on line 118: (SQR 4045) Function or operator 'dateadd' requires date argument. Any ideas on what I'm doing wrong? If I can get this to work, can I use a negitive number to subtract six days? Thanks for the help, Jim Nowlin jim_nowlin@navigators.org From owner-sqr-users@list.iex.net Mon Jul 2 16:12:04 2001 Date: Mon, 2 Jul 2001 16:37:55 -0400 From: "Beller, Jay" Subject: Re: Dateadd function error and to answer your second question, you can use negatives, as in the following excerpt: let $last_check_date = dateadd($AsofDate, 'DAY', 8) let $first_check_date = dateadd($AsOfDAte, 'DAY', -8) -----Original Message----- From: jim nowlin [mailto:jim_nowlin@NAVIGATORS.ORG] Sent: Monday, July 02, 2001 2:58 PM To: SQR-USERS@list.iex.net Subject: Dateadd function error Brio product and version number: SQR V4.3.4 Operating system and version number: True64 (Compaq) 5.0A Database name and version number: Oracle 7.3.4.0.0 This is the first time I tried to use "dateadd". The code I'm using follows: let $sunday = dateadd($parm-end-dt,'DAY',6) I get the following error in the sqrlog: Error on line 118: (SQR 4045) Function or operator 'dateadd' requires date argument. Any ideas on what I'm doing wrong? If I can get this to work, can I use a negitive number to subtract six days? Thanks for the help, Jim Nowlin jim_nowlin@navigators.org From owner-sqr-users@list.iex.net Tue Jul 10 09:45:17 2001 Date: Tue, 10 Jul 2001 10:28:37 -0400 From: Dominick LoGiudice Subject: SQR VIEWER SAVE AS CSV I am trying to use the save as .csv option in sqr viewer version 4.3.4 and it is not working. Is there a work around ? Thanks From owner-sqr-users@list.iex.net Tue Jul 10 11:14:32 2001 Date: Tue, 10 Jul 2001 13:05:34 -0300 From: Peter Clark Subject: Re: SQR VIEWER SAVE AS CSV There is a problem with that in 4.3.4. If you have an older version of SQR, you could use the Viewer from that version (we fell back to our 4.3.2 version of the Viewer). If not, you'll have to look into getting a newer version. >>> Dominick LoGiudice 2001/07/10 11:28:37 am >>> I am trying to use the save as .csv option in sqr viewer version 4.3.4 and it is not working. Is there a work around ? Thanks From owner-sqr-users@list.iex.net Tue Jul 10 11:40:50 2001 Date: Tue, 10 Jul 2001 18:47:23 +0200 From: Franck Masson Subject: Re: SQR VIEWER SAVE AS CSV check your installation and check if you have a JRE directory present. -----Original Message----- From: Discussion of SQR, Brio Technology's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Peter Clark Sent: mardi 10 juillet 2001 18:06 To: SQR-USERS@list.iex.net Subject: Re: SQR VIEWER SAVE AS CSV There is a problem with that in 4.3.4. If you have an older version of SQR, you could use the Viewer from that version (we fell back to our 4.3.2 version of the Viewer). If not, you'll have to look into getting a newer version. >>> Dominick LoGiudice 2001/07/10 11:28:37 am >>> I am trying to use the save as .csv option in sqr viewer version 4.3.4 and it is not working. Is there a work around ? Thanks From owner-sqr-users@list.iex.net Tue Jul 10 11:39:42 2001 Date: Tue, 10 Jul 2001 13:31:00 -0300 From: Peter Clark Subject: Re: SQR VIEWER SAVE AS CSV there is also a problem with the Save As HTML option in 4.3.4 >>> Peter Clark 2001/07/10 1:05:34 pm >>> There is a problem with that in 4.3.4. If you have an older version of SQR, you could use the Viewer from that version (we fell back to our 4.3.2 version of the Viewer). If not, you'll have to look into getting a newer version. >>> Dominick LoGiudice 2001/07/10 11:28:37 am >>> I am trying to use the save as .csv option in sqr viewer version 4.3.4 and it is not working. Is there a work around ? Thanks From owner-sqr-users@list.iex.net Mon Jul 2 16:39:07 2001 Date: Mon, 2 Jul 2001 22:33:37 +0100 From: Andrew Rivers Subject: Re: Dateadd function error Jim Unless defined as a date or converted to date, $parm-end-dt is a string. In your set-up section, declare you date variable of type: date begin-setup declare-variable date $param.end-tt $anotherDate end-declare end-setup Alternatively you could use: let $parm-end-dt =3D datetostr( $parm-end-dt ) or let $sunday =3D dateadd( datetostr( $parm-end-dt ),'DAY',6)=20 Andrew ----- Original Message -----=20 From: jim nowlin To: Sent: Monday, July 02, 2001 7:58 PM Subject: Dateadd function error > Brio product and version number: SQR V4.3.4 > Operating system and version number: True64 (Compaq) 5.0A > Database name and version number: Oracle 7.3.4.0.0 >=20 > This is the first time I tried to use "dateadd". The code I'm using > follows: >=20 > let $sunday =3D dateadd($parm-end-dt,'DAY',6) >=20 > I get the following error in the sqrlog: >=20 > Error on line 118: > (SQR 4045) Function or operator 'dateadd' requires date argument. >=20 > Any ideas on what I'm doing wrong? >=20 > If I can get this to work, can I use a negitive number to subtract six > days? >=20 > Thanks for the help, > Jim Nowlin > jim_nowlin@navigators.org From owner-sqr-users@list.iex.net Mon Jul 2 16:39:03 2001 Date: Mon, 2 Jul 2001 16:26:10 -0500 From: "Brahmbhatt, Mike" Subject: EXPRESSIONSPACE Sqr ver 3.0.7.3, OS: HP-UX, DB: Informix I am getting the following error when using the let command. "(4038) out of space while processing expression; Use -Mfile to increase EXPRESSIONSPACE" I am trying to string several variables into one. After some debugging, I have found that if the total length of the string is 3275 character or less it works fine. Once I increase the string size to 3276 or more, I get the above message. I have read several posting and I have tried the following and I still get the message. I have included a copy of my allmaxes.max 1) Removed EXPRESSIONSPACE from allmaxes.max 2) Change the size of EXPRESSIONSPACE to 10000 3) Change the size of EXPRESSIONSPACE to 8000. 4) Specified -Mfile option on the command line. If anyone has other suggestions, I would greatly appreciate it. Thank you, Mike ! !CURSORS Max number of database cursors (SQLBase only) !DYNAMICARGS Max number of dynamic SQL arguments !EXPRESSIONSPACE Max length of temp string storage during LET operations !FORWARDREFS Max number of &column forward references !NUMVARIABLES Max number of numeric variables and literal values !ONBREAKS Max number of ON-BREAK LEVEL=values per SET !POSITIONS Max number of placement parameters, "(10,5,30)" !PROGLINEPARS Max number of arguments for all program lines !PROGLINES Max number of program lines (SQR commands) !QUERIES Max number of BEGIN-SQL and BEGIN-SELECT paragraphs !QUERYARGS Max number of bind variables for all SQL statements !SQLSIZE Max length of a SQL statement in characters !STRINGSPACE Max length of string space for program line arguments !SUBVARS Max number of run-time substitution variables !VARIABLES Max number of variables, literals and database columns !WHENS Max number of WHEN arguments allowed per EVALUATE ! !CURSORS=20 DYNAMICARGS=500 EXPRESSIONSPACE=65535 FORWARDREFS=1000 NUMVARIABLES=1100 ONBREAKS=75 POSITIONS=5000 PROGLINEPARS=65535 PROGLINES=10000 QUERIES=400 QUERYARGS=2300 SQLSIZE=10000 STRINGSPACE=60000 SUBVARS=2000 VARIABLES=5000 WHENS=1000 From owner-sqr-users@list.iex.net Wed Jul 4 01:31:01 2001 Date: Wed, 4 Jul 2001 00:58:19 -0500 From: Yogesh Kulkarni Subject: Re: EXPRESSIONSPACE Hi Mike, I have encountered a similar error, typically when a "Begin-SELECT" does not have a corresponding "End-SELECT"(or Begin-SQL does not have an End-SQL"). We are using SQR4.3, Oracle 8 on Solaris. The error message is a bit misleading. Hope that helps. Regards, Yogesh "Discussion of SQR, Brio Technology's database reporting language" 07/02/2001 04:26 PM Please respond to sqr-users@list.iex.net To: SQR-USERS@list.iex.net cc: Subject: EXPRESSIONSPACE Sqr ver 3.0.7.3, OS: HP-UX, DB: Informix I am getting the following error when using the let command. "(4038) out of space while processing expression; Use -Mfile to increase EXPRESSIONSPACE" I am trying to string several variables into one. After some debugging, I have found that if the total length of the string is 3275 character or less it works fine. Once I increase the string size to 3276 or more, I get the above message. I have read several posting and I have tried the following and I still get the message. I have included a copy of my allmaxes.max 1) Removed EXPRESSIONSPACE from allmaxes.max 2) Change the size of EXPRESSIONSPACE to 10000 3) Change the size of EXPRESSIONSPACE to 8000. 4) Specified -Mfile option on the command line. If anyone has other suggestions, I would greatly appreciate it. Thank you, Mike ! !CURSORS Max number of database cursors (SQLBase only) !DYNAMICARGS Max number of dynamic SQL arguments !EXPRESSIONSPACE Max length of temp string storage during LET operations !FORWARDREFS Max number of &column forward references !NUMVARIABLES Max number of numeric variables and literal values !ONBREAKS Max number of ON-BREAK LEVEL=values per SET !POSITIONS Max number of placement parameters, "(10,5,30)" !PROGLINEPARS Max number of arguments for all program lines !PROGLINES Max number of program lines (SQR commands) !QUERIES Max number of BEGIN-SQL and BEGIN-SELECT paragraphs !QUERYARGS Max number of bind variables for all SQL statements !SQLSIZE Max length of a SQL statement in characters !STRINGSPACE Max length of string space for program line arguments !SUBVARS Max number of run-time substitution variables !VARIABLES Max number of variables, literals and database columns !WHENS Max number of WHEN arguments allowed per EVALUATE ! !CURSORS=20 DYNAMICARGS=500 EXPRESSIONSPACE=65535 FORWARDREFS=1000 NUMVARIABLES=1100 ONBREAKS=75 POSITIONS=5000 PROGLINEPARS=65535 PROGLINES=10000 QUERIES=400 QUERYARGS=2300 SQLSIZE=10000 STRINGSPACE=60000 SUBVARS=2000 VARIABLES=5000 WHENS=1000 From owner-sqr-users@list.iex.net Tue Jul 3 11:33:03 2001 Date: Tue, 3 Jul 2001 17:03:04 +0100 From: Brian Smith Subject: Temporary tables in SQL Server 2000 When setting up an ODBC connection to a Microsoft SQL Server 2000 database, the option "Create temporary stored procedures for prepared SQL statements and drop the stored procedures" is forced ON and is not able to be cleared. This has the result that an SQR report which uses any temporary tables (CREATE TABLE ##temp in the begin-setup section) does not work, since the #temp table was created inside a stored procedure, and thus ceases to exist when the procedure ends. SQR then gives an error such as: (SQR 5528) ODBC SQL dbexec: SQLExecute error 208 in cursor 11: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#temp'. (SQR 5528) ODBC SQL dbexec: SQLExecute error 8180 in cursor 11: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. Error on line 125: (SQR 3735) Could not execute SQL. This is from numerous reports that work perfectly when targetting a SQL Server 6.5 database. Unfortunately the -XP command-line option is not available under Windows. Is there a fix for this (without changing the report)? Brian Smith ***IMPORTANT*** The information in this mail is confidential and is intended solely for the addressee. Access, copying or re-use of information in it by anyone else is unauthorised. Any views or opinions presented are solely those of the author and do not necessarily represent those of Tempo Ltd or any of its affiliates. From owner-sqr-users@list.iex.net Tue Jul 3 12:28:45 2001 Date: Tue, 3 Jul 2001 13:19:49 -0400 From: Philippe Godin Subject: Re: Temporary tables in SQL Server 2000 Good day Brian, Here's the solution for your problem... With Sql Server you can use either #mytable or ##mytable. The first statement creates a temporary table that is accessible only to the user that have created it and it only last the time of the connection. The second statement also creates a temp table which is available for all the users connected to the database. My experimentations with this makes me believes that each time you put a BEGIN-SQL and END-SQL block into your code, SQR opens a new connection to the database and then closes it. This means that your table is created but destroyed at the end of the block. When your using a statement like: create temporary table ##mytable (blablabla) SQR is really creating the temp table like this: #mytable. This symbol "#" is usually used to tell SQR that the following variable is to contain a number. If it isn't the case you have to double up the "#" caracter. (This is similar to CGI script if your willing to use a backslach you need to double it in order to use it.) It might be a bit complicated but what you need to use is something like this: create temporary table ###mytable which will really create a ##mytable in Sql Server. Since this table will be available to any user of the database you might want to use a dynamic name when creating such a table. If two users execute the report at the same time there could be errors generated. The solution I've found for this is to create the table using a timestamp. Here's the code I usually use to achieve this: BEGIN-SELECT current_timestamp ×tamp from dummy_table END-SELECT Let $timestamp = translate(×tamp,'-','') Let $timestamp = translate($timestamp,':','') Let $timestamp = translate($timestamp,'/','') Let $timestamp = translate($timestamp,' ','') Let $temptable = '###' || $timestamp BEGIN-SQL CREATE TABLE [$temptable] (col1SQL varchar(30),col2 int,col3 float,col4 float,col5 float,col6 float,col7 float,col8 float,col9 float,col10 float,col11 float,col12 float,col13 float); END-SQL Hope this helps Philippe Godin -----Original Message----- From: Discussion of SQR, Brio Technology's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Brian Smith Sent: 3 juillet, 2001 12:03 To: SQR-USERS@list.iex.net Subject: Temporary tables in SQL Server 2000 Importance: High When setting up an ODBC connection to a Microsoft SQL Server 2000 database, the option "Create temporary stored procedures for prepared SQL statements and drop the stored procedures" is forced ON and is not able to be cleared. This has the result that an SQR report which uses any temporary tables (CREATE TABLE ##temp in the begin-setup section) does not work, since the #temp table was created inside a stored procedure, and thus ceases to exist when the procedure ends. SQR then gives an error such as: (SQR 5528) ODBC SQL dbexec: SQLExecute error 208 in cursor 11: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#temp'. (SQR 5528) ODBC SQL dbexec: SQLExecute error 8180 in cursor 11: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. Error on line 125: (SQR 3735) Could not execute SQL. This is from numerous reports that work perfectly when targetting a SQL Server 6.5 database. Unfortunately the -XP command-line option is not available under Windows. Is there a fix for this (without changing the report)? Brian Smith ***IMPORTANT*** The information in this mail is confidential and is intended solely for the addressee. Access, copying or re-use of information in it by anyone else is unauthorised. Any views or opinions presented are solely those of the author and do not necessarily represent those of Tempo Ltd or any of its affiliates. From owner-sqr-users@list.iex.net Wed Jul 4 02:51:48 2001 Date: Wed, 4 Jul 2001 08:39:41 +0100 From: Brian Smith Subject: Re: Temporary tables in SQL Server 2000 Thanks, but we've been using temporary tables in SQR for many years, so we know the technique very well, and the limitations imposed by SQR. Your 'explanation' is not quite correct - temporary tables must first be defined in the setup section of the report, after which there is no need for any special handling (other than ALWAYS appending an extra # to the name) PROVIDED the table is only referenced in the top-level cursor. SQR only creates a second database connection (which cannot see tables created in the first) when a query is invoked from inside another begin-select section. It turns out my posting was unreliable - the programmer was using an older version of SQR (4.2) - when run under 6.0 the problem does not occur. Brian -----Original Message----- From: Philippe Godin [mailto:philippe.godin@VIDEOTRON.CA] Sent: Tuesday, 03 July 2001 18:20 To: SQR-USERS@list.iex.net Subject: Re: Temporary tables in SQL Server 2000 Good day Brian, Here's the solution for your problem... With Sql Server you can use either #mytable or ##mytable. The first statement creates a temporary table that is accessible only to the user that have created it and it only last the time of the connection. The second statement also creates a temp table which is available for all the users connected to the database. My experimentations with this makes me believes that each time you put a BEGIN-SQL and END-SQL block into your code, SQR opens a new connection to the database and then closes it. This means that your table is created but destroyed at the end of the block. When your using a statement like: create temporary table ##mytable (blablabla) SQR is really creating the temp table like this: #mytable. This symbol "#" is usually used to tell SQR that the following variable is to contain a number. If it isn't the case you have to double up the "#" caracter. (This is similar to CGI script if your willing to use a backslach you need to double it in order to use it.) It might be a bit complicated but what you need to use is something like this: create temporary table ###mytable which will really create a ##mytable in Sql Server. Since this table will be available to any user of the database you might want to use a dynamic name when creating such a table. If two users execute the report at the same time there could be errors generated. The solution I've found for this is to create the table using a timestamp. Here's the code I usually use to achieve this: BEGIN-SELECT current_timestamp ×tamp from dummy_table END-SELECT Let $timestamp = translate(×tamp,'-','') Let $timestamp = translate($timestamp,':','') Let $timestamp = translate($timestamp,'/','') Let $timestamp = translate($timestamp,' ','') Let $temptable = '###' || $timestamp BEGIN-SQL CREATE TABLE [$temptable] (col1SQL varchar(30),col2 int,col3 float,col4 float,col5 float,col6 float,col7 float,col8 float,col9 float,col10 float,col11 float,col12 float,col13 float); END-SQL Hope this helps Philippe Godin -----Original Message----- From: Discussion of SQR, Brio Technology's database reporting language [mailto:SQR-USERS@list.iex.net]On Behalf Of Brian Smith Sent: 3 juillet, 2001 12:03 To: SQR-USERS@list.iex.net Subject: Temporary tables in SQL Server 2000 Importance: High When setting up an ODBC connection to a Microsoft SQL Server 2000 database, the option "Create temporary stored procedures for prepared SQL statements and drop the stored procedures" is forced ON and is not able to be cleared. This has the result that an SQR report which uses any temporary tables (CREATE TABLE ##temp in the begin-setup section) does not work, since the #temp table was created inside a stored procedure, and thus ceases to exist when the procedure ends. SQR then gives an error such as: (SQR 5528) ODBC SQL dbexec: SQLExecute error 208 in cursor 11: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#temp'. (SQR 5528) ODBC SQL dbexec: SQLExecute error 8180 in cursor 11: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. Error on line 125: (SQR 3735) Could not execute SQL. This is from numerous reports that work perfectly when targetting a SQL Server 6.5 database. Unfortunately the -XP command-line option is not available under Windows. Is there a fix for this (without changing the report)? Brian Smith ***IMPORTANT*** The information in this mail is confidential and is intended solely for the addressee. Access, copying or re-use of information in it by anyone else is unauthorised. Any views or opinions presented are solely those of the author and do not necessarily represent those of Tempo Ltd or any of its affiliates. ***IMPORTANT*** The information in this mail is confidential and is intended solely for the addressee. Access, copying or re-use of information in it by anyone else is unauthorised. Any views or opinions presented are solely those of the author and do not necessarily represent those of Tempo Ltd or any of its affiliates. From owner-sqr-users@list.iex.net Tue Jul 3 12:30:42 2001 Date: Tue, 3 Jul 2001 11:27:39 -0600 From: "Ackerson, Glenn" Subject: Printing with bold Hi group, I've been looking through the archives to see if I could figure out why I can get the underline command to work fine, but not the bold command. I'm running an SQR report using the standard Peoplesoft "setenv.sqc" parameters, and I did notice that the default for the printer definition in this sqc is "#define PRINTER_TYPE LINEPRINTER." Based on a message in the archives, I tried changing the #define statement to the "#define PRINTER_TYPE HPLASERJET" instead, but I'm still getting the same result - underlines work fine, bold text doesn't print as bold. Any suggestions? Thanks in advance for your response. If you do respond, if you wouldn't mind cc'ing your response to me directly, I would get the response sooner (I get messages in digest mode.) Thanks! Cheers, Glenn Ackerson ************************************************** Glenn Ackerson Information Technology Professional III Internet Address: Glenn.Ackerson@Unco.edu University of Northern Colorado Information Technology Carter Hall Greeley, CO 80639 From owner-sqr-users@list.iex.net Tue Jul 3 13:02:37 2001 Date: Tue, 3 Jul 2001 14:00:40 -0400 From: Glenn Coulter Subject: Re: Printing with bold Hi Glenn, When I have changed to the HP printer definition I usually add in a 'Declare-Printer' statement as well. I have attached some sample code below which is working fine for me. HTH..Glenn Coulter Begin-Setup #Include 'setupdb.sqc' #define PRINTER_TYPE HPLASERJET Declare-Printer HP-definition Type={PRINTER_TYPE} Font=5 Point-Size=12 Pitch=17 Symbol-Set=0U ! ASCII symbol set End-Declare Declare-Layout default Orientation=Portrait Top-Margin= .4 Left-Margin=.40 !gmc from .6 Max-Lines=63 Max-Columns=130 Char-Width=7.2 Line-Height=12 ! 72/printer_point-size End-Declare End-Setup . . . (print logic example from the main program) !***'Total' print $BEN1-TOTAL (+2,1) bold print '$' (+0,#ccol5) let #col = #ccol5+1 Graphic (+0,#col,8) horz-line {NORM_LNSIZE} ! Horizontal line. -----Original Message----- From: Ackerson, Glenn [mailto:Glenn.Ackerson@UNCO.EDU] Sent: Tuesday, July 03, 2001 1:28 PM To: SQR-USERS@list.iex.net Subject: Printing with bold Hi group, I've been looking through the archives to see if I could figure out why I can get the underline command to work fine, but not the bold command. I'm running an SQR report using the standard Peoplesoft "setenv.sqc" parameters, and I did notice that the default for the printer definition in this sqc is "#define PRINTER_TYPE LINEPRINTER." Based on a message in the archives, I tried changing the #define statement to the "#define PRINTER_TYPE HPLASERJET" instead, but I'm still getting the same result - underlines work fine, bold text doesn't print as bold. Any suggestions? Thanks in advance for your response. If you do respond, if you wouldn't mind cc'ing your response to me directly, I would get the response sooner (I get messages in digest mode.) Thanks! Cheers, Glenn Ackerson ************************************************** Glenn Ackerson Information Technology Professional III Internet Address: Glenn.Ackerson@Unco.edu University of Northern Colorado Information Technology Carter Hall Greeley, CO 80639 From owner-sqr-users@list.iex.net Thu Jul 5 08:31:10 2001 Date: Thu, 5 Jul 2001 09:24:36 -0400 From: Denise White Subject: Re: Printing with bold Hi Glenn, I recently wanted to use bold font for the first time, and couldn't get it to work at first. I tried a lot of different things, including the #define statement you mentioned, but nothing was working. It turned out to be something interesting: I was still using the old setup .sqc files; I knew there were new ones, but since all my programs were working fine, I didn't bother switching to them. I had to change #Include 'setup01a.sqc' to #Include 'setup31.sqc' Then I was able to use bold! But the font size was coming out wrong, so I also had to use the following (I put them at the beginning of BEGIN-PROGRAM): use-printer-type hplaserjet alter-printer point-size=8 pitch=17 HTH, Denise White Sr. Programmer/Analyst Vicor ------------------------------ Date: Tue, 3 Jul 2001 11:27:39 -0600 From: "Ackerson, Glenn" Subject: Printing with bold Hi group, I've been looking through the archives to see if I could figure out why I can get the underline command to work fine, but not the bold command. I'm running an SQR report using the standard Peoplesoft "setenv.sqc" parameters, and I did notice that the default for the printer definition in this sqc is "#define PRINTER_TYPE LINEPRINTER." Based on a message in the archives, I tried changing the #define statement to the "#define PRINTER_TYPE HPLASERJET" instead, but I'm still getting the same result - underlines work fine, bold text doesn't print as bold. Any suggestions? Thanks in advance for your response. If you do respond, if you wouldn't mind cc'ing your response to me directly, I would get the response sooner (I get messages in digest mode.) Thanks! Cheers, Glenn Ackerson ************************************************** Glenn Ackerson Information Technology Professional III Internet Address: Glenn.Ackerson@Unco.edu University of Northern Colorado Information Technology Carter Hall Greeley, CO 80639 From owner-sqr-users@list.iex.net Tue Jul 3 13:19:29 2001 Date: Tue, 3 Jul 2001 13:55:08 -0500 From: Steven Stein Subject: Re: SQR-USERS Digest - 1 Jul 2001 to 2 Jul 2001 (#2001-160) Emil: There's an interesting technique you can use if you want to get fancy. Save the spreadsheet in exactly the format you want as a .SLK file. The code can then be seen in a Text Editor. You can then make the SQR write a .SLK file. Franck Masson taught us this technique in this user group. Here's a sample. By the way, most of that fancy-looking formatting is easy because I use Textpad and write a Macro to wrap the SQR code around the .SLK code. For instance, when I save my sample .SLK file from Excel I get a line like: P;P"$"#,##0_);;[Red]\("$"#,##0\) and I use a Textpad macro to change that to: write 1 from 'P;P"$"#,##0_);;[Red]\("$"#,##0\)' - Steven Stein, CPA Covance Inc. 609-452-4551 BEGIN-PROGRAM let $file = 'C:\INVVAL.txt' Open $file as 1 for-writing record = 1000 LET #Y = 2 LET #COUNT = 0 LET #PUTWY = 0 LET #CNT_PTWY = 0 LET #QTY_PTWY = 0 LET #ISSUES = 0 LET #CNT_ISS = 0 LET #QTY_ISS = 0 LET #ADJ = 0 LET #CNT_ADJ = 0 LET #ADJ_DECR = 0 LET #ADJ_INCR = 0 LET #OTHER = 0 LET #CNT_OTHER = 0 MOVE ' ' TO $BUSUNIT MOVE ' ' TO $ACCNT MOVE ' ' TO $ACCTDESC MOVE ' ' TO $INVID MOVE ' ' TO $UOMSTD MOVE ' ' TO $UOMUOM MOVE ' ' TO $ITEMDESC do write-header DO MAIN-REPORT do write-totals END-PROGRAM ! MAIN REPORT BEGIN-PROCEDURE MAIN-REPORT begin-SELECT A.BUSINESS_UNIT count(*) &COUNT A.ACCOUNT B.DESCR A.INV_ITEM_ID sum(decode( A.TRANSACTION_GROUP,'020', A.MONETARY_AMOUNT,0)) &PUTWY sum(decode( A.TRANSACTION_GROUP,'020',1,0)) &CNT_PTWY sum(decode( A.TRANSACTION_GROUP,'020', C.QTY_BASE,0)) &QTY_PTWY sum(decode( A.TRANSACTION_GROUP,'030', A.MONETARY_AMOUNT,0)) &ISSUES sum(decode( A.TRANSACTION_GROUP,'030',1,0)) &CNT_ISS sum(decode( A.TRANSACTION_GROUP,'030', C.QTY_BASE,0)) &QTY_ISS sum(decode( A.TRANSACTION_GROUP,'050', A.MONETARY_AMOUNT,0)) &ADJ sum(decode( A.TRANSACTION_GROUP,'050',1,0)) &CNT_ADJ sum(decode( A.TRANSACTION_GROUP ,'050', DECODE(C.ADJUST_TYPE,'D',C.QTY_BASE*-1,0),0)) &ADJ_DECR sum(decode( A.TRANSACTION_GROUP ,'050', DECODE(C.ADJUST_TYPE,'I',C.QTY_BASE,0),0)) &ADJ_INCR sum(decode( A.TRANSACTION_GROUP,'020',0,'030',0,'050',0, A.MONETARY_AMOUNT)) &OTHER sum(decode( A.TRANSACTION_GROUP,'020',0,'030',0,'050',0,1)) &CNT_OTHER C.UNIT_MEASURE_STD C.UNIT_OF_MEASURE D.DESCR LET #COUNT = &COUNT LET #PUTWY = &PUTWY LET #CNT_PTWY = &CNT_PTWY LET #QTY_PTWY = &QTY_PTWY LET #ISSUES = &ISSUES LET #CNT_ISS = &CNT_ISS LET #QTY_ISS = &QTY_ISS LET #ADJ = &ADJ LET #CNT_ADJ = &CNT_ADJ LET #ADJ_DECR = &ADJ_DECR LET #ADJ_INCR = &ADJ_INCR LET #OTHER = &OTHER LET #CNT_OTHER = &CNT_OTHER MOVE &A.BUSINESS_UNIT TO $BUSUNIT MOVE &A.ACCOUNT TO $ACCNT MOVE &B.DESCR TO $ACCTDESC MOVE &A.INV_ITEM_ID TO $INVID MOVE &C.UNIT_MEASURE_STD TO $UOMSTD MOVE &C.UNIT_OF_MEASURE TO $UOMUOM MOVE &D.DESCR TO $ITEMDESC DO WRITE-LINES FROM PS_CM_ACCTG_LINE A, PS_GL_ACCOUNT_TBL B, PS_TRANSACTION_INV C, PS_MASTER_ITEM_TBL D WHERE A.BUSINESS_UNIT = 'ABC01' AND B.ACCOUNT = A.ACCOUNT AND A.ACCOUNT LIKE '04%' AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.ACCOUNT = B_ED.ACCOUNT AND B_ED.EFFDT <= SYSDATE) AND B.SETID = 'XYZ01' AND D.SETID = 'ABC01' AND D.INV_ITEM_ID = C.INV_ITEM_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.DT_TIMESTAMP = C.DT_TIMESTAMP AND A.SEQ_NBR = C.SEQ_NBR AND A.TRANSACTION_GROUP = C.TRANSACTION_GROUP AND A.ACCOUNTING_DT <= TO_DATE('2001-04-30','YYYY-MM-DD') ! AND A.INV_ITEM_ID like '0000000000000078%' GROUP BY A.BUSINESS_UNIT, A.ACCOUNT, B.DESCR, A.INV_ITEM_ID, C.UNIT_MEASURE_STD, C.UNIT_OF_MEASURE, D.DESCR END-SELECT END-PROCEDURE ! WRITE header BEGIN-PROCEDURE WRITE-HEADER write 1 from 'ID;PWXL;N;E' write 1 from 'P;PGeneral' write 1 from 'P;P0' write 1 from 'P;P0.00' write 1 from 'P;P#,##0' write 1 from 'P;P#,##0.00' write 1 from 'P;P#,##0_);;\(#,##0\)' write 1 from 'P;P#,##0_);;[Red]\(#,##0\)' write 1 from 'P;P#,##0.00_);;\(#,##0.00\)' write 1 from 'P;P#,##0.00_);;[Red]\(#,##0.00\)' write 1 from 'P;P"$"#,##0_);;\("$"#,##0\)' write 1 from 'P;P"$"#,##0_);;[Red]\("$"#,##0\)' write 1 from 'P;P"$"#,##0.00_);;\("$"#,##0.00\)' write 1 from 'P;P"$"#,##0.00_);;[Red]\("$"#,##0.00\)' write 1 from 'P;P0%' write 1 from 'P;P0.00%' write 1 from 'P;P0.00E+00' write 1 from 'P;P##0.0E+0' write 1 from 'P;P#\ ?/?' write 1 from 'P;P#\ ??/??' write 1 from 'P;Pm/d/yy' write 1 from 'P;Pd\-mmm\-yy' write 1 from 'P;Pd\-mmm' write 1 from 'P;Pmmm\-yy' write 1 from 'P;Ph:mm\ AM/PM' write 1 from 'P;Ph:mm:ss\ AM/PM' write 1 from 'P;Ph:mm' write 1 from 'P;Ph:mm:ss' write 1 from 'P;Pm/d/yy\ h:mm' write 1 from 'P;Pmm:ss' write 1 from 'P;Pmm:ss.0' write 1 from 'P;P@' write 1 from 'P;P[h]:mm:ss' write 1 from 'P;P_("$"* #,##0_);;_("$"* \(#,##0\);;_("$"* "-"_);;_(@_)' write 1 from 'P;P_(* #,##0_);;_(* \(#,##0\);;_(* "-"_);;_(@_)' write 1 from 'P;P_("$"* #,##0.00_);;_("$"* \(#,##0.00\);;_("$"* "-"??_);;_(@_)' write 1 from 'P;P_(* #,##0.00_);;_(* \(#,##0.00\);;_(* "-"??_);;_(@_)' write 1 from 'P;FArial;M240' write 1 from 'P;FArial;M240' write 1 from 'P;FArial;M240' write 1 from 'P;FArial;M240' write 1 from 'P;EArial;M240' write 1 from 'P;EArial;M240;SB' write 1 from 'F;P0;DG0G8;M300' write 1 from 'B;Y3273;X22;D0 0 3272 21' write 1 from 'O;L;D;V0;K47;G100 0.001' write 1 from 'F;W3 3 0' write 1 from 'F;W5 5 0' write 1 from 'F;W7 7 36' write 1 from 'F;W8 8 0' write 1 from 'F;W9 9 15' write 1 from 'F;W10 11 10' write 1 from 'F;W12 12 16' write 1 from 'F;W13 13 0' write 1 from 'F;W15 15 13' write 1 from 'F;W16 16 0' write 1 from 'F;W17 17 13' write 1 from 'F;W18 18 11' write 1 from 'F;W19 20 0' write 1 from 'F;W22 22 13' write 1 from 'F;P35;FF2G;C9' write 1 from 'F;P1;FI0G;C11' write 1 from 'F;P35;FF2G;C12' write 1 from 'F;P35;FF2G;C15' write 1 from 'F;P7;FF2G;C17' write 1 from 'F;P35;FF2G;C22' write 1 from 'F;P0;FG0C;SDBM6;M315;R1' write 1 from 'NN;NPrint_Titles;ER1' write 1 from 'F;Y1;X1' write 1 from 'C;K"BUSUNIT"' write 1 from 'F;X2' write 1 from 'C;K"ACCNT"' write 1 from 'F;X3' write 1 from 'C;K"ACCTDESC"' write 1 from 'F;X4' write 1 from 'C;K"INVID"' write 1 from 'F;X5' write 1 from 'C;K"UOMSTD"' write 1 from 'F;X6' write 1 from 'C;K"UOM"' write 1 from 'F;X7' write 1 from 'C;K"ITEMDESC"' write 1 from 'F;X8' write 1 from 'C;K"COUNT"' write 1 from 'F;P35;FF2C;X9' write 1 from 'C;K"PUTWY"' write 1 from 'F;X10' write 1 from 'C;K"CNT_PTWY"' write 1 from 'F;P1;FI0C;X11' write 1 from 'C;K"QTY_PTWY"' write 1 from 'F;P35;FF2C;X12' write 1 from 'C;K"ISSUES"' write 1 from 'F;X13' write 1 from 'C;K"CNT_ISS"' write 1 from 'F;X14' write 1 from 'C;K"QTY_ISS"' write 1 from 'F;P35;FF2C;X15' write 1 from 'C;K"ADJ"' write 1 from 'F;X16' write 1 from 'C;K"CNT_ADJ"' write 1 from 'F;P7;FF2C;X17' write 1 from 'C;K"ADJ_DECR"' write 1 from 'F;X18' write 1 from 'C;K"ADJ_INCR"' write 1 from 'F;X19' write 1 from 'C;K"OTHER"' write 1 from 'F;X20' write 1 from 'C;K"CNT_OTHER"' write 1 from 'F;X21' write 1 from 'C;K"AGG_QTY"' write 1 from 'F;P35;FF2C;X22' write 1 from 'C;K"AGG_AMT"' write 1 from 'C;Y2;X1;K" "' END-PROCEDURE ! WRITE lines BEGIN-PROCEDURE WRITE-LINES let #Y = #Y + 1 move #Y to $Y move #COUNT to $COUNT move #PUTWY to $PUTWY move #CNT_PTWY to $CNT_PTWY move #QTY_PTWY to $QTY_PTWY move #ISSUES to $ISSUES move #CNT_ISS to $CNT_ISS move #QTY_ISS to $QTY_ISS move #ADJ to $ADJ move #CNT_ADJ to $CNT_ADJ move #ADJ_DECR to $ADJ_DECR move #ADJ_INCR to $ADJ_INCR move #OTHER to $OTHER move #CNT_OTHER to $CNT_OTHER let #this_item_qty = #QTY_PTWY - #QTY_ISS + #ADJ_DECR + #ADJ_INCR let #this_item_amt = #PUTWY + #ISSUES + #ADJ + #OTHER move #this_item_qty to $this_item_qty move #this_item_amt to $this_item_amt let #tot_COUNT = #tot_COUNT + #COUNT let #tot_PUTWY = #tot_PUTWY + #PUTWY let #tot_CNT_PTWY = #tot_CNT_PTWY + #CNT_PTWY let #tot_QTY_PTWY = #tot_QTY_PTWY + #QTY_PTWY let #tot_ISSUES = #tot_ISSUES + #ISSUES let #tot_CNT_ISS = #tot_CNT_ISS + #CNT_ISS let #tot_QTY_ISS = #tot_QTY_ISS + #QTY_ISS let #tot_ADJ = #tot_ADJ + #ADJ let #tot_CNT_ADJ = #tot_CNT_ADJ + #CNT_ADJ let #tot_ADJ_DECR = #tot_ADJ_DECR + #ADJ_DECR let #tot_ADJ_INCR = #tot_ADJ_INCR + #ADJ_INCR let #tot_OTHER = #tot_OTHER + #OTHER let #tot_CNT_OTHER = #tot_CNT_OTHER + #CNT_OTHER let #tot_item_qty = #tot_item_qty + #this_item_qty let #tot_item_amt = #tot_item_amt + #this_item_amt let $BUSUNIT_SLK = 'C;Y' || $Y || ';K"' || $BUSUNIT || '"' let $ACCNT_SLK = 'C;X2;' || $ACCNT let $ACCTDESC_SLK = 'C;X3;K"' || $ACCTDESC || '"' let $INVID_SLK = 'C;X4;K' || $INVID let $UOMSTD_SLK = 'C;X5;K"' || $UOMSTD || '"' let $UOMUOM_SLK = 'C;X6;K"' || $UOMUOM || '"' let $ITEMDESC_SLK = 'C;X7;K"' || $ITEMDESC || '"' let $COUNT_SLK = 'C;X8;K' || $COUNT let $PUTWY_SLK = 'C;X9;K' || $PUTWY let $CNT_PTWY_SLK = 'C;X10;K' || $CNT_PTWY let $QTY_PTWY_SLK = 'C;X11;K' || $QTY_PTWY let $ISSUES_SLK = 'C;X12;K' || $ISSUES let $CNT_ISS_SLK = 'C;X13;K' || $CNT_ISS let $QTY_ISS_SLK = 'C;X14;K' || $QTY_ISS let $ADJ_SLK = 'C;X15;K || $ADJ let $CNT_ADJ_SLK = 'C;X16;K' || $CNT_ADJ let $ADJ_DECR_SLK = 'C;X17;K' || $ADJ_DECR let $ADJ_INCR_SLK = 'C;X18;K' || $ADJ_INCR let $OTHER_SLK = 'C;X19;K' || $OTHER let $CNT_OTHER_SLK = 'C;X20;K' || $CNT_OTHER let $this_item_qty_SLK = 'C;X21;K' || $this_item_qty let $this_item_amt_SLK = 'C;X22;K' || $this_item_amt write 1 from $BUSUNIT_SLK write 1 from $ACCNT_SLK write 1 from $ACCTDESC_SLK write 1 from $INVID_SLK write 1 from $UOMSTD_SLK write 1 from $UOMUOM_SLK write 1 from $ITEMDESC_SLK write 1 from $COUNT_SLK write 1 from $PUTWY_SLK write 1 from $CNT_PTWY_SLK write 1 from $QTY_PTWY_SLK write 1 from $ISSUES_SLK write 1 from $CNT_ISS_SLK write 1 from $QTY_ISS_SLK write 1 from $ADJ_SLK write 1 from $CNT_ADJ_SLK write 1 from $ADJ_DECR_SLK write 1 from $ADJ_INCR_SLK write 1 from $OTHER_SLK write 1 from $CNT_OTHER_SLK write 1 from $this_item_qty_SLK write 1 from $this_item_amt_SLK END-PROCEDURE begin-procedure write-totals let #Y = #Y + 1 move #Y to $Y move #tot_COUNT to $tot_COUNT move #tot_PUTWY to $tot_PUTWY move #tot_CNT_PTWY to $tot_CNT_PTWY move #tot_QTY_PTWY to $tot_QTY_PTWY move #tot_ISSUES to $tot_ISSUES move #tot_CNT_ISS to $tot_CNT_ISS move #tot_QTY_ISS to $tot_QTY_ISS move #tot_ADJ to $tot_ADJ move #tot_CNT_ADJ to $tot_CNT_ADJ move #tot_ADJ_DECR to $tot_ADJ_DECR move #tot_ADJ_INCR to $tot_ADJ_INCR move #tot_OTHER to $tot_OTHER move #tot_CNT_OTHER to $tot_CNT_OTHER move #tot_item_qty to $tot_item_qty move #tot_item_amt to $tot_item_amt let $firsttotcell = 'C;Y' || Y$ || ';X1;K" "' let $secontotcell = 'C;X2;K" "' let $thirdtotcell = 'C;X3;K" "' let $fourttotcell = 'C;X4;K" "' let $fifthtotcell = 'C;X5;K" "' let $sixthtotcell = 'C;X6;K" "' let $seventotcell = 'C;X7;K" "' let $tot_COUNT_SLK = 'C;X8;K' || $tot_COUNT let $tot_PUTWY_SLK = 'C;X9;K' || $tot_PUTWY let $tot_CNT_PTWY_SLK = 'C;X10;K' || $tot_CNT_PTWY let $tot_QTY_PTWY_SLK = 'C;X11;K' || $tot_QTY_PTWY let $tot_ISSUES_SLK = 'C;X12;K' || $tot_ISSUES let $tot_CNT_ISS_SLK = 'C;X13;K' || $tot_CNT_ISS let $tot_QTY_ISS_SLK = 'C;X14;K' || $tot_QTY_ISS let $tot_ADJ_SLK = 'C;X15;K' || $tot_ADJ let $tot_CNT_ADJ_SLK = 'C;X16;K' || $tot_CNT_ADJ let $tot_ADJ_DECR_SLK = 'C;X17;K' || $tot_ADJ_DECR let $tot_ADJ_INCR_SLK = 'C;X18;K' || $tot_ADJ_INCR let $tot_OTHER_SLK = 'C;X19;K' || $tot_OTHER let $tot_CNT_OTHER_SLK = 'C;X20;K'|| $tot_CNT_OTHER let $tot_item_qty_SLK = 'C;X21;K' || $tot_item_qty let $tot_item_amt_SLK = 'C;X22;K' || $tot_item_amt write 1 from $firsttotcell write 1 from $secontotcell write 1 from $thirdtotcell write 1 from $fourttotcell write 1 from $fifthtotcell write 1 from $sixthtotcell write 1 from $seventotcell write 1 from $tot_COUNT_SLK write 1 from $tot_PUTWY_SLK write 1 from $tot_CNT_PTWY_SLK write 1 from $tot_QTY_PTWY_SLK write 1 from $tot_ISSUES_SLK write 1 from $tot_CNT_ISS_SLK write 1 from $tot_QTY_ISS_SLK write 1 from $tot_ADJ_SLK write 1 from $tot_CNT_ADJ_SLK write 1 from $tot_ADJ_DECR_SLK write 1 from $tot_ADJ_INCR_SLK write 1 from $tot_OTHER_SLK write 1 from $tot_CNT_OTHER_SLK write 1 from $tot_item_qty_SLK write 1 from $tot_item_amt_SLK write 1 from 'E' end-procedure -------------------------------------------------------------------- Emil Galicinao @list.iex.net> on 06/29/2001 > 06:27:01 AM > > Please respond to sqr-users@list.iex.net > > Sent by: "Discussion of SQR, Brio Technology's database > reporting language" > > To: SQR-USERS@list.iex.net > cc: > > Subject: How can SQR generate an excel file with numeric data being > displayed as a string? > > Hi Everyone, > > Does anybody know how to convert a numeric data into a string such that > when I generate the report in excel, that data will no longer be treated > as numeric? Right now, when a numeric data is more than 11 digits > long, excel usually convert the data into an exponential form which is > something Im trying to avoid. > > An example would be: > The number 123456789012 becomes 1.23457E+11 when viewed from excel. > > I was thinking of appending a special character on that numeric data but > unfortunately, that special charater also appear in the report. > > Any suggestions? > > Best Regards, > Emil ----------------------------------------------------- Confidentiality Notice: This e-mail transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the e-mail address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this e-mail is strictly prohibited. If you have received this e-mail transmission in error, please reply to the sender, so that we can arrange for proper delivery, and then please delete the message from your inbox. Thank you. From owner-sqr-users@list.iex.net Tue Jul 3 16:36:14 2001 Date: Tue, 3 Jul 2001 14:13:03 -0700 From: Michael Prodor Subject: SQR Tutorial Files Does anyone have the table load file for the SQR 4.x tutorial? We migrated from 3 to 4 a while back and now have the opportunity to use the tutorial, but our files are not compatible. Thanks in advance for your help! -- Michael Prodor Qualcomm - IT QWBS Business Systems From owner-sqr-users@list.iex.net Thu Jul 5 14:52:03 2001 Date: Thu, 5 Jul 2001 15:45:24 -0400 From: Melanie Hershey Subject: SQR 4.3.4 Error - Executing Cursors This is a repost about getting the "problem executing cursor" error. I've been through the archives and haven't found anything that sounds like a solution to my particular problem. Here's the message I previously posted: "In addition to doing a Psoft upgrade, we are also in the process of upgrading to SQR version 4.3.4 (from 3.0.7.3) and are encountering the following error message intermittently when running an SQR program on our unix server: (SQR 5528) INFORMIX SQL OPEN/EXECUTE error -404 (ISAM: 0) in cursor 11: The cursor or statement is not available. Error on line 375: (SQR 3723) Problem executing cursor. Line 375 starts the following select: begin-procedure Get-Voucher-Id #Debug display 'Getting Voucher Id' let $vchr_found = 'N' begin-select xref.voucher_id let $vchr_found = 'Y' let $voucher_id = &xref.voucher_id from [$cx_ap_xref] xref where xref.invoice_id = &ap.invoice_id and xref.origin = &ap.origin and xref.business_unit = &ap.business_unit and xref.batch_type = 'V' end-select This procedure is called for each line in another table. When running this select statement and plugging in the bind variables in an outside SQL session it runs fine. Also, the statement generally executes fine for the first call to the procedure and errors out on the second call. Another thing to note is that when we run the SQR on the client, there are NO problems. Does this ring any bells? Are there any other environment variables or configuration files that I should look at? Any advice or insights would be greatly appreciated. Thanks! Melanie Hershey Carmax Auto Superstores Financial Systems Development FYI - We are upgrading all of the following at this point: Unix OS from HPUX 10.2 to HPUX 11 Informix from 7.30.UC7 to 7.31.FS7 (64-bit) Informix Connectivity from I-Connect to SDK 2.40 HC1 PeopleTools from 7.03 to 7.07 PS App from 7.01 to 7.02 SQR from 3.0.7.3 to 4.3.4 Tuxedo from 6.3 to 6.5" ---------------- After more research, my I'm thinking that there is a difference between the client SQR executable and the UNIX SQR executable in the way SQR is translating the code to communicate with an Informix database. I read on an Informix website that this error will occur when you try to reuse a cursor that has had a CLOSE or FREE statement issued. Does anyone know how I could determine where this is happening? Is there an SQR trace that will show me the Informix commands being sent to the database? This has me completely puzzled, so any assistance would be appreciated.... Thanks! From owner-sqr-users@list.iex.net Thu Jul 5 15:15:46 2001 Date: Thu, 5 Jul 2001 16:11:50 -0400 From: Glenn Coulter Subject: Re: SQR 4.3.4 Error - Executing Cursors Hi Melanie, Is the calling procedure performing any commits between the first and second call? I have gotten similar messages when a commit slips into the logic, as that command closes all the open cursors. HTH... Glenn -----Original Message----- From: Melanie Hershey [mailto:MelanieHershey@CARMAX.COM] Sent: Thursday, July 05, 2001 3:45 PM To: SQR-USERS@list.iex.net Subject: SQR 4.3.4 Error - Executing Cursors This is a repost about getting the "problem executing cursor" error. I've been through the archives and haven't found anything that sounds like a solution to my particular problem. Here's the message I previously posted: "In addition to doing a Psoft upgrade, we are also in the process of upgrading to SQR version 4.3.4 (from 3.0.7.3) and are encountering the following error message intermittently when running an SQR program on our unix server: (SQR 5528) INFORMIX SQL OPEN/EXECUTE error -404 (ISAM: 0) in cursor 11: The cursor or statement is not available. Error on line 375: (SQR 3723) Problem executing cursor. Line 375 starts the following select: begin-procedure Get-Voucher-Id #Debug display 'Getting Voucher Id' let $vchr_found = 'N' begin-select xref.voucher_id let $vchr_found = 'Y' let $voucher_id = &xref.voucher_id from [$cx_ap_xref] xref where xref.invoice_id = &ap.invoice_id and xref.origin = &ap.origin and xref.business_unit = &ap.business_unit and xref.batch_type = 'V' end-select This procedure is called for each line in another table. When running this select statement and plugging in the bind variables in an outside SQL session it runs fine. Also, the statement generally executes fine for the first call to the procedure and errors out on the second call. Another thing to note is that when we run the SQR on the client, there are NO problems. Does this ring any bells? Are there any other environment variables or configuration files that I should look at? Any advice or insights would be greatly appreciated. Thanks! Melanie Hershey Carmax Auto Superstores Financial Systems Development FYI - We are upgrading all of the following at this point: Unix OS from HPUX 10.2 to HPUX 11 Informix from 7.30.UC7 to 7.31.FS7 (64-bit) Informix Connectivity from I-Connect to SDK 2.40 HC1 PeopleTools from 7.03 to 7.07 PS App from 7.01 to 7.02 SQR from 3.0.7.3 to 4.3.4 Tuxedo from 6.3 to 6.5" ---------------- After more research, my I'm thinking that there is a difference between the client SQR executable and the UNIX SQR executable in the way SQR is translating the code to communicate with an Informix database. I read on an Informix website that this error will occur when you try to reuse a cursor that has had a CLOSE or FREE statement issued. Does anyone know how I could determine where this is happening? Is there an SQR trace that will show me the Informix commands being sent to the database? This has me completely puzzled, so any assistance would be appreciated.... Thanks! From owner-sqr-users@list.iex.net Fri Jul 6 02:52:24 2001 Date: Fri, 6 Jul 2001 09:44:54 +0200 From: "J.-Bernard Favre" Subject: Re: SQR 4.3.4 Error - Executing Cursors I had a similar problem (yesterday), but not exactly the same error message. I added a 'NULL' column in the begin-select like: let $col_null='NULL' begin-select [$col_null] &colnul=char xref.voucher_id let $vchr_found = 'Y' let $voucher_id = &xref.voucher_id from [$cx_ap_xref] xref where xref.invoice_id = &ap.invoice_id and xref.origin = &ap.origin and xref.business_unit = &ap.business_unit and xref.batch_type = 'V' end-select I cannot guarantee it will work, you can always try. Jean-Bernard. Melanie Hershey wrote: > This is a repost about getting the "problem executing cursor" error. I've > been through the archives and haven't found anything that sounds like a > solution to my particular problem. > > Here's the message I previously posted: > > "In addition to doing a Psoft upgrade, we are also in the process of > upgrading to SQR version 4.3.4 (from 3.0.7.3) and are encountering the > following error message intermittently when running an SQR program on our > unix server: > > (SQR 5528) INFORMIX SQL OPEN/EXECUTE error -404 (ISAM: 0) in cursor 11: > The cursor or statement is not available. > Error on line 375: > (SQR 3723) Problem executing cursor. > > Line 375 starts the following select: > > begin-procedure Get-Voucher-Id > #Debug display 'Getting Voucher Id' > > let $vchr_found = 'N' > > begin-select > xref.voucher_id > > let $vchr_found = 'Y' > let $voucher_id = &xref.voucher_id > > from [$cx_ap_xref] xref > where xref.invoice_id = &ap.invoice_id and > xref.origin = &ap.origin and > xref.business_unit = &ap.business_unit and > xref.batch_type = 'V' > end-select > > This procedure is called for each line in another table. When running this > select statement and plugging in the bind variables in an outside SQL > session it runs fine. Also, the statement generally executes fine for the > first call to the procedure and errors out on the second call. Another > thing to note is that when we run the SQR on the client, there are NO > problems. Does this ring any bells? Are there any other environment > variables or configuration files that I should look at? > > Any advice or insights would be greatly appreciated. > Thanks! > Melanie Hershey > Carmax Auto Superstores > Financial Systems Development > > FYI - We are upgrading all of the following at this point: > Unix OS from HPUX 10.2 to HPUX 11 > Informix from 7.30.UC7 to 7.31.FS7 (64-bit) > Informix Connectivity from I-Connect to SDK 2.40 HC1 > PeopleTools from 7.03 to 7.07 > PS App from 7.01 to 7.02 > SQR from 3.0.7.3 to 4.3.4 > Tuxedo from 6.3 to 6.5" > ---------------- > After more research, my I'm thinking that there is a difference between the > client SQR executable and the UNIX SQR executable in the way SQR is > translating the code to communicate with an Informix database. I read on > an Informix website that this error will occur when you try to reuse a > cursor that has had a CLOSE or FREE statement issued. Does anyone know how > I could determine where this is happening? Is there an SQR trace that will > show me the Informix commands being sent to the database? This has me > completely puzzled, so any assistance would be appreciated.... > > Thanks! From owner-sqr-users@list.iex.net Thu Jul 5 16:25:04 2001 Date: Thu, 5 Jul 2001 14:07:58 -0700 From: Charllotte Hurt Subject: help with sql I am working with an sql statement which I need to modify in order to pick up additional information in another table. (The tables involved are from the PeopleSoft Student Administration product and this sql statement is a view.) The outcome I want from this is all the fields currently being selected; but, in addition, I need the descr254a field from the rq_grp_tbl for those courses which have a rqrmrnt_group and also those courses which don't have a rqrmnt_group. The sql below is the original with my additions in bold. I also have put crse_ids to simplify my testing, one has a rqrmnt_group and one does not. My problem is that I receive one row with all the necessary information for the course id which has the rqrmnt_group, but for the one which doesn't I am getting back every row from the rq_grp_tbl (because of my not exists). I only want one row returned for each course_id in my select. I am getting back 243 rows, one for the course with the rqrmnt_group and 242 rows ! for the one which has no rqrmnt_group (242 rows is the totat count of the rq_grp_tbl). I would really appreciate some help on this. Any suggestions? I need to do this in the sql--not in an sqr. (Sqr would allow loops=1). Thanks in advance for your assistance. SELECT A.CRSE_ID ,A.EFFDT ,B.INSTITUTION ,B.SUBJECT ,B.CATALOG_NBR ,A.EFF_STATUS ,A.UNITS_MINIMUM ,A.UNITS_MAXIMUM ,A.COMPONENT ,A.COURSE_TITLE_LONG ,C.DESCRFORMAL ,D.DESCR ,E.XLATLONGNAME ,A.DESCRLONG ,f.descr254a ,b.rqrmnt_group FROM PS_CRSE_CATALOG A, PS_CRSE_OFFER B, PS_SUBJECT_TBL C, PS_INSTITUTION_TBL D, XLATTABLE E, ps_rq_grp_tbl f WHERE A.CRSE_ID = B.CRSE_ID AND a.crse_id in ('001750','000322') and A.EFFDT = ( SELECT MAX(Z.EFFDT) FROM PS_CRSE_CATALOG Z WHERE A.CRSE_ID = Z.CRSE_ID AND Z.EFFDT <= sysdate) AND A.EFF_STATUS = 'A' AND A.EFFDT = B.EFFDT AND B.CATALOG_PRINT = 'Y' AND B.INSTITUTION = C.INSTITUTION AND C.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_SUBJECT_TBL X WHERE C.INSTITUTION = X.INSTITUTION AND C.SUBJECT = X.SUBJECT AND X.EFFDT <= sysdate) AND B.INSTITUTION = D.INSTITUTION AND D.EFFDT = ( SELECT MAX(Y.EFFDT) FROM PS_INSTITUTION_TBL Y WHERE D.INSTITUTION = Y.INSTITUTION AND Y.EFFDT <= sysdate) AND B.SUBJECT = C.SUBJECT AND B.INSTITUTION = D.INSTITUTION AND E.FIELDNAME = 'COMPONENT' AND E.LANGUAGE_CD = (SELECT LANGUAGE_CD FROM PSOPTIONS) AND A.COMPONENT = E.FIELDVALUE AND E.EFFDT = ( SELECT MAX(W.EFFDT) FROM XLATTABLE W WHERE E.FIELDNAME = W.FIELDNAME AND E.LANGUAGE_CD = W.LANGUAGE_CD AND E.FIELDVALUE = W.FIELDVALUE AND W.EFFDT <= sysdate) and (b.rqrmnt_group = f.rqrmnt_group and f.effdt = ( SELECT MAX(f1.EFFDT) FROM ps_rq_grp_tbl f1 WHERE f1.rqrmnt_group = f.rqrmnt_group AND f1.EFFDT <= sysdate) and f.eff_status = 'A' or (not exists (select 'x' from ps_rq_grp_tbl f2 where f2.rqrmnt_group = b.rqrmnt_group))); From owner-sqr-users@list.iex.net Fri Jul 6 06:45:50 2001 Date: Fri, 6 Jul 2001 04:40:42 -0700 From: Arjan Hoornstra Subject: Re: help with sql Charlotte, You problem seems to be you 'f' table. This table is not joined because of the or clause, causing a carthesian product. Look at this part (I edited the layout for readability): and ( b.rqrmnt_group = f.rqrmnt_group and f.effdt = ( SELECT MAX(f1.EFFDT) FROM ps_rq_grp_tbl f1 WHERE f1.rqrmnt_group = f.rqrmnt_group AND f1.EFFDT <= sysdate ) and f.eff_status = 'A' or ( not exists (select 'x' from ps_rq_grp_tbl f2 where f2.rqrmnt_group = b.rqrmnt_group ) ) ) Because the 'or' is on the same level as the 'and' (twice) and the join it overrules everything. So you would have to use an outerjoin, but because of the 'max' clause in the query and the descr254a column in the select that is not possible. Depending on your database system I think the only way to solve this is create a temporary table in which you insert the contents of table f with only the max(EFFDT) of each group, and then you can use the outerjoin... Something like: select 'EFFDT' = MAX(f1.EFFDT), rqrmnt_group, eff_status, descr254a into #ps_rq_grp_tbl from ps_rq_grp_tbl where EFFDT <= sysdate and eff_status = 'A' group by rqrmnt_group, eff_status, descr254a (eff_status is optional if you only need the 'A' values) Now in your query replace ps_rq_grp_tbl with #ps_rq_grp_tbl as your 'f' table in the FROM clause, and change the join to: and b.rqrmnt_group *= f.rqrmnt_group and f.eff_status = 'A' This should work for Sybase and MS-sql, for Oracle you may need a workaround for the #table. Hope this helps, let me know... Arjan --- Charllotte Hurt wrote: > I am working with an sql statement which I need to modify in order to > pick up additional information in another table. (The tables > involved are from the PeopleSoft Student Administration product and > this sql statement is a view.) The outcome I want from this is all > the fields currently being selected; but, in addition, I need the > descr254a field from the rq_grp_tbl for those courses which have a > rqrmrnt_group and also those courses which don't have a rqrmnt_group. > The sql below is the original with my additions in bold. I also > have put crse_ids to simplify my testing, one has a rqrmnt_group and > one does not. My problem is that I receive one row with all the > necessary information for the course id which has the rqrmnt_group, > but for the one which doesn't I am getting back every row from the > rq_grp_tbl (because of my not exists). I only want one row returned > for each course_id in my select. I am getting back 243 rows, one for > the course with the rqrmnt_group and 242 rows ! > for the one which has no rqrmnt_group (242 rows is the totat count of > the rq_grp_tbl). > > I would really appreciate some help on this. Any suggestions? I > need to do this in the sql--not in an sqr. (Sqr would allow > loops=1). > > Thanks in advance for your assistance. > > SELECT > A.CRSE_ID > ,A.EFFDT > ,B.INSTITUTION > ,B.SUBJECT > ,B.CATALOG_NBR > ,A.EFF_STATUS > ,A.UNITS_MINIMUM > ,A.UNITS_MAXIMUM > ,A.COMPONENT > ,A.COURSE_TITLE_LONG > ,C.DESCRFORMAL > ,D.DESCR > ,E.XLATLONGNAME > ,A.DESCRLONG > ,f.descr254a > ,b.rqrmnt_group > FROM > PS_CRSE_CATALOG A, > PS_CRSE_OFFER B, > PS_SUBJECT_TBL C, > PS_INSTITUTION_TBL D, > XLATTABLE E, > ps_rq_grp_tbl f > WHERE A.CRSE_ID = B.CRSE_ID AND > a.crse_id in ('001750','000322') and > A.EFFDT = > ( SELECT MAX(Z.EFFDT) > FROM PS_CRSE_CATALOG Z > WHERE A.CRSE_ID = Z.CRSE_ID > AND Z.EFFDT <= sysdate) > AND > A.EFF_STATUS = 'A' AND > A.EFFDT = B.EFFDT AND > B.CATALOG_PRINT = 'Y' AND > B.INSTITUTION = C.INSTITUTION AND > C.EFFDT = > ( SELECT MAX(X.EFFDT) > FROM PS_SUBJECT_TBL X > WHERE C.INSTITUTION = X.INSTITUTION > AND C.SUBJECT = X.SUBJECT > AND X.EFFDT <= sysdate) AND > B.INSTITUTION = D.INSTITUTION AND > D.EFFDT = > ( SELECT MAX(Y.EFFDT) > FROM PS_INSTITUTION_TBL Y > WHERE D.INSTITUTION = Y.INSTITUTION > AND Y.EFFDT <= sysdate) AND > B.SUBJECT = C.SUBJECT AND > B.INSTITUTION = D.INSTITUTION AND > E.FIELDNAME = 'COMPONENT' AND > E.LANGUAGE_CD = > (SELECT LANGUAGE_CD FROM PSOPTIONS) > AND > A.COMPONENT = E.FIELDVALUE AND > E.EFFDT = > ( SELECT MAX(W.EFFDT) > FROM XLATTABLE W > WHERE E.FIELDNAME = W.FIELDNAME > AND E.LANGUAGE_CD = W.LANGUAGE_CD > AND E.FIELDVALUE = W.FIELDVALUE > AND W.EFFDT <= sysdate) > and (b.rqrmnt_group = f.rqrmnt_group > and f.effdt = > ( SELECT MAX(f1.EFFDT) > FROM ps_rq_grp_tbl f1 > WHERE f1.rqrmnt_group = f.rqrmnt_group > AND f1.EFFDT <= sysdate) > and f.eff_status = 'A' or > (not exists (select 'x' from ps_rq_grp_tbl f2 > where f2.rqrmnt_group = b.rqrmnt_group))); __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ From owner-sqr-users@list.iex.net Fri Jul 6 08:03:53 2001 Date: Fri, 6 Jul 2001 05:49:23 -0700 From: Michael Nielsen Subject: DECLARING A VARIABLE Currently my report is printing variables as a decimal. For example: 64.0 My report needs to print this value simply as: 64 How do I set my variables in the SQR Workbench? I am not sure what the code needs to look like to make my numbers go from decimals to integers. Thank You Michael Nielsen ___________________________________________________ GO.com Mail Get Your Free, Private E-mail at http://mail.go.com From owner-sqr-users@list.iex.net Fri Jul 6 08:07:39 2001 Date: Fri, 6 Jul 2001 09:03:59 -0400 From: Mario Mendoza Subject: Re: DECLARING A VARIABLE Use an edit mask let $var_a = edit(&var_a,'9,999') Mario Mendoza 1-888-647-6472 x633 mariom@flagshipcredit.com > -----Original Message----- > From: Michael Nielsen [SMTP:mnielsen33@GO.COM] > Sent: July 06, 2001 8:49 AM > To: SQR-USERS@list.iex.net > Subject: DECLARING A VARIABLE > > Currently my report is printing variables as a decimal. For example: > > 64.0 > > My report needs to print this value simply as: > > 64 > > How do I set my variables in the SQR Workbench? I am not sure what the > code needs to look like to make my numbers go from decimals to integers. > > Thank You > > Michael Nielsen > > ___________________________________________________ > GO.com Mail > Get Your Free, Private E-mail at http://mail.go.com From owner-sqr-users@list.iex.net Fri Jul 6 10:40:47 2001 Date: Fri, 6 Jul 2001 08:35:44 -0700 From: Chris Ottens Subject: Re: DECLARING A VARIABLE Or do it right from the print statement: print &var_a (1,1) edit 9,999 ----- Original Message ----- From: "Mario Mendoza" To: Sent: Friday, July 06, 2001 6:03 AM Subject: Re: DECLARING A VARIABLE > Use an edit mask > > let $var_a = edit(&var_a,'9,999') > > Mario Mendoza > 1-888-647-6472 x633 > mariom@flagshipcredit.com > > > -----Original Message----- > > From: Michael Nielsen [SMTP:mnielsen33@GO.COM] > > Sent: July 06, 2001 8:49 AM > > To: SQR-USERS@list.iex.net > > Subject: DECLARING A VARIABLE > > > > Currently my report is printing variables as a decimal. For example: > > > > 64.0 > > > > My report needs to print this value simply as: > > > > 64 > > > > How do I set my variables in the SQR Workbench? I am not sure what the > > code needs to look like to make my numbers go from decimals to integers. > > > > Thank You > > > > Michael Nielsen > > > > ___________________________________________________ > > GO.com Mail > > Get Your Free, Private E-mail at http://mail.go.com From owner-sqr-users@list.iex.net Fri Jul 6 08:59:22 2001 Date: Fri, 6 Jul 2001 06:54:45 -0700 From: Debbie Pavlak Subject: help with sql Charllotte, Why don't you try using an outer join on rq_grp_tbl? Something like this: ............. and (b.rqrmnt_group *= f.rqrmnt_group and f.effdt = ( SELECT MAX(f1.EFFDT) FROM ps_rq_grp_tbl f1 WHERE f1.rqrmnt_group = f.rqrmnt_group AND f1.EFFDT <= sysdate and f.eff_status = 'A')); Debbie > > Date: Thu, 5 Jul 2001 14:07:58 -0700 > From: Charllotte Hurt > Subject: help with sql > > I am working with an sql statement which I need to modify in order to pick up additional information in another table. (The tables involved are from the PeopleSoft Student Administration product and this sql statement is a view.) The outcome I want from this is all the fields currently being selected; but, in addition, I need the descr254a field from the rq_grp_tbl for those courses which have a rqrmrnt_group and also those courses which don't have a rqrmnt_group. The sql below is the original with my additions in bold. I also have put crse_ids to simplify my testing, one has a rqrmnt_group and one does not. My problem is that I receive one row with all the necessary information for the course id which has the rqrmnt_group, but for the one which doesn't I am getting back every row from the rq_grp_tbl (because of my not exists). I only want one row returned for each course_id in my select. I am getting back 243 rows, one for the course with the rqrmnt_group and 242 rows ! > for the one which has no rqrmnt_group (242 rows is the totat count of the rq_grp_tbl). > > I would really appreciate some help on this. Any suggestions? I need to do this in the sql--not in an sqr. (Sqr would allow loops=1). > > Thanks in advance for your assistance. > > SELECT > A.CRSE_ID > ,A.EFFDT > ,B.INSTITUTION > ,B.SUBJECT > ,B.CATALOG_NBR > ,A.EFF_STATUS > ,A.UNITS_MINIMUM > ,A.UNITS_MAXIMUM > ,A.COMPONENT > ,A.COURSE_TITLE_LONG > ,C.DESCRFORMAL > ,D.DESCR > ,E.XLATLONGNAME > ,A.DESCRLONG > ,f.descr254a > ,b.rqrmnt_group > FROM > PS_CRSE_CATALOG A, > PS_CRSE_OFFER B, > PS_SUBJECT_TBL C, > PS_INSTITUTION_TBL D, > XLATTABLE E, > ps_rq_grp_tbl f > WHERE A.CRSE_ID = B.CRSE_ID AND > a.crse_id in ('001750','000322') and > A.EFFDT = > ( SELECT MAX(Z.EFFDT) > FROM PS_CRSE_CATALOG Z > WHERE A.CRSE_ID = Z.CRSE_ID > AND Z.EFFDT <= sysdate) > AND > A.EFF_STATUS = 'A' AND > A.EFFDT = B.EFFDT AND > B.CATALOG_PRINT = 'Y' AND > B.INSTITUTION = C.INSTITUTION AND > C.EFFDT = > ( SELECT MAX(X.EFFDT) > FROM PS_SUBJECT_TBL X > WHERE C.INSTITUTION = X.INSTITUTION > AND C.SUBJECT = X.SUBJECT > AND X.EFFDT <= sysdate) AND > B.INSTITUTION = D.INSTITUTION AND > D.EFFDT = > ( SELECT MAX(Y.EFFDT) > FROM PS_INSTITUTION_TBL Y > WHERE D.INSTITUTION = Y.INSTITUTION > AND Y.EFFDT <= sysdate) AND > B.SUBJECT = C.SUBJECT AND > B.INSTITUTION = D.INSTITUTION AND > E.FIELDNAME = 'COMPONENT' AND > E.LANGUAGE_CD = > (SELECT LANGUAGE_CD FROM PSOPTIONS) > AND > A.COMPONENT = E.FIELDVALUE AND > E.EFFDT = > ( SELECT MAX(W.EFFDT) > FROM XLATTABLE W > WHERE E.FIELDNAME = W.FIELDNAME > AND E.LANGUAGE_CD = W.LANGUAGE_CD > AND E.FIELDVALUE = W.FIELDVALUE > AND W.EFFDT <= sysdate) > and (b.rqrmnt_group = f.rqrmnt_group > and f.effdt = > ( SELECT MAX(f1.EFFDT) > FROM ps_rq_grp_tbl f1 > WHERE f1.rqrmnt_group = f.rqrmnt_group > AND f1.EFFDT <= sysdate) > and f.eff_status = 'A' or > (not exists (select 'x' from ps_rq_grp_tbl f2 > where f2.rqrmnt_group = b.rqrmnt_group))); > > ------------------------------ _______________________________________________________ Send a cool gift with your E-Card http://www.bluemountain.com/giftcenter/ From owner-sqr-users@list.iex.net Fri Jul 6 10:00:13 2001 Date: Fri, 6 Jul 2001 07:57:38 -0700 From: Charllotte Hurt Subject: Re: help with sql Thanks for responding. I'll try your suggestion and try to get back to you. >>> dpavlak@EXCITE.COM 07/06/01 06:54AM >>> Charllotte, Why don't you try using an outer join on rq_grp_tbl? Something like this: ............. and (b.rqrmnt_group *= f.rqrmnt_group and f.effdt = ( SELECT MAX(f1.EFFDT) FROM ps_rq_grp_tbl f1 WHERE f1.rqrmnt_group = f.rqrmnt_group AND f1.EFFDT <= sysdate and f.eff_status = 'A')); Debbie > > Date: Thu, 5 Jul 2001 14:07:58 -0700 > From: Charllotte Hurt > Subject: help with sql > > I am working with an sql statement which I need to modify in order to pick up additional information in another table. (The tables involved are from the PeopleSoft Student Administration product and this sql statement is a view.) The outcome I want from this is all the fields currently being selected; but, in addition, I need the descr254a field from the rq_grp_tbl for those courses which have a rqrmrnt_group and also those courses which don't have a rqrmnt_group. The sql below is the original with my additions in bold. I also have put crse_ids to simplify my testing, one has a rqrmnt_group and one does not. My problem is that I receive one row with all the necessary information for the course id which has the rqrmnt_group, but for the one which doesn't I am getting back every row from the rq_grp_tbl (because of my not exists). I only want one row returned for each course_id in my select. I am getting back 243 rows, one for the course with the rqrmnt_group and 242 rows ! > for the one which has no rqrmnt_group (242 rows is the totat count of the rq_grp_tbl). > > I would really appreciate some help on this. Any suggestions? I need to do this in the sql--not in an sqr. (Sqr would allow loops=1). > > Thanks in advance for your assistance. > > SELECT > A.CRSE_ID > ,A.EFFDT > ,B.INSTITUTION > ,B.SUBJECT > ,B.CATALOG_NBR > ,A.EFF_STATUS > ,A.UNITS_MINIMUM > ,A.UNITS_MAXIMUM > ,A.COMPONENT > ,A.COURSE_TITLE_LONG > ,C.DESCRFORMAL > ,D.DESCR > ,E.XLATLONGNAME > ,A.DESCRLONG > ,f.descr254a > ,b.rqrmnt_group > FROM > PS_CRSE_CATALOG A, > PS_CRSE_OFFER B, > PS_SUBJECT_TBL C, > PS_INSTITUTION_TBL D, > XLATTABLE E, > ps_rq_grp_tbl f > WHERE A.CRSE_ID = B.CRSE_ID AND > a.crse_id in ('001750','000322') and > A.EFFDT = > ( SELECT MAX(Z.EFFDT) > FROM PS_CRSE_CATALOG Z > WHERE A.CRSE_ID = Z.CRSE_ID > AND Z.EFFDT <= sysdate) > AND > A.EFF_STATUS = 'A' AND > A.EFFDT = B.EFFDT AND > B.CATALOG_PRINT = 'Y' AND > B.INSTITUTION = C.INSTITUTION AND > C.EFFDT = > ( SELECT MAX(X.EFFDT) > FROM PS_SUBJECT_TBL X > WHERE C.INSTITUTION = X.INSTITUTION > AND C.SUBJECT = X.SUBJECT > AND X.EFFDT <= sysdate) AND > B.INSTITUTION = D.INSTITUTION AND > D.EFFDT = > ( SELECT MAX(Y.EFFDT) > FROM PS_INSTITUTION_TBL Y > WHERE D.INSTITUTION = Y.INSTITUTION > AND Y.EFFDT <= sysdate) AND > B.SUBJECT = C.SUBJECT AND > B.INSTITUTION = D.INSTITUTION AND > E.FIELDNAME = 'COMPONENT' AND > E.LANGUAGE_CD = > (SELECT LANGUAGE_CD FROM PSOPTIONS) > AND > A.COMPONENT = E.FIELDVALUE AND > E.EFFDT = > ( SELECT MAX(W.EFFDT) > FROM XLATTABLE W > WHERE E.FIELDNAME = W.FIELDNAME > AND E.LANGUAGE_CD = W.LANGUAGE_CD > AND E.FIELDVALUE = W.FIELDVALUE > AND W.EFFDT <= sysdate) > and (b.rqrmnt_group = f.rqrmnt_group > and f.effdt = > ( SELECT MAX(f1.EFFDT) > FROM ps_rq_grp_tbl f1 > WHERE f1.rqrmnt_group = f.rqrmnt_group > AND f1.EFFDT <= sysdate) > and f.eff_status = 'A' or > (not exists (select 'x' from ps_rq_grp_tbl f2 > where f2.rqrmnt_group = b.rqrmnt_group))); > > ------------------------------ _______________________________________________________ Send a cool gift with your E-Card http://www.bluemountain.com/giftcenter/ From owner-sqr-users@list.iex.net Fri Jul 6 10:57:24 2001 Date: Fri, 6 Jul 2001 08:54:40 -0700 From: Charllotte Hurt Subject: I believe I tried a union with no success. I received an error because of the descrlong field, whic I believe I tried a union with no success. I received an error because of the descrlong field, which I do need to as part of my select. Apparetnly union and descrlong don't mix. >>> Arjan Hoornstra 07/06/01 08:37AM >>> Hi Charlotte, The problem is that you have a few things to consider: - you want to see all rows of table B whether they join with F or not. That sounds like an outerjoin. - you want to select a column from the outer-table (f). Does not have to be a problem. - you want to use a max(date). Sounds like a 'group by', but if you use it in a subquery it has to be correlated (inner join). - you have additional statements for the outer table. So, if you would use the outer join you can not use the max function. The other solution given (by Debbie Pavlak) will give you the error like 'You can not use a inner join on a table that is also in an outer join'. Because you have to decide which it is... It's a common problem but you need a work around... There is however one other possibility, and that is the UNION statement. (ansi sql, works for every database platform) Then it looks like this in pseudo code: select everything, f.descr254a from tables a,b,c,d,e and f where clause and b.rqrmnt_group = f.rqrmnt_group ! first the join part and f.effdt = ( SELECT MAX(f1.EFFDT) FROM ps_rq_grp_tbl f1 WHERE f1.rqrmnt_group = f.rqrmnt_group AND f1.EFFDT <= sysdate ) and f.eff_status = 'A' UNION select everything, null ! now the others where there is no row in f from tables a,b,c,d,e where clause and not exists (select 'x' from ps_rq_grp_tbland f2 where f2.rqrmnt_group = b.rqrmnt_group ! index on f2.rqrmnt_group? ) But performance wise, depends on the number of rows in the 6 tables but I would try the #table.... Good luck Arjan PS Why not by the sqr-usergroup? --- Charllotte Hurt wrote: > Thanks for responding. I will consider your suggestion, but I was > hoping that I could accomplish what I needed with mods only to this > sql. Maybe that isn't possible? > __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ From owner-sqr-users@list.iex.net Fri Jul 6 11:11:00 2001 Date: Fri, 6 Jul 2001 09:06:36 -0700 From: Arjan Hoornstra Subject: Re: I believe I tried a union with no success. I received an error because of the descrlong field, whic Well, for using a union you have to make sure that you have the exact same number of clumns in your select list... in the same order and the same datatype. That's why I added the 'null' in the second select. Other than that I can;t think of anything why you would get an error (it's not a special column, is it?) for a descrlong except I see that it is the column right before the descr254a column.... But maybe you can copy the errormessage(s)? They may help... if it doesn't work like this. Arjan --- Charllotte Hurt wrote: > I believe I tried a union with no success. I received an error > because of the descrlong field, which I do need to as part of my > select. Apparetnly union and descrlong don't mix. > > >>> Arjan Hoornstra 07/06/01 08:37AM >>> > Hi Charlotte, > > The problem is that you have a few things to consider: > - you want to see all rows of table B whether they join with F or > not. > That sounds like an outerjoin. > - you want to select a column from the outer-table (f). Does not have > to be a problem. > - you want to use a max(date). Sounds like a 'group by', but if you > use > it in a subquery it has to be correlated (inner join). > - you have additional statements for the outer table. > > So, if you would use the outer join you can not use the max function. > The other solution given (by Debbie Pavlak) will give you the error > like 'You can not use a inner join on a table that is also in an > outer > join'. Because you have to decide which it is... > It's a common problem but you need a work around... > > There is however one other possibility, and that is the UNION > statement. > (ansi sql, works for every database platform) > > Then it looks like this in pseudo code: > > select everything, f.descr254a > from tables a,b,c,d,e and f > where clause > and b.rqrmnt_group = f.rqrmnt_group ! first the join part > and f.effdt = ( SELECT MAX(f1.EFFDT) > FROM ps_rq_grp_tbl f1 > WHERE f1.rqrmnt_group = f.rqrmnt_group > AND f1.EFFDT <= sysdate > ) > and f.eff_status = 'A' > UNION > select everything, null ! now the others where there is no row in > f > from tables a,b,c,d,e > where clause > and not exists (select 'x' > from ps_rq_grp_tbland f2 > where f2.rqrmnt_group = b.rqrmnt_group ! index on > f2.rqrmnt_group? > ) > > But performance wise, depends on the number of rows in the 6 tables > but > I would try the #table.... > > Good luck > > Arjan > > PS Why not by the sqr-usergroup? > > --- Charllotte Hurt wrote: > > Thanks for responding. I will consider your suggestion, but I was > > hoping that I could accomplish what I needed with mods only to this > > sql. Maybe that isn't possible? > > > > > > > > > __________________________________________________ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail > http://personal.mail.yahoo.com/ __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ From owner-sqr-users@list.iex.net Fri Jul 6 11:16:40 2001 Date: Fri, 6 Jul 2001 12:13:43 -0400 From: "Love, Kristin" Subject: Re: I believe I tried a union with no success. I received an err or because of the descrlong field, whic Sometimes you have more luck if you put the descrlong after all the other columns. I know it sounds unscientific, but I've seen it work (when using PeopleSoft's query builder) But DO double check your column order and data types. From owner-sqr-users@list.iex.net Fri Jul 6 11:21:21 2001 Date: Fri, 6 Jul 2001 09:18:53 -0700 From: Charllotte Hurt Subject: descrlong I will have to assemble my code again and try it to get the exact error. Descrlong is described as type LONG. Thanks for your responses. I'll get back to you after I try the union again. >>> ahoor@YAHOO.COM 07/06/01 09:06AM >>> Well, for using a union you have to make sure that you have the exact same number of clumns in your select list... in the same order and the same datatype. That's why I added the 'null' in the second select. Other than that I can;t think of anything why you would get an error (it's not a special column, is it?) for a descrlong except I see that it is the column right before the descr254a column.... But maybe you can copy the errormessage(s)? They may help... if it doesn't work like this. Arjan --- Charllotte Hurt wrote: > I believe I tried a union with no success. I received an error > because of the descrlong field, which I do need to as part of my > select. Apparetnly union and descrlong don't mix. > > >>> Arjan Hoornstra 07/06/01 08:37AM >>> > Hi Charlotte, > > The problem is that you have a few things to consider: > - you want to see all rows of table B whether they join with F or > not. > That sounds like an outerjoin. > - you want to select a column from the outer-table (f). Does not have > to be a problem. > - you want to use a max(date). Sounds like a 'group by', but if you > use > it in a subquery it has to be correlated (inner join). > - you have additional statements for the outer table. > > So, if you would use the outer join you can not use the max function. > The other solution given (by Debbie Pavlak) will give you the error > like 'You can not use a inner join on a table that is also in an > outer > join'. Because you have to decide which it is... > It's a common problem but you need a work around... > > There is however one other possibility, and that is the UNION > statement. > (ansi sql, works for every database platform) > > Then it looks like this in pseudo code: > > select everything, f.descr254a > from tables a,b,c,d,e and f > where clause > and b.rqrmnt_group = f.rqrmnt_group ! first the join part > and f.effdt = ( SELECT MAX(f1.EFFDT) > FROM ps_rq_grp_tbl f1 > WHERE f1.rqrmnt_group = f.rqrmnt_group > AND f1.EFFDT <= sysdate > ) > and f.eff_status = 'A' > UNION > select everything, null ! now the others where there is no row in > f > from tables a,b,c,d,e > where clause > and not exists (select 'x' > from ps_rq_grp_tbland f2 > where f2.rqrmnt_group = b.rqrmnt_group ! index on > f2.rqrmnt_group? > ) > > But performance wise, depends on the number of rows in the 6 tables > but > I would try the #table.... > > Good luck > > Arjan > > PS Why not by the sqr-usergroup? > > --- Charllotte Hurt wrote: > > Thanks for responding. I will consider your suggestion, but I was > > hoping that I could accomplish what I needed with mods only to this > > sql. Maybe that isn't possible? > > > > > > > > > __________________________________________________ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail > http://personal.mail.yahoo.com/ __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ From owner-sqr-users@list.iex.net Fri Jul 6 11:47:41 2001 Date: Fri, 6 Jul 2001 12:45:35 -0400 From: Philippe Godin Subject: Very important message... I hoping to get as many anwser as possible plz Good day everyone, We need to get as many replies as we can get from any of you who uses MRO's GMAO software called MAXIMO. During our development process of SQR reports using BRIO, we've encountered some severe problems and would like to know if any other users had the same and if so, how they solve it. When using BRIO to develop reports as we use to, there's is no problem at all. We can run them and use the viewer to see the results, we can even compile them without any errors generated. However, we cannot run the report under Oracle and Maximo 4i 4.1. After analysis, we've discovered that we can't use any special caracters such as é, è, û, °, ~, à, etc... in our code or otherwise we get an SQR 4747 error saying "The SQT file is corrupted,". This error is generated if any of these caracters are somehow in the code. As an example, we can't even use simple statements such as "Let $myvar = 'année'". This is quite a problem because we need to accents in our columns labels, report title, etc. when developping french versions of our reports. Moreover, on MS SQL Server 7.0 and Maximo 4i 4.1 it gets even more complex. Again, running the reports and compiling works just fine but running them from Maximo causes a Dr. Watson error. The error tells us that there's an access violation somewhere in memory... We've made several tests and everyhing seems to point out on one thing. We cannot use Begin-select statements otherwise the error is generated. Per example, the following piece of code alone doesn't even works: Begin-program begin-select wonum from workorder end-select end-program We were wondering if any of you had this problem and would like to help out finding a proper solution. Thanks to all for your support Philippe From owner-sqr-users@list.iex.net Fri Jul 6 18:22:07 2001 Date: Fri, 6 Jul 2001 10:52:12 -0700 From: Ben Le Subject: TNS problem-question Once in a while the following abort happens when we run the SQR program at the Unix prompt: "(SQR 5528) ORACLE OCILogon error 12154 in cursor 0: ORA-12154: TNS:could not resolve service name (SQR 4701) Cannot logon to the database. SQR: Program Aborting." Then next day runs fine. Does any body encounters this problem? How do you fix it? Thanks in advance. Oracle 8.1.5 Hp-Ux 11.1 SQR 6.0 for Unix SCT Banner 4.x -------------------------------------------------------------------------------------------------------------------------------- Benjamin Le ITS, System Development Portland Community College 12000 SW 49th Ave. Portland, Oregon 97219 (503)-977-4970 Fax: (503)-977-4987 Mailto:ble@pcc.edu http://www.pcc.edu -------------------------------------------------------------------------------------------------------------------------------- From owner-sqr-users@list.iex.net Mon Jul 9 10:36:23 2001 Date: Mon, 9 Jul 2001 11:14:55 -0400 From: Wendy Biggs Subject: Re: TNS problem-question Ben, When *I* get "TNS:could not resolve service name" errors, it means that I misspelled the sid. I'm not sure how your program is logging your/a userid into the oracle instance and so I can't say that your problem is the same... but essentially Oracle is saying "I can't find an instance with that name in the TNSNAMES file... it is possible but not likely that someone is fiddling with the TNSNAMES file but more likely a misspelling or missing link. Are you familiar with the TNSNAMES.ORA file? --wb -- "It should be challenging, but not impossible, to come up with an acceptable password." Karen Herrington - New criteria for Oracle passwords Ben Le wrote: > > Once in a while the following abort happens when we run the SQR program at > the Unix prompt: > "(SQR 5528) ORACLE OCILogon error 12154 in cursor 0: > ORA-12154: TNS:could not resolve service name > (SQR 4701) Cannot logon to the database. > SQR: Program Aborting." > > Then next day runs fine. Does any body encounters this problem? How do you > fix it? Thanks in advance. > > Oracle 8.1.5 > Hp-Ux 11.1 > SQR 6.0 for Unix > SCT Banner 4.x > > -------------------------------------------------------------------------------------------------------------------------------- > Benjamin Le > ITS, System Development > Portland Community College > 12000 SW 49th Ave. Portland, Oregon 97219 > (503)-977-4970 Fax: (503)-977-4987 > Mailto:ble@pcc.edu http://www.pcc.edu > -------------------------------------------------------------------------------------------------------------------------------- From owner-sqr-users@list.iex.net Wed Jul 11 17:04:03 2001 Date: Wed, 11 Jul 2001 17:59:53 -0400 From: Wendy Biggs Subject: Oracle Forms question If anyone is using Oracle Forms and can help me with populating a pull-down list, could you please contact me off-list at wendy.biggs@vt.edu. Or knows of an O Forms userlist? thanks, --wb -- "It should be challenging, but not impossible, to come up with an acceptable password." Karen Herrington - New criteria for Oracle passwords From owner-sqr-users@list.iex.net Tue Jul 10 02:07:00 2001 Date: Tue, 10 Jul 2001 13:50:11 +0700 From: Naiyanad Ohpanayikool Subject: Date format when retrieving from the database is wrong The date format which is kept in the database is YYYY i.e. 1977. But when I code SQR to retrieve the date field, and print out on the report, using print $date () Edit 'DD/MM/YYYY' The result come out on the report is 2077, not 1977. How can I set SQR to know that the date is in year 19xx not 20xx? BRIO Report Builder 6.1.3 SQR 6.0 ORACLE 8i Windows NT 4.0 Best regards, Nai ______________________________ Naiyanad Ohpanayikool Microsoft Solution Organisation, Accenture Bangkok Tel: +662 636-1616 This message is for the designated recipient only and may contain privileged or confidential information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. From owner-sqr-users@list.iex.net Thu Jul 12 11:33:40 2001 Date: Thu, 12 Jul 2001 12:24:45 -0400 From: "Risman, Mark" Subject: Re: Date format when retrieving from the database is wrong If you are getting 2077 here it is because your $date variable says 2077. Check to see where $date is set. - Mark -----Original Message----- From: Naiyanad Ohpanayikool [mailto:naiyanad.ohpanayikool@ACCENTURE.COM] Sent: Tuesday, July 10, 2001 2:50 AM To: SQR-USERS@list.iex.net Subject: Date format when retrieving from the database is wrong The date format which is kept in the database is YYYY i.e. 1977. But when I code SQR to retrieve the date field, and print out on the report, using print $date () Edit 'DD/MM/YYYY' The result come out on the report is 2077, not 1977. How can I set SQR to know that the date is in year 19xx not 20xx? BRIO Report Builder 6.1.3 SQR 6.0 ORACLE 8i Windows NT 4.0 Best regards, Nai ______________________________ Naiyanad Ohpanayikool Microsoft Solution Organisation, Accenture Bangkok Tel: +662 636-1616 This message is for the designated recipient only and may contain privileged or confidential information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. Visit our website at http://www.ubswarburg.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. This message is provided for informational purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments. From owner-sqr-users@list.iex.net Wed Jul 11 09:38:00 2001 Date: Wed, 11 Jul 2001 07:31:38 -0700 From: Michael Nielsen Subject: Exporting SQR into Microsoft Excel Hello everyone: Is their a way I can export information from SQR into a microsoft excel spreadsheet? Thank You Mike Nielsen ___________________________________________________ GO.com Mail Get Your Free, Private E-mail at http://mail.go.com From owner-sqr-users@list.iex.net Wed Jul 11 10:03:08 2001 Date: Wed, 11 Jul 2001 07:57:04 -0700 From: Rachael Beatty Subject: Re: Exporting SQR into Microsoft Excel --0__=9KFrcq1rJNyikAuL8Gy9Ti8J9xYByRYQu5AyThx5uyvCx4LW4fXuMhHp Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: 7bit You can either open up the lis file in excel and use fixed length to separate the columns or you can create a csv file in your sqr and use that to import into excel. Hope that helps. Rachael > -----Original Message----- > From: "Michael Nielsen" @INTERNET > Sent: Wednesday, July 11, 2001 7:32 AM > To: SQR-USERS@list.iex.net > Subject: Exporting SQR into Microsoft Excel > > > Hello everyone: > > Is their a way I can export information from SQR into a microsoft excel > spreadsheet? > > Thank You > > Mike Nielsen > > ___________________________________________________ > GO.com Mail > Get Your Free, Private E-mail at http://mail.go.com > (See attached file: C.DTF) --0__=9KFrcq1rJNyikAuL8Gy9Ti8J9xYByRYQu5AyThx5uyvCx4LW4fXuMhHp Content-Type: application/octet-stream; name=C.DTF Content-Disposition: attachment; filename=C.DTF Content-Transfer-Encoding: base64 eJ8+IjAOAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQWAAwAOAAAA0QcHAAsABwA5AAQAAwAxAQEggAMADgAAANEHBwAL AAcAKgAuAAMATAEBCYABACEAAABCNkJEMEY5NDhENzVENTExQUM0MjAwMDBFODY3QzBFRgA2BwEE gAEAJwAAAFJFOiBFeHBvcnRpbmcgU1FSIGludG8gTWljcm9zb2Z0IEV4Y2VsAIgNAQ2ABAACAAAA AgACAAEDkAYAAAoAADMAAAADAAFuAAAAAAMAAIAIIAYAAAAAAMAAAAAAAABGAAAAAFKFAAB9bgEA HgABgAggBgAAAAAAwAAAAAAAAEYAAAAAVIUAAAEAAAAEAAAAOS4wAAsAAoAIIAYAAAAAAMAAAAAA AABGAAAAAAaFAAAAAAAAAwADgAggBgAAAAAAwAAAAAAAAEYAAAAAAYUAAAAAAAALAASACCAGAAAA AADAAAAAAAAARgAAAAADhQAAAAAAAAsABYAIIAYAAAAAAMAAAAAAAABGAAAAAA6FAAAAAAAAAwAG gAggBgAAAAAAwAAAAAAAAEYAAAAAEIUAAAAAAAADAAeACCAGAAAAAADAAAAAAAAARgAAAAARhQAA AAAAAAsAD4AIIAYAAAAAAMAAAAAAAABGAAAAABSFAAAAAAAAAwAIgAggBgAAAAAAwAAAAAAAAEYA AAAAGIUAAAAAAAALABGACCAGAAAAAADAAAAAAAAARgAAAACChQAAAQAAAAIBCRABAAAAIwMAAB8D AADLBAAATFpGdaocY7cDAAoAcmNwZzEyNT4yAPQB9wKkA+MCAGNowQrAc2V0MCAHEwKDYwBQD6Zw cnEOUBAYVDxhaANxAoMSIQNkVG3RBCBSbW4CgzMERwhVzQKAfQqACMggOwlvDjBeNRb/CmACgAqB dgiQd9JrC4BkNAxgYwBQCwOWYwBBC2BuDhAwMxThSQvEIFkIYCBjA5FliGl0aASQIG9wCfCQIHVw IBzhIGwEADwgZgMQHdALgByweGN2ZQMgAHBkHXAQYB4heDcJgB3gCfBnHOAdoG8gcxBgCrFhdB3Q HbII4XW3FHAEIAWxeRxVBQBlIOJyYRxwc3YeJyIRBcBzfnEFwB8WHOAg4CBSB3Bw7wkRHnEgYR6z LgqiCoQKgI5IHTEk5BzwbHBzJoV+UgDQEDAe4CaaCvQd8DNeNgFAGvABQBHwbyDwY/J0C9I0IAMw GzQrgBE0UDE2IC0s4k8FEGcPC4AHQAXQB5BzYWdlPyzjJpYq9CrBCxMq9mktGDE0NAFAHfAxODBH AUAM0DCDYiBGA2E6ywMwDJJiEJAiTQ3gKOILB7AIkGwQYG4iIDwHFHAzVBuQQEdPLkMAT00+QElO VEXwUk5FVCuhJqQxsAZgVQIwOjI2VwmAbgeQZABheSwgSnVseRggMTE3oAHQMDEgUDc6MzIQoE01 t1QCbzaHU1FSLVVTSTVAU0Ad8XQuCJB4ji43QCtANcd1YmorIdk2h0V4JZILgGc6QiXU8zLBA2Bz bwGAPTEe0SksZy8PKyIqRHNsK4BB0m1nN9AvURs+XG88YCDgdPBwaFwnDAETgitxJpWPGvASIRTh LKA4IEge4PsJABywdgSQIhA3QDaAJqmWSQQgHOFpJFEgdzeAvCBJHHQ9Ux5xAhByAMC/PZACIB4g A2E92CMAbT6HzR60cxHwIrBkcxzwEHDKPyaaVBAwbmscMiaa3TLAax3QM0Umml9Q31Hv31L+JpQ0 kQWgSrBNC3ApFX5HEHAcMgXAMdAJ4DegUOUFEHYg4kUtAMADESfCEUQhOi8vVwIuZ28XVIImmhYR AFmgAB4AcAABAAAAIwAAAEV4cG9ydGluZyBTUVIgaW50byBNaWNyb3NvZnQgRXhjZWwAAAIBcQAB AAAAGwAAAAHBChhmP7iX+ea06UattgdMrTkuCAgAAH5owAADAC4AAAAAAAsAAgABAAAAAgExAAEA AAArAQAAUENERkVCMDkAAQACAHMAAAAAAAAAOKG7EAXlEBqhuwgAKypWwgAARU1TTURCLkRMTAAA AAAAAAAAG1X6IKpmEc2byACqAC/EWgwAAABMQVhISE1YUzAxAC9vPUhpbHRvbi9vdT1MQVhISC9j bj1SZWNpcGllbnRzL2NuPXJiZWF0dHkALgAAAAAAAAC2ZtgWTVzVEaxCAADoZ8DvAQDtZ/1wCjXU EawkAASsTJXjAAADMpScAAAAAAAALgAAAAAAAAC2ZtgWTVzVEaxCAADoZ8DvAQDtZ/1wCjXUEawk AASsTJXjAAADMpSdAAAQAAAAtr0PlI111RGsQgAA6GfA7ycAAABSRTogRXhwb3J0aW5nIFNRUiBp bnRvIE1pY3Jvc29mdCBFeGNlbAAAAwAJWQMAAAAeAEIQAQAAAEsAAAA8MTYzRUMzRTA2NEFFNDA0 MTkyNDA1RDVGMDQ5RDZGNTMwNzNDNTJBREBoaGNkY214czAyLmhvdGVscy5hZC5oaWx0b24uY29t PgAAAwDeP69vAABAADkA8OSJvxkKwQEDAPE/CQQAAB4AMUABAAAACAAAAFJCRUFUVFkAAwAaQAAA AAAeADBAAQAAAAgAAABSQkVBVFRZAAMAGUAAAAAAAwAmAAAAAAADADYAAAAAAAMA/T/kBAAAAwCA EP////8CAUcAAQAAADQAAABjPVVTO2E9IDtwPUhpbHRvbjtsPUxBWEhITVhTMDEtMDEwNzExMTQ1 NzA0Wi0xODA5ODYAAgH5PwEAAABIAAAAAAAAANynQMjAQhAatLkIACsv4YIBAAAAAAAAAC9PPUhJ TFRPTi9PVT1MQVhISC9DTj1SRUNJUElFTlRTL0NOPVJCRUFUVFkAHgD4PwEAAAAPAAAAUmFjaGFl bCBCZWF0dHkAAB4AOEABAAAACAAAAFJCRUFUVFkAAgH7PwEAAABIAAAAAAAAANynQMjAQhAatLkI ACsv4YIBAAAAAAAAAC9PPUhJTFRPTi9PVT1MQVhISC9DTj1SRUNJUElFTlRTL0NOPVJCRUFUVFkA HgD6PwEAAAAPAAAAUmFjaGFlbCBCZWF0dHkAAB4AOUABAAAACAAAAFJCRUFUVFkAQAAHMDg0jI8Z CsEBQAAIMK6D478XCsEBHgA9AAEAAAAFAAAAUkU6IAAAAAAeAB0OAQAAACMAAABFeHBvcnRpbmcg U1FSIGludG8gTWljcm9zb2Z0IEV4Y2VsAAAeADUQAQAAAEEAAAA8RUQ2N0ZENzAwQTM1RDQxMUFD MjQwMDA0QUM0Q