case when then 问题

[pt16300]

select a.BarCode,a.WorkSheet,a.BobbinsNO,a.SO, a.POS, a.MatType,a.LOT_NO,a.ProdSpec,a.PLANT,a.BF,a.GRADE,a.LINE,a.MF, a.userid,a.reqid,a.DMP,a.ProductMF,a.PackType,a.NetWT,a.GrossWT,a.Yards,a.NumOfBobbins,a.ProductDate,a.LableDate,a.LastModify,
a.StockPosition,a.StateCode,a.OrderNo, a.LOC,a.SGRADE, a.PC,a.StateSpec,a.Width,b.VRKME,'' as bff,
case when c.Type='L' then a.Yards*c.rate
when c.Type='W'then a.NetWT*c.rate
end as New
from vStockOnSelectDetial as a
LEFT JOIN PP_ProductOrderMain as b
on a.OrderNo=b.OrderNo
left JOIN SAPUnits as c
on b.VRKME=c.Units
where a.MatType = 't'

查出 VRKME列和New列有许多为null，想在我想把为null的都默认为KG和a.NetWT的值 也就是 b.VRKME='KG' New=a.NetWT
这个语句怎么写？？

[水族杰纶]

select a.BarCode,a.WorkSheet,a.BobbinsNO,a.SO, a.POS, a.MatType,a.LOT_NO,a.ProdSpec,a.PLANT,a.BF,a.GRADE,a.LINE,a.MF, a.userid,a.reqid,a.DMP,a.ProductMF,a.PackType,a.NetWT,a.GrossWT,a.Yards,a.NumOfBobbins,a.ProductDate,a.LableDate,a.LastModify, 
                      a.StockPosition,a.StateCode,a.OrderNo, a.LOC,a.SGRADE, a.PC,a.StateSpec,a.Width,ISNULL(b.VRKME,'KG'),'' as bff, 
                      case when c.Type='L' then a.Yards*c.rate  
                            when c.Type='W'then a.NetWT*c.rate                    
                            end as New 
                      from vStockOnSelectDetial as a  
                      LEFT JOIN PP_ProductOrderMain as b 
                      on  a.OrderNo=b.OrderNo 
                    left JOIN SAPUnits as c 
                  on b.VRKME=c.Units 
where a.MatType = 't' 

[水族杰纶]

ISNULL
以指定的取代值來取代 NULL。

語法
ISNULL ( check_expression , replacement_value ) 

引數
check_expression

是要檢查 NULL 的運算式，而 check_expression 可以是任何型別。

replacement_value

如果 check_expression 為 NULL 時，所傳回的運算式，replacement_value 必須有與 check_expresssion 相同的型別。 

傳回型別
傳回與 check_expression 相同的型別。

備註
如果不是 NULL 的話，會傳回 check_expression 的值，否則會傳回 replacement_value。

範例
A. 使用帶有 AVG 的 ISNULL
此範例會尋找所有標題的平均價格，以 $10.00 取代所有在 titles 資料表中 price 資料行的 NULL 項目。

USE pubs
GO
SELECT AVG(ISNULL(price, $10.00))
FROM titles
GO

以下為結果集：

-------------------------- 
14.24                      

(1 row(s) affected)

B. 使用 ISNULL
此範例選取在 titles 資料表中所有書籍的標題、類型與價格。如果給定標題的價格為 NULL，則在結果集中顯示的價格則為 0.00。

USE pubs
GO
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type, 
   ISNULL(price, 0.00) AS Price
FROM titles
GO

以下為結果集：

Title           Type         Price          
--------------- ------------ -------------------------- 
The Busy Execut business     19.99                      
Cooking with Co business     11.95                      
You Can Combat  business     2.99                       
Straight Talk A business     19.99                      
Silicon Valley  mod_cook     19.99                      
The Gourmet Mic mod_cook     2.99                       
The Psychology  UNDECIDED    0.00                       
But Is It User  popular_comp 22.95                      
Secrets of Sili popular_comp 20.00                      
Net Etiquette   popular_comp 0.00                       
Computer Phobic psychology   21.59                      
Is Anger the En psychology   10.95                      
Life Without Fe psychology   7.00                       
Prolonged Data  psychology   19.99                      
Emotional Secur psychology   7.99                       
Onions, Leeks,  trad_cook    20.95                      
Fifty Years in  trad_cook    11.95                      
Sushi, Anyone?  trad_cook    14.99                      

(18 row(s) affected)

[lizhengnan]

                      case when c.Type='L' then a.Yards*c.rate  
                            when c.Type='W'then a.NetWT*c.rate                    
                            else a.NetWT end as New 

[ljhcy99]

select a.BarCode,a.WorkSheet,a.BobbinsNO,a.SO, a.POS, a.MatType,a.LOT_NO,a.ProdSpec,a.PLANT,a.BF,a.GRADE,a.LINE,a.MF, a.userid,a.reqid,a.DMP,a.ProductMF,a.PackType,a.NetWT,a.GrossWT,a.Yards,a.NumOfBobbins,a.ProductDate,a.LableDate,a.LastModify,
a.StockPosition,a.StateCode,a.OrderNo, a.LOC,a.SGRADE, a.PC,a.StateSpec,a.Width,isnull(b.VRKME,a.NetWT) as NetWT,'' as bff,
case when c.Type='L' then a.Yards*c.rate
when c.Type='W'then a.NetWT*c.rate
else KG end as New
from vStockOnSelectDetial as a
LEFT JOIN PP_ProductOrderMain as b
on a.OrderNo=b.OrderNo
left JOIN SAPUnits as c
on b.VRKME=c.Units
where a.MatType = 't'

[-狙击手-]

isnull()

[pt16300]

KG解决了
New列中 的Null 怎么解决呢??
把他默认为 a.NetWT列里面的值

[dobear_0922]

select a.BarCode,a.WorkSheet,a.BobbinsNO,a.SO, a.POS, a.MatType,a.LOT_NO,a.ProdSpec
	,a.PLANT,a.BF,a.GRADE,a.LINE,a.MF, a.userid,a.reqid,a.DMP,a.ProductMF,a.PackType
	,a.NetWT,a.GrossWT,a.Yards,a.NumOfBobbins,a.ProductDate,a.LableDate,a.LastModify
	,a.StockPosition,a.StateCode,a.OrderNo, a.LOC,a.SGRADE, a.PC,a.StateSpec,a.Width
	,isnull(b.VRKME, 'KG') as VRKME,'' as bff
	,case when c.Type='L' then a.Yards*c.rate  
          when c.Type='W'then a.NetWT*c.rate
		  else a.NetWT                    
     end as New 
from vStockOnSelectDetial as a  
	LEFT JOIN PP_ProductOrderMain as b on  a.OrderNo=b.OrderNo 
    left JOIN SAPUnits as c on b.VRKME=c.Units 
where a.MatType = 't' 

[dobear_0922]

[Quote=引用 6 楼 pt16300 的回复:]
KG解决了
New列中 的Null 怎么解决呢??
把他默认为 a.NetWT列里面的值
[/Quote]
在Case when后面加个else就行了，参考7楼。

[水族杰纶]

--TRY
select a.BarCode,a.WorkSheet,a.BobbinsNO,a.SO, a.POS, a.MatType,a.LOT_NO,a.ProdSpec,a.PLANT,a.BF,a.GRADE,a.LINE,a.MF, a.userid,a.reqid,a.DMP,a.ProductMF,a.PackType,a.NetWT,a.GrossWT,a.Yards,a.NumOfBobbins,a.ProductDate,a.LableDate,a.LastModify, 
                      a.StockPosition,a.StateCode,a.OrderNo, a.LOC,a.SGRADE, a.PC,a.StateSpec,a.Width,ISNULL(b.VRKME,'KG'),'' as bff, 
                      case when c.Type='L' then a.Yards*c.rate  
                            when c.Type='W'then a.NetWT*c.rate 
                             else a.NetWT                   
                            end as New 
                      from vStockOnSelectDetial as a  
                      LEFT JOIN PP_ProductOrderMain as b 
                      on  a.OrderNo=b.OrderNo 
                    left JOIN SAPUnits as c 
                  on b.VRKME=c.Units 
where a.MatType = 't' 

[pt16300]

我解决了一个问题 但是 问题突然有多了起来
1. New列里面的NULL 没有 转换成 a.NetWT里面的值
2.本来根按照 MatType 里面的值 当MatType='t' or 'w' or 'd'时 VRKME列和New列里面才有数据
当MatType='s' or 'h' VRKME列和New列里面数据为NULL
所以还要判断下。。。
请问这样要怎么搞呢？？

[pt16300]

非常感谢各位
我才进公司...
还很多不懂

[pt16300]

select a.BarCode,a.WorkSheet,a.BobbinsNO,a.SO, a.POS, a.MatType,a.LOT_NO,a.ProdSpec,a.PLANT,a.BF,a.GRADE,a.LINE,a.MF, a.userid,a.reqid,a.DMP,a.ProductMF,a.PackType,a.NetWT,a.GrossWT,a.Yards,a.NumOfBobbins,a.ProductDate,a.LableDate,a.LastModify,
a.StockPosition,a.StateCode,a.OrderNo, a.LOC,a.SGRADE, a.PC,a.StateSpec,a.Width,'' as bff,
case when a.MatType='t'or a.MatType='w' or a.MatType='d'
then ISNULL(b.VRKME,'KG')end as VRKME,
case when a.MatType='t'or a.MatType='w' or a.MatType='d'
c.Type='L' then a.Yards*c.rate ,
c.Type='W'then a.NetWT*c.rate
else a.NetWT
end as New
from vStockOnSelectDetial as a
LEFT JOIN PP_ProductOrderMain as b
on a.OrderNo=b.OrderNo
left JOIN SAPUnits as c
on b.VRKME=c.Units
where a.MatType = 't'

第5行 c有 语法错误
怎么修改??

[水族杰纶]

select a.BarCode,a.WorkSheet,a.BobbinsNO,a.SO, a.POS, a.MatType,a.LOT_NO,a.ProdSpec,a.PLANT,a.BF,a.GRADE,a.LINE,a.MF, a.userid,a.reqid,a.DMP,a.ProductMF,a.PackType,a.NetWT,a.GrossWT,a.Yards,a.NumOfBobbins,a.ProductDate,a.LableDate,a.LastModify, 
                      a.StockPosition,a.StateCode,a.OrderNo, a.LOC,a.SGRADE, a.PC,a.StateSpec,a.Width,'' as bff, 
                      case when a.MatType='t'or a.MatType='w' or a.MatType='d' 
                            then ISNULL(b.VRKME,'KG')end as VRKME, 
                        case    when a.MatType='t'or a.MatType='w' or a.MatType='d'  OR
                                c.Type='L' then a.Yards*c.rate WHEN
                                c.Type='W'then a.NetWT*c.rate 
                            else a.NetWT                  
                            end as New 
                      from vStockOnSelectDetial as a  
                      LEFT JOIN PP_ProductOrderMain as b 
                      on  a.OrderNo=b.OrderNo 
                    left JOIN SAPUnits as c 
                  on b.VRKME=c.Units 
where a.MatType = 't' 

[pt16300]

select a.BarCode,a.WorkSheet,a.BobbinsNO,a.SO, a.POS, a.MatType,a.LOT_NO,a.ProdSpec,a.PLANT,a.BF,a.GRADE,a.LINE,a.MF, a.userid,a.reqid,a.DMP,a.ProductMF,a.PackType,a.NetWT,a.GrossWT,a.Yards,a.NumOfBobbins,a.ProductDate,a.LableDate,a.LastModify,a.StockPosition,a.StateCode,a.OrderNo, a.LOC,a.SGRADE, a.PC,a.StateSpec,a.Width,'' as bff,
case when a.MatType='t'or a.MatType='w' or a.MatType='d' then ISNULL(b.VRKME,'KG')end as VRKME,
case when a.MatType in ('t','w' ,'d') AND c.Type='L' then a.Yards * c.rate
when a.MatType in ('t','w' ,'d') AND c.Type='W'then a.NetWT * c.rate
-- when a.MatType IN ('S','H') THEN a.NetWT is null
else a.NetWT
end as New
from vStockOnSelectDetial as a
LEFT JOIN PP_ProductOrderMain as b
on a.OrderNo=b.OrderNo
left JOIN SAPUnits as c
on b.VRKME=c.Units
where a.MatType = 'S'

这个是最新测试了的 MatType为S,h时候 VRKME为 NULL了 就是 New列有数据 怎么判断当MatType为S,h时候 VRKME和 New 都是NULL
而 MatType为t,w,d时候 VRKME和 New 列是按 要求显示 数据??

[oraclelogan]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL codeselect a.BarCode,a.WorkSheet,a.BobbinsNO,a.SO, a.POS, a.MatType,a.LOT_NO,a.ProdSpec,a.PLANT,a.BF,a.GRADE,a.LINE,a.MF, a.userid,a.reqid,a.DMP,a.ProductMF,a.PackType,a.NetWT,a.GrossWT,a.Yards,a.NumOfBobbins,a.ProductDate,a.LableDate,a.LastModify,
a.StockPosition,a.StateCode,a.OrderNo, a.LOC,a.SGRADE, a.PC,a.StateSpec,a.Width,ISNULL(b.VRKME,'KG'),'' as bff,
…
[/Quote]

正解啊，老大！

[pt16300]

select a.BarCode,a.WorkSheet,a.BobbinsNO,a.SO, a.POS, a.MatType,a.LOT_NO,a.ProdSpec,a.PLANT,a.BF,a.GRADE,a.LINE,a.MF, a.userid,a.reqid,a.DMP,a.ProductMF,a.PackType,a.NetWT,a.GrossWT,a.Yards,a.NumOfBobbins,a.ProductDate,a.LableDate,a.LastModify,a.StockPosition,a.StateCode,a.OrderNo, a.LOC,a.SGRADE, a.PC,a.StateSpec,a.Width,'' as bff, 
  case when a.MatType in ('t','w' ,'d') then ISNULL(b.VRKME,'KG')end as VRKME,
  case when a.MatType in ('t','w' ,'d') AND c.Type='L' then a.Yards * c.rate                                 
       when a.MatType in ('t','w' ,'d') AND c.Type='W'then a.NetWT * c.rate 
       when a.MatType IN ('S','H') then null
       else a.NetWT                   
       end as New 
--case when a.MatType IN ('S','H') THEN  a.NetWT  end as New
from vStockOnSelectDetial as a  
     LEFT JOIN PP_ProductOrderMain as b on  a.OrderNo=b.OrderNo 
     LEFT JOIN SAPUnits as c on b.VRKME=c.Units 
where a.MatType = 'h'

搞定了...
then NULL...
感谢大家!!