Solved

Store procedure explanation

Posted on 2014-10-20
3
168 Views
Last Modified: 2014-10-22
Could someone tell me what does this do?


-- GET JT NUMBER AND SALES ORDER STATUS
UPDATE #ForecastSolution
SET JTNumber = SDURAB
,OrderStatus = SDNXTR
FROM F42119
WHERE OrderNum = SDDOCO
AND SDURCD = 'JT'
AND SDURAB <> 0

ALTER PROCEDURE [dbo].[Acct_ForecastSolutionEvaluation]



AS
BEGIN
SET NOCOUNT ON;
--SET STATISTICS IO ON;
--SET STATISTICS TIME ON;


IF OBJECT_ID('TEMPDB..#ForecastSolution') IS NOT NULL
BEGIN
	DROP TABLE #ForecastSolution;
END

CREATE TABLE #ForecastSolution
(
	 OrderNum		INT
	,OrderType		VARCHAR(2)
	,SoldTo			VARCHAR(8)	
	,CustomerName	VARCHAR(40)
	,BusinessUnit	VARCHAR(12)
	,ShortItem		INT
	,LongItem		VARCHAR(25)
	,OrderStatus	VARCHAR(3)
	,JTNumber		INT
	,PubTypeCode	VARCHAR(4)
	,ProdPubType	VARCHAR(1975)
	,ConfigType		VARCHAR(8)
	,Description2	VARCHAR(1975)
	,Brand			VARCHAR(1975)
	,[PageCount]	VARCHAR(1975)
	,Qty			VARCHAR(1975)
	,ShippedQTY		DECIMAL(11,2)
	,RevenueJT		VARCHAR(1975)
	,ExtendedAmt	DECIMAL(11,2)
);


INSERT INTO #ForecastSolution(OrderNum,OrderType,SoldTo,CustomerName,BusinessUnit,ShortItem,ShippedQTY,Description2,ExtendedAmt,PubTypeCode)
SELECT
OrderNum
,OrderType
,AddressNum
,cName
,BusinessUnit
,ShortItemNum
,QuantityShipped
,DescriptionLine2
,ExtendedPrice
,ProdPubType
FROM dbo.Acct_SalesStatementRD
WHERE GL_Date >= '20140101'

-- ADD OPEN ORDER
-- FROM F4211



-- GET JT NUMBER AND SALES ORDER STATUS
UPDATE #ForecastSolution
SET JTNumber = SDURAB
,OrderStatus = SDNXTR
FROM F42119
WHERE OrderNum = SDDOCO
AND SDURCD = 'JT'
AND SDURAB <> 0

-- GET JT NUMBER AND SALES ORDER STATUS
UPDATE #ForecastSolution
SET JTNumber = SDURAB
,OrderStatus = SDNXTR
FROM F4211
WHERE OrderNum = SDDOCO
AND SDURCD = 'JT'
AND SDURAB <> 0

Open in new window

0
Comment
Question by:yguyon28
3 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 166 total points
ID: 40391938
Changes (UPDATE) the value in temp table #ForecastSolution, column JTNumber and OrderStatus, to the values in tableF42119, columns  SDURAB and SDNXTR (respectively), only for the rows WHERE the value in column OrderNum = the value in column SDDOCO, SDURCD = 'JT', and SDURAB <> (does not equal) 0.

Looks like the SP you posted creates this temp table based on the SELECT you see after the INSERT statement, and then makes two updates, but then doesn't do anything with the table before the proc ends.
0
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40391940
The piece of code that you posted it's updating two fields (JTNumber  and OrderStatus ) from a temporary table (#ForecastSolution) with the values of two other fields (SDURAB and SDNXTR respectively) from a table named F42119 and only for the records that meet the criteria (OrderNum = SDDOCO  AND SDURCD = 'JT' AND SDURAB <> 0).
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 167 total points
ID: 40393220
Temporary-table #ForecastSolution is joined to table F42119 (via OrderNum = SDDOCO), this result-set is further filtered by the where-clause (SDURCD = 'JT' AND SDURAB <> 0).  If the filtered result-set has any rows remaining then you will get 2 fields updated in #ForecastSolution (JTNumber and OrderStatus) by values from F42119 (SDURAB and SDNXTR, respectively).

When more than one table is present in a query (select, insert, update, delete) then I like to use table-aliases, for readability (I also like sql-92 syntax):
UPDATE	a
SET	JTNumber		=	b.SDURAB
,	OrderStatus		=	b.SDNXTR
FROM	#ForecastSolution	a
JOIN	F42119			b	ON	a.OrderNum = b.SDDOCO
WHERE	b.SDURCD = 'JT'
AND	b.SDURAB <> 0

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now