April 18, 2007 at 7:31 am
I need to query 2 tables.
Data from table2 is for maximal date and less than date from table1
select t1.col1
t2.colDate,
t2.col3
from table1 t1 ,
table2 t2,
(select s.col1, max(s.colDate) as maxDate
from table2 s
where s.colDate < t1.colDate
group by s.col1,s.colDate
) a
where a.col1 = t2.col1 and
a.maxDate = t2.colDate and
t1.col1 = t2.col1
But it doesn't work.
I get error:
Server: Msg 155, Level 15, State 1, Line 5
't1.colDate' is not a recognized OPTIMIZER LOCK HINTS option.
I'll be very grateful for help.
April 18, 2007 at 7:52 am
Several things, but first, could you copy and paste the actual code, assuming the above isn't it, as I'm guessing it's just a simple syntax error of some sort? You don't have a comma after the first column, for instance.
Assuming that the above is similar to the actual code, your derived table looks a bit odd. You're getting the max of s.colDate, yet you're then grouping by that column in the same place. Finally, and this is just out of curiousity, is there a reason that you aren't using ANSI style joins?
April 18, 2007 at 8:02 am
Derived tables can be regarded as inline views so you cannot pass parameters. Something like the following should work:
SELECT T.col1, T.colDate, T.col3
FROM Table2 T
JOIN (
SELECT T2.col1, MAX(T2.colDate) AS maxDate
FROM Table1 T1
JOIN Table2 T2
ON T1.col1 = T2.col1
AND T2.colDate < T1.colDate
GROUP BY T2.col1
) D
ON T.col1 = D.col1
AND T.colDate = D.maxDate
April 18, 2007 at 9:24 am
I'm soryy for errors. I wanted to simplify my example.
Actual code is very complicated.
There are many tables in this query.
There ia a part from it:
select
m.minvc_id,
m.minvc_check_type ,
a.eqicf_tariff
from
dbo.metrology_inventory_checks m
inner join
equipment_inventory_check_fees a
on a.eqicf_check_type = m.minvc_check_type and
a.eqicf_primary_classification = m.minvc_primary_classification AND
a.eqicf_secondary_classification = m.minvc_secondary_classification
inner join
(
select eqicf_check_type,
max(eqicf_validity) as max_date,
eqicf_primary_classification,
eqicf_secondary_classification
from equipment_inventory_check_fees
where
eqicf_validity < m.minvc_date
group by
eqicf_check_type,
eqicf_primary_classification,
eqicf_secondary_classification
) t
on a.eqicf_check_type = t.eqicf_check_type AND
a.eqicf_primary_classification = t.eqicf_primary_classification AND
a.eqicf_secondary_classification = t.eqicf_secondary_classification and
a.eqicf_validity = t.max_date
April 18, 2007 at 9:49 am
Ken, thank you for your help.
Your idea is good, but there is a point that I need to get in result set
all fields from table1.
How can I get them ?
April 18, 2007 at 11:24 am
Unless there are confidentiality reasons, I'd still recommend posting your code. Based on your error, I'm guessing it will be a lot more complex trying to debug it without the code than trying to debug it by looking at complex code.
It really looks like a simple syntax error, which are often easy to find in even the most complex code.
April 19, 2007 at 1:57 am
Obviously you will have to join to Table1 outside of the derived table as well. Something like:
SELECT T1.*, T2.*
FROM Table1 T1
JOIN Table2 T2
ON T1.col1 = T2.col1
JOIN (
SELECT T4.col1, MAX(T4.colDate) AS maxDate
FROM Table1 T3
JOIN Table2 T4
ON T3.col1 = T4.col1
AND T4.colDate < T3.colDate
GROUP BY T4.col1
) D
ON T2.col1 = D.col1
AND T2.colDate = D.maxDate
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply