From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Sat Jan 01 00:00:43 2005 Date: Sat, 1 Jan 2005 00:00:02 -0500 From: Nathan Stratton Treadway Subject: [sqr-users] Monthly sqr-users Hints Posting sqr-users Mailing List Hints and Guidelines Nathan Stratton Treadway, Ray Ontko & Co. (nathanst@ontko.com) $Revision: 1.21 $ $Date: 2004-03-04 02:47:15-05 $ This document gathers in one location information about the sqr-users mail list. It includes instructions for changing your subscription settings (including unsubscribing), as well are guidelines for using the list. ______________________________________________________________________ Table of Contents 1. Changes Since Previous Version 2. Introduction 2.1 sqr-users Mailing List 2.2 sqr-users Hints and Guidelines Document 2.3 Related Mailing Lists 3. Interacting with the list server 3.1 Subscribing to the list 3.2 Unsubscribing from the list 3.3 When your email address changes 3.4 Digests 3.5 Obtaining your password 3.6 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 2003/11/01: o Update subscribe/unsubscribe instructions for new version of list server software. o New URL for "brio-l" mailing list. 2. Introduction 2.1. sqr-users Mailing List The sqr-users mailing list was created to enable discussion related to the SQR database language from Hyperion (and to other products in the SQR product suite). Note: SQR used to be published by SQRIBE Technologies, which was merged into Brio Technology on August 3, 1999. Brio Technology changed its name to Brio Software in October 2001. On October 16, 2003, Hyperion acquired Brio Software. SQR and related applications have also been called "Brio.Report" and "Brio Reports". The mailing list is provided as a service of Ray Ontko & Co. A searchable archive of past postings to the mailing list is available from the SQR Users Group web site . 2.2. sqr-users Hints and Guidelines Document This document gives various hints and guidelines about the sqr-users mailing list. It will be posted approximately monthly to the mailing list so that new users can see it (and existing users can find it easily). An HTML version of the document is always available at http://www.sqrug.org/sqr-users/sqr-users-hints.html. If you have comments or suggestions about this document, please contact me directly at the address listed in the title section. This document is Copyright (C) 1999-2004 by Ray Ontko & Co. It may be freely copied and distributed provided it is not modified in any way and it retains the original copyright notice. 2.3. Related Mailing Lists Here are some mailing lists that cover topics relating to SQR in some way. sqr-jobs : SQR Jobs This list may be used for posting SQR-related resumes, recruitments, job announcements, project announcments, help- wanted, work-wanted ads, etc. To join, follow the link above, or send any message to "sqr-jobs-subscribe@yahoogroups.com". peoplesoft-fans : PeopleSoft Fans Moderated list covering technical and functional aspects of PeopleSoft. To join, follow the link above, or send any message to "peoplesoft-fans-subscribe@yahoogroups.com". psusers : PeopleSoft Users For questions related to any aspect of PeopleSoft (including PeopleSoft-specific SQR issues). This list has merged with the Peoplesoft Fans mailing list and is no longer active, but the message achive can still be found at the link above. BrioSQRReport : Brio SQR Report List for discussion of SQR and SQR Developer (formerly called Brio.Report and Report Builder). To join, follow the link above, or send any message to "BrioSQRReport- subscribe@yahoogroups.com". rm-users : Report- Mart Users List for discussion of Brio Portal (previously called ReportMart). To join, follow the link above, or send a message containing the word "subscribe" in the body to "rm-users- request@sqrug.org". brioportalusersgroup : Brio Portal Users Group list List for discussion of Brio Portal. To join, follow the link above, or send any message to "brioportalusersgroup- subscribe@yahoogroups.com". brio-l : OpenITx Brio List List for technical and functional discussion of all Brio products (but most messages are currently about Brio Intelligence). To join, follow the link above. briousers : Brio.Enter- prise Users List for discussion of the Brio.Enterprise (now called Intelligence) product. To join, follow the link above, or send any message to "briousers-subscribe@yahoogroups.com". briolist : Brio List List for discussion of Brio Intelligence. To join, follow the link above, or send any message to "briolist- subscribe@yahoogroups.com". 3. Interacting with the list server The sqr-users mailing list is hosted using the Mailman list server package. All of the Mailman functions described below are accessed from the sqr-users List Info page, "http://www.sqrug.org/mailman/listinfo/sqr-users". 3.1. Subscribing to the list To subscribe to the mailing list, enter your e-mail address in the box at the top of the "Subscribing to sqr-users" section on the sqr-users List Info page, then choose a password to assign to your subscription and enter it in both "Password" boxes. This password is used to allow you to securely make changes to your subscription over the web. This password will be e-mailed back to you at varous times, so you should not use the same password as you are using elsewhere. Once you have filled in the password, click the "Subscribe" button. You should soon receive an e-mail message from the list server requesting confirmation of your subscription request. Once you have replied to this message and your subscription has been processed, you will receive a "welcome" message giving you more information on the list server, and as well as a reminder of the subscription password you chose. 3.2. Unsubscribing from the list To unsubscribe from the list, go to the sqr-users List Info page, enter your e-mail address in the "sqr-users Subscribers" section near the bottom of the page, and click the "Unsubscribe or edit options" button. You will then see the "member options" page; fill in your password at the top of the page and click "Log in". (If you have forgotton your password, see the ``Obtaining your Password'' section, below). This will take you to the "membership configuration" page. In the middle of that page, there will be an "Unsubscribing from sqr- users" section; simply turn on the "Yes, I really want to unsubscribe" checkbox and click the "Unsubscribe" button. You should get a page back saying that the removal was successful (as well as a confirmation email message). The removal will take effect immediately; there is no need to send in a confirmation e-mail message. If you don't know your password, you can choose to unsubscribe using e-mail confirmation. Instead of entering the password on the "member options" page, just click the "Unsubscribe" button found just below the "Log In" button. You will then see a page noting that a confirmation e-mail has been sent; once you follow the confirmation instructions in that e-mail your removal will be complete. 3.3. When your email address changes If your e-mail address changes -- for example, if your company changes names, or if your mail administrator changes the way your name is spelled out or abbreviated -- you will need to update your subscription to match your new address before you can post to the list (see the ``Posting'' section, below). To do this, simply log in to your "membership configuration" page (see the directions for logging in to this page in the ``Unsubscribing'' section, above). At the top of that page you'll see a "Changing your membership information" section. Fill out the text boxes and click the "Change My Address and Name" button. You will be sent a confirmation e-mail, and once you have responded to that e-mail your subscription will be updated and you can begin posting from your new address. 3.4. Digests You can request to get your messages in digest form (one large message containing all of the posts made each day). To do this, log in to your "membership configuration" page (see the directions on doing this in the ``Unsubscribing'' section, above). At the bottom of the page, you will see a section titled "Your sqr-users Subscription Options". Change the "Set Digest Mode" option (the second option in the list) to "On" and click the "Submit My Changes" button at the bottom of the page. You actually have a choice of two different digest formats, "MIME" or "Plain Text". The MIME format sends each individual list message as a separate MIME attachment to the digest; the plain text format consists of one large text message, with all of the individual messages run together. Which format you will want to use depends on your mail- reading software (and personal preference). If you are not sure which format will work best, you can just leave the default setting of "MIME" and see if the next morning's digest is easy to read in your mail reader -- you can change to the other format at any time using that same web page. If you use digests, you should not just "reply" to the digest mailing. Instead, be sure your message's subject header matches the subject in the original posting to which you are replying, and that you don't include a quoted copy of the entire digest in your post. To restore normal distribution, simply follow the above instructions choosing the "Off" setting for the Digest option. 3.5. Obtaining your password You will not be able to change your subscription options without your password. If you have forgotten your password, simply click the "Email My Password to Me" button on the "member options" page and your password will be emailed to you. You will also be e-mailed a copy of your password at the beginning of each month, as part of a "mailing list memberships reminder" message from the Mailman software. 3.6. Problems If the above instructions do not work, please contact the list manager at "sqr-users-manager@sqrug.org". (Do not send a message to the list itself -- no one there can help you with your problem!) Please include a description of what you were trying to do and what exactly happened when you made the attempt. 4. Posting to the sqr-users List In order to post to the mailing list, you must be subscribed to it (see directions above). More specifically, in order for Mailman to recognize that this message was sent by a subscriber, the return address on your message much exactly match the addresss you used to subscribe -- if you have more than one address you will need to be sure that you are sending from the correct one. (See the ``When you email address changes'' section, above, if your e-mail address has changed since you subscribed.) 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 as other subscribers' mail systems repond....) o do not send messages in HTML or other special formats. (These are not usable by many subscribers, especially those who receive their messages in digest format.) The list server will delete these special attachments from your message and send you a warning notice. Also, if you are trying to send (for example) SQR source code files, make sure your mail-reader software is assigning proper MIME types to your attachments or they may not be distributed. o keep in mind that over 500 people will receive your message, and that it will appear in the list archives. Try to keep your messages on-topic and relatively short. Consider sending your message directly to particular people if it won't interest many of the list's subscribers. o ignore abusive and inappropriate messages that get sent to the list, rather than getting drawn into a debate. The very nature of these messages will cause most subscribers to automatically discredit them -- no "response" is necessary. To the contrary, any response will only "add fuel to the fire," prolonging the controversy and further interupting the normal conversation on the list. 4.2. Posting a Message Once you are subscribed, you can post to the list by sending a message to "sqr-users@sqrug.org". Be sure your subject heading describes your specific problem or question (i.e. don't just use "SQR Question"). In the body of your message, please include the following information: o SQR product name and version number (see notes below) o Operating system name and version number o Database name and version number o Application name (i.e. PeopleSoft) and a version number, if any Also, include a short code sample whenever possible. It's much easier for other people to understand what you are trying to do when they can see the actual code. (At the same time, be careful not to post confidential or proprietary code, since the posting will be placed in a public web archive.) Note that your question should relate to SQR or other Brio Report product in some way. Don't ask questions about PeopleSoft, for example, unless you are having SQR-related issues. (For PeopleSoft- specific questions, see other lists mentioned in the ``Related Mailing Lists'' section.) In 2001, PeopleSoft purchased from Brio the rights to the source code for SQR and began releasing its own line of SQR. PeopleSoft's SQR version numbering is different from Brio/Hyperion's, so it's very important to mention whether you are using Hyperion's "SQR" or "SQR for PeopleSoft". (This info is included automatically if you include the full SQR version-id string as described below.) To get the version-id string for your copy of SQR, you can run the following program: begin-program show $sqr-ver end-program This will produce a line that looks something like this: SQR/3.0.15/Intel/SCO Unix R3.2 V5.0/Oracle 7.2.2.3/Sep 25 1996 Include the full line of output in your message. Note that the oper- ating system and database version numbers listed here are the ones under which this copy of SQR was compiled, so you'll still need to include this information for your own environment. In a command-line environment (Unix, etc.), you can also get this version-id string by saying sqr -id 4.3. Replying to a Message Messages sent out on the list have a Reply-To header pointing to the list. This means that you should be able to simply use your mail reader's "reply" function to send a reply to the list. Generally, all answers to questions sent out on the list should be public replies, so that other subscribers can benefit. In those cases where a private reply is appropriate, be sure to over-ride the default Reply-To address. If your message is not actually a reply to the earlier message, please do not use the "reply" function. Instead, address a new e-mail message to the list as described in the previous section. (This is because the list archives will incorrectly show the new message in the original thread if you use "reply".) You should quote enough lines from the original message that later readers can understand your answer even if they missed the original question. At the same time, you should remove any unnecessary lines from the quote in order to make your message shorter and easier to understand. (Remember that some people read the mailing list in digest form, and the digests become much larger if unnecessary lines are quoted.) 5. Job Announcements Please do not post job announcements on the sqr-users list, but use the sqr-jobs list instead. (See ``Related Mailing Lists'' for more information on this list.) A large number of sqr-users subscribers do not want to receive job announcements (for various reasons) and such posts tend to cause a lot of disruption of the discussion on the list. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Sat Jan 01 15:21:38 2005 From: "Manuel Basil Arakkal" Subject: RE: [sqr-users] In DB2 $sql-error not displaying the complete error message Date: Sat, 01 Jan 2005 12:19:53 -0800 I had basically added the code with an error in it in my email to display the fact that $sql-error does not give the full description when an error is encountered. You have said that the error message gives enough information. However the error message with the full information was obtained without using the $sql-error. If we use the $sql-error then the error message is not displayed fully and that is my problem. We would like to have $sql-error display the full error message so that we could take action based on the error message. We could use on-error to get information on which row is being processed by the SQR. However since $sql-error does not display the full error in DB2 this is not possible. There should be no problem with using &variables within the SQL statement. These variables are used to pick up values from other SQL statements in the same SQL without assigning them to $variables. There should also be no problem in indenting FROM and WHERE clauses. Manuel RE: [sqr-users] In DB2 $sql-error not displaying the complete error message -------------------------------------------------------------------------------- Subject: RE: [sqr-users] In DB2 $sql-error not displaying the complete error message From: Bob Stone Date: Thu, 2 Dec 2004 08:39:49 -0600 Delivery-date: Thu, 02 Dec 2004 09:40:58 -0500 List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." -------------------------------------------------------------------------------- Well, I think there are a few problems in your code that should help you fix it. The error you get gives you enough information, you just have to read it. It says it found a '[' and the SQR doesn't know what the heck that is. So, you cant have the bracket in there...and, like the SQR, i'm not sure what that is either. It looks like you're trying to check a value, but dont know if it will exists. And if it does not exist, you want to stick the variable in there. Since I dont know DB2, i dont know what the fix is on that. In Oracle you can say select nvl(col1, $var1) ! <-- nvl - if no value (null value) of col1 then use $var1 from table where [$where_clause] -------------the problem of your SQL #ifdef MVS ,\$Incr_Effdt\ ,\#Job_Insert_Effseq\ #else ,[$Incr_Effdt] ,[#Job_Insert_Effseq] #end-if -------------- Another problem in your SQR is at the bottom. Recently someone reread the rules on indentation, and I've already forgotten them, BUT, i'm gonna go ahead and say that you shouldn't have indented the 'FROM' and 'WHERE'. I know its okay to indent the 'AND's (possibly the where too). It maybe be personal preference, but I like to keep my entire SQL statement flush to the left margin...it makes for more easily readable SQR (in my opinion). But the serious problem at the bottom are the &variables you're trying to match against...You cant match against &variables in a SQL clause...you need to have set them up as $variables. (let $var1 = &var1, etc). If you're trying to match them up as a 2 table where clause thing, then include both tables in the FROM, and match them up in the WHERE. Once you figure out that # \\ [] problem up top, unindent your FROM/WHEREs, and set up the variables as $variables, your SQR should work a lot better. You dont necesarily need to get a better error message, you just need to understand the one you got a bit better. When it says 'An unexpected token "[" was found following "" ' and you've got a [ character sitting in your SQL, that should be a clue that it doesn't like that. Also when it says 'SQL: ...WHERE EMPLID = ? AND EMPL_RCD = ? AND EFFDT = ? AND EFFSEQ = ?' you might realize that it couldn't figure out what those &variable were supposed to be. -----Original Message----- From: sqr-users-bounces+bstone=fastenal.com@sqrug.org [mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of Manuel Basil Arakkal Sent: Wednesday, December 01, 2004 5:35 PM To: sqr-users@sqrug.org Subject: [sqr-users] In DB2 $sql-error not displaying the complete error message We have this code in our program: Begin-Sql ON-ERROR=KP-SQL-ERROR('PS_JOB_EARNS_DIST - INSERT ',&JOB.EMPLID, #sql-status, $sql-error) INSERT INTO PS_JOB_EARNS_DIST SELECT EMPLID ,EMPL_RCD #ifdef MVS ,\$Incr_Effdt\ ,\#Job_Insert_Effseq\ #else ,[$Incr_Effdt] ,[#Job_Insert_Effseq] #end-if ,DEPTID ,JOBCODE ,POSITION_NBR ,GL_PAY_TYPE ,ACCT_CD ,SHIFT ,LOCATION ,ERNCD ,COMPRATE ,DIST_PCT ,STD_HOURS ,BUSINESS_UNIT FROM PS_JOB_EARNS_DIST WHERE EMPLID = &JOBI.EMPLID AND EMPL_RCD = &JOBI.EMPL_RCD AND EFFDT = &JOBI.EFFDT AND EFFSEQ = &JOBI.EFFSEQ End-Sql Begin-Procedure KP-SQL-ERROR($Table_Name, $Emplid, #sql-status, $sql-error) Let $sql-status = #sql-status Let $error_message = 'FATAL SQL ERROR. Table_Name:' || $Table_Name || 'Emplid: '||$Emplid||'Error: '||$sql-error||' - '||$sql-status Show $error_message Stop End-PROCEDURE which produces the following message in the log file: FATAL SQL ERROR. Table_Name:PS_JOB_EARNS_DIST - INSERT Emplid: KC0007 Error: SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statemen - -518.0000000000000000000000000000000000000000000000000 Error on line 3259: (SQR 3301) Program stopped by user request. SQR for PeopleSoft: Program Aborting. The $sql-error varaible does not give the complete error message. When the ON-ERROR code is removed then we get the complete error message from DB2: (SQR 5528) DB2 SQL PREPARE/DECLARE error -104 in cursor 37: SQL0104N An unexpected token "[" was found following "". Expected tokens may include: "( + - ? : CASE CAST USER CURRENT ". SQLSTATE=42601 SQL: INSERT INTO PS_JOB_EARNS_DIST SELECT EMPLID ,EMPL_RCD ,'2000-12-16' ,[? ,DEPTID ,JOBCODE ,POSITION_NBR ,GL_PAY_TYPE ,ACCT_CD ,SHIFT ,LOCATION ,ERNCD ,COMPRATE ,DIST_PCT ,STD_HOURS ,BUSINESS_UNIT FROM PS_JOB_EARNS_DIST WHERE EMPLID = ? AND EMPL_RCD = ? AND EFFDT = ? AND EFFSEQ = ? (SQR 5528) DB2 SQL OPEN/EXECUTE error -518 in cursor 37: SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement. SQLSTATE=07003 Error on line 2536: (SQR 3735) Could not execute SQL. SQR for PeopleSoft: Program Aborting. Is there some way I could get the complete error message from DB2 using ON-ERROR? Thanks, Manuel _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users -------------------------------------------------------------------------------- Prev by Date: [sqr-users] Printing data in next page using on-break Next by Date: RE: [sqr-users] MAX() Query Previous by thread: [sqr-users] Printing data in next page using on-break Next by thread: [sqr-users] How to extract data in an SQR Indexes: [ Thread] [ Author] [ Date] SQR-USERS List and Archive Info SQRUG Home Page _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Sat Jan 01 19:54:49 2005 From: "Steve Cavill" Subject: RE: [sqr-users] In DB2 $sql-error not displaying the complete errormessage Date: Sun, 2 Jan 2005 11:50:04 +1100 Hi Manuel, I think your problem is that you are getting 2 error messages from DB2 and SQR is only displaying the last of the 2. The FIRST error is that the SQL won't compile: (SQR 5528) DB2 SQL PREPARE/DECLARE error -104 in cursor 37: SQL0104N An unexpected token "[" was found following "". Expected tokens may include: "( + - ? : CASE CAST USER CURRENT ". SQLSTATE=42601 The SECOND error is that the SQL can't run (because it is not prepared) (SQR 5528) DB2 SQL OPEN/EXECUTE error -518 in cursor 37: SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement. SQLSTATE=07003 The $sql-error does contain that whole message (although I don't know why it seems to cut off the "t" in "statement". Because there are two errors, the $sql-error from the first error gets overwritten by the 2nd error before SQR gets a chance to report the error. This is normal behaviour for SQR. $sql-error only ever contains the most recent error message. Can you trap the first (prepare) error? I doubt it. If you want to see the full error messages you could use the -s flag which sends to sql to the .err file for later analysis. Note that for runtime error checking I think you are better off using #sql-status, as the messages themselves can contain a lot of variable text which is hard to check for. In your case error -518 indicates the sql could not be compiled. This is not really a "runtime" error as the "[" in your sql will never compile. But you can still print out the values of all the variables yourself to determine what is wrong with the SQL, and keep processing further records. Regarding your actual SQL, I assume that $Incr_Effdt and #Job_Insert_Effseq are hardcoded values you want to use in your select. In which case you should be able to just use the variable names without the [] brackets. The [] syntax only applies in begin-select. And you are right the &variable are fine and the from, where formatting is all irrelevant in a begin-sql block, the special sql formatting rules only apply in BEGIN-SELECT. Cheers, Steve. -----Original Message----- From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org [mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] On Behalf Of Manuel Basil Arakkal Sent: Sunday, 2 January 2005 7:20 AM To: bstone@fastenal.com Cc: sqr-users@sqrug.org Subject: RE: [sqr-users] In DB2 $sql-error not displaying the complete errormessage I had basically added the code with an error in it in my email to display the fact that $sql-error does not give the full description when an error is encountered. You have said that the error message gives enough information. However the error message with the full information was obtained without using the $sql-error. If we use the $sql-error then the error message is not displayed fully and that is my problem. We would like to have $sql-error display the full error message so that we could take action based on the error message. We could use on-error to get information on which row is being processed by the SQR. However since $sql-error does not display the full error in DB2 this is not possible. There should be no problem with using &variables within the SQL statement. These variables are used to pick up values from other SQL statements in the same SQL without assigning them to $variables. There should also be no problem in indenting FROM and WHERE clauses. Manuel RE: [sqr-users] In DB2 $sql-error not displaying the complete error message ---------------------------------------------------------------------------- ---- Subject: RE: [sqr-users] In DB2 $sql-error not displaying the complete error message From: Bob Stone Date: Thu, 2 Dec 2004 08:39:49 -0600 Delivery-date: Thu, 02 Dec 2004 09:40:58 -0500 List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." ---------------------------------------------------------------------------- ---- Well, I think there are a few problems in your code that should help you fix it. The error you get gives you enough information, you just have to read it. It says it found a '[' and the SQR doesn't know what the heck that is. So, you cant have the bracket in there...and, like the SQR, i'm not sure what that is either. It looks like you're trying to check a value, but dont know if it will exists. And if it does not exist, you want to stick the variable in there. Since I dont know DB2, i dont know what the fix is on that. In Oracle you can say select nvl(col1, $var1) ! <-- nvl - if no value (null value) of col1 then use $var1 from table where [$where_clause] -------------the problem of your SQL #ifdef MVS ,\$Incr_Effdt\ ,\#Job_Insert_Effseq\ #else ,[$Incr_Effdt] ,[#Job_Insert_Effseq] #end-if -------------- Another problem in your SQR is at the bottom. Recently someone reread the rules on indentation, and I've already forgotten them, BUT, i'm gonna go ahead and say that you shouldn't have indented the 'FROM' and 'WHERE'. I know its okay to indent the 'AND's (possibly the where too). It maybe be personal preference, but I like to keep my entire SQL statement flush to the left margin...it makes for more easily readable SQR (in my opinion). But the serious problem at the bottom are the &variables you're trying to match against...You cant match against &variables in a SQL clause...you need to have set them up as $variables. (let $var1 = &var1, etc). If you're trying to match them up as a 2 table where clause thing, then include both tables in the FROM, and match them up in the WHERE. Once you figure out that # \\ [] problem up top, unindent your FROM/WHEREs, and set up the variables as $variables, your SQR should work a lot better. You dont necesarily need to get a better error message, you just need to understand the one you got a bit better. When it says 'An unexpected token "[" was found following "" ' and you've got a [ character sitting in your SQL, that should be a clue that it doesn't like that. Also when it says 'SQL: ...WHERE EMPLID = ? AND EMPL_RCD = ? AND EFFDT = ? AND EFFSEQ = ?' you might realize that it couldn't figure out what those &variable were supposed to be. -----Original Message----- From: sqr-users-bounces+bstone=fastenal.com@sqrug.org [mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of Manuel Basil Arakkal Sent: Wednesday, December 01, 2004 5:35 PM To: sqr-users@sqrug.org Subject: [sqr-users] In DB2 $sql-error not displaying the complete error message We have this code in our program: Begin-Sql ON-ERROR=KP-SQL-ERROR('PS_JOB_EARNS_DIST - INSERT ',&JOB.EMPLID, #sql-status, $sql-error) INSERT INTO PS_JOB_EARNS_DIST SELECT EMPLID ,EMPL_RCD #ifdef MVS ,\$Incr_Effdt\ ,\#Job_Insert_Effseq\ #else ,[$Incr_Effdt] ,[#Job_Insert_Effseq] #end-if ,DEPTID ,JOBCODE ,POSITION_NBR ,GL_PAY_TYPE ,ACCT_CD ,SHIFT ,LOCATION ,ERNCD ,COMPRATE ,DIST_PCT ,STD_HOURS ,BUSINESS_UNIT FROM PS_JOB_EARNS_DIST WHERE EMPLID = &JOBI.EMPLID AND EMPL_RCD = &JOBI.EMPL_RCD AND EFFDT = &JOBI.EFFDT AND EFFSEQ = &JOBI.EFFSEQ End-Sql Begin-Procedure KP-SQL-ERROR($Table_Name, $Emplid, #sql-status, $sql-error) Let $sql-status = #sql-status Let $error_message = 'FATAL SQL ERROR. Table_Name:' || $Table_Name || 'Emplid: '||$Emplid||'Error: '||$sql-error||' - '||$sql-status Show $error_message Stop End-PROCEDURE which produces the following message in the log file: FATAL SQL ERROR. Table_Name:PS_JOB_EARNS_DIST - INSERT Emplid: KC0007 Error: SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statemen - -518.0000000000000000000000000000000000000000000000000 Error on line 3259: (SQR 3301) Program stopped by user request. SQR for PeopleSoft: Program Aborting. The $sql-error varaible does not give the complete error message. When the ON-ERROR code is removed then we get the complete error message from DB2: (SQR 5528) DB2 SQL PREPARE/DECLARE error -104 in cursor 37: SQL0104N An unexpected token "[" was found following "". Expected tokens may include: "( + - ? : CASE CAST USER CURRENT ". SQLSTATE=42601 SQL: INSERT INTO PS_JOB_EARNS_DIST SELECT EMPLID ,EMPL_RCD ,'2000-12-16' ,[? ,DEPTID ,JOBCODE ,POSITION_NBR ,GL_PAY_TYPE ,ACCT_CD ,SHIFT ,LOCATION ,ERNCD ,COMPRATE ,DIST_PCT ,STD_HOURS ,BUSINESS_UNIT FROM PS_JOB_EARNS_DIST WHERE EMPLID = ? AND EMPL_RCD = ? AND EFFDT = ? AND EFFSEQ = ? (SQR 5528) DB2 SQL OPEN/EXECUTE error -518 in cursor 37: SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement. SQLSTATE=07003 Error on line 2536: (SQR 3735) Could not execute SQL. SQR for PeopleSoft: Program Aborting. Is there some way I could get the complete error message from DB2 using ON-ERROR? Thanks, Manuel _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ---------------------------------------------------------------------------- ---- Prev by Date: [sqr-users] Printing data in next page using on-break Next by Date: RE: [sqr-users] MAX() Query Previous by thread: [sqr-users] Printing data in next page using on-break Next by thread: [sqr-users] How to extract data in an SQR Indexes: [ Thread] [ Author] [ Date] SQR-USERS List and Archive Info SQRUG Home Page _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Jan 03 09:17:02 2005 Date: Mon, 3 Jan 2005 06:15:49 -0800 (PST) From: "Nehal H. Patel" Subject: Re: [sqr-users] Executing a Sybase stored procedure from displaying results Thanks for the reply Dattatraya, The stored procedure is simple, it does not take any prams. The stored proc just queries a table and returns the two fields in ASC order. I'd like to display this data. I've never used a stored proc before, in the past i've always used embedded SQL in the SQR. Thanks. -Nehal. --- Dattatraya Shetty wrote: > Hi Neha, > What results would you like to Display ? Is it > output of the Stored > Procedure or else are you going to save the results > into a Temp Table > and want to display that ? > > Please give more details. > Datta. > > --- "Nehal H. Patel" > wrote: > > > Hello, > > I have a stored proc that accepts one parameter > and > > I'd like to display the results. > > > > Does anyone know how and have an example? > > > > Thanks. > > > > > > > > __________________________________ > > Do you Yahoo!? > > Yahoo! Mail - now with 250MB free storage. Learn > more. > > http://info.mail.yahoo.com/mail_250 > > > > _______________________________________________ > > sqr-users mailing list > > sqr-users@sqrug.org > > http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - Find what you need with new enhanced > search. > http://info.mail.yahoo.com/mail_250 > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > __________________________________ Do you Yahoo!? Dress up your holiday email, Hollywood style. Learn more. http://celebrity.mail.yahoo.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Jan 03 09:54:14 2005 From: Jeff.Steinfeldt@wellsfargo.com Subject: RE: [sqr-users] Executing a Sybase stored procedure from displayi ng results Date: Mon, 3 Jan 2005 08:52:48 -0600 What database are you connecting to? (Oracle, SQL Server, ???) -----Original Message----- From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On Behalf Of Nehal H. Patel Sent: Monday, January 03, 2005 8:16 AM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: Re: [sqr-users] Executing a Sybase stored procedure from displaying results Thanks for the reply Dattatraya, The stored procedure is simple, it does not take any prams. The stored proc just queries a table and returns the two fields in ASC order. I'd like to display this data. I've never used a stored proc before, in the past i've always used embedded SQL in the SQR. Thanks. -Nehal. --- Dattatraya Shetty wrote: > Hi Neha, > What results would you like to Display ? Is it > output of the Stored > Procedure or else are you going to save the results > into a Temp Table > and want to display that ? > > Please give more details. > Datta. > > --- "Nehal H. Patel" > wrote: > > > Hello, > > I have a stored proc that accepts one parameter > and > > I'd like to display the results. > > > > Does anyone know how and have an example? > > > > Thanks. > > > > > > > > __________________________________ > > Do you Yahoo!? > > Yahoo! Mail - now with 250MB free storage. Learn > more. > > http://info.mail.yahoo.com/mail_250 > > > > _______________________________________________ > > sqr-users mailing list > > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - Find what you need with new enhanced > search. > http://info.mail.yahoo.com/mail_250 > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > __________________________________ Do you Yahoo!? Dress up your holiday email, Hollywood style. Learn more. http://celebrity.mail.yahoo.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Jan 03 10:00:05 2005 From: Jeff.Steinfeldt@wellsfargo.com Subject: RE: [sqr-users] Executing a Sybase stored procedure from displayi ng results Date: Mon, 3 Jan 2005 08:58:51 -0600 Sorry folks, its definitely Monday! -----Original Message----- From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On Behalf Of Jeff.Steinfeldt@wellsfargo.com Sent: Monday, January 03, 2005 8:53 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Executing a Sybase stored procedure from displayi ng results What database are you connecting to? (Oracle, SQL Server, ???) -----Original Message----- From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On Behalf Of Nehal H. Patel Sent: Monday, January 03, 2005 8:16 AM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: Re: [sqr-users] Executing a Sybase stored procedure from displaying results Thanks for the reply Dattatraya, The stored procedure is simple, it does not take any prams. The stored proc just queries a table and returns the two fields in ASC order. I'd like to display this data. I've never used a stored proc before, in the past i've always used embedded SQL in the SQR. Thanks. -Nehal. --- Dattatraya Shetty wrote: > Hi Neha, > What results would you like to Display ? Is it > output of the Stored > Procedure or else are you going to save the results > into a Temp Table > and want to display that ? > > Please give more details. > Datta. > > --- "Nehal H. Patel" > wrote: > > > Hello, > > I have a stored proc that accepts one parameter > and > > I'd like to display the results. > > > > Does anyone know how and have an example? > > > > Thanks. > > > > > > > > __________________________________ > > Do you Yahoo!? > > Yahoo! Mail - now with 250MB free storage. Learn > more. > > http://info.mail.yahoo.com/mail_250 > > > > _______________________________________________ > > sqr-users mailing list > > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - Find what you need with new enhanced > search. > http://info.mail.yahoo.com/mail_250 > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > __________________________________ Do you Yahoo!? Dress up your holiday email, Hollywood style. Learn more. http://celebrity.mail.yahoo.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Jan 03 15:46:41 2005 Subject: Re: [sqr-users] Executing a Sybase stored procedure from displaying results From: "Richard C Spoth" Date: Mon, 3 Jan 2005 15:47:33 -0500 Use the excute..INTO function. execute SPyourStoreProcedure INTO &First int , &Second int , &Third char(3) Rick "Nehal H. Patel" Sent by: sqr-users-bounces+spoth.richard=healthnow.org@sqrug.org 01/03/2005 09:15 AM Please respond to "This list is for discussion about the SQR database reporting language from Hyperion Solutions." To: "This list is for discussion about the SQR database reporting language from Hyperion Solutions." cc: Subject: Re: [sqr-users] Executing a Sybase stored procedure from displaying results Thanks for the reply Dattatraya, The stored procedure is simple, it does not take any prams. The stored proc just queries a table and returns the two fields in ASC order. I'd like to display this data. I've never used a stored proc before, in the past i've always used embedded SQL in the SQR. Thanks. -Nehal. --- Dattatraya Shetty wrote: > Hi Neha, > What results would you like to Display ? Is it > output of the Stored > Procedure or else are you going to save the results > into a Temp Table > and want to display that ? > > Please give more details. > Datta. > > --- "Nehal H. Patel" > wrote: > > > Hello, > > I have a stored proc that accepts one parameter > and > > I'd like to display the results. > > > > Does anyone know how and have an example? > > > > Thanks. > > > > > > > > __________________________________ > > Do you Yahoo!? > > Yahoo! Mail - now with 250MB free storage. Learn > more. > > http://info.mail.yahoo.com/mail_250 > > > > _______________________________________________ > > sqr-users mailing list > > sqr-users@sqrug.org > > http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - Find what you need with new enhanced > search. > http://info.mail.yahoo.com/mail_250 > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > __________________________________ Do you Yahoo!? Dress up your holiday email, Hollywood style. Learn more. http://celebrity.mail.yahoo.com _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users CONFIDENTIALITY NOTICE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain proprietary, confidential, trade secret or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited and may be a violation of law. If you are not the intended recipient or a person responsible for delivering this message to an intended recipient, please contact the sender by reply email and destroy all copies of the original message. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Jan 04 14:50:07 2005 Date: Tue, 4 Jan 2005 13:48:59 -0600 From: Krishna Prasad Kommuri Subject: [sqr-users] Error writing to file Hi, When i try to write a numeric variable of length 6, it gives me the following error. (SQR 3211) #Numeric variables and literals must have :length of 1, 2 or 4 bytes. Is it mandatory to have a numeric variable of the specified length (1,2 or 4)? Thanks, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Jan 04 14:55:46 2005 Subject: RE: [sqr-users] Error writing to file Date: Tue, 4 Jan 2005 13:55:09 -0600 From: "Knapp, Richard" Must =3D mandatory, no? Today's programmer quiz: What is the largest value that can be stored in 4 bytes? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Tuesday, January 04, 2005 1:49 PM To: sqr-users@sqrug.org Subject: [sqr-users] Error writing to file Hi, When i try to write a numeric variable of length 6, it gives me the following error. (SQR 3211) #Numeric variables and literals must have :length of 1, 2 or 4 bytes. Is it mandatory to have a numeric variable of the specified length (1,2 or 4)? Thanks, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Jan 04 15:00:27 2005 Subject: RE: [sqr-users] Error writing to file Date: Wed, 5 Jan 2005 01:31:37 +0530 From: "Murty Mirthipati Venkata Suryanarayan" This is a multi-part message in MIME format... ------_=_NextPart_001_01C4F298.32C1EBAB Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 SSBkb3VidCBpZiB5b3UgYXJlIGF0dGVtcHRpbmcgdG8gd3JpdGUgdGhlIG51 bWJlciBkaXJlY3RseSB0byBmaWxlLiBUaGlzIHNlZW1zIHRvIGJlIGNvZGUg bGV2ZWwgZXJyb3IuIE5vdCByZWxhdGVkIHRvIHRoZSBkYWJhc2UgbGV2ZWwg KHdpdGggZmllbGQgc2l6ZXMgYW5kIGFsbCkNCiANCldoeSBub3QgeW91IHBv c3QgdGhlIHBvcnRpb24gb2YgY29kZSB0aGF0IHlvdSBhcmUgZmFjaW5nIGVy cm9yIHdpdGg/DQogDQpNdXJ0eSA7KQ0KDQoJLS0tLS1PcmlnaW5hbCBNZXNz YWdlLS0tLS0gDQoJRnJvbTogS3Jpc2huYSBQcmFzYWQgS29tbXVyaSBbbWFp bHRvOmtvbW11cmlAZ21haWwuY29tXSANCglTZW50OiBUdWUgMDQtSmFuLTIw MDUgMTQ6NDggDQoJVG86IHNxci11c2Vyc0BzcXJ1Zy5vcmcgDQoJQ2M6IA0K CVN1YmplY3Q6IFtzcXItdXNlcnNdIEVycm9yIHdyaXRpbmcgdG8gZmlsZQ0K CQ0KCQ0KDQoJSGksDQoJDQoJICAgICAgIFdoZW4gaSB0cnkgdG8gd3JpdGUg YSBudW1lcmljIHZhcmlhYmxlIG9mIGxlbmd0aCA2LCBpdCBnaXZlcyBtZQ0K CXRoZSBmb2xsb3dpbmcgZXJyb3IuIChTUVIgMzIxMSkgI051bWVyaWMgdmFy aWFibGVzIGFuZCBsaXRlcmFscyBtdXN0DQoJaGF2ZSA6bGVuZ3RoIG9mIDEs IDIgb3IgNCBieXRlcy4NCgkNCgkgICAgICAgIElzIGl0IG1hbmRhdG9yeSB0 byBoYXZlIGEgbnVtZXJpYyB2YXJpYWJsZSBvZiB0aGUgc3BlY2lmaWVkDQoJ bGVuZ3RoICgxLDIgb3IgNCk/DQoJDQoJVGhhbmtzLA0KCQ0KCV9fX19fX19f X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fDQoJc3Fy LXVzZXJzIG1haWxpbmcgbGlzdA0KCXNxci11c2Vyc0BzcXJ1Zy5vcmcNCglo dHRwOi8vd3d3LnNxcnVnLm9yZy9tYWlsbWFuL2xpc3RpbmZvL3Nxci11c2Vy cw0KCQ0KCQ0KDQo= ------_=_NextPart_001_01C4F298.32C1EBAB Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'application/ms-tnef' MIME type. It has been automatically discarded before sending the message to the list. ------_=_NextPart_001_01C4F298.32C1EBAB Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ------_=_NextPart_001_01C4F298.32C1EBAB-- From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Jan 04 15:07:27 2005 Date: Tue, 04 Jan 2005 15:06:13 -0500 From: "Larry Roux" Subject: RE: [sqr-users] Error writing to file It has been a long time....without using a calculator: 299,451,648? But I think it is +/- 149,725,824 for in integer - Less for a float. Larry Roux Syracuse University lroux@syr.edu >>> KnappR@umsystem.edu 1/4/2005 2:55:09 PM >>> Must = mandatory, no? Today's programmer quiz: What is the largest value that can be stored in 4 bytes? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Tuesday, January 04, 2005 1:49 PM To: sqr-users@sqrug.org Subject: [sqr-users] Error writing to file Hi, When i try to write a numeric variable of length 6, it gives me the following error. (SQR 3211) #Numeric variables and literals must have :length of 1, 2 or 4 bytes. Is it mandatory to have a numeric variable of the specified length (1,2 or 4)? Thanks, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Jan 04 15:12:32 2005 Subject: RE: [sqr-users] Error writing to file Date: Tue, 4 Jan 2005 13:11:17 -0700 From: "Lance Munger" I'll go with 4,294,967,295 ... Lance D. Munger RF4 Developer 303.925.3120 office 720.364.4721 mobile -----Original Message----- From: sqr-users-bounces+lance.munger=3Dtq3navigant.com@sqrug.org [mailto:sqr-users-bounces+lance.munger=3Dtq3navigant.com@sqrug.org] On Behalf Of Larry Roux Sent: Tuesday, January 04, 2005 1:06 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Error writing to file It has been a long time....without using a calculator: 299,451,648? But I think it is +/- 149,725,824 for in integer - Less for a float. =20 =20 =20 =20 Larry Roux Syracuse University lroux@syr.edu >>> KnappR@umsystem.edu 1/4/2005 2:55:09 PM >>> Must =3D mandatory, no? Today's programmer quiz: What is the largest value that can be stored in 4 bytes? Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Tuesday, January 04, 2005 1:49 PM To: sqr-users@sqrug.org Subject: [sqr-users] Error writing to file Hi, When i try to write a numeric variable of length 6, it gives me the following error. (SQR 3211) #Numeric variables and literals must have :length of 1, 2 or 4 bytes. Is it mandatory to have a numeric variable of the specified length (1,2 or 4)? Thanks, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Jan 04 15:26:11 2005 Subject: RE: [sqr-users] Error writing to file Date: Wed, 5 Jan 2005 02:00:58 +0530 From: "Murty Mirthipati Venkata Suryanarayan" This is a multi-part message in MIME format... ------_=_NextPart_001_01C4F29C.4C7AA755 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 SSBnb3QgaXQuIExhbmNlLCBoYXMgYSBmYXN0ZXIgY2FsY3VsYXRvciB0aGFu IG1pbmUgOykNCk11cnR5IDspDQogDQoNCgktLS0tLU9yaWdpbmFsIE1lc3Nh Z2UtLS0tLSANCglGcm9tOiBMYW5jZSBNdW5nZXIgW21haWx0bzpMYW5jZS5N dW5nZXJAVFEzTmF2aWdhbnQuY29tXSANCglTZW50OiBUdWUgMDQtSmFuLTIw MDUgMTU6MTEgDQoJVG86IFRoaXMgbGlzdCBpcyBmb3IgZGlzY3Vzc2lvbiBh Ym91dCB0aGUgU1FSIGRhdGFiYXNlIHJlcG9ydGluZ2xhbmd1YWdlIGZyb21I eXBlcmlvbiBTb2x1dGlvbnMuIA0KCUNjOiANCglTdWJqZWN0OiBSRTogW3Nx ci11c2Vyc10gRXJyb3Igd3JpdGluZyB0byBmaWxlDQoJDQoJDQoNCglJJ2xs IGdvIHdpdGggNCwyOTQsOTY3LDI5NSAuLi4NCgkNCglMYW5jZSBELiBNdW5n ZXINCglSRjQgRGV2ZWxvcGVyDQoJMzAzLjkyNS4zMTIwIG9mZmljZQ0KCTcy MC4zNjQuNDcyMSBtb2JpbGUNCgkNCgktLS0tLU9yaWdpbmFsIE1lc3NhZ2Ut LS0tLQ0KCUZyb206IHNxci11c2Vycy1ib3VuY2VzK2xhbmNlLm11bmdlcj10 cTNuYXZpZ2FudC5jb21Ac3FydWcub3JnDQoJW21haWx0bzpzcXItdXNlcnMt Ym91bmNlcytsYW5jZS5tdW5nZXI9dHEzbmF2aWdhbnQuY29tQHNxcnVnLm9y Z10gT24NCglCZWhhbGYgT2YgTGFycnkgUm91eA0KCVNlbnQ6IFR1ZXNkYXks IEphbnVhcnkgMDQsIDIwMDUgMTowNiBQTQ0KCVRvOiBzcXItdXNlcnNAc3Fy dWcub3JnDQoJU3ViamVjdDogUkU6IFtzcXItdXNlcnNdIEVycm9yIHdyaXRp bmcgdG8gZmlsZQ0KCQ0KCUl0IGhhcyBiZWVuIGEgbG9uZyB0aW1lLi4uLndp dGhvdXQgdXNpbmcgYSBjYWxjdWxhdG9yOiAyOTksNDUxLDY0OD8gQnV0DQoJ SSB0aGluayBpdCBpcyArLy0gMTQ5LDcyNSw4MjQgZm9yIGluIGludGVnZXIg LSBMZXNzIGZvciBhIGZsb2F0Lg0KCQ0KCQ0KCQ0KCQ0KCQ0KCUxhcnJ5IFJv dXgNCglTeXJhY3VzZSBVbml2ZXJzaXR5DQoJbHJvdXhAc3lyLmVkdQ0KCQ0K CQ0KCT4+PiBLbmFwcFJAdW1zeXN0ZW0uZWR1IDEvNC8yMDA1IDI6NTU6MDkg UE0gPj4+DQoJDQoJDQoJTXVzdCA9IG1hbmRhdG9yeSwgbm8/DQoJDQoJVG9k YXkncyBwcm9ncmFtbWVyIHF1aXo6DQoJV2hhdCBpcyB0aGUgbGFyZ2VzdCB2 YWx1ZSB0aGF0IGNhbiBiZSBzdG9yZWQgaW4gNCBieXRlcz8NCgkNCglSaWNo YXJkIEtuYXBwDQoJRGF0YWJhc2UgUHJvZ3JhbW1lci9BbmFseXN0DQoJSW5z dGl0dXRpb25hbCBSZXNlYXJjaCBhbmQgUGxhbm5pbmcNCglVbml2ZXJzaXR5 IG9mIE1pc3NvdXJpIFN5c3RlbQ0KCTU3My04ODItODg1Ng0KCWtuYXBwckB1 bXN5c3RlbS5lZHUNCgkNCgktLS0tLU9yaWdpbmFsIE1lc3NhZ2UtLS0tLQ0K CUZyb206IHNxci11c2Vycy1ib3VuY2VzK2tuYXBwcj11bXN5c3RlbS5lZHVA c3FydWcub3JnDQoJW21haWx0bzpzcXItdXNlcnMtYm91bmNlcytrbmFwcHI9 dW1zeXN0ZW0uZWR1QHNxcnVnLm9yZ10gT24gQmVoYWxmIE9mDQoJS3Jpc2hu YSBQcmFzYWQgS29tbXVyaQ0KCVNlbnQ6IFR1ZXNkYXksIEphbnVhcnkgMDQs IDIwMDUgMTo0OSBQTQ0KCVRvOiBzcXItdXNlcnNAc3FydWcub3JnDQoJU3Vi amVjdDogW3Nxci11c2Vyc10gRXJyb3Igd3JpdGluZyB0byBmaWxlDQoJDQoJ SGksDQoJDQoJICAgICAgIFdoZW4gaSB0cnkgdG8gd3JpdGUgYSBudW1lcmlj IHZhcmlhYmxlIG9mIGxlbmd0aCA2LCBpdCBnaXZlcyBtZQ0KCXRoZSBmb2xs b3dpbmcgZXJyb3IuIChTUVIgMzIxMSkgI051bWVyaWMgdmFyaWFibGVzIGFu ZCBsaXRlcmFscyBtdXN0DQoJaGF2ZSA6bGVuZ3RoIG9mIDEsIDIgb3IgNCBi eXRlcy4NCgkNCgkgICAgICAgIElzIGl0IG1hbmRhdG9yeSB0byBoYXZlIGEg bnVtZXJpYyB2YXJpYWJsZSBvZiB0aGUgc3BlY2lmaWVkDQoJbGVuZ3RoICgx LDIgb3IgNCk/DQoJDQoJVGhhbmtzLA0KCQ0KCV9fX19fX19fX19fX19fX19f X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fDQoJc3FyLXVzZXJzIG1h aWxpbmcgbGlzdA0KCXNxci11c2Vyc0BzcXJ1Zy5vcmcNCglodHRwOi8vd3d3 LnNxcnVnLm9yZy9tYWlsbWFuL2xpc3RpbmZvL3Nxci11c2Vycw0KCQ0KCV9f X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19f DQoJc3FyLXVzZXJzIG1haWxpbmcgbGlzdA0KCXNxci11c2Vyc0BzcXJ1Zy5v cmcNCglodHRwOi8vd3d3LnNxcnVnLm9yZy9tYWlsbWFuL2xpc3RpbmZvL3Nx ci11c2Vycw0KCQ0KCV9fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19f X19fX19fX19fX19fX19fDQoJc3FyLXVzZXJzIG1haWxpbmcgbGlzdA0KCXNx ci11c2Vyc0BzcXJ1Zy5vcmcNCglodHRwOi8vd3d3LnNxcnVnLm9yZy9tYWls bWFuL2xpc3RpbmZvL3Nxci11c2Vycw0KCQ0KCV9fX19fX19fX19fX19fX19f X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fDQoJc3FyLXVzZXJzIG1h aWxpbmcgbGlzdA0KCXNxci11c2Vyc0BzcXJ1Zy5vcmcNCglodHRwOi8vd3d3 LnNxcnVnLm9yZy9tYWlsbWFuL2xpc3RpbmZvL3Nxci11c2Vycw0KCQ0KCQ0K DQo= ------_=_NextPart_001_01C4F29C.4C7AA755 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'application/ms-tnef' MIME type. It has been automatically discarded before sending the message to the list. ------_=_NextPart_001_01C4F29C.4C7AA755 Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ------_=_NextPart_001_01C4F29C.4C7AA755-- From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Jan 04 15:41:19 2005 From: "Alexander, Steve" Subject: RE: [sqr-users] Error writing to file Date: Tue, 4 Jan 2005 12:39:30 -0800 Do you really want to write the number as a six byte binary number (an integer up to 282 trillion)? Or do you want to write a six digit number, like 123456? The latter is done like so: let $number = edit(#number, '999999') write 1 from $number:6 -----Original Message----- From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org [mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org]On Behalf Of Murty Mirthipati Venkata Suryanarayan Sent: Tuesday, January 04, 2005 12:02 PM To: This list is for discussion about the SQR database reportinglanguage fromHyperion Solutions. Subject: RE: [sqr-users] Error writing to file I doubt if you are attempting to write the number directly to file. This seems to be code level error. Not related to the dabase level (with field sizes and all) Why not you post the portion of code that you are facing error with? Murty ;) -----Original Message----- From: Krishna Prasad Kommuri [mailto:kommuri@gmail.com] Sent: Tue 04-Jan-2005 14:48 To: sqr-users@sqrug.org Cc: Subject: [sqr-users] Error writing to file Hi, When i try to write a numeric variable of length 6, it gives me the following error. (SQR 3211) #Numeric variables and literals must have :length of 1, 2 or 4 bytes. Is it mandatory to have a numeric variable of the specified length (1,2 or 4)? Thanks, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Jan 04 15:59:18 2005 From: "the dragon" Subject: RE: [sqr-users] Error writing to file Date: Tue, 04 Jan 2005 14:57:09 -0600 Turn all numbers into character fields, using an edit mask to get around this. peace, clark 'the dragon' willis PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away. "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." Carl Sandburg (1878 - 1967) ----Original Message Follows---- Hi, When i try to write a numeric variable of length 6, it gives me the following error. (SQR 3211) #Numeric variables and literals must have :length of 1, 2 or 4 bytes. Is it mandatory to have a numeric variable of the specified length (1,2 or 4)? Thanks, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 10:02:41 2005 From: Bob Stone Subject: RE: [sqr-users] Error writing to file Date: Wed, 5 Jan 2005 09:01:21 -0600 I concur. 4,294,967,295 is the biggest (thats 4,294,967,296 numbers including 0). And if it was signed it would be somewhere between (2,147,483,647, -2,147,483,647) including both a positive and negative 0 (which is just silly). So if you're saying you have a number larger than that which you are trying to print, then you need to use an 8byte number. Dont confusing the number of DIGITS with the number of BYTES...bytes holds lots of digits. My guess is that you have some sort of syntax wrong where YOU think that you're helping it by telling it it's six digits, when in fact you're telling it that it should be 6 bytes. If you want it to print 6 digits every time use an edit mask (like printing the number 123 would be 000123 using 'print #var (+1,1) edit 099999'). Otherwise just print normally. -----Original Message----- I'll go with 4,294,967,295 ... Lance D. Munger -----Original Message----- [mailto:sqr-users-bounces+lance.munger=tq3navigant.com@sqrug.org] On Behalf Of Larry Roux It has been a long time....without using a calculator: 299,451,648? But I think it is +/- 149,725,824 for in integer - Less for a float. >>> KnappR@umsystem.edu 1/4/2005 2:55:09 PM >>> Must = mandatory, no? Today's programmer quiz: What is the largest value that can be stored in 4 bytes? -----Original Message----- Hi, When i try to write a numeric variable of length 6, it gives me the following error. (SQR 3211) #Numeric variables and literals must have :length of 1, 2 or 4 bytes. Is it mandatory to have a numeric variable of the specified length (1,2 or 4)? Thanks, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 11:04:01 2005 Date: Wed, 5 Jan 2005 10:02:48 -0600 From: Krishna Prasad Kommuri Subject: [sqr-users] Printing report in Landscape Hi All, Could anyone suggest me/give code for printing a report in LANDSCAPE. I tried this code which gives the layout in landscape but while printing the report it is choosing potrait. begin-setup declare-layout default paper-size = (11,8.50) orientation = landscape top-margin = 0.75 bottom-margin = 0.5 left-margin = 0.5 right-margin = 0.5 char-width = 5 end-declare !------------------------------------>I tried doing this but it didnt work out !------------------------------------>so commented these lines. !declare-printer default-hp ! point-size = 5 ! font = 5 !end-declare !declare-report land ! layout = land1 ! printer-type = hp !end-declare end-setup Ant help is appreciated. Thanks in advance. Bye _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 14:26:59 2005 Subject: RE: [sqr-users] Printing report in Landscape Date: Thu, 6 Jan 2005 01:01:33 +0530 From: "Murty Mirthipati Venkata Suryanarayan" This is a multi-part message in MIME format... ------_=_NextPart_001_01C4F35D.29F56BC7 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 UGFwZXItc2l6ZSBpcyB0aGUgcGFyYW1ldGVyIHRoYXQgZGV0ZXJtaW5lcyB0 aGUgbGF5b3V0IHNpemUuIFRyeSB3aXRoIHNvbWV0aGluZyBsaWtlICg4LjUs MTEpIGFuZCBzZWUgdGhlIHJlc3VsdC4NCiANCk11cnR5IDspDQoNCgktLS0t LU9yaWdpbmFsIE1lc3NhZ2UtLS0tLSANCglGcm9tOiBLcmlzaG5hIFByYXNh ZCBLb21tdXJpIFttYWlsdG86a29tbXVyaUBnbWFpbC5jb21dIA0KCVNlbnQ6 IFdlZCAwNS1KYW4tMjAwNSAxMTowMiANCglUbzogc3FyLXVzZXJzQHNxcnVn Lm9yZyANCglDYzogDQoJU3ViamVjdDogW3Nxci11c2Vyc10gUHJpbnRpbmcg cmVwb3J0IGluIExhbmRzY2FwZQ0KCQ0KCQ0KDQoJSGkgQWxsLA0KCUNvdWxk IGFueW9uZSBzdWdnZXN0IG1lL2dpdmUgY29kZSBmb3IgcHJpbnRpbmcgYSBy ZXBvcnQgaW4gTEFORFNDQVBFLg0KCUkgdHJpZWQgdGhpcyBjb2RlIHdoaWNo IGdpdmVzIHRoZSBsYXlvdXQgaW4gbGFuZHNjYXBlIGJ1dCB3aGlsZQ0KCXBy aW50aW5nIHRoZSByZXBvcnQgaXQgaXMgY2hvb3NpbmcgcG90cmFpdC4NCgkN CgliZWdpbi1zZXR1cA0KCSAgZGVjbGFyZS1sYXlvdXQgZGVmYXVsdA0KCSAg ICAgcGFwZXItc2l6ZSAgPSAoMTEsOC41MCkNCgkgICAgIG9yaWVudGF0aW9u ICA9IGxhbmRzY2FwZQ0KCSAgICAgdG9wLW1hcmdpbiAgID0gMC43NQ0KCSAg ICAgYm90dG9tLW1hcmdpbiAgPSAwLjUNCgkgICAgIGxlZnQtbWFyZ2luICA9 IDAuNQ0KCSAgICAgcmlnaHQtbWFyZ2luID0gMC41DQoJICAgICBjaGFyLXdp ZHRoICA9IDUNCgkgIGVuZC1kZWNsYXJlDQoJIS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLT5JIHRyaWVkIGRvaW5nIHRoaXMgYnV0IGl0 IGRpZG50DQoJd29yayBvdXQgIS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLT5zbyBjb21tZW50ZWQgdGhlc2UNCglsaW5lcy4NCgkgICFk ZWNsYXJlLXByaW50ZXIgZGVmYXVsdC1ocA0KCSAgISAgIHBvaW50LXNpemUg ID0gNQ0KCSAgISAgIGZvbnQgID0gNQ0KCSAgIWVuZC1kZWNsYXJlDQoJICAh ZGVjbGFyZS1yZXBvcnQgbGFuZA0KCSAgISAgIGxheW91dCAgPSBsYW5kMQ0K CSAgISAgIHByaW50ZXItdHlwZSA9IGhwDQoJICAhZW5kLWRlY2xhcmUNCgkg DQoJZW5kLXNldHVwDQoJQW50IGhlbHAgaXMgYXBwcmVjaWF0ZWQuDQoJVGhh bmtzIGluIGFkdmFuY2UuDQoJQnllDQoJDQoJX19fX19fX19fX19fX19fX19f X19fX19fX19fX19fX19fX19fX19fX19fX19fX18NCglzcXItdXNlcnMgbWFp bGluZyBsaXN0DQoJc3FyLXVzZXJzQHNxcnVnLm9yZw0KCWh0dHA6Ly93d3cu c3FydWcub3JnL21haWxtYW4vbGlzdGluZm8vc3FyLXVzZXJzDQoJDQoJDQoN Cg== ------_=_NextPart_001_01C4F35D.29F56BC7 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'application/ms-tnef' MIME type. It has been automatically discarded before sending the message to the list. ------_=_NextPart_001_01C4F35D.29F56BC7 Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users ------_=_NextPart_001_01C4F35D.29F56BC7-- From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 14:42:47 2005 Date: Wed, 05 Jan 2005 13:40:52 -0600 From: "Brooke Funk" Subject: RE: [sqr-users] Printing report in Landscape I find I have to use different font sizes to help direct whether to use landscape or portrait in addition to the paper size. I see you commented out font = 5. We use Kyocera printers and our print page font list says a font of 5 is for portrait but to use the same font but to do it as landscape is a 22. You might try something like that if just changing the paper size doesn't work. >>> murtym@hexaware.com 01/05/05 01:31PM >>> Paper-size is the parameter that determines the layout size. Try with something like (8.5,11) and see the result. Murty ;) -----Original Message----- From: Krishna Prasad Kommuri [mailto:kommuri@gmail.com] Sent: Wed 05-Jan-2005 11:02 To: sqr-users@sqrug.org Cc: Subject: [sqr-users] Printing report in Landscape Hi All, Could anyone suggest me/give code for printing a report in LANDSCAPE. I tried this code which gives the layout in landscape but while printing the report it is choosing potrait. begin-setup declare-layout default paper-size = (11,8.50) orientation = landscape top-margin = 0.75 bottom-margin = 0.5 left-margin = 0.5 right-margin = 0.5 char-width = 5 end-declare !------------------------------------>I tried doing this but it didnt work out !------------------------------------>so commented these lines. !declare-printer default-hp ! point-size = 5 ! font = 5 !end-declare !declare-report land ! layout = land1 ! printer-type = hp !end-declare end-setup Ant help is appreciated. Thanks in advance. Bye _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users NOTICE: This e-mail is intended solely for the use of the individual to whom it is addressed and may contain information that is privileged, confidential or otherwise exempt from disclosure. If the reader of this e-mail is not the intended recipient or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify us by replying to the original message at the listed e-mail address. Thank you. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 17:02:22 2005 Date: Wed, 5 Jan 2005 16:01:10 -0600 From: Krishna Prasad Kommuri Subject: [sqr-users] Concatenating report files? Hi, How do u concatenate two report files(.lis files that are generated when u execute the sqr) into one in sqr? Thanks, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 17:05:52 2005 Subject: RE: [sqr-users] Printing report in Landscape Date: Wed, 5 Jan 2005 16:03:57 -0600 From: "Lindquist, Maria" I would look at the delivered sqc - ptset02.sqc=20 Maria K Lindquist mlindqui@uwc.edu IS Programmer/Analyst - Business Office University of Wisconsin Colleges 780 Regent St, PO Box 8680, Madison, WI 53708-8680 Phone: 608.265.2677 / Fax: 608.265.5770 -----Original Message----- From: sqr-users-bounces+mlindqui=3Duwc.edu@sqrug.org [mailto:sqr-users-bounces+mlindqui=3Duwc.edu@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Wednesday, January 05, 2005 10:03 AM To: sqr-users@sqrug.org Subject: [sqr-users] Printing report in Landscape Hi All, Could anyone suggest me/give code for printing a report in LANDSCAPE. I tried this code which gives the layout in landscape but while printing the report it is choosing potrait. =20 begin-setup declare-layout default paper-size =3D (11,8.50) orientation =3D landscape top-margin =3D 0.75 bottom-margin =3D 0.5 left-margin =3D 0.5 right-margin =3D 0.5 char-width =3D 5 end-declare !------------------------------------>I tried doing this but it didnt work out !------------------------------------>so commented these lines. !declare-printer default-hp ! point-size =3D 5 ! font =3D 5 !end-declare !declare-report land ! layout =3D land1 ! printer-type =3D hp !end-declare =20=20 end-setup Ant help is appreciated. Thanks in advance. Bye _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 17:09:41 2005 Subject: RE: [sqr-users] Printing report in Landscape Date: Wed, 5 Jan 2005 16:04:40 -0600 From: "Lindquist, Maria" Or you can use this include in the beginning of your SQR #Include 'setup32.sqc' !printer and page-size initialization - LANDSCAPE=20 Maria K Lindquist mlindqui@uwc.edu IS Programmer/Analyst - Business Office University of Wisconsin Colleges 780 Regent St, PO Box 8680, Madison, WI 53708-8680 Phone: 608.265.2677 / Fax: 608.265.5770 -----Original Message----- From: sqr-users-bounces+mlindqui=3Duwc.edu@sqrug.org [mailto:sqr-users-bounces+mlindqui=3Duwc.edu@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Wednesday, January 05, 2005 10:03 AM To: sqr-users@sqrug.org Subject: [sqr-users] Printing report in Landscape Hi All, Could anyone suggest me/give code for printing a report in LANDSCAPE. I tried this code which gives the layout in landscape but while printing the report it is choosing potrait. =20 begin-setup declare-layout default paper-size =3D (11,8.50) orientation =3D landscape top-margin =3D 0.75 bottom-margin =3D 0.5 left-margin =3D 0.5 right-margin =3D 0.5 char-width =3D 5 end-declare !------------------------------------>I tried doing this but it didnt work out !------------------------------------>so commented these lines. !declare-printer default-hp ! point-size =3D 5 ! font =3D 5 !end-declare !declare-report land ! layout =3D land1 ! printer-type =3D hp !end-declare =20=20 end-setup Ant help is appreciated. Thanks in advance. Bye _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 17:24:51 2005 Subject: RE: [sqr-users] Printing report in Landscape Date: Wed, 5 Jan 2005 16:23:21 -0600 From: "Schaetzly, Kent" Based on your commented code, your declare-report refers to a layout called= "land1", however you called your layout "default". Try specifying "layout= =3D default" in your declare-report section and use uppercase "orientation= =3D LANDSCAPE" in your declare-layout section. -----Original Message----- From: sqr-users-bounces+kschaetzly=3Deprod.com@sqrug.org [mailto:sqr-users-bounces+kschaetzly=3Deprod.com@sqrug.org]On Behalf Of Brooke Funk Sent: Wednesday, January 05, 2005 1:41 PM To: sqr-users@sqrug.org Subject: RE: [sqr-users] Printing report in Landscape I find I have to use different font sizes to help direct whether to use landscape or portrait in addition to the paper size. I see you commented out font =3D 5. We use Kyocera printers and our print page font list says a font of 5 is for portrait but to use the same font but to do it as landscape is a 22. You might try something like that if just changing the paper size doesn't work. >>> murtym@hexaware.com 01/05/05 01:31PM >>> Paper-size is the parameter that determines the layout size. Try with something like (8.5,11) and see the result. =20 Murty ;) -----Original Message-----=20 From: Krishna Prasad Kommuri [mailto:kommuri@gmail.com]=20 Sent: Wed 05-Jan-2005 11:02=20 To: sqr-users@sqrug.org=20 Cc:=20 Subject: [sqr-users] Printing report in Landscape =09 =09 Hi All, Could anyone suggest me/give code for printing a report in LANDSCAPE. I tried this code which gives the layout in landscape but while printing the report it is choosing potrait. =09 begin-setup declare-layout default paper-size =3D (11,8.50) orientation =3D landscape top-margin =3D 0.75 bottom-margin =3D 0.5 left-margin =3D 0.5 right-margin =3D 0.5 char-width =3D 5 end-declare !------------------------------------>I tried doing this but it didnt work out !------------------------------------>so commented these lines. !declare-printer default-hp ! point-size =3D 5 ! font =3D 5 !end-declare !declare-report land ! layout =3D land1 ! printer-type =3D hp !end-declare =09=20 end-setup Ant help is appreciated. Thanks in advance. Bye =09 _______________________________________________ sqr-users mailing list sqr-users@sqrug.org=20 http://www.sqrug.org/mailman/listinfo/sqr-users=20 =09 =09 NOTICE: This e-mail is intended solely for the use of the=20 individual to whom it is addressed and may contain information=20 that is privileged, confidential or otherwise exempt from=20 disclosure. If the reader of this e-mail is not the intended=20 recipient or the employee or agent responsible for delivering=20 the message to the intended recipient, you are hereby notified=20 that any dissemination, distribution, or copying of this=20 communication is strictly prohibited. If you have received this=20 communication in error, please immediately notify us by replying=20 to the original message at the listed e-mail address.=20=20 Thank you. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 17:49:09 2005 Subject: RE: [sqr-users] sql-status Date: Wed, 5 Jan 2005 16:48:17 -0600 From: "Turner, Ivan" #sql-status is only good for begin-sql blocks it is not intended for begin-select. For begin-select, you must use a switch/counter variable that is initialized before the select. -----Original Message----- From: sqr-users-bounces+ivan.turner=3Dqwest.com@sqrug.org [mailto:sqr-users-bounces+ivan.turner=3Dqwest.com@sqrug.org] On Behalf Of George Jansen Sent: Monday, December 27, 2004 10:12 AM To: sqr-users@sqrug.org Subject: Re: [sqr-users] sql-status Something like this is awfully easy to test, isn't it? ************************************ Begin-Program Begin-Select Dummy &dummy >From Dual Where Dummy < 'X' End-Select Display 'No rows case:' Display #sql-status let $bad-identifier =3D 'SCOTT.123' Begin-Select On-Error=3Doops Nocol &nocol >From [$bad-identifier] End-Select End-Program Begin-Procedure oops Display 'Error case: ' Display #sql-status End-Procedure ************************ And yes, the values are database-dependent for the error case. >>> kommuri@gmail.com 12/26/04 9:50 AM >>> Hi, What will be the value in sql-status i) when no rows are fetched in the begin-select block ii) when an error occurs in begin-select? Does the return value depends on the underlying database? We are on Oracle. Thanks, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 18:09:14 2005 Date: Wed, 5 Jan 2005 17:07:54 -0600 From: Krishna Prasad Kommuri Subject: [sqr-users] Error Printing Report in Landscape Hello All, I have tried all alternatives that you guys suggested, but the output that i got was in this format. I really appreciate you guys for your suggestions. Thanks =20=20=20=20=20=20 =20 =1BE=1B&l1O=1B&k8.3333H=1B&l5C=1B(0N=1B(s1P=1B(s5V=1B(s22T =1B&a4L=1B&l5E=1B&a640v180H407930 =1B&a640v780H =1B&a640v930H100 =1B&a640v1530H =1B&a640v1680HAzevedo,Frank =1B&a640v3180H =1B&a640v3330H27-APR-2000 =1B&a640v3930H =1B&a640v4080H05-MAR-2001 =1B&a640v4680H =1B&a640v4830H5 =1B&a640v5430H =1B&a640v5580H05-MAR-2001 =1B&a800v280H =1B&a880v430HTERMINATED =1B&a960v180H1309708 =1B&a960v780H =1B&a960v930H009 =1B&a960v1530H =1B&a960v1680HBegic,Almira =1B&a960v3180H =1B&a960v3330H03-NOV-1999 =1B&a960v3930H =1B&a960v4080H10-MAY-2000 =1B&a960v4680H =1B&a960v4830H5 =1B&a960v5430H =1B&a960v5580H=20 =1B&a1120v280H =1B&a1200v430H26-JUL-2004 =1B&a1280v180H217645 =1B&a1280v780H =1B&a1280v930H100 =1B&a1280v1530H _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 18:18:13 2005 Date: Wed, 5 Jan 2005 17:17:08 -0600 From: Krishna Prasad Kommuri Subject: [sqr-users] Does command line flags affect landscape printing? Hi All Thanks...for your responses I tried including that but cloudnt get the result. Do I need to do any thing else apart from including? My output is something like:instead of getting in the same line.. =1BE=1B&l1O=1B&k8.3333H=1B&l5C=1B(0N=1B(s1P=1B(s5V=1B(s22T =1B&a4L=1B&l5E=1B&a640v180H407930 =1B&a640v780H =1B&a640v930H100 =1B&a640v1530H =1B&a640v1680HAzevedo,Frank =1B&a640v3180H =1B&a640v3330H27-APR-2000 =1B&a640v3930H =1B&a640v4080H05-MAR-2001 =1B&a640v4680H =1B&a640v4830H5 =1B&a640v5430H =1B&a640v5580H05-MAR-2001 =1B&a800v280H =1B&a880v430HTERMINATED =20 Do I need to specify any flags while Iam running through sqrw? Right now Iam giving: =20 N:\hr819830sp1\bin\sqrps\sqr\ora\BINW\sqrw.exe=20 -iN:\hr819830sp1\sqr\;N:\hr819830sp1\bin\sqrps\sqr\ora\BINW\=20 -zifN:\hr819830sp1\sqr\pssqr.ini -fC:\temp\test.lis=20=20=20=20=20=20=20=20 -printer:hp -oc:\temp\sqr.log _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 05 21:31:29 2005 From: "Steve Cavill" Subject: RE: [sqr-users] Does command line flags affect landscape printing? Date: Thu, 6 Jan 2005 13:29:24 +1100 That's HP PCL output only suitable for printing on HP printers. Is that what you actually want? Check the available options for the -printer: flag and check you have the right setting. Steve.=20 -----Original Message----- From: sqr-users-bounces+steve.cavill=3Dinfoclarity.com.au@sqrug.org [mailto:sqr-users-bounces+steve.cavill=3Dinfoclarity.com.au@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Thursday, 6 January 2005 10:17 AM To: sqr-users@sqrug.org Subject: [sqr-users] Does command line flags affect landscape printing? Hi All Thanks...for your responses I tried including that but cloudnt get the result. Do I need to do any thing else apart from including? My output is something like:instead of getting in the same line.. =1BE=1B&l1O=1B&k8.3333H=1B&l5C=1B(0N=1B(s1P=1B(s5V=1B(s22T =1B&a4L=1B&l5E=1B&a640v180H407930 =1B&a640v780H =1B&a640v930H100 =1B&a640v1530H =1B&a640v1680HAzevedo,Frank =1B&a640v3180H =1B&a640v3330H27-APR-2000 =1B&a640v3930H =1B&a640v4080H05-MAR-2001 =1B&a640v4680H =1B&a640v4830H5 =1B&a640v5430H =1B&a640v5580H05-MAR-2001 =1B&a800v280H =1B&a880v430HTERMINATED =20 Do I need to specify any flags while Iam running through sqrw? Right now Iam giving: =20 N:\hr819830sp1\bin\sqrps\sqr\ora\BINW\sqrw.exe -iN:\hr819830sp1\sqr\;N:\hr819830sp1\bin\sqrps\sqr\ora\BINW\ -zifN:\hr819830sp1\sqr\pssqr.ini -fC:\temp\test.lis=20=20=20=20=20=20=20=20 -printer:hp -oc:\temp\sqr.log _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Thu Jan 06 09:02:02 2005 Subject: RE: [sqr-users] Error Printing Report in Landscape Date: Thu, 6 Jan 2005 09:00:33 -0500 From: "Beller, Jay" The first 2 escape sequences shown are the proper codes for PCL in landscap= e mode: E is a reset; &l1O is the request for landscape. If this still isn't working, either your printer doesn't support PCL or som= ething in your environment (e.g., spooling process, printer definitions, et= c) is interfering with the printer receiving the binary data shown. What platform are you running on? -----Original Message----- From: sqr-users-bounces+jbeller=3Dlbisoftware.com@sqrug.org [mailto:sqr-users-bounces+jbeller=3Dlbisoftware.com@sqrug.org]On Behalf Of Krishna Prasad Kommuri Sent: Wednesday, January 05, 2005 6:08 PM To: sqr-users@sqrug.org Subject: [sqr-users] Error Printing Report in Landscape Hello All, I have tried all alternatives that you guys suggested, but the output that i got was in this format. I really appreciate you guys for your suggestions. Thanks =20=20=20=20=20=20 =20 =1BE=1B&l1O=1B&k8.3333H=1B&l5C=1B(0N=1B(s1P=1B(s5V=1B(s22T =1B&a4L=1B&l5E=1B&a640v180H407930 =1B&a640v780H =1B&a640v930H100 =1B&a640v1530H =1B&a640v1680HAzevedo,Frank =1B&a640v3180H =1B&a640v3330H27-APR-2000 =1B&a640v3930H =1B&a640v4080H05-MAR-2001 =1B&a640v4680H =1B&a640v4830H5 =1B&a640v5430H =1B&a640v5580H05-MAR-2001 =1B&a800v280H =1B&a880v430HTERMINATED =1B&a960v180H1309708 =1B&a960v780H =1B&a960v930H009 =1B&a960v1530H =1B&a960v1680HBegic,Almira =1B&a960v3180H =1B&a960v3330H03-NOV-1999 =1B&a960v3930H =1B&a960v4080H10-MAY-2000 =1B&a960v4680H =1B&a960v4830H5 =1B&a960v5430H =1B&a960v5580H=20 =1B&a1120v280H =1B&a1200v430H26-JUL-2004 =1B&a1280v180H217645 =1B&a1280v780H =1B&a1280v930H100 =1B&a1280v1530H _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Thu Jan 06 09:16:03 2005 Date: Thu, 06 Jan 2005 09:14:26 -0500 From: "George Jansen" Subject: RE: [sqr-users] sql-status Perhaps you are thinking of #sql-count? #sql-status will reliably report status for queries. With Oracle, it will give a 0 for successful queries and a negative number for errors. A query retrieving no rows is successful, for an empty relation is as we all recall a legitimate relation. >>> Ivan.Turner@qwest.com 1/5/2005 5:48:17 PM >>> #sql-status is only good for begin-sql blocks it is not intended for begin-select. For begin-select, you must use a switch/counter variable that is initialized before the select. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Thu Jan 06 11:27:16 2005 Subject: RE: [sqr-users] Does command line flags affect landscape printing? Date: Thu, 6 Jan 2005 10:26:09 -0600 From: "Lindquist, Maria" I would like to see the section in the program where you have your PRINT lines.=20 Maria K Lindquist mlindqui@uwc.edu IS Programmer/Analyst - Business Office University of Wisconsin Colleges 780 Regent St, PO Box 8680, Madison, WI 53708-8680 Phone: 608.265.2677 / Fax: 608.265.5770 -----Original Message----- From: sqr-users-bounces+mlindqui=3Duwc.edu@sqrug.org [mailto:sqr-users-bounces+mlindqui=3Duwc.edu@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Wednesday, January 05, 2005 5:17 PM To: sqr-users@sqrug.org Subject: [sqr-users] Does command line flags affect landscape printing? Hi All Thanks...for your responses I tried including that but cloudnt get the result. Do I need to do any thing else apart from including? My output is something like:instead of getting in the same line.. =1BE=1B&l1O=1B&k8.3333H=1B&l5C=1B(0N=1B(s1P=1B(s5V=1B(s22T =1B&a4L=1B&l5E=1B&a640v180H407930 =1B&a640v780H =1B&a640v930H100 =1B&a640v1530H =1B&a640v1680HAzevedo,Frank =1B&a640v3180H =1B&a640v3330H27-APR-2000 =1B&a640v3930H =1B&a640v4080H05-MAR-2001 =1B&a640v4680H =1B&a640v4830H5 =1B&a640v5430H =1B&a640v5580H05-MAR-2001 =1B&a800v280H =1B&a880v430HTERMINATED =20 Do I need to specify any flags while Iam running through sqrw? Right now Iam giving: =20 N:\hr819830sp1\bin\sqrps\sqr\ora\BINW\sqrw.exe -iN:\hr819830sp1\sqr\;N:\hr819830sp1\bin\sqrps\sqr\ora\BINW\ -zifN:\hr819830sp1\sqr\pssqr.ini -fC:\temp\test.lis=20=20=20=20=20=20=20=20 -printer:hp -oc:\temp\sqr.log _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Mon Jan 10 23:51:26 2005 Date: Tue, 11 Jan 2005 12:50:12 +0800 (CST) From: chek hong Subject: [sqr-users] how to call sqr program in Win2k from Unix I have an Unix WEB/Application server and a Win2K sqr report server. How do I call or execute a sqr program in Win2k server from the Unix server besides using the unix command rexec ? Is there an unix version for SQR ? thanks Yahoo! Mobile - Download the latest ringtones, games, and more! _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Jan 11 01:31:55 2005 Date: Mon, 10 Jan 2005 22:30:18 -0800 (PST) From: G Azevedo Subject: [sqr-users] How to call sqr program in Win2k from Unix There definitely is a Unix version of SQR (HP-UX at least). I'm sure others can give more details since I only know this from past experience, not currently on this platform. I can't offer anymore details on how to address your request however. ============================================================== Greg G. Azevedo IT & Business Professional o IT Consulting o Business Analysis & Reporting o Customer & Applications Support o Data Warehouse Analyst Email: azevedogg@sbcglobal.net Serving customers and partners is paramount ============================================================== --- chek hong wrote: > I have an Unix WEB/Application server and a Win2K sqr report > server. How do I call or execute a sqr program in Win2k server > from the Unix server besides using the unix command rexec ? > > Is there an unix version for SQR ? > > thanks > > Yahoo! Mobile > - Download the latest ringtones, games, and more! > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Jan 11 07:04:33 2005 From: "the dragon" Subject: RE: [sqr-users] how to call sqr program in Win2k from Unix Date: Tue, 11 Jan 2005 06:02:51 -0600 Yes, there is a Unix version of sqr, and that iswhere you should be running your sqr reports. Unix is more secure, faster and more stabile than Windoze products. However, you should, depending on your volume and load, get another Unix server/lpar for your batch processes. If you're using OraSoft, you should be able to configure the PIA to look for the sqrs on the batch server. peace, clark 'the dragon' willis PSA: Salary <> Slavery. If you earn a salary, your employer is renting your services for 40 hours a week, not purchasing your soul. Your time is the only real finite asset that you have, and once used it can never be recovered, so don't waste it by giving it away. "Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." Carl Sandburg (1878 - 1967) ----Original Message Follows---- I have an Unix WEB/Application server and a Win2K sqr report server. How do I call or execute a sqr program in Win2k server from the Unix server besides using the unix command rexec ? Is there an unix version for SQR ? thanks _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 12 04:41:52 2005 Date: Wed, 12 Jan 2005 15:13:47 +0530 From: "Karunakaran Perumbrayoor" Subject: [sqr-users] Reg Reading FileNames from RemoteServer using PeopleCode Hi All, My Query is connected with PeopleCode. Incase U Guys have an idea = abt this, please respond. I have been trying all possible ways to get a goo= d solution. I am actually writing an AE Program to read the FileNames present in a Remo= teServer Path.=20 I was thinking of using FindFiles PeopleCode Function which fetches the Fil= eNames. But this is not possible without mapping the Drive. Because these Files are present in a Remote Server, I need to pass the User= ID, PassWord etc to access the RemoteServer Path. Once the File Read is successful, I will be storing all these File Names in= a Custom Table. I have to run a process which will FTP all the Files (prom= pted from the Custom Table in the Run Control Page) from the remote server = and load them into the Local Server (FileServer). But that is working... an= d there seems to be no problem with that. The Problem, I am facing at the moment is "Reading the FileNames" from the = Remote Server. I tried using Exec and provided a script for execution... Bu= t it isnt working either. Is there any way by which I can read the FileName= s from Remote Server Path... At present I am trying to use MapNetworkDrive Function present in NetworkDr= iveMapping DLL File. Once the mapping is successful, I think I might be abl= e to use FindFiles... Hope to get some kind of a suggestion or advices from U, on how to proceed = from here on.. Regards... Karun _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 12 08:10:33 2005 Date: Wed, 12 Jan 2005 08:08:17 -0500 From: "George Jansen" Subject: Re: [sqr-users] Reg Reading FileNames from RemoteServer using PeopleCode "I am writing instructions for my assistant to drive in nails with a screwdriver. Everything's working great, except it's slow, and he gets a sore wrist. Any suggestions for how he might hold the screwdriver differently?" Maybe you need a different tool? _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 12 09:28:04 2005 From: "Ed Kelly" Date: Wed, 12 Jan 2005 14:25:57 +0000 Subject: [sqr-users] SQR Long Names For some reason, SQR is not creating long file names. I have modified theINI file to explicitly define the mode as long. Anyone have an idea what else I could try? SQR for PeopleSoft/8.20.13/PC/Windows NT 4.0/Oracle 8.0.6/Jul 19 2004 The following settings are in both the SQR.INI file in the windows folder and the PSSQR.INI file: [Default Settings] AllowDateAsChar=TRUE output-file-mode=long OutputTwoDigitYearWarningMsg=FALSE UseY2KCenturyAlgorithm=TRUE UseUnicodeInternal=TRUE AutoDetectUnicodeFiles=TRUE _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 12 09:56:26 2005 From: "Greg Devereaux" Subject: Re: [sqr-users] SQR Long Names Date: Wed, 12 Jan 2005 09:56:34 -0500 Hi Ed, Are you using NTFS or FAT files system with NT? Fat filesystems typically do not support the long filenames properly as compared to NTFS. Greg. ----- Original Message ----- From: "Ed Kelly" To: Sent: Wednesday, January 12, 2005 9:25 AM Subject: [sqr-users] SQR Long Names > For some reason, SQR is not creating long file names. I have modified > theINI file to explicitly define the mode as long. Anyone have an idea > what else I could try? > > > > SQR for PeopleSoft/8.20.13/PC/Windows NT 4.0/Oracle 8.0.6/Jul 19 2004 > > > The following settings are in both the SQR.INI file in the windows folder > and the PSSQR.INI file: > > [Default Settings] > AllowDateAsChar=TRUE > output-file-mode=long > OutputTwoDigitYearWarningMsg=FALSE > UseY2KCenturyAlgorithm=TRUE > UseUnicodeInternal=TRUE > AutoDetectUnicodeFiles=TRUE > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 12 10:43:19 2005 From: "Ed Kelly" Subject: Re: [sqr-users] SQR Long Names Date: Wed, 12 Jan 2005 15:41:51 +0000 Greg, That could quite possibly be the case. However, since I did not configure this machine, how would I know for sure (i.e., what could I check in order to determine the file system in use)? Thanks, Ed >Hi Ed, > >Are you using NTFS or FAT files system with NT? >Fat filesystems typically do not support the long filenames properly as >compared to NTFS. > >Greg. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 12 10:51:29 2005 Subject: Re: [sqr-users] SQR Long Names From: CAllo@hinda.com Date: Wed, 12 Jan 2005 09:50:41 -0600 Check Disk Administrator on Windows NT/2K/XP. It will tell you the type of file system on the drive/volume in question. If you do not have access to this Disk Administrator, you may need to get whoever has "admin" access to the local machine, i.e. whoever set it up. Christopher Allo PeopleSoft Specialist x-3348 This communication is for the exclusive use of addressee and may contain confidential, proprietary or privileged information which may be used exclusively for its intended purpose. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication, and destroy all copies. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 12 11:00:05 2005 From: "Richard C Spoth" Date: Wed, 12 Jan 2005 11:01:14 -0500 Subject: [sqr-users] Is SQR V6.1.3 compatible with Sybase 12.5 Does anyone know if SQR V6.1.3 compatible with Sybase 12.5, both running on AIX 5.2? Is anyone using Sybase 12.5 with SQR V6.1.3? Rick CONFIDENTIALITY NOTICE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain proprietary, confidential, trade secret or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited and may be a violation of law. If you are not the intended recipient or a person responsible for delivering this message to an intended recipient, please contact the sender by reply email and destroy all copies of the original message. _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Jan 12 11:03:26 2005 From: "Greg Devereaux" Subject: Re: [sqr-users] SQR Long Names Date: Wed, 12 Jan 2005 11:02:59 -0500 Hi Ed, The disk administrator will show you which filesystem the disk was formatted with. You may require the administrator password to use it. You can convert a FAT filesystem to NTFS without a complete rebuild But not convert a NTFS filesystem back to FAT. To see options and syntax for convert do a convert /? in a command window or look it up in help. Let me know if you have any further questions. Greg. ----- Original Message ----- From: "Ed Kelly" To: Sent: Wednesday, January 12, 2005 10:41 AM Subject: Re: [sqr-users] SQR Long Names > Greg, > > That could quite possibly be the case. However, since I did not configure > this machine, how would I know for sure (i.e., what could I check in order > to determine the file system in use)? > > Thanks, > Ed > > >>Hi Ed, >> >>Are you using NTFS or FAT files system with NT? >>Fat filesystems typically do not support the long filenames properly as >>compared to NTFS. >> >>Greg. > > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Thu Jan 13 17:14:55 2005 Date: Thu, 13 Jan 2005 16:13:47 -0600 From: Krishna Prasad Kommuri Subject: [sqr-users] Variable Declaration Hi, How to declare the following decimal variables in SQR In COBOL: X PIC 9(10)V9(2) VALUE 0. Y PIC 9(10)V99 VALUE ZEROS. W PIC S9(4)V99 SIGN IS LEADING, SEPARATE. Z PIC S9(10)V9(2) USAGE COMP-3. Any help is appreciated. Thanks in advance, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Thu Jan 13 17:20:24 2005 From: Jeff.Steinfeldt@wellsfargo.com Subject: RE: [sqr-users] Variable Declaration Date: Thu, 13 Jan 2005 16:19:07 -0600 Are you running on a mainframe or are you importing an ebcdic file into Unix or Windows environment? Database is Oracle? -----Original Message----- From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Thursday, January 13, 2005 4:14 PM To: sqr-users@sqrug.org Subject: [sqr-users] Variable Declaration Hi, How to declare the following decimal variables in SQR In COBOL: X PIC 9(10)V9(2) VALUE 0. Y PIC 9(10)V99 VALUE ZEROS. W PIC S9(4)V99 SIGN IS LEADING, SEPARATE. Z PIC S9(10)V9(2) USAGE COMP-3. Any help is appreciated. Thanks in advance, _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Fri Jan 14 09:21:30 2005 Date: Fri, 14 Jan 2005 08:20:14 -0600 From: Krishna Prasad Kommuri Subject: [sqr-users] RE: Variable Declaration We are on Oracle and we operate on Windows Environment. We are converting COBOL program which were previous run on mainframe into SQR. Please let me know if you need further details. Thanks, On Fri, 14 Jan 2005 06:04:23 -0500, sqr-users-request@sqrug.org wrote: > Send sqr-users mailing list submissions to > sqr-users@sqrug.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://www.sqrug.org/mailman/listinfo/sqr-users > or, via email, send a message with subject or body 'help' to > sqr-users-request@sqrug.org > > You can reach the person managing the list at > sqr-users-owner@sqrug.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of sqr-users digest..." > > > Today's Topics: > > 1. Variable Declaration (Krishna Prasad Kommuri) > 2. RE: Variable Declaration (Jeff.Steinfeldt@wellsfargo.com) > > > > ---------- Forwarded message ---------- > From: Krishna Prasad Kommuri > To: sqr-users@sqrug.org > Date: Thu, 13 Jan 2005 16:13:47 -0600 > Subject: [sqr-users] Variable Declaration > Hi, > How to declare the following decimal variables in SQR > In COBOL: X PIC 9(10)V9(2) VALUE 0. > Y PIC 9(10)V99 VALUE ZEROS. > W PIC S9(4)V99 SIGN IS LEADING, SEPARATE. > Z PIC S9(10)V9(2) USAGE COMP-3. > > Any help is appreciated. > Thanks in advance, > > > > ---------- Forwarded message ---------- > From: Jeff.Steinfeldt@wellsfargo.com > To: sqr-users@sqrug.org > Date: Thu, 13 Jan 2005 16:19:07 -0600 > Subject: RE: [sqr-users] Variable Declaration > Are you running on a mainframe or are you importing an ebcdic file into Unix > or Windows environment? Database is Oracle? > > -----Original Message----- > From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org > [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On > Behalf Of Krishna Prasad Kommuri > Sent: Thursday, January 13, 2005 4:14 PM > To: sqr-users@sqrug.org > Subject: [sqr-users] Variable Declaration > > Hi, > How to declare the following decimal variables in SQR > In COBOL: X PIC 9(10)V9(2) VALUE 0. > Y PIC 9(10)V99 VALUE ZEROS. > W PIC S9(4)V99 SIGN IS LEADING, SEPARATE. > Z PIC S9(10)V9(2) USAGE COMP-3. > > Any help is appreciated. > Thanks in advance, > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > > > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Fri Jan 14 09:42:36 2005 From: Jeff.Steinfeldt@wellsfargo.com Subject: RE: [sqr-users] RE: Variable Declaration Date: Fri, 14 Jan 2005 08:41:25 -0600 Ok, that helps. I'll assume that you'll be pulling the COBOL files across via a BINARY FTP so the COMP-3 doesn't get pooched when translated. You really don't want to try to read this file directly from SQR. That COMP-3 field has to come across as a binary and will likely contain the binary zero. SQR doesn't handle those well, basically treating them as end-of-line characters and truncating the record. I suggest that you look into something called SQL*Loader which is an data loading utility provided by Oracle (with the install). You would use SQL*Loader to load the data into an Oracle table and then query that from your SQR program. Your Oracle DBA will be able to help set up the control file for SQL*Loader. Its been a while since I worked with mainframe COBOL so there's a certain amount of guessing here but I think your records will look something like this: xxxxxxxxxxxxyyyyyyyyyyyyywwwwwwwwzzzzzzzzzzzzzz 9999999999.99999999999.99-9999.999999999999999F Did they tell you the record length on the COBOL file? Is it 47? Must be more to the record than what you've included. -----Original Message----- From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Friday, January 14, 2005 8:20 AM To: sqr-users@sqrug.org Subject: [sqr-users] RE: Variable Declaration We are on Oracle and we operate on Windows Environment. We are converting COBOL program which were previous run on mainframe into SQR. Please let me know if you need further details. Thanks, On Fri, 14 Jan 2005 06:04:23 -0500, sqr-users-request@sqrug.org wrote: > Send sqr-users mailing list submissions to > sqr-users@sqrug.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://www.sqrug.org/mailman/listinfo/sqr-users > or, via email, send a message with subject or body 'help' to > sqr-users-request@sqrug.org > > You can reach the person managing the list at > sqr-users-owner@sqrug.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of sqr-users digest..." > > > Today's Topics: > > 1. Variable Declaration (Krishna Prasad Kommuri) > 2. RE: Variable Declaration (Jeff.Steinfeldt@wellsfargo.com) > > > > ---------- Forwarded message ---------- > From: Krishna Prasad Kommuri > To: sqr-users@sqrug.org > Date: Thu, 13 Jan 2005 16:13:47 -0600 > Subject: [sqr-users] Variable Declaration > Hi, > How to declare the following decimal variables in SQR > In COBOL: X PIC 9(10)V9(2) VALUE 0. > Y PIC 9(10)V99 VALUE ZEROS. > W PIC S9(4)V99 SIGN IS LEADING, SEPARATE. > Z PIC S9(10)V9(2) USAGE COMP-3. > > Any help is appreciated. > Thanks in advance, > > > > ---------- Forwarded message ---------- > From: Jeff.Steinfeldt@wellsfargo.com > To: sqr-users@sqrug.org > Date: Thu, 13 Jan 2005 16:19:07 -0600 > Subject: RE: [sqr-users] Variable Declaration > Are you running on a mainframe or are you importing an ebcdic file > into Unix or Windows environment? Database is Oracle? > > -----Original Message----- > From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org > [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On > Behalf Of Krishna Prasad Kommuri > Sent: Thursday, January 13, 2005 4:14 PM > To: sqr-users@sqrug.org > Subject: [sqr-users] Variable Declaration > > Hi, > How to declare the following decimal variables in SQR > In COBOL: X PIC 9(10)V9(2) VALUE 0. > Y PIC 9(10)V99 VALUE ZEROS. > W PIC S9(4)V99 SIGN IS LEADING, SEPARATE. > Z PIC S9(10)V9(2) USAGE COMP-3. > > Any help is appreciated. > Thanks in advance, > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > > > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Fri Jan 14 11:40:59 2005 From: "Alexander, Steve" Subject: RE: [sqr-users] RE: Variable Declaration Date: Fri, 14 Jan 2005 08:39:12 -0800 Is Krishna asking how to import data that has been formatted in a specific way, or how to declare variables that have a specific number of digits? I thought it was the latter. SQR usually does not require declarations for string or numeric variables. If numbers have to be formatted for output, we use the edit() function to convert them to a string. -----Original Message----- From: Jeff.Steinfeldt@wellsfargo.com [mailto:Jeff.Steinfeldt@wellsfargo.com] Sent: Friday, January 14, 2005 6:41 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] RE: Variable Declaration Ok, that helps. I'll assume that you'll be pulling the COBOL files across via a BINARY FTP so the COMP-3 doesn't get pooched when translated. You really don't want to try to read this file directly from SQR. That COMP-3 field has to come across as a binary and will likely contain the binary zero. SQR doesn't handle those well, basically treating them as end-of-line characters and truncating the record. I suggest that you look into something called SQL*Loader which is an data loading utility provided by Oracle (with the install). You would use SQL*Loader to load the data into an Oracle table and then query that from your SQR program. Your Oracle DBA will be able to help set up the control file for SQL*Loader. Its been a while since I worked with mainframe COBOL so there's a certain amount of guessing here but I think your records will look something like this: xxxxxxxxxxxxyyyyyyyyyyyyywwwwwwwwzzzzzzzzzzzzzz 9999999999.99999999999.99-9999.999999999999999F Did they tell you the record length on the COBOL file? Is it 47? Must be more to the record than what you've included. -----Original Message----- From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Friday, January 14, 2005 8:20 AM To: sqr-users@sqrug.org Subject: [sqr-users] RE: Variable Declaration We are on Oracle and we operate on Windows Environment. We are converting COBOL program which were previous run on mainframe into SQR. Please let me know if you need further details. Thanks, On Fri, 14 Jan 2005 06:04:23 -0500, sqr-users-request@sqrug.org wrote: > Send sqr-users mailing list submissions to > sqr-users@sqrug.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://www.sqrug.org/mailman/listinfo/sqr-users > or, via email, send a message with subject or body 'help' to > sqr-users-request@sqrug.org > > You can reach the person managing the list at > sqr-users-owner@sqrug.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of sqr-users digest..." > > > Today's Topics: > > 1. Variable Declaration (Krishna Prasad Kommuri) > 2. RE: Variable Declaration (Jeff.Steinfeldt@wellsfargo.com) > > > > ---------- Forwarded message ---------- > From: Krishna Prasad Kommuri > To: sqr-users@sqrug.org > Date: Thu, 13 Jan 2005 16:13:47 -0600 > Subject: [sqr-users] Variable Declaration > Hi, > How to declare the following decimal variables in SQR > In COBOL: X PIC 9(10)V9(2) VALUE 0. > Y PIC 9(10)V99 VALUE ZEROS. > W PIC S9(4)V99 SIGN IS LEADING, SEPARATE. > Z PIC S9(10)V9(2) USAGE COMP-3. > > Any help is appreciated. > Thanks in advance, > > > > ---------- Forwarded message ---------- > From: Jeff.Steinfeldt@wellsfargo.com > To: sqr-users@sqrug.org > Date: Thu, 13 Jan 2005 16:19:07 -0600 > Subject: RE: [sqr-users] Variable Declaration > Are you running on a mainframe or are you importing an ebcdic file > into Unix or Windows environment? Database is Oracle? > > -----Original Message----- > From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org > [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On > Behalf Of Krishna Prasad Kommuri > Sent: Thursday, January 13, 2005 4:14 PM > To: sqr-users@sqrug.org > Subject: [sqr-users] Variable Declaration > > Hi, > How to declare the following decimal variables in SQR > In COBOL: X PIC 9(10)V9(2) VALUE 0. > Y PIC 9(10)V99 VALUE ZEROS. > W PIC S9(4)V99 SIGN IS LEADING, SEPARATE. > Z PIC S9(10)V9(2) USAGE COMP-3. > > Any help is appreciated. > Thanks in advance, > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > > > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Fri Jan 14 11:48:13 2005 From: Jeff.Steinfeldt@wellsfargo.com Subject: RE: [sqr-users] RE: Variable Declaration Date: Fri, 14 Jan 2005 10:46:39 -0600 Well, true, he asked how to declare the variables but the short answer is that you can't declare anything in SQR that will allow you to read a file containing a COBOL COMP-3 data type. File either needs to be pure ASCII to read from SQR or must be converted to compatible data types in some other fashion. -----Original Message----- From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On Behalf Of Alexander, Steve Sent: Friday, January 14, 2005 10:39 AM To: 'This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions.' Subject: RE: [sqr-users] RE: Variable Declaration Is Krishna asking how to import data that has been formatted in a specific way, or how to declare variables that have a specific number of digits? I thought it was the latter. SQR usually does not require declarations for string or numeric variables. If numbers have to be formatted for output, we use the edit() function to convert them to a string. -----Original Message----- From: Jeff.Steinfeldt@wellsfargo.com [mailto:Jeff.Steinfeldt@wellsfargo.com] Sent: Friday, January 14, 2005 6:41 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] RE: Variable Declaration Ok, that helps. I'll assume that you'll be pulling the COBOL files across via a BINARY FTP so the COMP-3 doesn't get pooched when translated. You really don't want to try to read this file directly from SQR. That COMP-3 field has to come across as a binary and will likely contain the binary zero. SQR doesn't handle those well, basically treating them as end-of-line characters and truncating the record. I suggest that you look into something called SQL*Loader which is an data loading utility provided by Oracle (with the install). You would use SQL*Loader to load the data into an Oracle table and then query that from your SQR program. Your Oracle DBA will be able to help set up the control file for SQL*Loader. Its been a while since I worked with mainframe COBOL so there's a certain amount of guessing here but I think your records will look something like this: xxxxxxxxxxxxyyyyyyyyyyyyywwwwwwwwzzzzzzzzzzzzzz 9999999999.99999999999.99-9999.999999999999999F Did they tell you the record length on the COBOL file? Is it 47? Must be more to the record than what you've included. -----Original Message----- From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Friday, January 14, 2005 8:20 AM To: sqr-users@sqrug.org Subject: [sqr-users] RE: Variable Declaration We are on Oracle and we operate on Windows Environment. We are converting COBOL program which were previous run on mainframe into SQR. Please let me know if you need further details. Thanks, On Fri, 14 Jan 2005 06:04:23 -0500, sqr-users-request@sqrug.org wrote: > Send sqr-users mailing list submissions to > sqr-users@sqrug.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://www.sqrug.org/mailman/listinfo/sqr-users > or, via email, send a message with subject or body 'help' to > sqr-users-request@sqrug.org > > You can reach the person managing the list at > sqr-users-owner@sqrug.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of sqr-users digest..." > > > Today's Topics: > > 1. Variable Declaration (Krishna Prasad Kommuri) > 2. RE: Variable Declaration (Jeff.Steinfeldt@wellsfargo.com) > > > > ---------- Forwarded message ---------- > From: Krishna Prasad Kommuri > To: sqr-users@sqrug.org > Date: Thu, 13 Jan 2005 16:13:47 -0600 > Subject: [sqr-users] Variable Declaration > Hi, > How to declare the following decimal variables in SQR > In COBOL: X PIC 9(10)V9(2) VALUE 0. > Y PIC 9(10)V99 VALUE ZEROS. > W PIC S9(4)V99 SIGN IS LEADING, SEPARATE. > Z PIC S9(10)V9(2) USAGE COMP-3. > > Any help is appreciated. > Thanks in advance, > > > > ---------- Forwarded message ---------- > From: Jeff.Steinfeldt@wellsfargo.com > To: sqr-users@sqrug.org > Date: Thu, 13 Jan 2005 16:19:07 -0600 > Subject: RE: [sqr-users] Variable Declaration > Are you running on a mainframe or are you importing an ebcdic file > into Unix or Windows environment? Database is Oracle? > > -----Original Message----- > From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org > [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On > Behalf Of Krishna Prasad Kommuri > Sent: Thursday, January 13, 2005 4:14 PM > To: sqr-users@sqrug.org > Subject: [sqr-users] Variable Declaration > > Hi, > How to declare the following decimal variables in SQR > In COBOL: X PIC 9(10)V9(2) VALUE 0. > Y PIC 9(10)V99 VALUE ZEROS. > W PIC S9(4)V99 SIGN IS LEADING, SEPARATE. > Z PIC S9(10)V9(2) USAGE COMP-3. > > Any help is appreciated. > Thanks in advance, > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users > > > _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users _______________________________________________ sqr-users mailing list sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Fri Jan 14 13:10:09 2005 Date: Fri, 14 Jan 2005 11:09:26 -0700 From: kevin.reschenberg@sparkpath.com Subject: RE: [sqr-users] RE: Variable Declaration If you are rewriting these old COBOL programs in SQR, try to avoid using the equivalent of COMP and COMP-3 fields if you can! Just read and write numbers as normal strings and convert them to/from numbers as needed using normal SQR functions such as EDIT() and TO_NUMBER(), as Steve suggested. If you really, really need to read or write "mainframe" files directly, there are a few things to keep in mind. As Jeff pointed out, you must FTP the input file without using the ASCII/CRLF options, which translate the data from EBCDIC to ASCII but will destroy any COMP or COMP-3 data. You need an exact copy of the file without translation. However, that still leaves you with two problems. First, all text fields will still be in EBCDIC (assuming the file is from an EBCDIC-based mainframe) and your SQR program must translate them to ASCII. That's not too difficult. The second problem has to do with the binary COMP and packed COMP-3 fields. Again, as Jeff mentioned, these will contain binary zero bytes and therefore cannot be read by SQR as strings. The secret to dealing with these is to read the file, one character at a time, reading each character into a numeric #variable. (Go back and reread the first sentence of this email before continuing.) Open the file as RECORD=1:FIXED-NOLF . Then READ 1 INTO #CHAR:1 to get each character, which could be a 0 or any other number. If it's a binary field you are handling, do the math to get the value of the entire field. If it's packed (COMP-3) or zoned (PIC S999 DISPLAY), you need to pay special attention to the last byte, which encodes the sign. When I worked at PeopleSoft I created an SQC containing procedures for reading and writing binary, packed, zoned and EBCDIC text fields, but it's probably lost by now. I just checked Tony DeLia's site and there are links to something similar, but the links seem to be broken. (Anybody know what happened to him?) It isn't too difficult to do, IF you have a good understanding of these formats on the mainframe and use the one-character numeric variable trick. Sorry about this. If Jeff's idea about loading the data into a table works for you, that's far better than trying to code this! Kevin Reschenberg SparkPath Technologies, Inc. sqr-info.com www.sparkpath.com www.sqr-info.com -------- Original Message -------- Subject: RE: [sqr-users] RE: Variable Declaration From: Jeff.Steinfeldt@wellsfargo.com Date: Fri, January 14, 2005 8:46 am To: sqr-users@sqrug.org Well, true, he asked how to declare the variables but the short answer is that you can't declare anything in SQR that will allow you to read a file containing a COBOL COMP-3 data type. File either needs to be pure ASCII to read from SQR or must be converted to compatible data types in some other fashion. -----Original Message----- From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On Behalf Of Alexander, Steve Sent: Friday, January 14, 2005 10:39 AM To: 'This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions.' Subject: RE: [sqr-users] RE: Variable Declaration Is Krishna asking how to import data that has been formatted in a specific way, or how to declare variables that have a specific number of digits? I thought it was the latter. SQR usually does not require declarations for string or numeric variables. If numbers have to be formatted for output, we use the edit() function to convert them to a string. -----Original Message----- From: Jeff.Steinfeldt@wellsfargo.com [mailto:Jeff.Steinfeldt@wellsfargo.com] Sent: Friday, January 14, 2005 6:41 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] RE: Variable Declaration Ok, that helps. I'll assume that you'll be pulling the COBOL files across via a BINARY FTP so the COMP-3 doesn't get pooched when translated. You really don't want to try to read this file directly from SQR. That COMP-3 field has to come across as a binary and will likely contain the binary zero. SQR doesn't handle those well, basically treating them as end-of-line characters and truncating the record. I suggest that you look into something called SQL*Loader which is an data loading utility provided by Oracle (with the install). You would use SQL*Loader to load the data into an Oracle table and then query that from your SQR program. Your Oracle DBA will be able to help set up the control file for SQL*Loader. Its been a while since I worked with mainframe COBOL so there's a certain amount of guessing here but I think your records will look something like this: xxxxxxxxxxxxyyyyyyyyyyyyywwwwwwwwzzzzzzzzzzzzzz 9999999999.99999999999.99-9999.999999999999999F Did they tell you the record length on the COBOL file? Is it 47? Must be more to the record than what you've included. -----Original Message----- From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On Behalf Of Krishna Prasad Kommuri Sent: Friday, January 14, 2005 8:20 AM To: sqr-users@sqrug.org Subject: [sqr-users] RE: Variable Declaration We are on Oracle and we operate on Windows Environment. We are converting COBOL program which were previous run on mainframe into SQR. Please let me know if you need further details. Thanks, On Fri, 14 Jan 2005 06:04:23 -0500, sqr-users-request@sqrug.org wrote: > Send sqr-users mailing list submissions to > sqr-users@sqrug.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://www.sqrug.org/mailman/listinfo/sqr-users > or, via email, send a message with subject or body 'help' to > sqr-users-request@sqrug.org > > You can reach the person managing the list at > sqr-users-owner@sqrug.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of sqr-users digest..." > > > Today's Topics: > > 1. Variable Declaration (Krishna Prasad Kommuri) > 2. RE: Variable Declaration (Jeff.Steinfeldt@wellsfargo.com) > > > > ---------- Forwarded message ---------- > From: Krishna Prasad Kommuri > To: sqr-users@sqrug.org > Date: Thu, 13 Jan 2005 16:13:47 -0600 > Subject: [sqr-users] Variable Declaration > Hi, > How to declare the following decimal variables in SQR > In COBOL: X PIC 9(10)V9(2) VALUE 0. > Y PIC 9(10)V99 VALUE ZEROS. > W PIC S9(4)V