An ugly self-join (oh, that doesnt work!)

Hi again, all...here I am again, trying to work on my CloseIndex thang again...Same subject, different tack...

Basically, I have two tables...for the sake of simplicity, let me define them as:

PortfolioIndex
PortfolioID int
CreateDate smalldatetime
CloseIndex float

PortfolioPerformance
PortfolioID int
CreateDate smalldatetime
PrevDate smalldatetime
DailyPerChg float

UPDATE PortfolioIndex
SET CloseIndex = CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
FROM PortfolioIndex AS P INNER JOIN PortfolioIndex AS PPI on (P.PortfolioID = PPI.PortfolioID), PortfolioPerformance AS PP
WHERE (P.PortfolioID = PP.PortfolioID) AND
((P.CreateDate = PP.CreateDate) AND
(P.CreateDate = @.CreateDate) AND
(PPI.CreateDate = PP.PrevDate))

What I am trying to do is...get the previous day's portfolioIndex row's CloseIndex and create a new one for today's row.

As ugly as it is, it works when I execute it in the SQL Query Analyzer, but when I try to create the stored procedure, the syntax check complains that the PortfolioIndex reference at the UPDATE... part is AMBIGUOUS...yet when I define it in the SP as P.PortfolioIndex, it fails at run time saying there is no object named P.PortfolioIndex (well, of course there isn't!).

How can I make this work (and if possible, make it prettier too! *L* ;) )well, now I see it doesn't really work in the sql analyzer either...but did on a previous iteration (before I added the inner join).

Still, the idea/question is the same...is there a less kludg-ey way to do the self-join to get the previous day's row and update the new row based on data from the old and the 2nd (PortfolioPerformance) table?

Thanks,
Paul|||select /*PI.CloseIndex = */ PI.PortfolioID, CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PI, PortfolioIndex PPI, PortfolioPerformance PP
where ((PI.PortfolioID = PPI.PortfolioID AND
PI.PortfolioID = PP.PortfolioID))AND
((PI.CreateDate = PP.CreateDate) AND
(PI.CreateDate = @.CreateDate) AND
(PPI.CreateDate = PP.PrevDate))

It worked this way in the SQL Analyzer...when I was testing without the update...so...how can I reference the table in an update without being ambiguous? I can't seem to find any example code anywhere about updates with self-joins...can it be done?

Thanks,
Paul|||Update table
set column = (select ...)|||Thanks...*hanging head*

It must be time to go home...

Thanks for taking the time to point me in the direction of the forest once again...|||I've been trying to get the select correct as suggested, but can't get it to work for me.

Possibly because one requirement is not plain from my previous description...

There will be multiple PortfolioIndex rows there each day (since there will be more than one PortfolioID on each day).

Modifying my update as suggested results in multiple results being returned from the sub-query, which I cannot figure out how to apply to each individual PortfolioIndex row! *grrrr*

Here is my modified query:

DECLARE @.CreateDate smalldatetime
DECLARE @.PrevDate smalldatetime
SET @.CreateDate = '2004-02-13'
SET @.PrevDate = '2004-02-12'

Update PortfolioIndex
Set CloseIndex = (
select CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PPI, PortfolioPerformance PP
where (PPI.PortfolioID = PP.PortfolioID)AND
(PP.CreateDate = @.CreateDate) AND
(PPI.CreateDate = @.PrevDate))

The result is:
Server: Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Any helpful suggestions?|||anyone? I know I must be missing something obvious, but can't see it...
how does one update a table joined to itself in the select clause?

Originally posted by TallCowboy0614
I've been trying to get the select correct as suggested, but can't get it to work for me.

Possibly because one requirement is not plain from my previous description...

There will be multiple PortfolioIndex rows there each day (since there will be more than one PortfolioID on each day).

Modifying my update as suggested results in multiple results being returned from the sub-query, which I cannot figure out how to apply to each individual PortfolioIndex row! *grrrr*

Here is my modified query:

DECLARE @.CreateDate smalldatetime
DECLARE @.PrevDate smalldatetime
SET @.CreateDate = '2004-02-13'
SET @.PrevDate = '2004-02-12'

Update PortfolioIndex
Set CloseIndex = (
select CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PPI, PortfolioPerformance PP
where (PPI.PortfolioID = PP.PortfolioID)AND
(PP.CreateDate = @.CreateDate) AND
(PPI.CreateDate = @.PrevDate))

The result is:
Server: Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Any helpful suggestions?|||The error say it all...how can you update a column with n results...you need to make sure the query only returns 1
per row

Forget the update and just focus on the select to make sure it's returning what you need.sql

An odd quandry.

So I've set up 3 tables for some recursive data verification for an
object link embedding procedure.
Basically importing filenames.

The three fields in question are identical.

Document_link.key2_value
document_link_staging.key2_value
document_link_storage.key2_value

And these three fields are populated from a substring of the filenames
which are generated in another table.

Filenametbl.pickno

here is the rub.

If I have 100 identical records in the document link tables with
key2_values that are in Filenametbl, and three hundred records in
Filenametbl, then this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging) and pickno not in (select key2_value from
document_link_storage)

should return 200 records.
It returns 0 records.

So while this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging)

returns 200 records in this scenario,

this query returns 0:
select * from Filenametbl where pickno not in (select key2_value from
document_link_storage)

I am trying to figure out why that is, as the casting for the
key2_values is exactly the same (varchar(255))

Can anybody tell me how to remedy this sort of thing, as its been
bugging me for about 2 months.
I've been able to work around it, but what it is... is just terribly
ineffiecient.Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead of
WHERE ... NOT IN.

select
*
from
Filenametbl f
where not exists (select * from
document_link_staging dls
where dls.key2_value = f.pickno)
and not exists (select * from
document_link_storage dls
where dls.key2_value = f.pickno)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@.gmail.com> wrote in message
news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
So I've set up 3 tables for some recursive data verification for an
object link embedding procedure.
Basically importing filenames.

The three fields in question are identical.

Document_link.key2_value
document_link_staging.key2_value
document_link_storage.key2_value

And these three fields are populated from a substring of the filenames
which are generated in another table.

Filenametbl.pickno

here is the rub.

If I have 100 identical records in the document link tables with
key2_values that are in Filenametbl, and three hundred records in
Filenametbl, then this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging) and pickno not in (select key2_value from
document_link_storage)

should return 200 records.
It returns 0 records.

So while this query:
select * from Filenametbl where pickno not in (select key2_value from
document_link_staging)

returns 200 records in this scenario,

this query returns 0:
select * from Filenametbl where pickno not in (select key2_value from
document_link_storage)

I am trying to figure out why that is, as the casting for the
key2_values is exactly the same (varchar(255))

Can anybody tell me how to remedy this sort of thing, as its been
bugging me for about 2 months.
I've been able to work around it, but what it is... is just terribly
ineffiecient.|||Hey, whoa, that works.
You're the man Tom.

So just use EXISTS when there are nulls in the select list?
Kinda like coalesce, but for subqueries?

Tom Moreau wrote:
> Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead of
> WHERE ... NOT IN.
> select
> *
> from
> Filenametbl f
> where not exists (select * from
> document_link_staging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_storage dls
> where dls.key2_value = f.pickno)
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@.gmail.com> wrote in message
> news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
> So I've set up 3 tables for some recursive data verification for an
> object link embedding procedure.
> Basically importing filenames.
>
> The three fields in question are identical.
> Document_link.key2_value
> document_link_staging.key2_value
> document_link_storage.key2_value
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
> Filenametbl.pickno
> here is the rub.
> If I have 100 identical records in the document link tables with
> key2_values that are in Filenametbl, and three hundred records in
> Filenametbl, then this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging) and pickno not in (select key2_value from
> document_link_storage)
> should return 200 records.
> It returns 0 records.
> So while this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging)
> returns 200 records in this scenario,
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_storage)
> I am trying to figure out why that is, as the casting for the
> key2_values is exactly the same (varchar(255))
> Can anybody tell me how to remedy this sort of thing, as its been
> bugging me for about 2 months.
> I've been able to work around it, but what it is... is just terribly
> ineffiecient.|||(KyussWren@.gmail.com) writes:
> Hey, whoa, that works.
> You're the man Tom.
> So just use EXISTS when there are nulls in the select list?
> Kinda like coalesce, but for subqueries?

EXISTS and NOT EXISTS have wider applicability than so. You also need
EXISTS / NOT EXISTS when the condition involves more than one column.
IN + subquery is mainly something I use when I'm writing ad hoc-queries
and I'm lazy. In programming code I use EXISTS / NOT EXISTS 90% of
the time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm a big EXISTS fan. The NULLs here are to do with the column you chose in
your subquery. I wouldn't think of it like COALESCE. Basically, NULL <>
anything, even another NULL. An IN predicate can be broken down like this:

x IN (1, 2, 3 null)

... means:

x = 1 or x = 2 or x = 3 or x = null

So, if x is 1, 2 or 3, it will be true. If x is null, then the result is
false, since x is really unknown and not equal to anything.

Now consider this:

x NOT IN (1, 2, 3 null)

... means:

x <> 1 and x <> 2 and x <> 3 and x <> null

Google de Morgan's Law.

What if x is 4? All conditions must be met. It passes the first 3, but
fails on the last, since 4 <> null is unknown, and is treated as false.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@.gmail.com> wrote in message
news:1146665188.587472.63170@.v46g2000cwv.googlegro ups.com...
Hey, whoa, that works.
You're the man Tom.

So just use EXISTS when there are nulls in the select list?
Kinda like coalesce, but for subqueries?

Tom Moreau wrote:
> Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead
> of
> WHERE ... NOT IN.
> select
> *
> from
> Filenametbl f
> where not exists (select * from
> document_link_staging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_storage dls
> where dls.key2_value = f.pickno)
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@.gmail.com> wrote in message
> news:1146621327.262902.130050@.v46g2000cwv.googlegr oups.com...
> So I've set up 3 tables for some recursive data verification for an
> object link embedding procedure.
> Basically importing filenames.
>
> The three fields in question are identical.
> Document_link.key2_value
> document_link_staging.key2_value
> document_link_storage.key2_value
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
> Filenametbl.pickno
> here is the rub.
> If I have 100 identical records in the document link tables with
> key2_values that are in Filenametbl, and three hundred records in
> Filenametbl, then this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging) and pickno not in (select key2_value from
> document_link_storage)
> should return 200 records.
> It returns 0 records.
> So while this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging)
> returns 200 records in this scenario,
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_storage)
> I am trying to figure out why that is, as the casting for the
> key2_values is exactly the same (varchar(255))
> Can anybody tell me how to remedy this sort of thing, as its been
> bugging me for about 2 months.
> I've been able to work around it, but what it is... is just terribly
> ineffiecient.

Always get one more blank page after group footer?

hi,

I have a report, basically, it has a table, and a group, and I checked the "Page break " at end in the group properties window. But when I run the report, between two group, there always is one more blank page there which is not what I want. If I clear that checkbox, the second data group did not change to a new page.
So, I guess I should check the checkbox, but how to remove the blank page?

Thanks.

Hi,

You're right with checking the Checkbox, but make sure that your width of the page is not more than the standart page-width, which is 17.5.

I tried hard before i found it out ;-)

|||

hey, thank you so much.
This is exactly what is happening for me.
If the width is in inch, in layout page, we need to change the width <= 6.5in
Thanks again.

|||Oh, sorry, I didn't think about the metric system |||hey, what about if im printing the report using a landscape format?

the width will definitely be more than 6.5 in, but i need it to be wider since im printing in a landscae format

Always get one more blank page after group footer?

hi,

I have a report, basically, it has a table, and a group, and I checked the "Page break " at end in the group properties window. But when I run the report, between two group, there always is one more blank page there which is not what I want. If I clear that checkbox, the second data group did not change to a new page.
So, I guess I should check the checkbox, but how to remove the blank page?

Thanks.

Hi,

You're right with checking the Checkbox, but make sure that your width of the page is not more than the standart page-width, which is 17.5.

I tried hard before i found it out ;-)

|||

hey, thank you so much.
This is exactly what is happening for me.
If the width is in inch, in layout page, we need to change the width <= 6.5in
Thanks again.

|||Oh, sorry, I didn't think about the metric system |||hey, what about if im printing the report using a landscape format?

the width will definitely be more than 6.5 in, but i need it to be wider since im printing in a landscae format