CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
ferronrsmith
ferronrsmith

Reputation: 1110

Keeping getting a Msg 102, Level 15, State 1, Line 18 Incorrect syntax near 'e'.

SELECT TOP(5000) 
 a.actions, 
 b.backtracks, 
 i.items,  
 u.users,     
 ROUND(a.actions/u.users,2) AS average_actions_per_user,     
 ROUND((e.number_of_total_actions_of_items_in_itemassoc/a.actions)*100,2) as recommendation_coverage  
 FROM  (SELECT count(1) as actions FROM [action] WHERE tenantid='1') a JOIN  
       (SELECT count(1) as backtracks FROM backtracking WHERE tenantid='') b JOIN   
       (SELECT count(1) as items FROM (SELECT distinct itemid FROM [action] where tenantid = '1') a) i JOIN    
       (SELECT count(1) as users FROM (SELECT distinct userid FROM [action] where tenantid = '1') a) u JOIN  

       (SELECT SUM(c) AS number_of_total_actions_of_items_in_itemassoc FROM 
       (SELECT c FROM 
       (SELECT itemid, itemtypeid, tenantid, COUNT(1) as c 
            FROM [action] WHERE tenantid = '1' GROUP BY itemId,itemTypeId,tenantId) a 
            INNER JOIN itemassoc i ON (i.itemfromid = a.itemid AND a.itemtypeid = i.itemFromTypeId AND a.tenantid = i.tenantid)
                 GROUP BY a.itemid, a.itemtypeid, a.tenantid) a) e JOIN

Can someone help me? I see the error, but I don't know why it's happening :(

Upvotes: 0

Views: 1410

Answers (1)

Andrei
Andrei

Reputation: 56688

Seems like the "on condition" is missing for all the JOINs. This modified query has no problems with syntax (not sure it does what it is needed though):

SELECT TOP(5000) 
 a.actions, 
 b.backtracks, 
 i.items,  
 u.users,     
 ROUND(a.actions/u.users,2) AS average_actions_per_user,     
 ROUND((e.number_of_total_actions_of_items_in_itemassoc/a.actions)*100,2) as recommendation_coverage  
 FROM  (SELECT count(1) as actions FROM [action] WHERE tenantid='1') a JOIN  
       (SELECT count(1) as backtracks FROM backtracking WHERE tenantid='') b on 1 = 1 JOIN   
       (SELECT count(1) as items FROM (SELECT distinct itemid FROM [action] where tenantid = '1') a) i on 1 = 1 JOIN    
       (SELECT count(1) as users FROM (SELECT distinct userid FROM [action] where tenantid = '1') a) u on 1 = 1 JOIN  

       (SELECT SUM(c) AS number_of_total_actions_of_items_in_itemassoc FROM 
       (SELECT c FROM 
       (SELECT itemid, itemtypeid, tenantid, COUNT(1) as c 
            FROM [action] WHERE tenantid = '1' GROUP BY itemId,itemTypeId,tenantId) a 
            INNER JOIN itemassoc i ON (i.itemfromid = a.itemid AND a.itemtypeid = i.itemFromTypeId AND a.tenantid = i.tenantid)
                 GROUP BY a.itemid, a.itemtypeid, a.tenantid) a) e on 1 = 1

if you remove all the on 1 = 1 with your actual conditions for JOINs I think you will get what you need.

Upvotes: 1

Related Questions