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

Try

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 = REPS.REP_ID
AND REPS.REP_ID > -1


You cannot order the view in the DDL, but :

select * from ALIST order by REPNAME

will order the list just fine...

Gabhan
Input