Help with SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with SQL

Author

Topic

zwheeler
Starting Member

25 Posts

Posted - 2011-12-21 : 14:12:03

Hi
I have the following SQL Statement and it works:
DELETE i
FROM stg_history_ira i
INNER JOIN (--dates properties became inactive
(SELECT pa.fk_property_id, s.max_date_event
FROM oe_mf_data_mart..mf_history_property_active pa
INNER JOIN (--last event for each property
SELECT fk_property_id, max(date_event) as max_date_event
FROM oe_mf_data_mart..mf_history_property_active
GROUP BY fk_property_id
) s ON pa.fk_property_id = s.fk_property_id AND pa.date_event = s.max_date_event
WHERE pa.ind_property_active = 'N'
) x ON i.fk_property_id = x.fk_property_id AND i.date_event > x.max_date_event
GO

However, I need to add a table and reflect the query with changes below;however, i keep getting syntax error by Inner and syntax error by s

DELETE i
FROM stg_history_ira i
INNER JOIN (--dates properties became inactive
(SELECT pa.fk_property_id, s.max_date_event
FROM oe_mf_data_mart..mf_history_property_active pa, oe_mf_data_mart..mf_property p
WHERE pa.fk_property_id = p.pk_property_id)
INNER JOIN (--last event for each property
SELECT fk_property_id, max(date_event) as max_date_event
FROM oe_mf_data_mart..mf_history_property_active, oe_mf_data_mart..mf_property
WHERE fk_property_id = pk_property_id
GROUP BY fk_property_id
) s ON pa.fk_property_id = s.fk_property_id AND pa.date_event = s.max_date_event
WHERE p.ind_property_active = 'N'
) x ON i.fk_property_id = x.fk_property_id AND i.date_event > x.max_date_event
GO

what am i doing wrong
I am using sql server 2000

thanks in advance

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 14:17:48

ummm..what are you really trying to do?


	DELETE i
	  FROM stg_history_ira i
INNER JOIN (
			(SELECT pa.fk_property_id, s.max_date_event
			   FROM oe_mf_data_mart..mf_history_property_active pa, oe_mf_data_mart..mf_property p
			  WHERE pa.fk_property_id = p.pk_property_id)  AS XXX 
 		 INNER JOIN (
						SELECT fk_property_id, max(date_event) as max_date_event
						  FROM oe_mf_data_mart..mf_history_property_active
					INNER JOIN oe_mf_data_mart..mf_property 
						    ON fk_property_id = pk_property_id
					  GROUP BY fk_property_id
						) s 
				ON pa.fk_property_id = s.fk_property_id AND pa.date_event = s.max_date_event
			  WHERE p.ind_property_active = 'N'
			) x 
		ON i.fk_property_id = x.fk_property_id AND i.date_event > x.max_date_event
GO

do you know about [ code] [ /code] tags?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 01:47:33

should be


DELETE i
	  FROM stg_history_ira i
INNER JOIN (SELECT XXX.fk_property_id, s.max_date_event
	    FROM	(SELECT pa.fk_property_id,pa.date_event ,p.ind_property_active
			   FROM oe_mf_data_mart..mf_history_property_active pa, oe_mf_data_mart..mf_property p
			  WHERE pa.fk_property_id = p.pk_property_id)  AS XXX 
 		 INNER JOIN (
						SELECT fk_property_id, max(date_event) as max_date_event
						  FROM oe_mf_data_mart..mf_history_property_active
					INNER JOIN oe_mf_data_mart..mf_property 
						    ON fk_property_id = pk_property_id
					  GROUP BY fk_property_id
						) s 
				ON XXX.fk_property_id = s.fk_property_id AND XXX.date_event = s.max_date_event
			  WHERE XXX.ind_property_active = 'N'
			) x 
		ON i.fk_property_id = x.fk_property_id AND i.date_event > x.max_date_event
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources