RE: Force implicit data conversion
Date: Mon, 2 Mar 2015 14:12:33 -0500
Message-ID: <570a01d0551c$d71d95f0$8558c1d0$_at_rsiz.com>
Are you talking about
“During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.”
?
if so, that is NOT the comparison, but rather a target variable that needs to receive the value returned by the select.
keyword: target in target variable.
mwf
From: "" <dmarc-noreply_at_freelists.org> (Redacted sender "swvanlinge_at_yahoo.com" for DMARC)
Sent: Monday, March 02, 2015 1:52 PM
To: dmarc-noreply_at_freelists.org; mwf_at_rsiz.com; 'Oracle-L Freelists'
Subject: Re: Force implicit data conversion
I'm on Oracle 11gR2 (11.2.0.4) and the documentation (http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements002.htm#SQLRF00214) says it'll convert the column to the variable's data type. However, if I make the variable (date) equal to the timestamp column with ms's either rounded or chopped, it doesn't pull up the record, which makes me think it might not be the case.
I'll try the other way around (find a timestamp with 0 ms and see if the date field will be converted to timestamp). Weird.
Thanks,
Stephen
From: stephen van linge <dmarc-noreply_at_freelists.org>
To: "mwf_at_rsiz.com" <mwf_at_rsiz.com>; "dmarc-noreply_at_freelists.org" <dmarc-noreply_at_freelists.org>; 'Oracle-L Freelists' <oracle-l_at_freelists.org>
Sent: Monday, March 2, 2015 10:38 AM
Subject: Re: Force implicit data conversion
No, I need it specifically to be an implicit conversion, aka something Oracle is doing on its' own.
Also, the trace file does not show this. I just don't see a way to tell if the data types are changing in the where clause, and if they are, what's changing to what data type? A way to see this should be sufficient.
Thanks for your help,
Stephen
From: Mark W. Farnham <mwf_at_rsiz.com>
To: dmarc-noreply_at_freelists.org; 'Oracle-L Freelists' <oracle-l_at_freelists.org>
Sent: Monday, March 2, 2015 10:29 AM
Subject: RE: Force implicit data conversion
do you mean, for example using the cast function?:
SQL> r
1 select
2 --+ gather_plan_statistics
3 * from time_size
4* where cast(timeuntrunc as date) > to_date('19700101','YYYYMMDD')
ID DATETRUNC DATEUNTRU TIMETRUNC
- --------- --------- ------------------------------------------------------------------------
TIMEUNTRUNC
1 09-MAY-11 09-MAY-11 09-MAY-11 12.00.00.000000 AM 09-MAY-11 09.58.59.000000 AM 3 ~ 09-MAY-11 ~ 09-MAY-11 10.58.51.000000 AM 1 19-DEC-12 19-DEC-12 19-DEC-12 12.00.00.000000 AM 19-DEC-12 01.33.47.000000 AM 1 19-DEC-12 19-DEC-12 19-DEC-12 12.00.00.000000 AM 19-DEC-12 02.01.05.000000 AM SQL> save q_cast_time
Created file q_cast_time.sql
SQL> _at_q_xplan
PLAN_TABLE_OUTPUT
SQL_ID 9gqfbr6653j64, child number 0
select --+ gather_plan_statistics * from time_size where
cast(timeuntrunc as date) > to_date('19700101','YYYYMMDD')
Plan hash value: 1526675273
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 4 |00:00:00.01 | 8 | |* 1 | TABLE ACCESS FULL| TIME_SIZE | 1 | 1 | 3 (0)| 4 |00:00:00.01 | 8 |
Predicate Information (identified by operation id):
1 - filter(CAST(INTERNAL_FUNCTION("TIMEUNTRUNC") AS date)>TO_DATE(' 1970-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of stephen van linge (Redacted sender "swvanlinge_at_yahoo.com" for DMARC)
Sent: Monday, March 02, 2015 1:09 PM
To: Oracle-L Freelists
Subject: Force implicit data conversion
Hi all,
This is an academic question. Given a table A with column B (timestamp data type) and date parameter C, how can i force an implicit conversion within a select statement of B to date type?
I'm looking at this:
SELECT *
FROM A
WHERE B = C
and I'm not sure if B is being converted to date, or if C is being converted to timestamp...
Thanks,
Stephen
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 02 2015 - 20:12:33 CET