Hi Kirk,
A good way would be to store the potential actiontype values in a look up table and then be prepared to accept null values as blanks (SET NULLDISPLAY TO "") if actiontype is not C, U, or D. Then you can use a join in the SQL and all is well.
CREATE TABLE atcodes (actiontype C(1),atvalue C(7))
INSERT INTO atcodes (actiontype,atvalue) VALUES ("C","Created")
INSERT INTO atcodes (actiontype,atvalue) VALUES ("U","Updated")
INSERT INTO atcodes (actiontype,atvalue) VALUES ("D","Deleted")
Now the SQL is ...
CREATE VIEW myview AS ;
SELECT mytable.*,atcodes.atvalue ;
FROM mytable ;
LEFT JOIN atcodes ON mytable.actiontype = atcodes.actiontype
Once you move into the area where a code in a table can have more than one meaning, you almost have to go relational and have a lookup table, even if it's never modified.
>I'm creating a view for a table. One column is called action type (Char 1) and has one of the following value: C,U,D. I want to create a column that if it is a C, then return CREATED, if its a U return Updated, and if it is a D return Deleted
>
>Can someone help me out with this?
>
>Thanks
>Kirk
------------------------------------------------
John Koziol, ex-MVP, ex-MS, ex-FoxTeam. Just call me "X"
"When the going gets weird, the weird turn pro" - Hunter Thompson (Gonzo) RIP 2/19/05