Home » SQL & PL/SQL » SQL & PL/SQL » function based index using new_time function
function based index using new_time function [message #277313] Mon, 29 October 2007 09:13 Go to next message
karismapanda
Messages: 58
Registered: January 2007
Member
Hi All,
In one of my query i am using new_time ((WW_ORDER.CREATED_DT ),'GMT','PDT') in where clause.
for that query is running very slow,i tried to create one index on that but getting the following error when running any query

select WW_ORDER.CREATED_DT from WW_ORDER where rownum<=10;
select WW_ORDER.CREATED_DT from WW_ORDER where rownum<=10
                       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got -



can't i create an index on it like:

create index idx_ord_created_dt on WW_ORDER(new_time(created_dt,'GMT','PDT'));

Thanks & Regards,
Karisma
Re: function based index using new_time function [message #277321 is a reply to message #277313] Mon, 29 October 2007 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Copy and paste the whole session
2/ Post your Oracle version with 4 decimals
3/ Desc WW_ORDER

Regards
Michel
Re: function based index using new_time function [message #277409 is a reply to message #277321] Tue, 30 October 2007 00:04 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
Thanks for your reply.
my oracle version is:10.1.0.3.0

WW_ADMIN@vcadb1>select WW_ORDER.CREATED_DT from WW_ORDER where rownum<=10;

CREATED_DT
---------------------------------------------------------------------------
2004/03/24 05:26:00:000000 AM
2004/03/18 06:12:00:000000 PM
2004/03/18 06:13:00:000000 PM
2004/03/18 06:14:00:000000 PM
2004/03/18 06:15:00:000000 PM
2004/03/18 06:17:00:000000 PM
2004/03/18 06:17:00:000000 PM
2004/03/18 06:17:00:000000 PM
2004/03/18 06:18:00:000000 PM
2004/03/18 06:20:00:000000 PM

10 rows selected.

Elapsed: 00:00:00.01
WW_ADMIN@vcadb1>create index idx_ord_created_dt on WW_ORDER(new_time(created_dt,'GMT','PDT'));

Index created.

Elapsed: 00:00:03.23
WW_ADMIN@vcadb1>select WW_ORDER.CREATED_DT from WW_ORDER where rownum<=10;
select WW_ORDER.CREATED_DT from WW_ORDER where rownum<=10
                       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got -


Elapsed: 00:00:00.01
WW_ADMIN@vcadb1>desc WW_ORDER
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER(20)
 CREATED_BY                                NOT NULL NUMBER(20)
 CREATED_DT                                NOT NULL TIMESTAMP(6)
 MODIFIED_BY                               NOT NULL NUMBER(20)
 MODIFIED_DT                               NOT NULL TIMESTAMP(6)
 RETAIL_ID                                 NOT NULL NUMBER(20)
 APPOINTMENT_ID                                     NUMBER(20)
 STATUS                                    NOT NULL VARCHAR2(20)
 DOCTOR_ID                                          NUMBER(20)
 PATIENT_ID                                NOT NULL NUMBER(20)
 CLIENT_ID                                 NOT NULL NUMBER(20)
 REASON_ID                                          NUMBER(20)
 WALKIN_FLAG                                        CHAR(1)
 REF_NO                                             NUMBER(20)
 PATIENT_WEIGHT                                     NUMBER(20,5)
 PATIENT_WT_UOM                                     VARCHAR2(20)
 F8_MSG                                             VARCHAR2(300)
 PATNT_WT_MOD_DT                                    TIMESTAMP(6)
 REFERRAL_TYPE_ID                                   NUMBER(20)
 REFERRAL_SOURCE_ID                                 NUMBER(20)
 SOURCE_ORDER_ID                                    NUMBER(20)
 TYPE                                      NOT NULL VARCHAR2(32)
 IS_HOSPITALIZED                                    CHAR(1)
 READY_TO_GO                                        CHAR(1)
 RELIEF                                             CHAR(1)
 ORDER_TOTAL                               NOT NULL FLOAT(126)
 WT_POPUP_CANCEL_DT                                 TIMESTAMP(6)



Thanks & Regards,
Karisma
Re: function based index using new_time function [message #277427 is a reply to message #277409] Tue, 30 October 2007 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

NEW_TIME returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.

This is documentation.

Regards
Michel
Re: function based index using new_time function [message #277434 is a reply to message #277313] Tue, 30 October 2007 02:04 Go to previous message
karismapanda
Messages: 58
Registered: January 2007
Member
Thanks a lot Michel.
Previous Topic: Index on Date Field
Next Topic: Can i pass collection into a ref cursor?
Goto Forum:
  


Current Time: Sun Dec 11 02:34:32 CST 2016

Total time taken to generate the page: 0.05197 seconds