Unanswered: Best way to combine, lookup and export
I have three tables: Audit
contains Field1, Field2..... Audit_Errors
contains Field1, Field 2... (same structure as Audit) ..+ Error_code Lookups
contains fields: Code, Description
I've been doing this programatically via Oracle, ODBC and VBA (Access). With over 3 million records, it is now a problem and I wish to migrate it to oracle/plsql.
Here's what I've been doing in code:
1 - Take all fields from audit into a new table, called Audit_summary and add a field called Status. Replace the field status with "Processed" for all fields in Audit.
2- Take all fields from Audit_Errors and load into Audit_summary
3- go through Audit_summary and replace status as follows:
if error code = "Processed" then status = "Processed" otherwise
if field1 > field2, status = "AC exclusion"
if field3 > field4 status = status & "Z exclusion"
lookup error_code in Lookup table and status = status & description
I'm just starting in the wonderful world of PLSQL and can't thnk of a way to do it with just a query. Can Anyone guide me in the right direction?
Several ways to do this, something like this might work - depends on exactly what you want status to look like
insert into audit_summary (col1, col2,..., status, error_code)
case when col1>col2 then 'AC exclusion ' else null ||
case when col3>col4 then 'Z exclusion' else null end,
0 -- assuming audit table has no errors
case when ae.col1>ae.col2 then 'AC exclusion ' else null ||
case when ae.col3>ae.col4 then 'Z exclusion ' else null end
ae.error_code -- assuming audit_error table has errors
from audit_error ae, lookups L
ae.error_code = L.error_code (+) -- may or may not want the outer join
the case as written will combine your conditions - you'll get status = 'ac exclusion z exclustion erorr_code_description' for any rows that fit both col1> and col3> conditions.
that is exactly what I've been missing - I can utilize a case statement within the insert into....others have mentioned it but I didn't know how to format it within and couldn't find an example anywhere.
I love it!