Sorry if that title is confusing.
Say I have a view on a table. And then say I build a view using that view.
Shouldn't I, theoretically, always be able to decompose a view built on
another view into a single view built on the underlying table(s)? I know
that SQL Server has to do this eventually. Obviously I'm doing something
wrong.
I'm trying to find the stock and fund "postions" (number of shares) for
client accounts as of a given date. We have all asset positions at the end
of each month, and then we get changed postitions whenever they change
during the month.
I know this is not a new problem, but I went ahead and wrote the SQL that I
thought would give the answer. Maybe I should have searched for the common
way of doing this. My real question is why can't I write this as one SQL
statement.
DDL is listed below. I started with a view that returns all end-of-January
positions plus all positions through the given date (which is only 3 days
later, February 3rd). The first view is here. I'm not gonna hard code
dates into my views in production; this is an experiment:
Create View Pos20050203 As
Select SSN_Tin, Acct_Number, Fund_ID, Position_Date, Quantity_ASD
From Positions Where Position_Date between '2005-01-31' and '2005-02-03'
Now that's simple enough. The next step is to find the latest position
date for each position:
SELECT SSN_Tin, Acct_Number, Fund_ID, MAX(Position_Date) AS LastPosDate
FROM Pos20050203
GROUP BY SSN_Tin, Acct_Number, Fund_ID)
The next step... Let me show you my final attempt at putting this into one
SQL statement and you can all flame me on what I'm doing wrong:
Select * From
(Select SSN_Tin, Acct_Number, Fund_ID, Position_Date, Quantity_ASD
From Positions
Where Position_Date between '2005-01-31' and '2005-02-03')
AS Pos20050203
Where Exists
(Select * From
(Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) AS LastPosDate
From Pos20050203
Group by SSN_Tin, Acct_Number, Fund_id) AS Derived
Where Derived.ssn_tin = Pos20050203.ssn_tin
And Derived.acct_number = Pos20050203.acct_number
And Derived.fund_id = Pos20050203.fund_id
And Derived.LastPosDate = Pos20050203.Position_Date)
I get "Invalid object name 'Pos20050203'." I wish QA would tell me WHICH
reference to this name is invalid; probably one in the last Where clause.
The table is certainly named a couple of steps earlier in the process.
How can this be fixed?
Oh, SSN_Tin is char(9), Acct_Number is varchar(20), Fund_ID is varchar(22),
Position_Date is datetime, and Quantity_asd is decimal.
Any help is appreciated.
David Walker...say I have a view on a table. And then say I build a view using
that view. Shouldn't I, theoretically, always be able to decompose a
view built on
another view into a single view built on the underlying table(s)?
...
that's not true. if you build and view then refer to that view in
another query, that's fine, because the first view has been complied.
if you just 'decompose' the first view in the 2nd query, it mgiht not
work. in ur case, the invalid object refers to this section...
Where Exists
(Select * From
(Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) AS
LastPosDate
>From Pos20050203
..
because Pos20050203 is in the same query, when sql executes the query,
it's not there yet.
if you use stored procedure and declare the temp table for the first
part of the query then it will work. (or you can create a view for the
first part)|||"DWalker" <none@.none.com> wrote in message
news:uImJFrRFFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Sorry if that title is confusing.
> Say I have a view on a table. And then say I build a view using that
view.
> Shouldn't I, theoretically, always be able to decompose a view built on
> another view into a single view built on the underlying table(s)?
> Create View Pos20050203 As
> Select SSN_Tin, Acct_Number, Fund_ID, Position_Date, Quantity_ASD
> From Positions Where Position_Date between '2005-01-31' and '2005-02-03'
> SELECT SSN_Tin, Acct_Number, Fund_ID, MAX(Position_Date) AS LastPosDate
> FROM Pos20050203
> GROUP BY SSN_Tin, Acct_Number, Fund_ID)
Generally, just replace the viewname with the select that the view
represents.
Note that you must use an alias on the subquery.
So:
SELECT SSN_Tin, Acct_Number, Fund_ID, MAX(Position_Date) AS LastPosDate
FROM ( SELECT Select SSN_Tin, Acct_Number, Fund_ID, Position_Date,
Quantity_ASD
FROM Positions
WHERE Position_Date between '2005-01-31' and '2005-02-03') t
GROUP BY SSN_Tin, Acct_Number, Fund_ID
Good Luck,
Jim|||"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in news:b424$4214e40a
$432498ca$26444@.allthenewsgroups.com:
> Generally, just replace the viewname with the select that the view
> represents.
Yes, but I hate repeating clauses, and sometimes I get parentheses in the
wrong places.
I now have this, and it works. (Pay no attention to the Select *)
Select * from
(Select * From PositionView Where Position_Date Between '2005-01-31' and
'2005-02-03') As PositionSlice
Where Exists
(Select * From
(Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) as LastDate
From PositionView
Where Position_Date Between '2005-01-31' and '2005-02-03'
Group by ssn_tin, acct_number, fund_id) As LastPos
Where PositionSlice.SSN_Tin = LastPos.SSN_Tin
And PositionSlice.Acct_Number = LastPos.Acct_Number
And PositionSlice.Fund_ID = LastPos.Fund_ID
And PositionSlice.Position_Date = LastPos.LastDate)
************
Question: Is there any way with table aliases to avoid repeating the
"Position_Date Between" clause in the query? I know it might not be
faster, but it will be simpler. I can't figure out how to do that.
Thanks.
David Walker|||"DWalker" <none@.none.com> wrote in message
news:euOEULSFFHA.3312@.TK2MSFTNGP15.phx.gbl...
> Select * from
> (Select * From PositionView Where Position_Date Between '2005-01-31' and
> '2005-02-03') As PositionSlice
> Where Exists
> (Select * From
> (Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) as LastDate
> From PositionView
> Where Position_Date Between '2005-01-31' and '2005-02-03'
> Group by ssn_tin, acct_number, fund_id) As LastPos
> Where PositionSlice.SSN_Tin = LastPos.SSN_Tin
> And PositionSlice.Acct_Number = LastPos.Acct_Number
> And PositionSlice.Fund_ID = LastPos.Fund_ID
> And PositionSlice.Position_Date = LastPos.LastDate)
Maybe Try something like this:
Select * from PositionView p
inner join (Select SSN_Tin, Acct_number, Fund_id,
Max(Position_Date) as lastdate
From positionView where Position_date
Between '20050131' and '20050203') l
on p.SSN_Tin = l.SSN_Tin
and p.Acct_number = l.Acct_number
and p.Fund_Id = l.Fund_Id
and p.Position_Date = l.LastDate
Good Luck,
Jim|||That doesn't work. See below...
"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in
news:88153$4214fc66$432498ca$7045@.allthe
newsgroups.com:
> "DWalker" <none@.none.com> wrote in message
> news:euOEULSFFHA.3312@.TK2MSFTNGP15.phx.gbl...
>
>
> Maybe Try something like this:
> Select * from PositionView p
> inner join (Select SSN_Tin, Acct_number, Fund_id,
> Max(Position_Date) as lastdate
> From positionView where Position_date
> Between '20050131' and '20050203') l
> on p.SSN_Tin = l.SSN_Tin
> and p.Acct_number = l.Acct_number
> and p.Fund_Id = l.Fund_Id
> and p.Position_Date = l.LastDate
> Good Luck,
> Jim
>
Your suggestion returns these errors:
Server: Msg 8118, Level 16, State 1, Line 1
Column 'positionView.SSN_TIN' is invalid in the select list because it
is not contained in an aggregate function and there is no GROUP BY
clause. Server: Msg 8118, Level 16, State 1, Line 1
Column 'positionView.ACCT_NUMBER' is invalid in the select list because
it is not contained in an aggregate function and there is no GROUP BY
clause. Server: Msg 8118, Level 16, State 1, Line 1
Column 'positionView.FUND_ID' is invalid in the select list because it
is not contained in an aggregate function and there is no GROUP BY
clause.
I'm wondering if it's even possible to do what I'm doing wihtout
spelling out the "Position_Date Between" clause twice...
DAvid|||On Thu, 17 Feb 2005 10:59:36 -0800, DWalker wrote:
>"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in news:b424$4214e40a
>$432498ca$26444@.allthenewsgroups.com:
>
>Yes, but I hate repeating clauses, and sometimes I get parentheses in the
>wrong places.
>I now have this, and it works. (Pay no attention to the Select *)
(snip)
>Question: Is there any way with table aliases to avoid repeating the
>"Position_Date Between" clause in the query? I know it might not be
>faster, but it will be simpler. I can't figure out how to do that.
Hi David,
You can't always prevent having to repeat things. But in your case, I
think it is possible. Looking at just your query, without knowing your
tables and therefore unable to test it, I think you should get the same
results from
Select Column1, Column2, ..., ColumnN from
PositionView As PositionSlice
Where Exists
(Select * From
(Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) as LastDate
From PositionView
Where Position_Date Between '2005-01-31' and '2005-02-03'
Group by ssn_tin, acct_number, fund_id) As LastPos
Where PositionSlice.SSN_Tin = LastPos.SSN_Tin
And PositionSlice.Acct_Number = LastPos.Acct_Number
And PositionSlice.Fund_ID = LastPos.Fund_ID
And PositionSlice.Position_Date = LastPos.LastDate)
Or even
SELECT Column1, Column2, ..., ColumnN
FROM PositionView AS PositionSlice
WHERE Position_Date =
(SELECT MAX(Position_Date)
FROM Position_View AS LastPos
WHERE LastPos.Position_Date BETWEEN '2005-01-31' AND '2005-02-03'
AND PositionSlice.SSN_Tin = LastPos.SSN_Tin
AND PositionSlice.Acct_Number = LastPos.Acct_Number
AND PositionSlice.Fund_ID = LastPos.Fund_ID)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"DWalker" <none@.none.com> wrote in message
news:#28IjwTFFHA.1392@.tk2msftngp13.phx.gbl...
> That doesn't work. See below...
> Your suggestion returns these errors:
> Server: Msg 8118, Level 16, State 1, Line 1
> Column 'positionView.SSN_TIN' is invalid in the select list because it
> is not contained in an aggregate function and there is no GROUP BY
> clause. Server: Msg 8118, Level 16, State 1, Line 1
> Column 'positionView.ACCT_NUMBER' is invalid in the select list because
> it is not contained in an aggregate function and there is no GROUP BY
> clause. Server: Msg 8118, Level 16, State 1, Line 1
> Column 'positionView.FUND_ID' is invalid in the select list because it
> is not contained in an aggregate function and there is no GROUP BY
> clause.
So add the group by clause, keep in mind, I don't have your tables and can't
test these:
Select * from PositionView p
inner join (Select SSN_Tin, Acct_number, Fund_id, Max(Position_Date)
as lastdate
From positionView where Position_date Between
'20050131' and '20050203'
Group by SSN_Tin, Acct_number, Fund_id) t
on p.SSN_Tin = l.SSN_Tin
and p.Acct_number = l.Acct_number
and p.Fund_Id = l.Fund_Id
and p.Position_Date = l.LastDate|||Thanks, I'll look at that.
David
Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
news:agdb11tunlg45j5o3bru281vc7dpjbc0k2@.
4ax.com:
> On Thu, 17 Feb 2005 10:59:36 -0800, DWalker wrote:
>
> (snip)
> Hi David,
> You can't always prevent having to repeat things. But in your case, I
> think it is possible. Looking at just your query, without knowing your
> tables and therefore unable to test it, I think you should get the
> same results from
> Select Column1, Column2, ..., ColumnN from
> PositionView As PositionSlice
> Where Exists
> (Select * From
> (Select SSN_Tin, Acct_Number, Fund_ID, Max(Position_Date) as LastDate
> From PositionView
> Where Position_Date Between '2005-01-31' and '2005-02-03'
> Group by ssn_tin, acct_number, fund_id) As LastPos
> Where PositionSlice.SSN_Tin = LastPos.SSN_Tin
> And PositionSlice.Acct_Number = LastPos.Acct_Number
> And PositionSlice.Fund_ID = LastPos.Fund_ID
> And PositionSlice.Position_Date = LastPos.LastDate)
> Or even
> SELECT Column1, Column2, ..., ColumnN
> FROM PositionView AS PositionSlice
> WHERE Position_Date =
> (SELECT MAX(Position_Date)
> FROM Position_View AS LastPos
> WHERE LastPos.Position_Date BETWEEN '2005-01-31' AND
> '2005-02-03' AND PositionSlice.SSN_Tin = LastPos.SSN_Tin
> AND PositionSlice.Acct_Number = LastPos.Acct_Number
> AND PositionSlice.Fund_ID = LastPos.Fund_ID)
> Best, Hugo|||Thanks, I'll look at that. You have an a alias t where I think you mean
l, but otherwise it looks OK. (I don't like one-letter aliases, but
that's just my preference. Something like LastPos is more meaningful
than l. And l looks like I anyway, depending on the font. But thanks
for the help!
David Walker
"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in
news:beab9$4215f005$432498ca$15926@.allth
enewsgroups.com:
> "DWalker" <none@.none.com> wrote in message
> news:#28IjwTFFHA.1392@.tk2msftngp13.phx.gbl...
> So add the group by clause, keep in mind, I don't have your tables and
> can't test these:
> Select * from PositionView p
> inner join (Select SSN_Tin, Acct_number, Fund_id,
> Max(Position_Date)
> as lastdate
> From positionView where Position_date Between
> '20050131' and '20050203'
> Group by SSN_Tin, Acct_number, Fund_id) t
> on p.SSN_Tin = l.SSN_Tin
> and p.Acct_number = l.Acct_number
> and p.Fund_Id = l.Fund_Id
> and p.Position_Date = l.LastDate
>
