At 09:58 AM 8/10/2003 -0400, you wrote:

>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.

CREATE VIEW ALIST ( REPNAME, NAME, ADDRESS )
AS
SELECT REPS.REP_NAME, MAILLIST.ML_MAIL_NAME, MAILLIST.ML_MAIL_ADDRESS
FROM MAILLIST
JOIN REPS
ON MAILLIST.ML_REPID = REPS.REP_ID
WHERE
REPS.REP_ID > -1;
COMMIT;
(2)
SELECT REPNAME, NAME, ADDRESS FROM ALIST
ORDER BY REPNAME;

heLen