|
View previous topic :: View next topic
|
| Author |
Message |
prasenjit_dey
New User
Joined: 30 Apr 2008
Posts: 20
Location: Noida
|
|
|
|
I have a query to update a table, the query looks like:
| Code: |
UPDATE AAA.xxx_xxxxCVGPR_AGG_T T1
SET xxx_sub_CLM_NR =
( SELECT xxx_SUB_CLM_NR
FROM AAA.xxx_CLM_COVERAGE_T T2
WHERE
T2.xxx_PRD_END_DT = T1.xxx_PRD_END_DT
AND T2.xxx_COVG_CD = T1.xxx_COVG_CD
AND T2.xxx_CLM_FIL_NR = T1.xxx_CLM_FIL_NR )
WHERE EXISTS
( SELECT 1 FROM AAA.xxx_CLM_COVERAGE_T T3
WHERE
T3.xxx_PRD_END_DT = T1.xxx_PRD_END_DT
AND T3.xxx_COVG_CD = T1.xxx_COVG_CD
AND T3.xxx_CLM_FIL_NR = T1.xxx_CLM_FIL_NR ); |
How can we run it in batch mode. Tried it running also thru SPUFI but it gives an -811 error message.
Does anyone have any idea.
Thanks. |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
| Back to top |
|
|
prasenjit_dey
New User
Joined: 30 Apr 2008
Posts: 20
Location: Noida
|
|
|
|
Craq i understand, but my query was if we cannot run it through SPUFI than what are the other alternatives!
Thanks. |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
Hi,
| Quote: |
| if we cannot run it through SPUFI than what are the other alternatives! |
What is the problem/error while running in SPUFI? As you said, if you got -811, you would get the same everywhere you run the query.
Please take a look at the query and -811 reason and change it accordingly.
BTW, you can use QMF, some third-party tools, SPUFI in batch or an application program to run a query... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| what are the other alternatives! |
Until you correct the problem, you have no alternatives. . . It is not an issue/error with spufi.
Nothing will run the query as long as the -811 is raised. This includes all of the possible ways to run sql queries (i.e. those mentioned by Gnanas).
If a "simple" sql query will not work due to the -811, you may need to imbed different sql in a program and code around the situation. |
|
| Back to top |
|
|
prasenjit_dey
New User
Joined: 30 Apr 2008
Posts: 20
Location: Noida
|
|
|
|
Cursor is an option for this problem, but writing an cursor program will be a hectic task because again we will have to raise a request and all other formalities.
Cant we add something to the query so that it selects only one row from the result set and update the table, for all the matching records. |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| Quote: |
| ... but writing an cursor program will be a hectic task because again we will have to raise a request and all other formalities. |
It shoud be done if that is your requirement. You should do all the formalities for your task. That's not hectic. 
It seems you are doing the job for your convenient, not for the requirement!. 
Anyway, will this work?
| Code: |
| FETCH FIRST ROW ONLY |
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
If your select returns more than one row and you choose to process only the "first" row returned, might there be some other problem later? Is there any chance that some data integrity/inconsistency problem could be introduced?
Suggest you do the proper bit of work now so that you (or worse, someone else) does not have to deal with some other problem later. |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
[quote="Gnanas N"]
| Quote: |
Anyway, will this work?
| Code: |
| FETCH FIRST ROW ONLY |
|
But this is a problem with the Subquery. |
|
| Back to top |
|
|
prasenjit_dey
New User
Joined: 30 Apr 2008
Posts: 20
Location: Noida
|
|
|
|
The subquery returns multiple rows.
the filter conditions:
T2.xxx_PRD_END_DT = T1.xxx_PRD_END_DT
AND T2.xxx_COVG_CD = T1.xxx_COVG_CD
AND T2.xxx_CLM_FIL_NR = T1.xxx_CLM_FIL_NR
returns rows that differs on the COVG_CD or PRD_END_DT or the CLM_FIL_NR.
But is there any method to just take a row one by one out of the result set and update the table with corresponding values. |
|
| Back to top |
|
|
prasenjit_dey
New User
Joined: 30 Apr 2008
Posts: 20
Location: Noida
|
|
|
|
| The problem is both the tables dont have any PK or any CK. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
You will need to write "real" code and control the process in the code (embed appropriate sql in a program as mentioned days ago).
The "simple" query does not appear to be able to do what is needed. |
|
| Back to top |
|
|
|
|
