- SQL code
SELECT SUBSTRING(REGDATE,1,4) + '-' + SUBSTRING(REGDATE,5,2) + '-' + SUBSTRING(REGDATE,7,2) as [日期], SUBSTRING(REGDATE,9,2) + ':' + SUBSTRING(REGDATE,11,2) as [时间], N'入库指示' as [文件类型], YYVBELN as [出货单编号], YYINVNO as [发票编号], YYEBELN as [PO编号], REGDATE as [登录日] from IN_STOCK_HEADER WHERE REGDATE like '%20111212%' UNION ALL --出库指示 SELECT SUBSTRING(REGDATE,1,4) + '-' + SUBSTRING(REGDATE,5,2) + '-' + SUBSTRING(REGDATE,7,2) as [日期], SUBSTRING(REGDATE,9,2) + ':' + SUBSTRING(REGDATE,11,2) as [时间], N'出库指示' as [文件类型], VBELN as [出货单编号], '' as [发票编号], '' as [PO编号], REGDATE as [登录日] from OUT_STOCK_HEADER
这是UNION ALL后的结果集，如果再进行查询？SQL
------解决方案--------------------
- SQL code
select *from (SELECT SUBSTRING(REGDATE,1,4) + '-' + SUBSTRING(REGDATE,5,2) + '-' + SUBSTRING(REGDATE,7,2) as [日期], SUBSTRING(REGDATE,9,2) + ':' + SUBSTRING(REGDATE,11,2) as [时间], N'入库指示' as [文件类型], YYVBELN as [出货单编号], YYINVNO as [发票编号], YYEBELN as [PO编号], REGDATE as [登录日] from IN_STOCK_HEADER WHERE REGDATE like '%20111212%' UNION ALL --出库指示 SELECT SUBSTRING(REGDATE,1,4) + '-' + SUBSTRING(REGDATE,5,2) + '-' + SUBSTRING(REGDATE,7,2) as [日期], SUBSTRING(REGDATE,9,2) + ':' + SUBSTRING(REGDATE,11,2) as [时间], N'出库指示' as [文件类型], VBELN as [出货单编号], '' as [发票编号], '' as [PO编号], REGDATE as [登录日] from OUT_STOCK_HEADER)twhere ....
------解决方案--------------------
把你的查询括起来组成派生表
select * from (你的查询语句) as t where ....
as t不能漏掉，派生表需要别名