Prev: Having divide by zero error
Next: Best way to persist alter table changes in my local DB to web host
From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 19 Jan 2010 07:52
Hi Gert-Jan,

I Thank you very much for your reply & suggestion.

Yes, I know that Only UPDATE statement can do this. But did you know that
UPDATE Statement will not update one record month than once in one UPDATE
statement ?

Means if Transaction Table (FaItemLedger) is having more than one record of
master then it will update only first record. (i.e. In transaction there
more than one record for item code 101 then it will update only first
record.)

Sanjay Shah

"Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message
news:4B51B3C1.CA367EC3(a)xs4all.nl...
> Sanjay,
>
> There are three things I'd like to note. Feel free to ignore whatever
> doesn't help you:
>
> 1. You are using an unsafe date format in the cursor definition. The
> interpretation of ('04/01/2003') depends entirely on the connection
> settings at the time you run the query.
>
> 2. You might consider specifying the cursor with as FAST_FORWARD, since
> it is not your intension to change the table that is used in the cursor,
> and you only navigate the cursor with FETCH NEXT.
>
> 3. There is no need for a cursor. You can write one UPDATE statement to
> do all this.
>
> --
> Gert-Jan
>
>
>
> "SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." wrote:
>>
>> Dear Sir,
>>
>> I am using SQL Server 2005, Visual Basic 6 (SP6) & Microsoft ActiveX Data
>> Object 2.5.
>>
>> If I run following query through SQL Server 2005 Management Studio
>> through
>> QUERY only then it works FINE. But if I run from application using
>> Execute
>> method of Command Object of ADO then it process maximum 49 records in
>> CURSOR
>> and query completes without any errors.
>>
>> I had checked by inserting records in a Temporary Table. If I do Not Use
>> UPDATE statement then it INSERT maximum 65 records in Table and if I use
>> UPDATE statement then it INSERT & UPDATE maximum 49 records.
>>
>> PLEASE NOTE I DON'T WANT TO USE STORED PROCEDURE.
>>
>> Please reply me after proper reading of this query.
>>
>> Sanjay Shah
>>
>> --- Declare Scalar Variables
>> DECLARE @cDocumentType AS nVarChar(3), @dDocumentDate AS DATETIME,
>> @cEntryType AS nVarChar(3), @cStoreCode AS nVarChar(6), @cItemCode AS
>> nVarChar(15), @nItemSno AS INT, @cValuation AS nVarChar(1), @cColorCode
>> AS
>> nVarChar(4), @cSizeCode AS nVarChar(4), @cBatchSerialNo AS nVarChar(15),
>> @nRDocumentYear AS INT, @cRBranchCode AS nVarChar(3), @cRDocumentType AS
>> nVarChar(3), @cRSeriesCode AS nVarChar(4), @cRDocumentNo AS nVarChar(6),
>> @nRItemSno AS INT, @nQuantity AS FLOAT, @nQuantity1 AS FLOAT, @nRate AS
>> FLOAT, @nPer AS INT, @nAmount AS FLOAT, @cTaxCode AS nVarChar(3),
>> @nTaxableAmount AS FLOAT, @nTaxAmount AS FLOAT;
>>
>> --- Define Cursor with Transaction Ledger
>> DECLARE Ledger_Cursor CURSOR FOR
>> SELECT DocumentType, DocumentDate, EntryType, StoreCode, ItemCode,
>> ItemSno,
>> Valuation, ColorCode, SizeCode, BatchSerialNo, RDocumentYear,
>> RBranchCode,
>> RDocumentType, RSeriesCode, RDocumentNo, RItemSno, Quantity, Quantity1,
>> Rate, Per, Amount, TaxCode, TaxableAmount, TaxAmount FROM FaItemLedger
>> WHERE
>> DocumentDate >= ('04/01/2003') AND DocumentDate <= ('04/30/2003') ORDER
>> BY
>> DocumentDate, CASE WHEN Quantity > 0 OR Quantity1 > 0 OR Amount > 0 THEN
>> 1
>> ELSE 2 END ;
>>
>> --- Open Cursor Rows
>> OPEN Ledger_Cursor;
>>
>> --- Get Values of Cursor Columns into Scalar Variables
>> FETCH NEXT FROM Ledger_Cursor INTO
>> @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, @cItemCode,
>> @nItemSno, @cValuation, @cColorCode, @cSizeCode, @cBatchSerialNo,
>> @nRDocumentYear, @cRBranchCode, @cRDocumentType, @cRSeriesCode,
>> @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate, @nPer,
>> @nAmount,
>> @cTaxCode, @nTaxableAmount, @nTaxAmount;
>>
>> -- Perform till Fatch Status is 0 means (Not EOF)
>> WHILE @@FETCH_STATUS = 0
>> BEGIN
>> --- Initialise RDocumentNo if Valuation is Not Bill Wise
>> IF @cValuation NOT IN ('B','P')
>> BEGIN
>> SET @nRDocumentYear = 0;
>> SET @cRBranchCode = '';
>> SET @cRDocumentType = '';
>> SET @cRSeriesCode = '';
>> SET @cRDocumentNo = '';
>> SET @nRItemSno = 0;
>> END
>>
>> --- Update Stock Master with Values of Scalar Variables
>> UPDATE FaStockMaster SET
>> FaStockMaster.StockQuantity =
>> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) ,
>> FaStockMaster.StockQuantity1 =
>> Round(FaStockMaster.StockQuantity1 + @nQuantity1 * 1, 3) ,
>> FaStockMaster.Used = 'True',
>> FaStockMaster.Month1TotalQuantity =
>> ROUND(FaStockMaster.Month1TotalQuantity + @nQuantity * 1, 3),
>> FaStockMaster.Month1TotalQuantity1 =
>> ROUND(FaStockMaster.Month1TotalQuantity1 + @nQuantity1 * 1, 3),
>> FaStockMaster.Rate = CASE WHEN
>> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) > 0 AND CASE
>> WHEN @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity >
>> 0
>> AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue +
>> @nAmount
>> * 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)
>> *
>> CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity
>> >
>> 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity >
>> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per
>> END
>> , 2) END > 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity >
>> 0
>> AND @cValuation IN ('W','F') THEN Round(CASE WHEN @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity >
>> 0
>> AND @cVa
>> luation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount * 1,
>> 2)
>> ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) * CASE
>> WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity
>> >
>> 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity >
>> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per
>> END
>> , 2) END / Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) * CASE
>> WHEN @cValuation IN ('B','P') AND @nQuantity > 0 AND
>> FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END ,
>> 6)
>> WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity
>> >
>> 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN Round(@nRate
>> /
>> CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
>> FaStockMaster.Per END * FaStockMaster.Per, 6) ELSE FaStockMaster.Rate
>> END ,
>> FaStockMaster.Per = CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
>> FaStockMaster.Per END ,
>> FaStockMaster.StockValue = CASE WHEN @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity >
>> 0
>> AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue +
>> @nAmount
>> * 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3)
>> *
>> CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity
>> >
>> 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity >
>> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per
>> END
>> , 2) END,
>> FaStockMaster.TaxCode = CASE WHEN @cValuation IN ('B','P')
>> AND @nQuantity > 0 THEN @cTaxCode ELSE FaStockMaster.TaxCode END ,
>> FaStockMaster.TaxableAmount = CASE WHEN @cValuation IN
>> ('B','P') AND @nQuantity > 0 THEN @nTaxableAmount ELSE
>> FaStockMaster.TaxableAmount END ,
>> FaStockMaster.TaxAmount = CASE WHEN @cValuation IN
>> ('B','P')
>> AND @nQuantity > 0 THEN @nTaxAmount ELSE FaStockMaster.TaxAmount END ,
>> FaStockMaster.LastReceiptDate = CASE WHEN @cDocumentType
>> IN
>> ('GIN','PUR','CPU','ISU','PRE') AND @cEntryType <> 'GIR' AND @nQuantity >
>> 0
>> AND @dDocumentDate > FaStockMaster.LastReceiptDate THEN @dDocumentDate
>> ELSE
>> FaStockMaster.LastReceiptDate END
>> WHERE
>> FaStockMaster.ItemCode = @cItemCode AND
>> FaStockMaster.StoreCode = @cStoreCode AND
>> FaStockMaster.ColorCode = @cColorCode AND
>> FaStockMaster.SizeCode = @cSizeCode AND
>> FaStockMaster.BatchSerialNo = @cBatchSerialNo AND
>> FaStockMaster.DocumentYear = @nRDocumentYear AND
>> FaStockMaster.BranchCode = @cRBranchCode AND
>> FaStockMaster.DocumentType = @cRDocumentType AND
>> FaStockMaster.SeriesCode = @cRSeriesCode AND
>> FaStockMaster.DocumentNo = @cRDocumentNo AND
>> FaStockMaster.ItemSno = @nRItemSno ;
>>
>> --- Get Next Values of Cursor Columns into Scalar Variables
>> FETCH NEXT FROM Ledger_Cursor INTO
>> @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode,
>> @cItemCode, @nItemSno, @cValuation, @cColorCode, @cSizeCode,
>> @cBatchSerialNo, @nRDocumentYear, @cRBranchCode, @cRDocumentType,
>> @cRSeriesCode, @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1,
>> @nRate,
>> @nPer, @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
>> END
>>
>> --- Close Cursor
>> CLOSE Ledger_Cursor;
>>
>> --- Release Cursor
>> DEALLOCATE Ledger_Cursor;
>>
>> --- *** End of Procedure


From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 19 Jan 2010 07:53
Hi Fred,

Which property ?

Sanjay Shah

"Fred" <foleide(a)free.fr.invalid> wrote in message
news:eOUS5sqlKHA.2132(a)TK2MSFTNGP05.phx.gbl...
> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> a �crit dans le
> message de groupe de discussion : 4B51B3C1.CA367EC3(a)xs4all.nl...
>> Sanjay,
>>
>> There are three things I'd like to note. Feel free to ignore whatever
>> doesn't help you:
>>
>> 1. You are using an unsafe date format in the cursor definition. The
>> interpretation of ('04/01/2003') depends entirely on the connection
>> settings at the time you run the query.
>>
>> 2. You might consider specifying the cursor with as FAST_FORWARD, since
>> it is not your intension to change the table that is used in the cursor,
>> and you only navigate the cursor with FETCH NEXT.
>>
>> 3. There is no need for a cursor. You can write one UPDATE statement to
>> do all this.
>
> And 4 : Perhaps a time out on VB code side. Check the command object
> properties.
>
> --
> Fred
> foleide(a)free.fr


From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 19 Jan 2010 07:55
Hi Dan,

I tried all parameters of Cursor Object.

Sanjay Shah

"Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message
news:AD7EA5E3-4737-4BDB-908F-C98BB29A645E(a)microsoft.com...
>> I had checked by inserting records in a Temporary Table. If I do Not Use
>> UPDATE statement then it INSERT maximum 65 records in Table and if I use
>> UPDATE statement then it INSERT & UPDATE maximum 49 records.
>
> Add SET NOCOUNT ON to the beginning of the script. This is a Best
> Practice for ADO applications because it will suppress the DONE_IN_PROC
> messages (rowcounts) that can interfere expected behavior with the ADO
> API. Alternatively, you can invoke the ADODB.Command MoveNext and
> NextRecordset methods from within nested loops in your application code to
> consume all the resultsets.
>
> Also, to add on to Gert-Jan's recommendations, I suggest you add LOCAL to
> the CURSOR declaration like the example below.
>
> DECLARE Ledger_Cursor CURSOR LOCAL FAST_FORWARD FOR ...
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
>


From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 19 Jan 2010 07:56
Hi Gert,

This is not problem of STATIC and FAST_FORWARD. Because I had tried all
parameters.

This might be a problem of provider.

Sanjay Shah

"Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message
news:4B51EB46.21E53262(a)xs4all.nl...
> Plamen Ratchev wrote:
>>
>> Hugo did interesting tests and concluded using STATIC achieves best
>> performance:
>> http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx
>>
>> --
>> Plamen Ratchev
>> http://www.SQLStudio.com
>
> I guess that both STATIC and FAST_FORWARD are good choices.
>
> You may also have seen the final note of Hugo himself in the comments at
> the end, that says "I found that there are cases where the FAST_FORWARD
> option is faster than the STATIC option", which conforms with the
> "wisdom" about cursors from 2000 to 2009.
>
> --
> Gert-Jan


From: SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD. on 19 Jan 2010 07:58
Hi Michael,

I am not updating one record more than one in one UPDATE statement.
Therefore I am using CURSOR.

Thanks,

Sanjay Shah

"Michael Coles" <admin(a)geocodenet.com> wrote in message
news:D5BF8CE8-74A7-4279-BCAA-544402C3F06F(a)microsoft.com...
> Try the temp table again, and put a PK constraint on (ItemCode, StoreCode,
> ColorCode, SizeCode, BatchSerialNo, DocumentYear, BranchCode,
> DocumentType, SeriesCode, DocumentNo, ItemSno) columns in the temp table.
> If it errors out this means you are generating duplicates, which means
> your update statement is updating some rows two or more times.
>
> If you want to see exactly which rows are being updated twice you can
> create the temp table without the PK constraint and run a query like this:
>
> SELECT ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo,
> DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno,
> COUNT(*)
> FROM #MyTempTable
> GROUP BY ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo,
> DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno
> HAVING COUNT(*) > 1;
>
> --
> Thanks
>
> Michael Coles
> SQL Server MVP
> Author, "Expert SQL Server 2008 Encryption"
> (http://www.apress.com/book/view/1430224649)
> ----------------
>
> "SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." <microbrain(a)vsnl.com> wrote
> in message news:uHIfDgplKHA.5520(a)TK2MSFTNGP06.phx.gbl...
>> Dear Sir,
>>
>> I am using SQL Server 2005, Visual Basic 6 (SP6) & Microsoft ActiveX Data
>> Object 2.5.
>>
>> If I run following query through SQL Server 2005 Management Studio
>> through QUERY only then it works FINE. But if I run from application
>> using Execute method of Command Object of ADO then it process maximum 49
>> records in CURSOR and query completes without any errors.
>>
>> I had checked by inserting records in a Temporary Table. If I do Not Use
>> UPDATE statement then it INSERT maximum 65 records in Table and if I use
>> UPDATE statement then it INSERT & UPDATE maximum 49 records.
>>
>> PLEASE NOTE I DON'T WANT TO USE STORED PROCEDURE.
>>
>> Please reply me after proper reading of this query.
>>
>> Sanjay Shah
>>
>>
>> --- Declare Scalar Variables
>> DECLARE @cDocumentType AS nVarChar(3), @dDocumentDate AS DATETIME,
>> @cEntryType AS nVarChar(3), @cStoreCode AS nVarChar(6), @cItemCode AS
>> nVarChar(15), @nItemSno AS INT, @cValuation AS nVarChar(1), @cColorCode
>> AS nVarChar(4), @cSizeCode AS nVarChar(4), @cBatchSerialNo AS
>> nVarChar(15), @nRDocumentYear AS INT, @cRBranchCode AS nVarChar(3),
>> @cRDocumentType AS nVarChar(3), @cRSeriesCode AS nVarChar(4),
>> @cRDocumentNo AS nVarChar(6), @nRItemSno AS INT, @nQuantity AS FLOAT,
>> @nQuantity1 AS FLOAT, @nRate AS FLOAT, @nPer AS INT, @nAmount AS FLOAT,
>> @cTaxCode AS nVarChar(3), @nTaxableAmount AS FLOAT, @nTaxAmount AS FLOAT;
>>
>> --- Define Cursor with Transaction Ledger
>> DECLARE Ledger_Cursor CURSOR FOR
>> SELECT DocumentType, DocumentDate, EntryType, StoreCode, ItemCode,
>> ItemSno, Valuation, ColorCode, SizeCode, BatchSerialNo, RDocumentYear,
>> RBranchCode, RDocumentType, RSeriesCode, RDocumentNo, RItemSno, Quantity,
>> Quantity1, Rate, Per, Amount, TaxCode, TaxableAmount, TaxAmount FROM
>> FaItemLedger WHERE DocumentDate >= ('04/01/2003') AND DocumentDate <=
>> ('04/30/2003') ORDER BY DocumentDate, CASE WHEN Quantity > 0 OR Quantity1
>> > 0 OR Amount > 0 THEN 1 ELSE 2 END ;
>>
>> --- Open Cursor Rows
>> OPEN Ledger_Cursor;
>>
>> --- Get Values of Cursor Columns into Scalar Variables
>> FETCH NEXT FROM Ledger_Cursor INTO
>> @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, @cItemCode,
>> @nItemSno, @cValuation, @cColorCode, @cSizeCode, @cBatchSerialNo,
>> @nRDocumentYear, @cRBranchCode, @cRDocumentType, @cRSeriesCode,
>> @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate, @nPer,
>> @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
>>
>> -- Perform till Fatch Status is 0 means (Not EOF)
>> WHILE @@FETCH_STATUS = 0
>> BEGIN
>> --- Initialise RDocumentNo if Valuation is Not Bill Wise
>> IF @cValuation NOT IN ('B','P')
>> BEGIN
>> SET @nRDocumentYear = 0;
>> SET @cRBranchCode = '';
>> SET @cRDocumentType = '';
>> SET @cRSeriesCode = '';
>> SET @cRDocumentNo = '';
>> SET @nRItemSno = 0;
>> END
>>
>> --- Update Stock Master with Values of Scalar Variables
>> UPDATE FaStockMaster SET
>> FaStockMaster.StockQuantity =
>> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) ,
>> FaStockMaster.StockQuantity1 =
>> Round(FaStockMaster.StockQuantity1 + @nQuantity1 * 1, 3) ,
>> FaStockMaster.Used = 'True',
>> FaStockMaster.Month1TotalQuantity =
>> ROUND(FaStockMaster.Month1TotalQuantity + @nQuantity * 1, 3),
>> FaStockMaster.Month1TotalQuantity1 =
>> ROUND(FaStockMaster.Month1TotalQuantity1 + @nQuantity1 * 1, 3),
>> FaStockMaster.Rate = CASE WHEN
>> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) > 0 AND CASE WHEN
>> @cDocumentType IN ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU')
>> AND @nQuantity > 0 AND @cValuation IN ('W','F') THEN
>> Round(FaStockMaster.StockValue + @nAmount * 1, 2) ELSE
>> Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) * CASE WHEN
>> @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity
>> > 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
>> FaStockMaster.Per END , 2) END > 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity >
>> 0 AND @cValuation IN ('W','F') THEN Round(CASE WHEN @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity >
>> 0 AND @cVa
>> luation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount * 1,
>> 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) *
>> CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity
>> > 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
>> FaStockMaster.Per END , 2) END / Round(FaStockMaster.StockQuantity +
>> @nQuantity * 1, 3) * CASE WHEN @cValuation IN ('B','P') AND @nQuantity >
>> 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per
>> END , 6) WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
>> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity
>> > 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN Round(@nRate
>> / CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
>> FaStockMaster.Per END * FaStockMaster.Per, 6) ELSE FaStockMaster.Rate
>> END ,
>> FaStockMaster.Per = CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
>> FaStockMaster.Per END ,
>> FaStockMaster.StockValue = CASE WHEN @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity >
>> 0 AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue +
>> @nAmount * 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity +
>> @nQuantity * 1, 3) * CASE WHEN @cValuation IN ('L','B','P','A','N') AND
>> (@dDocumentDate >= FaStockMaster.LastReceiptDate OR @cValuation IN
>> ('B','P')) AND @nQuantity > 0 AND @cDocumentType IN
>> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
>> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND
>> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
>> FaStockMaster.Per END , 2) END,
>> FaStockMaster.TaxCode = CASE WHEN @cValuation IN ('B','P')
>> AND @nQuantity > 0 THEN @cTaxCode ELSE FaStockMaster.TaxCode END ,
>> FaStockMaster.TaxableAmount = CASE WHEN @cValuation IN
>> ('B','P') AND @nQuantity > 0 THEN @nTaxableAmount ELSE
>> FaStockMaster.TaxableAmount END ,
>> FaStockMaster.TaxAmount = CASE WHEN @cValuation IN
>> ('B','P') AND @nQuantity > 0 THEN @nTaxAmount ELSE
>> FaStockMaster.TaxAmount END ,
>> FaStockMaster.LastReceiptDate = CASE WHEN @cDocumentType IN
>> ('GIN','PUR','CPU','ISU','PRE') AND @cEntryType <> 'GIR' AND @nQuantity >
>> 0 AND @dDocumentDate > FaStockMaster.LastReceiptDate THEN @dDocumentDate
>> ELSE FaStockMaster.LastReceiptDate END
>> WHERE
>> FaStockMaster.ItemCode = @cItemCode AND
>> FaStockMaster.StoreCode = @cStoreCode AND
>> FaStockMaster.ColorCode = @cColorCode AND
>> FaStockMaster.SizeCode = @cSizeCode AND
>> FaStockMaster.BatchSerialNo = @cBatchSerialNo AND
>> FaStockMaster.DocumentYear = @nRDocumentYear AND
>> FaStockMaster.BranchCode = @cRBranchCode AND
>> FaStockMaster.DocumentType = @cRDocumentType AND
>> FaStockMaster.SeriesCode = @cRSeriesCode AND
>> FaStockMaster.DocumentNo = @cRDocumentNo AND
>> FaStockMaster.ItemSno = @nRItemSno ;
>>
>> --- Get Next Values of Cursor Columns into Scalar Variables
>> FETCH NEXT FROM Ledger_Cursor INTO
>> @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode,
>> @cItemCode, @nItemSno, @cValuation, @cColorCode, @cSizeCode,
>> @cBatchSerialNo, @nRDocumentYear, @cRBranchCode, @cRDocumentType,
>> @cRSeriesCode, @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1,
>> @nRate, @nPer, @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
>> END
>>
>> --- Close Cursor
>> CLOSE Ledger_Cursor;
>>
>> --- Release Cursor
>> DEALLOCATE Ledger_Cursor;
>>
>> --- *** End of Procedure
>>
>>
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Having divide by zero error
Next: Best way to persist alter table changes in my local DB to web host