Can I not use CREATE VIEW in a DTS package or sp?
-
I've written the code below which seems to do what I want it to do. It parses fine and executes in Query Analyzer, but when I tried to move it into a SQL step in a DTS package, it threw a parsing error ("invalid object name 'max_dates'"). I also got an error when I tried to use it in a stored procedure. Do neither of these two things support CREATE VIEW? And is there an alternate way to accomplish what I'm trying to do?
Thanks.
Code:
-- START
-- create view with dates and sites in temp table
CREATE VIEW max_dates
AS
SELECT Site AS Site_md, MAX(DataDate) AS DataDate_md FROM tblARDataTemp
GROUP BY Site
GO
-- delete the data from
DELETE FROM tblARData
WHERE Site IN (
SELECT Site_md
FROM max_dates)
GO
-- create view that has only the most current data in temp table for each site
CREATE VIEW current_ar
AS
SELECT DISTINCT * FROM tblARDataTemp t
INNER JOIN max_dates md
ON t.Site=md.Site_md AND t.DataDate=md.DataDate_md
GO
-- insert data into tblARData from view current_ar
INSERT INTO tblARData
(DataDate, Region, Site, CustomerNumber, CustomerName, PaymentTerms, TotalAmount, CurrentAmount, Overdue1_30, Overdue31_60, Overdue61_90, Overdue91, CurrencyCode, ConversionFactor, Collector)
SELECT
c.DataDate, c.Region, c.Site, c.CustomerNumber, c.CustomerName, c.PaymentTerms, c.TotalAmount, c.CurrentAmount, c.Overdue1_30, c.Overdue31_60, c.Overdue61_90, c.Overdue91, c.CurrencyCode, v.ConversionFactor, c.Collector
FROM current_ar As c
INNER JOIN tblCurrencyConversion AS v ON c.CurrencyCode = v.CurrencyCode
GO
-- drop views
DROP VIEW current_ar
DROP VIEW max_dates
--END
-
I would create the views once outside of the DTS package and just reference them within the DTS package and sprocs.
-
If your intention is to make the views "temporary", that exist only for the duration of the process, then use derived tables instead of views.
The issue is that SQL batches are parsed, then executed. In a stored procedure, it's all 1 batch, therefore the parser can't find the views, because they don't exist until they've been created in the execution step.
-- delete using EXISTS instead of IN, and referencing
-- the base table instead of a view
DELETE FROM tblARData As ar
WHERE EXISTS (
SELECT *
FROM DataDate_md As md
WHERE md.Site = ar.Site
)
-- insert data into tblARData from derived table for current_ar
INSERT INTO tblARData
(DataDate, Region, Site, CustomerNumber, CustomerName, PaymentTerms, TotalAmount, CurrentAmount, Overdue1_30, Overdue31_60, Overdue61_90, Overdue91, CurrencyCode, ConversionFactor, Collector)
SELECT
c.DataDate, c.Region, c.Site, c.CustomerNumber, c.CustomerName, c.PaymentTerms, c.TotalAmount, c.CurrentAmount, c.Overdue1_30, c.Overdue31_60, c.Overdue61_90, c.Overdue91, c.CurrencyCode, v.ConversionFactor, c.Collector
FROM
(
SELECT DISTINCT * FROM tblARDataTemp t
INNER JOIN (
SELECT Site AS Site_md, MAX(DataDate) AS DataDate_md
FROM tblARDataTemp
GROUP BY Site
 
dtMaxDate -- derived table "dtMaxDate"ON t.Site = md.Site_md AND
t.DataDate = md.DataDate_md
) c -- Derived table "c" equivalent to view for current_ar
INNER JOIN tblCurrencyConversion AS v
ON c.CurrencyCode = v.CurrencyCode
-
I'm not sure I understand this code:
DELETE FROM tblARData As ar
WHERE EXISTS (
SELECT *
FROM DataDate_md As md
WHERE md.Site = ar.Site
)
"DataDate_md" - that's not a table in my database. In my earlier statement I was creating a view that I called max_dates which showed me the distinct sites and maximum date value for each site from my table tblARDataTemp.
Also, What is the key sequence equivalent of that "wink" icon? I'm assuming that was inserted into your response unintentionally.
Thanks.
-
Typo on my part, I typed a column name instead of the tablename:
DELETE FROM tblARData As ar
WHERE EXISTS (
SELECT *
FROM tblARDataTemp As t
WHERE t.Site = ar.Site
)
EXISTS will be more efficient than IN, especially when the IN() was based on a view that had to do aggregation that was unnecessary for the delete.
The "wink" icon was a closing parenthesis, to close off the derived table block.
-
I would code the delete using:
DELETE ar
FROM tblARData As ar
INNER JOIN tblARDataTemp As t
ON t.Site = ar.Site
Is WHERE EXISTS(...) more efficient than this?
-
Yeah, I always get tripped up on that alias syntax using DELETE.
-
>>Is WHERE EXISTS(...) more efficient than this?
Depends on indexing and cardinality of the data.
What is an INNER JOIN going to do in this context if there are 2 or more records in tblARDataTemp with the same Site ? (and we know there are, since the initial view was doing a group by on Site to eliminate dupes).
EXISTS () will resolve to true on 1st occurrence of Site in tblARDataTemp, but what does an INNER JOIN query plan do in a DELETE when there are multiple records of the same Site value being joined to ?
-
PW,
In trying to test both scenarios, the following doesn't work. Any idea why?
declare @tblARData table (site int)
insert @tblARData values(1)
insert @tblARData values(2)
insert @tblARData values(2)
insert @tblARData values(3)
declare @tblARDataTemp table (site int)
insert @tblARDataTemp values(1)
insert @tblARDataTemp values(1)
insert @tblARDataTemp values(2)
DELETE FROM @tblARData As ar
WHERE EXISTS (
SELECT *
FROM @tblARDataTemp As t
WHERE t.Site = ar.Site
)
select * from @tblARData
go
-
Syntax error in my delete code.
DELETE ar
FROM @tblARData As ar
WHERE EXISTS (
SELECT *
FROM @tblARDataTemp As t
WHERE t.Site = ar.Site
)
-
This works:
CREATE table #tblARData (site int)
insert #tblARData values(1)
insert #tblARData values(2)
insert #tblARData values(2)
insert #tblARData values(3)
declare @tblARDataTemp table (site int)
insert @tblARDataTemp values(1)
insert @tblARDataTemp values(1)
insert @tblARDataTemp values(2)
DELETE FROM #tblARData
WHERE EXISTS (
SELECT *
FROM @tblARDataTemp As t
WHERE t.Site = #tblARData.Site
)
select * from #tblARData
go
You cannot always use table variables n the way you use tables.
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply