Home » SQL & PL/SQL » SQL & PL/SQL » Update query problem (merged)
Update query problem (merged) [message #347345] Thu, 11 September 2008 06:05 Go to next message
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 Go to previous messageGo to next message
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 #347356 is a reply to message #347345] Thu, 11 September 2008 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- Use a formatter
- Keep your lines in 80 characters
- Copy and paste the error

Regards
Michel
Re: Update query problem (merged) [message #347359 is a reply to message #347356] Thu, 11 September 2008 06:20 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Thanks michel for replying.

Error message is :- Invalid number
Re: Update query problem (merged) [message #347362 is a reply to message #347359] Thu, 11 September 2008 06:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Solution based on your information:

Don't use invalid numbers.

You gave not enough information for a more detailed answer.
Re: Update query problem (merged) [message #347370 is a reply to message #347345] Thu, 11 September 2008 07:08 Go to previous messageGo to next message
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 #347372 is a reply to message #347370] Thu, 11 September 2008 07:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What are umm01 and umm02?
When you enclose them in quotes, they are condidered as strings. It is not possible to sum strings.
If they are columns, remove the quotes
Re: Update query problem (merged) [message #347376 is a reply to message #347345] Thu, 11 September 2008 07:34 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Thank frank,

These are codes which i have to update by checking the condition.
so i have to write a update which will check the condition and update the codes.
Re: Update query problem (merged) [message #347381 is a reply to message #347376] Thu, 11 September 2008 07:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but this makes no sense to me.
You do a select (sum(string_a)) and explain that you do this because you have to update string_a.
I don't get it.
Re: Update query problem (merged) [message #347382 is a reply to message #347376] Thu, 11 September 2008 07:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 #347408 is a reply to message #347345] Thu, 11 September 2008 09:37 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Waiting for reply?
Re: Update query problem (merged) [message #347415 is a reply to message #347408] Thu, 11 September 2008 09:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Reply: Don't use invalid numbers.

No way to tell what the exact problem is with the provided information.

[Updated on: Thu, 11 September 2008 09:53]

Report message to a moderator

Re: Update query problem (merged) [message #347418 is a reply to message #347345] Thu, 11 September 2008 10:14 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous message
Frank
Messages: 7880
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

Previous Topic: Settings for ORDCOM
Next Topic: Get Main Filters
Goto Forum:
  


Current Time: Tue Dec 06 10:21:25 CST 2016

Total time taken to generate the page: 0.16925 seconds