Skipping code in a procedure? [message #658479] |
Thu, 15 December 2016 09:31 |
|
MyLastGamble
Messages: 5 Registered: December 2016
|
Junior Member |
|
|
So, I'm not sure how easy this question is to answer but I will post it anyway.
We have a procedure that runs during our monthly process. The whole process itself takes about 8 hours to complete, this specific procedure takes a good hour to run. What this procedure does is load sales data into a production table for various lines of businesses. There are approximately 30 insert statements that run during this procedure.
Here is the problem we're encountering. This procedure has not been changed since may of this year and its ran fine all year since it was updated. However the past 2 months when its ran its loaded 0 in sales for 1 line of business, everything else loads fine. When we take that LOB's query out of the procedure and run it stand alone, it runs fine and loads sales information. It just when it runs during the monthly process that it pulls in 0's. Here's the query, with object names changed for anonymity:
INSERT
INTO production_data
(
AGENT_KEY,
OFFICE_KEY,
LINE_OF_BUSINESS,
TIME,
Month_To_Date,
M_GOAL,
Year_To_Date,
Y_GOAL,
Counts
)
SELECT b.agent_key,
NULL,
Line_Of_Bus_Variable,
b.time,
(SELECT NVL(SUM(NVL(total_net_new,0) + NVL(total_net_renewed,0)),0)
FROM SCA.stg_monthly_sales p
JOIN SCA.stg_option_codes o
ON o.option_id = p.period__id
WHERE o.option_cd = 'MTH'
AND p.office_cd = b.office_cd
AND p.period = b.period
AND p.year = b.year
) AS Month_To_Date,
0 AS m_goal,
(SELECT NVL(SUM(NVL(total_net_new,0) + NVL(total_net_renewed,0)),0)
FROM SCA.stg_monthly_sales p
JOIN SCA.stg_option_codes o
ON o.option_id = p.period__id
WHERE o.option_cd = 'MTH'
AND p.office_cd = b.office_cd
AND p.period <= b.period
AND p.year = b.year
) AS Year_To_Date,
0 AS y_goal,
NULL AS Counts
FROM SCA.v_Agent_Hier b
WHERE b.period_descr = 'MTH'
AND b.time = VAR_TIME;
A sample record output after the procedure runs would look like this:
AGENT_KEY OFFICE_KEY LINE_OF_BUSINESS TIME Month_To_Date M_GOAL Year_To_Date Y_GOAL Count
14698 NULL 64 12/1/2016 0 0 0 0 NULL
As you can see, for all agent keys it pulls 0's. However, if I were to run the insert statement against the database right after or during the monthly process WITHOUT ALTERING ANYTHING, it actually pulls sales data:
AGENT_KEY OFFICE_KEY LINE_OF_BUSINESS TIME Month_To_Date M_GOAL Year_To_Date Y_GOAL Count
14698 NULL 64 12/1/2016 138104.36 0 1423156.21 0 NULL
Any idea of what could cause a subquery to just randomly not pull any information during a procedure run? The table that the subquery pulls from is populated hours before this process starts, this process and all processes before are dependent on the subquery's table population finishing. Its odd that it happened last month but the query ran fine outside of the procedure with no alterings. The whole process ran fine in test and then it loaded 0 sales again this month, with the insert statement running fine again after the procedure was done, again with no alterings to any data sources. I am at a loss as all my tests show that it should be running fine. Any help would be appreciated!
|
|
|
|
Re: Skipping code in a procedure? [message #658488 is a reply to message #658484] |
Thu, 15 December 2016 10:34 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
An oracle error ought to mean no rows inserted, not wrong values. If the insert/select had a function call and that function had when others then 1 might make sense.
2 seems more likely.
So check the datatypes of the columns being compared in the where clause of the sub-queries. Make sure they're the same. If they're not then the oracle will have to do implicit conversion which may rely on session settings that are different between the environment where it works and the one where it doesn't.
If that is the case, add the appropriate function calls to do explicit conversions (to_number, to_date for example) with the appropriate format mask.
|
|
|
|
|
Re: Skipping code in a procedure? [message #658497 is a reply to message #658479] |
Thu, 15 December 2016 13:11 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
That VAR_TIME is a little suspicious to me.
Looks like your "TIME" column is a date. Are they TRUNCed in the table?
How does the procedure set VAR_TIME vs. how are you setting VAR_TIME when you run it stand-alone?
|
|
|
|
|
|
|
Re: Skipping code in a procedure? [message #658502 is a reply to message #658501] |
Thu, 15 December 2016 16:07 |
|
MyLastGamble
Messages: 5 Registered: December 2016
|
Junior Member |
|
|
joy_division wrote on Thu, 15 December 2016 15:05I'm lost here. First, 11/31/2016 is not a valid date. 3149 is a number, yet you are displaying a date. My head hurts.
Sorry. 11/31 was a mistype, should be 11/30. I put a date in that field to make it easier to read the output but we actually use number codes, like 3149 or 4433, to reference dates as 11/30/2016 could be either a day, a month end, or a week end, and 3149 lets me know its 11/30/2016, end of the month.
|
|
|
|