Need some help, hint for query

I've got a table of exchange rates:
EXCH_RATE
-----------
id | name | amount | rate | date_set
1 c1 1 100 2004-01-01
2 c2 100 86 2004-01-01
3 c3 1 25 2004-01-01
1 c1 1 101 2004-01-10
2 c2 100 87 2004-01-10
1 c1 1 110 2004-02-01
2 c2 100 90 2004-02-01
3 c3 1 26 2004-02-01

for a particular date there is an appropriate exchange rate. If rate is not changed for a given date it comes from the last changed date. Now I'd like to do a query to find out what is the current exchange rate for all currencies. I can do it for one currency, say with id 2 like this
SELECT * FROM EXCH_RATE
WHERE id = 2 AND
date_set = (SELECT max(date_set) FROM EXCH_RATE WHERE id = 2)

result:
id | name | amount | rate | date_set
2 c2 100 90 2004-02-01

The question is how to retirieve the last exchange rates for all currencies?
Thank you in advance

P.S. I'm using DB2 if this info is neededI'm not sure of the exact syntax with DB2. In SQL Server, we can do this:

SELECT
id,
name,
amount,
rate,
date_set
FROM
EXCH_RATE er1
INNER JOIN (
SELECT MAX(date_set) AS date_set, id
FROM EXCH_RATE) er2 ON er1.date_set = er2.date_set
AND er1.id = er2.id|||I would suggest using:SELECT a.id, a.name, a.amount
, a.rate, a.date_set
FROM EXCH_RATE AS a
WHERE a.date_set = (SELECT Max(b.date_set)
FROM EXCH_RATE AS b
WHERE b.id = a.id)This should work for a specific id or all id values.

-PatP|||?? So will the query I provided. You just need to add a where clause to it if you want to pass a paramater in and only do it for one value. It would be the same with your query.|||I don't think that your query will do that on DB2 (UDB or Z-Series, I don't know enough about DB2/400 to have a useful opinion), although I haven't actually tested it. I think that my query will work on all of the DB2 versions (again, I haven't tested that either).

-PatP|||Kewl. I haven't used DB2 in a long time. I can't remember anything about how it worked from the little I did. lol|||thank you guys.

Need help with Update statement; DDL included

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.