SQL Statement (subquery) problems

[Bufsabres]

I am trying to correct a statement where two tables do not have any unique information ...
BIQUERY.TBLNEWOEE OEE and BIQUERY.TBLDTIME DT. I am trying to use a second subquery to alleviate the problem but I am not able to do so. What is the best way to segreate the BIQUERY.TBLDTIME DT in a subquery to add the Downtime column without having outrageous totals in the columns? Downtime is the only column contained in the BIQUERY.TBLDTIME DT table. The select statement is as follows...

SELECT TMP.*, AVAILABILITY * PERFORMANCEEFFICIENCY * QUALITYRATE AS OEE
FROM (SELECT OEE.MACHINENO, MACH.MACHINEDESCRIPTION, CASE WHEN IFNULL(SUM(NETTIME), 0) = 0 THEN 0 ELSE FLOAT(SUM(OPTIME)) / FLOAT(SUM(NETTIME)) END AS Availability, CASE WHEN IFNULL(SUM(OPTIME), 0) = 0 THEN 0 ELSE float(SUM(CYCTIME * PARTS)) / FLOAT(SUM(OPTIME)) END AS PerformanceEfficiency, CASE WHEN IFNULL(SUM(PARTS), 0) = 0 THEN 0 ELSE FLOAT((SUM(PARTS) - SUM(DEFECTS))) / FLOAT(SUM(PARTS)) END AS QualityRate, SUM(OEE.PARTS) AS TotalParts, SUM(DT.DTHOURS) + (FLOAT(SUM(DTMINS)) / 60) AS Downtime, SUM(DEFECTS) AS Scrap
FROM BIQUERY.TBLNEWOEE OEE, BIQUERY.VWPRODUCTIONMACHINES MACH, BIQUERY.TBLDTIME DT
WHERE OEE.COMPANY = MACH.COMPANY AND OEE.LOCATION = MACH.LOCATION AND OEE.MACHINENO = MACH.MACHINENUMBER AND OEE.COMPANY = DT.COMPANY AND OEE.LOCATION = DT.LOCATION AND OEE.MACHINENO = DT.MACHINENO AND(OEE.THEDATE BETWEEN ? AND ?) AND (OEE.MACHINENO BETWEEN ? AND ?)
GROUP BY OEE.MACHINENO, MACH.MACHINEDESCRIPTION) TMP
ORDER BY MACHINENO

 

[cbhnp]

First, this looks like a MySQL query instead of SQL Server. Second, formatting your sql statements properly will make them much easier to debug.

I suggest only calculating your sums of the TBLNEWOEE and VWPRODUCTIONMACHINES tables in the subquery. Then join the resulting TMP table to the TBLDTIME.

SELECT     TMP.*
	, SUM(DT.DTHOURS) + (FLOAT(SUM(DTMINS)) / 60) AS Downtime
	, AVAILABILITY * PERFORMANCEEFFICIENCY * QUALITYRATE AS OEE
FROM  
(SELECT OEE.MACHINENO
	, MACH.MACHINEDESCRIPTION
	, CASE WHEN IFNULL(SUM(NETTIME), 0) = 0 THEN 0 
			ELSE FLOAT(SUM(OPTIME)) / FLOAT(SUM(NETTIME))
			 END AS AVAILABILITY
	, CASE WHEN IFNULL(SUM(OPTIME), 0) = 0 THEN 0
			ELSE float(SUM(CYCTIME * PARTS)) / FLOAT(SUM(OPTIME)) END
			 AS PERFORMANCEEFFICIENCY
	, CASE WHEN IFNULL(SUM(PARTS), 0) = 0 THEN 0 
			ELSE FLOAT((SUM(PARTS) - SUM(DEFECTS))) / FLOAT(SUM(PARTS))
			END AS QUALITYRATE
	, SUM(OEE.PARTS) AS TotalParts
	, SUM(DEFECTS) AS Scrap
	FROM   BIQUERY.TBLNEWOEE OEE
	, BIQUERY.VWPRODUCTIONMACHINES MACH
	WHERE OEE.COMPANY = MACH.COMPANY
	 AND OEE.LOCATION = MACH.LOCATION
	 AND OEE.MACHINENO = MACH.MACHINENUMBER
	 AND(OEE.THEDATE BETWEEN ? AND ?)
	 AND (OEE.MACHINENO BETWEEN ? AND ?) 
	GROUP BY OEE.MACHINENO, MACH.MACHINEDESCRIPTION) TMP
	, BIQUERY.TBLDTIME DT 
WHERE
	  TMP.COMPANY = DT.COMPANY
	 AND TMP.LOCATION = DT.LOCATION
	 AND TMP.MACHINENO = DT.MACHINENO
ORDER BY MACHINENO

 

[ESquared]

Third, naming columns with something other than ALL UPPER CASE WILL MAKE THEM JUST A TAD EASIER TO READ AND SCAN. Even if the tables themselves use upper case, don't do it in your queries.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[Bufsabres]

Thanks for responding, I tried the code and here is the message...Could not generate a list of fields for the query. Check the query syntax or click Refresh Fields on the query toolbar.
ERROR [42S22][IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0205-Column COMPANY not in table TMP in *N.

 

[ESquared]

This is the SQL Server forum. I think you might be using MySQL or some other brand. Is that true? Float() is not a valid construction in MS SQL Server

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[Bufsabres]

I am new to statement writing, and I am altering someone else's reports created in MS Visual Studio.NET 2003 in the Microsoft Developement Enviroment and deploying the reports through Reporting Services.

 

[vongrunt]

I don't remember FLOAT() function/? stuff in mySQL, and subqueries were introduced just recently (v4.1). Plus mySQL folks don't have a habit to YELL at you :x. Looks like DB/2 code to me.

 

[SQLBill]

Bufsabres,

It looks to me like you are actually using Microsoft Access as a backend (database) based on this: iSeries Access ODBC Driver

If so, you should post this in the MS Access forums.

-SQLBill
Posting advice: FAQ481-4875

SELECT 'forum'
CASE [SQL TYPE]
WHEN 'MySQL' THEN 'forum436'
WHEN 'Access' THEN 'forum700', 'forum701',
'forum702', 'forum703', 'forum704', 'forum181'
WHEN 'ORACLE' THEN 'forum185' 'forum186', 'forum759'
END

 

[Bufsabres]

The database resides on an iSeries Server (AS/400), we are in the process of deploying a new SQL server. I would like to pull the data into the SQL server nightly and create reports from there. But currently I am altering existing reports and I am having trouble with what seems like a join problem. To my knowledge I am not running any MYSQL software. I have installed SQL Server 2000. I am very new to this an if I am in the wrong forum please tell me where to post and receive some help on this issue. Thanks

 

[SQLBill]

You say you are altering existing reports. Are you running these reports against the AS/400 database? If so, you need to write the programs in whatever language the AS/400 db uses. All SQL is not equal and what works in SQL Server (TSQL) may not work in other SQL's (or AS/400).

If you are altering the reports and testing them against the SQL Server database, does that database have data in it yet? It's hard to tell by your post if you have started pulling data from the AS/400 into SQL Server.

-SQLBill

 

[Bufsabres]

SQLBill:

Still running against the as400.

 

[cbhnp]

Buf,
Add these two columns to the beginning of your subquery:

OEE.COMPANY
,OEE.LOCATION

Also add them to the subquery GROUP BY statement:

GROUP BY OEE.COMPANY, OEE.LOCATION, OEE.MACHINENO, MACH.MACHINEDESCRIPTION

In my response above I didn't give the TBLDTIME table any columns in the TMP table to join to.