[quoted text, click to view]

I don't follow your requirement. Is this the actual result you want? When
the id_no is A, and the period is D, change the amount to 0, and add the
formula 10+20=30 to the result in the amount column for the row where id_no
is A, and period is N?

Isn't this something the presentation layer can do much easier?

--
http://www.aspfaq.com/
(Reverse address to reply.)

Not sure if I followed your requirement well, but how about:

SELECT t1.id_no, t1.period, t1.pr_no,
CASE WHEN COUNT( * ) > 1 AND t1.period = 'D'
THEN 0
ELSE SUM( t2.amount )
END AS "amount"
FROM #tbl t1
INNER JOIN #tbl t2
ON t2.id_no = t1.id_no
GROUP BY t1.id_no, t1.period, t1.pr_no ;

--
Anith

create table #tbl (
id_no char(1),
period char(1),
pr_no char(5),
amount float
)
insert into #tbl values ( 'A','D','001',10)
insert into #tbl values ( 'A','N','002',20)
insert into #tbl values ( 'B','D','003',30)
insert into #tbl values ( 'C','N','004',40)

Using select amount, I want to reformat the table into this,
such that for the same id_No, the D-amount is summed into N-amount.

id_no period pr_no amount
A D 001 0
A N 002 10+20=30
B D 003 30
C N 004 40


--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.26

that's my original plan. But I could also use SQL in presentation layer
(foxpro).So the question remains. :)

[quoted text, click to view]

--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.26