// input details SELECT PARTSINPUTBODY.BILLCODE, PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE, PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE, PARTSINPUTHAND.TOTALSUM, PARTSINPUTBODY.QUANTITYFROM PARTSINPUTHAND INNER JOIN PARTSINPUTBODY ON PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODEWHERE (PARTSINPUTBODY.PARTCODE LIKE ' 01002% ') and (PartsinputHand.Billdate Between' 2004-12-01 'and' 2004-12-31 ')
// output details SELECT PARTSOUTPUTHAND.BILLCODE, PARTSOUTPUTHAND.CLIENTNAME, PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE, PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTBODY.TOTALSUMFROM PARTSOUTPUTBODY INNER JOIN PARTSOUTPUTHAND ON PARTSOUTPUTBODY.BILLCODE = PARTSOUTPUTHAND.BILLCODE // ******** ************ // subtotal amount of a commodity INPUTSELECT sUM (PARTSINPUTBODY.TOTALSUM) AS Expr1, PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODEFROM PARTSINPUTBODY CROSS JOIN PARTSINPUTHANDWHERE (PARTSINPUTBODY.PARTCODE LIKE '01% ' ) Group by partsinputbody.partName, PartsInputBody.Partcode, PARTSINPUTBODY.PARTCODE
// amount of a commodity subtotal (INPUT) SELECT SUM (E) AS Expr1FROM (SELECT SUM (PARTSINPUTBODY.TOTALSUM) AS E, PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE FROM PARTSINPUTBODY CROSS JOIN PARTSINPUTHAND WHERE (PARTSINPUTBODY.PARTCODE LIKE '01 % ') Group by partsinputbody.partname, partsinputbody.partcode) derivedtbl // ***************************** / / small the amount of a commodity meter (OUTPUT) SELECT sUM (PARTSOUTPUTBODY.TOTALSUM) AS Expr1, PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODEFROM PARTSOUTPUTBODY CROSS JOIN PARTSOUTPUTHANDwhere PARTSOUTPUTBODY.PARTCODE like '01% 'GROUP BY PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE // Subtotal the amount of a commodity (OUTPUT) SELECT sUM (E) AS Expr1FROM (SELECT sUM (PARTSINPUTBODY.TOTALSUM) AS E, PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE FROM PARTSINPUTBODY CROSS JOIN PARTSINPUTHAND WHERE (PARTSINPUTBODY.PARTCODE LIKE '01% ') GROUP By PartsInputBody.PartName, Parts InputBody.Partcode) DerivedTBL
// ******************************************************** ****
WHERE (ONLYID NOT IN (SELECT Onlyid from Customer)
Private {private declarations} // fid: string; public {public declarations} proteness;
CREATE PROCEDURE sp_operator @name varchar (30), @Department varchar (10) AS select * from operator where name = @ name and Department=@DepartmentParameters.CreateParameter (Name, DataType, Direction, Size, Value)
TMPSP.Parameters.createParameter ('@ JGFL', FTString, PDINPUT, 12, EditText); Par.Value
PreexecProc
// paramcheck: = false; parameters.clear; //parameters.createParameter('@bgntime', pddate, pdinput, 10, new); //parameters.createParameter ('@endtime' ,ftdate, pdinput, 10, now) WITH MYDM.ADOsp Do Begin ProcedureName: = 'sp_spaceused'; // Set the stored procedure name close; // Close the previous data set parameters.clear; // Empty the previous parameter parameters.createParameter ('@ Objname', ftstring, PDINPUT, 100, 'TABLE1'; / / Generate new parameters open; // or execproc, do not return the data set END;
Error: Parameter unable to get parameter information in front of the query statement before the preparation command Function TfrmQueryx.getinfoname (ID: String): string; VartmpQuery: tadoquery; st: string; begin tmpquery: = tadoquery.create (self) ; with TmpQuery do begin close; ConnectionString: = OceanQueryXpartsinput.ConnectionString; sql.Clear; sql.Add ( 'select name from partinfo where code =' QuotedStr (FId)); open; // exe error sT: = fieldbyname ( ' Name '). Value; Free; End; Result: = st;
Inventory statistics list number project name last month, this month, this month, this month, this month, this month
Survey last month = last month stock - last month's acquisition [different] This month stock = this month is in the library - this month
Suppose it is the product name of the December, which is a last month, it should be the launch of November SELECT PARTINPUTBODY.QUANTITY
SELECT PARTSINPUTBODY.PARTNAME AS trade names, PARTSINPUTBODY.PARTCODE AS commodity code, PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE, PARTSINPUTHAND.TOTALSUM, PARTSINPUTBODY.QUANTITY AS November warehousing amount FROM PARTSINPUTHAND INNER JOIN PARTSINPUTBODY ON PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODEWHERE (PartsInputHand.Billdate Between '2004-12-01' and '2004-12-31')
SELECT PARTSOUTPUTBODY.PARTNAME AS trade name, PARTSOUTPUTBODY.PARTCODE AS commodity code, PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTHAND.TOTALSUM, PARTSOUTPUTBODY.QUANTITY AS November collar amount, PARTSOUTPUTHAND.CLIENTNAMEFROM PARTSOUTPUTHAND INNER JOIN PARTSOUTPUTBODY ON PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODEWHERE (PARTSOUTPUTHAND .Billdate Between '2004-12-01' and '2004-12-31') Select View1. November into the warehouse - View2. November acquisition amount AS November Survival, View2. November acquisition, View1. November Inner Join View2 on View1. November into the warehouse> View2. November acquisition
// *************** SELECT A. Introduction, b. Acquisition, AS, B. Used, a. In stock from (Select PartsInputBody.PartName, PARTSINPUTBODY.PARTCODE, PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE, PARTSINPUTHAND.TOTALSUM, PARTSINPUTBODY.QUANTITY AS storage amount FROM PARTSINPUTHAND INNER JOIN PARTSINPUTBODY ON PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE WHERE (PARTSINPUTHAND.BILLDATE BETWEEN @MonthDateBegin AND @MonthDateEnd)) a CROSS JOIN (SELECT PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE, PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTHAND.TOTALSUM, PARTSOUTPUTBODY.QUANTITY AS collar amount, PARTSOUTPUTHAND.CLIENTNAME FROM PARTSOUTPUTHAND INNER JOIN PARTSOUTPUTBODY ON PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODE WHERE (PARTSINPUTHAND.BILLDATE BETWEEN @ MonthdateBegin and @Monthdateend) BWHE Re (a.partcode = '01001')
A commodity or a kind of merchandise month statistics contains Select a. In the warehouse - b. Acquisition amount AS Survey this month, b. Consultation, a. Into the warehouse from (Select PartsInputBody.PartName, PartsInputBody.Partcode, PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE, PARTSINPUTHAND.TOTALSUM, PARTSINPUTBODY.QUANTITY AS storage amount FROM PARTSINPUTHAND INNER JOIN PARTSINPUTBODY ON PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE WHERE (PARTSINPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12 -31 ')) a INNER JOIN (SELECT PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE, PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTHAND.TOTALSUM, PARTSOUTPUTBODY.QUANTITY AS collar amount, PARTSOUTPUTHAND.CLIENTNAME FROM PARTSOUTPUTHAND INNER JOIN PARTSOUTPUTBODY ON PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODE WHERE (PartSoutPutHand.Billdate Between '2004-12-01' and '2004-12-31')) b on a. Introduction> B. Acquisition amount where (a.partcode = '01001' ) // A commodity or a kind of goods month statistics
Select A.PartName, Sum (a. Introduction - B. Consultation) AS Survey this month, SUM (B. Consultation) AS This month, SUM (a. Into the warehouse) AS this month FROM (SELECT PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE, PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE, PARTSINPUTHAND.TOTALSUM, PARTSINPUTBODY.QUANTITY AS storage amount FROM PARTSINPUTHAND INNER JOIN PARTSINPUTBODY ON PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE WHERE (PARTSINPUTHAND.BILLDATE BETWEEN ' 2004-12-01 'AND' 2004-12-31 ')) a INNER JOIN (SELECT PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE, PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTHAND.TOTALSUM, PARTSOUTPUTBODY.QUANTITY AS collar amount, PARTSOUTPUTHAND.CLIENTNAME FROM PARTSOUTPUTHAND INNER Join PartsOutputBody on partSoutputHand.Billcode = partSoutputBody.billcode where (PartSoutputHand.billdate Between 2004-12-01 'and' 2004-12-31 ')) B on a. Introduction> B The amount where (A.Partcode Like '01001%) Group by a.partname // ************** Reference FastReport Designer Usesfr_class designer Usesfr_class, fr_dset, fr_dbset, db, dbtables, FR_CHART, FR_DESGN; .frreport1.designReport;
// current to obtain approval of a commodity, but no storage SELECT PARTCODE, PARTNAMEFROM dbo.STOCKBILLBODYWHERE (PARTCODE NOT IN (SELECT partcode FROM partsinputbody INNER JOIN partsinputhand ON partsinputbody.billcode = partsinputhand.billcode WHERE partsinputhand.billdate BETWEEN '2004-12-01' AND '2004-12-31')) // current to obtain approval of a stock of goods / no no stock recipients SELECT PARTCODE, PARTNAMEFROM dbo.STOCKBILLBODYWHERE (PARTCODE nOT IN (SELECT partcode FROM partsoutputbody INNER JOIN partsoutputhand ON partsoutputbody.billcode = partsoutputhand .Billcode where partshputhand.billdate between '2004-12-01' and '2004-12-31')) // (_'- 0-090000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 --- To create a stored procedure to enter data to the data table, The code writing is, for example,:
CREATE PROC ProcTest1 @inttest smallint, @ strtest char (4) output AS INSERT INTO TEST_User.TRANS_TEST valueS (@ inttest, @ strtest) SELECT @ strtest, * FROM TEST_User.TRANS_TEST ---- above code added to the data table TEST_User.TRANS_TEST Data, the data table includes two fields: the first field is the data of the smallint type, the parameter type is set to input (can be defined in the stored procedure); the second field data is a character type, parameter type, parameter type For Output.
---- The writing of the module program is similar to the DataInsert process, which is mainly due to the middle part of the module. The main code corresponding to the following:
StoredProc1.close; {Close Store Procedure} StoredProc1.Params [1] .ParamType: = Ptinput; {Settings Parameter Types of Stored Procedures} StoredProc1.Params [1] .asinteger: = 1; {Settings Data during Storage Process} StoredProc1.Params [2] .ParamType: = ptoutput; storedproc1.params [2] .sstring: = 'abcd'; storedproc1.open; {Open Store Procedure} // 0-9-090
