Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Syntax Error

Author  Topic 

iancuct
Yak Posting Veteran

73 Posts
Posted - 2004-08-12 : 13:45:01
I can't seem to figure out why this gives me syntax error, can anyone help?

(SELECT
SUM(A.PdtTotalCount * (A.PdtFormLength / 12.00)) AS DailyFootage, A.PdtProdDate,
SUM(A.PdtTotalCount * A.PdtNumUp) AS QtyName,
SUM (CASE WHEN (A.PdtShiftID = 1 OR A.PdtShiftID = 2 and A.PdtOpCode='DN') THEN
(DATEDIFF(mi,A.PdtTimeStart, A.PdtTimeStop) / 60.00)
ELSE 0
END)
AS DownTime,
SUM (CASE WHEN (A.PdtShiftID = 1 OR A.PdtShiftID = 2 and A.PdtOpCode='IT') THEN
(DATEDIFF(mi,A.PdtTimeStart, A.PdtTimeStop) / 60.00)
ELSE 0
END)
AS IdleTime,
AVG ((7.5-(A.PdtPG1Hrs + A.PdtPG2Hrs + A.PdtPG3Hrs + A.PdtPG4Hrs + A.PdtPG5Hrs))/7.5) as Efficiency,
SUM (A.PdtPG1Hrs + A.PdtPG2Hrs + A.PdtPG3Hrs + A.PdtPG4Hrs + A.PdtPG5Hrs) AS ProductGroupHours
FROM DMAIL.TblProdDetail
WHERE PdtMachType='F'
GROUP BY PdtProdDate) AS A,
Left Join
(SELECT
SUM(B.PdtTotalCount * (B.PdtFormLength / 12.00)) AS DailyFootageI, B.PdtProdDate,
SUM(PdtTotalCount * PdtNumUp) AS QtyNameI,
SUM (CASE WHEN (B.PdtShiftID = 1 OR B.PdtShiftID = 2 and B.PdtOpCode='DN') THEN
(DATEDIFF(mi,B.PdtTimeStart, B.PdtTimeStop) / 60.00)
ELSE 0
END)
AS DownTimeI,
SUM (CASE WHEN (B.PdtShiftID = 1 OR B.PdtShiftID = 2 and B.PdtOpCode='IT') THEN
(DATEDIFF(mi,B.PdtTimeStart, B.PdtTimeStop) / 60.00)
ELSE 0
END)
AS IdleTimeI,
AVG ((7.5-(B.PdtPG1Hrs + B.PdtPG2Hrs + B.PdtPG3Hrs + B.PdtPG4Hrs + B.PdtPG5Hrs))/7.5) as EfficiencyI,
SUM (B.PdtPG1Hrs + B.PdtPG2Hrs + B.PdtPG3Hrs + B.PdtPG4Hrs + B.PdtPG5Hrs) AS ProductGroupHoursI
FROM DMAIL.TblProdDetail
WHERE PdtMachType='I'
GROUP BY PdtProdDate) AS B
ON A.PdtProdDate=B.PdtProdDate

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-08-12 : 14:13:53
You should really add [ code ] and [ /code] (without any spaces) around this statement so the indentation would be visible and the code more readable...
Go to Top of Page

iancuct
Yak Posting Veteran

73 Posts
Posted - 2004-08-12 : 14:58:52
Lets see am i doing it right?

(SELECT
SUM(A.PdtTotalCount * (A.PdtFormLength / 12.00)) AS DailyFootage, A.PdtProdDate,
SUM(A.PdtTotalCount * A.PdtNumUp) AS QtyName,
SUM (CASE WHEN (A.PdtShiftID = 1 OR A.PdtShiftID = 2 and A.PdtOpCode='DN') THEN 
(DATEDIFF(mi,A.PdtTimeStart, A.PdtTimeStop) / 60.00) 
ELSE 0
END)
AS DownTime,
SUM (CASE WHEN (A.PdtShiftID = 1 OR A.PdtShiftID = 2 and A.PdtOpCode='IT') THEN 
(DATEDIFF(mi,A.PdtTimeStart, A.PdtTimeStop) / 60.00) 
ELSE 0
END)
AS IdleTime,
AVG ((7.5-(A.PdtPG1Hrs + A.PdtPG2Hrs + A.PdtPG3Hrs + A.PdtPG4Hrs + A.PdtPG5Hrs))/7.5) as Efficiency,
SUM (A.PdtPG1Hrs + A.PdtPG2Hrs + A.PdtPG3Hrs + A.PdtPG4Hrs + A.PdtPG5Hrs) AS ProductGroupHours
FROM DMAIL.TblProdDetail
WHERE PdtMachType='F'
GROUP BY PdtProdDate) AS A,
Left Join 
(SELECT 
SUM(B.PdtTotalCount * (B.PdtFormLength / 12.00)) AS DailyFootageI, B.PdtProdDate,
SUM(PdtTotalCount * PdtNumUp) AS QtyNameI,
SUM (CASE WHEN (B.PdtShiftID = 1 OR B.PdtShiftID = 2 and B.PdtOpCode='DN') THEN 
(DATEDIFF(mi,B.PdtTimeStart, B.PdtTimeStop) / 60.00) 
ELSE 0
END)
AS DownTimeI,
SUM (CASE WHEN (B.PdtShiftID = 1 OR B.PdtShiftID = 2 and B.PdtOpCode='IT') THEN 
(DATEDIFF(mi,B.PdtTimeStart, B.PdtTimeStop) / 60.00) 
ELSE 0
END)
AS IdleTimeI,
AVG ((7.5-(B.PdtPG1Hrs + B.PdtPG2Hrs + B.PdtPG3Hrs + B.PdtPG4Hrs + B.PdtPG5Hrs))/7.5) as EfficiencyI,
SUM (B.PdtPG1Hrs + B.PdtPG2Hrs + B.PdtPG3Hrs + B.PdtPG4Hrs + B.PdtPG5Hrs) AS ProductGroupHoursI
FROM DMAIL.TblProdDetail
WHERE PdtMachType='I'
GROUP BY PdtProdDate) AS B
ON A.PdtProdDate=B.PdtProdDate
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-08-12 : 15:05:39
When you view this thread, is the query formatted the same as it is in Query Analyzer? It's very hard to read in its current format. It would take quite a bit of time for us to figure this out without some indentation and line breaks. That's why no one is answering your question.

Tara
Go to Top of Page

iancuct
Yak Posting Veteran

73 Posts
Posted - 2004-08-12 : 15:51:29
[code]
(SELECT
SUM(A.PdtTotalCount * (A.PdtFormLength / 12.00)) AS DailyFootage, A.PdtProdDate,
SUM(A.PdtTotalCount * A.PdtNumUp) AS QtyName,
SUM (CASE WHEN (A.PdtShiftID = 1 OR A.PdtShiftID = 2 and A.PdtOpCode='DN') THEN
(DATEDIFF(mi,A.PdtTimeStart, A.PdtTimeStop) / 60.00)
ELSE 0
END)
AS DownTime,
SUM (CASE WHEN (A.PdtShiftID = 1 OR A.PdtShiftID = 2 and A.PdtOpCode='IT') THEN
(DATEDIFF(mi,A.PdtTimeStart, A.PdtTimeStop) / 60.00)
ELSE 0
END)
AS IdleTime,
AVG ((7.5-(A.PdtPG1Hrs + A.PdtPG2Hrs + A.PdtPG3Hrs + A.PdtPG4Hrs + A.PdtPG5Hrs))/7.5) as Efficiency,
SUM (A.PdtPG1Hrs + A.PdtPG2Hrs + A.PdtPG3Hrs + A.PdtPG4Hrs + A.PdtPG5Hrs) AS ProductGroupHours
FROM DMAIL.TblProdDetail
WHERE PdtMachType='F'
GROUP BY PdtProdDate) AS A,

Left Join

(SELECT
SUM(B.PdtTotalCount * (B.PdtFormLength / 12.00)) AS DailyFootageI, B.PdtProdDate,
SUM(PdtTotalCount * PdtNumUp) AS QtyNameI,
SUM (CASE WHEN (B.PdtShiftID = 1 OR B.PdtShiftID = 2 and B.PdtOpCode='DN') THEN
(DATEDIFF(mi,B.PdtTimeStart, B.PdtTimeStop) / 60.00)
ELSE 0
END)
AS DownTimeI,
SUM (CASE WHEN (B.PdtShiftID = 1 OR B.PdtShiftID = 2 and B.PdtOpCode='IT') THEN
(DATEDIFF(mi,B.PdtTimeStart, B.PdtTimeStop) / 60.00)
ELSE 0
END)
AS IdleTimeI,
AVG ((7.5-(B.PdtPG1Hrs + B.PdtPG2Hrs + B.PdtPG3Hrs + B.PdtPG4Hrs + B.PdtPG5Hrs))/7.5) as EfficiencyI,
SUM (B.PdtPG1Hrs + B.PdtPG2Hrs + B.PdtPG3Hrs + B.PdtPG4Hrs + B.PdtPG5Hrs) AS ProductGroupHoursI
FROM DMAIL.TblProdDetail
WHERE PdtMachType='I'
GROUP BY PdtProdDate) AS B
ON A.PdtProdDate=B.PdtProdDate
[/code]
Go to Top of Page

iancuct
Yak Posting Veteran

73 Posts
Posted - 2004-08-12 : 15:52:50
I have to Select statements which by themselves work, but i am tring to Left Join them together. Is that possible?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-08-12 : 15:54:19
Show us what the data looks like for each of the queries. I don't think that you'll be able to do it like you have it, but we can provide a solution if we first see the result sets of each query and also what they would look like when combined.

Tara
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-08-12 : 16:00:16
As far as I can see (I haven't gone into the details with the () and everything) these are two derived tables joined together but you miss the initial select. Put this in front of what you got here and see if it works (might me a longshot):
SELECT a.DailyFootage, a.PdtProdDate, a.QtyName, a.DownTime, a.IdleTime,
  a.Efficiency, a.ProductGroupHours, b.DailyFootageI, b.PdtProdDate, b.QtyNameI,
  b.DownTimeI, b.IdleTimeI, b.EfficiencyI, b.ProductGroupHoursI
FROM
Go to Top of Page

iancuct
Yak Posting Veteran

73 Posts
Posted - 2004-08-12 : 16:28:43
Thanks a lot Lumbago, you are right that's what it was , I was missing the initial Select.. I can't believe i couldn't see it , i must be going blind
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2004-08-12 : 16:31:26
No problemo compadre...
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site