| Update query problem (merged) [message #347345] |
Thu, 11 September 2008 06:05  |
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
|
Senior Member |
|
|
Hi,
i have to compare the amount of one table with that twice of other and those who are not equal or greater than that . we have to mark them. i came up with query as below.
select sum(case when (2*a.N_PRE_MIT_AMT) >=b.N_MIT_AMT then 0 else 1 end)
from fct_sub_exposures a,fct_non_sec_exposures ,exp_mitigant_mapping c
where a.f_eligibility_flag='C'
and b.v_facility_id=c.v_linkage_id
and c.n_mitigant_skey=a.n_mitigant_skey
group by b.v_facility_id,a.n_mitigant_skey
these tables contain million or records.
I have to update who satisfy the above condition.
In place of "0" in query i have to put some code(like mm1).
but than sum fucntion doesnt allow me to add those.
or there is some other way to update the table with this option.
need help
thanks in advance.
[Updated on: Thu, 11 September 2008 06:13] by Moderator Report message to a moderator |
|
|
|
| Update query problem [message #347349 is a reply to message #347345] |
Thu, 11 September 2008 06:07  |
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
|
Senior Member |
|
|
Hi,
i have to compare the amount of one table with that twice of other and those who are not equal or greater than that . we have to mark them. i came up with query as below.
select sum(case when (2*a.N_PRE_MIT_AMT) >=b.N_MIT_AMT then 0 else 1 end)
from fct_abc_exp a,fct_sec b,fct_mapping c
where a.f_eligibility_flag='C'
and b.v_facility_id=c.v_linkage_id
and c.n_mitigant_skey=a.n_mitigant_skey
group by b.v_facility_id,a.n_mitigant_skey
these tables contain million or records.
I have to update who satisfy the above condition.
In place of "0" in query i have to put some code(like mm1).
but than sum fucntion doesnt allow me to add those.
or there is some other way to update the table with this option.
need help
thanks in advance.
[Updated on: Thu, 11 September 2008 06:12] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Update query problem (merged) [message #347370 is a reply to message #347345] |
Thu, 11 September 2008 07:08 |
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
|
Senior Member |
|
|
Copy and pasting the console error.
SQL> select sum(case when (2*a.N_PRE_MITIGATION_EAD_AMT_RCY)
>=b.N_EAD_POST_NET_POST_CRM
then 'umm01' else 'umm02' end)
2 from fct_sub_exposures a,fct_non_sec_exposures b,exp_mitigant_mapping c where
3 a.f_eligibility_flag='C'
4 and b.v_facility_id=c.v_linkage_id
5 and c.n_mitigant_skey=a.n_mitigant_skey
6 group by b.v_facility_id,a.n_mitigant_skey
7 /
select sum(case when (2*a.N_PRE_MITIGATION_EAD_AMT_RCY) >=b.N_EAD_POST_NET_POST_CRM
then 'umm01' else 'umm02' end)
from fct_sub_exposures a,fct_non_sec_exposures b,exp_mitigant_mapping c where
a.f_eligibility_flag='C'
and b.v_facility_id=c.v_linkage_id
and c.n_mitigant_skey=a.n_mitigant_skey
group by b.v_facility_id,a.n_mitigant_skey
ORA-01722: invalid number
SQL> |
|
|
|
|
|
|
|
|
|
| Re: Update query problem (merged) [message #347382 is a reply to message #347376] |
Thu, 11 September 2008 07:46 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
So what are those "codes"?
Strings or column names?
Will we have to pull each tiny bit of information out of your ears one after the other? Then this will most likely take weeks to resolve.
|
|
|
|
| Re: Update query problem (merged) [message #347395 is a reply to message #347345] |
Thu, 11 September 2008 08:27 |
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
|
Senior Member |
|
|
Sorry for confusion.
Below is full query.Can any one suggest this method is good
or i have to go for some other method
UPDATE (select sum(case when (2*a.N_PRE_MITIGATION_EAD_AMT_RCY)
>=b.N_EAD_POST_NET_POST_CRM
then 0 else 1 end) as code
from fct_sub_exposures a,fct_non_sec_exposures b,exp_mitigant_mapping c where
a.f_eligibility_flag='C'
and b.v_facility_id=c.v_linkage_id
and c.n_mitigant_skey=a.n_mitigant_skey
group by b.v_facility_id,a.n_mitigant_skey)
SET b.v_code_id = CASE WHEN code = 0 THEN 'UMM01' ELSE 'UMM02' END |
|
|
|
|
|
|
|
| Re: Update query problem (merged) [message #347418 is a reply to message #347345] |
Thu, 11 September 2008 10:14 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Unfortunately, even placing code inside code tags does not make it readable when it is not logically formatted.
Please, consider using SQL Formatter.
UPDATE (SELECT SUM(CASE
WHEN (2 * a.n_pre_Mitigation_eAd_Amt_rcy) >= b.n_eAd_Post_Net_Post_crm THEN 0
ELSE 1
END) AS Code
FROM fct_Sub_Exposures a,
fct_Non_sec_Exposures b,
exp_mItigAnt_Mapping c
WHERE a.f_Eligibility_Flag = 'C'
AND b.v_Facility_Id = c.v_Linkage_Id
AND c.n_mItigAnt_sKey = a.n_mItigAnt_sKey
GROUP BY b.v_Facility_Id,
a.n_mItigAnt_sKey)
SET b.v_Code_Id = CASE
WHEN Code = 0 THEN 'UMM01'
ELSE 'UMM02'
END
Now, it is obvious that the code is full of errors.
Firstly, you reference B.V_CODE_ID in the SET clause out of its scope, as you did not selected it in the subquery.
Secondly, as you do not group by B.V_CODE_ID, it brings the logical problem how to identify the rows which shall be updated with calculated values.
Thirdly, it is not possible to update subquery with GROUP BY clause (see previous problem; you update table rows, not the result set), so the only way is to re-write it to correlated update.
[Edit: corrected second and third problem description]
[Updated on: Thu, 11 September 2008 10:30] Report message to a moderator |
|
|
|
| Re: Update query problem (merged) [message #347427 is a reply to message #347408] |
Thu, 11 September 2008 11:11 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Let's get back one step:
First read flyboy's reply.
Then sit back, relax, deep breath and start to tell us what it is you want to do.
Do NOT repeat your query (no sense in that anyway, since you change it structurally everytime anyway), but tell us something like
I have a table xxx. In this table I want to update a column YYY and set its value to either the string UMM01 or the string UMM02.
I want to update each and every row in the table.
The condition for choosing UMM01 or UMM02 are as follows:
If today is Tuesday, I want UMM01, else UMM02
Preferrably, you will also provide a create table statement for your table, along with some insert-statements and a desired result based on those.
Now you go..
[Updated on: Thu, 11 September 2008 11:12] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
