星期三, 7月 21, 2004

Can one drop a column from a table?

Can one drop a column from a table?From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command.
Other workarounds: 1. SQL> update t1 set column_to_drop = NULL;
SQL> rename t1 to t1_base;
SQL> create view t1 as select from t1_base;

2. SQL> create table t2 as select from t1;
SQL> drop table t1;
SQL> rename t2 to t1;

===========
rem -----------------------------------------------------------------------
rem Filename: dropcol.sql
rem Purpose: Demonstrate Oracle8i drop column functionality
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- drop table x
-- /

create table x(a date, b date, c date)
/

-- Drop column B:
alter table x set unused column b -- Mark col as UNUSED
/
select * from sys.dba_unused_col_tabs
/
alter table x drop unused columns
/

-- Drop column C (different method):
alter table x drop column c cascade constraints
/

=================
-t
---
Tyson Chen
tysonchen () gmail

星期三, 7月 14, 2004

給我整個月,其餘免談

select create_date from break_report
where create_date between to_date('2004/07/01 00:00:00','YYYY/MM/DD HH24:MI:SS')
and last_day(to_date('2004/07/01 23:59:59','YYYY/MM/DD HH24:MI:SS'))

-t
---
Tyson Chen
tysonchen () gmail

星期一, 7月 05, 2004

當預約日期小於今天就不要show

select * from tt where to_date('2004' || '/' || tmonth || '/' || tday,'yyyy/mm/dd') > mdate

-t
---
Tyson Chen
tysonchen () gmail