Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Tariq Mehmood
  Where is Tariq Mehmood?
 BAHAWALPUR
 Pakistan
 Tariq Mehmood
 To: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: UDATE instead REPLACE
Thread ID: 289065 Message ID: 289157 # Views: 34 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, December 28, 2010 7:58:04 PM         
   


> > Dear Sir,
> >
> > I have following working codes
> >
> >
> > select crsalp
> > goto top
> > do while !eof()
> > 	scatter memvar
> > 
> > 	xyz="crs"+sys(2015)
> > 	select ;
> > 		sum(iif(type="P",cr_amount,0))as cr_amount,;
> > 		sum(iif(type="D",dr_amount,0))as ddr_amount1;
> > 		where vou_no=m.vou_no;
> > 		from crsalp;
> > 		group by vou_no;
> > 		into cursor xyz readwrite
> > 
> > 	wait window alltrim(str(m.vou_no)) at srows()/2,scols()/2 nowait
> > 
> > 	select xyz
> > 	am1=cr_amount-ddr_amount1
> > 
> > 	select cr_sum
> > 	locate for vou_no=M.vou_no
> > 	if found()
> > 		replace dr_amount with am1
> > 	endif
> > 
> > 	select crsalp
> > 	if !eof()
> > 		skip
> > 	endif
> > 
> > enddo
> > 
> > messagebox("Done",0+16+256,"Successfully")
> > 
> > 

> >
> > I need UPDADTE SQL command against above codes.
> >
> > Please help
>
>
*********
> CREATE CURSOR crsums (vou_no int, crsum int)
> INSERT INTO Crsums VALUES (1, 0)
> INSERT INTO Crsums VALUES (2, -10)
> INSERT INTO Crsums VALUES (3, 50)
> 
> CREATE CURSOR Crsalp (vou_no int, type C(1), cr_amount int, dr_amount int)
> INSERT into Crsalp VALUES (1, 'P', 10, 0 )
> INSERT into Crsalp VALUES (1, 'P', 20, 0 )
> INSERT into Crsalp VALUES (1, 'D', 0, 25 )
> INSERT into Crsalp VALUES (2, 'D', 0, 10 )
> INSERT into Crsalp VALUES (2, 'D', 0, 10 )
> INSERT into Crsalp VALUES (3, 'P', 10, 0 )
> INSERT into Crsalp VALUES (3, 'D', 0, 15 )
> INSERT into Crsalp VALUES (3, 'P', 10, 0 )
> 
> UPDATE Crsums SET Crsum = crsum + (SELECT SUM(cr_amount)-SUM(dr_amount) from Crsalp ;
>  WHERE vou_no=Crsums.vou_no )
> 

> Result:

> Vou_no Crsum
> 1 5
> 2 -30
> 3 55
>

>
> I'm assuming that when there's a dr_amount then cr_amount=0, and viceversa so there's no point in checking for P and D.
>
> -Anders


Dear Anders

I have these cursor

CREATE CURSOR crsalp (vou_no n(4),type c(1),cr_amount n(4))
INSERT INTO crsalp values(1,'P',500)
INSERT INTO crsalp values(1,'D',100)
INSERT INTO crsalp values(2,'P',600)
INSERT INTO crsalp values(3,'P',800)
 
CREATE CURSOR cr_sum (vou_no n(4),dr_amount n(4))
INSERT INTO cr_sum values(1,0)
INSERT INTO cr_sum values(2,0)
INSERT INTO cr_sum values(3,0)


ENTIRE THREAD

UDATE instead REPLACE Posted by Tariq mehmood @ 12/28/2010 8:21:00 AM
RE: UDATE instead REPLACE Posted by Christian Tabligan @ 12/28/2010 8:55:55 AM
RE: UDATE instead REPLACE Posted by Tariq mehmood @ 12/28/2010 9:23:56 AM
RE: UDATE instead REPLACE Posted by Christian Tabligan @ 12/28/2010 10:08:55 AM
RE: UDATE instead REPLACE Posted by Tariq mehmood @ 12/28/2010 6:45:05 PM
RE: UDATE instead REPLACE Posted by Christian Tabligan @ 12/29/2010 2:20:32 AM
RE: UDATE instead REPLACE Posted by Tariq mehmood @ 12/29/2010 3:31:28 AM
RE: UDATE instead REPLACE Posted by Anders Altberg @ 12/28/2010 7:34:37 PM
RE: UDATE instead REPLACE Posted by Tariq mehmood @ 12/28/2010 7:58:04 PM
RE: UDATE instead REPLACE Posted by Anders Altberg @ 12/29/2010 5:32:54 PM
RE: UDATE instead REPLACE Posted by Anders Altberg @ 12/29/2010 5:59:54 PM