SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Dynamic Sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/15/2001 :  13:09:33  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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  Show Profile  Reply with Quote
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"






Go to Top of Page

malamohan
Starting Member

India
34 Posts

Posted - 03/15/2001 :  23:19:15  Show Profile  Reply with Quote
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
Go to Top of Page

btrimpop
Posting Yak Master

USA
214 Posts

Posted - 03/16/2001 :  08:48:00  Show Profile  Reply with Quote
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)








Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000