根据sql表值函数中的大小写选择一个查询 [英] select a query based on case in sql table valued function

问题描述

CREATE function [dbo].[getRptOrders_test](@OrderNumber int)
returns table
as
RETURN
(
select case exists (select 1 from pf  where ordernumber = @OrderNumber) 
when 1 then 

      SELECT osc.tName, osc.VariantName, osc.Quantity,pf.RequestedDate, ISNULL(osc.Shipped, 0) AS TotalQtyShipped,
      case p.isakit
            when 1 then dbo.getKitPrice(osc.ShoppingCartRecID)
            else osc.OrderedProductPrice
      end
      as OrderedProductPrice, osc.ShoppingCartRecID, osc.Notes, p.IsAKit, osc.OrderNumber,o.CompanyID
      FROM Orders_ShoppingCart osc
      INNER JOIN  p ON osc.ProductID = p.ProductID
      INNER JOIN  o ON osc.OrderNumber = o.OrderNumber
      INNER JOIN   pitem ON osc.ShoppingCartRecID = pitem.ShoppingCartRecID
      INNER JOIN  pf ON pitem.POID = pf.POID
      WHERE (osc.OrderNumber = @OrderNumber) --and dbo.HasAKit(p.productID) = 1
      and osc.iskititem=0
      
      else 
      
       SELECT osc.tName, osc.VariantName, osc.Quantity, ISNULL(osc.Shipped, 0) AS TotalQtyShipped,
      case p.isakit
            when 1 then dbo.getKitPrice(osc.ShoppingCartRecID)
            else osc.OrderedProductPrice
      end
      as OrderedProductPrice, osc.ShoppingCartRecID, osc.Notes, p.IsAKit, osc.OrderNumber,o.CompanyID
      FROM Orders_ShoppingCart osc
      INNER JOIN  p ON osc.ProductID = p.ProductID
      INNER JOIN  o ON osc.OrderNumber = o.OrderNumber
      
      WHERE (osc.OrderNumber = @OrderNumber) --and dbo.HasAKit(p.productID) = 1
      and osc.iskititem=0
     
)
 

GO

推荐答案

使用此

Use this

CREATE function [dbo].[getRptOrders_test](@OrderNumber int)
returns table
as
RETURN
(
select case when exists (select 1 from pf  where ordernumber = @OrderNumber)
then

试试这个...

Hi,

Try this...

select case WHEN  exists (select 1 from pf  where ordernumber = @OrderNumber)
then 1 ...

希望这会对你有所帮助。





干杯

Hope this will help you.


Cheers

这篇关于根据sql表值函数中的大小写选择一个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！