?
Solved

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

Posted on 2013-01-29
3
Medium Priority
?
1,744 Views
Last Modified: 2013-01-29
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
Comment
Question by:jcwiatr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 1000 total points
ID: 38833529
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 38833535
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
 
LVL 1

Author Closing Comment

by:jcwiatr
ID: 38833634
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question