Re: [SQL] adding column with not null constraint

2002-10-20 Thread Johannes Lochmann 
On Fri, 2002-10-18 at 22:12, Vivek Khera wrote:
> ALTER TABLE msg_owner ADD COLUMN user_optional_fields VARCHAR(255);
> ALTER TABLE msg_owner ALTER user_optional_fields SET DEFAULT '';
> UPDATE msg_owner SET user_optional_fields = '';
> 
> Now my problem is I cannot find any syntax for ALTER TABLE ADD
> CONSTRAINT to put a NOT NULL constraint on a column.  Can someone help
> me here?

An additional thought to what [EMAIL PROTECTED] said:

BEGIN;

ALTER TABLE msg_owner ADD CONSTRAINT chk_msg_owner_usr_opt_flds_null
CHECK(user_optional_fields IS NOT NULL);

COMMIT;

HTH

Johannes Lochmann


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Restricting a VIEW.

2002-10-20 Thread Ludwig Lim 

--- Terry Yapt <[EMAIL PROTECTED]> wrote:
> Hello all,
> 
> I have a doubt.  In the next example, I have a table
> with two columns:
> - DATE
> - MONEY
> 
> And a VIEW which SUM's the money GROUPing by
> 'month/year' (I cut off the day)...
> 
> Ok.. I would like to be able to SELECT * FROM VIEW..
> but restricting by complete dates (dd/mm/)...
> (Last select in the example)
> 
> I think it isn't possible, but I would like to know
> your opinion... Or if there is any workaround...
> 
> Best regards..
> 
> --==
> DROP TABLE ty_test;
> CREATE TABLE ty_test
>   (datein date NOT NULL,
>money  numeric(6,2) NOT NULL,
>   PRIMARY KEY (datein)
> ) WITHOUT OIDS;
> 
> INSERT INTO ty_test VALUES ('2002/10/01',10);
> INSERT INTO ty_test VALUES ('2002/10/15',20);
> INSERT INTO ty_test VALUES ('2002/11/15',30);
> 
> DROP VIEW vw_ty_test;
> CREATE VIEW vw_ty_test AS
> SELECT
> TO_CHAR(datein,'MM/') AS datein2,
> SUM(money)
>   FROM
> ty_test
>   GROUP BY
> datein2;
> 
> SELECT * FROM ty_test;  -- All rows from table.
> SELECT * FROM vw_ty_test;   -- All rows from view.
> 
  I don't the work around using a view but you can do
it without using a view:
  
  SELECT
to_number(to_char(datein,'mm'),'99') as month,
to_number(to_char(datein,''),'') as year,
SUM(money)
  FROM ty_test
  WHERE datein BETWEEN 
 to_date('01/10/2002','mm/dd/') AND
 to_date('09/10/2002','mm/ddy/')
  ORDER BY 
to_number(to_char(datein,'mm'),'99') 
to_number(to_char(datein,''),'');


ludwig.

__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly