[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

RE: [sqr-users] Using On-Error=Skip

Subject: RE: [sqr-users] Using On-Error=Skip
From: Peter Burton <Peter_Burton@Hyperion.com>
Date: Mon, 2 Feb 2004 09:52:13 -0800
List-id: This list is for discussion about the SQR database reporting language from Hyperion. <sqr-users.sqrug.org>


Lori,

1) In the Begin-Setup section

        Begin-SQL On-Error=Skip
        Create temp table tmp_LeadFIDs   ! all CustIDs from the Leads table
(if exists)
                (
                cust_id  integer
                )
        End-SQL

2) In the Begin-Report section

        Move '{$prefix}_targs' To $Table
        Begin-SQL On-Error=Ignore
        Insert into tmp_LeadFIDs
        Select Distinct
        cust_id
         From [$Table]
        End-SQL

2) Add procedure "Ignore"

        Begin-Procedure Ignore
        End-Procedure

Peter
        
>  -----Original Message-----
> From:         sqr-users-admin@sqrug.org@HYPERION   On Behalf Of "Richard C
> Spoth" <Spoth.Richard@healthnow.org>
> Sent: Monday, February 02, 2004 12:35 PM
> To:   sqr-users@sqrug.org
> Cc:   sqr-users@sqrug.org; sqr-users-admin@sqrug.org
> Subject:      RE: [sqr-users] Using On-Error=Skip
> 
> Then substitue that table name using a varaible.
> 
> Let $TableName ='{$prefix}_targs'
> 
> Begin-SQL On-Error=Skip
> Insert into tmp_LeadFIDs
> Select Distinct
> cust_id
> >From $TableName                          ! prefix passed in LeadArgs
> file
> End-SQL
> 
> Rick Spoth
> 
> 
> 
> 
> 
> Lori.Puntasecca@frx.com
> Sent by: sqr-users-admin@sqrug.org
> 02/02/04 12:23 PM
> Please respond to sqr-users
> 
> 
>               To:     sqr-users@sqrug.org
>               cc:
>               Subject:        RE: [sqr-users] Using On-Error=Skip
> 
> 
> Hi again -
> Sorry if I wasnt clear - I do create the tmp_LeadFIDs table. It's the
> {$prefix}_targs (a database table) that does not exist (for some runs).
> Thanks,
> Lori
> 
> -----Original Message-----
> From: Richard C Spoth [mailto:Spoth.Richard@healthnow.org
> <mailto:Spoth.Richard@healthnow.org> ]
> Sent: Monday, February 02, 2004 12:18 PM
> To: sqr-users@sqrug.org
> Cc: sqr-users@sqrug.org; sqr-users-admin@sqrug.org
> Subject: RE: [sqr-users] Using On-Error=Skip
> You should be able to just set a standard string variable with the table.
> 
> Try something like this.
> 
> Let $TableName ='tmp_LeadFIDs'
> 
> Begin-SQL On-Error=Skip
> Insert into [$TableName]
> Select Distinct
> cust_id
> >From {$prefix}_targs                          ! prefix passed in LeadArgs
> file
> End-SQL
> 
> Rick Spoth
> 
> 
> 
> 
> 
> Lori.Puntasecca@frx.com
> Sent by: sqr-users-admin@sqrug.org
> 02/02/04 12:05 PM
> Please respond to sqr-users
> 
> 
>               To:     sqr-users@sqrug.org
>               cc:
>               Subject:        RE: [sqr-users] Using On-Error=Skip
> 
> 
> Thanks for the quick response, Rick.
> Questions re: your solutions:
> 1 - I don't want to create the table if it does exist (I would get an
> error
> too). Is there a way to say 'If not exists, Create table ...'? 2 - This is
> the only place in the SQR I attempt to access this table. That is why I
> try
> to load the data to a temp table to be accessed later in the program. If I
> use a substitution #variable (rather than an ask (@ file) variable, will
> that avoid the error?
> 
> Thanks again,
> Lori Puntasecca
> 
> -----Original Message-----
> From: Richard C Spoth [mailto:Spoth.Richard@healthnow.org
> <mailto:Spoth.Richard@healthnow.org> ]
> Sent: Monday, February 02, 2004 11:08 AM
> To: sqr-users@sqrug.org
> Cc: sqr-users@sqrug.org; sqr-users-admin@sqrug.org
> Subject: Re: [sqr-users] Using On-Error=Skip
> 
> 
> SQR needs to see the table when it complies.
> 
> There are 2 solutions I have used in the past.
> 
> 1.  Create the table in the Begin-Setup
> 
> or
> 
> 2.  Use a SQR substtitution variable for the table name in all queries.
> 
> Rick Spoth
> 
> 
> 
> 
> 
> Lori.Puntasecca@frx.com
> Sent by: sqr-users-admin@sqrug.org
> 02/02/04 10:50 AM
> Please respond to sqr-users
> 
> 
>               To:     sqr-users@sqrug.org
>               cc:
>               Subject:        [sqr-users] Using On-Error=Skip
> 
> 
> Hi All -
> I created an SQR that needs to check for the existence of a table and if
> it
> exists load the cust_id's to a temp table.
> It works fine if the table exists but if it doesn't I get an error on
> compile:
> 
> (SQR 5528) INFORMIX SQL PREPARE/DECLARE error -206 (ISAM: -111) in cursor
> 4:
>       The specified table (aafp_targs) is not in the database.
> 
> Here is the code I use:
> 
> Begin-Setup
> 
> Begin-SQL
> Create temp table tmp_LeadFIDs                        ! all CustIDs from
> the
> Leads table (if exists)
> (cust_id  integer
> );
> End-SQL
> 
> Begin-SQL On-Error=Skip
> Insert into tmp_LeadFIDs
> Select Distinct
> cust_id
> >From {$prefix}_targs                          ! prefix passed in LeadArgs
> file
> End-SQL
> 
> End-Setup
> 
> Does anyone have any other ideas on how to do this?
> Thanks in advance,
> Lori A Puntasecca
> Forest Laboratories, Inc.
> MIS- Data Tools Team
> email:  <mailto:lori.puntasecca@frx.com <mailto:lori.puntasecca@frx.com> >
> lori.puntasecca@frx.com
> 
> 
> 
> 
> ____________________________________________________________________
> 
> This e-mail and its attachments may contain Forest Laboratories, Inc.
> proprietary information that is privileged, confidential or subject to
> copyright belonging to Forest Laboratories, Inc. This e-mail is intended
> solely for the use of the individual or entity to which it is addressed.
> If
> you are not the intended recipient of this e-mail, or the employee or
> agent
> responsible for delivering this e-mail to the intended recipient, you are
> hereby notified that any dissemination, distribution, copying or action
> taken in relation to the contents of and attachments to this e-mail is
> strictly prohibited and may be unlawful. If you have received this e-mail
> in
> error, please notify the sender immediately and permanently delete the
> original and any copy of this e-mail and any printout.
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users
> <http://www.sqrug.org/mailman/listinfo/sqr-users> 
> 
> 
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users
> <http://www.sqrug.org/mailman/listinfo/sqr-users> 
> 
> 
> 
> ____________________________________________________________________
> 
> This e-mail and its attachments may contain Forest Laboratories, Inc.
> proprietary information that is privileged, confidential or subject to
> copyright belonging to Forest Laboratories, Inc. This e-mail is intended
> solely for the use of the individual or entity to which it is addressed.
> If
> you are not the intended recipient of this e-mail, or the employee or
> agent
> responsible for delivering this e-mail to the intended recipient, you are
> hereby notified that any dissemination, distribution, copying or action
> taken in relation to the contents of and attachments to this e-mail is
> strictly prohibited and may be unlawful. If you have received this e-mail
> in
> error, please notify the sender immediately and permanently delete the
> original and any copy of this e-mail and any printout.
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users
> <http://www.sqrug.org/mailman/listinfo/sqr-users> 
> 
> 
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users
> <http://www.sqrug.org/mailman/listinfo/sqr-users> 
> 
> 
> 
> ____________________________________________________________________
> 
> This e-mail and its attachments may contain Forest Laboratories, Inc.
> proprietary information that is privileged, confidential or subject to
> copyright belonging to Forest Laboratories, Inc. This e-mail is intended
> solely for the use of the individual or entity to which it is addressed.
> If
> you are not the intended recipient of this e-mail, or the employee or
> agent
> responsible for delivering this e-mail to the intended recipient, you are
> hereby notified that any dissemination, distribution, copying or action
> taken in relation to the contents of and attachments to this e-mail is
> strictly prohibited and may be unlawful. If you have received this e-mail
> in
> error, please notify the sender immediately and permanently delete the
> original and any copy of this e-mail and any printout.
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
> <http://www.sqrug.org/mailman/listinfo/sqr-users> 
> 
> 
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
> <http://www.sqrug.org/mailman/listinfo/sqr-users> 
> 


************************************************************************

If you have received this e-mail in error, please delete it and notify the 
sender as soon as possible. The contents of this e-mail may be confidential and 
the unauthorized use, copying, or dissemination of it and any attachments to 
it, is prohibited. 

Internet communications are not secure and Hyperion does not, therefore, accept 
legal responsibility for the contents of this message nor for any damage caused 
by viruses.  The views expressed here do not necessarily represent those of 
Hyperion.

For more information about Hyperion, please visit our Web site at 
www.hyperion.com

Here was a non admissible message part of 'application/ms-tnef' MIME
type. It has been automatically discarded before sending the message to
the list.

Prev by Date: RE: [sqr-users] Mail Attachment thru SQR on UNIX
Next by Date: RE: [sqr-users] Using On-Error=Skip
Previous by thread: RE: [sqr-users] Using On-Error=Skip
Next by thread: RE: [sqr-users] Using On-Error=Skip
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page