August 17, 2011 at 9:35 am
GSquared (8/16/2011)
nadabadan (8/15/2011)
troe_atl (8/15/2011)
As for nadabadan your response was awful. I don’t expect you to read my mind and I didn’t need or want your rude response. There are comments in the code so anyone that knows a little about T-SQL could have figured it out. Next time keep your useless comments to yourself and try to be more professional when posting responses.There is nothing advanced or difficult about the T-SQL in your stored procedure. Basic garden variety junk someone with a month's knowledge of T-SQL put together. Also, the comments in the code are useless. It's so useless, your procedure would be better without them.
Instead of arguing with me, next time take some time and effort to frame the question properly. Maybe that way, you'll get an answer. Put yourself in our position and ask yourself what others who are trying to answer your question would need. After all, we are trying to help you do the job you obviously are not smart enough to do. In fine, take some of your advice and be more professional when posting questions.
The above post has been reported to forum moderators. Please ignore it till they have had a chance to review it against forum posting policies.
Hey genius. Did you bother to read the thread.
The question was
I have a store procedure that has a summary query that return 4 rows but only inserts 2 rows into my table. Please take a look at the code below and tell me what I am doing wrong.
And I answered it with
I see two inserts into PLT_DBOH_DATA..gltrxdet
I see one insert into PLT_DBOH_DATA..gltrx_all
What have you contributed other than being a little snitch.
August 17, 2011 at 9:53 am
And what exactly did you contribute? A lot of arrogance and confrontational nonsense.
Here is your post with all of the arrogant and non contributing drivel strikethrough
There is nothing advanced or difficult about the T-SQL in your stored procedure. Basic garden variety junk someone with a month's knowledge of T-SQL put together. Also, the comments in the code are useless. It's so useless, your procedure would be better without them.Instead of arguing with me, next time take some time and effort to frame the question properly. Maybe that way, you'll get an answer. Put yourself in our position and ask yourself what others who are trying to answer your question would need. After all, we are trying to help you
do the job you obviously are not smart enough to do. In fine, take some of your advice and be more professional when posting questions.
Nobody wants to be a snitch as you say but this forum is about helping people not putting them down because they are not as smart as you perceive yourself to be. You claim the OP was not professional but your responses were childish at best and nowhere near professional. Truly the epitome of the pot calling the kettle black. I actually agree with most of the things you said but the way you said them was aggressive and condescending.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 17, 2011 at 10:25 am
Sean Lange (8/17/2011)
And what exactly did you contribute? A lot of arrogance and confrontational nonsense.Here is your post with all of the arrogant and non contributing drivel strikethrough
There is nothing advanced or difficult about the T-SQL in your stored procedure. Basic garden variety junk someone with a month's knowledge of T-SQL put together. Also, the comments in the code are useless. It's so useless, your procedure would be better without them.Instead of arguing with me, next time take some time and effort to frame the question properly. Maybe that way, you'll get an answer. Put yourself in our position and ask yourself what others who are trying to answer your question would need. After all, we are trying to help you
do the job you obviously are not smart enough to do. In fine, take some of your advice and be more professional when posting questions.Nobody wants to be a snitch as you say but this forum is about helping people not putting them down because they are not as smart as you perceive yourself to be. You claim the OP was not professional but your responses were childish at best and nowhere near professional. Truly the epitome of the pot calling the kettle black. I actually agree with most of the things you said but the way you said them was aggressive and condescending.
And here is the OP's comment, you know what my "drivel" was in response to.
As for nadabadan your response was awful. I don’t expect you to read my mind and I didn’t need or want your rude response. There are comments in the code so anyone that knows a little about T-SQL could have figured it out. Next time keep your useless comments to yourself and try to be more professional when posting responses.
Way to selectively quote a post. My "drivel" was in response to another drivel -> from the OP : "There are comments in the code so anyone that knows a little about T-SQL could have figured it out." Talk about aggressive and condescending. I don't perceive myself to be "smart". But I can see when someone has put little to no effort into framing their question and no effort into solving their own question. As for GSquared, the guy contributed nothing. All he does is harass other people who are at least trying to be helpful. I bet that the next time troe_atl posts here, he'll write a better post. That's how we improve. Coddling people doesn't work. As for what I contributed. I ANSWERED the OP's question. And I can't think of anything more unprofessional and impolite than snitching.
August 17, 2011 at 11:45 am
I finally got the procedure to do what I wanted it to do. The modified procedure is listed below. Special thanks to all that contributed!!
USE [ISPurchasing]
GO
/****** Object: StoredProcedure [dbo].[cust_CreateJournalEntry] Script Date: 08/17/2011 13:37:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cust_CreateJournalEntry]
@PO_NUMBER VARCHAR(10)
AS
DECLARE @OrderID Varchar(16)
DECLARE @ExtendedPrice MONEY
DECLARE @rate_type_home VARCHAR(8)
DECLARE @rate_type_oper VARCHAR(8)
DECLARE @home_currency VARCHAR(8)
DECLARE @oper_currency VARCHAR(8)
DECLARE @company_code VARCHAR(8)
DECLARE @org_id VARCHAR(30)
DECLARE @company_id VARCHAR(30)
DECLARE @next_num VARCHAR(16)
DECLARE @date INT
DECLARE @account VARCHAR(10)
DECLARE @BudgetUnit VARCHAR(10)
DECLARE @AccountCode VARCHAR(20)
DECLARE @ShortDescription VARCHAR(16)
DECLARE @VendorName VARCHAR(40)
DECLARE @SequenceID INT
SET @SequenceID = 0
SELECT
@rate_type_home = rate_type_home, --Get rate type home
@rate_type_oper = rate_type_oper, --Get rate type oper
@home_currency = home_currency, --Get Home Currency
@oper_currency = oper_currency, --Get Oper Currency
@company_code = company_code, --Get Company_Code
@company_id = company_id --Get Company Id
FROM PLT_DBOH_DATA..glco
--get the Org_id
SELECT TOP 1 @org_id = org_id FROM PLT_DBOH_DATA..smspiduser_vw spid WHERE global_user = 1
--Obtain the next control number
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-(SELECT LEN(next_jrnl_ctrl_code) from PLT_DBOH_DATA..glnumber)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
--and prepare the next
UPDATE PLT_DBOH_DATA..glnumber SET next_jrnl_ctrl_code = next_jrnl_ctrl_code + 1
--Get julian date
SELECT @date = DATEDIFF(dd, '1/1/1980', GETDATE()) + 722815 --today
IF(@org_id IS NULL OR @org_id = '')
SET @org_id = 'DBOH'
SELECT @OrderID = REQ.RequisitionNumber,
@VendorName = REQ.SupplierName
FROM Requisition REQ
WHERE REQ.POID = @PO_NUMBER
INSERT INTO PLT_DBOH_DATA..gltrx_all(journal_type,journal_ctrl_num,journal_description,date_entered,date_applied,recurring_flag,repeating_flag,reversing_flag,hold_flag,
posted_flag,date_posted,source_batch_code,batch_code,type_flag,intercompany_flag,company_code,app_id,home_cur_code,document_1,trx_type,
user_id,source_company_code,process_group_num,oper_cur_code,org_id,interbranch_flag)
VALUES('PO', @next_num, 'From ISPurchasing PO: '+ @PO_NUMBER,@date,@date,0,0,0,0,0,0,'','',0,0,@company_code,6000,@home_currency,'',111,7,@company_code,'',@oper_currency,@org_id,0)
DECLARE CUR CURSOR FOR
SELECT BudgetUnit,
POChartOfAccount,
'Encumbrance',
SUM(UnitPrice * UnitsOrdered)
FROM Requisition AS Requisition_1
INNER JOIN ((PurchaseOrder
INNER JOIN Requisition ON PurchaseOrder.POID = Requisition.POID)
INNER JOIN RequisitionLineItems
ON Requisition.RequisitionNumber = RequisitionLineItems.RequisitionNumber)
ON Requisition_1.RequisitionNumber = RequisitionLineItems.RequisitionNumber
WHERE PurchaseOrder.POID = @PO_NUMBER
GROUP BY PurchaseOrder.POID,POChartOfAccount,BudgetUnit
OPEN CUR
FETCH NEXT FROM CUR INTO @BudgetUnit,@Account,@ShortDescription, @ExtendedPrice
WHILE @@FETCH_STATUS <> -1
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
----GET COMPLETE ACCOUNT CODE FOR JOURNAL ENTRY
SET @AccountCode = @account + @BudgetUnit
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
INSERT INTO PLT_DBOH_DATA..gltrxdet(journal_ctrl_num,sequence_id,rec_company_code,company_id,account_code,description,document_1,document_2,reference_code,balance,nat_balance,
nat_cur_code,rate,posted_flag,date_posted,trx_type,offset_flag,seg1_code,seg2_code,seg3_code,seg4_code,seq_ref_id,balance_oper,rate_oper,
rate_type_home,rate_type_oper,org_id)
VALUES(@next_num, @SequenceID, @company_code, @company_id, @AccountCode, SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', @ExtendedPrice, @ExtendedPrice,'USD',1,0,0,111,0, @account, @BudgetUnit,'','',0, @ExtendedPrice,1,@rate_type_home,@rate_type_oper,@org_id)
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
INSERT INTO PLT_DBOH_DATA..gltrxdet(journal_ctrl_num,sequence_id,rec_company_code,company_id,account_code,description,document_1,document_2,reference_code,balance,nat_balance,
nat_cur_code,rate,posted_flag,date_posted,trx_type,offset_flag,seg1_code,seg2_code,seg3_code,seg4_code,seq_ref_id,balance_oper,rate_oper,
rate_type_home,rate_type_oper,org_id)
VALUES(@next_num, @SequenceID ,@company_code, @company_id, '2700000', SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', (@ExtendedPrice)*-1, (@ExtendedPrice)*-1,'USD',1,0,0,111,0, '2700', '000','','',0, (@ExtendedPrice)-1,1,@rate_type_home,@rate_type_oper,@org_id)
FETCH NEXT FROM CUR INTO @BudgetUnit, @account, @ShortDescription, @ExtendedPrice
END
END
CLOSE CUR
DEALLOCATE CUR
August 17, 2011 at 11:48 am
Oh man you so don't want to use a cursor here. I am pretty swamped at the moment but I can help later today if nobody else jumps in.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 17, 2011 at 12:28 pm
One thing at a time, you don't want a sub select here
--Obtain the next control number
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-(SELECT LEN(next_jrnl_ctrl_code) from PLT_DBOH_DATA..glnumber)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
In fact, it's only not erroring because there is only one row in the table. Since the fields come from the same table, you can write this as
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-LEN(next_jrnl_ctrl_code)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
August 17, 2011 at 12:34 pm
Uh oh. I thought I was done! The cursor was the only way I was able to get it to work the way I needed it to but if that will cause problems, your help with this will be greatly appreciated.
Thanks again for your help.
USE [ISPurchasing]
GO
/****** Object: StoredProcedure [dbo].[cust_CreateJournalEntry] Script Date: 08/17/2011 13:37:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cust_CreateJournalEntry]
@PO_NUMBER VARCHAR(10)
AS
DECLARE @OrderID Varchar(16)
DECLARE @ExtendedPrice MONEY
DECLARE @rate_type_home VARCHAR(8)
DECLARE @rate_type_oper VARCHAR(8)
DECLARE @home_currency VARCHAR(8)
DECLARE @oper_currency VARCHAR(8)
DECLARE @company_code VARCHAR(8)
DECLARE @org_id VARCHAR(30)
DECLARE @company_id VARCHAR(30)
DECLARE @next_num VARCHAR(16)
DECLARE @date INT
DECLARE @account VARCHAR(10)
DECLARE @BudgetUnit VARCHAR(10)
DECLARE @AccountCode VARCHAR(20)
DECLARE @ShortDescription VARCHAR(16)
DECLARE @VendorName VARCHAR(40)
DECLARE @SequenceID INT
SET @SequenceID = 0
SELECT
@rate_type_home = rate_type_home, --Get rate type home
@rate_type_oper = rate_type_oper, --Get rate type oper
@home_currency = home_currency, --Get Home Currency
@oper_currency = oper_currency, --Get Oper Currency
@company_code = company_code, --Get Company_Code
@company_id = company_id --Get Company Id
FROM PLT_DBOH_DATA..glco
--get the Org_id
SELECT TOP 1 @org_id = org_id FROM PLT_DBOH_DATA..smspiduser_vw spid WHERE global_user = 1
--Obtain the next control number
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-(SELECT LEN(next_jrnl_ctrl_code) from PLT_DBOH_DATA..glnumber)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
--and prepare the next
UPDATE PLT_DBOH_DATA..glnumber SET next_jrnl_ctrl_code = next_jrnl_ctrl_code + 1
--Get julian date
SELECT @date = DATEDIFF(dd, '1/1/1980', GETDATE()) + 722815 --today
IF(@org_id IS NULL OR @org_id = '')
SET @org_id = 'DBOH'
SELECT @OrderID = REQ.RequisitionNumber,
@VendorName = REQ.SupplierName
FROM Requisition REQ
WHERE REQ.POID = @PO_NUMBER
INSERT INTO PLT_DBOH_DATA..gltrx_all(journal_type,journal_ctrl_num,journal_description,date_entered,date_applied,recurring_flag,repeating_flag,reversing_flag,hold_flag,
posted_flag,date_posted,source_batch_code,batch_code,type_flag,intercompany_flag,company_code,app_id,home_cur_code,document_1,trx_type,
user_id,source_company_code,process_group_num,oper_cur_code,org_id,interbranch_flag)
VALUES('PO', @next_num, 'From ISPurchasing PO: '+ @PO_NUMBER,@date,@date,0,0,0,0,0,0,'','',0,0,@company_code,6000,@home_currency,'',111,7,@company_code,'',@oper_currency,@org_id,0)
DECLARE CUR CURSOR FOR
SELECT BudgetUnit,
POChartOfAccount,
'Encumbrance',
SUM(UnitPrice * UnitsOrdered)
FROM Requisition AS Requisition_1
INNER JOIN ((PurchaseOrder
INNER JOIN Requisition ON PurchaseOrder.POID = Requisition.POID)
INNER JOIN RequisitionLineItems
ON Requisition.RequisitionNumber = RequisitionLineItems.RequisitionNumber)
ON Requisition_1.RequisitionNumber = RequisitionLineItems.RequisitionNumber
WHERE PurchaseOrder.POID = @PO_NUMBER
GROUP BY PurchaseOrder.POID,POChartOfAccount,BudgetUnit
OPEN CUR
FETCH NEXT FROM CUR INTO @BudgetUnit,@Account,@ShortDescription, @ExtendedPrice
WHILE @@FETCH_STATUS <> -1
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
----GET COMPLETE ACCOUNT CODE FOR JOURNAL ENTRY
SET @AccountCode = @account + @BudgetUnit
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
INSERT INTO PLT_DBOH_DATA..gltrxdet(journal_ctrl_num,sequence_id,rec_company_code,company_id,account_code,description,document_1,document_2,reference_code,balance,nat_balance,
nat_cur_code,rate,posted_flag,date_posted,trx_type,offset_flag,seg1_code,seg2_code,seg3_code,seg4_code,seq_ref_id,balance_oper,rate_oper,
rate_type_home,rate_type_oper,org_id)
VALUES(@next_num, @SequenceID, @company_code, @company_id, @AccountCode, SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', @ExtendedPrice, @ExtendedPrice,'USD',1,0,0,111,0, @account, @BudgetUnit,'','',0, @ExtendedPrice,1,@rate_type_home,@rate_type_oper,@org_id)
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
INSERT INTO PLT_DBOH_DATA..gltrxdet(journal_ctrl_num,sequence_id,rec_company_code,company_id,account_code,description,document_1,document_2,reference_code,balance,nat_balance,
nat_cur_code,rate,posted_flag,date_posted,trx_type,offset_flag,seg1_code,seg2_code,seg3_code,seg4_code,seq_ref_id,balance_oper,rate_oper,
rate_type_home,rate_type_oper,org_id)
VALUES(@next_num, @SequenceID ,@company_code, @company_id, '2700000', SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', (@ExtendedPrice)*-1, (@ExtendedPrice)*-1,'USD',1,0,0,111,0, '2700', '000','','',0, (@ExtendedPrice)-1,1,@rate_type_home,@rate_type_oper,@org_id)
FETCH NEXT FROM CUR INTO @BudgetUnit, @account, @ShortDescription, @ExtendedPrice
END
END
CLOSE CUR
DEALLOCATE CUR
August 17, 2011 at 3:10 pm
Oh my!!! There is so much inefficient and not accurate code in here.
Let's start here.
SELECT
@rate_type_home = rate_type_home, --Get rate type home
@rate_type_oper = rate_type_oper, --Get rate type oper
@home_currency = home_currency, --Get Home Currency
@oper_currency = oper_currency, --Get Oper Currency
@company_code = company_code, --Get Company_Code
@company_id = company_id --Get Company Id
FROM PLT_DBOH_DATA..glco
Which record is this going to get? Do you know? The first one? Last? Which record is first? There is no order by. If there is ever more than 1 record in this table you have just randomly selected which row to use to populate your variables.
--get the Org_id
SELECT TOP 1 @org_id = org_id FROM PLT_DBOH_DATA..smspiduser_vw spid WHERE global_user = 1
Same issue as above. Which org_id is this going to get??
--Obtain the next control number
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-(SELECT LEN(next_jrnl_ctrl_code) from PLT_DBOH_DATA..glnumber)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
Todd already gave you the code to fix this thing.
SELECT @OrderID = REQ.RequisitionNumber,
@VendorName = REQ.SupplierName
FROM Requisition REQ
WHERE REQ.POID = @PO_NUMBER
Is POID the primary key for the requisition table or is there a unique constraint on that column? If either answer is no then you once again don't know which record you are going to get.
SELECT BudgetUnit,
POChartOfAccount,
'Encumbrance',
SUM(UnitPrice * UnitsOrdered)
FROM Requisition AS Requisition_1
INNER JOIN ((PurchaseOrder
INNER JOIN Requisition ON PurchaseOrder.POID = Requisition.POID)
INNER JOIN RequisitionLineItems
ON Requisition.RequisitionNumber = RequisitionLineItems.RequisitionNumber)
ON Requisition_1.RequisitionNumber = RequisitionLineItems.RequisitionNumber
WHERE PurchaseOrder.POID = @PO_NUMBER
GROUP BY PurchaseOrder.POID,POChartOfAccount,BudgetUnit
It looks like you are selecting from Requisition and using the fields from PurchaseOrder? Why does Requisition need to join to itself? There may be a reason that query needs to be that way but it looks very strange to say the least. I would investigate what that is supposed to be doing.
SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255)
Isn't that the same thing as using Left(@PO_NUMBER + ' | ' + @VendorName, 255)? Oh wait, you only want the first 255 characters of varchar(10) + ' | ' + varchar(40). That is at MOST 53 characters. Why bother with all the string manipulation? How about simply @PO_NUMBER + ' | ' + @VendorName
CONVERT(VARCHAR(16), '')
Talk about a waste or processing power. This is literally casting a hard coded empty string to a empty varchar(16). Look at the field to the right of this for the proper way to insert an empty string. Hint: ''
Ok now on to how you are processing this. Cursors are the worst performing construct in t-sql hands down. Well any form of RBAR (row by agonizing row) is awful. If this thing works right now I would be surprised if the output is actually correct. I would not at all be surprised if this is really REALLY slow. Start with fixing all the other issues and see how it performs. If it is still really slow lets go back and see about eradicating that **cough**cursor**cough**
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 17, 2011 at 3:54 pm
Your query that defines the cursor cleans up to this
SELECT
BudgetUnit,
POChartOfAccount,
'Encumbrance' ShortDescription,
SUM(UnitPrice * UnitsOrdered) ExtendedPrice
FROM
Requisition r
INNER JOIN
PurchaseOrder po on r.POID = po.POID
INNER JOIN
RequisitionLineItems rli ON r.RequisitionNumber = rli.RequisitionNumber
WHERE
PurchaseOrder.POID = @PO_NUMBER
GROUP BY
BudgetUnit,
POChartOfAccount
But this will only be correct if requisitions and purchase orders are a one-to-one relationship. Also, what makes these PO's define as "Encumbrance"? There is no filter in the where clause for them, so why are these different than other types?
I don't want to promote cursors, but at least we can make them right. This is unnecessary
WHILE @@FETCH_STATUS <> -1
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
Replace with the single clause
WHILE @@FETCH_STATUS = 0
BEGIN
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
August 17, 2011 at 4:04 pm
Hi Sean. For this segment of code, there is only one record in this table and it will be the only one every entered for this table. This is how the procedure was originally coded so I left it as is.
SELECT
@rate_type_home = rate_type_home, --Get rate type home
@rate_type_oper = rate_type_oper, --Get rate type oper
@home_currency = home_currency, --Get Home Currency
@oper_currency = oper_currency, --Get Oper Currency
@company_code = company_code, --Get Company_Code
@company_id = company_id --Get Company Id
FROM PLT_DBOH_DATA..glco
Same thing with this code. It only has one record in the table which represents my company. It will never be more than one record. I don't know why it was coded that way so I didn't change it because I don't know what impact it might have on the front end.
--get the Org_id
SELECT TOP 1 @org_id = org_id FROM PLT_DBOH_DATA..smspiduser_vw spid WHERE global_user = 1
You are right. Thanks Todd.
--Obtain the next control number
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-(SELECT LEN(next_jrnl_ctrl_code) from PLT_DBOH_DATA..glnumber)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
The POID field is a foreign key constraint in the Requisition table.
SELECT @OrderID = REQ.RequisitionNumber,
@VendorName = REQ.SupplierName
FROM Requisition REQ
WHERE REQ.POID = @PO_NUMBER
The reason I got the Requisition table joining itself is because two entries must be made for each record in the gltrxdet table. One entry must be made to debit an account by the AccountCode and another entry must be made to credit the expense account which is the '2700000' account.
SELECT BudgetUnit,
POChartOfAccount,
'Encumbrance',
SUM(UnitPrice * UnitsOrdered)
FROM Requisition AS Requisition_1
INNER JOIN ((PurchaseOrder
INNER JOIN Requisition ON PurchaseOrder.POID = Requisition.POID)
INNER JOIN RequisitionLineItems
ON Requisition.RequisitionNumber = RequisitionLineItems.RequisitionNumber)
ON Requisition_1.RequisitionNumber = RequisitionLineItems.RequisitionNumber
WHERE PurchaseOrder.POID = @PO_NUMBER
GROUP BY PurchaseOrder.POID,POChartOfAccount,BudgetUnit
I will change this.
SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255)
and this.
CONVERT(VARCHAR(16), '')
I know it is better to code this using set based processing but I don't know how yet. I will read up some more about set processing. Thanks for all your help.
Ok now on to how you are processing this. Cursors are the worst performing construct in t-sql hands down. Well any form of RBAR (row by agonizing row) is awful. If this thing works right now I would be surprised if the output is actually correct. I would not at all be surprised if this is really REALLY slow. Start with fixing all the other issues and see how it performs. If it is still really slow lets go back and see about eradicating that **cough**cursor**cough**
August 18, 2011 at 3:01 am
This incorporates many of the changes suggested to date and replaces the cursor with a local temporary table.
ALTER PROCEDURE [dbo].[cust_CreateJournalEntry]
@PO_NUMBER VARCHAR(10)
AS
DECLARE @OrderID Varchar(16)
--DECLARE @ExtendedPrice MONEY
DECLARE @rate_type_home VARCHAR(8)
DECLARE @rate_type_oper VARCHAR(8)
DECLARE @home_currency VARCHAR(8)
DECLARE @oper_currency VARCHAR(8)
DECLARE @company_code VARCHAR(8)
DECLARE @org_id VARCHAR(30)
DECLARE @company_id VARCHAR(30)
DECLARE @next_num VARCHAR(16)
DECLARE @date INT
--DECLARE @account VARCHAR(10)
--DECLARE @BudgetUnit VARCHAR(10)
--DECLARE @AccountCode VARCHAR(20)
--DECLARE @ShortDescription VARCHAR(16)
DECLARE @VendorName VARCHAR(40)
DECLARE @SequenceID INT
SET @SequenceID = 0
SELECT
@rate_type_home = rate_type_home, --Get rate type home
@rate_type_oper = rate_type_oper, --Get rate type oper
@home_currency = home_currency, --Get Home Currency
@oper_currency = oper_currency, --Get Oper Currency
@company_code = company_code, --Get Company_Code
@company_id = company_id --Get Company Id
FROM PLT_DBOH_DATA..glco
--get the Org_id
SELECT TOP 1 @org_id = org_id
FROM PLT_DBOH_DATA..smspiduser_vw spid
WHERE global_user = 1
IF(@org_id IS NULL OR @org_id = '')
SET @org_id = 'DBOH'
------------------------------------------------------------------
--Obtain the next control number
--SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-(SELECT LEN(next_jrnl_ctrl_code) from PLT_DBOH_DATA..glnumber)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
--Obtain the next control number
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask, 1, 12-LEN(next_jrnl_ctrl_code))
+ CAST(next_jrnl_ctrl_code AS VARCHAR(16))
FROM PLT_DBOH_DATA..glnumber
--and prepare the next
UPDATE PLT_DBOH_DATA..glnumber SET next_jrnl_ctrl_code = next_jrnl_ctrl_code + 1
------------------------------------------------------------------
--Get julian date
SELECT @date = DATEDIFF(dd, '1/1/1980', GETDATE()) + 722815 --today
SELECT
@OrderID = REQ.RequisitionNumber,
@VendorName = REQ.SupplierName
FROM Requisition REQ
WHERE REQ.POID = @PO_NUMBER
INSERT INTO PLT_DBOH_DATA..gltrx_all(journal_type,journal_ctrl_num,journal_description,date_entered,date_applied,recurring_flag,repeating_flag,reversing_flag,hold_flag,
posted_flag,date_posted,source_batch_code,batch_code,type_flag,intercompany_flag,company_code,app_id,home_cur_code,document_1,trx_type,
user_id,source_company_code,process_group_num,oper_cur_code,org_id,interbranch_flag)
VALUES('PO', @next_num, 'From ISPurchasing PO: '+ @PO_NUMBER,@date,@date,0,0,0,0,0,0,'','',0,0,@company_code,6000,@home_currency,'',111,7,@company_code,'',@oper_currency,@org_id,0)
--------------------------------
-- get the single matching row from Requisition into a local temporary table
-- (query from toddasd)
-- drop the temp table if it already exists
IF OBJECT_ID (N'tempdb..#Requisition') IS NOT NULL DROP TABLE #Requisition
SELECT
BudgetUnit, -- @BudgetUnit
POChartOfAccount, -- @account
ShortDescription = 'Encumbrance', -- @ShortDescription
ExtendedPrice = SUM(UnitPrice * UnitsOrdered) -- @ExtendedPrice
INTO #Requisition -- local temporary table
FROM Requisition r
INNER JOIN PurchaseOrder po
ON r.POID = po.POID
INNER JOIN RequisitionLineItems rli
ON r.RequisitionNumber = rli.RequisitionNumber
WHERE PurchaseOrder.POID = @PO_NUMBER
GROUP BY BudgetUnit, POChartOfAccount
-------------------------------
-- first insert into gltrxdet
INSERT INTO PLT_DBOH_DATA..gltrxdet(
journal_ctrl_num,
sequence_id,
rec_company_code,
company_id,
account_code,
[description],
document_1,
document_2,
reference_code,
balance,
nat_balance,
nat_cur_code,
rate,
posted_flag,
date_posted,
trx_type,
offset_flag,
seg1_code, seg2_code, seg3_code, seg4_code,
seq_ref_id,
balance_oper,
rate_oper,
rate_type_home,
rate_type_oper,
org_id)
SELECT
journal_ctrl_num = @next_num,
sequence_id = 1, --@SequenceID,
rec_company_code = @company_code,
company_id = @company_id,
account_code = POChartOfAccount + BudgetUnit, --@AccountCode, = @account + @BudgetUnit
[description] = @PO_NUMBER + ' | ' + @VendorName,
document_1 = ShortDescription, --@ShortDescription,
document_2 = CONVERT(VARCHAR(16), ''),
reference_code = '',
balance = ExtendedPrice, --@ExtendedPrice,
nat_balance = ExtendedPrice, --@ExtendedPrice,
nat_cur_code = 'USD',
rate = 1,
posted_flag = 0,
date_posted = 0,
trx_type = 111,
offset_flag = 0,
seg1_code = POChartOfAccount, --@Account,
seg2_code = BudgetUnit, --@BudgetUnit,
seg3_code = '',
seg4_code = '',
seq_ref_id = 0,
balance_oper = ExtendedPrice, --@ExtendedPrice,
rate_oper = 1,
rate_type_home = @rate_type_home,
rate_type_oper = @rate_type_oper,
org_id = @org_id
FROM #Requisition
-- second insert into gltrxdet
INSERT INTO PLT_DBOH_DATA..gltrxdet(
journal_ctrl_num,
sequence_id,
rec_company_code,
company_id,
account_code,
[description],
document_1,
document_2,
reference_code,
balance,
nat_balance,
nat_cur_code,
rate,
posted_flag,
date_posted,
trx_type,
offset_flag,
seg1_code, seg2_code, seg3_code, seg4_code,
seq_ref_id,
balance_oper,
rate_oper,
rate_type_home,
rate_type_oper,
org_id)
SELECT
journal_ctrl_num = @next_num,
sequence_id = 2, --@SequenceID ,
rec_company_code = @company_code,
company_id = @company_id,
account_code = '2700000',
[description] = @PO_NUMBER + ' | ' + @VendorName,
document_1 = ShortDescription, --@ShortDescription,
document_2 = CONVERT(VARCHAR(16), ''),
reference_code = '',
balance = (ExtendedPrice)*-1, -- (@ExtendedPrice)*-1
nat_balance = (ExtendedPrice)*-1, -- (@ExtendedPrice)*-1
nat_cur_code = 'USD',
rate = 1,
posted_flag = 0,
date_posted = 0,
trx_type = 111,
offset_flag = 0,
seg1_code = '2700',
seg2_code = '000',
seg3_code = '',
seg4_code = '',
seq_ref_id = 0,
balance_oper = (ExtendedPrice)-1, -- (@ExtendedPrice)-1
rate_oper = 1,
rate_type_home = @rate_type_home,
rate_type_oper = @rate_type_oper,
org_id = @org_id
FROM #Requisition
-------------------------------------------------------------------------
-------------------------------------------------------------------------
/*
DECLARE CUR CURSOR FOR
SELECT
BudgetUnit,
POChartOfAccount,
'Encumbrance',
SUM(UnitPrice * UnitsOrdered)
FROM Requisition AS Requisition_1
INNER JOIN ((PurchaseOrder
INNER JOIN Requisition ON PurchaseOrder.POID = Requisition.POID)
INNER JOIN RequisitionLineItems
ON Requisition.RequisitionNumber = RequisitionLineItems.RequisitionNumber)
ON Requisition_1.RequisitionNumber = RequisitionLineItems.RequisitionNumber
WHERE PurchaseOrder.POID = @PO_NUMBER
GROUP BY PurchaseOrder.POID,POChartOfAccount,BudgetUnit
OPEN CUR
FETCH NEXT FROM CUR INTO @BudgetUnit,@Account,@ShortDescription, @ExtendedPrice
WHILE @@FETCH_STATUS <> -1
BEGIN
----GET COMPLETE ACCOUNT CODE FOR JOURNAL ENTRY
SET @AccountCode = @account + @BudgetUnit
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
INSERT INTO PLT_DBOH_DATA..gltrxdet(journal_ctrl_num,sequence_id,rec_company_code,company_id,account_code,description,document_1,document_2,reference_code,balance,nat_balance,
nat_cur_code,rate,posted_flag,date_posted,trx_type,offset_flag,seg1_code,seg2_code,seg3_code,seg4_code,seq_ref_id,balance_oper,rate_oper,
rate_type_home,rate_type_oper,org_id)
VALUES(@next_num, @SequenceID, @company_code, @company_id, @AccountCode, SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', @ExtendedPrice, @ExtendedPrice,'USD',1,0,0,111,0, @account, @BudgetUnit,'','',0, @ExtendedPrice,1,@rate_type_home,@rate_type_oper,@org_id)
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
INSERT INTO PLT_DBOH_DATA..gltrxdet(journal_ctrl_num,sequence_id,rec_company_code,company_id,account_code,description,document_1,document_2,reference_code,balance,nat_balance,
nat_cur_code,rate,posted_flag,date_posted,trx_type,offset_flag,seg1_code,seg2_code,seg3_code,seg4_code,seq_ref_id,balance_oper,rate_oper,
rate_type_home,rate_type_oper,org_id)
VALUES(@next_num, @SequenceID ,@company_code, @company_id, '2700000', SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', (@ExtendedPrice)*-1, (@ExtendedPrice)*-1,'USD',1,0,0,111,0, '2700', '000','','',0, (@ExtendedPrice)-1,1,@rate_type_home,@rate_type_oper,@org_id)
FETCH NEXT FROM CUR INTO @BudgetUnit, @account, @ShortDescription, @ExtendedPrice
END
CLOSE CUR
DEALLOCATE CUR
*/
GO
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2011 at 9:59 am
ChrisM@Work (8/18/2011)
This incorporates many of the changes suggested to date and replaces the cursor with a local temporary table.
ALTER PROCEDURE [dbo].[cust_CreateJournalEntry]@PO_NUMBER VARCHAR(10)
AS
DECLARE @OrderID Varchar(16)
--DECLARE @ExtendedPrice MONEY
DECLARE @rate_type_home VARCHAR(8)
DECLARE @rate_type_oper VARCHAR(8)
DECLARE @home_currency VARCHAR(8)
DECLARE @oper_currency VARCHAR(8)
DECLARE @company_code VARCHAR(8)
DECLARE @org_id VARCHAR(30)
DECLARE @company_id VARCHAR(30)
DECLARE @next_num VARCHAR(16)
DECLARE @date INT
--DECLARE @account VARCHAR(10)
--DECLARE @BudgetUnit VARCHAR(10)
--DECLARE @AccountCode VARCHAR(20)
--DECLARE @ShortDescription VARCHAR(16)
DECLARE @VendorName VARCHAR(40)
DECLARE @SequenceID INT
SET @SequenceID = 0
SELECT
@rate_type_home = rate_type_home, --Get rate type home
@rate_type_oper = rate_type_oper, --Get rate type oper
@home_currency = home_currency, --Get Home Currency
@oper_currency = oper_currency, --Get Oper Currency
@company_code = company_code, --Get Company_Code
@company_id = company_id --Get Company Id
FROM PLT_DBOH_DATA..glco
--get the Org_id
SELECT TOP 1 @org_id = org_id
FROM PLT_DBOH_DATA..smspiduser_vw spid
WHERE global_user = 1
IF(@org_id IS NULL OR @org_id = '')
SET @org_id = 'DBOH'
------------------------------------------------------------------
--Obtain the next control number
--SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask,1,12-(SELECT LEN(next_jrnl_ctrl_code) from PLT_DBOH_DATA..glnumber)) + CAST(next_jrnl_ctrl_code as varchar(16)) FROM PLT_DBOH_DATA..glnumber
--Obtain the next control number
SELECT @next_num = SUBSTRING(jrnl_ctrl_code_mask, 1, 12-LEN(next_jrnl_ctrl_code))
+ CAST(next_jrnl_ctrl_code AS VARCHAR(16))
FROM PLT_DBOH_DATA..glnumber
--and prepare the next
UPDATE PLT_DBOH_DATA..glnumber SET next_jrnl_ctrl_code = next_jrnl_ctrl_code + 1
------------------------------------------------------------------
--Get julian date
SELECT @date = DATEDIFF(dd, '1/1/1980', GETDATE()) + 722815 --today
SELECT
@OrderID = REQ.RequisitionNumber,
@VendorName = REQ.SupplierName
FROM Requisition REQ
WHERE REQ.POID = @PO_NUMBER
INSERT INTO PLT_DBOH_DATA..gltrx_all(journal_type,journal_ctrl_num,journal_description,date_entered,date_applied,recurring_flag,repeating_flag,reversing_flag,hold_flag,
posted_flag,date_posted,source_batch_code,batch_code,type_flag,intercompany_flag,company_code,app_id,home_cur_code,document_1,trx_type,
user_id,source_company_code,process_group_num,oper_cur_code,org_id,interbranch_flag)
VALUES('PO', @next_num, 'From ISPurchasing PO: '+ @PO_NUMBER,@date,@date,0,0,0,0,0,0,'','',0,0,@company_code,6000,@home_currency,'',111,7,@company_code,'',@oper_currency,@org_id,0)
--------------------------------
-- get the single matching row from Requisition into a local temporary table
-- (query from toddasd)
-- drop the temp table if it already exists
IF OBJECT_ID (N'tempdb..#Requisition') IS NOT NULL DROP TABLE #Requisition
SELECT
BudgetUnit, -- @BudgetUnit
POChartOfAccount, -- @account
ShortDescription = 'Encumbrance', -- @ShortDescription
ExtendedPrice = SUM(UnitPrice * UnitsOrdered) -- @ExtendedPrice
INTO #Requisition -- local temporary table
FROM Requisition r
INNER JOIN PurchaseOrder po
ON r.POID = po.POID
INNER JOIN RequisitionLineItems rli
ON r.RequisitionNumber = rli.RequisitionNumber
WHERE PurchaseOrder.POID = @PO_NUMBER
GROUP BY BudgetUnit, POChartOfAccount
-------------------------------
-- first insert into gltrxdet
INSERT INTO PLT_DBOH_DATA..gltrxdet(
journal_ctrl_num,
sequence_id,
rec_company_code,
company_id,
account_code,
[description],
document_1,
document_2,
reference_code,
balance,
nat_balance,
nat_cur_code,
rate,
posted_flag,
date_posted,
trx_type,
offset_flag,
seg1_code, seg2_code, seg3_code, seg4_code,
seq_ref_id,
balance_oper,
rate_oper,
rate_type_home,
rate_type_oper,
org_id)
SELECT
journal_ctrl_num = @next_num,
sequence_id = 1, --@SequenceID,
rec_company_code = @company_code,
company_id = @company_id,
account_code = POChartOfAccount + BudgetUnit, --@AccountCode, = @account + @BudgetUnit
[description] = @PO_NUMBER + ' | ' + @VendorName,
document_1 = ShortDescription, --@ShortDescription,
document_2 = CONVERT(VARCHAR(16), ''),
reference_code = '',
balance = ExtendedPrice, --@ExtendedPrice,
nat_balance = ExtendedPrice, --@ExtendedPrice,
nat_cur_code = 'USD',
rate = 1,
posted_flag = 0,
date_posted = 0,
trx_type = 111,
offset_flag = 0,
seg1_code = POChartOfAccount, --@Account,
seg2_code = BudgetUnit, --@BudgetUnit,
seg3_code = '',
seg4_code = '',
seq_ref_id = 0,
balance_oper = ExtendedPrice, --@ExtendedPrice,
rate_oper = 1,
rate_type_home = @rate_type_home,
rate_type_oper = @rate_type_oper,
org_id = @org_id
FROM #Requisition
-- second insert into gltrxdet
INSERT INTO PLT_DBOH_DATA..gltrxdet(
journal_ctrl_num,
sequence_id,
rec_company_code,
company_id,
account_code,
[description],
document_1,
document_2,
reference_code,
balance,
nat_balance,
nat_cur_code,
rate,
posted_flag,
date_posted,
trx_type,
offset_flag,
seg1_code, seg2_code, seg3_code, seg4_code,
seq_ref_id,
balance_oper,
rate_oper,
rate_type_home,
rate_type_oper,
org_id)
SELECT
journal_ctrl_num = @next_num,
sequence_id = 2, --@SequenceID ,
rec_company_code = @company_code,
company_id = @company_id,
account_code = '2700000',
[description] = @PO_NUMBER + ' | ' + @VendorName,
document_1 = ShortDescription, --@ShortDescription,
document_2 = CONVERT(VARCHAR(16), ''),
reference_code = '',
balance = (ExtendedPrice)*-1, -- (@ExtendedPrice)*-1
nat_balance = (ExtendedPrice)*-1, -- (@ExtendedPrice)*-1
nat_cur_code = 'USD',
rate = 1,
posted_flag = 0,
date_posted = 0,
trx_type = 111,
offset_flag = 0,
seg1_code = '2700',
seg2_code = '000',
seg3_code = '',
seg4_code = '',
seq_ref_id = 0,
balance_oper = (ExtendedPrice)-1, -- (@ExtendedPrice)-1
rate_oper = 1,
rate_type_home = @rate_type_home,
rate_type_oper = @rate_type_oper,
org_id = @org_id
FROM #Requisition
-------------------------------------------------------------------------
-------------------------------------------------------------------------
/*
DECLARE CUR CURSOR FOR
SELECT
BudgetUnit,
POChartOfAccount,
'Encumbrance',
SUM(UnitPrice * UnitsOrdered)
FROM Requisition AS Requisition_1
INNER JOIN ((PurchaseOrder
INNER JOIN Requisition ON PurchaseOrder.POID = Requisition.POID)
INNER JOIN RequisitionLineItems
ON Requisition.RequisitionNumber = RequisitionLineItems.RequisitionNumber)
ON Requisition_1.RequisitionNumber = RequisitionLineItems.RequisitionNumber
WHERE PurchaseOrder.POID = @PO_NUMBER
GROUP BY PurchaseOrder.POID,POChartOfAccount,BudgetUnit
OPEN CUR
FETCH NEXT FROM CUR INTO @BudgetUnit,@Account,@ShortDescription, @ExtendedPrice
WHILE @@FETCH_STATUS <> -1
BEGIN
----GET COMPLETE ACCOUNT CODE FOR JOURNAL ENTRY
SET @AccountCode = @account + @BudgetUnit
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
INSERT INTO PLT_DBOH_DATA..gltrxdet(journal_ctrl_num,sequence_id,rec_company_code,company_id,account_code,description,document_1,document_2,reference_code,balance,nat_balance,
nat_cur_code,rate,posted_flag,date_posted,trx_type,offset_flag,seg1_code,seg2_code,seg3_code,seg4_code,seq_ref_id,balance_oper,rate_oper,
rate_type_home,rate_type_oper,org_id)
VALUES(@next_num, @SequenceID, @company_code, @company_id, @AccountCode, SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', @ExtendedPrice, @ExtendedPrice,'USD',1,0,0,111,0, @account, @BudgetUnit,'','',0, @ExtendedPrice,1,@rate_type_home,@rate_type_oper,@org_id)
--INCREMENT SEQUENCE
SET @SequenceID =@SequenceID + 1
INSERT INTO PLT_DBOH_DATA..gltrxdet(journal_ctrl_num,sequence_id,rec_company_code,company_id,account_code,description,document_1,document_2,reference_code,balance,nat_balance,
nat_cur_code,rate,posted_flag,date_posted,trx_type,offset_flag,seg1_code,seg2_code,seg3_code,seg4_code,seq_ref_id,balance_oper,rate_oper,
rate_type_home,rate_type_oper,org_id)
VALUES(@next_num, @SequenceID ,@company_code, @company_id, '2700000', SUBSTRING(@PO_NUMBER + ' | ' + @VendorName,1,255),@ShortDescription, CONVERT(VARCHAR(16), ''),'', (@ExtendedPrice)*-1, (@ExtendedPrice)*-1,'USD',1,0,0,111,0, '2700', '000','','',0, (@ExtendedPrice)-1,1,@rate_type_home,@rate_type_oper,@org_id)
FETCH NEXT FROM CUR INTO @BudgetUnit, @account, @ShortDescription, @ExtendedPrice
END
CLOSE CUR
DEALLOCATE CUR
*/
GO
Might want to add a SET NOCOUNT ON. Also, does this sp need to handle any errors? What about transactional issues?
August 18, 2011 at 10:08 am
ChrisM@Work (8/18/2011)
...This incorporates many of the changes suggested to date and replaces the cursor with a local temporary table...
Troe_atl, we still have to enclose the inserts within a transaction and introduce appropriate error handling. The transaction block will necessitate some shifting around of the code. In order to keep this as simple and helpful as possible, can I suggest that you test the code I've posted, adding any changes (suggested by Sean or toddasd) I've missed, then work on the transaction afterwords? Cheers & good luck.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 18, 2011 at 12:55 pm
Hi guys. I really appreciate all of the help in optimizing this sproc. Chris, I've tested the code you submitted and everything works fine but I need the results to look like the results below. I got the following results by using the cursor in the code I posted. Notice how the sequence_id increments for each record. Also notice how the account_code doesn't get truncated and the 2700000 account number follows each account code.
journal_ctrl_num sequence_id account_code description document_1 balance
JRNL00061865 1 6100505 76023 | Staples, Inc. Encumbrance 392.8
JRNL00061865 2 2700000 76023 | Staples, Inc. Encumbrance -392.8
JRNL00061865 3 6170555 76023 | Staples, Inc. Encumbrance 568.82
JRNL00061865 4 2700000 76023 | Staples, Inc. Encumbrance -568.82
I got the following results with the code you posted. Here the sequence number doesn't increment and the account code gets truncated. I need the results to look like the results above because that's how it is viewed in the front end.
journal_ctrl_num sequence_id account_code description document_1 balance
JRNL00061866 1 6605 76023 | Staples, Inc. Encumbrance 392.8
JRNL00061866 1 6725 76023 | Staples, Inc. Encumbrance 568.82
JRNL00061866 2 2700000 76023 | Staples, Inc. Encumbrance -392.8
JRNL00061866 2 2700000 76023 | Staples, Inc. Encumbrance -568.82
August 18, 2011 at 1:11 pm
That is because the sequence numbers were hard coded in the insert that Chris wrote. I believe you want to maintain the same kind of sequence you had in the original? first insert from table1 = 1 and first insert from table2 = 2. I have an idea of how you could use the ROW_NUMBER windowed function to achieve this. Give me a few and I will toss something together.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply