Four table: Securities, Positions, ExchangeList and ExchangeListMember
Securities has an SecurityID field and the exchange it is traded on.
Positions has a SecurityID and an Account.
ExchangeList is used to define the exchanges that an account is permitted to
own securities on. The ExchangeList.ExchangeListCode would contain an
account code.
ExchangeListMember contains the specific exchanges permitted for an account.
For example Account A can own securities on the NYS and BUE exchanges.
I have created a temp table called #TempPositions which contains all
positions. I want to update #TempPositions by placing an asterisk before the
SecurityID in records where the security is NOT permitted because the
securitys' exchange is NOT permissible.
UPDATE #TempPositions SET #TempPositions.SecurityID = '*' + #TempPositions
.SecurityID
WHERE .....
Here are my expected results for SELECT * FROM #TempPositions after the
update.
1,A
*2,A
3,A
3,B
4,B
*5,B
6,C
7,C
*1,D
*2,D
More narrative:
Account A is permitted to own securities on the NYS and BUE exchanges. It
has a position with SecurityID =2 which is traded on CBT. This position is
restricted.
Account B is permitted to own securities on the BUE and CPH exchanges. It
has a position with SecurityID =5 which is traded on NAS. This position is
restricted.
Account C does not have an ExchangeList. No restrictions.
Account D is permitted to own securities on the NAS exchange only. It has a
position with SecurityID =1 which is traded on NYS. It has a position with
SecurityID =2 which is traded on CBT .These positions is restricted.
This would be easier for me if the ExchangeList table defined restricted
exchanges rather than permitted exchanges. Any help would be appreciated.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Securities]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Positions]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ExchangeList]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[ExchangeList]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ExchangeListMember]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ExchangeListMember]
GO
CREATE TABLE [dbo].[Securities] (
[SecurityID] [int] NOT NULL ,
[Exchange] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Positions] (
[SecurityID] [int] NOT NULL ,
[Account] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ExchangeList] (
[ExchangeListCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ExchangeListName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ExchangeListMember] (
[ExchangeListCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Exchange] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[#TempPositions] (
[SecurityID] [int] NOT NULL ,
[Account] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Securities] ADD
CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
(
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] ADD
CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
(
[SecurityID],
[Account]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ExchangeList] ADD
CONSTRAINT [PK_ExchangeList] PRIMARY KEY CLUSTERED
(
[ExchangeListCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ExchangeListMember] ADD
CONSTRAINT [PK_ExchangeListMember] PRIMARY KEY CLUSTERED
(
[ExchangeListCode],
[Exchange]
) ON [PRIMARY]
GO
INSERT Securities (SecurityID,Exchange) VALUES (1,'NYS')
INSERT Securities (SecurityID,Exchange) VALUES (2,'CBT')
INSERT Securities (SecurityID,Exchange) VALUES (3,'BUE')
INSERT Securities (SecurityID,Exchange) VALUES (4,'CPH')
INSERT Securities (SecurityID,Exchange) VALUES (5,'NAS')
INSERT Securities (SecurityID,Exchange) VALUES (6,'IRL')
INSERT Securities (SecurityID,Exchange) VALUES (7,'JPN')
INSERT Securities (SecurityID,Exchange) VALUES (8,'KOR')
GO
INSERT Positions (SecurityID,Account)VALUES (1,'A')
INSERT Positions (SecurityID,Account)VALUES (2,'A')
INSERT Positions (SecurityID,Account)VALUES (3,'A')
INSERT Positions (SecurityID,Account)VALUES (3,'B')
INSERT Positions (SecurityID,Account)VALUES (4,'B')
INSERT Positions (SecurityID,Account)VALUES (5,'B')
INSERT Positions (SecurityID,Account)VALUES (6,'C')
INSERT Positions (SecurityID,Account)VALUES (7,'C')
INSERT Positions (SecurityID,Account)VALUES (1,'D')
INSERT Positions (SecurityID,Account)VALUES (2,'D')
GO
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('A', 'A
Permissible Exchanges')
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('B', 'B
Permissible Exchanges')
INSERT ExchangeList (ExchangeListCode,ExchangeListName) VALUES ('D', 'D
Permissible Exchanges')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('A','NYS')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('A','BUE')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','BUE')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','CPH')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('B','NAS')
INSERT ExchangeListMember (ExchangeListCode,Exchange) VALUES ('D','NAS')
INSERT #TempPositions (SecurityID,Account)(SELECT * FROM Positions)Terri wrote:
Thanks for the DDL.
I tried it out but I'm
.
Your narrative explanation doesn't seem to completely fit the data you
supplied or I'm looking at it the wrong way.
However I think this is what you're looking for:
alter table #TempPositions add Restricted bit default 0 NOT NULL
UPDATE #TempPositions SET #TempPositions.Restricted = 1
WHERE securityID not in (select securities.SecurityID from
ExchangeListMember inner join securities on securities.SecurityID =
#TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
and securities.Exchange = ExchangeListMember.Exchange)
You cannot add a asterix to a integer column so I added a column named
Restricted and flag it 1 if it is.
HTH,
Stijn Verrept.|||Thanks Stijn, Sorry about me trying to add an asterisk to an integer field.
Altering the table is fine.
I had tried something like your solution but the problem is with Account C.
Account C doesn't have an ExchangeList so securities should never be
restricted. The subselect somehow needs to take into account "accounts" with
no ExchangeList and make sure the securities of those accounts are not
flagged as restricted.
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:1sqdnTV81ZHwoDreRVny0A@.scarlet.biz...
> Terri wrote:
> Thanks for the DDL.
> I tried it out but I'm
.
> Your narrative explanation doesn't seem to completely fit the data you
> supplied or I'm looking at it the wrong way.
> However I think this is what you're looking for:
> alter table #TempPositions add Restricted bit default 0 NOT NULL
> UPDATE #TempPositions SET #TempPositions.Restricted = 1
> WHERE securityID not in (select securities.SecurityID from
> ExchangeListMember inner join securities on securities.SecurityID =
> #TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
> and securities.Exchange = ExchangeListMember.Exchange)
>
> You cannot add a asterix to a integer column so I added a column named
> Restricted and flag it 1 if it is.
> --
> HTH,
> Stijn Verrept.|||Terri wrote:
> Thanks Stijn, Sorry about me trying to add an asterisk to an integer
> field. Altering the table is fine.
> I had tried something like your solution but the problem is with
> Account C. Account C doesn't have an ExchangeList so securities
> should never be restricted. The subselect somehow needs to take into
> account "accounts" with no ExchangeList and make sure the securities
> of those accounts are not flagged as restricted.
Aha! That's probably what I didn't understand, then this will be
better :)
UPDATE #TempPositions SET #TempPositions.Restricted = 1
WHERE securityID not in (select securities.SecurityID from
ExchangeListMember inner join securities on securities.SecurityID =
#TempPositions.SecurityID and ExchangeListCode = #TempPositions.Account
and securities.Exchange = ExchangeListMember.Exchange)
and (select count(*) from ExchangeListMember where ExchangeListCode =
#TempPositions.Account) > 0
HTH,
Stijn Verrept.|||"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:T9qdnWst9ZU81TrenZ2dnUVZ8qidnZ2d@.sc
arlet.biz...
> Aha! That's probably what I didn't understand, then this will be
> better :)
Perfect, thanks so much.|||>> I have created a temp table called #TempPositions which contains all posi
tions. I want to update #TempPositions by placing an asterisk [in violation
of the rule about not formatting display data in the database!!] before the
SecurityID in records [sic
] where the security is NOT permitted because the securitys' exchange is NOT
permissible. <<
Have you thought about using a VIEW that would always be up to date,
instead of constantly updating a temp table in proprietary syntax?
But the real point is that this is a constraint and needs to be done
with REFERENCES clauses and a proper design -- NOT a temp table at all!
Also, ask yourself why you have two tables with the SAME structure in
violation of the rules of any data modeling?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135050199.293240.265890@.g14g2000cwa.googlegroups.com...
positions. I want to update #TempPositions by placing an asterisk [in
violation of the rule about not formatting display data in the database!!]
before the SecurityID in records [sic] where the security is NOT permitted
because the securitys' exchange is NOT permissible. <<
> Have you thought about using a VIEW that would always be up to date,
> instead of constantly updating a temp table in proprietary syntax?
> But the real point is that this is a constraint and needs to be done
> with REFERENCES clauses and a proper design -- NOT a temp table at all!
> Also, ask yourself why you have two tables with the SAME structure in
> violation of the rules of any data modeling?
>
My employer strongly discourages me from presenting real data or table
structure so I may post a problem that helps me solve my issue but doesn't
reveal any information about my data, data structure, or systems. I suspect
others do this is well so I don't think it's safe to assume that posted data
structure or methods are used in production. They could merely serve as a
demonstration of a related problem.
In this case I am actually using temp tables in a stored procedure. I update
my temp table 6 times in the procedure. Perhaps someone could write a single
select statement which would return the results I need, but not me at this
point. I consider the logic of the procedure to be highly complex. I find it
easier to achieve my desired result with a series of updates against temp
tables although I realize this is not ideal from an academic point of view.
The procedure takes <4 seconds to run and is only run in production several
times a w
so I consider the performance acceptable. Others who have
viewed this procedure find it easier to comprehend. I find it easier to
maintain and modify. I understand your point and will consider it in the
future.
