首页新闻找找看学习计划

两个表内连接,查询一个需要的字段(内连接中的数据),如何插入到表值函数?

0
悬赏园豆:100 [已关闭问题] 关闭于 2019-11-21 10:33

本来按照需求要显示一个新的字段,打印出来,但是呢,这个字段在之前的数据集里面是不存在的,于是我就想着新增啊,查找两个表,找到自己所需要的字段,把查找的结果作为一个新的字段插入到原来的数据集里面(这个数据集是通过表值函数获得的),然后在报表绑定数据的时候就可以通过数据集来带出来,但是我不会弄啊,表值函数看着好复杂

下面是我查找获得的字段(我需要的)
SELECT fFabricWeight FROM dbo.ProductionConsignBillDetail a
INNER JOIN dbo.ProductionConsignBill b
ON b.strConsignBillCode = a.strConsignBillCode
WHERE a.strFactoryBatchNum= b.strProductionOutputBillCode AND b.bIsCancel=0

下面是表值函数(头疼):
USE [TFERP_DYRC_CUS]
GO
/****** Object: UserDefinedFunction [dbo].[F_getstrProcessItemTable2_Print] Script Date: 2019/11/20 15:22:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[F_getstrProcessItemTable2_Print]
(
@sInvoiceNo NVARCHAR(50),
@sCustomer NVARCHAR(100),
@sOrderCode NVARCHAR(100),
@sFlowCardCode NVARCHAR(100),
@sFabricCode NVARCHAR(100),
@sFabricName NVARCHAR(200),
@sColorNumber NVARCHAR(100),
@bisSample VARCHAR(20),
@dDateStart DATETIME,
@dDateEnd DATETIME
)
RETURNS @objReturn TABLE
(
strInvoiceNo VARCHAR(50),
dtConsignTime DATETIME,
strFlowCardCode VARCHAR(100),
strCustomerName VARCHAR(50),
strDetailCustomer VARCHAR(150),--由于美利用明细客户保存客户的客户单号,所以取值直接从卡取,史2011-7-13
strCustomerOrderCode VARCHAR(100),
strOrderCode VARCHAR(200),
strFabricName VARCHAR(200),
strColorNumber VARCHAR(100),
strColorName VARCHAR(100),
fDeliveryRealQty FLOAT,
fWeight FLOAT,
strItem VARCHAR(150),
fprice FLOAT,
strpriceUnit VARCHAR(16),
strSumUnit VARCHAR(16),
fSumMoney FLOAT,
uiLevel VARCHAR(50),
fReceivedAmount FLOAT,
strAlias VARCHAR(200),
strWeightUnit VARCHAR(50),
strCountUnit VARCHAR(50),
fCheckSum FLOAT,
strFabricClass VARCHAR(50),
strColorLevel VARCHAR(50),
strFabricCode VARCHAR(50),
strNote VARCHAR(150),
strProcessMainFlowName VARCHAR(100),--加工主流程
strOtherChargeItem VARCHAR(200),--另收费项目
strOtherKindItem VARCHAR(100),--另类收费项目(纸筒)
strScroll VARCHAR(50), --纸筒的公斤数
strOtherChargeItemAndPrice VARCHAR(4000),
fColorNumberprice FLOAT ----色号单价
)
AS
BEGIN
DECLARE @strFlowCardCode VARCHAR(100)
DECLARE @strInvoiceNo VARCHAR(50)
DECLARE @strCustomerName VARCHAR(100)
DECLARE @dtConsignTime DATETIME
DECLARE @strOrderCode VARCHAR(100)
DECLARE @strFabricName VARCHAR(200)
DECLARE @strAlias VARCHAR(200)
DECLARE @strColorNumber VARCHAR(100)
DECLARE @strColorName VARCHAR(100)
DECLARE @strProcessMainFlowName VARCHAR(100)--加工主流程
DECLARE @fPrice float --总单价
DECLARE @fProcessMoney float --染整金额
DECLARE @strOtherChargeItem VARCHAR(200)--另收费项目
DECLARE @strOtherKindItem VARCHAR(100)--另类收费项目
DECLARE @fProductionSum VARCHAR(8)--加工金额
DECLARE @strSumUnit VARCHAR(16)--金额单位
DECLARE @strWeightUnit VARCHAR(50)--重量单位
DECLARE @strWeightUnit1 VARCHAR(50)--重量单位
DECLARE @fDeliveryRealQty FLOAT --实收数量
DECLARE @fWeight FLOAT--实收重量
DECLARE @fFigMoney FLOAT --定形金额
DECLARE @fFigPrice FLOAT --定形单价
DECLARE @fPackingQty FLOAT --打包数量
DECLARE @strCountUnit NVARCHAR(20)--数量单位
DECLARE @strDetailCustomer NVARCHAR(50)--明细客户
DECLARE @strCustomerOrderCode NVARCHAR(200)
DECLARE @strFabricClass NVARCHAR(50)--布类
DECLARE @strColorLevel NVARCHAR(50)--色级
DECLARE @strFabricCode VARCHAR(50)--产编
DECLARE @uiLevel VARCHAR(50)
DECLARE @fCheckSum FLOAT
DECLARE @fReceivedAmount FLOAT
DECLARE @strNote VARCHAR(150)
DECLARE @strScroll varchar(50)
DECLARE @strOtherChargeItemAndPrice VARCHAR(4000)---零收费项目
DECLARE @fColorNumberprice FLOAT---色号单价

IF (ISNULL(@sCustomer,'')='')
    SET @sCustomer='%'
    
DECLARE r CURSOR FOR
SELECT  strInvoiceNo,dtConsignTime,PCIR.strFlowCardCode,
        WorkFlowCard.strDetailCustomer,WorkFlowCard.strCustomerOrderCode,PCIR.strCustomerName,
        WorkFlowCard.strOrderCode,WorkFlowCard.strFabricName,WorkFlowCard.strColorNumber,
        WorkFlowCard.strColorName,strAlias,
        PCIR.strProcessMainFlowName,PCIR.strOtherChargeItem,PCIR.strOtherKindItem,
        PCIR.strWeightUnit,PCIR.fDeliveryRealQty,PCIR.fWeight,PCIR.fProcessMoney,PCIR.fPrice,
        PCIR.fFigMoney,PCIR.fPackingQty,PCIR.strCountUnit,PCIR.fCheckSum,
        (CASE WHEN uiLevel=0 THEN '未到款' WHEN uiLevel=1 THEN '已到款' WHEN uiLevel=-1 THEN '部分到款' END),fReceivedAmount,
        WorkFlowCard.strFabricClass,WorkFlowCard.strColorLevel,dbo.WorkFlowCard.strFabricCode,PCIR.strScroll,
        ( PCIR.strDetailMemo+(case when PCIR.strDescription<>'' then '|'+PCIR.strDescription else '' end )+case when fFigMoney<>0 then '|定型增减,'+Convert(varchar(20), Round(case when PCIR.fWeight<>0 AND PCIR.strWeightUnit='kg' then fFigMoney/PCIR.fWeight ELSE fFigMoney/(PCIR.fWeight*0.45359237) end,1))+',元/公斤' else '' end ) as strOtherChargeItemAndPrice,
        ROUND(ISNULL(PCIR.fPrice,0),2)/1000.0  as fColorNumberprice
        FROM dbo.ProductionConsignInvoiceReturn PCIR LEFT OUTER JOIN dbo.WorkFlowCard ON
        PCIR.strFlowCardCode=dbo.WorkFlowCard.strFlowCardCode
        WHERE  PCIR.bIsAudit=1 AND PCIR.bIsCancel=0 AND PCIR.fCheckSum<>0
        AND ISNULL(PCIR.strFlowCardCode,'') LIKE '%'+@sFlowCardCode+'%'
        AND ISNULL(strInvoiceNo,'') LIKE '%'+@sInvoiceNo+'%' AND ISNULL(dbo.WorkFlowCard.strFabricCode,'') LIKE '%'+@sFabricCode+'%'  AND ISNULL(PCIR.strCustomerName,'') LIKE @sCustomer
        AND ISNULL(WorkFlowCard.strOrderCode,'') LIKE '%'+@sOrderCode+'%' AND ISNULL(WorkFlowCard.strFabricName,'') LIKE '%'+@sFabricName+'%'
        AND ISNULL(WorkFlowCard.strColorNumber,'') LIKE '%'+@sColorNumber+'%' AND uiLevel LIKE '%'+@bisSample+'%' 
        AND dtConsignTime>=@dDateStart AND dtConsignTime<@dDateEnd
OPEN r      
  FETCH NEXT FROM r INTO @strInvoiceNo,@dtConsignTime,@strFlowCardCode,
        @strDetailCustomer,@strCustomerOrderCode,@strCustomerName,
        @strOrderCode,@strFabricName,@strColorNumber,
        @strColorName,@strAlias,
        @strProcessMainFlowName,@strOtherChargeItem,@strOtherKindItem,
        @strWeightUnit,@fDeliveryRealQty,@fWeight,@fProcessMoney,@fPrice,
        @fFigMoney,@fPackingQty,@strCountUnit,@fCheckSum,
        @uiLevel,@fReceivedAmount,@strFabricClass,@strColorLevel,@strFabricCode,@strScroll,@strOtherChargeItemAndPrice,@fColorNumberprice

WHILE @@FETCH_STATUS=0
BEGIN
IF @strOrderCode IS NULL OR @strOrderCode='' --若是期初入库的情况,无卡号信息则取入库信息
BEGIN
SELECT TOP 1 @strOrderCode=strordercode,@strFabricName=strFabricName,@strColorNumber=strColorNumber,
@strColorName=strColorName,@strAlias=strAlias,@strFabricClass=strFabricClass,@strFabricCode=strFabricCode,
@fDeliveryRealQty=fProductionPieceAmount
FROM dbo.ProductionOutputBillDetail WHERE strflowcardcode=@strFlowCardCode AND strProductionOutputBillCode='CBC'+@strInvoiceNo
END

IF LTRIM(RTRIM(@strWeightUnit))='吨'
BEGIN
SET @fWeight=@fWeight1000
SET @fFigMoney=ROUND(ISNULL(@fFigMoney,0)/1000,1)
IF ISNULL(@fWeight,0)>0
SET @fFigPrice=ROUND(ISNULL(@fFigMoney,0)/(1000
@fWeight),1)
SET @strWeightUnit='元/公斤'
SET @strWeightUnit1='公斤'
END
IF UPPER(LTRIM(RTRIM(@strWeightUnit)))='KG'
BEGIN
SET @strWeightUnit='元/公斤'
IF ISNULL(@fWeight,0)>0
SET @fFigPrice=ROUND(ISNULL(@fFigMoney,0)/@fWeight,1)
SET @strWeightUnit1='公斤'
END
ELSE
IF UPPER(LTRIM(RTRIM(@strWeightUnit)))='磅'
BEGIN
SET @fWeight=@fWeight*0.45359237
SET @strWeightUnit='元/公斤'
IF ISNULL(@fWeight,0)>0
SET @fFigPrice=ROUND(ISNULL(@fFigMoney,0)/@fWeight,1)
SET @strWeightUnit1='公斤'
END
ELSE
BEGIN
SET @strWeightUnit='元/'+@strWeightUnit
SET @strWeightUnit1=@strWeightUnit
END
IF @strCountUnit='元/个' --OR @strOtherChargeItem='纸筒'
SET @fDeliveryRealQty=@fPackingQty
--IF @strCountUnit<>'疋' AND @strCountUnit<>''
--SET @fDeliveryRealQty=@fPackingQty
---获取加工主流程中项目及单价、金额
IF LEN(@strProcessMainFlowName)>0 AND ISNULL(@fPrice,0)>=0 AND ISNULL(@fProcessMoney,0)>=0
BEGIN
INSERT @objReturn
VALUES(@strInvoiceNo,@dtConsignTime,@strFlowCardCode,@strCustomerName,@strDetailCustomer,@strCustomerOrderCode,
@strOrderCode,@strFabricName,@strColorNumber,@strColorName,@fDeliveryRealQty,@fWeight,@strProcessMainFlowName,
ROUND((ISNULL(@fPrice,0)/1000),2),@strWeightUnit,'元',ROUND(ISNULL(@fProcessMoney,0),2),@uiLevel,
@fReceivedAmount,@strAlias,@strCountUnit,@strWeightUnit1,@fCheckSum,@strFabricClass,@strColorLevel,@strFabricCode,'',@strProcessMainFlowName,'','','',
@strOtherChargeItemAndPrice,@fColorNumberprice)
END
-----获取另收费项目及单价、金额 这里面的存储过程

IF LEN(@strOtherChargeItem)>0
BEGIN
INSERT @objReturn
SELECT @strInvoiceNo,@dtConsignTime,@strFlowCardCode,@strCustomerName,@strDetailCustomer,@strCustomerOrderCode,
@strOrderCode,@strFabricName,@strColorNumber,@strColorName,@fDeliveryRealQty,@fWeight,strItem ,
fprice,strpriceUnit,strSumUnit,fSumMoney,@uiLevel,@fReceivedAmount,@strAlias,@strCountUnit,@strWeightUnit1,
@fCheckSum,@strFabricClass,@strColorLevel,@strFabricCode,'','',strItem,CASE WHEN strItem LIKE '%纸筒%' THEN strItem ELSE '' END,CASE WHEN strItem LIKE '%纸筒%' THEN @strScroll ELSE '' END ,
@strOtherChargeItemAndPrice,@fColorNumberprice
FROM [dbo].F_getsDetailTable
END

-----获取另类收费项目及单价、金额
IF LEN(@strOtherKindItem)>0
BEGIN
INSERT @objReturn
SELECT @strInvoiceNo,@dtConsignTime,@strFlowCardCode,@strCustomerName,@strDetailCustomer,@strCustomerOrderCode,
@strOrderCode,@strFabricName,@strColorNumber,@strColorName,@fDeliveryRealQty,@fWeight, strItem ,
fprice,strpriceUnit,strSumUnit,fSumMoney,@uiLevel,@fReceivedAmount,@strAlias,@strCountUnit,@strWeightUnit1,
@fCheckSum,@strFabricClass,@strColorLevel,@strFabricCode,'','',strItem,'',@strScroll,@strOtherChargeItemAndPrice,@fColorNumberprice
FROM [dbo].F_getsDetailTable

END
-----获取另类收费项目及单价、金额
IF ISNULL(@fFigMoney,0)<>0
BEGIN
INSERT @objReturn
VALUES(@strInvoiceNo,@dtConsignTime,@strFlowCardCode,@strCustomerName,@strDetailCustomer,@strCustomerOrderCode,
@strOrderCode,@strFabricName,@strColorNumber,@strColorName,@fDeliveryRealQty,@fWeight,
'定形费增减',@fFigPrice,@strWeightUnit,'元',@fFigMoney,@uiLevel,@fReceivedAmount,@strAlias,
@strCountUnit,@strWeightUnit1,@fCheckSum,@strFabricClass,@strColorLevel,@strFabricCode,'','','定形费增减','','',@strOtherChargeItemAndPrice,@fColorNumberprice)
END

-------若是客退返修则不收费 add by rain 2010/12/17
--IF(SELECT TOP 1 strWorkFlowClass FROM WorkFlowCard WHERE strFlowCardCode=@strFlowCardCode)='客退返修'
--BEGIN
-- INSERT @objReturn
-- VALUES(@strInvoiceNo,@dtConsignTime,@strFlowCardCode,@strCustomerName,@strDetailCustomer,
-- @strOrderCode,@strFabricName,@strColorNumber,@strColorName,@fDeliveryRealQty,@fWeight,
-- '',@fFigPrice,@strWeightUnit,'元',@fFigMoney,'',@fReceivedAmount,@strAlias,
-- @strCountUnit,@strWeightUnit1,@fCheckSum,@strFabricClass,@strColorLevel,@strFabricCode,'客退返修不收费','','','','',@strOtherChargeItemAndPrice,@fColorNumberprice)
--END

FETCH NEXT FROM r INTO @strInvoiceNo,@dtConsignTime,@strFlowCardCode,
@strDetailCustomer,@strCustomerOrderCode,@strCustomerName,
@strOrderCode,@strFabricName,@strColorNumber,
@strColorName,@strAlias,
@strProcessMainFlowName,@strOtherChargeItem,@strOtherKindItem,
@strWeightUnit,@fDeliveryRealQty,@fWeight,@fProcessMoney,@fPrice,
@fFigMoney,@fPackingQty,@strCountUnit,@fCheckSum,
@uiLevel,@fReceivedAmount,@strFabricClass,@strColorLevel,@strFabricCode,@strScroll,@strOtherChargeItemAndPrice,@fColorNumberprice
END
CLOSE r
DEALLOCATE r
RETURN
END

夕阳下的你我的主页 夕阳下的你我 | 初学一级 | 园豆:114
提问于:2019-11-20 18:15
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册