当视图封装基础资料时，如何处理FID生成？

已有 1 条回答

深圳周少斌

题主，您好！

 

将你提供的源视图修改了一下，也就是修改以下红字的部分，你试试，应该可以了。

说明：你的问题，利用SQL的开窗函数特性就能解决：开窗函数详解

 

SELECT   

FID=1000000+ROW_NUMBER()

    OVER(ORDER BY B.FLOTID ASC, C.FTIAOMA ASC, C.FUSEORGID ASC,C.FSTOREUNITID ASC,C.FAUXUNITID ASC,

                  C.FCARTONNUMBER ASC,(CASE WHEN C.FBATCHANDCARTONNUMBER IS NULL THEN a.FBATCHANDCARTONNUMBER ELSE c.FBATCHANDCARTONNUMBER END) ASC,

  C.FAREAMM ASC,C.FSQUAREFEET ASC,(CASE WHEN C.FMODELX IS NULL THEN a.FMODELX ELSE c.FMODELX END) ASC,

  (CASE WHEN C.FFUKUAN = 0 THEN a.FFWIDTH ELSE c.FFUKUAN END) ASC,C.FLENGTH ASC,C.FCUSMATERIALNUMBER ASC, C.FCUSPURNUMBER ASC),

                a.FSTOCKORGID, B.FLOTID AS OldFID, D.FISINVENTORY, C.FTIAOMA AS FNUMBER, E.FMATERIALID, C.FUSEORGID, 

                a.FBASEUNITID, C.FSTOREUNITID AS FStockUnitId, C.FAUXUNITID AS FSecUnitId, a.FSTOCKID, a.FLOT, 

                B.FNUMBER AS Flot_TXT, C.FTIAOMA, a.FBASEQTY AS FSUTTLE, a.FSECQTY AS FAUXQTY, 

                C.FCARTONNUMBER AS FXIANGHAO, CASE WHEN C.FBATCHANDCARTONNUMBER IS NULL 

                THEN a.FBATCHANDCARTONNUMBER ELSE c.FBATCHANDCARTONNUMBER END AS FBATCHANDCARTONNUMBER, 

                C.FAREAMM, C.FSQUAREFEET, a.FSTOCKLOCID, 'A' AS FFORBIDSTATUS, 'C' AS FDOCUMENTSTATUS, 

                CASE WHEN C.FMODELX IS NULL THEN a.FMODELX ELSE c.FMODELX END AS FMODELX, 

                CASE WHEN C.FFUKUAN = 0 THEN a.FFWIDTH ELSE c.FFUKUAN END AS FFUKUAN, a.FPRODUCEDATE, 

                a.FEXPIRYDATE, C.FLENGTH, C.FCUSMATERIALNUMBER, C.FCUSPURNUMBER

FROM      dbo.T_STK_INVENTORY AS a INNER JOIN

                dbo.T_BD_LOTMASTER AS B ON a.FLOT = B.FLOTID LEFT OUTER JOIN

                dbo.T_Cust_ZJTMSC AS C ON B.FNUMBER = C.FPICI INNER JOIN

                dbo.T_BD_MATERIALBASE AS D ON a.FMATERIALID = D.FMATERIALID INNER JOIN

                dbo.T_BD_MATERIAL AS E ON a.FMATERIALID = E.FMASTERID INNER JOIN

                dbo.T_BD_MATERIAL_L AS F ON E.FMASTERID = F.FMATERIALID

WHERE   (a.FSTOCKID > 0) AND (a.FBASEQTY > 0.0) AND (a.FSTOCKORGID = '901376') AND (F.FNAME LIKE '%纸') AND 

                (D.FISPRODUCE = '1') AND (E.FUSEORGID = '901376') AND (a.FLOT <> '')

 

相关问答

基础资料导入问题

已有 1 个回答　|　1年前

金蝶基础资料基本计量单位不自动带入

已有 1 个回答　|　1年前

陈列费抵货款如何处理

已有 1 个回答　|　1年前

应付账款长期挂着数，实际不需要支付了，这样怎么处理比较好？

已有 1 个回答　|　1年前

资本化支出可以加计扣除吗？税务处理全解析

已有 3 个回答　|　6月前

最新文章查看更多