请问为什么会报错?有点搞不懂.报错信息:
服务器: 消息 8155,级别 16,状态 2,行 1
No column was specified for column 3 of 'minTrackInTime'.SQL statement:select
isnull(packagetypename,'') as packagetype,
isnull(dimensionname,'') as dimension,
isnull(leadcountname,'') as leadcount,
isnull(productname,'') as productname,
isnull(MfgOrder.MfgOrderName,'') as MfgOrderName,
Container.containername,
isnull(ResourceDef.ResourceName,'') as ResourceName,
isnull(e.employeename ,'') as trackInemployee,
isnull(HISTORYMAINLINE.TxnDATE,'') as TrackInDateTime ,
isnull(hm.TxnDATE,'') as TrackOutDateTime,
isnull(eOUT.employeename ,'') as trackOUTemployee,
lottrackresourcehistory.Qty as TrackOutQty
from Container
left outer join MfgOrder on Container.MfgOrderId=MfgOrder.MfgOrderID
left outer join HISTORYMAINLINE WITH (NOLOCK) on Container.containerid =HISTORYMAINLINE.containerid
left outer join cdodefinition WITH (NOLOCK) on HISTORYMAINLINE.TxnType = cdodefinition.CDODEFID
left outer join operation WITH (NOLOCK) on HISTORYMAINLINE.operationid = operation.operationid
left outer join HISTORYMAINLINE hm on hm.containerid=Container.containerid and hm.resourceid=HISTORYMAINLINE.resourceid
left outer join cdodefinition cdodefinitionout WITH (NOLOCK) on hm.TxnType = cdodefinitionout.CDODEFID
left outer join operation operationout WITH (NOLOCK) on hm.operationid = operationout.operationid
Left outer join ResourceDef with (nolock) on ResourceDef.ResourceId = HistoryMainLine.ResourceId
Left Outer Join lottrackresourcehistory with (NoLock) on lottrackresourcehistory.HistoryMainLineId = hm.HistoryMainLineId
Left Outer Join employee e with (NoLock) on e.employeeid=HISTORYMAINLINE.EmployeeId
left outer join employee eOut with (NoLock) on eOut.employeeid=HM.EmployeeId
left outer join product with (nolock) on product.productid = container.productid
left outer join productbase with (nolock) on product.productbaseid = productbase.productbaseid
left outer join packagetype with (nolock) on packagetype.packagetypeid = product.packagetypeid
left outer join dimension with (nolock) on product.dimensionid = dimension.dimensionid
left outer join leadcount with (nolock) on product.leadcountid = leadcount.leadcountid
JOIN
(
SELECT containername,resourcename,min(h.txndate)
from Container
left outer join HISTORYMAINLINE h WITH (NOLOCK) on Container.containerid =h.containerid
left outer join cdodefinition WITH (NOLOCK) on h.TxnType = cdodefinition.CDODEFID
left outer join operation WITH (NOLOCK) on h.operationid = operation.operationid
Left outer join ResourceDef with (nolock) on ResourceDef.ResourceId = h.ResourceId
where
Operation.OperationName='Wire Bond'
and cdodefinition.CDONAME='LotTrackInResource'
and h.TxnDATE between '2006-10-23 00:00:00' and '2006-10-24 23:59:59'
and containername='799504:2'
group by containername,resourcename
) minTrackInTime on minTrackInTime.containername=container.containername and minTrackInTime.ResourceName=ResourceDef.ResourceName
JOIN
(
SELECT containername,resourcename,max(h.txndate)
from Container
left outer join HISTORYMAINLINE h WITH (NOLOCK) on Container.containerid =h.containerid
left outer join cdodefinition WITH (NOLOCK) on h.TxnType = cdodefinition.CDODEFID
left outer join operation WITH (NOLOCK) on h.operationid = operation.operationid
Left outer join ResourceDef with (nolock) on ResourceDef.ResourceId = h.ResourceId
where
Operation.OperationName='Wire Bond'
and cdodefinition.CDONAME='LotTrackInResource'
and h.TxnDATE between '2006-10-23 00:00:00' and '2006-10-24 23:59:59'
and containername='799504:2'
group by containername,resourcename
) mAnTrackInTime on mAnTrackInTime.containername=container.containername and mAnTrackInTime.ResourceName=ResourceDef.ResourceName
JOIN
(
SELECT containername,resourcename,min(h.txndate)
from Container
left outer join HISTORYMAINLINE h WITH (NOLOCK) on Container.containerid =h.containerid
left outer join cdodefinition WITH (NOLOCK) on h.TxnType = cdodefinition.CDODEFID
left outer join operation WITH (NOLOCK) on h.operationid = operation.operationid
Left outer join ResourceDef with (nolock) on ResourceDef.ResourceId = h.ResourceId
where
Operation.OperationName='Wire Bond'
and cdodefinition.CDONAME='LotTrackOUTResource'
and h.TxnDATE between '2006-10-23 00:00:00' and '2006-10-24 23:59:59'
and containername='799504:2'
group by containername,resourcename
) minTrackOUTTime on minTrackOUTTime.containername=container.containername and minTrackOUTTime.ResourceName=ResourceDef.ResourceName
JOIN
(
SELECT containername,resourcename,max(h.txndate)
from Container
left outer join HISTORYMAINLINE h WITH (NOLOCK) on Container.containerid =h.containerid
left outer join cdodefinition WITH (NOLOCK) on h.TxnType = cdodefinition.CDODEFID
left outer join operation WITH (NOLOCK) on h.operationid = operation.operationid
Left outer join ResourceDef with (nolock) on ResourceDef.ResourceId = h.ResourceId
where
Operation.OperationName='Wire Bond'
and cdodefinition.CDONAME='LotTrackOUTResource'
and h.TxnDATE between '2006-10-23 00:00:00' and '2006-10-24 23:59:59'
and containername='799504:2'
group by containername,resourcename
) mAnTrackOUTTime on mAnTrackOUTTime.containername=container.containername and mAnTrackOUTTime.ResourceName=ResourceDef.ResourceName
where
((HISTORYMAINLINE.TxnDATE=minTrackInTime.TxnDATE and hm.TxnDATE=minTrackOUTTime.TxnDATE)
or( HISTORYMAINLINE.TxnDATE=mAnTrackInTime.TxnDATE and hm.TxnDATE=mAnTrackOUTTime.TxnDATE ))and
Operation.OperationName='Wire Bond'
and cdodefinition.CDONAME='LotTrackInResource'
and cdodefinitionout.CDONAME='LotTrackOutResource'
and operationout.operationname='Wire Bond'
and HISTORYMAINLINE.TxnDATE between '2006-10-23 00:00:00' and '2006-10-24 23:59:59'
--and DATEDIFF (minute,HISTORYMAINLINE.TxnDATE,hm.TxnDATE)<15
and container.containername='799504:2'
order by
packagetypename,containername,resourcename
服务器: 消息 8155,级别 16,状态 2,行 1
No column was specified for column 3 of 'minTrackInTime'.SQL statement:select
isnull(packagetypename,'') as packagetype,
isnull(dimensionname,'') as dimension,
isnull(leadcountname,'') as leadcount,
isnull(productname,'') as productname,
isnull(MfgOrder.MfgOrderName,'') as MfgOrderName,
Container.containername,
isnull(ResourceDef.ResourceName,'') as ResourceName,
isnull(e.employeename ,'') as trackInemployee,
isnull(HISTORYMAINLINE.TxnDATE,'') as TrackInDateTime ,
isnull(hm.TxnDATE,'') as TrackOutDateTime,
isnull(eOUT.employeename ,'') as trackOUTemployee,
lottrackresourcehistory.Qty as TrackOutQty
from Container
left outer join MfgOrder on Container.MfgOrderId=MfgOrder.MfgOrderID
left outer join HISTORYMAINLINE WITH (NOLOCK) on Container.containerid =HISTORYMAINLINE.containerid
left outer join cdodefinition WITH (NOLOCK) on HISTORYMAINLINE.TxnType = cdodefinition.CDODEFID
left outer join operation WITH (NOLOCK) on HISTORYMAINLINE.operationid = operation.operationid
left outer join HISTORYMAINLINE hm on hm.containerid=Container.containerid and hm.resourceid=HISTORYMAINLINE.resourceid
left outer join cdodefinition cdodefinitionout WITH (NOLOCK) on hm.TxnType = cdodefinitionout.CDODEFID
left outer join operation operationout WITH (NOLOCK) on hm.operationid = operationout.operationid
Left outer join ResourceDef with (nolock) on ResourceDef.ResourceId = HistoryMainLine.ResourceId
Left Outer Join lottrackresourcehistory with (NoLock) on lottrackresourcehistory.HistoryMainLineId = hm.HistoryMainLineId
Left Outer Join employee e with (NoLock) on e.employeeid=HISTORYMAINLINE.EmployeeId
left outer join employee eOut with (NoLock) on eOut.employeeid=HM.EmployeeId
left outer join product with (nolock) on product.productid = container.productid
left outer join productbase with (nolock) on product.productbaseid = productbase.productbaseid
left outer join packagetype with (nolock) on packagetype.packagetypeid = product.packagetypeid
left outer join dimension with (nolock) on product.dimensionid = dimension.dimensionid
left outer join leadcount with (nolock) on product.leadcountid = leadcount.leadcountid
JOIN
(
SELECT containername,resourcename,min(h.txndate)
from Container
left outer join HISTORYMAINLINE h WITH (NOLOCK) on Container.containerid =h.containerid
left outer join cdodefinition WITH (NOLOCK) on h.TxnType = cdodefinition.CDODEFID
left outer join operation WITH (NOLOCK) on h.operationid = operation.operationid
Left outer join ResourceDef with (nolock) on ResourceDef.ResourceId = h.ResourceId
where
Operation.OperationName='Wire Bond'
and cdodefinition.CDONAME='LotTrackInResource'
and h.TxnDATE between '2006-10-23 00:00:00' and '2006-10-24 23:59:59'
and containername='799504:2'
group by containername,resourcename
) minTrackInTime on minTrackInTime.containername=container.containername and minTrackInTime.ResourceName=ResourceDef.ResourceName
JOIN
(
SELECT containername,resourcename,max(h.txndate)
from Container
left outer join HISTORYMAINLINE h WITH (NOLOCK) on Container.containerid =h.containerid
left outer join cdodefinition WITH (NOLOCK) on h.TxnType = cdodefinition.CDODEFID
left outer join operation WITH (NOLOCK) on h.operationid = operation.operationid
Left outer join ResourceDef with (nolock) on ResourceDef.ResourceId = h.ResourceId
where
Operation.OperationName='Wire Bond'
and cdodefinition.CDONAME='LotTrackInResource'
and h.TxnDATE between '2006-10-23 00:00:00' and '2006-10-24 23:59:59'
and containername='799504:2'
group by containername,resourcename
) mAnTrackInTime on mAnTrackInTime.containername=container.containername and mAnTrackInTime.ResourceName=ResourceDef.ResourceName
JOIN
(
SELECT containername,resourcename,min(h.txndate)
from Container
left outer join HISTORYMAINLINE h WITH (NOLOCK) on Container.containerid =h.containerid
left outer join cdodefinition WITH (NOLOCK) on h.TxnType = cdodefinition.CDODEFID
left outer join operation WITH (NOLOCK) on h.operationid = operation.operationid
Left outer join ResourceDef with (nolock) on ResourceDef.ResourceId = h.ResourceId
where
Operation.OperationName='Wire Bond'
and cdodefinition.CDONAME='LotTrackOUTResource'
and h.TxnDATE between '2006-10-23 00:00:00' and '2006-10-24 23:59:59'
and containername='799504:2'
group by containername,resourcename
) minTrackOUTTime on minTrackOUTTime.containername=container.containername and minTrackOUTTime.ResourceName=ResourceDef.ResourceName
JOIN
(
SELECT containername,resourcename,max(h.txndate)
from Container
left outer join HISTORYMAINLINE h WITH (NOLOCK) on Container.containerid =h.containerid
left outer join cdodefinition WITH (NOLOCK) on h.TxnType = cdodefinition.CDODEFID
left outer join operation WITH (NOLOCK) on h.operationid = operation.operationid
Left outer join ResourceDef with (nolock) on ResourceDef.ResourceId = h.ResourceId
where
Operation.OperationName='Wire Bond'
and cdodefinition.CDONAME='LotTrackOUTResource'
and h.TxnDATE between '2006-10-23 00:00:00' and '2006-10-24 23:59:59'
and containername='799504:2'
group by containername,resourcename
) mAnTrackOUTTime on mAnTrackOUTTime.containername=container.containername and mAnTrackOUTTime.ResourceName=ResourceDef.ResourceName
where
((HISTORYMAINLINE.TxnDATE=minTrackInTime.TxnDATE and hm.TxnDATE=minTrackOUTTime.TxnDATE)
or( HISTORYMAINLINE.TxnDATE=mAnTrackInTime.TxnDATE and hm.TxnDATE=mAnTrackOUTTime.TxnDATE ))and
Operation.OperationName='Wire Bond'
and cdodefinition.CDONAME='LotTrackInResource'
and cdodefinitionout.CDONAME='LotTrackOutResource'
and operationout.operationname='Wire Bond'
and HISTORYMAINLINE.TxnDATE between '2006-10-23 00:00:00' and '2006-10-24 23:59:59'
--and DATEDIFF (minute,HISTORYMAINLINE.TxnDATE,hm.TxnDATE)<15
and container.containername='799504:2'
order by
packagetypename,containername,resourcename
((HISTORYMAINLINE.TxnDATE=minTrackInTime.TxnDATE and hm.TxnDATE=minTrackOUTTime.TxnDATE)其中 minTrackInTime.TxnDATE 未定义,所以修改上面定义SELECT containername,resourcename,min(h.txndate)中的 min(h.txndate) 修改成 min(h.txndate) as TxnDATE
多谢晒!where
((HISTORYMAINLINE.TxnDATE=minTrackInTime.TxnDATE and hm.TxnDATE=minTrackOUTTime.TxnDATE)其中 minTrackInTime.TxnDATE 未定义,所以修改上面定义SELECT containername,resourcename,min(h.txndate)中的 min(h.txndate) 修改成 min(h.txndate) as TxnDATE
顶了,`~~