oracle query

i have data displaying

53.83;May 23, 2015 8:21:04 PM EDT

My query below to get between values is giving error can someone help me out with this please
select datavalue, timestamp from  numericlog where logname = 'xxxxxxxxxxx' and timestamp between ('2015-06-06' and '2015-06-08')
romeiovasuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
I am not sure I understand what you are doing, but there are no parenthesis in the between and you should use explicit conversions:

select datavalue, timestamp from  numericlog where logname = 'xxxxxxxxxxx' and timestamp between to_timestamp('2015-06-06', 'yyyy-mm-dd') and to_timestamp('2015-06-08', 'yyyy-mm-dd')
romeiovasuAuthor Commented:
i am getting this following error
Error:
Error while refreshing the data provider.

Cause of Error
ORA-28527: Heterogeneous Services datatype mapping error
ORA-02063: preceding line from EH

my records inside the table currently showing like this
53.83 May 23, 2015 8:21:04 PM EDT
johnsoneSenior Oracle DBACommented:
What is the datatype of that column?  You refer to it as timestamp so that is what I used.  Seems like it isn't actually a timestamp.

It also looks like you are querying a remote database.  What RDBMS is that?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark GeerlingsDatabase AdministratorCommented:
There are at least two problems in your query:
1. The "between" operator in Oracle does not expect or allow parentheses.  So the syntax should be more like this:
    select ... from ... where ... between [date_value1] and [date_value2]

2. Providing values like this: '2015-06-06' when you expect Oracle to use that as a date is always dangerous.  It is much safer to always use an explicit datatype conversion something like what johnsone suggested.

Then, the fact that you see data displayed like this:
53.83;May 23, 2015 8:21:04 PM EDT

does not tell us much.

We don't know if that is a single column value, or if that is two columns, with "53.83" in one column, and the rest of that string as the value for the second column.  If that is two columns, and the second column is actually an Oracle "date" or "timestamp", it is quite easy to query that in Oracle.

But, we need you to tell us whether that value is in fact a single column value, and if yes, what is the datatype?  Or, is that a representation of multiple database columns, and if so what are their datatypes?

And the fact that your error message includes: "ORA-28527: Heterogeneous Services datatype mapping error" indicates to us that there are at least two databases involved here, Oracle plus something else.  We don't know if your data is actually in Oracle, or not.  It looks like not, even though you are querying it through Oracle.
romeiovasuAuthor Commented:
that is actually two columns sorry for the confusion i am looking only for timestamp.  It is synonym from Oracle, how can i get just date like mm-dd-yyyy only. and between dates
slightwv (䄆 Netminder) Commented:
If it was a timestamp or date column in Oracle, the first query would have worked.  Since it didn't, there is a problem.

In Oracle to get a date in that format is easy:
select to_char(sysdate,'MM-DD-YYYY') from dual;

BUT, that makes it a STRING not a DATE so there are issues with BETWEEN.  One does an ASCII compare and one a DATE compare.

In Oracle dates don't have a 'format' until they are displayed.  There are a few things that can control how the date value is displayed.

Just because it looks like "May 23, 2015 8:21:04 PM EDT" doesn't mean it is stored that way.

We need to know the actual data type of the column:  date, timestamp, varchar2, ???

We also need to know the other database and data type to help with the ORA-28527.  As the error suggests, there appears to be a data type mapping issue.
awking00Information Technology SpecialistCommented:
romeiovasu,
The questions posed to you by the experts need replies if you want to get a solution to your problem. A describe on the numericlog table (or view) could help along with a describe on the target db table.
romeiovasuAuthor Commented:
it is a timestamp.  And it is not coming from other database, it is coming from the same database it is a Synonym.
slightwv (䄆 Netminder) Commented:
I don't see how you can get the "ORA-28527: Heterogeneous Services datatype mapping error" from a table in an Oracle database when connected to that database.

Heterogeneous Services is connecting an Oracle database to a non-Oracle database.
romeiovasuAuthor Commented:
it is like a text file and it use as a synonym in oracle database.
Mark GeerlingsDatabase AdministratorCommented:
Synonyms in Oracle are for a table or a view (or a procedure, etc.) but not for an individual column of a table or a view.

To query Oracle data based on a timestamp column and the "between" operator, you need to provide "where" clause values in the same datatype as the column you want the query to search by.  Here is an example to find records in a three-hour time span (8pm - 11pm on a particular date:
select [column1, column2]
from my_table
where [timestamp_column] between TO_TIMESTAMP ('May-23-2015 08:00:00pm', 'Mon-DD-YYYY HH24:MI:SSam')
  and  TO_TIMESTAMP ('May-23-2015 11:00:00pm', 'Mon-DD-YYYY HH24:MI:SSam')

Note, there are many different formats that you can use to provide the values to search for.  The important thing is to provide a format mask for TO_TIMESTAMP that exactly matches the data you provide.
slightwv (䄆 Netminder) Commented:
From sqlplus please post the output from:
desc numericlog
Mark GeerlingsDatabase AdministratorCommented:
Can you also post the output of this query:

select owner, object_type from all_objects
where object_name = 'NUMERICLOG';
romeiovasuAuthor Commented:
Here is from the sqlplus.
Data shows like this
TIMESTAMP
----------------
25-MAY-15
26-MAY - 15
27-MAY-15
28-MAY-15
29-MAY-15
romeiovasuAuthor Commented:
object_type
-----------------
SYNONYM
slightwv (䄆 Netminder) Commented:
Why are you not posting what we are asking for?

I asked for a:
describe of the table, not a select from the table.

>>Data shows like this

I already explained that Oracle dates and timestamps don't have a format until displayed.

If that is a copy/paste, then your problem is in the data.

...
26-MAY - 15
...

Notice the spaces?  This cannot be a DATE and must be a VARCHAR2.

If it isn't a copy/paste, what is that supposed to tell us?
awking00Information Technology SpecialistCommented:
>>here is from the sqlplus.<<
What was the command you entered to get that output? Also, we would still like to see the output from the "desc numericlog" command from sqlplus.
Mark GeerlingsDatabase AdministratorCommented:
Since the object name (numericlog) in your query is a synonym (and not the actual object name) can you run this query and post the results:

select owner, table_owner, table_name, db_link
from all_synonyms
where synonym_name = 'NUMERICLOG';
romeiovasuAuthor Commented:
SQL> select owner, table_owner, table_name, db_link
  2  from all_synonyms
  3  where synonym_name = 'NUMERICLOG';

OWNER                          TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
DB_LINK
--------------------------------------------------------------------------------

OPS$OCSHIS                     OAUSER
NUMERICLOG
EH.COMPANYNAME.LOCAL

HISTORY                        OAUSER
NUMERICLOG
EH.COMPANYNAME.LOCAL

OWNER                          TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
DB_LINK
--------------------------------------------------------------------------------
johnsoneSenior Oracle DBACommented:
Looks like a synonym to a table across a database link as we suspected.  What is EH.COMPANYNAME.LOCAL?  That is the target of the database link.  I suspect that isn't an Oracle database.
romeiovasuAuthor Commented:
that is actually like a text file calling.
slightwv (䄆 Netminder) Commented:
Why haven't you posted the table description that I've asked for?

>>that is actually like a text file calling.

A text file would be an External Table with no Heterogeneous Services needed.

and "actually like" isn't "actually".

What is it "actually"?
johnsoneSenior Oracle DBACommented:
Honestly, without know what it is exactly and how heterogeneous services deals with it, I'm afraid we can't help much.
awking00Information Technology SpecialistCommented:
So the commands we would like to see the results of are:
desc oasuser.numericlog

and

desc oasuser.numericlog@eh.company.local
romeiovasuAuthor Commented:
ok if i create a query like this.  It works instead of passing as mm/dd/yyyy if i pass dd/mm/yyyy it works.

Can you guys help me even though if pass the parameter as mm/dd/yyyy it should go as dd/mm/yyyy

SELECT
  NUMERICLOG.DATAVALUE,
  NUMERICLOG.TIMESTAMP
FROM
  NUMERICLOG
WHERE
  (
   NUMERICLOG.TIMESTAMP  BETWEEN  '06-10-2015 14:25:19'  AND  '06-10-2015 14:25:37'
   AND
   NUMERICLOG.LOGNAME  =  'LT31110:IO.IOVALUE,2s_2w'
  )
slightwv (䄆 Netminder) Commented:
We have no way of knowing 'why' since you haven't provided all the information we've asked for.

My educated guess is the TIMESTAMP column isn't an actual Oracle TIMESTAMP and you are doing string/ascii comparisons not actual date/time comparisons.
awking00Information Technology SpecialistCommented:
>>Can you guys help me ...<<
Only if you help us help you and get us some answers to the numerous requests that have been made. Without that information, everything we supply will be a guess at best.
romeiovasuAuthor Commented:
ok can you guys tell me what you are looking for.  So that i can get it.
Mark GeerlingsDatabase AdministratorCommented:
If the TIMESTAMP column has a datatype of: DATE or TIMESTAMP, you definitely do *NOT* want to supply values for it like this:
WHERE
    NUMERICLOG.TIMESTAMP  BETWEEN  '06-10-2015 14:25:19'  AND  '06-10-2015 14:25:37'

Why not?  That forces Oracle to do an implicit datatype conversion, since Oracle can only compare numbers to numbers, characters to characters, dates to dates, etc.  If that column is actually a TIMESTAMP and your query provides a value like: '06-10-2015 14:25:37', Oracle sees a TIMESTAMP in the database and a CHARACTER string in your query.  (OK, to humans, your character string looks like it represents a "date" value, but when you present it to Oracle inside single quotes, Oracle sees it as a CHARACTER value.)  Since Oracle cannot compare a DATE to CHARACTER, it has to convert one of those values to the other datatype.  Since converting a CHARACTER string to a DATE can result in an error (like if you supply this string for example: '02-31-2015 34:25:37') Oracle always does the safe conversion, which is: DATE to CHARACTER.  

That causes two problems:
1. The database cannot use an index (if any) on the DATE or TIMESTAMP column (so your query may be slow)
2. The comparison is then an ASCII string comparison, not a DATE comparison.  When you want to search for a date range near the end of the year, this character string comparison will *NOT* give you the results you want:
WHERE
    NUMERICLOG.TIMESTAMP BETWEEN  '12-31-2015 21:25:19'  AND  '01-01-2016 02:25:37'

To avoid those problems you need to do an explicit datatype conversion like we suggested, more like this:
WHERE
    NUMERICLOG.TIMESTAMP between TO_TIMESTAMP ('May-23-2015 08:00:00pm', 'Mon-DD-YYYY HH24:MI:SSam')
   and  TO_TIMESTAMP ('May-23-2015 11:00:00pm', 'Mon-DD-YYYY HH24:MI:SSam')

If however this column is actually not a TIMESTAMP datatype, then you have a different problem.  Then you have a character string that you want Oracle to treat as a date.

We don't know what the datatype of your column is.  That is why we have asked you about that a few times.
romeiovasuAuthor Commented:
it is coming from a text file.  
how to get desc numericlog from sql plus.  Can you guys give me a query.
Mark GeerlingsDatabase AdministratorCommented:
Oracle does not allow you to query text files directly.  Oracle supports defining an "external table" that is a pointer to a text file, but then the "external table" definition includes the datatype for each column.

In your case, "numericlog" is a synonym to an object outside of your Oracle database.  We don't understand how Oracle's "Heterogeneous Services" are involved here, but the error message you posted indicates that "Heterogeneous Services" are involved.  This is usually to connect to a non-Oracle database (something like: SQL Server, DB2, etc.) but usually not to present a simple text file to Oracle.
slightwv (䄆 Netminder) Commented:
>>how to get desc numericlog from sql plus.  Can you guys give me a query.

That is it.  There is no query.

From the SQL prompt, desc numericlog

SQL> desc numericlog
johnsoneSenior Oracle DBACommented:
You have given us a single puzzle piece and asked us to keep guessing the picture.  We don't know, so please tell us what the whole picture is.

We're good but not that good.

Honestly, you have some very good people following this question, but we cannot help you without all the pieces.
romeiovasuAuthor Commented:
SQL> desc numericlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 LOGNAME                                   NOT NULL VARCHAR2(32512 CHAR)
 OBJECTNAME                                NOT NULL VARCHAR2(32512 CHAR)
 QUALITY                                   NOT NULL NUMBER(10)
 DATAVALUE                                 NOT NULL FLOAT(49)
 INTERVALS                                 NOT NULL NUMBER(10)
 TIMESTAMP                                 NOT NULL DATE
 AGGREGATE                                 NOT NULL VARCHAR2(32)
johnsoneSenior Oracle DBACommented:
So what happens if you use this:

NUMERICLOG.TIMESTAMP  BETWEEN  to_date('06-10-2015 14:25:19', 'mm-dd-yyyy hh24:mi:ss')  AND  to_date('06-10-2015 14:25:37', 'mm-dd-yyyy hh24:mi:ss')
slightwv (䄆 Netminder) Commented:
Just for grins try this one:
desc oasuser.numericlog@eh.company.local
romeiovasuAuthor Commented:
ERROR:
ORA-04043: object OASUSER.NUMERICLOG does not exist
ORA-02063: preceding line from EH
slightwv (䄆 Netminder) Commented:
I'm far from a Heterogeneous Services (HS)  Expert so I cannot walk you though the specifics of where to look but everything is pointing to a mapping issue.

What do you know about the remote databases setup?  Is the remote table using HS to read the text file?

How is the mapping set up for it?

Take a look at this description of the error message:
http://psoug.org/oraerror/ORA-28527.htm

Try querying the HS_ALL_DD view on the remote database for the table to make sure the mappings are set up correctly.

http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5129.htm#REFRN29089
johnsoneSenior Oracle DBACommented:
Please try the TO_DATE rather than the TO_TIMESTAMP version.  The mapping issue may be between DATE and TIMESTAMP.  Especially if there is a different RDBMS on the other end.  We all assumed that since the name of the column was TIMESTAMP that it was also the datatype.  If it is going through Heterogeneous Services, I wouldn't be surprised if it is trying to push that predicate and cannot translate it.
romeiovasuAuthor Commented:
i am getting zero value.
Mark GeerlingsDatabase AdministratorCommented:
"zero value"

Does that mean: 0 rows returned?  That is better than getting an Oracle error, correct?

That may indicate that the query doesn't find any rows that meet the criteria you entered.  Can you post the exact SQL query that you tried?

(When you posted the results of: "SQL> desc numericlog" that was very helpful.  That allows us to see the actual datatypes for the columns that Oracle is working with.)
romeiovasuAuthor Commented:
ok now i am getting an error.  Here is the query.

SQL> select datavalue, timestamp
  2  from numericlog
  3  where logname = 'LT31110:IO:VALUE,2s_2w' and
  4  timestamp between to_date('10-06-2015 15:00:00', 'dd/mm/yyyy hh24:mi:ss')
  5  and to_date('10-06-2015 15:10:00', 'dd/mm/yyyy hh24:mi:ss')
  6  ;
where logname = 'LT31110:IO:VALUE,2s_2w' and
                *
ERROR at line 3:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using
OLEDB_SQL](pICommand->Execute:rc=-2147217900):Extended = Error getting ItemID.
ORA-02063: preceding 2 lines from EH
romeiovasuAuthor Commented:
Now i got some values with
28783 rows selected.

SQL> select datavalue, timestamp from numericlog
  2  where logname = 'LT31110:IO.VALUE,2s_2w'
  3  and timestamp > to_date('10/06/2015 17:40:00', 'DD/MM/YYYY HH24:MI:SS');
johnsoneSenior Oracle DBACommented:
If you are getting valid data returned in some cases now, then I believe your query problem is solved.  Now it looks more like a data problem.
romeiovasuAuthor Commented:
only the date conversion i am still having issue converting between mm/dd/yyyy or from dd/mm/yyyy
slightwv (䄆 Netminder) Commented:
I agree with the previous Expert comment and have suggested the same:  Looks like a data problem.

Since the to_date

>>i am still having issue converting between mm/dd/yyyy or from dd/mm/yyyy

I don't understand this.  What are you asking?  Those are just format masks for the to_date or to_timestamp calls.  They need to match the values you provide.

 to_date('10/06/2015 17:40:00', 'DD/MM/YYYY HH24:MI:SS')

Is June 10th.

 to_date('10/06/2015 17:40:00', 'MM/DD/YYYY HH24:MI:SS');

Is October 6th.

>>ERROR at line 3:

Since you can get rows from some queries and errors with others, I'm still leaning towards a data issue.  Something in the text file cannot be mapped to an Oracle DATE.

I would still love to know the setup you have.

It appears you have a database link to another server that somehow has Heterogeneous Services set up to read a text file using OleDB.

This sounds overly complex to me but it is your system.  We just need to understand it better than we do now.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
romeiovasuAuthor Commented:
it is a client system.  I am try to get the data on crystal reports using SAP BOBJ.   I will try with you date formats tomorrow.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.