parameter to derived table

  • 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.

  • 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?

     

  • 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

  • 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

                   

     

  • 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 ?

  • 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.

  • 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