Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    3

    Unanswered: SQL Server: Bug 274729

    Hi,

    I've run into this problem:

    http://support.microsoft.com/default...ticle%3D274729

    Now, I've got a pretty huge query that needs to use a correlated subquery when the outer query has a GROUP BY clause. Here's an extract (I've missed out 'simple' fields which come straight out of a table):

    SELECT DISTINCT
    c.contact_id,
    c.rn_create_date,

    [snip]

    MAX(o.order_date) as lastOrderDate,
    (SELECT order_channel_id FROM [ReportsED].[dbo].order_ ord WHERE ord.bill_to_contact_id = c.contact_id AND ord.order_date = o.order_date) as lastOrderChannelId
    FROM [ReportsED].[dbo].Contact c
    [snip]
    LEFT OUTER JOIN [ReportsED].[dbo].Order_ o ON o.bill_to_contact_id = c.contact_id
    WHERE
    CHARINDEX(left(c.Zip, 1), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') > 0
    GROUP BY
    c.contact_id,
    c.rn_create_date
    [snip rest of fields corresponding to those that aren't aggregates]

    When running this I get "The query processor encountered an unexpected error during execution", as explained by the bug.

    Now, the Microsoft article recommends using DISTINCT to eliminate the GROUP BY clause. How do I do that in this more complex scenario where I not only already have a DISTINCT in the SELECT, but also have multiple GROUP BY fields?

    Many thanks,
    Dan Fairs

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to get that select statement out of your field list. Here is one way, using nested queries. Try running each level of the query separately to see how the inner queries return the channel_id for the most recent order date. You might be able to eliminate the outermost layer by using an aggregate query instead.

    SELECT
    Contact.contact_id,
    Contact.rn_create_date,
    channel_ids.lastOrderDate,
    channel_ids.order_channel_id
    from Contact
    left outer join
    (select order_.bill_to_contact
    lastOrderDates.lastOrderDate,
    order_.order_channel_id
    from order_
    inner join
    (select bill_to_contact_id. Max(order_.order_date) as order_date from order_) lastOrderDates
    on order_.bill_to_contact_id = lastOrderDates.bill_to_contact_id
    and order_.order_date = lastOrderDates.order_date) channel_ids
    on Contact.contact_id = channel_ids.bill_to_contact

    What kind of table name is "order_"?

    blindman

  3. #3
    Join Date
    May 2002
    Posts
    3
    Hi,

    Originally posted by blindman
    You need to get that select statement out of your field list.

    [snip]

    What kind of table name is "order_"?

    blindman
    Thanks for the tip - I wasn't in yesterday, but I'll try your suggestion today!

    As for the table name - it's nothing to do with me, I'm new here...

    Cheers,
    Dan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •