Re: Error when accessing a DB-view via MS-Access / ODBC

kielhd_at_freenet.de (kielhd) wrote in message news:<10a4173d.0306052232.3908bf72_at_posting.google.com>...
> Environment on the Client:
> MS Access 2000
> Oracle ODBC Driver 8.01.07.00
>
> Environment on the Server:
> Oracle Release 8.1.7.0.0
>
> Problem:
> I am accessing a DB-view via MS-Access using a ODBC driver. The access
> was ok until I made a change to the view. Before the error occured,
> the view was looking like this:
>
> 1 create view IMD_HARDWARE
> 2 as
> 3 select CSM_COMPSYSID_CSMSERVID.CSM_SERVICE_ID,
> 4 COMPUTER.COMPUTER_MODEL,
> 5 COMPUTER.SYS_SER_NUM,
> 6 COMPUTER.OS_INST_DATE,
> 7 COMPUTER_SYS_MEM.PHYSICAL_TOTAL_KB,
> 8 PROCESSOR.PROCESSOR_MODEL,
> 9 PROCESSOR.MAX_SPEED,
> 10 INST_PROCESSOR.PROCESSOR_NUM,
> 11 COMPUTER.OS_NAME,
> 12 COMPUTER.OS_MAJOR_VERS,
> 13 COMPUTER.OS_MINOR_VERS,
> 14 COMPUTER.OS_SUB_VERS
> 15 from COMPUTER,
> 16 PROCESSOR,
> 17 INST_PROCESSOR,
> 18 COMPUTER_SYS_MEM,
> 19 CSM_COMPSYSID_CSMSERVID
> 20 where CSM_COMPSYSID_CSMSERVID.COMPUTER_SYS_ID =
> COMPUTER.COMPUTER_SYS_ID and
> 21 COMPUTER_SYS_MEM.COMPUTER_SYS_ID =
> COMPUTER.COMPUTER_SYS_ID and
> 22 INST_PROCESSOR.COMPUTER_SYS_ID = COMPUTER.COMPUTER_SYS_ID
> and
> 23 INST_PROCESSOR.PROCESSOR_ID = PROCESSOR.PROCESSOR_ID;
>
> To have the date displayed in a different format, I changed line 6 to
>
> 6 to_char(to_date(COMPUTER.OS_INST_DATE, 'DY MON DD
> HH24:MI:SS YYYY'), 'DD.MM.YYYY') OS_INST_DATE,
>
> and the following error occured:
>
> ODBC Call failed
> [Oracle][ODBC][Ora]ORA-01846: not a valid day of the week (#1846).
>
> As I need to access this view via ODBC, I am looking forward for any
> hints.
>

I assume COMPUTER.OS_INST_DATE is VARCHAR2 datatype. In that case, COMPUTER.OS_INST_DATE value must have same format as your date format 'DY MON DD HH24:MI:SS YYYY' i.e. Value should be like 'FRI JUN 20 12:27:51 2003'.

If COMPUTER.OS_INST_DATE is DATE datatype, then you don't need to_date conversion.

Regards
/Rauf Sarwar Received on Fri Jun 20 2003 - 11:40:41 CDT