Query in Oracle for Linked Server SQL Server

5/27/2016

5/25/2016

Remove the quotes from around Priority:

SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN ''0'' THEN ''Critical''  
WHEN ''1'' THEN ''High'' 
WHEN ''2'' THEN ''Medium''
ELSE ''Low''
END,  
Assigned_Group, 
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION  in (''OPER'', ''INF'',''SEC MANAG'')      
AND STATUS <>6 
AND SERVICE_TYPE=0
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') >=''2016-05-23'' 
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') < ''2016-05-24''
ORDER BY SUBMIT_DATE 
')

5/25/2016

ASKER

The error has now changed:

OLE DB provider "OraOLEDB.Oracle" for linked server "LNK-SERVER01" returned message "ORA-00932: inconsistent datatypes: expected NUMBER got CHAR".

5/25/2016

I think the Priority column is number not CHAR, in which case you don't have to enclose the values in quotes:

SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''  
WHEN 1 THEN ''High'' 
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,  
Assigned_Group, 
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION  in (''OPER'', ''INF'',''SEC MANAG'')      
AND STATUS <>6 
AND SERVICE_TYPE=0
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') >=''2016-05-23'' 
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') < ''2016-05-24''
ORDER BY SUBMIT_DATE 
')

5/25/2016

ASKER

hi

This error occurs executing the query the linked server, direct running on oracle not the error occurs, managed to solve the problem first creating a temporary table in SQL and inserting the result.

The other problem is that I need the query is always executed taking into account the previous day, 00:00:00 to 23:59:00 automatically

5/25/2016

I don't understand why upi added those complicated expression for submit date, what you need is a simple filter:

AND Submit_Date BETWEEN SYSDATE-1 and SYSDATE

SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''  
WHEN 1 THEN ''High'' 
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,  
Assigned_Group, 
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION  in (''OPER'', ''INF'',''SEC MANAG'')      
AND STATUS <>6 
AND SERVICE_TYPE=0
AND Submit_Date BETWEEN SYSDATE-1 and SYSDATE
ORDER BY SUBMIT_DATE 
')

5/25/2016

ASKER

The period to be fixed from the previous day, regardless of the time I run the value must be always the previous day period

with sysdate -1 and sysdate

every time I run the result will change

5/25/2016

ASKER

this expression was placed because the data type to submit date was defined as numeric

5/25/2016

Then use this:

SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''  
WHEN 1 THEN ''High'' 
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,  
Assigned_Group, 
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION  in (''OPER'', ''INF'',''SEC MANAG'')      
AND STATUS <>6 
AND SERVICE_TYPE=0
AND Submit_Date BETWEEN TO_DATE(TO_CHAR(SYSDATE-1, ''YYYY-MM-DD'')) and TO_DATE(TO_CHAR(SYSDATE, ''YYYY-MM-DD''))
ORDER BY SUBMIT_DATE 
')

Never apply function to the table column in the where clause, you should do it the the values that you compare it to otherwise the possible existing indexes will not be used, which will make your query slower. Also use 'YYYY-MM-DD' format in strings when comparing, it is universally recognized.

5/25/2016

I made a correction above as I forgot the AND before Submit_Date column:

AND Submit_Date BETWEEN...

5/25/2016

ASKER

This error occurs executing the query :

OLE DB provider "OraOLEDB.Oracle" for linked server "LNK_SERVER01" returned message "ORA-00932: inconsistent datatypes: expected NUMBER got DATE".

5/25/2016

What is the type for Submit_Date column? Does that query run directly on Oracle?

5/25/2016

ASKER

submit_date is number(15)

It is occurring the same error in oracle

Attached error
Capturar.PNG

5/25/2016

That's the problem. We are trying to compare Submit_Date, which is of type NUMBER(13) with a date TO_DATE(TO_CHAR(SYSDATE-1, ''YYYY-MM-DD'')). That doesn't work. Why is Submit_Date of type number and what is stored in it for today's date?

5/25/2016

ASKER

I have no control over it, the software was not developed internally

All date columns was created with data type numeric

5/25/2016

So you have to find out how to turn a date into a number that matches the values in that column. Does your initial query work?

5/25/2016

Actually try this:

SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''  
WHEN 1 THEN ''High'' 
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,  
Assigned_Group, 
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION  in (''OPER'', ''INF'',''SEC MANAG'')      
AND STATUS <>6 
AND SERVICE_TYPE=0
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') BETWEEN TO_CHAR(SYSDATE-1, ''YYYY-MM-DD'') and TO_CHAR(SYSDATE, ''YYYY-MM-DD'')
ORDER BY SUBMIT_DATE 
')

5/27/2016

ASKER

Good Morning,

As it is the query, every time I run will bring a different result, the result needs to be fixed independent fo the day time running.

The date range should always be from 0:00 to 23:59 Fixed

5/27/2016

ASKER

If I run the query now, the result is bringing records of 27 and can not, the records need to be just the previous day.

Attached results
Capturar.PNG

ASKER CERTIFIED SOLUTION

5/27/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial