现存量查时所用到的SQL语句是怎样的？

sky11726

现存量查时所用到的SQL语句是怎样的？？关系到那几个表？现存量中的待发货数量是怎样的SQL语句查询到的？求助，在线等。

[ 本帖最后由 stonys 于 2008-6-2 16:46 编辑 ]

zxcong

我是来混积分的。。。。。

霏霏露

怎样可以看到呀

vaneorin

进来学习下

yimi200

SQL语句:
SELECT '002' as 帐套, W.cWhCode, W.cWhName, I.cInvCode, I.cInvAddCode,  I.cInvName, I.cInvStd, I.cInvCCode , IC.cInvCName,

CU_M.cComUnitName AS cInvM_Unit, CASE WHEN I.iGroupType = 0 THEN NULL  WHEN I.iGrouptype = 2 THEN CU_A.cComUnitName  WHEN I.iGrouptype = 1 THEN CU_G.cComUnitName END  AS cInvA_Unit,CASE WHEN I.iGroupType = 0 THEN NULL      WHEN I.iGroupType = 2 THEN (CASE WHEN CS.iQuantity = 0.0 OR CS.iNum = 0.0 THEN NULL ELSE CS.iQuantity/CS.iNum END)      WHEN I.iGroupType = 1 THEN CU_G.iChangRate END AS iExchRate,
i.cInvDefine1,i.cInvDefine2,i.cInvDefine3,i.cInvDefine4,i.cInvDefine5,i.cInvDefine6,i.cInvDefine7,i.cInvDefine8,CS.cFree1,CS.cFree2,CS.cFree3, Null as cFree4, Null as cFree5, Null as cFree6, Null as cFree7, Null as cFree8, Null as cFree9, Null as cFree10, Null as cInvDefine9, Null as cInvDefine10, Null as cInvDefine11, Null as cInvDefine12, Null as cInvDefine13, Null as cInvDefine14, Null as cInvDefine15, Null as cInvDefine16,cs.cBatch, cs.EnumName As iSoTypeName, cs.csocode as SOCode, convert(nvarchar,cs.isoseq) as iRowNo,
cs.cvmivencode,v1.cvenabbname as cvmivenname , isnull(E.enumname,N'') as cMassUnitName,CS.dVDate, CS.dMdate,CS.iMassDate,
(iQuantity) AS iQtty,( CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN ISNULL(iNum,0) WHEN iGroupType = 1 THEN iQuantity/ CU_G.iChangRate END) AS iNum,
  CASE WHEN CS.bStopFlag = 1 OR CS.bGspStop = 1 THEN iQuantity ELSE IsNull(fStopQuantity,0) END AS iStopQtty,
  CASE WHEN CS.bStopFlag = 1 OR CS.bGspStop = 1 THEN (CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN ISNULL(iNum,0) WHEN iGroupType = 1 THEN iQuantity/ CU_G.iChangRate END)
ELSE (CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN ISNULL(fStopNum,0) WHEN iGroupType = 1 THEN fStopQuantity/ CU_G.iChangRate END) END AS iStopNum,
(fInQuantity) AS fInQtty,
(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fInNum,0) WHEN iGroupType = 1 THEN fInQuantity/ CU_G.iChangRate END) AS fInNum,
(fTransInQuantity) AS fTransInQtty,
(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fTransInNum,0) WHEN iGroupType = 1 THEN fTransInQuantity/ CU_G.iChangRate END) AS fTransInNum,
(ISNULL(fInQuantity,0) + ISNULL(fTransInQuantity,0)) AS fInQttySum,
(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fInNum,0) + ISNULL(fTransInNum,0) WHEN iGroupType = 1 THEN (ISNULL(fInQuantity,0) + ISNULL(fTransInNum,0))/ CU_G.iChangRate END) AS fInNumSum,
(fOutQuantity) AS fOutQtty,
(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fOutNum,0) WHEN iGroupType = 1 THEN fOutQuantity/ CU_G.iChangRate END) AS fOutNum,
(fTransOutQuantity) AS fTransOutQtty,
(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fTransOutNum,0) WHEN iGroupType = 1 THEN fTransOutQuantity/ CU_G.iChangRate END) AS fTransOutNum,
(ISNULL(fOutQuantity,0) + ISNULL(fTransOutQuantity,0)) AS fOutQttySum ,
(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fOutNum,0) + ISNULL(fTransOutNum,0) WHEN iGroupType = 1 THEN (ISNULL(fOutQuantity,0) + ISNULL(fTransOutNum,0))/ CU_G.iChangRate END) AS fOutNumSum,
(fDisableQuantity) AS fDisableQtty,
(CASE WHEN iGroupType = 0 THEN NULL WHEN iGroupType=2 THEN ISNULL(fDisableNum,0) WHEN iGroupType = 1 THEN fDisableQuantity/ CU_G.iChangRate END) AS fDisableNum,
(CASE WHEN bInvBatch=1 THEN  CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0) - IsNull(fStopQuantity,0) END  + ISNULL(fInQuantity,0) - ISNULL(fOutQuantity,0) ELSE  CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0) - IsNull(fStopQuantity,0) END  + ISNULL(fInQuantity,0) - ISNULL(fOutQuantity,0) END) AS fAvailQtty,
(CASE WHEN iGroupType = 0 THEN 0  WHEN iGroupType = 2 THEN  CASE WHEN bInvBatch=1 THEN  CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iNum,0) - IsNull(fStopNum,0) END  + ISNULL(fInNum,0) - ISNULL(fOutNum,0) ELSE  CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iNum,0) - IsNull(fStopNum,0) END  + ISNULL(fInNum,0) - ISNULL(fOutNum,0) END WHEN iGroupType = 1 THEN  (CASE WHEN bInvBatch=1 THEN  CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0) - IsNull(fStopQuantity,0) END  + ISNULL(fInQuantity,0) - ISNULL(fOutQuantity,0) ELSE  CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0) - IsNull(fStopQuantity,0) END  + ISNULL(fInQuantity,0) - ISNULL(fOutQuantity,0) END)/CU_G.iChangRate ELSE NULL END) AS fAvailNum
-- INTO tempdb..TMPUF_171085006_heron525795190_WZUF
FROM v_CurrentStock CS inner join ufdata_002_2008..Inventory I ON I.cInvCode = CS.cInvCode   
left join ufdata_002_2008..InventoryClass IC ON IC.cInvCCode = I.cInvCCode LEFT OUTER JOIN ufdata_002_2008..ComputationUnit CU_G ON
I.cSTComUnitCode =CU_G.cComUnitCode
LEFT OUTER JOIN ufdata_002_2008..ComputationUnit CU_A ON I.cAssComUnitCode = CU_A.cComunitCode
LEFT OUTER JOIN ufdata_002_2008..ComputationUnit CU_M ON I.cComUnitCode = CU_M.cComunitCode
LEFT OUTER JOIN ufdata_002_2008..Warehouse W ON CS.cWhCode = W.cWhCode
left join  ufdata_002_2008..vendor v1 on v1.cvencode = cs.cvmivencode
LEFT OUTER JOIN ufdata_002_2008..v_aa_enum E with (nolock) on E.enumcode=convert(nchar,CS.cMassUnit) and E.enumType=N'ST.MassUnit'

sky11726

待发货数量是那一项？

sky11726

这一句太复杂了，可否分解一下？

断壁残阳

CurrentStock表的
foutquantity
字段
就是待发数量

sky11726

非常感谢！

绿萝

先收藏，再慢慢研究

hongling1122

收藏一下，估计以后我也会用到的；

lg0219

一米兄弟真是强啊。
特别说明：一米兄弟是开发高手，有个性化开发方面的需求找他没错。

wanyaxiong

二楼真牛！

wanyaxiong

楼主为什么要在SQL里查询呢？在用友前台不是可以查询吗？
呵呵

dyliht

高手就是高手，整的东西都看不懂啊  
呵呵

sky11726

发现前台查询出错了。待发数量出错，引起了可用数量也错了。但现存量没有错。所以相搞清楚待发数量的来源。

yygy2008

太多了，看着晕

dlzcswer

好东西 研究研究