Hi,

I am trying to join two tables, one has multiple records per ID, the
other is unique record per ID.
The two tables look like below
A
ID date var1 var 2
001 1/1 10 20
001 2/1 12 15
001 3/1 17 18
002 2/1 13 10
002 3/1 12 14
............

B
ID
001
002
003
004
....

The join conditions are
1. table A's ID = table B's ID
2. take the variables associated with the 1st occrrence of the date
variable in table A's

I have the following SQL code but the it didn't work. It says the
columns are ambiguously defined. Anyone can help me? Greatly appreciate
it!

PROC SQL;
CONNECT TO ORACLE (USER="&user" PASS="&pass" PATH="@POWH17"
BUFFSIZE=25000);

CREATE TABLE actvy1_1st AS SELECT * FROM CONNECTION TO ORACLE
(
SELECT
actvy1.ID,
actvy1.var1,
actvy1.var2,
actvy1.date

from
A actvy1,
(select a.ID,
min(a.date) mindate
from A a,
B c
where a.ID =c.ID
group by ID
) b

where actvy1.ID =b.ID
and actvy1.date =b.mindate
order by ID
);
disconnect from oracle;
quit;

i'm betting this is homework, and you havent' learned enough in your
class to know that Oracle is different from SQL Server?

qualify ID in:

group by ID
order by ID

(menglin.cao (AT) gmail (DOT) com) writes:

Oracle? This is an SQL Server newsgroup!

This line appears to be the culprit.

And I wonder, should you really have B in the derived table?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx