Good Morning all:

I am trying to create a view from a mailing list. In this list is a record pointer to a table of reps. Each maillist record is assigned to a rep. I need to show the reps name ( from the reps table ) when extracting from the maillist table not the rep ID ( used in the maillist table ). I am trying to create a view to do that using the following:

CREATE VIEW ALIST ( REPNAME, NAME, ADDRESS )
AS
SELECT REPS.REP_NAME, MAILLIST.ML_MAIL_NAME, MAILLIST.ML_MAIL_ADDRESS
FROM MAILLIST REPS
WHERE MAILLIST.ML_REPID =
( SELECT REPS.REP_NAME FROM REPS WHERE REPS.REP_ID > -1 )

I have 2 problems ( so far )

1) I get a REPS.REP_NAME column unknown error error in the Select reps statement. It does exist in the REPS table.
2) I need to order this list by REPNAME. Is this possible from a view I know I cant use the ORDER BY statement, is there another trick to doing this?

This can be a read only view.

Thanks in advance for your help

Bob Lazarchik

[Non-text portions of this message have been removed]