Click here to Skip to main content
15,742,518 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
Select
 DATA0006_1.RKEY as D6_Rkey,
 sum((DATA0447_1.TVALUE + DATA0056_1.ELAPSED_TIME) * DATA0034_1.COST_DRIVER) as totHcost

From
DATA0059 DATA0059_1 WITH (NOLOCK)
INNER JOIN 
DATA0056 DATA0056 WITH (NOLOCK)
ON DATA0059_1.TPUT_PTR = DATA0056_1.RKEY
INNER JOIN
DATA0006 DATA0006_1 WITH(NOLOCK)
ON DATA0006_1.RKEY = DATA0056_1.WOPTR
INNER JOIN
 DATA0447 DATA0447_1 WITH (NOLOCK)
 INNER JOIN
 DATA0445 DATA0445_1 WITH (NOLOCK)
 ON DATA0447_1.SOURCE_PTR = DATA0445_1.RKEY
 INNER JOIN
 DATA0006 DATA0006_1 WITH (NOLOCK)
 ON DATA0006_1.RKEY = DATA0445_1.WOPTR
 LEFT OUTER JOIN
 DATA0017 DATA0017_1 WITH (NOLOCK)
 ON DATA0017_1.RKEY = DATA0006_1.INVENTORY_PTR
 INNER JOIN
 DATA0034 DATA0034_1 WITH (NOLOCK)
 ON DATA0034_1.RKEY = DATA0445_1.WCPTR
 LEFT OUTER JOIN
 DATA0050 DATA0050_1 WITH (NOLOCK)
 ON DATA0050_1.RKEY = DATA0006_1.CUST_PART_PTR (LINE 46)
 INNER JOIN
 DATA0005 DATA0005_1 WITH (NOLOCK)
 ON DATA0005_1.RKEY = DATA0445_1.EMPL_PTR
 INNER JOIN
 DATA0135 DATA0135_1 WITH (NOLOCK)
 ON DATA0135_1.RKEY = DATA0445_1.PARAM_PTR 
Where
 (DATA0445_1.TDATE > '11/11/2010') AND (DATA0056_1.TDATE > '11/11/2010')
 group by DATA0006_1.RKEY
Order By
 DATA0006_1.RKEY


What I have tried:

Cannot figure out why im getting this error. Everything seems to be fine.

It says error on line 46 i have marked it above
Posted
Updated 22-Sep-16 6:04am
v2

Aren't you missing a ON clause:
SQL
INNER JOIN
 DATA0447 DATA0447_1 WITH (NOLOCK)
---Should be an ON here?
 INNER JOIN
 DATA0445 DATA0445_1 WITH (NOLOCK)
 ON DATA0447_1.SOURCE_PTR = DATA0445_1.RKEY
 
Share this answer
 
v2
Comments
Saaharjit Singh 22-Sep-16 12:29pm    
Thanks
OriginalGriff 22-Sep-16 13:58pm    
You're welcome!
Your table names give me anxiety, i pity whoever has to maintain that schema. I tried to create your tables as var tables to make this replicatable but i got frustrated.

In short, you need to invest in some sort of code formatting plugin or force yourself to better format your code.

SQL
Select
 DATA0006_1.RKEY as D6_Rkey,
 sum((DATA0447_1.TVALUE + DATA0056_1.ELAPSED_TIME) * DATA0034_1.COST_DRIVER) as totHcost
 
FROM DATA0059 DATA0059_1 WITH (NOLOCK)
INNER JOIN DATA0056 DATA0056 WITH (NOLOCK) ON DATA0059_1.TPUT_PTR = DATA0056_1.RKEY
INNER JOIN DATA0006 DATA0006_1 WITH(NOLOCK) ON DATA0006_1.RKEY = DATA0056_1.WOPTR
INNER JOIN  DATA0447 DATA0447_1 WITH (NOLOCK) 
INNER JOIN DATA0445 DATA0445_1 WITH (NOLOCK) ON DATA0447_1.SOURCE_PTR = DATA0445_1.RKEY
INNER JOIN DATA0006 DATA0006_1 WITH (NOLOCK) ON DATA0006_1.RKEY = DATA0445_1.WOPTR
LEFT OUTER JOIN DATA0017 DATA0017_1 WITH (NOLOCK) ON DATA0017_1.RKEY = DATA0006_1.INVENTORY_PTR
INNER JOIN DATA0034 DATA0034_1 WITH (NOLOCK) ON DATA0034_1.RKEY = DATA0445_1.WCPTR
LEFT OUTER JOIN DATA0050 DATA0050_1 WITH (NOLOCK) ON DATA0050_1.RKEY = DATA0006_1.CUST_PART_PTR
INNER JOIN DATA0005 DATA0005_1 WITH (NOLOCK) ON DATA0005_1.RKEY = DATA0445_1.EMPL_PTR
INNER JOIN DATA0135 DATA0135_1 WITH (NOLOCK)  ON DATA0135_1.RKEY = DATA0445_1.PARAM_PTR 
Where
 (DATA0445_1.TDATE > '11/11/2010') AND (DATA0056_1.TDATE > '11/11/2010')
 group by DATA0006_1.RKEY
Order By
 DATA0006_1.RKEY



Without being able to execute this query, the big issue i see is that you don't have the ON clause for one of the joins

SQL
INNER JOIN DATA0006 DATA0006_1 WITH(NOLOCK) ON DATA0006_1.RKEY = DATA0056_1.WOPTR
INNER JOIN  DATA0447 DATA0447_1 WITH (NOLOCK) 
INNER JOIN DATA0445 DATA0445_1 WITH (NOLOCK) ON DATA0447_1.SOURCE_PTR = DATA0445_1.RKEY


See DATA0447 WITH NO LOCK has no ON < table.column > = < table.column > clause?

That is more than likely your issue.
 
Share this answer
 
v2
Comments
Saaharjit Singh 22-Sep-16 12:29pm    
Thanks and thanks for the advice. Just took over this project. Can't really make any changes to the schema

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900