I am having a problem with a crosstab query and am wondering if anyone else has had this issue.
The query was built several years ago and had been executed manually by one of our data analyst. I offered to schedule the query in Data Point.
I created the automation and tested it and it ran fine. There were no errors and the result set was correct. So, I set the query on a schedule in the job manager. However, when I tested the query by running from the job manager the aggregated results all returned zeros.
I have tried rebuilding the schedule several times and have the same result each time - the results are good if I run the automation in data point but not when I execute from the job manager.
I have tried with both versions 3.5 and 3.6
I do have other crosstab queries that do not have this issue and the one difference is that the query that will not return the correct result from the scheduled results uses a DB link to connect from our plan instance of Oracle to our source instance. Other than that I can find no difference.
Has anyone had a similar issue or any ideas what could be causing this type of behaviour?
How do you schedule the task - in TDP job scheduler or in Windows Scheduler?
If you schedule your task in TDP, please make sure you’re starting TDP as administrator (on Vista and Windows 7 systems)
When aggregating results returned zeroes - what was the value of Last Run Result column in the Job Scheduler?
Also, make sure an account your scheduled task is running under has enough permissions to use “a DB link to connect from our plan instance of Oracle to our source instance”.
I am creating the schedule with the TDP job scheduler. I do have administrative rights on the Windows box running TDP and I currently have 64 other scheduled jobs running on this box. It is running WIn 7 Pro.
It is funny that you ask about the last column - there are 24 columns, each one for a monthly planning period for a two year horizon, and the last column, rownum 24, is the only aggregated column with data.
The aggregation is created with the following statement for periods 1 to 24 using rownum 1 though rownum 24.
ROUND ( SUM (DECODE (plan_period, (SELECT prior_period FROM period_dates GROUP BY rownum, prior_period HAVING rownum = 1 ), resource_hours, 0 )), 0 ) “Period 1”
If I substitute the ‘0’ in the decode function it returns the substitution which normally would tell me that the summed value is 0. However, in the detailed data and when running the query interactively I can see this is not true.
I have permissions for the DB link and use it frequently for other TDP scheduled requests.
As I mentioned, the odd thing with this query is that if I rum the automation manually it is ok. I am logged on with the same account when doing so.
It is not enough to have administrative rights on the box you’re running TDP. It is also important to start TDP ‘as administrator’ when you’re scheduling jobs from TDP:
Try to start TDP like this or reschedule you task in Windows Job Scheduler to see if you have any difference.
Also, by ‘Last Run Result’ column I mean this column in the Windows Job Scheduler:
What value do you have in this column for the failed job?
I rebuilt the automations and schedules as administrator as you suggested. The scheduled results as well as the result when submitting the automation interactively were both the same - all but the last column contained zeros. There are no errors - the log reports that the jobs completed successfully.
Your point regarding permissions made me wonder if the issue might be with something other than Data Point. To make a long story short I found that when logged on to the PC that executes the schedules I would get the same result regardless. I operate the PC using a remote desktop connection and while understanding the reason why it would be different is beyond me I can say that when I run the query on my local machine it executes correctly. I am going to try to have the automation built and executed on the machine locally to see if there is a difference. I have previously experienced issues with connections built in TDP. This particular connection to our planning instance was built remotely and perhaps is somehow lending to the problem.
I think you are on the right track. You might check with your IT to see if your logid used on the remote pc is being authenticated differently than your local pc.
