Re: Updating a table

[ANDERSD2 <ANDERSD2@OAKWOOD.ORG>]

I thought I saw a variable called $temptable, but the below suggestion has
$_temptable.  Is the UPDATE being executed within a local procedure?  If so,
be sure to pass a value for $_temptable.

let $temptable = 'PS_EXAMPLE_THINGY'
let $Building = 'A BIG ONE'
let #Count = 7
do Update-Procedure($temptable, $Bulding, #Count)

begin-procedure Update-Procedure($_temptable, $Building, #count)
update [$_temptable]
set col3 = #count
where col1SQL = [$Building]
end-procedure

Please note in the begin-procedure line the utilization of the local
variable $_temptable.  I believe if you tried to use $temptable it would be
null.


-----Original Message-----
From: Lu, Clint [mailto:clint.lu@CAREMARK.COM]
Sent: Wednesday, June 13, 2001 1:29 PM
To: SQR-USERS@list.iex.net
Subject: Re: Updating a table


The last thing I can think of is the variable $building might have a blank
at the end. before doing update. do this to see if it works.

   let $building = rtrim($building,' ')
   update [$_temptable] set col3 = #count where col1SQL = [$building]

Hope this works.




Clint Lu
Sr. Programmer/Analyst
Caremark Rx, Inc.
Phone: (847) 559-4717
Fax    : (847) 559-4840


-----Original Message-----
From: Philippe Godin [mailto:philippe.godin@VIDEOTRON.CA]
Sent: Wednesday, June 13, 2001 11:55 AM
To: SQR-USERS@list.iex.net
Subject: Re: Updating a table


My problem doesn't seem to come from the statement but from the syntax since
update [$_temptable] set col3 = 52 where col1SQL = 'BAT02' works
and  both update [$_temptable] set col3 = 52 where col1SQL = [$building]
or update [$_temptable] set col3 = #count where col1SQL = [$building]
doesn't...
I've checked and both variables contains data before entering the update
clause.

-----Message d'origine-----
De : Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]De la part de Lu, Clint
Envoyé : 13 juin, 2001 12:19
À : SQR-USERS@list.iex.net
Objet : Re: Updating a table


A couple of things I can think of. First, do you use insert and update
statements as the same program? if you do, then make sure to commit after
you finish with insert before you do the update. Second, to check the
$building and #count variables to make sure that they have the values that
you expect to update. On the other hand, comment out the update statement to
see if the insert statement is working and then continue working with update
statement. Hope this helps.



Clint Lu
Sr. Programmer/Analyst
Caremark Rx, Inc.
Phone: (847) 559-4717
Fax    : (847) 559-4840


-----Original Message-----
From: Philippe Godin [mailto:philippe.godin@VIDEOTRON.CA]
Sent: Wednesday, June 13, 2001 10:15 AM
To: SQR-USERS@list.iex.net
Subject: Updating a table


Good day,

    Does any of you out there have ever tried to update an SQL SERVER 7.0
temporary table using variables?
    This is my problem, I'm trying to update a table that I've created
likewise:

CREATE TABLE [$temptable] (col1SQL varchar(30),col2 int,col3 float,col4
float,col5 float,col6 float,col7 float,col8 float,col9 float,col10
float,col11 float,col12 float,col13 float);

$temptable is a timestamp.

I succeed in doing inserts such as:

insert into [$_temptable] values
($building,#count,'','','','','','','','','','','')

But can't get this to work:

update [$_temptable] set col3 = #count where col1SQL = [$building]

Sqr isn't giving any errors on this but put 0 everywhere in the table
instead of putting 52...

I've tried different ways of using the varaibles such as putting them within
[] etc. but can't get this to work

It seems that the more strange it looks the best it's work.

TIA
Philippe Godin