Solved

Store procedure explanation

Posted on 2014-10-20
3
175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 50

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Postdresql 1 55
Reformat SQL - so SSRS can read the columns 25 46
how would you excute a sproc on another database on the fly 9 35
Estimating my database size 7 51
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

739 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