Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Format 2

Status
Not open for further replies.
rwn

rwn

Technical User
Dec 14, 2002
420
US
This SQL when trying to execute shows the ERROR message: (Invalid Column Name Job_Operation.Est_Run_Hrs). I have viewed the statement and unable to see the problem with the code.

SELECT Job_Operation.Job, Job_Operation.Work_Center, Job_Operation.Est_Run_Hrs, Job_Operation.Act_Run_Hrs, [Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs] AS [HRS VAR], Job.Status, Job_Operation_Time.Employee, Job_Operation_Time.Work_Date, ([Job_Operation.Est_Run_Hrs]/([Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs]))*100 AS [% VAR]
FROM Job INNER JOIN (Job_Operation INNER JOIN Job_Operation_Time ON Job_Operation.Job_Operation = Job_Operation_Time.Job_Operation) ON Job.Job = Job_Operation.Job
WHERE (((Job_Operation.Work_Center)='DESIGN') AND ((Job.Status)='ACTIVE') AND ((Job_Operation_Time.Work_Date)=Getdate()-1) AND ((([Job_Operation.Est_Run_Hrs]/([Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs]))*100)>25))
ORDER BY ([Job_Operation.Est_Run_Hrs]/([Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs]))*100;
 
Sort by date Sort by votes
not sure why you have so complicated joins with huge number of () why not simpler
SQL: 
SELECT O.Job, 
	O.Work_Center, 
	O.Est_Run_Hrs, 
	O.Act_Run_Hrs, 
	[HRS VAR] = O.Act_Run_Hrs - O.Est_Run_Hrs, 
	J.[Status], 
	T.Employee, 
	T.Work_Date, 
	[% VAR] = O.Est_Run_Hrs/(O.Act_Run_Hrs - O.Est_Run_Hrs)*100
	FROM Job J
	INNER JOIN Job_Operation O
		ON J.Job = O.Job			
	INNER JOIN Job_Operation_Time T
		ON O.Job_Operation	=	T.Job_Operation 
	WHERE    O.Work_Center	=	'DESIGN' 
		AND J.[Status]	=	'ACTIVE' 
		AND T.Work_Date	=	Getdate() - 1 
		AND (O.Est_Run_Hrs/(O.Act_Run_Hrs-O.Est_Run_Hrs) * 100) >25
	ORDER BY O.Est_Run_Hrs/(O.Act_Run_Hrs-O.Est_Run_Hrs)*100;
 
Upvote 0 Downvote
Are you running this query in Access or SQL Server?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
code looks wrong - and it is advisable that you format your code better as it makes it easier for yourself to see where errors are, and specially if you wish us to have a look at it.

code in red below is a subquery (e.g. surrounded by 2 curved brackets) - its missing select list, and its missing an table alias as table job_operation is stated within the subquery it cannot be referenced outside it hence your error on one of its columns - but error is in a way misleading as the real error is before the column mentioned in the error.

Code: 
SELECT Job_Operation.Job
      , Job_Operation.Work_Center
      , Job_Operation.Est_Run_Hrs
      , Job_Operation.Act_Run_Hrs
      , [Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs] AS [HRS VAR]
      , Job.Status
      , Job_Operation_Time.Employee
      , Job_Operation_Time.Work_Date
      , ([Job_Operation.Est_Run_Hrs]/([Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs]))*100 AS [% VAR]
FROM Job
INNER JOIN [highlight #EF2929](Job_Operation
INNER JOIN Job_Operation_Time
ON Job_Operation.Job_Operation = Job_Operation_Time.Job_Operation)[/highlight]
ON Job.Job = Job_Operation.Job
WHERE (((Job_Operation.Work_Center)='DESIGN')
   AND ((Job.Status)='ACTIVE')
   AND ((Job_Operation_Time.Work_Date)=Getdate()-1)
   AND ((([Job_Operation.Est_Run_Hrs]/([Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs]))*100)>25))
ORDER BY ([Job_Operation.Est_Run_Hrs]/([Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs]))*100;

its possible that what you wanted to do is
Code: 
SELECT Job_Operation.Job
      , Job_Operation.Work_Center
      , Job_Operation.Est_Run_Hrs
      , Job_Operation.Act_Run_Hrs
      , [Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs] AS [HRS VAR]
      , Job.Status
      , Job_Operation_Time.Employee
      , Job_Operation_Time.Work_Date
      , ([Job_Operation.Est_Run_Hrs]/([Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs]))*100 AS [% VAR]
FROM Job
[COLOR=#3465A4]INNER JOIN Job_Operation
ON Job.Job = Job_Operation.Job
INNER JOIN Job_Operation_Time
ON Job_Operation.Job_Operation = Job_Operation_Time.Job_Operation[/color]
WHERE (((Job_Operation.Work_Center)='DESIGN')
   AND ((Job.Status)='ACTIVE')
   AND ((Job_Operation_Time.Work_Date)=Getdate()-1)
   AND ((([Job_Operation.Est_Run_Hrs]/([Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs]))*100)>25))
ORDER BY ([Job_Operation.Est_Run_Hrs]/([Job_Operation.Act_Run_Hrs]-[Job_Operation.Est_Run_Hrs]))*100;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I see the problem. I twas staring at me the whole time. When you use square brackets, you need to use them around tables and columns, but not both.

change:
[Job_Operation.Est_Run_Hrs]

To:

[Job_Operation].[Est_Run_Hrs]

Apply this consistently everywhere in your query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
@ gk53 -

it works great but sometimes I get a devide by error.
 
Upvote 0 Downvote
The divide by zero error would occur when Act_Run_Hrs is the same value as Est_Run_Hrs.

Everywhere in your query where you are doing a division, you should do this instead:

Code: 
(Est_Run_Hrs/[!]NullIf([/!]Act_Run_Hrs-Est_Run_Hrs[!], 0)[/!])*100;

With this modification, when actual = estimated, the result will be null. since you are filtering on % variance > 25, these rows will be filtered out.

Alternatively, you could add another where clause criteria like this:

Code: 
Where Act_Run_Hrs <> Est_Run_Hrs (and all your other stuff)


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I made these changes to the code but still recieve and error incorrect syntax NERA =.

SELECT Job_Operation.Job, Job_Operation.Work_Center, Job_Operation.Est_Run_Hrs, Job_Operation.Act_Run_Hrs, IIf([Job_Operation].[Act_Run_Hrs]-[Job_Operation].[Est_Run_Hrs]=0,0,([Job_Operation].[Est_Run_Hrs]/([Job_Operation].[Act_Run_Hrs]-[Job_Operation].[Est_Run_Hrs]))*100) AS [% VAR], [Job_Operation].[Act_Run_Hrs]-[Job_Operation].[Est_Run_Hrs] AS [HRS VAR], Job.Status, Job_Operation_Time.Work_Date, Job_Operation.Status


FROM Job INNER JOIN (Job_Operation INNER JOIN Job_Operation_Time ON Job_Operation.Job_Operation = Job_Operation_Time.Job_Operation) ON Job.Job = Job_Operation.Job


WHERE (((Job_Operation.Work_Center)='design') AND ((IIf([Job_Operation].[Act_Run_Hrs]-[Job_Operation].[Est_Run_Hrs]=0,0,([Job_Operation].[Est_Run_Hrs]/([Job_Operation].[Act_Run_Hrs]-[Job_Operation].[Est_Run_Hrs]))*100))>25) AND ((Job.Status)='ACTIVE') AND ((Job_Operation_Time.Work_Date)=GETDate()-1) AND ((Job_Operation.Status)<>'C'))


ORDER BY IIf([Job_Operation].[Act_Run_Hrs]-[Job_Operation].[Est_Run_Hrs]=0,0,([Job_Operation].[Est_Run_Hrs]/([Job_Operation].[Act_Run_Hrs]-[Job_Operation].[Est_Run_Hrs]))*100);
 
Upvote 0 Downvote
try this:

Code: 
SELECT  Job_Operation.Job, 
        Job_Operation.Work_Center, 
        Job_Operation.Est_Run_Hrs, 
        Job_Operation.Act_Run_Hrs, 
        Job_Operation.Est_Run_Hrs/NullIf(Job_Operation.Act_Run_Hrs-Job_Operation.Est_Run_Hrs, 0) * 100 AS [% VAR], 
        Job_Operation.Act_Run_Hrs-Job_Operation.Est_Run_Hrs AS [HRS VAR], 
        Job.Status, 
        Job_Operation_Time.Work_Date, 
        Job_Operation.Status
FROM    Job 
        INNER JOIN Job_Operation 
          ON Job.Job = Job_Operation.Job
        INNER JOIN Job_Operation_Time 
          ON Job_Operation.Job_Operation = Job_Operation_Time.Job_Operation
WHERE   Job_Operation.Work_Center='design'
        AND Job_Operation.Est_Run_Hrs/NullIf(Job_Operation.Act_Run_Hrs-Job_Operation.Est_Run_Hrs, 0) * 100 > 25
        AND Job.Status='ACTIVE'
        AND Job_Operation_Time.Work_Date=GETDate()-1
        AND Job_Operation.Status<>'C'
ORDER BY [% VAR]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
code below should work

SQL: 
;with t as 
(
SELECT O.Job, 
	O.Work_Center, 
	O.Est_Run_Hrs, 
	O.Act_Run_Hrs, 
	[HRS VAR] = O.Act_Run_Hrs - O.Est_Run_Hrs, 
	J.[Status], 
	T.Employee, 
	T.Work_Date, 
	[% VAR] = case when (O.Act_Run_Hrs - O.Est_Run_Hrs) = 0 then 0
				else  O.Est_Run_Hrs/(O.Act_Run_Hrs - O.Est_Run_Hrs)*100 end
	FROM Job J
	INNER JOIN Job_Operation O
		ON J.Job = O.Job			
	INNER JOIN Job_Operation_Time T
		ON O.Job_Operation	=	T.Job_Operation 
	WHERE    O.Work_Center	=	'DESIGN' 
		AND J.[Status]	=	'ACTIVE' 
		AND T.Work_Date	=	Getdate() - 1 
		AND 'Y' = case when (O.Act_Run_Hrs - O.Est_Run_Hrs) = 0 then 'N'
				when  (O.Est_Run_Hrs/(O.Act_Run_Hrs-O.Est_Run_Hrs) * 100) >25 then 'Y'
				else 'N' end
)
select * from t
	ORDER BY t.[% VAR]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
346
pjw001
pjw001
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
373
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
235
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
214
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
258
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top