Re: Insert Into-why doesn't it work?
Date: 30 Apr 2003 05:06:45 -0700
Message-ID: <a1a0416b.0304300406.3e207088_at_posting.google.com>
monicaroman_at_yahoo.com (Monica Roman) wrote in message news:<9eb77af5.0304290812.49235b7f_at_posting.google.com>...
> I have 2 tables that have the exact same structure:
> Name Type
> ------------------------------- ------------
> FIELD1 DATE
> FIELD2 VARCHAR2(30)
> FIELD3 VARCHAR2(25)
> FIELD4 CLOB
> I need to:
> INSERT INTO Table1
> SELECT * FROM Table2
> WHERE Field1 like '28-APR-03%';
> But it doesn't work! why?
Possible glitch #1: Order of columns; NEVER use
INSERT INTO <Table1> SELECT * FROM <Table2> ...
instead:
INSERT INTO Table1(
FIELD1, FIELD2, FIELD3, FIELD4
) SELECT FIELD1, FIELD2, FIELD3, FIELD4
FROM Table2
WHERE TO_CHAR(FIELD1, 'DD-MON-YYYY') = '28-APR-2003';
The order of columns with "*" is NOT well defined!
Possible glitch #2: "... WHERE Field1 like '28-APR-03%'" Rather use TO_CHAR() with appropriate formats when querying dates! Avoid the use of LIKE with DATEs, rather use it with strings. As you see above, you can use "=" when TO_CHAR() gets the correct format ('DD-MON-YYYY'). Possible glitch #3: USE 4-DIGIT YEARS! Didn't you learn from "Y2k"? :-)
HTH Received on Wed Apr 30 2003 - 14:06:45 CEST