| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 03/15/2001 : 13:09:33
|
Mala writes "Hi,
I have got dynamic query built as below.
update t_inv_header1 set inv_para1="DIS",inv_per1=4,inv_para2="FGT",inv_para3="INS",
inv_per3=25,inv_para4="LOC",inv_per4=2,inv_para5="TOT",
inv_pamt1=160,
inv_pamt2=0,
inv_pamt3=0,
inv_pamt4=4000-160,
inv_pamt5=4000+4000-160-160+0+0,
where inv_no ="PI0100001" and inv_tcode="pi1"
My problem is values assinged to columns to be compued. But all these things are stored in varchar data type and not allowing me to do so.
eg. inv_pamt4 should be "3840" instead of 4000-160
I can't compute the values before assinging to variables b'coz
I will be stuffing/replaceing the numeric fields depednign other
variable.
Like if I got my column values like this "GRS-DIS+FGT+LOC"
if will go on replacing my values for the same as below.
set @v_pexpfld = replace(@v_pExpfld1,'GRS',@v_amt)
set @v_pexpfld=replace(@v_pexpfld,'DIS',@vpexpfld)
please help me how can I get the desired values.
Bye,
Mala" |
|
|
btrimpop
Posting Yak Master
USA
214 Posts |
Posted - 03/15/2001 : 14:17:08
|
There are several approaches you can take. All of them will require converting from the varchar to numeric and back I'm afraid.
If I understand correctly your inv_pamt# columns are varchars, correct?
If you are building the update dynamically you can build in converts so the update statement looks like this when you're done:
update t_inv_header1 set inv_para1="DIS",inv_per1=4,inv_para2="FGT",inv_para3="INS",
inv_per3=25,inv_para4="LOC",inv_per4=2,inv_para5="TOT",
inv_pamt1=convert(varchar,160),
inv_pamt2=convert(varchar,0),
inv_pamt3=convert(varchar,0),
inv_pamt4=convert(varchar,4000-160),
inv_pamt5=convert(varchar,4000+4000-160-160+0+0),
where inv_no ="PI0100001" and inv_tcode="pi1"
|
 |
|
|
malamohan
Starting Member
India
34 Posts |
Posted - 03/15/2001 : 23:19:15
|
Hi,
Thanks. But my Inv_pamt# columns are float in table. As for constructing query my inv_pamt5 may have value of (inv_pamt2+inv_pamt3) * 10/100.
Regards,
mala |
|
|
|
btrimpop
Posting Yak Master
USA
214 Posts |
Posted - 03/16/2001 : 08:48:00
|
Ok, so if I understand, you have calculations stored in varchar variables and want to update your inv_pmt# columns with the calculations in these variables, correct?
Your procedure is something like this:
declare @var varchar(100)
set @var = '(inv_pamt2+inv_pamt3) * 10/100'
and now you want to use the string in @var to update an inv_pmt# column?
Something like this should do the trick
declare @cmd varchar(4000)
set @cmd = 'update t_inv_header1 set inv_para1='+''''+'DIS'+''''+
',inv_per1=4,inv_para2='+''''+'FGT'+''''+
',inv_para3='+''''+'INS'+''''+
',inv_per3=25,inv_para4='+''''+'LOC'+''''+
',inv_per4=2,inv_para5='+''''+'TOT'+''''+
',inv_pamt5='+@var+
'where inv_no ='+''''+'PI0100001'+''''+
' and inv_tcode=+''''+'pi1'+''''
exec(@cmd)
|
|
|
| |
Topic |
|
|
|
Dynamic Sql
