Guanjiapo ERP V3 II generate the SQL SELECT query statement introduced by Kingdee financial software K3 v10.3 standard credential

Source: Internet
Author: User

Note: The double and enclosed variables must be constant before query execution, for example, & previous day &

Select a. billdate as fdate, max (Year (A. billdate) as fyear,
Max (month (A. billdate) as fperiod, 'record' as fgroupid, '1' as fnumber,
'2014. 02 'As faccountnum, 'waste paper goods (Project Accounting)' As faccountname,
'RMB' as fcurrencynum, 'RMB' as fcurrencyname, sum (A. Total)
As famountfor, sum (A. Total) as fdebit, 0 as fcredit, 'tong Meihua 'As fpreparerid,
'None' as fcheckerid, 'none' as fapproveid, 'none' as fcashierid,
''As fhandler, '*' As fsettletypeid, ''as fsettleno,
Max (I. billcode + ', quantity:' + ltrim (STR (A. qty, 15, 3 ))
+ 'Ton; '+ I. Comment +'; '+ ltrim (I. Explain) as fexplanation, 0 as fquantity,

'*' As fmeasureunitid, 0 as funitprice, ''as freference, max (A. billdate)
As ftransdate, ''as ftransno, 0 as fattachments, 999 as fserialnum,
''As fobjectname, ''as fparameter, 1 as fexchangerate, 0 as fentryid,
Max ('department --- '+ Left (K. usercode, 6) +' --- '+ Left (K. fullname, 5)
+ '| Location ---' + right (ltrim (K. usercode), 2) + '---' + right (ltrim (K. fullname), 3 ))

As fitem, 0 as fposted, ''as finternalind,'' as fcashflow, K. usercode as expr1,

Max (K. fullname) as expr2, B. usercode, max (B. fullname) as expr3, I. billcode
From inoutstocktable a inner join
Btype B on B. typeid = A. btypeid inner join
Stock K on K. typeid = A. ktypeid inner join
Billindex I on I. billnumberid = A. billnumberid
Where (A. billtype = 34) or (A. billtype = 6) and (A. billdate = '& previous day &') and (I. redword = 0)
Group by A. billdate, K. usercode, B. usercode, I. billcode
Union all
Select a. billdate as fdate, max (Year (A. billdate) as fyear,
Max (month (A. billdate) as fperiod, 'record' as fgroupid, '1' as fnumber,
'2014. 02 'As faccountnum, 'waste paper payable' As faccountname,
'RMB' as fcurrencynum, 'RMB' as fcurrencyname, sum (A. Total) as famountfor,
0 as fdebit, sum (A. Total) as fcredit, 'tong Meihua 'As fpreparerid,
'None' as fcheckerid, 'none' as fapproveid, 'none' as fcashierid,
''As fhandler, '*' As fsettletypeid, ''as fsettleno,
Max (I. billcode + ', quantity:' + ltrim (STR (A. qty, 15, 3 ))
+ 'Ton; '+ I. Comment +'; '+ ltrim (I. Explain) as fexplanation, 0 as fquantity,

'*' As fmeasureunitid, 0 as funitprice, ''as freference, max (A. billdate)
As ftransdate, ''as ftransno, 0 as fattachments, 999 as fserialnum,
''As fobjectname, ''as fparameter, 1 as fexchangerate, 0 as fentryid,
Max ('department --- '+ Left (K. usercode, 6) +' --- '+ Left (K. fullname, 5)
+ '| Supplier ---' + ltrim (B. usercode) + '---' + ltrim (B. fullname) as fitem,
0 as fposted, ''as finternalind,'' as fcashflow, K. usercode as expr1,
Max (K. fullname) as expr2, B. usercode, max (B. fullname) as expr3, I. billcode
From inoutstocktable a inner join
Btype B on B. typeid = A. btypeid inner join
Stock K on K. typeid = A. ktypeid inner join
Billindex I on I. billnumberid = A. billnumberid
Where (A. billtype = 34) or (A. billtype = 6) and (A. billdate = '& previous day &') and (I. redword = 0)
Group by A. billdate, K. usercode, B. usercode, I. billcode
Order by A. billdate, K. usercode, B. usercode, I. billcode

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.