| View previous topic :: View next topic |
| Author |
Message |
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
 Posted: Sun Sep 18, 2005 9:02 pm Post subject: Update col. in table A with 4 cols summed in table B |
 |
|
I need to update a column(dec. 9(9)v99) in table A with 4 columns(dec. 9(9)v99) from table B where SSN and Date are a match. I have tried running this in SPUFI without any luck. I changed the query to a Select query and the calculated result is correct. Also Table B can have more than 1 row that matches.
Table A looks like this
SSN int.
TOTAL1 Dec. S9(9)v99
Table B looks like this
SSN int
Date YYYY-MM-DD
Amount1 Dec. S9(9)v99
Amount2 Dec. S9(9)V99
Amount3 Dec. S9(9)v99
Amount4 Dec. S9(9)V99
This should not be very hard but I'm failing miserably(sp).
Thanks
NASCAR9
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Sun Sep 18, 2005 10:13 pm Post subject: |
|
|
Nascar9,
Try this untested Sql.
| Code: |
UPDATE TABLEA X
SET TOTAL1 = (SELECT AMOUNT1+AMOUNT2+AMOUNT3+AMOUNT4
FROM TABLEB Y
WHERE X.SSN = Y.SSN
AND X.DATE = Y.DATE)
;
|
keep in mind that you would get sqlcode of -407 if TOTAL1 is defined with NOT NULL
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Mon Sep 19, 2005 9:33 am Post subject: |
|
|
Kolusu, Thanks for the response. Here is my exact query, notice there is a 'GROUP BY'. I'm getting a -815 Can I create a derivied(sp) column in a update? The only reason I'm using SSNO is for the 'Group By'.
| Code: |
UPDATE HOURS.MEMNHR A
SET (A.SSNO
,A.MAR2004) =
(SELECT B.SSNO, SUM(B.FIRST99
+ B.OVER99
+ B.TVHRS
+ B.FAMLVE_HRS) AS TOT
FROM HOURS.HRSDTL B
WHERE B.PSTPERIOD = '2004-03-01'
AND B.SSNO = A.SSNO
GROUP BY B.SSNO);
|
NASCAR9
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Sep 19, 2005 10:18 am Post subject: |
|
|
| Quote: |
Kolusu, Thanks for the response. Here is my exact query, notice there is a 'GROUP BY'. I'm getting a -815 Can I create a derivied(sp) column in a update? The only reason I'm using SSNO is for the 'Group By'.
|
NASCAR9,
Why do you want to use a coulmn function SUM? A column function returns just one single row. . You are using a GROUP BY clause in a Sub select of an update statement which is NOT valid.
From your question I understood that tables looked like this.
tableA:
| Code: |
----+---------+---------+---------
SSN SSN_DATE TOTAL1
----+---------+---------+---------
11111 2005-01-19 .00
22222 2005-02-19 .00
33333 2005-03-19 .00
44444 2005-04-19 .00
55555 2005-05-19 .00
|
Table:b
| Code: |
---+---------+---------+---------+---------+---------+---------+---------+----
SSN SSN_DATE AMOUNT1 AMOUNT2 AMOUNT3 AMOUNT4
---+---------+---------+---------+---------+---------+---------+---------+----
11111 2005-01-19 10.00 20.00 30.00 40.00
22222 2005-02-19 20.00 30.00 40.00 50.00
33333 2005-03-19 30.00 40.00 50.00 60.00
44444 2005-04-19 40.00 50.00 60.00 70.00
|
and you want the following output
| Code: |
---+---------+---------+---------+
SSN SSN_DATE TOTAL1
---+---------+---------+---------+
11111 2005-01-19 100.00
22222 2005-02-19 140.00
33333 2005-03-19 180.00
44444 2005-04-19 220.00
55555 2005-05-19 -------------
|
i.e The records matching in tableb with tablea will get updated with the total of the 4 amounts.
If it is indeed your requirement , the sql posted by me will give you the desired results.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Mon Sep 19, 2005 10:25 am Post subject: |
|
|
Kolusu,
Table A is the correct structure.
Table be looks like this:
| Code: |
SSN SSN_DATE AMOUNT1 AMOUNT2 AMOUNT3 MOUNT4
---+---------+---------+---------+---------+---------+---------+---------+----
11111 2005-01-19 10.00 20.00 30.00 40.00
11111 2005-01-19 10.00 20.00 30.00 40.00
11111 2005-01-19 10.00 20.00 30.00 40.00
22222 2005-02-19 20.00 30.00 40.00 50.00
33333 2005-03-19 30.00 40.00 50.00 60.00
33333 2005-03-19 30.00 40.00 50.00 60.00
44444 2005-04-19 40.00 50.00 60.00 70.00
|
The output is correct.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Sep 19, 2005 10:54 am Post subject: |
|
|
NASCAR9,
Run the sql provided by me and you will have the desired results.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
SureshKumar
Intermediate
Joined: 23 Jan 2003
Posts: 211
Topics: 21
|
| Posted: Mon Sep 19, 2005 11:40 am Post subject: |
|
|
NASCAR9,
Get around -407 with COALESCE or any of its equivalent.
| Quote: |
UPDATE TABLEA X
SET TOTAL1 = (SELECT COALESCE((AMOUNT1+AMOUNT2+AMOUNT3+AMOUNT4),0)
FROM TABLEB Y
WHERE X.SSN = Y.SSN
AND X.DATE = Y.DATE)
;
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Sep 19, 2005 12:19 pm Post subject: |
|
|
| Quote: |
NASCAR9,
Get around -407 with COALESCE or any of its equivalent.
|
SureshKumar,
I don't think COALESCE would work in this condition as the NULL would be on the outer query.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
SureshKumar
Intermediate
Joined: 23 Jan 2003
Posts: 211
Topics: 21
|
| Posted: Mon Sep 19, 2005 2:25 pm Post subject: |
|
|
kolusu,
Good catch, did not realize it. tried to circumvent using CASE statement, but could not get it to work. I will try that route when I get time. Thanks |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Mon Sep 19, 2005 3:04 pm Post subject: |
|
|
I tried the query above. Remember that table B may or may not have more than 1 row that fits the selection criteria. This is why I used a Group By cluase.
| Code: |
---------+---------+---------+---------+---------+---------+---------+--------
UPDATE HOURS.MEMNHR A 000100
SET A.MAR2004 = (SELECT B.FIRST99 + B.OVER99 + B.TVHRS + 000200
B.FAMLVE_HRS 000210
FROM HOURS.HRSDTL B 000220
WHERE B.PSTPERIOD = '2004-03-01' 000230
AND B.SSNO = A.SSNO); 000240
---------+---------+---------+---------+---------+---------+---------+--------
DSNT408I SQLCODE = -811, ERROR: THE RESULT OF AN EMBEDDED SELECT STATEMENT OR
A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF
MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE
IS MORE THAN ONE VALUE
|
NASCAR9
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Sep 20, 2005 7:01 am Post subject: |
|
|
Nascar9,
If you are doing this via a pgm then why not simply declare a cursor with WITHHOLD option along with UPDATE for on total1 column? I thought you are doing it via spufi.
EX:
| Code: |
EXEC SQL
DECLARE UPD_CSR CURSOR WITH HOLD FOR
SELECT A.MAR2004
,B.FIRST99 + B.OVER99 + B.TVHRS + b.FAMLVE_HRS
FROM HOURS.MEMNHR A
,HOURS.HRSDTL B
WHERE B.PSTPERIOD = '2004-03-01'
AND B.SSNO = A.SSNO
FOR UPDATE OF A.MAR2004
END-EXEC.
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Tue Sep 20, 2005 9:41 am Post subject: |
|
|
Kolusu, I was under the gun to get this done ASAP, hence SPUFI. I ended up creating a temp table inserting the values needed. Next I used the temp table and did a straight update. All queries where done in SPUFI.
You made the point about not being able to use a 'Group By' in a Sub Select, Which was my suspicion.
Thanks for all the help
NASCAR9
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
