October 1, 2003 at 8:01 am
I AM TRYING TO RUN THE FOLLOWING SCRIPT BUT KEEP GETTING THE ERROR MESSAGE
ORA-00932: inconsistent datatypes
CAN ANYBODY HELP AND TELL ME WHERE I HAVE GONE WRONG??
INSERT INTO my_table
select TRUNC(ACTION_DATE) AS ACTION_DATE, PHONE_NUMBER, NAME, MOBILE_NUMBER,
PER_CURRENT_CLASS, DIALLER_INTENSITY,
replace('0'||ltrim(mobile_number, '+44'),',', '') as reformat_mobile_number
from U70119."message_sent"
where action_date = '11-JUN-2003';
commit
THANK YOU.
October 1, 2003 at 8:25 am
Let's start with the table you are inserting this into.
What is the order of the columns? Is it the same as the order in the SELECT statement? Probably not.
If the table is Phone_Number, Action_Date and you are INSERTing Action_Date, Phone_Number; then you will get that error.
This is why it's best to include the column names in the INSERT statement.
OR
It could be that the matching columns aren't the same length. For example: if the SELECTed data is VARCHAR(10) and you are trying to put it into a VARCHAR(5) column.
-SQLBill
October 1, 2003 at 8:57 am
Thanks SQLBill your a star.
October 1, 2003 at 11:55 am
Thank you, but which suggestion solved the issue?
It's always good to post what solved a problem, that way others see the final solution if they come across this thread.
-SQLBill
October 2, 2003 at 1:15 am
Thanks bill the problem was the matching columns are differant VARCHAR length's as you suggested.
October 2, 2003 at 8:13 am
You are welcome.
I emailed this to you, but here's my response concerning how to change a column's datatype in case anyone else is curious.
The way to change the column datatype is to use the ALTER TABLE command.
ALTER TABLE table_name
ALTER COLUMN column_name VARCHAR(10)
Just replace the 10 with whatever size you need the column to be.
Changing a columns datatype should always be tested.
Refer to Books OnLine for more information on ALTER TABLE command and the ALTER COLUMN option.
-SQLBill
October 2, 2003 at 8:57 am
I tried the following:
alter table "text_messages_sent"
alter COLUMN "Action_Date" DATE
but get the error message:
ORA-01735: invalid ALTER TABLE option
am i missing something simple?
October 2, 2003 at 9:49 am
I think what you're missing is that you're working with a table in an Oracle database and SQLBill is talking about Transact-SQL syntax.
The Oracle syntax for changing a column is:
ALTER TABLE text_messages_sent
MODIFY (action_date DATE)
Greg
October 3, 2003 at 2:10 am
thanks greg.
does the column have to be empty? if so is there anyway round this, the column has 185000 records in.
October 6, 2003 at 10:25 am
After looking up the syntax, I've found that you can only change a column datatype if all rows contain nulls in the column.
Looks like you're stuck recreating and loading the table. Sorry I misled you.
Greg
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply