Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    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?

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Several ways to do this, something like this might work - depends on exactly what you want status to look like

    Code:
    insert into audit_summary (col1, col2,..., status, error_code)
    (select
      col1, col2,...., 
      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
    from audit
    )
    union all
    (select
      ae.col1, ae.col2,...., 
      case when ae.col1>ae.col2 then 'AC exclusion ' else null ||
      case when ae.col3>ae.col4 then 'Z exclusion ' else null end
      || L.error_description,
      ae.error_code  -- assuming audit_error table has errors
    from audit_error ae, lookups L
    where 
      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.

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    EXCELLENT!!

    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!


    Thanks!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •