SQL case when 语句问题

2011-11-23 08:55:44 1.6k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

winpcap无法安装提示新版本已经安装解决办法问题显示如下该问题会导致ensp里的云识别不到网卡可能是因为之前安装了npcap,而且安装时勾选了第四个选项导致无法安装,需要完全卸载npcap解决办法：办法一：在控制面板的程序与卸载里面卸载npcap和winPcap，然后再安装winPcap，如果搜不到往下看办法二：在C:\Windows\SysWOW64目录下搜索wpcap.dll和packet.dll并重命名为wpcap.dll.old和packet.dll.old办法三：如果搜不到这两个文件需要安装win10Pcap才能找到这两个文件下载地址：Wi 阅读详情



(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中去执行时它得到的结果跟我单独执行结果不同呢？

...全文

unity转微信小游戏：UI大小设置 unity转微信小游戏：UI大小设置阅读详情

zhangzhen116

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

流年筱澈 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

打赏
举报



(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

打赏
举报

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_什么的

SQL case when 语句问题

相关推荐