SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND WO='1103046' AND B.TAXUP > 0 order by B.DATETIME desc SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND B.TAXUP > 0 order by B.DATETIME desc select TOP 1 TAXUP,DATETIME from VQUT WHERE CODE='EOJ00000700' order by DATETIME desc
如果第一条SQL没结果那么就执行第二条,如果第二条没有结果就执行第三条。
这样的SQL 怎么在一条语句中实现?
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND WO='1103046' AND B.TAXUP > 0 order by B.DATETIME desc SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND B.TAXUP > 0 order by B.DATETIME desc select TOP 1 TAXUP,DATETIME from VQUT WHERE CODE='EOJ00000700' order by DATETIME desc
如果第一条SQL没结果那么就执行第二条,如果第二条没有结果就执行第三条。
这样的SQL 怎么在一条语句中实现?
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND WO='1103046' AND B.TAXUP > 0 order by B.DATETIME desc
)
begin
if not exists (SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND B.TAXUP > 0 order by B.DATETIME desc
)
begin
select TOP 1 TAXUP,DATETIME from VQUT WHERE CODE='EOJ00000700' order by DATETIME desc
end
end
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND WO='1103046' AND B.TAXUP > 0 order by B.DATETIME desc )
begin
SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND WO='1103046' AND B.TAXUP > 0 order by B.DATETIME desc
end
else if exists(
SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND B.TAXUP > 0 order by B.DATETIME desc )
begin
SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND B.TAXUP > 0 order by B.DATETIME desc
endelse
begin select TOP 1 TAXUP,DATETIME from VQUT WHERE CODE='EOJ00000700' order by DATETIME descend
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND WO='1103046' AND B.TAXUP > 0 order by B.DATETIME desc if @@rowcount=0
SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND B.TAXUP > 0 order by B.DATETIME desc if @@rowcount=0
select TOP 1 TAXUP,DATETIME from VQUT WHERE CODE='EOJ00000700' order by DATETIME desc
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[P_DJ]
(
@deeplevel int,
@firstcode nvarchar(100),
@liqty int
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN
create table #tree
(
bomno nvarchar(100),
code nvarchar(100),
deeplevel int,
cbdesc nvarchar(100),
qty_nee numeric(19,8),
loc varchar(32),
wastage numeric(6,2),
liqty numeric(19,8),
sh numeric(19,8),
isLeafnode int,
tree nvarchar(max) default ''
)
declare
@cbdesc varchar(32),
@QTY_NEED numeric(19,8),
@loc varchar(32),
@wastage numeric(6,2)insert #tree
select BOMT.BOMNO,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((@liqty+(@liqty*(WASTAGE/100)))*QTY_NEED),(WASTAGE/100),1,
BOMT.CODE + left('00000000000000000000',20-len(BOMT.CODE)) from BOMT left JOIN MAINBOM on BOMT.CODE=MAINBOM.BOMNO where BOMT.BOMNO=@firstcode
WHILE @@rowcount > 0
BEGIN
SET @deeplevel = @deeplevel + 1update #tree set isLeafnode= 0 from #tree
join BOMT
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
insert #tree
select @firstcode,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((liqty+(liqty*(WASTAGE/100)))*QTY_NEED),sh+(WASTAGE/100),1,#tree.tree+'_'+BOMT.CODE+left('00000000000000000000',20-len(BOMT.CODE))
from BOMT
join #tree
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
left join MAINBOM
on BOMT.CODE=MAINBOM.BOMNO
END
select space((deeplevel-1)*2)+cast(deeplevel as varchar),code,cbdesc,qty_nee,loc,sh,liqty,(select top 1 TAXUP from ANT where ANT.CODE=#tree.code AND TAXUP > 0 order by DATETIME desc) taxup from #tree WHERE isLeafnode=1 RETURN @@ERROR ENDSET NOCOUNT OFF
SET XACT_ABORT OFF 这个存储过程中 taxup 这个字段就是我要修改的,要根据上面我所说的如果第一天SQL没有结果就查询第二天,如果没有再查询第三条。
if exists(SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND WO='1103046' AND B.TAXUP > 0 order by B.DATETIME desc )
SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND WO='1103046' AND B.TAXUP > 0 order by B.DATETIME desc
else
if exists(SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND B.TAXUP > 0 order by B.DATETIME desc)
SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE='EOJ00000700' AND B.TAXUP > 0 order by B.DATETIME desc
else
if exists(select TOP 1 TAXUP,DATETIME from VQUT WHERE CODE='EOJ00000700' order by DATETIME desc)
select TOP 1 TAXUP,DATETIME from VQUT WHERE CODE='EOJ00000700' order by DATETIME desc
可以直接放到存储过程中
报错的话,应该是别处的错误吧。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[P_DJ]
(
@deeplevel int,
@firstcode nvarchar(100),
@liqty int,
@gd int
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN
create table #tree
(
bomno nvarchar(100),
code nvarchar(100),
deeplevel int,
cbdesc nvarchar(100),
qty_nee numeric(19,8),
loc varchar(32),
wastage numeric(6,2),
liqty numeric(19,8),
sh numeric(19,8),
isLeafnode int,
tree nvarchar(max) default ''
)
declare
@cbdesc varchar(32),
@QTY_NEED numeric(19,8),
@loc varchar(32),
@wastage numeric(6,2)insert #tree
select BOMT.BOMNO,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((@liqty+(@liqty*(WASTAGE/100)))*QTY_NEED),(WASTAGE/100),1,
BOMT.CODE + left('00000000000000000000',20-len(BOMT.CODE)) from BOMT left JOIN MAINBOM on BOMT.CODE=MAINBOM.BOMNO where BOMT.BOMNO=@firstcode
WHILE @@rowcount > 0
BEGIN
SET @deeplevel = @deeplevel + 1update #tree set isLeafnode= 0 from #tree
join BOMT
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
insert #tree
select @firstcode,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((liqty+(liqty*(WASTAGE/100)))*QTY_NEED),sh+(WASTAGE/100),1,#tree.tree+'_'+BOMT.CODE+left('00000000000000000000',20-len(BOMT.CODE))
from BOMT
join #tree
on #tree.deeplevel=@deeplevel-1
and BOMT.BOMNO collate database_default =#tree.code
left join MAINBOM
on BOMT.CODE=MAINBOM.BOMNO
END
select space((deeplevel-1)*2)+cast(deeplevel as varchar),code,cbdesc,qty_nee,loc,sh,liqty,
(if exists(SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE=#tree.code AND WO=@gd AND B.TAXUP > 0 order by B.DATETIME desc )
begin
SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE=#tree.code AND WO=@gd AND B.TAXUP > 0 order by B.DATETIME desc
end
else if exists(
SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE=#tree.code AND B.TAXUP > 0 order by B.DATETIME desc )
begin
SELECT TOP 1 B.TAXUP
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE=#tree.code AND B.TAXUP > 0 order by B.DATETIME desc
end
else
begin
select TOP 1 TAXUP from VQUT WHERE CODE=#tree.code order by DATETIME desc
end) taxup from #tree WHERE isLeafnode=1
RETURN @@ERROR ENDSET NOCOUNT OFF
SET XACT_ABORT OFF消息 156,级别 15,状态 1,过程 P_DJ,第 58 行
关键字 'if' 附近有语法错误。
消息 102,级别 15,状态 1,过程 P_DJ,第 78 行
')' 附近有语法错误。
消息 102,级别 15,状态 1,过程 P_DJ,第 84 行
'OFF' 附近有语法错误。