Query in Oracle for Linked Server SQL Server

Good Morning,

I would like your help to two questions:

1 - I am running a query in Oracle through Linked Server SQL Server, when I run the query I am getting the error below to column that has the case:

Msg 7347, Level 16, State 1, Line 2
OLE DB provider 'OraOLEDB.Oracle' for linked server 'LNK-SERVER01' returned data that does not match expected data length for column '[OraOLEDB.Oracle].CASE'PRIORITY'WHEN'0'THEN'CRIT'. The (maximum) expected data length is 64, while the returned data length is 6.


2 - I need to schedule a job in SQL Server to bring the previous day's (D-1), but do not know how to do this automatically.

I'm putting the query attached

I appreciate the help
consulta_2.txt
Support_38Asked:
Who is Participating?
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.

ZberteocCommented:
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 
')

Open in new window

Support_38Author Commented:
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".
ZberteocCommented:
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 
')

Open in new window

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
Support_38Author Commented:
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
ZberteocCommented:
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 
')

Open in new window

Support_38Author Commented:
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
Support_38Author Commented:
this expression was placed because the data type to  submit date was defined as numeric
ZberteocCommented:
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 
')

Open in new window

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.
ZberteocCommented:
I made a correction above as I forgot the AND before Submit_Date column:

AND Submit_Date BETWEEN...
Support_38Author Commented:
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".
ZberteocCommented:
What is the type for Submit_Date column? Does that query run directly on Oracle?
Support_38Author Commented:
submit_date is number(15)

It is occurring the same error in oracle

Attached error
Capturar.PNG
ZberteocCommented:
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?
Support_38Author Commented:
I have no control over it, the software was not developed internally

All date columns was created with data type numeric
ZberteocCommented:
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?
ZberteocCommented:
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 
')

Open in new window

Support_38Author Commented:
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
Support_38Author Commented:
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
ZberteocCommented:
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'')||'' 00:00'' and TO_CHAR(SYSDATE-1, ''YYYY-MM-DD'')||'' 23:59''
ORDER BY SUBMIT_DATE 
')

Open in new window

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
Support_38Author Commented:
Hello,

Perfect, that's what I needed.

I appreciate the help.
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
Microsoft SQL Server

From novice to tech pro — start learning today.