-
Where I Am Wrong
I am preparing a table in SQL ...here it goes...
1>>>CREATE TABLE employee
(EMPLOYEE_ID NUMBER(6) NOT NULL,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25)NOT NULL,
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4) );
2>>>INSERT INTO employee
(EMPLOYEE_ID ,FIRST_NAME, LAST_NAME,HIRE_DATE, JOB_ID, SALARY ,COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID )
VALUES (&EMPLOYEE_ID ,'&FIRST_NAME', '&LAST_NAME', &HIRE_DATE, '&JOB_ID', &SALARY , &COMMISSION_PCT , &MANAGER_ID , &DEPARTMENT_ID )
after putting data i get follwoing error
3>>>old 4: VALUES (&EMPLOYEE_ID ,'&FIRST_NAME', '&LAST_NAME', &HIRE_DATE, '&JOB_ID', &SALARY , &COMMISSION_PCT , &MANAGER_ID , &DEPARTMENT_ID )
new 4: VALUES (100 ,'Steven', 'king', 20-JUN-97, 'AD_PRES', 24000 , NULL , NULL , 90 )
VALUES (100 ,'Steven', 'king', 20-JUN-97, 'AD_PRES', 24000 , NULL , NULL , 90 )
*
ERROR at line 4:
ORA-00984: column not allowed here
WHAT I AM DOING WRONG..CANNOT POINT THE ERROR..CAN U HELP??
-
the date column is wrong, you cant specify dates like that
-
Try:
VALUES (&EMPLOYEE_ID ,'&FIRST_NAME', '&LAST_NAME'
, to_date('&HIRE_DATE','DD-MM-YYYY')
, '&JOB_ID', &SALARY , &COMMISSION_PCT
, &MANAGER_ID , &DEPARTMENT_ID )
The date is formated as 20-06-2006 Or pick any date format you like. (see the SQL reference guide).
-
The date is formated as 20-06-2006 Or pick any date format you like.
This is not working...
can somebody guide me thru step by step.......
The default type is DD-MON-RR....
so where i am making mistake
Last edited by database123; 09-14-2006 at 02:19 PM.
-
Originally Posted by database123
2>>>INSERT INTO employee
(EMPLOYEE_ID ,FIRST_NAME, LAST_NAME,HIRE_DATE, JOB_ID, SALARY ,COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID )
VALUES (&EMPLOYEE_ID ,'&FIRST_NAME', '&LAST_NAME', &HIRE_DATE, '&JOB_ID', &SALARY , &COMMISSION_PCT , &MANAGER_ID , &DEPARTMENT_ID )
after putting data i get follwoing error
3>>>old 4: VALUES (&EMPLOYEE_ID ,'&FIRST_NAME', '&LAST_NAME', &HIRE_DATE, '&JOB_ID', &SALARY , &COMMISSION_PCT , &MANAGER_ID , &DEPARTMENT_ID )
new 4: VALUES (100 ,'Steven', 'king', 20-JUN-97, 'AD_PRES', 24000 , NULL , NULL , 90 )
VALUES (100 ,'Steven', 'king', 20-JUN-97, 'AD_PRES', 24000 , NULL , NULL , 90 )
*
ERROR at line 4:
ORA-00984: column not allowed here
WHAT I AM DOING WRONG..CANNOT POINT THE ERROR..CAN U HELP??
this should work for you.
Code:
VALUES (100 ,'Steven', 'king', '20-JUN-97', 'AD_PRES', 24000 , NULL , NULL , 90 )
"What is past is PROLOGUE"
-
Firstly, your post title ought to read something like, "ORA-00984: column not allowed here".
Secondly, never rely on default date formats. Either specify the format explicitly or use the ansi standard ...
Code:
...
VALUES (100 ,'Steven', 'king', date '1997-06-20', 'AD_PRES', 24000
...
-
ya thanks....after my post i figured out that i had to put
'&HIRE_DATE'...... as it also has characters and all worked out well....thanks for help guys....wil be posting any more problems in this thread only...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|