SQL case when 语句问题

zhangzhen116 2011-11-23 08:55:44 


(CASE WHEN 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=RESVT.CODE AND A.WO like @gd AND B.TAXUP > 0  )

         THEN

             (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=RESVT.CODE  AND B.TAXUP > 0  )

         WHEN 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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] desc )

         THEN

             (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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] DESC)

         ELSE

             (select TOP 1 TAXUP from VQUT WHERE VQUT.CODE=RESVT.CODE order by DATETIME DESC)

     END)

他的执行方式是从上往下执行，如果第一条语句没有结果再执行下一条语句。是这样的嘛？
为什么我这里他执行结果不对。
第一条语句单独执行有结果。那它应该就不会再往下执行了。 但是当我把CASE 让到一条SQL中去执行时它得到的结果跟我单独执行结果不同呢？
...全文
1514 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
流年筱澈 2011-11-23
  • 打赏
  • 举报
 回复
你写的是存在语句
这样肯定会往下执行
Mr_Nice 2011-11-23
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 zhangzhen116 的回复:]

引用 3 楼 orchidcat 的回复:
SQL code
(CASE WHEN exists(SELECT top 1 B.TAXUP --如果此处存在则执行1
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE=RESVT.CODE AND A.WO …………
[/Quote]

CASE	WHEN exists(SELECT top 1   B.TAXUP   --如果此处存在则执行1,否则判断2，成立，执行3，不成立，执行4

             FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

             WHERE B.CODE=RESVT.CODE AND A.WO like @gd AND B.TAXUP > 0  )

         THEN   --1

             (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=RESVT.CODE  AND B.TAXUP > 0  )

       ELSE

		CASE WHEN   --2

			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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] desc )

         THEN		--3

             (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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] DESC)

         ELSE		--4

             (select TOP 1 TAXUP from VQUT WHERE VQUT.CODE=RESVT.CODE order by DATETIME DESC)

		END

END
zhangzhen116 2011-11-23
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 xuam 的回复:]
满足when条件执行then语句，否则执行else语句.
[/Quote]
按照这样的逻辑那么我的SQL语句是这样？
(CASE WHEN 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=RESVT.CODE AND A.WO like @gd AND B.TAXUP > 0  )

         THEN

             (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=RESVT.CODE AND A.WO like @gd AND B.TAXUP > 0  )

         WHEN 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=RESVT.CODE  AND B.TAXUP > 0  )

         THEN 

             (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=RESVT.CODE  AND B.TAXUP > 0  )   

         WHEN 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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] desc )

         THEN

             ( 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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] desc )

         WHEN 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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] DESC)

         THEN 

             (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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] DESC)    

         ELSE

             (select TOP 1 TAXUP from VQUT WHERE VQUT.CODE=RESVT.CODE order by DATETIME DESC)

     END)DJ,
中国风 2011-11-23
  • 打赏
  • 举报
 回复
改改

SELECT 

	(CASE WHEN a.MASTERJOB = '' THEN a.JOBNO ELSE a.MASTERJOB END) AS WO,

	JOBNO,

	a.CHILDJOB,

	a.CODE,

	a.BDESC,

	a.P_CODE,

	b.CBDESC,

	a.QTY,

	a.UNIT,

	a.LOC,

	ISNULL(c.TAXUP,d.TAXUP) AS DJ,

	(CASE WHEN CHILDJOB='' THEN JOBNO ELSE CHILDJOB END)AS W,

	e.QTY AS T,

	e.QTY2 AS BL,

	h.TL,

	i.VEN,

	j.qty_nee AS QTY_NEE,

	j.SH



FROM RESVT  AS a

	OUTER APPLY

		(SELECT TOP 1 BDESC FROM PRO WHERE CODE=a.P_CODE)AS b

	OUTER APPLY

		(SELECT  top 1  TAXUP

             FROM ANT x LEFT JOIN ANTUDF y ON (x.PANNO=y.PANNO and x.ITEMNO=y.ITEMNO)

             WHERE x.CODE=a.CODE   AND y.TAXUP > 0 ORDER BY CASE WHEN x.WO=@gd THEN 1 ELSE 2 END,order by B.[DATETIME] desc)AS c				--like 改為 =

    OUTER APPLY

		(select TOP 1 TAXUP from VQUT WHERE CODE=a.CODE order by [DATETIME] DESC) AS d

	OUTER APPLY

		(SELECT TOP 1 SUM(QTY) AS QTY,SUM(CASE WHEN MRNO LIKE '%HCBL%' THEN QTY ELSE 0 end) AS QTY2  FROM MRT WHERE JOBNO=a.JOBNO AND CODE=a.CODE AND P_CODE=a.P_CODE) AS e

	OUTER APPLY

		(select TOP 1 SUM(BADQTY+PBADQTY+RETUQTY) AS TL from PRT 

		Left JOIN PRTUDF ON PRT.PRNO=PRTUDF.PRNO  AND PRT.ITEMNO=PRTUDF.ITEMNO WHERE JOBNO=a.JOBNO AND CODE=a.CODE AND P_CODE=a.P_CODE) AS h

	OUTER APPLY

		(SELECT TOP 1 VEN FROM ANT x1 LEFT JOIN ANTUDF y1 ON (x1.PANNO=y1.PANNO and x1.ITEMNO=y1.ITEMNO)WHERE x1.CODE=a.CODE AND y1.WO = @gd ) AS i

	OUTER APPLY

		(SELECT TOP 1 qty_nee,sh from #tree where #tree.code=a.CODE AND #tree.p_code=a.P_CODE) AS j

            

WHERE a.MASTERJOB = '' AND a.JOBNO=@gd OR MASTERJOB=@gd
中国风 2011-11-23
  • 打赏
  • 举报
 回复 
這樣測測



SELECT 

	(CASE WHEN a.MASTERJOB = '' THEN a.JOBNO ELSE a.MASTERJOB END) AS WO,

	JOBNO,

	a.CHILDJOB,

	a.CODE,

	a.BDESC,

	a.P_CODE,

	b.CBDESC,

	a.QTY,

	a.UNIT,

	a.LOC,

	ISNULL(c.TAXUP,d.TAXUP) AS DJ,

	(CASE WHEN CHILDJOB='' THEN JOBNO ELSE CHILDJOB END)AS W,

	e.QTY AS T,

	f.QTY AS BL,

	h.TL,

	i.VEN,

	j.qty_nee AS QTY_NEE,

	j.SH



FROM RESVT  AS a

	OUTER APPLY

		(SELECT TOP 1 BDESC FROM PRO WHERE CODE=a.P_CODE)AS b

	OUTER APPLY

		(SELECT  top 1  TAXUP

             FROM ANT x LEFT JOIN ANTUDF y ON (x.PANNO=y.PANNO and x.ITEMNO=y.ITEMNO)

             WHERE x.CODE=a.CODE   AND y.TAXUP > 0 ORDER BY CASE WHEN x.WO=@gd THEN 1 ELSE 2 END,order by B.[DATETIME] desc)AS c				--like 改為 =

    OUTER APPLY

		(select TOP 1 TAXUP from VQUT WHERE CODE=a.CODE order by [DATETIME] DESC) AS d

	OUTER APPLY

		(SELECT TOP 1 SUM(QTY) AS QTY FROM MRT WHERE JOBNO=a.JOBNO AND CODE=a.CODE AND P_CODE=a.P_CODE) AS e

	OUTER APPLY

		(SELECT TOP 1 SUM(QTY) AS QTY FROM MRT WHERE JOBNO=a.JOBNO AND CODE=a.CODE AND P_CODE=a.P_CODE AND MRNO LIKE '%HCBL%' ) AS f 

	OUTER APPLY

		(select TOP 1 SUM(BADQTY+PBADQTY+RETUQTY) AS TL from PRT 

		Left JOIN PRTUDF ON PRT.PRNO=PRTUDF.PRNO  AND PRT.ITEMNO=PRTUDF.ITEMNO WHERE JOBNO=a.JOBNO AND CODE=a.CODE AND P_CODE=a.P_CODE) AS h

	OUTER APPLY

		(SELECT TOP 1 VEN FROM ANT x1 LEFT JOIN ANTUDF y1 ON (x1.PANNO=y1.PANNO and x1.ITEMNO=y1.ITEMNO)WHERE x1.CODE=a.CODE AND y1.WO = @gd ) AS i

	OUTER APPLY

		(SELECT TOP 1 qty_nee,sh from #tree where #tree.code=a.CODE AND #tree.p_code=a.P_CODE) AS j

            

WHERE a.MASTERJOB = '' AND a.JOBNO=@gd OR MASTERJOB=@gd
中国风 2011-11-23
  • 打赏
  • 举报
 回复
全改為left join ，這語句寫得太沒效率了，改一下估計提高效率用倍數計數
houyajin 2011-11-23
  • 打赏
  • 举报
 回复
同意七楼，这个应该是程序控制的思想。没有选对语句。
zhongguoren666 2011-11-23
  • 打赏
  • 举报
 回复
同意七楼的，用IF去控制吧，你的逻辑性会很清晰，而且你调试还有你的视野都是很清楚的。
烟波钓 2011-11-23
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 orchidcat 的回复:]
SQL code
(CASE WHEN exists(SELECT top 1 B.TAXUP --如果此处存在则执行1
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE=RESVT.CODE AND A.WO ……
[/Quote]
这样没问题啊 lz放到了怎样的具体 Sql语句中了？
--小F-- 2011-11-23
  • 打赏
  • 举报
 回复
那就不要用case when 用if判断了。
if exists...
执行1
else
执行2
zhangzhen116 2011-11-23
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 orchidcat 的回复:]
SQL code
(CASE WHEN exists(SELECT top 1 B.TAXUP --如果此处存在则执行1
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE=RESVT.CODE AND A.WO ……
[/Quote]
逻辑明白了。他不是直接输出 


(CASE WHEN exists(SELECT  top 1  TAXUP

             FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

             WHERE B.CODE=RESVT.CODE AND A.WO like @gd  AND B.TAXUP > 0  )

         THEN   ----1

             (SELECT top 1   TAXUP 

             FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

             WHERE B.CODE=RESVT.CODE  AND B.TAXUP > 0  ) --AND charindex(@gd,WO)>0

         WHEN  ------2

          exists(

1有结果他会执行2所以它得到的结果是0.011
如果我想让他如果1有结果就输出1，1没结果再执行2呢?这样怎么写？
zhangzhen116 2011-11-23
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 orchidcat 的回复:]
SQL code
(CASE WHEN exists(SELECT top 1 B.TAXUP --如果此处存在则执行1
FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)
WHERE B.CODE=RESVT.CODE AND A.WO ……
[/Quote]
嗯 但是额单独把 
SELECT  top 1  TAXUP

             FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

             WHERE B.CODE=RESVT.CODE AND A.WO like @gd  AND B.TAXUP > 0
踢出去执行他是有结果的。结果是0.010
但是在整个SQL里面执行得到的结果确实0.011.
zhangzhen116 2011-11-23
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 haiwer 的回复:]
语句贴全来，估计这个语句本不该写得这么复杂
[/Quote]
SELECT (CASE WHEN MASTERJOB = '' THEN JOBNO ELSE MASTERJOB END)WO,JOBNO,CHILDJOB,CODE,BDESC,P_CODE,

(SELECT TOP 1 BDESC FROM PRO WHERE CODE=RESVT.P_CODE)CBDESC,QTY,UNIT,LOC,

(CASE WHEN exists(SELECT  top 1  TAXUP

             FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

             WHERE B.CODE=RESVT.CODE AND A.WO like @gd  AND B.TAXUP > 0  )

         THEN

             (SELECT top 1   TAXUP 

             FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

             WHERE B.CODE=RESVT.CODE  AND B.TAXUP > 0  ) --AND charindex(@gd,WO)>0

         WHEN 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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] desc )

         THEN

             (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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] DESC)

         ELSE

             (select TOP 1 TAXUP from VQUT WHERE VQUT.CODE=RESVT.CODE order by [DATETIME] DESC)

     END)DJ,

(CASE WHEN CHILDJOB='' THEN JOBNO ELSE CHILDJOB END)W,

(SELECT TOP 1 SUM(QTY) FROM MRT WHERE JOBNO=RESVT.JOBNO AND CODE=RESVT.CODE AND P_CODE=RESVT.P_CODE)T,

(SELECT TOP 1 SUM(QTY) FROM MRT WHERE JOBNO=RESVT.JOBNO AND CODE=RESVT.CODE AND P_CODE=RESVT.P_CODE AND MRNO LIKE '%HCBL%' )BL,

(select TOP 1 SUM(BADQTY+PBADQTY+RETUQTY) from PRT Left JOIN PRTUDF ON PRT.PRNO=PRTUDF.PRNO  AND PRT.ITEMNO=PRTUDF.ITEMNO WHERE JOBNO=RESVT.JOBNO AND CODE=RESVT.CODE AND P_CODE=RESVT.P_CODE)TL,

(SELECT TOP 1 VEN FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)WHERE B.CODE=RESVT.CODE AND A.WO like @gd )VEN,

(SELECT TOP 1 qty_nee from #tree where #tree.code=RESVT.CODE AND #tree.p_code=RESVT.P_CODE)QTY_NEE,

(SELECT TOP 1 sh from #tree where #tree.code=RESVT.CODE AND #tree.p_code=RESVT.P_CODE)SH

FROM RESVT WHERE 1=1 and (CASE WHEN MASTERJOB = '' THEN JOBNO ELSE MASTERJOB END)=@gd
Mr_Nice 2011-11-23
  • 打赏
  • 举报
 回复 
(CASE WHEN exists(SELECT top 1   B.TAXUP   --如果此处存在则执行1

             FROM ANT B LEFT JOIN ANTUDF A ON (B.PANNO=A.PANNO and B.ITEMNO=A.ITEMNO)

             WHERE B.CODE=RESVT.CODE AND A.WO like @gd AND B.TAXUP > 0  )

         THEN   --1

             (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=RESVT.CODE  AND B.TAXUP > 0  )

         WHEN exists(                --如果此处存在则执行2

             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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] desc )

         THEN   --2

             (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=RESVT.CODE AND B.TAXUP > 0  order by B.[DATETIME] DESC)

         ELSE   --如果上述两个都不存在，则执行此处

             (select TOP 1 TAXUP from VQUT WHERE VQUT.CODE=RESVT.CODE order by DATETIME DESC)

     END)


应该是条件排除的关系。
xuam 2011-11-23
  • 打赏
  • 举报
 回复
满足when条件执行then语句，否则执行else语句.
昵称被占用了 2011-11-23
  • 打赏
  • 举报
 回复
语句贴全来，估计这个语句本不该写得这么复杂
zhangzhen116 2011-11-23
  • 打赏
  • 举报
 回复
[Quote=引用 19 楼 roy_88 的回复:]
引用 18 楼 zhangzhen116 的回复:

数据提供程序或其他服务返回E_FAIL状态。就是这个。


這是SQL2005以上版本的寫法，你的數據庫是?

以下腳本在查詢分析器里測測，如果是程序調用，建議用存儲過程的方式

SQL code
SELECT
(CASE WHEN a.MASTERJOB = '' THEN a.JOBNO ELSE a.MA……
[/Quote]

我的是2005的。我是在存储过程里面写的。



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go











ALTER PROCEDURE [dbo].[P_DJ]

(

@deeplevel int,

@firstcode nvarchar(100),

@firstcodes nvarchar(100),

@gd nvarchar(100),

@isLeafnode int

)

AS

SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN



create table #tree 

(

bomno nvarchar(100),

code nvarchar(100),

p_code nvarchar(100),

deeplevel int, 

cbdesc nvarchar(100), 

qty_nee numeric(19,8), 

loc varchar(32),

wastage numeric(6,2),

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,BOMT.P_CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,(WASTAGE/100),1,

BOMT.CODE + left('00000000000000000000',20-len(BOMT.CODE)) from BOMT left JOIN MAINBOM on  BOMT.CODE=MAINBOM.BOMNO where BOMT.BOMNO IN(@firstcode,@firstcodes) 





WHILE @@rowcount > 0 

BEGIN

    SET @deeplevel = @deeplevel + 1



    update #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,BOMT.P_CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,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 

    (CASE WHEN a.MASTERJOB = '' THEN a.JOBNO ELSE a.MASTERJOB END) AS WO,

    JOBNO,

    a.CHILDJOB,

    a.CODE,

    a.BDESC,

    a.P_CODE,

    b.CBDESC,

    a.QTY,

    a.UNIT,

    a.LOC,

   (CASE WHEN 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=a.CODE AND A.WO like @gd AND B.TAXUP > 0  )

         THEN

             (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=a.CODE AND A.WO like @gd AND B.TAXUP > 0  )

         WHEN 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=a.CODE  AND B.TAXUP > 0  )

         THEN 

             (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=a.CODE  AND B.TAXUP > 0  )   

         WHEN 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=a.CODE AND B.TAXUP > 0  order by B.[DATETIME] desc )

         THEN

             (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=a.CODE AND B.TAXUP > 0  order by B.[DATETIME] desc )

         ELSE

             (select TOP 1 TAXUP from VQUT WHERE VQUT.CODE=a.CODE order by DATETIME DESC)

     END)DJ,

    e.QTY AS T,

    f.QTY AS BL,

    h.TL,

    i.VEN,

    j.qty_nee AS QTY_NEE,

    j.sh



FROM RESVT  AS a

    OUTER APPLY

        (SELECT TOP 1 CBDESC FROM PRO WHERE CODE=a.P_CODE)AS b



    OUTER APPLY

        (SELECT TOP 1 SUM(QTY) AS QTY FROM MRT WHERE JOBNO=a.JOBNO AND CODE=a.CODE AND P_CODE=a.P_CODE) AS e

    OUTER APPLY

        (SELECT TOP 1 SUM(QTY) AS QTY FROM MRT WHERE JOBNO=a.JOBNO AND CODE=a.CODE AND P_CODE=a.P_CODE AND MRNO LIKE '%HCBL%' ) AS f 

    OUTER APPLY

        (select TOP 1 SUM(BADQTY+PBADQTY+RETUQTY) AS TL from PRT 

        Left JOIN PRTUDF ON PRT.PRNO=PRTUDF.PRNO  AND PRT.ITEMNO=PRTUDF.ITEMNO WHERE JOBNO=a.JOBNO AND CODE=a.CODE AND P_CODE=a.P_CODE) AS h

    OUTER APPLY

        (SELECT TOP 1 VEN FROM ANT x1 LEFT JOIN ANTUDF y1 ON (x1.PANNO=y1.PANNO and x1.ITEMNO=y1.ITEMNO)WHERE x1.CODE=a.CODE AND y1.WO = @gd ) AS i

    OUTER APPLY

        (SELECT TOP 1 qty_nee,sh from #tree where #tree.code=a.CODE AND #tree.p_code=a.P_CODE) AS j

            

WHERE 1=1 and (CASE WHEN a.MASTERJOB = '' THEN a.JOBNO ELSE a.MASTERJOB END)=@gd and a.CHILDJOB='' 



RETURN @@ERROR END



SET NOCOUNT OFF

SET XACT_ABORT OFF





EXEC P_DJ 1,'WI-EGD-3862','WI-EGD-B0867',1107014,1



在数据库里面能正常执行。

中国风 2011-11-23
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 zhangzhen116 的回复:]

数据提供程序或其他服务返回E_FAIL状态。就是这个。
[/Quote]

這是SQL2005以上版本的寫法，你的數據庫是?

以下腳本在查詢分析器里測測，如果是程序調用，建議用存儲過程的方式 
SELECT 

    (CASE WHEN a.MASTERJOB = '' THEN a.JOBNO ELSE a.MASTERJOB END) AS WO,

    a.JOBNO,

    a.CHILDJOB,

    a.CODE,

    a.BDESC,

    a.P_CODE,

    b.CBDESC,

    a.QTY,

    a.UNIT,

    a.LOC,

    ISNULL(c.TAXUP,d.TAXUP) AS DJ,

    (CASE WHEN a.CHILDJOB='' THEN a.JOBNO ELSE a.CHILDJOB END)AS W,

    e.QTY AS T,

    e.QTY2 AS BL,

    h.TL,

    i.VEN,

    j.qty_nee AS QTY_NEE,

    j.SH



FROM RESVT  AS a

    OUTER APPLY

        (SELECT TOP 1 BDESC FROM PRO WHERE CODE=a.P_CODE)AS b

    OUTER APPLY

        (SELECT  top 1  TAXUP

             FROM ANT x LEFT JOIN ANTUDF y ON (x.PANNO=y.PANNO and x.ITEMNO=y.ITEMNO)

             WHERE x.CODE=a.CODE   AND y.TAXUP > 0 ORDER BY CASE WHEN x.WO=@gd THEN 1 ELSE 2 END,order by B.[DATETIME] desc)AS c                --like 改為 =

    OUTER APPLY

        (select TOP 1 TAXUP from VQUT WHERE CODE=a.CODE order by [DATETIME] DESC) AS d

    OUTER APPLY

        (SELECT TOP 1 SUM(QTY) AS QTY,SUM(CASE WHEN MRNO LIKE '%HCBL%' THEN QTY ELSE 0 end) AS QTY2  FROM MRT WHERE JOBNO=a.JOBNO AND CODE=a.CODE AND P_CODE=a.P_CODE) AS e

    OUTER APPLY

        (select TOP 1 SUM(BADQTY+PBADQTY+RETUQTY) AS TL from PRT 

        Left JOIN PRTUDF ON PRT.PRNO=PRTUDF.PRNO  AND PRT.ITEMNO=PRTUDF.ITEMNO WHERE JOBNO=a.JOBNO AND CODE=a.CODE AND P_CODE=a.P_CODE) AS h

    OUTER APPLY

        (SELECT TOP 1 VEN FROM ANT x1 LEFT JOIN ANTUDF y1 ON (x1.PANNO=y1.PANNO and x1.ITEMNO=y1.ITEMNO)WHERE x1.CODE=a.CODE AND y1.WO = @gd ) AS i

    OUTER APPLY

        (SELECT TOP 1 qty_nee,sh from #tree where #tree.code=a.CODE AND #tree.p_code=a.P_CODE) AS j

            

WHERE a.MASTERJOB = '' AND a.JOBNO=@gd OR MASTERJOB=@gd
zhangzhen116 2011-11-23
  • 打赏
  • 举报
 回复
数据提供程序或其他服务返回E_FAIL状态。就是这个。
zhangzhen116 2011-11-23
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 roy_88 的回复:]
改改


SQL code
SELECT
(CASE WHEN a.MASTERJOB = '' THEN a.JOBNO ELSE a.MASTERJOB END) AS WO,
JOBNO,
a.CHILDJOB,
a.CODE,
a.BDESC,
a.P_CODE,
b.CBDESC,
a.QTY,
……
[/Quote]
我使用您的方法在数据库里面是可以执行。但是当我帮这个存储过程用到程序中时就出问题了。
说什么E_什么的
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题：1. 自己写了SQL却不知道对不对。2. 在上线后发现原有的数据内容SQL语句执⾏结果没问题， 但是数据变化了以后SQL返回的结果就错误了。3. 稍微复杂⼀点的SQL就不知道该如何写了。4. 看不懂同⾏写的SQL是为什么。5. 不知道索引的原理。6. 不知道怎么加索引能够提⾼查询性能纵观全网的教程都只停留在概念解析的层面，没有深入剖析原理，所以看了那么多的教程，可还是不会写。本教程与其他教程不同带你深入了解原理，让你真正了解什么是SQL语句，让你懂得SQL语句的编写⽅法。不再为SQL语句而烦恼。真正做到面对任何问题能够判断出SQL语句是不是能够实现，该怎样实现。 
SQL之CASE WHEN用法详解
简单CASE WHEN函数： CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END 等同于，使用CASE WHEN条件表达式...
sql语句之case when的用法
当需要从数据源上直接判断数据显示带不动含义的时候，就可以在SQL语句中使用case when 函数。 语法如下： Case具有两种格式。简单Case函数和Case搜索函数。 简单Case函数格式： CASE 列名 WHEN 条件值1 THEN 选项1 ...
sql语句case when常用查询总结
case when语句有两种： （1）简单case when （2）case搜索函数法 简单case when只能处理等式问题，case搜索函数法可以处理等式问题也可以处理不等式问题。case when的语法逻辑 二、case when语句在业务中常见的几种...
sqlserver CASE WHEN语句示例
简单CASE WHEN 语句 case when 回复确认=’’ then ‘未完成’ when ...单条件CASE WHEN 语句 CASE WHEN 采购合同审批表.采购科是否 ='否' THEN '终止' WHEN 采购合同审批表.部门经理是否 ='否' THEN '终止' ...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章