• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1869
  • Last Modified:

SQL Statement error - cannot call methods on char. using rank() in subquery"

Hi,

We are trying to use a rank() and partition by statement in this SQL statement below in MS SQL but it seems to be failing on the group by line.

The Original Statement is:
SELECT DISTINCT 
Keyword_1.Level_One AS Keyword, 
Keyword_1.Level_One_Desc AS Keyword_Description,
Count(Incident.Incident_Number) AS Count  
FROM ((Incident  LEFT JOIN Keyword_1 ON Incident.Keyword_1=Keyword_1.Keyword left join period ON (Incident.Opened_Date >= period.start and  Incident.Opened_Date <= period.Salesend)) )
WHERE (Incident.Opened_Date >='21 Jan 2013' AND Incident.Opened_Date<='21 Jan 2013 23:59:59')  
GROUP BY Keyword_1.Level_One,Keyword_1.Level_One_Desc  
ORDER BY Count(Incident.Incident_Number) DESC

Open in new window


Below is the New Statement with Rank() and partition subquery to list only unique Incident_numbers  where RANK = 1. The subquery is returning the correct records but I then am unable to group those records as in the original statement.

SELECT DISTINCT
tmp.Keyword_1.Level_One AS Keyword,        
tmp.Keyword_1.Level_One_Desc AS Keyword_Description, 
Count(tmp.Incident_Number) AS Count 
from (
      select incident.*, keyword_1.*, Rank() over  (partition by incident.incident_number order by incident.roll ) as RANK
FROM ((Incident  LEFT JOIN Keyword_1 ON Incident.Keyword_1=Keyword_1.Keyword left join period ON (Incident.Opened_Date >= period.start and  Incident.Opened_Date <= period.Salesend))) 
      WHERE (Incident.Opened_Date >='21 Jan 2013' AND Incident.Opened_Date<='21 Jan 2013 23:59:59')
) tmp
where RANK = 1 
GROUP BY tmp.Keyword_1.Level_One,tmp.Keyword_1.Level_One_Desc
ORDER BY Count(tmp.Incident_Number) DESC

Open in new window


Running the above satement produces the error:

Msg 258, Level 15, State 1, Line 11
Cannot call methods on char.


The error message is complaining about the GROUP BY line, if I comment out the group by line, I get the same “Cannot call methods on char” error on the first line in the select, and then on the second line. The statement will only execute if I remove the first 2 components of the select and just leave the Count(tmp.Incident_number) which is just returning a count.

Can someone suggest a way to be able to still use the rank() and partition statement in a subquery and allow a group by as per the original statement.

Thanks
0
jcwiatr
Asked:
jcwiatr
2 Solutions
 
chaauCommented:
I think you need to convert the statement as per below:

SELECT DISTINCT
tmp.Level_One AS Keyword,        
tmp.Level_One_Desc AS Keyword_Description, 
Count(tmp.Incident_Number) AS Count 
from (
      select incident.Incident_Number as Incident_Number, keyword_1.Level_One as Level_One, keyword_1.Level_One_Desc as Level_One_Desc Rank() over  (partition by incident.incident_number order by incident.roll ) as RANK
FROM ((Incident  LEFT JOIN Keyword_1 ON Incident.Keyword_1=Keyword_1.Keyword left join period ON (Incident.Opened_Date >= period.start and  Incident.Opened_Date <= period.Salesend))) 
      WHERE (Incident.Opened_Date >='21 Jan 2013' AND Incident.Opened_Date<='21 Jan 2013 23:59:59')
) tmp
where RANK = 1 
GROUP BY tmp.Level_One, tmp.Level_One_Desc
ORDER BY Count(tmp.Incident_Number) DESC

Open in new window


Explanation: when you created a subquery tmp the column names in this subquery will not be like "Keyword_1.Level_One". They will most like be just "Level_One". For the conflicting column the engine will auto-generate the column names like "Level_one_1" (if the same column exists in Keyword_1 and Incident). I do not have the whole structures of your tables you used, therefore I have just included into "tmp" those columns you have used in the outer query. I believe it is a good practice anyway. There is no reason to retrieve whole tables Keyword_1 and Incident if you are only going to use a couple of columns.

So, to summarise, the error was in this statement: "tmp.Keyword_1.Level_One"
0
 
Anthony PerkinsCommented:
Try it this way:
SELECT  tmp.Level_One AS Keyword,
        tmp.Level_One_Desc AS Keyword_Description,
        COUNT(*) AS [Count]
FROM    (SELECT k.Level_One,
                k.Level_One_Desc,
                RANK() OVER (PARTITION BY i.incident_number ORDER BY i.roll) AS [RANK]
         FROM   Incident i
                LEFT JOIN Keyword_1 k ON i.Keyword_1 = k.Keyword
                LEFT JOIN period p ON i.Opened_Date BETWEEN p.start AND p.Salesend
         WHERE  i.Opened_Date BETWEEN '21 Jan 2013' AND '21 Jan 2013 23:59:59'
        ) tmp
WHERE   tmp.[RANK] = 1
GROUP BY tmp.Level_One,
        tmp.Level_One_Desc
ORDER BY [Count] DESC

Open in new window

0
 
jcwiatrAuthor Commented:
Thanks to both, I needed to remove the Table refernce in the topmost Select statment and just use the tmp alias to refernce the fields. Acperkins statement ran sucessfully.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now