Comments and changes to this ticket

• You flagged this item as spam.
  

  ReggieB December 22nd, 2008 @ 02:29 PM

  Sorry - didn't get the format syntax right.

  My problem is that:

  
  ORDER BY c.comp_name
  

  becomes

  
  ORDER BY c\.comp_name
  

  My code is

  
        @opportunities = WillPaginate::Collection.create(page, per_page) do |pager|
          result = Opportunity.find(:all, 
                 :select => "
  [o].*,
   [c].[Comp_Name] AS company_name,
   [p].[Pers_Salutation] AS person_salutation,
   [p].[Pers_FirstName] AS person_first_name,
   [p].[Pers_LastName] AS person_last_name,
   [u].[User_FirstName] AS user_first_name,
   [u].[User_LastName] AS user_last_name
  ",
                 :conditions => conditions.join(" AND "),
                 :joins => "
  AS [o]
  LEFT JOIN [Company] AS [c]
    ON [o].[Oppo_PrimaryCompanyId] = [c].[Comp_CompanyId]
  LEFT JOIN [Person] AS [p]
    ON [o].[Oppo_PrimaryPersonId] = [p].[Pers_PersonId]
  LEFT JOIN [Users] AS [u]
    ON [o].[Oppo_AssignedUserId] = [u].[User_UserId]
  ",
                 :order => 'c.comp_name',
                 :limit => pager.per_page,
                 :offset => pager.offset)
          # inject the result array into the paginated collection:
          pager.replace(result)
  
          unless pager.total_entries
            # the pager didn't manage to guess the total count, do it manually
            pager.total_entries = Opportunity.count(:all, :conditions => conditions.join(" AND "))
          end
        end
  

  And the error message is:

  
  DBI::DatabaseError: Execute
      OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
        Incorrect syntax near '\.'.
      HRESULT error code:0x80020009
        Exception occurred.: SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 20  
  [o].*,
   [c].[Comp_Name] AS company_name,
   [p].[Pers_Salutation] AS person_salutation,
   [p].[Pers_FirstName] AS person_first_name,
   [p].[Pers_LastName] AS person_last_name,
   [u].[User_FirstName] AS user_first_name,
   [u].[User_LastName] AS user_last_name
   FROM Opportunity  
  AS [o]
  LEFT JOIN [Company] AS [c]
    ON [o].[Oppo_PrimaryCompanyId] = [c].[Comp_CompanyId]
  LEFT JOIN [Person] AS [p]
    ON [o].[Oppo_PrimaryPersonId] = [p].[Pers_PersonId]
  LEFT JOIN [Users] AS [u]
    ON [o].[Oppo_AssignedUserId] = [u].[User_UserId]
   WHERE (Oppo_CreatedDate < '2008-12-01 00:00:00' AND Oppo_CreatedDate > '2008-11-01 00:00:00')  ORDER BY c.comp_name) AS tmp1 ORDER BY c\.\[comp_name\] DESC) AS tmp2 ORDER BY c\.\[comp_name\]
  

• You flagged this item as spam.
  

  ReggieB December 22nd, 2008 @ 02:33 PM

  I've also tried using paginate_by_sql:

  
        sql = <<EOF
  SELECT [o].*,
    [c].[Comp_Name] AS company_name,
    [p].[Pers_Salutation] AS person_salutation,
    [p].[Pers_FirstName] AS person_first_name,
    [p].[Pers_LastName] AS person_last_name,
    [u].[User_FirstName] AS user_first_name,
    [u].[User_LastName] AS user_last_name
  FROM [Opportunity] AS [o]
    LEFT JOIN [Company] AS [c]
      ON [o].[Oppo_PrimaryCompanyId] = [c].[Comp_CompanyId]
    LEFT JOIN [Person] AS [p]
      ON [o].[Oppo_PrimaryPersonId] = [p].[Pers_PersonId]
    LEFT JOIN [Users] AS [u]
      ON [o].[Oppo_AssignedUserId] = [u].[User_UserId]
  WHERE #{conditions.join(" AND ")}
  ORDER BY [c].[comp_name]
  EOF
  
        @opportunities = Opportunity.paginate_by_sql(sql, :page => params[:page], :per_page => 3)
  

  But this results in another error:

  
  DBI::DatabaseError: Execute
      OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
        The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
      HRESULT error code:0x80020009
        Exception occurred.: SELECT COUNT(*) FROM (SELECT [o].*,
    [c].[Comp_Name] AS company_name,
    [p].[Pers_Salutation] AS person_salutation,
    [p].[Pers_FirstName] AS person_first_name,
    [p].[Pers_LastName] AS person_last_name,
    [u].[User_FirstName] AS user_first_name,
    [u].[User_LastName] AS user_last_name
  FROM [Opportunity] AS [o]
    LEFT JOIN [Company] AS [c]
      ON [o].[Oppo_PrimaryCompanyId] = [c].[Comp_CompanyId]
    LEFT JOIN [Person] AS [p]
      ON [o].[Oppo_PrimaryPersonId] = [p].[Pers_PersonId]
    LEFT JOIN [Users] AS [u]
      ON [o].[Oppo_AssignedUserId] = [u].[User_UserId]
  WHERE Oppo_CreatedDate < '2008-12-01 00:00:00' AND Oppo_CreatedDate > '2008-11-01 00:00:00'
  ORDER BY [c].[comp_name]
  ) AS count_table
  

• You flagged this item as spam.
  

  ReggieB December 24th, 2008 @ 11:46 AM

  I've investigated this further and it is not a will_paginate bug. It's an active record bug. The same thing happens with a find :

  
  class Opportunity
    def self.find_test
      find(:all,
                 :select => "
  [o].*,
   [c].[Comp_Name] AS company_name,
   [p].[Pers_Salutation] AS person_salutation,
   [p].[Pers_FirstName] AS person_first_name,
   [p].[Pers_LastName] AS person_last_name,
   [u].[User_FirstName] AS user_first_name,
   [u].[User_LastName] AS user_last_name
  ",
                 :joins => "
  AS [o]
  LEFT JOIN [Company] AS [c]
    ON [o].[Oppo_PrimaryCompanyId] = [c].[Comp_CompanyId]
  LEFT JOIN [Person] AS [p]
    ON [o].[Oppo_PrimaryPersonId] = [p].[Pers_PersonId]
  LEFT JOIN [Users] AS [u]
    ON [o].[Oppo_AssignedUserId] = [u].[User_UserId]
  ",
                 :order => 'c.comp_name',
                 :limit => 30,
                 :offset => 30)
    end
  end
  
  Opportunity.find_test
  

  Gives the same error. Can you close this ticket please, and I'll see if I can get the problem sorted elsewhere.

• You flagged this item as spam.
  

  ReggieB December 24th, 2008 @ 12:23 PM

  Working on the problem further, its the square brackets causing the problem. They are commonly used in MS SQL code, but ActiveRecord seems to behave differently if it finds them in and offset or limit option. Take out the square brackets and I started getting somewhere. This version of the above test works:

  
  class Opportunity << Base
    def self.find_test
      find(:all,
                 :select => "
  o.*,
   c.Comp_Name AS company_name,
   p.Pers_Salutation AS person_salutation,
   p.Pers_FirstName AS person_first_name,
   p.Pers_LastName AS person_last_name,
   u.User_FirstName AS user_first_name,
   u.User_LastName AS user_last_name
  ",
                 :joins => "
  AS o
  LEFT JOIN Company AS c
    ON o.Oppo_PrimaryCompanyId = c.Comp_CompanyId
  LEFT JOIN Person AS p
    ON o.Oppo_PrimaryPersonId = p.Pers_PersonId
  LEFT JOIN Users AS u
    ON o.Oppo_AssignedUserId = u.User_UserId
  ",
                 :order => 'company_name',
                 :limit => 30,
                 :offset => 30)
    end 
  end
  

  Note: I also has to tweak the :order statement as the limit and offset are used in a SELECT statement within another SELECT statement, and the preceding table name gets stripped off. Therefore you need to use a column name that is consistent in each of the SELECT statements.

  So definitely close this ticket now please.

• 

  Mislav January 8th, 2009 @ 03:33 PM

  • State changed from “new” to “invalid”

  Glad you solved your problem :) Closing