From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Fri Oct 01 00:02:18 2004 Date: Fri, 1 Oct 2004 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 Fri Oct 01 11:14:24 2004 Date: Fri, 1 Oct 2004 12:12:19 -0400 (EDT) From: "Kevin Laroche" Subject: Re: [sqr-users] suggestions for best practicewith insertsinto temptable Hi David Alas, we are in the client server groove, with the idea that the users should be able to run a report when they want without our help. Thanks Kevin LaRoche oracle forms and SQR too _______________________________________________ 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 Oct 05 14:45:10 2004 From: Eduardo_Bugarin@hdm.honda.com Date: Tue, 5 Oct 2004 14:44:30 -0500 Subject: [sqr-users] insert sql hello everyone I need to insert data by sql, but I have the next message ORA-00984: COLUMN NOT ALLOWED HERE I know that this is about the fields on the Oracle table could some one tellme if this is correct: begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( {ARHDM}, $GROUP_ID, {ARHDM}, $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', IN THIS FIELD I NEED TO INSERT AN EMPTY DATA, BUT THE FIELD DOSEN´T ACCEPT NULLS HOW CAN I INSERT ???????????????????? {FECHA}, {CERO_CD}, ' ' end-sql end-procedure thank you for your help _______________________________________________ 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 Oct 05 14:56:50 2004 Subject: RE: [sqr-users] insert sql Date: Tue, 5 Oct 2004 12:54:16 -0700 From: "Bill Winkfield" Eduardo, It looks like your problem is due to a missing bracket in your SQL. begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( {ARHDM}, $GROUP_ID, {ARHDM}, $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', <=3D=3D this is a space, not a null {FECHA}, {CERO_CD}, ' ' ) <=3D=3D you need a closing bracket here end-sql end-procedure -----Original Message----- From: Eduardo_Bugarin@hdm.honda.com [mailto:Eduardo_Bugarin@hdm.honda.com]= =20 Sent: Tuesday, October 05, 2004 12:45 PM To: sqr-users@sqrug.org Subject: [sqr-users] insert sql hello everyone I need to insert data by sql, but I have the next message ORA-00984: COLUMN NOT ALLOWED HERE I know that this is about the fields on the Oracle table could some one tellme if this is correct: begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( {ARHDM}, $GROUP_ID, {ARHDM}, $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', IN THIS FIELD I NEED TO INSERT AN EMPTY DATA, BUT THE FIELD DOSEN=B4T ACCEPT NULLS HOW CAN I INSERT ???????????????????? {FECHA}, {CERO_CD}, ' ' end-sql end-procedure thank you for your help _______________________________________________ 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 Oct 05 15:00:36 2004 Date: Tue, 05 Oct 2004 15:58:08 -0400 From: "Larry Roux" Subject: Re: [sqr-users] insert sql It depends on the field type. In Oracle, a date can be NULL (''), for a number insert 0, for a character field put in a space (' ') Larry Roux Syracuse University lroux@syr.edu _______________________________________________ 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 Oct 05 15:03:31 2004 From: "Penny Ginn" Subject: RE: [sqr-users] insert sql Date: Tue, 5 Oct 2004 15:58:38 -0400 If the Oracle column is defined as "not null," then there's no way you're going to get a null value in there. It looks like you're inserting a single space - a non-null column should allow that as data. If the column is NOT defined as not null, then you can use the word NULL in your insert; i.e. ... $CLIENTE, $FOLIO_FACT, #CNT_GRP, NULL, {FECHA}, ... Mrs. Penny M. Ginn Associate Director of Information Technology Presbyterian College Clinton, SC 29325 864-833-8212 -----Original Message----- From: sqr-users-bounces+pmginn=presby.edu@sqrug.org [mailto:sqr-users-bounces+pmginn=presby.edu@sqrug.org]On Behalf Of Eduardo_Bugarin@hdm.honda.com Sent: Tuesday, October 05, 2004 2:45 PM To: sqr-users@sqrug.org Subject: [sqr-users] insert sql hello everyone I need to insert data by sql, but I have the next message ORA-00984: COLUMN NOT ALLOWED HERE I know that this is about the fields on the Oracle table could some one tellme if this is correct: begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( {ARHDM}, $GROUP_ID, {ARHDM}, $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', IN THIS FIELD I NEED TO INSERT AN EMPTY DATA, BUT THE FIELD DOSEN´T ACCEPT NULLS HOW CAN I INSERT ???????????????????? {FECHA}, {CERO_CD}, ' ' end-sql end-procedure thank you for your help _______________________________________________ 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 Oct 05 15:06:22 2004 From: "Aguirre, Jose" Subject: RE: [sqr-users] insert sql Date: Tue, 5 Oct 2004 14:59:37 -0500 Use this function for all the not nullable fields before your insert: LET $FIELD_NAME =3D NVL($FIELD_NAME, ' ')=20 Good luck, Jos=E9 A. Aguirre -----Original Message----- From: sqr-users-bounces+jaguir=3Dlsuhsc.edu@sqrug.org [mailto:sqr-users-bounces+jaguir=3Dlsuhsc.edu@sqrug.org] On Behalf Of Eduardo_Bugarin@hdm.honda.com Sent: Tuesday, October 05, 2004 2:45 PM To: sqr-users@sqrug.org Subject: [sqr-users] insert sql hello everyone I need to insert data by sql, but I have the next message ORA-00984: COLUMN NOT ALLOWED HERE I know that this is about the fields on the Oracle table could some one tellme if this is correct: begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( {ARHDM}, $GROUP_ID, {ARHDM}, $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', IN THIS FIELD I NEED TO INSERT AN EMPTY DATA, BUT THE FIELD DOSEN=B4T ACCEPT NULLS HOW CAN I INSERT ???????????????????? {FECHA}, {CERO_CD}, ' ' end-sql end-procedure thank you for your help _______________________________________________ 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 Oct 05 15:27:32 2004 Date: Tue, 5 Oct 2004 13:26:32 -0700 From: kevin.reschenberg@sparkpath.com Subject: RE: [sqr-users] insert sql It looks like you are asking about two problems--the null column and the error message. I have a suggestion for the error. Check to be sure that the contents of your substitution variables, such as {ARHDM}, contain quotes if necessary. A value without quotes may look like a column name to Oracle. The following example will get the "column not allowed here" error because the second value is not enclosed in quotes: insert into ps_animal_tbl values('CAT',Cat,'Cat') If we use a substitution variable: insert into ps_animal_tbl values('CAT',{Cat},'Cat') it will work as long as {Cat} is defined to include quotes: #define Cat 'Cat' You could also get around the error by simply enclosing the entire substitution variable in quotes: '{ARHDM}' Kevin Reschenberg SparkPath Technologies, Inc. (949) 466-1674 www.sparkpath.com -------- Original Message -------- Subject: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Tue, October 05, 2004 12:44 pm To: sqr-users@sqrug.org hello everyone I need to insert data by sql, but I have the next message ORA-00984: COLUMN NOT ALLOWED HERE I know that this is about the fields on the Oracle table could some one tellme if this is correct: begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( {ARHDM}, $GROUP_ID, {ARHDM}, $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', IN THIS FIELD I NEED TO INSERT AN EMPTY DATA, BUT THE FIELD DOSEN´T ACCEPT NULLS HOW CAN I INSERT ???????????????????? {FECHA}, {CERO_CD}, ' ' end-sql end-procedure thank you for your help _______________________________________________ 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 Oct 05 15:43:59 2004 Subject: RE: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Tue, 5 Oct 2004 15:41:57 -0500 Kevin, you are rigth, I have 2 problems (I have not notice the error about this {ARHDM}) with the suggestions from all, I did resolve my sql insert thank you for your time and support have a nice day |---------+---------------------------------------------------------> | | kevin.reschenberg@sparkpath.com | | | Enviado por: | | | sqr-users-bounces+eduardo_bugarin=hdm.honda.co| | | m@sqrug.org | | | | | | | | | 05/10/2004 03:26 p.m. | | | Por favor, responda a "This list is for | | | discussion about the SQR database reporting | | | language from Hyperion Solutions." | | | | |---------+---------------------------------------------------------> >----------------------------------------------------------------------------------------------| | | | Para: "This list is for discussion about the SQR database reporting language | | from Hyperion Solutions." | | cc: (cco: Eduardo Bugarin/HDM/AM/HONDA) | | Asunto: RE: [sqr-users] insert sql | >----------------------------------------------------------------------------------------------| It looks like you are asking about two problems--the null column and the error message. I have a suggestion for the error. Check to be sure that the contents of your substitution variables, such as {ARHDM}, contain quotes if necessary. A value without quotes may look like a column name to Oracle. The following example will get the "column not allowed here" error because the second value is not enclosed in quotes: insert into ps_animal_tbl values('CAT',Cat,'Cat') If we use a substitution variable: insert into ps_animal_tbl values('CAT',{Cat},'Cat') it will work as long as {Cat} is defined to include quotes: #define Cat 'Cat' You could also get around the error by simply enclosing the entire substitution variable in quotes: '{ARHDM}' Kevin Reschenberg SparkPath Technologies, Inc. (949) 466-1674 www.sparkpath.com -------- Original Message -------- Subject: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Tue, October 05, 2004 12:44 pm To: sqr-users@sqrug.org hello everyone I need to insert data by sql, but I have the next message ORA-00984: COLUMN NOT ALLOWED HERE I know that this is about the fields on the Oracle table could some one tellme if this is correct: begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( {ARHDM}, $GROUP_ID, {ARHDM}, $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', IN THIS FIELD I NEED TO INSERT AN EMPTY DATA, BUT THE FIELD DOSEN _______________________________________________ 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 Oct 06 11:37:47 2004 Subject: RE: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Wed, 6 Oct 2004 11:37:12 -0500 Hi all: other question: when i execute my sqr program, I have the next message error: A NON-NUMERIC CHARACTER WAS FOUND WHERE A NUMERIC WAS EXPECTED some of my code is: #DEFINE QUINCE 15 THIS IS A NUMERIC VALUE #DEFINE ARHDM ARHDM begin-sql insert into PS_SOME_TABLE values ( '{ARHDM}', $GROUP_ID, '{ARHDM}', $CLIENTE, $FOLIO_FACT, '{QUINCE}' IS THIS IS CORRECT TO PUT THE NUMERIC VALUE 15 ??? ) end-sql I will appreciate any suggestion thank you for your time |---------+---------------------------------------------------------> | | kevin.reschenberg@sparkpath.com | | | Enviado por: | | | sqr-users-bounces+eduardo_bugarin=hdm.honda.co| | | m@sqrug.org | | | | | | | | | 05/10/2004 03:26 p.m. | | | Por favor, responda a "This list is for | | | discussion about the SQR database reporting | | | language from Hyperion Solutions." | | | | |---------+---------------------------------------------------------> >----------------------------------------------------------------------------------------------| | | | Para: "This list is for discussion about the SQR database reporting language | | from Hyperion Solutions." | | cc: (cco: Eduardo Bugarin/HDM/AM/HONDA) | | Asunto: RE: [sqr-users] insert sql | >----------------------------------------------------------------------------------------------| It looks like you are asking about two problems--the null column and the error message. I have a suggestion for the error. Check to be sure that the contents of your substitution variables, such as {ARHDM}, contain quotes if necessary. A value without quotes may look like a column name to Oracle. The following example will get the "column not allowed here" error because the second value is not enclosed in quotes: insert into ps_animal_tbl values('CAT',Cat,'Cat') If we use a substitution variable: insert into ps_animal_tbl values('CAT',{Cat},'Cat') it will work as long as {Cat} is defined to include quotes: #define Cat 'Cat' You could also get around the error by simply enclosing the entire substitution variable in quotes: '{ARHDM}' Kevin Reschenberg SparkPath Technologies, Inc. (949) 466-1674 www.sparkpath.com -------- Original Message -------- Subject: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Tue, October 05, 2004 12:44 pm To: sqr-users@sqrug.org hello everyone I need to insert data by sql, but I have the next message ORA-00984: COLUMN NOT ALLOWED HERE I know that this is about the fields on the Oracle table could some one tellme if this is correct: begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( {ARHDM}, $GROUP_ID, {ARHDM}, $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', IN THIS FIELD I NEED TO INSERT AN EMPTY DATA, BUT THE FIELD DOSEN _______________________________________________ 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 Oct 06 12:07:25 2004 Date: Wed, 6 Oct 2004 10:04:47 -0700 From: kevin.reschenberg@sparkpath.com Subject: RE: [sqr-users] insert sql If the column is defined as numeric, you don't need the quotes. In that case, instead of '{QUINCE}' just use {QUINCE} Also, if your program really says #DEFINE QUINCE 15 THIS IS A NUMERIC VALUE including the "THIS IS A NUMERIC VALUE", be sure to make that a comment: #DEFINE QUINCE 15 ! THIS IS A NUMERIC VALUE Otherwise, {QUINCE} will include both the 15 and the words. Kevin Reschenberg SparkPath Technologies, Inc. (949) 466-1674 www.sparkpath.com -------- Original Message -------- Subject: RE: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Wed, October 06, 2004 9:37 am To: sqr-users@sqrug.org Hi all: other question: when i execute my sqr program, I have the next message error: A NON-NUMERIC CHARACTER WAS FOUND WHERE A NUMERIC WAS EXPECTED some of my code is: #DEFINE QUINCE 15 THIS IS A NUMERIC VALUE #DEFINE ARHDM ARHDM begin-sql insert into PS_SOME_TABLE values ( '{ARHDM}', $GROUP_ID, '{ARHDM}', $CLIENTE, $FOLIO_FACT, '{QUINCE}' IS THIS IS CORRECT TO PUT THE NUMERIC VALUE 15 ??? ) end-sql I will appreciate any suggestion thank you for your time |---------+---------------------------------------------------------> | | kevin.reschenberg@sparkpath.com | | | Enviado por: | | | sqr-users-bounces+eduardo_bugarin=hdm.honda.co| | | m@sqrug.org | | | | | | | | | 05/10/2004 03:26 p.m. | | | Por favor, responda a "This list is for | | | discussion about the SQR database reporting | | | language from Hyperion Solutions." | | | | |---------+---------------------------------------------------------> >----------------------------------------------------------------------------------------------| | | | Para: "This list is for discussion about the SQR database reporting language | | from Hyperion Solutions." | | cc: (cco: Eduardo Bugarin/HDM/AM/HONDA) | | Asunto: RE: [sqr-users] insert sql | >----------------------------------------------------------------------------------------------| It looks like you are asking about two problems--the null column and the error message. I have a suggestion for the error. Check to be sure that the contents of your substitution variables, such as , contain quotes if necessary. A value without quotes may look like a column name to Oracle. The following example will get the "column not allowed here" error because the second value is not enclosed in quotes: insert into ps_animal_tbl values('CAT',Cat,'Cat') If we use a substitution variable: insert into ps_animal_tbl values('CAT',,'Cat') it will work as long as is defined to include quotes: #define Cat 'Cat' You could also get around the error by simply enclosing the entire substitution variable in quotes: '' Kevin Reschenberg SparkPath Technologies, Inc. (949) 466-1674 www.sparkpath.com -------- Original Message -------- Subject: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Tue, October 05, 2004 12:44 pm To: sqr-users@sqrug.org hello everyone I need to insert data by sql, but I have the next message ORA-00984: COLUMN NOT ALLOWED HERE I know that this is about the fields on the Oracle table could some one tellme if this is correct: begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( , $GROUP_ID, , $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', IN THIS FIELD I NEED TO INSERT AN EMPTY DATA, BUT THE FIELD DOSEN _______________________________________________ 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 Oct 06 13:15:59 2004 From: "Aguirre, Jose" Subject: RE: [sqr-users] insert sql Date: Wed, 6 Oct 2004 13:14:44 -0500 Do this instead if you are bringing QUINCE as a string value. You can place it right before the insert: LET #QUINCE =3D to_number($QUINCE) then=20 begin-sql insert into PS_SOME_TABLE values ( '{ARHDM}', $GROUP_ID, '{ARHDM}', $CLIENTE, $FOLIO_FACT, #QUINCE) Thanks, Jos=E9 A. Aguirre 504.568.2455 -----Original Message----- From: sqr-users-bounces+jaguir=3Dlsuhsc.edu@sqrug.org [mailto:sqr-users-bounces+jaguir=3Dlsuhsc.edu@sqrug.org] On Behalf Of Eduardo_Bugarin@hdm.honda.com Sent: Wednesday, October 06, 2004 11:37 AM To: sqr-users@sqrug.org Subject: RE: [sqr-users] insert sql Hi all: other question: when i execute my sqr program, I have the next message error: A NON-NUMERIC CHARACTER WAS FOUND WHERE A NUMERIC WAS EXPECTED some of my code is: #DEFINE QUINCE 15 THIS IS A NUMERIC VALUE #DEFINE ARHDM ARHDM begin-sql insert into PS_SOME_TABLE values ( '{ARHDM}', $GROUP_ID, '{ARHDM}', $CLIENTE, $FOLIO_FACT, '{QUINCE}' IS THIS IS CORRECT TO PUT THE NUMERIC VALUE 15 ??? ) end-sql I will appreciate any suggestion thank you for your time |---------+---------------------------------------------------------> | | kevin.reschenberg@sparkpath.com | | | Enviado por: | | | sqr-users-bounces+eduardo_bugarin=3Dhdm.honda.co| | | m@sqrug.org | | | | | | | | | 05/10/2004 03:26 p.m. | | | Por favor, responda a "This list is for | | | discussion about the SQR database reporting | | | language from Hyperion Solutions." | | | | |---------+---------------------------------------------------------> =20 >--------------------------------------------------------------------------- -------------------| | | | Para: "This list is for discussion about the SQR database reporting language | | from Hyperion Solutions." | | cc: (cco: Eduardo Bugarin/HDM/AM/HONDA) | | Asunto: RE: [sqr-users] insert sql | =20 >--------------------------------------------------------------------------- -------------------| It looks like you are asking about two problems--the null column and the error message. I have a suggestion for the error. Check to be sure that the contents of your substitution variables, such as {ARHDM}, contain quotes if necessary. A value without quotes may look like a column name to Oracle. The following example will get the "column not allowed here" error because the second value is not enclosed in quotes: insert into ps_animal_tbl values('CAT',Cat,'Cat') If we use a substitution variable: insert into ps_animal_tbl values('CAT',{Cat},'Cat') it will work as long as {Cat} is defined to include quotes: #define Cat 'Cat' You could also get around the error by simply enclosing the entire substitution variable in quotes: '{ARHDM}' Kevin Reschenberg SparkPath Technologies, Inc. (949) 466-1674 www.sparkpath.com -------- Original Message -------- Subject: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Tue, October 05, 2004 12:44 pm To: sqr-users@sqrug.org hello everyone I need to insert data by sql, but I have the next message ORA-00984: COLUMN NOT ALLOWED HERE I know that this is about the fields on the Oracle table could some one tellme if this is correct: begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( {ARHDM}, $GROUP_ID, {ARHDM}, $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', IN THIS FIELD I NEED TO INSERT AN EMPTY DATA, BUT THE FIELD DOSEN=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B =1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B=1B= =1B=1B=1B=1B=1B=1B=1B=1B _______________________________________________ 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 Oct 06 14:16:31 2004 Subject: RE: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Wed, 6 Oct 2004 14:14:03 -0500 I did what Kevin said, and everthing is well thank you both |---------+---------------------------------------------------------> | | kevin.reschenberg@sparkpath.com | | | Enviado por: | | | sqr-users-bounces+eduardo_bugarin=hdm.honda.co| | | m@sqrug.org | | | | | | | | | 06/10/2004 12:04 p.m. | | | Por favor, responda a "This list is for | | | discussion about the SQR database reporting | | | language from Hyperion Solutions." | | | | |---------+---------------------------------------------------------> >----------------------------------------------------------------------------------------------| | | | Para: "This list is for discussion about the SQR database reporting language | | from Hyperion Solutions." | | cc: (cco: Eduardo Bugarin/HDM/AM/HONDA) | | Asunto: RE: [sqr-users] insert sql | >----------------------------------------------------------------------------------------------| If the column is defined as numeric, you don't need the quotes. In that case, instead of '{QUINCE}' just use {QUINCE} Also, if your program really says #DEFINE QUINCE 15 THIS IS A NUMERIC VALUE including the "THIS IS A NUMERIC VALUE", be sure to make that a comment: #DEFINE QUINCE 15 ! THIS IS A NUMERIC VALUE Otherwise, {QUINCE} will include both the 15 and the words. Kevin Reschenberg SparkPath Technologies, Inc. (949) 466-1674 www.sparkpath.com -------- Original Message -------- Subject: RE: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Wed, October 06, 2004 9:37 am To: sqr-users@sqrug.org Hi all: other question: when i execute my sqr program, I have the next message error: A NON-NUMERIC CHARACTER WAS FOUND WHERE A NUMERIC WAS EXPECTED some of my code is: #DEFINE QUINCE 15 THIS IS A NUMERIC VALUE #DEFINE ARHDM ARHDM begin-sql insert into PS_SOME_TABLE values ( '{ARHDM}', $GROUP_ID, '{ARHDM}', $CLIENTE, $FOLIO_FACT, '{QUINCE}' IS THIS IS CORRECT TO PUT THE NUMERIC VALUE 15 ??? ) end-sql I will appreciate any suggestion thank you for your time |---------+---------------------------------------------------------> | | kevin.reschenberg@sparkpath.com | | | Enviado por: | | | sqr-users-bounces+eduardo_bugarin=hdm.honda.co| | | m@sqrug.org | | | | | | | | | 05/10/2004 03:26 p.m. | | | Por favor, responda a "This list is for | | | discussion about the SQR database reporting | | | language from Hyperion Solutions." | | | | |---------+---------------------------------------------------------> >----------------------------------------------------------------------------------------------| | | | Para: "This list is for discussion about the SQR database reporting language | | from Hyperion Solutions." | | cc: (cco: Eduardo Bugarin/HDM/AM/HONDA) | | Asunto: RE: [sqr-users] insert sql | >----------------------------------------------------------------------------------------------| It looks like you are asking about two problems--the null column and the error message. I have a suggestion for the error. Check to be sure that the contents of your substitution variables, such as , contain quotes if necessary. A value without quotes may look like a column name to Oracle. The following example will get the "column not allowed here" error because the second value is not enclosed in quotes: insert into ps_animal_tbl values('CAT',Cat,'Cat') If we use a substitution variable: insert into ps_animal_tbl values('CAT',,'Cat') it will work as long as is defined to include quotes: #define Cat 'Cat' You could also get around the error by simply enclosing the entire substitution variable in quotes: '' Kevin Reschenberg SparkPath Technologies, Inc. (949) 466-1674 www.sparkpath.com -------- Original Message -------- Subject: [sqr-users] insert sql From: Eduardo_Bugarin@hdm.honda.com Date: Tue, October 05, 2004 12:44 pm To: sqr-users@sqrug.org hello everyone I need to insert data by sql, but I have the next message ORA-00984: COLUMN NOT ALLOWED HERE I know that this is about the fields on the Oracle table could some one tellme if this is correct: begin-procedure INSERT_DAT begin-sql insert into SOME_TABLE values ( , $GROUP_ID, , $CLIENTE, $FOLIO_FACT, #CNT_GRP, ' ', IN THIS FIELD I NEED TO INSERT AN EMPTY DATA, BUT THE FIELD DOSEN _______________________________________________ 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 Oct 06 14:50:41 2004 From: "DICKMAN, ERIC C." Date: Wed, 6 Oct 2004 14:47:57 -0500 Subject: [sqr-users] An SQR Layout Question. Hi, I am attempting to create an SQR report with both a Landscape layout and a Portrait layout. Is this functionality possible in a SQR file? And if yes, are there any examples of this available. Thanks, Eric _______________________________________________ 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 Oct 06 15:22:09 2004 Subject: RE: [sqr-users] An SQR Layout Question. Date: Wed, 6 Oct 2004 15:19:22 -0500 From: "Hins, Thomas D. \(HSC\)" You would have to use a conditional statement of somekind and then know why= you would want the same report to possibly come out both ways and code for= that. Tom Hins Information Technology - Application Solutions University of Oklahoma Health Sciences Center P.O. Box 26901 Oklahoma City, OK 73190 Phone:(405) 271-2262 Ext. 50213 Fax: (405) 271-2352 (The information transmitted is intended only for the person or entity to w= hich it is addressed and may contain confidential and/or privileged materia= l. If you are not the intended recipient of this message you are hereby not= ified that any use, review, retransmission, dissemination, distribution, re= production or any action taken in reliance upon this message is prohibited.= If you received this in error, please contact the sender and delete the ma= terial from any computer. Any views expressed in this message are those of = the individual sender and may not necessarily reflect the views of the comp= any.) -----Original Message----- From: sqr-users-bounces+thomas-hins=3Douhsc.edu@sqrug.org [mailto:sqr-users-bounces+thomas-hins=3Douhsc.edu@sqrug.org]On Behalf Of DICKMAN, ERIC C. Sent: Wednesday, October 06, 2004 2:48 PM To: 'sqr-users@sqrug.org' Subject: [sqr-users] An SQR Layout Question. Hi, I am attempting to create an SQR report with both a Landscape layout and a Portrait layout. Is this functionality possible in a SQR file? And if yes, are there any examples of this available. Thanks, Eric _______________________________________________ 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 Oct 06 15:49:19 2004 Date: Wed, 06 Oct 2004 16:46:34 -0400 From: "Jessie Dickenson" Subject: RE: [sqr-users] An SQR Layout Question. You could define 2 separate report layouts; In one layout, you could specify portrait mode & in the other, landscape. However, the SPF files that gets generated have the following naming convention: Assume that your SQR program is called TEST.SQR 1. The report with the first layout definition (literally going sequentially) will be generated as TEST.SPF 2. The report with the second layout will be genearted as TEST.S01. Hope this helps. Thanks, Jessie >>> Thomas-Hins@ouhsc.edu 10/6/2004 4:19:22 PM >>> You would have to use a conditional statement of somekind and then know why you would want the same report to possibly come out both ways and code for that. Tom Hins Information Technology - Application Solutions University of Oklahoma Health Sciences Center P.O. Box 26901 Oklahoma City, OK 73190 Phone:(405) 271-2262 Ext. 50213 Fax: (405) 271-2352 (The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the intended recipient of this message you are hereby notified that any use, review, retransmission, dissemination, distribution, reproduction or any action taken in reliance upon this message is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of the company.) -----Original Message----- From: sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org [mailto:sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org]On Behalf Of DICKMAN, ERIC C. Sent: Wednesday, October 06, 2004 2:48 PM To: 'sqr-users@sqrug.org' Subject: [sqr-users] An SQR Layout Question. Hi, I am attempting to create an SQR report with both a Landscape layout and a Portrait layout. Is this functionality possible in a SQR file? And if yes, are there any examples of this available. Thanks, Eric _______________________________________________ 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 Oct 06 15:56:03 2004 From: "Penny Ginn" Date: Wed, 6 Oct 2004 16:54:49 -0400 Subject: [sqr-users] SQR Layout / Output file names Is there a way to override the output file names? I've got a program I'm working on with 2 optional output formats (envelopes or labels), depending on the parameter the user enters. I really need the output to be named the same name, regardless of whether it's labels or envelopes. Suggestions? -----Original Message----- From: sqr-users-bounces+pmginn=presby.edu@sqrug.org [mailto:sqr-users-bounces+pmginn=presby.edu@sqrug.org]On Behalf Of Jessie Dickenson Sent: Wednesday, October 06, 2004 3:47 PM To: Thomas-Hins@ouhsc.edu; sqr-users@sqrug.org Subject: RE: [sqr-users] An SQR Layout Question. You could define 2 separate report layouts; In one layout, you could specify portrait mode & in the other, landscape. However, the SPF files that gets generated have the following naming convention: Assume that your SQR program is called TEST.SQR 1. The report with the first layout definition (literally going sequentially) will be generated as TEST.SPF 2. The report with the second layout will be genearted as TEST.S01. Hope this helps. Thanks, Jessie >>> Thomas-Hins@ouhsc.edu 10/6/2004 4:19:22 PM >>> You would have to use a conditional statement of somekind and then know why you would want the same report to possibly come out both ways and code for that. Tom Hins Information Technology - Application Solutions University of Oklahoma Health Sciences Center P.O. Box 26901 Oklahoma City, OK 73190 Phone:(405) 271-2262 Ext. 50213 Fax: (405) 271-2352 (The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the intended recipient of this message you are hereby notified that any use, review, retransmission, dissemination, distribution, reproduction or any action taken in reliance upon this message is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of the company.) -----Original Message----- From: sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org [mailto:sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org]On Behalf Of DICKMAN, ERIC C. Sent: Wednesday, October 06, 2004 2:48 PM To: 'sqr-users@sqrug.org' Subject: [sqr-users] An SQR Layout Question. Hi, I am attempting to create an SQR report with both a Landscape layout and a Portrait layout. Is this functionality possible in a SQR file? And if yes, are there any examples of this available. Thanks, Eric _______________________________________________ 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 Thu Oct 07 04:17:30 2004 Date: 7 Oct 2004 09:15:37 -0000 From: "p satheesh kumar" Subject: [sqr-users] Re: confirm af2203e0d11e018e78b9fbb5e8c5a1e9fffe57b9 Hi, I have recently subscribed in sqrug.org. I need clarification. My problem is excel download option. I am integrating coldfusion with cgi a= nd in cgi I am using sqr to create report. Checked the below things - Checked the parameters from coldfusion. Required parameters are coming fi= ne. - No errors where found in cgi file. - SQR is able to create the report in .csv format. But not displaying fine = (It displays the path of sqr). Attached the sample report.=20 Checked SQRIBE_HOME path it is fine in environment. The below code I have u= sed=20 $sqrcommand1 =3D "sqrt $Rptsqr $UIDPWD -E$ErrorFile -F$FileName -PRINTER:E= P -EH_CSV ". " ". "$ID1" ." ". " ".=20=20=20=20=20=20=20=20=20=20=20 "\'". "$ID2" ."\'". " ".=20=20=20=20=20=20=20=20=20=20=20 " ". "$ID3" ." ". " ". "\'". "$PARAMETER1" ."\'". " ". "\'". "$PARAMETER2" ."\'". " ". "\'". "$PARAMETER3" ."\'". " ". "\'". "$PARAMETER4" ."\'". " ". " 1>/dev/null 2>/dev/null" ;=20 But when I work from the site. It is not showing the output in expected for= mat. But it is able to create .csv file. When I use the same in on the server command prompt by giving required para= meters and save the file in .csv it is working fine. The output is displaye= d fine.=20 I have been searching on the internet for the last two days. I am not able = to find any solutions. Can someone help me out? Thanks in advance, Satheesh. _______________________________________________ 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 Oct 07 04:22:34 2004 Date: 7 Oct 2004 09:18:51 -0000 From: "p satheesh kumar" Subject: [sqr-users] Re: confirm af2203e0d11e018e78b9fbb5e8c5a1e9fffe57b9 Hi, I have recently subscribed in sqrug.org. I need clarification. My problem is excel download option. I am integrating coldfusion with cgi a= nd in cgi I am using sqr to create report. Checked the below things - Checked the parameters from coldfusion. Required parameters are coming fi= ne. - No errors where found in cgi file. - SQR is able to create the report in .csv format. But not displaying fine = (It displays the path of sqr). Attached the sample report.=20 Checked SQRIBE_HOME path it is fine in environment. The below code I have u= sed=20 $sqrcommand1 =3D "sqrt $Rptsqr $UIDPWD -E$ErrorFile -F$FileName -PRINTER:E= P -EH_CSV ". " ". "$ID1" ." ". " ".=20=20=20=20=20=20=20=20=20=20=20 "\'". "$ID2" ."\'". " ".=20=20=20=20=20=20=20=20=20=20=20 " ". "$ID3" ." ". " ". "\'". "$PARAMETER1" ."\'". " ". "\'". "$PARAMETER2" ."\'". " ". "\'". "$PARAMETER3" ."\'". " ". "\'". "$PARAMETER4" ."\'". " ". " 1>/dev/null 2>/dev/null" ;=20 But when I work from the site. It is not showing the output in expected for= mat. But it is able to create .csv file. When I use the same in on the server command prompt by giving required para= meters and save the file in .csv it is working fine. The output is displaye= d fine.=20 I have been searching on the internet for the last two days. I am not able = to find any solutions. Can someone help me out? Thanks in advance, Satheesh. _______________________________________________ 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 Oct 07 04:34:02 2004 Date: 7 Oct 2004 09:32:06 -0000 From: "p satheesh kumar" Subject: [sqr-users] Output .csv format not working This is a multi-part message in MIME format... --Next_1097141501---0-202.54.124.152-10133 Content-Disposition: inline Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi, I have already sent mail but I didn't include the attachment. Now I attache= d. My problem is excel download option. I am integrating coldfusion with cgi a= nd in cgi I am using sqr to create report. Checked the below things - Checked the parameters from coldfusion. Required parameters are coming fi= ne. - No errors where found in cgi file. - SQR is able to create the report in .csv format. But not displaying fine = (It displays the path of sqr). Attached the sample report.=20 Checked SQRIBE_HOME path it is fine in environment. The below code I have u= sed=20 $sqrcommand1 =3D "sqrt $Rptsqr $UIDPWD -E$ErrorFile -F$FileName -PRINTER:E= P -EH_CSV ". " ". "$ID1" ." ". " ".=20=20=20=20=20=20=20=20=20=20 "\'". "$ID2" ."\'". " ".=20=20=20=20=20=20=20=20=20=20 " ". "$ID3" ." ". " ". "\'". "$PARAMETER1" ."\'". " ". "\'". "$PARAMETER2" ."\'". " ". "\'". "$PARAMETER3" ."\'". " ". "\'". "$PARAMETER4" ."\'". " ". " 1>/dev/null 2>/dev/null" ;=20 But when I work from the site. It is not showing the output in expected for= mat. But it is able to create .csv file. When I use the same in on the server command prompt by giving required para= meters and save the file in .csv it is working fine. The output is displaye= d fine.=20 I have been searching on the internet for the last two days. I am not able = to find any solutions. Can someone help me out? Thanks in advance, Satheesh. =20 --Next_1097141501---0-202.54.124.152-10133 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'application/pdf' MIME type. It has been automatically discarded before sending the message to the list. --Next_1097141501---0-202.54.124.152-10133 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 --Next_1097141501---0-202.54.124.152-10133-- From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Thu Oct 07 23:59:10 2004 From: pinak.vashi@blr.hexaware.com Date: Fri, 8 Oct 2004 11:27:59 +0630 Subject: [sqr-users] SQR writing the file without extension Hi All, I am facing one strange problem during writing the file from a sqr, which doesn't have the file extension (no .txt). SQR is creating the files and it is available in PSREPORTS directory. But I am not able to see the output file in 'View Log/Trace' link of precess monitor. When I create the file with .txt or .dat extension, I am able to see these files in 'View Log/Trace' link of precess monitor. I am thinking that it is related to some PeopleCode. Can you please help me out. thanks, Pinak Thanks, Pinak Vashi pinak.vashi@blr.hexaware.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 Fri Oct 08 13:43:44 2004 Date: Fri, 08 Oct 2004 13:42:17 -0500 From: "Jennifer Lee" Subject: [sqr-users] Fwd: Puzzling Input Argument File Error >>> Jennifer Lee 10/6/2004 7:47:19 AM >>> Hi, I would like to seek advice from this group about an input argument file error: Q1) If an input argument file is to be up loaded into a Unix environment, does it matter to SQRW whether or not each argument line ends with '\r \n' (return followed by new-line) or simply '\n'? Q2) If an argument line only contains '\n', will SQRW know that it should take that as a 'null' value? Q3) What happens after SQRW reads in a line with '\n'? Will it assume that there is no more input values or will it continue to read in values from the argument file until end of the file? Q4) Are there some watch-outs for using argument files for input command? What are the situations where 'Error getting INPUT' can occur? Some background info: 1) We are using SQR for PeopleSoft/8.20. Sun's Solaris is our Unix OS. 2) I was able to run from my PC an sqr program with all input values in an argument file. The command line looks like this: sqrw.exe ... @c:\csqr\umfac001.parm 3) There are five input statements in the program: input $Parm1 'Enter Parm1 (Optional, Press ENTER to continue)' input $Parm2 'Enter Parm2 (Optional, Press ENTER to continue)' input $Parm3 maxlen=5 'Enter Parm3' input $Parm4 maxlen=30 'Enter Parm4 (Optional, Press ENTER to continue)' input $Parm5 maxlen=10 'Enter Parm5' 4) The argument file used by the program looks like this (from a PC text editor): Line 1:A blank space followed by a catridge return Line 2:A catridge return only Line 3:OSF followed by a catridge return Line 4:A catridge return only Line 5:2005 5) Because we are trying to run this sqr program outside the Process Scheduler but with a Unix script calling SQRW, we up loaded the program and the argument file into our Unix environment. Of course the @ argument has the proper Unix file path and file name. 6) When the same program was executed on Unix with a Unix script, the program died right after 'Enter Parm3' is display in the log file. 7) Here's what it was logged in the .err output file: Error on line 162: (SQR 2500) Error getting INPUT. SQR for PeopleSoft: Program Aborting. Thanks in advance for your help!! This e-mail and its attachments have been scanned for viruses. EAS - Systems Support, OIT, University of Minnesota _______________________________________________ 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 Oct 08 14:15:55 2004 Subject: RE: [sqr-users] Fwd: Puzzling Input Argument File Error Date: Fri, 8 Oct 2004 14:14:39 -0500 From: "Knapp, Richard" SQRW is a windows application so it expects input files to have the standard windows line terminators (CRLF). Why are you trying to use a unix file with unix line terminators (LF)in a windows application? Transfer the file to the windows box with FTP and that will convert to the destination OS line terminator or find some way to preserve the crlfs.=20 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 Jennifer Lee Sent: Friday, October 08, 2004 1:42 PM To: sqr-users@sqrug.org Subject: [sqr-users] Fwd: Puzzling Input Argument File Error >>> Jennifer Lee 10/6/2004 7:47:19 AM >>> Hi, I would like to seek advice from this group about an input argument file error: Q1) If an input argument file is to be up loaded into a Unix environment, does it matter to SQRW whether or not each argument line ends with '\r \n' (return followed by new-line) or simply '\n'? Q2) If an argument line only contains '\n', will SQRW know that it should take that as a 'null' value? Q3) What happens after SQRW reads in a line with '\n'? Will it assume that there is no more input values or will it continue to read in values from the argument file until end of the file? Q4) Are there some watch-outs for using argument files for input command? What are the situations where 'Error getting INPUT' can occur? Some background info: 1) We are using SQR for PeopleSoft/8.20. Sun's Solaris is our Unix OS. 2) I was able to run from my PC an sqr program with all input values in an argument file.=20=20=20 The command line looks like this:=20 sqrw.exe ... @c:\csqr\umfac001.parm 3) There are five input statements in the program: input $Parm1 'Enter Parm1 (Optional, Press ENTER to continue)' input $Parm2 'Enter Parm2 (Optional, Press ENTER to continue)' input $Parm3 maxlen=3D5 'Enter Parm3' input $Parm4 maxlen=3D30 'Enter Parm4 (Optional, Press ENTER to continue)' input $Parm5 maxlen=3D10 'Enter Parm5' 4) The argument file used by the program looks like this (from a PC text editor): Line 1:A blank space followed by a catridge return Line 2:A catridge return only Line 3:OSF followed by a catridge return Line 4:A catridge return only Line 5:2005=20 5) Because we are trying to run this sqr program outside the Process Scheduler but with a Unix script calling SQRW, we up loaded the program and the argument file into our Unix environment. Of course the @ argument has the proper Unix file path and file name. 6) When the same program was executed on Unix with a Unix script, the program died right after 'Enter Parm3' is display in the log file.=20=20 7) Here's what it was logged in the .err output file: Error on line 162: (SQR 2500) Error getting INPUT. SQR for PeopleSoft: Program Aborting. Thanks in advance for your help!! This e-mail and its attachments have been scanned for viruses. EAS - Systems Support, OIT, University of Minnesota _______________________________________________ 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 Oct 08 14:35:46 2004 Subject: Re: [sqr-users] Fwd: Puzzling Input Argument File Error From: Russ Wulfson Date: Fri, 8 Oct 2004 14:34:33 -0500 1)Put some shows surrounded by xx's show 'xxx' $parm 'xxx' in your program and see what sqr is seeing. 2) transfer your parm file to unix using ascci not binary. We move files back and forth all the time from windows to unix without any problems, we always use ascii transfers. Caveat - we don't use parm files, we do use input and output files. Thanks, Russ Wulfson _______________________________________________ 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 Oct 11 13:26:33 2004 Date: Mon, 11 Oct 2004 14:24:09 -0400 From: "Debbie Larney" Subject: [sqr-users] Max effective date evaluation not in sql Everyone, I have a simple issue that I can't seem to work out. I need to determine th= e max effective dated row from the minimum row returned in three different = select statements.=20 Here are my sample select statements select com.effdt from ps_company_tbl com where com.effdt =3D (select=20 min(inner.effdt)=20 from ps_company_tbl inner=20 where inner.company =3D com.company and=20 inner.effdt <=3D sysdate)=20 select pay.effdt from ps_paygroup_tbl pay where pay.effdt =3D (select=20 min(inner.effdt)=20 from ps_paygroup_tbl inner=20 where inner.company =3D pay.company and=20 and inner.paygroup =3D pay.paygroup inner.effdt <=3D sysdate)=20 select loc.effdt from ps_location_tbl loc where loc.effdt =3D (select=20 min(inner.effdt)=20 from ps_location_tbl inner=20 where inner.company =3D loc.company and=20 and inner.location =3D loc.location inner.effdt <=3D sysdate)=20 Now that I have loc.effdt, pay.effdt and com.effdt how do I determine which= is the max effective dated row. Thanks in advance Deb ******************************************************************** CONFIDENTIALITY NOTICE: The information contained in this e-mail is legally privileged and confiden= tial information intended only for the use of the individual or entity to w= hom it is addressed. If the reader of this message is not the intended rec= ipient, you are hereby notified that any viewing, dissemination, distributi= on, or copying of this e-mail message is strictly prohibited. If you have = received and/or are viewing this e-mail in error, please immediately notify= the sender by reply e-mail, and delete this e-mail from your system. 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 Mon Oct 11 13:54:06 2004 Subject: RE: [sqr-users] Max effective date evaluation not in sql Date: Mon, 11 Oct 2004 13:52:59 -0500 From: "Knapp, Richard" A crude bubble sort? If A > B if A > C it's A end-if else if B > C=20 it's B else it's C end-if end-if 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 Debbie Larney Sent: Monday, October 11, 2004 1:24 PM To: sqr-users@sqrug.org Subject: [sqr-users] Max effective date evaluation not in sql Everyone, I have a simple issue that I can't seem to work out. I need to determine the max effective dated row from the minimum row returned in three different select statements.=20 Here are my sample select statements select com.effdt from ps_company_tbl com where com.effdt =3D (select=20 min(inner.effdt)=20 from ps_company_tbl inner=20 where inner.company =3D com.company and=20 inner.effdt <=3D sysdate)=20 select pay.effdt from ps_paygroup_tbl pay where pay.effdt =3D (select=20 min(inner.effdt)=20 from ps_paygroup_tbl inner=20 where inner.company =3D pay.company and=20 and inner.paygroup =3D pay.paygroup inner.effdt <=3D sysdate)=20 select loc.effdt from ps_location_tbl loc where loc.effdt =3D (select=20 min(inner.effdt)=20 from ps_location_tbl inner=20 where inner.company =3D loc.company and=20 and inner.location =3D loc.location inner.effdt <=3D sysdate)=20 Now that I have loc.effdt, pay.effdt and com.effdt how do I determine which is the max effective dated row. Thanks in advance Deb ******************************************************************** CONFIDENTIALITY NOTICE: The information contained in this e-mail is legally privileged and confidential information intended only for the use of the individual or entity to whom it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any viewing, dissemination, distribution, or copying of this e-mail message is strictly prohibited. If you have received and/or are viewing this e-mail in error, please immediately notify the sender by reply e-mail, and delete this e-mail from your system. 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 Mon Oct 11 15:08:45 2004 From: Scott Eubank Date: Mon, 11 Oct 2004 16:05:47 -0400 Subject: [sqr-users] ISOLATION LEVEL I need to do a "dirty read", basically an uncommited read for a report so that I do not disrupt processes that are running at nite. How do I "change isolation Level UR" in an SQR for the entire SQR. AIX, DB2 , PSOFT-Tools 7.59 Any help would be greatly appreciated. Scott Ebank =================================================================== NOTICE - CONFIDENTIAL AND PRIVILEGED - This e-mail may contain privileged and confidential information and is intended only for the addressee named above. If you received this message in error, please immediately notify the sender by return e-mail and delete the original message; any distribution, copying or use of this e-mail by you is strictly prohibited and may be unlawful. _______________________________________________ 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 Oct 12 08:42:24 2004 Date: Tue, 12 Oct 2004 09:39:55 -0400 From: Neal Patterson Subject: [sqr-users] Re: sqr-users Digest, Vol 8, Issue 7 If Debbie wants an SQL approach to picking the maximum if her 3 dates, maybe something like this would do: select max(MyDate) from (select com.effdt as MyDate from ps_company_tbl com where com.effdt = (select min(inner.effdt) from ps_company_tbl inner where inner.company = com.company and inner.effdt <= sysdate) union select pay.effdt as MyDate from ps_paygroup_tbl pay where pay.effdt = (select min(inner.effdt) from ps_paygroup_tbl inner where inner.company = pay.company and and inner.paygroup = pay.paygroup inner.effdt <= sysdate) union select loc.effdt as MyDate from ps_location_tbl loc where loc.effdt = (select min(inner.effdt) from ps_location_tbl inner where inner.company = loc.company and and inner.location = loc.location inner.effdt <= sysdate)) as T - Neal Patterson ----------------------------------------------------------------------- At 06:03 AM 10/12/2004 -0500, you 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. Max effective date evaluation not in sql (Debbie Larney) > 2. RE: Max effective date evaluation not in sql (Knapp, Richard) > 3. ISOLATION LEVEL (Scott Eubank) > > >---------------------------------------------------------------------- > >Message: 1 >Date: Mon, 11 Oct 2004 14:24:09 -0400 >From: "Debbie Larney" >Subject: [sqr-users] Max effective date evaluation not in sql >To: >Message-ID: > <768DB1A57CCF794A9A6F7288B66D8F6563A8E1@HAMMERHEAD.ltc.com> >Content-Type: text/plain; charset="iso-8859-1" > >Everyone, > >I have a simple issue that I can't seem to work out. I need to determine >the max effective dated row from the minimum row returned in three >different select statements. >Here are my sample select statements > >select com.effdt >from ps_company_tbl com >where com.effdt = (select > min(inner.effdt) > from ps_company_tbl inner > where inner.company = com.company and > inner.effdt <= sysdate) > >select pay.effdt >from ps_paygroup_tbl pay >where pay.effdt = (select > min(inner.effdt) > from ps_paygroup_tbl inner > where inner.company = pay.company and > and inner.paygroup = pay.paygroup > inner.effdt <= sysdate) > >select loc.effdt >from ps_location_tbl loc >where loc.effdt = (select > min(inner.effdt) > from ps_location_tbl inner > where inner.company = loc.company and > and inner.location = loc.location > inner.effdt <= sysdate) > >Now that I have loc.effdt, pay.effdt and com.effdt how do I determine >which is the max effective dated row. > >Thanks in advance > >Deb > > > >******************************************************************** >CONFIDENTIALITY NOTICE: >The information contained in this e-mail is legally privileged and >confidential information intended only for the use of the individual or >entity to whom it is addressed. If the reader of this message is not the >intended recipient, you are hereby notified that any viewing, >dissemination, distribution, or copying of this e-mail message is strictly >prohibited. If you have received and/or are viewing this e-mail in error, >please immediately notify the sender by reply e-mail, and delete this >e-mail from your system. > >Thank you. >******************************************************************** > > > > >------------------------------ > >Message: 2 >Date: Mon, 11 Oct 2004 13:52:59 -0500 >From: "Knapp, Richard" >Subject: RE: [sqr-users] Max effective date evaluation not in sql >To: "This list is for discussion about the SQR database > reportinglanguage from Hyperion Solutions." >Message-ID: > >Content-Type: text/plain; charset="US-ASCII" > > >A crude bubble sort? If A > B > if A > C > it's A > end-if > else > if B > C > it's B > else > it's C > end-if > end-if > >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 >Debbie Larney >Sent: Monday, October 11, 2004 1:24 PM >To: sqr-users@sqrug.org >Subject: [sqr-users] Max effective date evaluation not in sql > >Everyone, > >I have a simple issue that I can't seem to work out. I need to determine >the max effective dated row from the minimum row returned in three >different select statements. >Here are my sample select statements > >select com.effdt >from ps_company_tbl com >where com.effdt = (select > min(inner.effdt) > from ps_company_tbl inner > where inner.company = com.company and > inner.effdt <= sysdate) > >select pay.effdt >from ps_paygroup_tbl pay >where pay.effdt = (select > min(inner.effdt) > from ps_paygroup_tbl inner > where inner.company = pay.company and > and inner.paygroup = pay.paygroup > inner.effdt <= sysdate) > >select loc.effdt >from ps_location_tbl loc >where loc.effdt = (select > min(inner.effdt) > from ps_location_tbl inner > where inner.company = loc.company and > and inner.location = loc.location > inner.effdt <= sysdate) > >Now that I have loc.effdt, pay.effdt and com.effdt how do I determine >which is the max effective dated row. > >Thanks in advance > >Deb > > > >******************************************************************** >CONFIDENTIALITY NOTICE: >The information contained in this e-mail is legally privileged and >confidential information intended only for the use of the individual or >entity to whom it is addressed. If the reader of this message is not >the intended recipient, you are hereby notified that any viewing, >dissemination, distribution, or copying of this e-mail message is >strictly prohibited. If you have received and/or are viewing this >e-mail in error, please immediately notify the sender by reply e-mail, >and delete this e-mail from your system. > >Thank you. >******************************************************************** > > >_______________________________________________ >sqr-users mailing list >sqr-users@sqrug.org >http://www.sqrug.org/mailman/listinfo/sqr-users > > > >------------------------------ > >Message: 3 >Date: Mon, 11 Oct 2004 16:05:47 -0400 >From: Scott Eubank >Subject: [sqr-users] ISOLATION LEVEL >To: 'SQRUG' >Message-ID: > ><4B25267A624F0F46AAAF8F42CB62863102A5DF98@exchange2k2.corp.advancestores.com> > >Content-Type: text/plain; charset="iso-8859-1" > > > I need to do a "dirty read", basically an uncommited read for a report so >that I do not disrupt processes that are running at nite. How do I >"change isolation Level UR" in an SQR for the entire SQR. > > AIX, DB2 , PSOFT-Tools 7.59 > >Any help would be greatly appreciated. > > >Scott Ebank > > > >=================================================================== >NOTICE - CONFIDENTIAL AND PRIVILEGED - This e-mail may contain >privileged and confidential information and is intended only for the >addressee named above. If you received this message in error, >please immediately notify the sender by return e-mail and delete the >original message; any distribution, copying or use of this e-mail >by you is strictly prohibited and may be unlawful. > > > >------------------------------ > >_______________________________________________ >sqr-users mailing list >sqr-users@sqrug.org >http://www.sqrug.org/mailman/listinfo/sqr-users > > >End of sqr-users Digest, Vol 8, Issue 7 >*************************************** ------------------------------------------------------------------- Neal Patterson, Sr. Programmer/Analyst College Relations Information Systems Services, Colby College 4333 Mayflower Hill Drive, Waterville, ME 04901-8843 Phone: (207)872-3419 E-mail: rnpatter@colby.edu ------------------------------------------------------------------- _______________________________________________ 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 Oct 12 11:05:00 2004 Date: Tue, 12 Oct 2004 17:03:48 +0100 (BST) From: Amber S Subject: [sqr-users] Data truncated during update from flat file Hi folks, I am trying to update a peoplesoft record with inputs from a flat file, the length of the data contained in my variable varies from reord to record and I am coming across an odd situation wherein only part of my variable is inserted in the colum if it follows a row with a shorter length variable. Has anyone encountered this before? any comments? I am checking the values of my variable in my log file and they are perfectly alright, no truncates happening there, it is only during the update that this occurs and the variables do not specify the field length, all are within the 30 chars specified. Any suggestions are much appreciated. Thanks, Amber ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.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 Tue Oct 12 11:10:25 2004 Subject: RE: [sqr-users] Data truncated during update from flat file Date: Tue, 12 Oct 2004 10:09:11 -0600 From: "Bartlett, Dwain" Are the fields in your input record delimited in any way? If they are then= you can use the unstring command. Dwain Bartlett SABHRS Services Bureau, ITSD HR Systems Analyst (406) 444-0418 dbartlett@state.mt.us -----Original Message----- From: Amber S [mailto:amber_sqr@yahoo.co.uk] Sent: Tuesday, October 12, 2004 10:04 AM To: sqr-users@sqrug.org Subject: [sqr-users] Data truncated during update from flat file Hi folks, I am trying to update a peoplesoft record with inputs from a flat file, the length of the data contained in my variable varies from reord to record and I am coming across an odd situation wherein only part of my variable is inserted in the colum if it follows a row with a shorter length variable. Has anyone encountered this before? any comments? I am checking the values of my variable in my log file and they are perfectly alright, no truncates happening there, it is only during the update that this occurs and the variables do not specify the field length, all are within the 30 chars specified. Any suggestions are much appreciated. Thanks, Amber =09 =09 =09=09 ___________________________________________________________ALL-NEW Yahoo! M= essenger - all new features - even more fun! http://uk.messenger.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 Tue Oct 12 11:16:37 2004 From: James Womeldorf Subject: RE: [sqr-users] Data truncated during update from flat file Date: Tue, 12 Oct 2004 11:15:32 -0500 Are you checking the actual field length in the database or are you checking it in PS? It is possible that they are different. Could there be tabs in you input file? Could you send us a code snippet? Jim -----Original Message----- From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org [mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org]On Behalf Of Amber S Sent: Tuesday, October 12, 2004 11:04 AM To: sqr-users@sqrug.org Subject: [sqr-users] Data truncated during update from flat file Hi folks, I am trying to update a peoplesoft record with inputs from a flat file, the length of the data contained in my variable varies from reord to record and I am coming across an odd situation wherein only part of my variable is inserted in the colum if it follows a row with a shorter length variable. Has anyone encountered this before? any comments? I am checking the values of my variable in my log file and they are perfectly alright, no truncates happening there, it is only during the update that this occurs and the variables do not specify the field length, all are within the 30 chars specified. Any suggestions are much appreciated. Thanks, Amber ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.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 Tue Oct 12 11:22:43 2004 Date: Tue, 12 Oct 2004 17:18:35 +0100 (BST) From: Amber S Subject: RE: [sqr-users] Data truncated during update from flat file Yes, I am using a csv file and also assigning to variables using the unstring command, there is nothing in the csv file that should cause this, as I mentioned earlier, the Show command displays the complete variable --- "Bartlett, Dwain" wrote: > Are the fields in your input record delimited in any > way? If they are then you can use the unstring > command. > > Dwain Bartlett > SABHRS Services Bureau, ITSD > HR Systems Analyst > (406) 444-0418 > dbartlett@state.mt.us > > > -----Original Message----- > From: Amber S [mailto:amber_sqr@yahoo.co.uk] > Sent: Tuesday, October 12, 2004 10:04 AM > To: sqr-users@sqrug.org > Subject: [sqr-users] Data truncated during update > from flat file > > > Hi folks, > I am trying to update a peoplesoft record with > inputs > from a flat file, the length of the data contained > in > my variable varies from reord to record and I am > coming across an odd situation wherein only part of > my > variable is inserted in the colum if it follows a > row > with a shorter length variable. > Has anyone encountered this before? any comments? > I am checking the values of my variable in my log > file > and they are perfectly alright, no truncates > happening > there, it is only during the update that this occurs > and the variables do not specify the field length, > all > are within the 30 chars specified. > Any suggestions are much appreciated. > Thanks, > Amber > > > > > > > ___________________________________________________________ALL-NEW > Yahoo! Messenger - all new features - even more fun! > http://uk.messenger.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 > ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.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 Tue Oct 12 11:26:34 2004 Subject: RE: [sqr-users] Data truncated during update from flat file Date: Tue, 12 Oct 2004 11:10:51 -0500 From: "Knapp, Richard" The first question I answer in any file read is: Am I reading what is in the file?=20 Richard Knapp Database Programmer/Analyst Institutional Research and Planning University of Missouri System 573-882-8856 knappr@umsystem.edu =20 -----Original Message----- From: sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org [mailto:sqr-users-bounces+knappr=3Dumsystem.edu@sqrug.org] On Behalf Of Amber S Sent: Tuesday, October 12, 2004 11:04 AM To: sqr-users@sqrug.org Subject: [sqr-users] Data truncated during update from flat file Hi folks, I am trying to update a peoplesoft record with inputs from a flat file, the length of the data contained in my variable varies from reord to record and I am coming across an odd situation wherein only part of my variable is inserted in the colum if it follows a row with a shorter length variable. Has anyone encountered this before? any comments? I am checking the values of my variable in my log file and they are perfectly alright, no truncates happening there, it is only during the update that this occurs and the variables do not specify the field length, all are within the 30 chars specified. Any suggestions are much appreciated. Thanks, Amber =09 =09 =09=09 ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.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 Tue Oct 12 11:30:45 2004 Subject: RE: [sqr-users] Data truncated during update from flat file Date: Tue, 12 Oct 2004 10:29:37 -0600 From: "Bartlett, Dwain" Here is a sample unstring command. unstring $record by ',' into $product $invoice_id $invoice_dt $vendor $pr= iority $account $amount $descr $bu $due_dt Dwain Bartlett SABHRS Services Bureau, ITSD HR Systems Analyst (406) 444-0418 dbartlett@state.mt.us -----Original Message----- From: Amber S [mailto:amber_sqr@yahoo.co.uk] Sent: Tuesday, October 12, 2004 10:19 AM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Data truncated during update from flat file Yes, I am using a csv file and also assigning to variables using the unstring command, there is nothing in the csv file that should cause this, as I mentioned earlier, the Show command displays the complete variable --- "Bartlett, Dwain" wrote:=20 > Are the fields in your input record delimited in any > way? If they are then you can use the unstring > command. >=20 > Dwain Bartlett > SABHRS Services Bureau, ITSD > HR Systems Analyst > (406) 444-0418 > dbartlett@state.mt.us >=20 >=20 > -----Original Message----- > From: Amber S [mailto:amber_sqr@yahoo.co.uk] > Sent: Tuesday, October 12, 2004 10:04 AM > To: sqr-users@sqrug.org > Subject: [sqr-users] Data truncated during update > from flat file >=20 >=20 > Hi folks, > I am trying to update a peoplesoft record with > inputs > from a flat file, the length of the data contained > in > my variable varies from reord to record and I am > coming across an odd situation wherein only part of > my > variable is inserted in the colum if it follows a > row > with a shorter length variable. > Has anyone encountered this before? any comments? > I am checking the values of my variable in my log > file > and they are perfectly alright, no truncates > happening > there, it is only during the update that this occurs > and the variables do not specify the field length, > all > are within the 30 chars specified. > Any suggestions are much appreciated. > Thanks, > Amber >=20 >=20 >=20 >=20=09 >=20=09 >=20=09=09 > ___________________________________________________________ALL-NEW > Yahoo! Messenger - all new features - even more fun! > http://uk.messenger.yahoo.com >=20 > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users >=20 > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users >=20=20 =09 =09 =09=09 ___________________________________________________________ALL-NEW Yahoo! M= essenger - all new features - even more fun! http://uk.messenger.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 Tue Oct 12 11:33:43 2004 Date: Tue, 12 Oct 2004 17:32:06 +0100 (BST) From: Amber S Subject: RE: [sqr-users] Data truncated during update from flat file I have checked the field length in the database and there arnt any tabs in the input file. Here is the procedure that does the update !************************************************************************ Begin-procedure Update_PS !************************************************************************ Display $Dv_Position_Nbr Display $Job_Effdt Display $DV_Job_Title Show 'Updates' #sql-count Begin-Sql UPDATE PS_POSITION_DATA SET PS_POSITION_DATA.DESCR = $DV_Job_Title WHERE PS_POSITION_DATA.POSITION_NBR = $Dv_Position_Nbr AND PS_POSITION_DATA.EFFDT >= (SELECT MAX(EFFDT) FROM PS_POSITION_DATA PD2 WHERE PS_POSITION_DATA.POSITION_NBR = PD2.POSITION_NBR AND PD2.EFFDT <= $Job_Effdt) End-Sql End-Procedure --- James Womeldorf wrote: > Are you checking the actual field length in the > database or are you checking > it in PS? It is possible that they are different. > Could there be tabs in you input file? > Could you send us a code snippet? > Jim > > -----Original Message----- > From: > sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org > [mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org]On > Behalf Of > Amber S > Sent: Tuesday, October 12, 2004 11:04 AM > To: sqr-users@sqrug.org > Subject: [sqr-users] Data truncated during update > from flat file > > > Hi folks, > I am trying to update a peoplesoft record with > inputs > from a flat file, the length of the data contained > in > my variable varies from reord to record and I am > coming across an odd situation wherein only part of > my > variable is inserted in the colum if it follows a > row > with a shorter length variable. > Has anyone encountered this before? any comments? > I am checking the values of my variable in my log > file > and they are perfectly alright, no truncates > happening > there, it is only during the update that this occurs > and the variables do not specify the field length, > all > are within the 30 chars specified. > Any suggestions are much appreciated. > Thanks, > Amber > > > > > > > ___________________________________________________________ALL-NEW > Yahoo! > Messenger - all new features - even more fun! > http://uk.messenger.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 > ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.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 Tue Oct 12 11:38:23 2004 Subject: RE: [sqr-users] Data truncated during update from flat file Date: Tue, 12 Oct 2004 10:37:15 -0600 From: "Bartlett, Dwain" Can you send a sample of the input file? Dwain Bartlett SABHRS Services Bureau, ITSD HR Systems Analyst (406) 444-0418 dbartlett@state.mt.us -----Original Message----- From: Amber S [mailto:amber_sqr@yahoo.co.uk] Sent: Tuesday, October 12, 2004 10:32 AM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Data truncated during update from flat file I have checked the field length in the database and there arnt any tabs in the input file. Here is the procedure that does the update !************************************************************************ Begin-procedure Update_PS !************************************************************************ Display $Dv_Position_Nbr Display $Job_Effdt Display $DV_Job_Title Show 'Updates' #sql-count Begin-Sql UPDATE PS_POSITION_DATA SET PS_POSITION_DATA.DESCR =3D $DV_Job_Title WHERE PS_POSITION_DATA.POSITION_NBR =3D $Dv_Position_Nbr AND PS_POSITION_DATA.EFFDT >=3D (SELECT MAX(EFFDT) FROM PS_POSITION_DATA PD2 WHERE PS_POSITION_DATA.POSITION_NBR =3D PD2.POSITION_NBR AND PD2.EFFDT <=3D $Job_Effdt) End-Sql End-Procedure --- James Womeldorf wrote:=20 > Are you checking the actual field length in the > database or are you checking > it in PS? It is possible that they are different. > Could there be tabs in you input file? > Could you send us a code snippet? > Jim >=20 > -----Original Message----- > From: > sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org > [mailto:sqr-users-bounces+jwomeldo=3Dfastenal.com@sqrug.org]On > Behalf Of > Amber S > Sent: Tuesday, October 12, 2004 11:04 AM > To: sqr-users@sqrug.org > Subject: [sqr-users] Data truncated during update > from flat file >=20 >=20 > Hi folks, > I am trying to update a peoplesoft record with > inputs > from a flat file, the length of the data contained > in > my variable varies from reord to record and I am > coming across an odd situation wherein only part of > my > variable is inserted in the colum if it follows a > row > with a shorter length variable. > Has anyone encountered this before? any comments? > I am checking the values of my variable in my log > file > and they are perfectly alright, no truncates > happening > there, it is only during the update that this occurs > and the variables do not specify the field length, > all > are within the 30 chars specified. > Any suggestions are much appreciated. > Thanks, > Amber >=20 >=20 >=20 >=20=09 >=20=09 >=20=09=09 > ___________________________________________________________ALL-NEW > Yahoo! > Messenger - all new features - even more fun!=20 > http://uk.messenger.yahoo.com >=20 > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users >=20 > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users >=20=20 =09 =09 =09=09 ___________________________________________________________ALL-NEW Yahoo! M= essenger - all new features - even more fun! http://uk.messenger.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 Tue Oct 12 11:42:06 2004 From: PSFTPROGRAMMER@aol.com Date: Tue, 12 Oct 2004 12:39:21 EDT Subject: Re: [sqr-users] Data truncated during update from flat file Adding to Dwain's suggestion- Also do a show statement on the values coming out of the flat file, and take those values and the SQL from the program and do an insert into the database directly. I ahve found a couple of times that the data may have an extra comma in it (like a comma in a name) or the data is in the wrong order I am expecting it. Doing this helps track down where the issue is. Also, it may not be the entire file you are having trouble with, but maybe just one line? If you put the show statement as part of an on-error command you will see the data that is actually causing the error. Denise _______________________________________________ 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 Oct 12 11:44:57 2004 From: PSFTPROGRAMMER@aol.com Date: Tue, 12 Oct 2004 12:43:03 EDT Subject: Re: [sqr-users] Data truncated during update from flat file What are the values for $Dv_Position_Nbr, $Job_Effdt, & $DV_Job_Title? _______________________________________________ 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 Oct 12 11:51:19 2004 From: "Lynds,Rick" Subject: RE: [sqr-users] Data truncated during update from flat file Date: Tue, 12 Oct 2004 09:50:03 -0700 UNSTRING is a very literal command. It will split on the specified delimiter character regardless of where quote marks make occur in the input string. If any of your input fields have the remotest possibility of containing commas, you may want to use a more sophisticated method of parsing your flat-file input data. Rick -----Original Message----- From: sqr-users-bounces+rlynds=mwdh2o.com@sqrug.org [mailto:sqr-users-bounces+rlynds=mwdh2o.com@sqrug.org]On Behalf Of Amber S Sent: Tuesday, October 12, 2004 9:19 AM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: RE: [sqr-users] Data truncated during update from flat file Yes, I am using a csv file and also assigning to variables using the unstring command, there is nothing in the csv file that should cause this, as I mentioned earlier, the Show command displays the complete variable _______________________________________________ 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 Oct 12 11:54:57 2004 Date: Tue, 12 Oct 2004 17:51:51 +0100 (BST) From: Amber S Subject: Re: [sqr-users] Data truncated during update from flat file This is a multi-part message in MIME format... --0-1273688325-1097599911=:57672 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Content-Id: Content-Disposition: inline I have attached a sample copy of the csv file. And here I am pasting my unstring code.... begin-procedure read-data-file while not #end-file Read 1 into $Input_Record:3000 Unstring $Input_Record By ',' Into $DV_Emplid $DV_First_Name $DV_Last_Name $DV_Payroll_No $DV_Deptid $DV_Position_Nbr $DV_Job_Title $DV_Reports_To_Nbr let $DV_Emplid = rtrim($Dv_Emplid, ' ') let $Dv_Position_Nbr = lpad($Dv_Position_Nbr, 8, '0') let $Dv_Job_Title = substr($Dv_Job_Title,1,30) let #DV_count = #DV_count +1 do job-title if #end-file break end-if end-while Close 1 end-procedure Also the $Job_Effdt varible holds the value of the current EFFDT of an employees record --- PSFTPROGRAMMER@aol.com wrote: > What are the values for $Dv_Position_Nbr, > $Job_Effdt, & $DV_Job_Title? > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com --0-1273688325-1097599911=:57672 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Description: AUTOMATIC WARNING Here was a non admissible message part of 'application/octet-stream' MIME type. It has been automatically discarded before sending the message to the list. --0-1273688325-1097599911=:57672 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 --0-1273688325-1097599911=:57672-- From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Tue Oct 12 11:58:12 2004 Date: Tue, 12 Oct 2004 17:54:51 +0100 (BST) From: Amber S Subject: RE: [sqr-users] Data truncated during update from flat file I have tried the same with '~' as well, still the same problem --- "Lynds,Rick" wrote: > UNSTRING is a very literal command. It will split > on the specified > delimiter character regardless of where quote marks > make occur in the input > string. If any of your input fields have the > remotest possibility of > containing commas, you may want to use a more > sophisticated method of > parsing your flat-file input data. > > Rick > > -----Original Message----- > From: sqr-users-bounces+rlynds=mwdh2o.com@sqrug.org > [mailto:sqr-users-bounces+rlynds=mwdh2o.com@sqrug.org]On > Behalf Of Amber > S > Sent: Tuesday, October 12, 2004 9:19 AM > To: This list is for discussion about the SQR > database reporting > language from Hyperion Solutions. > Subject: RE: [sqr-users] Data truncated during > update from flat file > > > Yes, I am using a csv file and also assigning to > variables using the unstring command, there is > nothing > in the csv file that should cause this, as I > mentioned > earlier, the Show command displays the complete > variable > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.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 Tue Oct 12 12:15:21 2004 Subject: RE: [sqr-users] Data truncated during update from flat file Date: Tue, 12 Oct 2004 11:14:11 -0600 From: "Bartlett, Dwain" I did not get the attachment, rename it to a xxxxx.dat file. Dwain Bartlett SABHRS Services Bureau, ITSD HR Systems Analyst (406) 444-0418 dbartlett@state.mt.us -----Original Message----- From: Amber S [mailto:amber_sqr@yahoo.co.uk] Sent: Tuesday, October 12, 2004 10:52 AM To: This list is for discussion about the SQR database reporting language from Hyperion Solutions. Subject: Re: [sqr-users] Data truncated during update from flat file I have attached a sample copy of the csv file. And here I am pasting my unstring code.... begin-procedure read-data-file while not #end-file Read 1 into $Input_Record:3000 Unstring $Input_Record By ',' Into $DV_Emplid $DV_First_Name $DV_Last_Name $DV_Payroll_No $DV_Deptid $DV_Position_Nbr $DV_Job_Title $DV_Reports_To_Nbr let $DV_Emplid =3D rtrim($Dv_Emplid, ' ') let $Dv_Position_Nbr =3D lpad($Dv_Position_Nbr, 8, '0') let $Dv_Job_Title =3D substr($Dv_Job_Title,1,30) let #DV_count =3D #DV_count +1 do job-title if #end-file break end-if end-while Close 1 end-procedure Also the $Job_Effdt varible holds the value of the current EFFDT of an employees record --- PSFTPROGRAMMER@aol.com wrote:=20 > What are the values for $Dv_Position_Nbr, > $Job_Effdt, & $DV_Job_Title? >=20 > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users >=20=20 =09 =09 =09=09 ___________________________________________________________ALL-NEW Yahoo! M= essenger - all new features - even more fun! http://uk.messenger.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 Tue Oct 12 12:18:18 2004 Subject: RE: [sqr-users] Data truncated during update from flat file From: kristy.engelkes@rsmi.com Date: Tue, 12 Oct 2004 12:15:39 -0500 We have had this happen a few times. It was due to the mdac being incorrect, out-of-sync, or some type of problem. After updating the mdac, it then worked correctly. This definitely sounds like the same type of problem. "Amber S" Sent by: sqr-users-bounces+kristy.engelkes=rsmi.com@sqrug.org 10/12/2004 11:54 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] Data truncated during update from flat file I have tried the same with '~' as well, still the same problem --- "Lynds,Rick" wrote: > UNSTRING is a very literal command. It will split > on the specified > delimiter character regardless of where quote marks > make occur in the input > string. If any of your input fields have the > remotest possibility of > containing commas, you may want to use a more > sophisticated method of > parsing your flat-file input data. > > Rick > > -----Original Message----- > From: sqr-users-bounces+rlynds=mwdh2o.com@sqrug.org > [mailto:sqr-users-bounces+rlynds=mwdh2o.com@sqrug.org]On > Behalf Of Amber > S > Sent: Tuesday, October 12, 2004 9:19 AM > To: This list is for discussion about the SQR > database reporting > language from Hyperion Solutions. > Subject: RE: [sqr-users] Data truncated during > update from flat file > > > Yes, I am using a csv file and also assigning to > variables using the unstring command, there is > nothing > in the csv file that should cause this, as I > mentioned > earlier, the Show command displays the complete > variable > > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.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 Wed Oct 13 03:58:19 2004 Date: Wed, 13 Oct 2004 09:54:32 +0100 (BST) From: Amber S Subject: RE: [sqr-users] Data truncated during update from flat file This is a multi-part message in MIME format... --0-1056027300-1097657672=:68124 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Content-Id: Content-Disposition: inline Hello Dwain, I have renamed the file to sample.dat and have attached it, hope you receive it this time. thanks! --- "Bartlett, Dwain" wrote: > I did not get the attachment, rename it to a > xxxxx.dat file. > > Dwain Bartlett > SABHRS Services Bureau, ITSD > HR Systems Analyst > (406) 444-0418 > dbartlett@state.mt.us > > > -----Original Message----- > From: Amber S [mailto:amber_sqr@yahoo.co.uk] > Sent: Tuesday, October 12, 2004 10:52 AM > To: This list is for discussion about the SQR > database reporting > language from Hyperion Solutions. > Subject: Re: [sqr-users] Data truncated during > update from flat file > > > I have attached a sample copy of the csv file. > And here I am pasting my unstring code.... > > begin-procedure read-data-file > > while not #end-file > > Read 1 into $Input_Record:3000 > > Unstring $Input_Record By ',' Into > > $DV_Emplid > $DV_First_Name > $DV_Last_Name > $DV_Payroll_No > $DV_Deptid > $DV_Position_Nbr > $DV_Job_Title > $DV_Reports_To_Nbr > > let $DV_Emplid = rtrim($Dv_Emplid, ' ') > let $Dv_Position_Nbr = lpad($Dv_Position_Nbr, 8, > '0') > let $Dv_Job_Title = substr($Dv_Job_Title,1,30) > > let #DV_count = #DV_count +1 > do job-title > > if #end-file > break > end-if > > end-while > > Close 1 > > end-procedure > > Also the $Job_Effdt varible holds the value of the > current EFFDT of an employees record > > > --- PSFTPROGRAMMER@aol.com wrote: > > What are the values for $Dv_Position_Nbr, > > $Job_Effdt, & $DV_Job_Title? > > > > _______________________________________________ > > sqr-users mailing list > > sqr-users@sqrug.org > > http://www.sqrug.org/mailman/listinfo/sqr-users > > > > > > > > ___________________________________________________________ALL-NEW > Yahoo! Messenger - all new features - even more fun! > http://uk.messenger.yahoo.com > > _______________________________________________ > sqr-users mailing list > sqr-users@sqrug.org > http://www.sqrug.org/mailman/listinfo/sqr-users > ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com --0-1056027300-1097657672=:68124 Content-Type: text/plain; name="sample.dat" Content-Description: sample.dat Content-Disposition: inline; filename="sample.dat" 6150,Mary,Poppins,X1077890,DEPTID,00005490,Systems Developer HAI,00003299 5023,Joe,Bloggs,X1070440,DEPTID,00005456,Clerical Officer - BBV,00005485 6233,Homer,Simpson,X2073400,DEPTID,00005609,Personal Secretary,00004980 6209,Miu,Miu,X1022095,DEPTID,00005593,Personal Secretary,00004979 --0-1056027300-1097657672=:68124 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 --0-1056027300-1097657672=:68124-- From sqr-users-bounces+sqrusersarch=sqrug.org@sqrug.org Wed Oct 13 07:31:24 2004 Subject: RE: [sqr-users] Re: sqr-users Digest, Vol 8, Issue 7 Date: Wed, 13 Oct 2004 08:28:46 -0400 From: "Debbie Larney" Neil, That is a great idea. Thank you so much. I will definitely use it. Deb -----Original Message----- From: Neal Patterson [mailto:rnpatter@colby.edu] Sent: Tuesday, October 12, 2004 9:40 AM To: sqr-users@sqrug.org Subject: [sqr-users] Re: sqr-users Digest, Vol 8, Issue 7 If Debbie wants an SQL approach to picking the maximum if her 3 dates,=20 maybe something like this would do: select max(MyDate) from (select com.effdt as MyDate from ps_company_tbl com where com.effdt =3D (select min(inner.effdt) from ps_company_tbl inner where inner.company =3D com.company and inner.effdt <=3D sysdate) union select pay.effdt as MyDate from ps_paygroup_tbl pay where pay.effdt =3D (select min(inner.effdt) from ps_paygroup_tbl inner where inner.company =3D pay.company and and inner.paygroup =3D pay.paygroup inner.effdt <=3D sysdate) union select loc.effdt as MyDate from ps_location_tbl loc where loc.effdt =3D (select min(inner.effdt) from ps_location_tbl inner where inner.company =3D loc.company and and inner.location =3D loc.location inner.effdt <=3D sysdate)) as T - Neal Patterson ----------------------------------------------------------------------- At 06:03 AM 10/12/2004 -0500, you 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. Max effective date evaluation not in sql (Debbie Larney) > 2. RE: Max effective date evaluation not in sql (Knapp, Richard) > 3. ISOLATION LEVEL (Scott Eubank) > > >---------------------------------------------------------------------- > >Message: 1 >Date: Mon, 11 Oct 2004 14:24:09 -0400 >From: "Debbie Larney" >Subject: [sqr-users] Max effective date evaluation not in sql >To: >Message-ID: > <768DB1A57CCF794A9A6F7288B66D8F6563A8E1@HAMMERHEAD.ltc.com> >Content-Type: text/plain; charset=3D"iso-8859-1" > >Everyone, > >I have a simple issue that I can't seem to work out. I need to determine >= the max effective dated row from the minimum row returned in three=20 >different select statements. >Here are my sample select statements > >select com.effdt >from ps_company_tbl com >where com.effdt =3D (select > min(inner.effdt) > from ps_company_tbl inner > where inner.company =3D com.company and > inner.effdt <=3D sysdate) > >select pay.effdt >from ps_paygroup_tbl pay >where pay.effdt =3D (select > min(inner.effdt) > from ps_paygroup_tbl inner > where inner.company =3D pay.company and > and inner.paygroup =3D pay.paygroup > inner.effdt <=3D sysdate) > >select loc.effdt >from ps_location_tbl loc >where loc.effdt =3D (select > min(inner.effdt) > from ps_location_tbl inner > where inner.company =3D loc.company and > and inner.location =3D loc.location > inner.effdt <=3D sysdate) > >Now that I have loc.effdt, pay.effdt and com.effdt how do I determine=20 >which is the max effective dated row. > >Thanks in advance > >Deb > > > >******************************************************************** >CONFIDENTIALITY NOTICE: >The information contained in this e-mail is legally privileged and=20 >confidential information intended only for the use of the individual or=20 >entity to whom it is addressed. If the reader of this message is not the = >intended recipient, you are hereby notified that any viewing,=20 >dissemination, distribution, or copying of this e-mail message is strictly= >prohibited. If you have received and/or are viewing this e-mail in error= ,=20 >please immediately notify the sender by reply e-mail, and delete this=20 >e-mail from your system. > >Thank you. >******************************************************************** > > > > >------------------------------ > >Message: 2 >Date: Mon, 11 Oct 2004 13:52:59 -0500 >From: