CTE Issue. pl need help. - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

CTE Issue. pl need help.

Author

Topic

silk2fire
Starting Member

2 Posts

Posted - 2007-10-08 : 16:58:22

WITH SPECS (PROCESS_NAME,TECH,MEAS_PROC_TYPE,MEAS_SECTOR,MEAS_OPER,PROC_PROC_TYPE,PROC_SECTOR,PROC_OPER,DV_ID,LSL,NOM,USL) AS
( SELECT
PROCESS_NAME,TECH,MEAS_PROC_TYPE,MEAS_SECTOR,MEAS_OPER,PROC_PROC_TYPE,PROC_SECTOR,PROC_OPER,DV_ID,LSL,NOM,USL
FROM
SPC.PRODUCT_SPECS B
),
WAFER_DATA(TECH,MEAS_PROC_TYPE,MEAS_SECTOR,MEAS_OPER,PROC_PROC_TYPE,PROC_SECTOR,PROC_OPER,DV_ID,MEAN,WMEAN,SIGMAAVG,SIGMAAVG2,NUMWAFERS,SUMQTY,AVGQTY) AS
(SELECT
TECH,MEAS_PROC_TYPE,MEAS_SECTOR,MEAS_OPER,PROC_PROC_TYPE,PROC_SECTOR,PROC_OPER,DV_ID,
Avg(AVG_READING) AS mean,
Sum(AVG_READING * QTY_MEAS) AS wMean,
StDev(AVG_READING) As sigmaAvg,
Avg(SIGMA * SIGMA) AS avgSigma2,
Count(*) AS NUMWAFERS,
Sum(QTY_MEAS) As sumQty,
Avg(QTY_MEAS) As avgQty
FROM
SPC.PRODUCT_WAFER_DATA A
WHERE
TECH_CODE IN ('G','U')
AND MEAS_TIMESTAMP >= dateadd(dd,-30,GETDATE())
GROUP BY
TECH,MEAS_PROC_TYPE,MEAS_SECTOR,MEAS_OPER,PROC_PROC_TYPE,PROC_SECTOR,PROC_OPER,DV_ID
)

SELECT
SPECS.PROCESS_NAME,
SPECS.TECH,
SPECS.MEAS_PROC_TYPE,
SPECS.MEAS_SECTOR,
SPECS.MEAS_OPER,
SPECS.PROC_PROC_TYPE,
SPECS.PROC_SECTOR,
SPECS.PROC_OPER,
SPECS.DV_ID,
SPECS.USL,
SPECS.NOM,
SPECS.LSL,
WAFER_DATA.mean,
WAFER_DATA.wMean,
WAFER_DATA.sigmaAvg,
WAFER_DATA.avgSigma2,
WAFER_DATA.NUMWAFERS,
WAFER_DATA.sumQty,
WAFER_DATA.avgQty
FROM
SPECS LEFT OUTER JOIN WAFER_DATA ON
(WAFER_DATA.TECH = SPECS.TECH AND WAFER_DATA.PROC_SECTOR = SPECS.PROC_SECTOR AND WAFER_DATA.PROC_OPER = WAFER_DATA.PROC_OPER AND WAFER_DATA.PROC_PROC_TYPE = SPECS.PROC_PROC_TYPE AND
WAFER_DATA.MEAS_SECTOR = SPECS.MEAS_SECTOR AND WAFER_DATA.MEAS_OPER = SPECS.MEAS_OPER AND WAFER_DATA.MEAS_PROC_TYPE = SPECS.MEAS_PROC_TYPE AND WAFER_DATA.DV_ID = SPECS.DV_ID)
ORDER BY
SPECS.TECH,SPECS.MEAS_SECTOR,SPECS.MEAS_OPER,SPECS.MEAS_PROC_TYPE,SPECS.DV_ID

****************************************
Msg 207, Level 16, State 1, Line 42
Invalid column name 'avgSigma2'.
****************************************

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 19:53:49

This?

(TECH,MEAS_PROC_TYPE,MEAS_SECTOR,MEAS_OPER,PROC_PROC_TYPE,PROC_SECTOR,PROC_OPER,DV_ID,MEAN,WMEAN,SIGMAAVG,
~~SIGMAAVG2,~~
avgSigma2,
NUMWAFERS,SUMQTY,AVGQTY)

Kristen

silk2fire
Starting Member

2 Posts

Posted - 2007-10-09 : 00:04:49

THANK YOU. you save my day.

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 04:16:21

My pleasure!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources