help with unions (is there a faster way to do this??)

[Glowworm27]

Hello all,

I have a proc that will return exceptions from a HUGE table of data. The exceptions are a set of rules the program checks before data can be sent to our processing company.

For instance we check for records where dates are wrong, amounts are incorrect, to big, or a negative amount, and many, many more. We actuall check for about 20-30 differnt things.

I created a query that would return the record ID, and text that described which rule was violated, and then use a UNION statement to return the next rule, and so on and so forth.

Well as our data has built up this query is taking longer and longer and now it is timing out very frequently!

Is there some way I can get a recordset of all these exceptions into one recordset? and without timing out????

Thanks
G

here is the code

--- First Check to see if a Non Flat rate employee has regular hours (001) greater than 40 hours
	Select 'User has over 40 regular Hours' as Exception_text, Universal_ID  from Payroll_Detail
	Where Pay_Code = '001' And Check_Date = oCheckDate And Flat_Rate_Ind = 'N'
	group by Universal_Id 
	Having Sum(Hours) > 40
	Union
	--- Next Check to be sure the total Gross pay amount does not exceed 99,999.99 dollars
	Select 'User has Gross Pay Amount > $99,999.99' as Exception_text, Universal_ID From Payroll_Detail
	Where Check_Date = oCheckDate
	Group by Universal_Id
	Having Sum(Gross_Pay) > 99999.99
	Union
	--- Next check to be sure the Total Gross pay amount does not go less than zero (negative paycheck)
	Select 'User has Gross Pay Amount < 0' as Exception_text, Universal_ID From Payroll_Detail
	Where Check_Date = oCheckDate
	Group by Universal_Id
	Having Sum(Gross_Pay) < 0
	Union
	--- Next Make sure we have the employee in the Master File
	Select 'User not found in Employee Master File', Payroll_Detail.Universal_ID from Payroll_Detail
	Left Join Employee_Master On Payroll_Detail.UNIVERSAL_ID = Employee_Master.UNIVERSAL_ID
	Where Check_Date = oCheckDate and (Employee_Master.UNIVERSAL_ID IS Null)
	Union
	--- Next check to make sure we have a 6 digit BFS account number for the MTA account number we just used
	Select 'No BFS 6 digit Store number for Employee', Payroll_Detail.Universal_ID From Payroll_Detail
	Left Join Department_Acct_Unit_Lookup On Payroll_Detail.ACCT_UNIT = Department_Acct_Unit_Lookup.ACCT_UNIT
	where Check_Date = oCheckDate and (Department_Acct_Unit_Lookup.BFS_6_DIGIT is Null)
	Union
	--- Next check to make sure that regular hours have been calculated for Flat rate employees
	Select 'No Regular Hours found for Flag Rate employee', Payroll_Detail.Universal_ID From Payroll_Detail
	Where Check_Date = oCheckDate and Flat_Rate_Ind = 'Y' and Hours = 0
	Union
	--- Next check to make sure we do not have flag_Hours for NON=flat rate employees
	Select 'Flag hours found on NON Flag Rate Employee', Payroll_Detail.Universal_ID From Payroll_Detail 
	Where Check_Date = oCheckDate and Flat_Rate_Ind = 'N' and Payroll_Detail.FLAT_HOURS <> 0
	Union
	--- Next check to make sure the Work_Week_Ending_Date is not Prior to the Reactivte Date
	Select 'Payroll Date Prior to Hire Date', Payroll_Detail.Universal_ID From Payroll_Detail
	Inner Join Employee_Rate On Payroll_Detail.UNIVERSAL_ID = Employee_Rate.UNIVERSAL_ID
	Where Payroll_Detail.Check_Date = oCheckDate And Payroll_Detail.WORK_WEEK_ENDING_DATE < Employee_Rate.REACTIVATE_DATE
	and Employee_Rate.Work_Week_Ending_Date = (Select Max(Work_Week_Ending_Date) from Employee_Rate E 
																where E.UNIVERSAL_ID = Payroll_Detail.UNIVERSAL_ID)
	Union
	--- and finaly make sure the Work_Week_Ending_Date is always a Saturday
	select 'Work Week is not a Saturday', Universal_ID From Payroll_Detail
	Where Payroll_Detail.Check_Date = oCheckDate
	and to_char(Work_week_Ending_date, 'DY') <> 'SAT'
	;

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[SantaMufasa]

Speed will improve if you use "UNION ALL" instead of "UNION". ("UNION" much ensure that there are no duplicates in your results set.)

But as an alternative to either of the above, how about:

--- First Check to see if a Non Flat rate employee has regular hours (001) greater than 40 hours
    INSERT INTO PAYROLL_EXCEPTIONS
    Select 'User has over 40 regular Hours' as Exception_text, Universal_ID  from Payroll_Detail
    Where Pay_Code = '001' And Check_Date = oCheckDate And Flat_Rate_Ind = 'N'
    group by Universal_Id 
    Having Sum(Hours) > 40;
    COMMIT;

    --- Next Check to be sure the total Gross pay amount does not exceed 99,999.99 dollars
    INSERT INTO PAYROLL_EXCEPTIONS
    Select 'User has Gross Pay Amount > $99,999.99' as Exception_text, Universal_ID From Payroll_Detail
    Where Check_Date = oCheckDate
    Group by Universal_Id
    Having Sum(Gross_Pay) > 99999.99;
    COMMIT;

...et cetera

I assert that this will be much faster...Plus, if you run this from the SQL*Plus prompt and you also issue the SQL*Plus commands:

SET TIME ON
SET TIMING ON

...you are able to see both the clock time and elapsed processing time for each query to determine which of your queries represent high processing costs. Another benefit is that you are able to view intermediate results in the PAYROLL_EXCEPTIONS table since you issue a COMMIT in between each query.

Lastly, you see the same results that you hoped from your original query by simply doing:

SELECT * FROM PAYROLL_EXCEPTIONS...

Let us know your reactions to these ideas.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[taupirho]

I'm guessing Dave's way of doing things will be faster than yours but may still take some time. But another advantage of using Dave's methodology is that you will immediately "see" which query is your bottleneck and can tune accordingly. Post details if you get to this stage and we'll try and assist more.


In order to understand recursion, you must first understand recursion.

 

[Thargy]

Glow,

I suggest that you do no work at all, and let Oracle sweat.

Create a target table with a structure identical to the one you aready have (source table), but with no data. Do a CTAS (Create Table As Select WHERE 1=2) to achieve this instantaneously.

Put a constraint on the table for each condition for which a check is required.

Use DBMS_ERRLOG to create a suitable error table.

Then insert into target_table select * from source_table errors into the error table.

Oracle will give you a list of all the dud records and what caused them to fail in the error table. You write no code, oracle does all the error checking. Speed is almost irrelevant compared with fully automated checking. If this takes ages, kick the job off at 5 pm, and come in the next day to a list of all your errors. Rinse and repeat until NUM_OF_ERRORS =0.

Don't do it yourself when Oracle has standard features to do it for you.

Regards

T

 

[taupirho]

Tharg, good call but doesn't some - in fact most - of your solution depend on being on Oracle 10?


In order to understand recursion, you must first understand recursion.

 

[Thargy]

good catch Taupirho.

My apologies to all, I use 10g2 all the time and made the fundamental mistake of posting the above solution in a 9i forum. Mea Culpa.

Regards

T