Home » SQL & PL/SQL » SQL & PL/SQL » query (oracle9.0)
query [message #353061] Fri, 10 October 2008 16:36 Go to next message
vchimakurthi@gmail.com
Messages: 3
Registered: October 2008
Junior Member
hi
any one can help me to do the query pls

i have 2 tables

agents and missions

agents
agent_id,activation_date,deactivation_date,location_id
missions
location_id,mission_id

for those deactivated agents that are on more than seven missions, change their deactivation date to the earliest deactivation date of all agents who were activated in the same year as the agent you are updating.(this does make sense if you think about it!).
Now create a copy of your updated AGENTS table called ABC


I WROTE

UPDATE AGENTS A
SET DEACTIVATION_DATE=
(
SELECT MIN(DEACTIVATION_DATE) FROM AGENTS B
WHERE TO_CHAR(A.ACTIVATION_DATE,'YY')=TO_CHAR(B.ACTIVATION_DATE,'YY')
)
WHERE AGENT_ID IN
(
SELECT AGENT_ID
FROM
(SELECT AGENT_ID,MISSION_ID FROM AGENTS A
INNER JOIN MISSIONS M
ON A.LOCATION_ID=M.LOCATION_ID
WHERE DEACTIVATION_DATE IS NOT NULL
ORDER BY AGENT_ID)
HAVING COUNT(MISSION_ID)>7
GROUP BY AGENT_ID
)
BUT I FELL SOME THING WRONG


Re: query [message #353079 is a reply to message #353061] Sat, 11 October 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: query [message #353082 is a reply to message #353061] Sat, 11 October 2008 01:10 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Check the Proper Use of Correlated query with WHERE EXISTS

Also check the Expected order of Group by , Where , having and Order by ( Btw why do you need Order by ? )

Thumbs Up
Rajuvan
Previous Topic: Connect By Prior and nocycles ...
Next Topic: Ceate a type with a method inside
Goto Forum:
  


Current Time: Sat Dec 10 18:57:13 CST 2016

Total time taken to generate the page: 0.07147 seconds