December 21, 2011 at 12:13 pm
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
December 21, 2011 at 1:01 pm
Your first Inner Join goes to a derived table with no Select statment:
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)
If that select is meant to be a derived table within the query, it needs to have a table alias (like the "s" and "x" on your other query), and there needs to be a Select statement above it. If it's not a sub-table (derived table), then the "(" before it is redundant.
That's where your syntax error is coming from, but I can't recommend a precise fix for it based on what you've posted thus far. Does that help at least a little?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply