October 1, 2009 at 12:22 pm
Is it possible to create a SQL Server 2008 view that houses a variable?
For example:
create view V_myView as
Select col_1, col_2, col_3, col_4 from table_a
where col_2 = @variable_1
(reason: we have a complex SQL statement that contains a correlated subquery w/a nested select max function that will not parse in SSIS 2008 data flow, oledb data source --- so we'd like to run the SQL on the Sybase server.. and we'll SELECT from the view in SSIS)
October 1, 2009 at 12:58 pm
Views don't use variables. But all you'd need to do is get rid of your where in the view and use it in the referencing statement.
IE.
create view V_myView as
Select col_1, col_2, col_3, col_4 from table_a
GO
select * from V_myview
where col_2 = @variable_1
October 1, 2009 at 1:04 pm
thanks for the feedback. Unfortunately, we are stuck w/ the SQL as is (it's a huge, 15 table join, using correlated sub-selects w/ a nested SELECT MAX function.. It operates OK in our existing SQL 2K DTS package -- running the huge select against a Sybase ODBC connection.. Now we've ported everything to SQL 2008 SSIS 64-bit. The same SQL statement does not parse in the SSIS designer (w/in the Data Flow's data source)
Was looking to move this large select over onto the actual Sysbase Server and create a view on it.. w/ the capability to selctively pull rows using a variable fed into the view..
October 1, 2009 at 1:11 pm
I've attached the actual SQL statement below. If I comment out the SELECT MAX section, the statement parses appropriately.
/*****
AND (VV.Fund_Valuation_Date =
(SELECT MAX (Fund_Valuation_Date) FROM dbo.Fund_Valuation VVV
WHERE VVV.Fund_Valuation_Date < '2009/09/30' --@DATEPARAM
AND VVV.Fund_Number = V.Fund_Number
AND VVV.Class_Type_Abbr_Name = V.Class_Type_Abbr_Name
AND VVV.Class_Type_Category = V.Class_Type_Category))
****/
SQL statement here:
SELECT DISTINCT RTRIM(F.Fund_Number) AS Fund_Number,
RTRIM(C.Class_Type_Abbr_Name) AS Class_Type_Abbr_Name,
..
..
..
VV.Fund_Valuation_Date AS Prior_Fund_Valuation_Date,
VV.Fund_Actual_Price AS Prior_Fund_Actual_Price
FROM dbo.Fund F INNER JOIN dbo.Fund_Valuation V
ON F.Fund_Number = V.Fund_Number
INNER JOIN dbo.Fund_Class C
ON F.Fund_Number = C.Fund_Number
AND V.Class_Type_Abbr_Name = C.Class_Type_Abbr_Name
AND V.Class_Type_Category = C.Class_Type_Category
INNER JOIN dbo.Fund_Separate_Account S
ON F.Fund_Number = S.Fund_Number
AND V.Class_Type_Abbr_Name = S.Class_Type_Abbr_Name
AND V.Class_Type_Category = S.Class_Type_Category
INNER JOIN dbo.Plan_Table P
ON F.Fund_Number = P.Fund_Number
AND V.Class_Type_Abbr_Name = P.Class_Type_Abbr_Name
AND V.Class_Type_Category = P.Class_Type_Category
LEFT OUTER JOIN dbo.Fund_Dividend D
ON F.Fund_Number = D.Fund_Number
AND V.Fund_Valuation_Date = D.Fund_Dividend_Ex_Date
LEFT OUTER JOIN dbo.Fund_Valuation VV
ON V.Fund_Number = VV.Fund_Number
AND V.Class_Type_Abbr_Name = VV.Class_Type_Abbr_Name
AND V.Class_Type_Category = VV.Class_Type_Category
AND (VV.Fund_Valuation_Date =
(SELECT MAX (Fund_Valuation_Date) FROM dbo.Fund_Valuation VVV
WHERE VVV.Fund_Valuation_Date < '2009/09/30' --@DATEPARAM
AND VVV.Fund_Number = V.Fund_Number
AND VVV.Class_Type_Abbr_Name = V.Class_Type_Abbr_Name
AND VVV.Class_Type_Category = V.Class_Type_Category))
WHERE V.Fund_Valuation_Date = -- @DATEPARM
AND S.System_Abbr_Name IN ('XYZ','ABC')
AND P.System_Abbr_Name IN ('XYZ','ABC')
AND P.Plan_Type_Code <> 'WWW'
October 1, 2009 at 1:55 pm
October 1, 2009 at 2:08 pm
This is a stab in the dark. It likely won't work(or be obnoxiously slow), but try it.
SELECT DISTINCT
RTRIM(F.Fund_Number) AS Fund_Number,
RTRIM(C.Class_Type_Abbr_Name) AS Class_Type_Abbr_Name,
VV.Fund_Valuation_Date AS Prior_Fund_Valuation_Date,
VV.Fund_Actual_Price AS Prior_Fund_Actual_Price
FROM dbo.Fund F
INNER JOIN dbo.Fund_Valuation V ON F.Fund_Number = V.Fund_Number
INNER JOIN dbo.Fund_Class C ON F.Fund_Number = C.Fund_Number
AND V.Class_Type_Abbr_Name = C.Class_Type_Abbr_Name
AND V.Class_Type_Category = C.Class_Type_Category
INNER JOIN dbo.Fund_Separate_Account S ON F.Fund_Number = S.Fund_Number
AND V.Class_Type_Abbr_Name = S.Class_Type_Abbr_Name
AND V.Class_Type_Category = S.Class_Type_Category
INNER JOIN dbo.Plan_Table P ON F.Fund_Number = P.Fund_Number
AND V.Class_Type_Abbr_Name = P.Class_Type_Abbr_Name
AND V.Class_Type_Category = P.Class_Type_Category
LEFT OUTER JOIN dbo.Fund_Dividend D ON F.Fund_Number = D.Fund_Number
AND V.Fund_Valuation_Date = D.Fund_Dividend_Ex_Date
LEFT OUTER JOIN dbo.Fund_Valuation VV ON V.Fund_Number = VV.Fund_Number
AND V.Class_Type_Abbr_Name = VV.Class_Type_Abbr_Name
AND V.Class_Type_Category = VV.Class_Type_Category
AND (VV.Fund_Valuation_Date = (SELECT
MAX(Fund_Valuation_Date)
FROM
dbo.Fund_Valuation VVV
WHERE
VVV.Fund_Valuation_Date < V.Fund_Valuation_Date --@DATEPARAM
AND VVV.Fund_Number = V.Fund_Number
AND VVV.Class_Type_Abbr_Name = V.Class_Type_Abbr_Name
AND VVV.Class_Type_Category = V.Class_Type_Category
))
WHERE -- V.Fund_Valuation_Date = '2009/09/30'-- @DATEPARM
S.System_Abbr_Name IN ('XYZ', 'ABC')
AND P.System_Abbr_Name IN ('XYZ', 'ABC')
AND P.Plan_Type_Code <> 'WWW'
SELECT * FROM yourview WHERE Prior_Fund_Valuation_Date = '2009/09/30'
October 1, 2009 at 2:18 pm
INLINE table-valued functions are essentially views with parameters.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply