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

    Unanswered: Best way to combine, lookup and export

    I have three tables:
    contains Field1, Field2.....
    contains Field1, Field 2... (same structure as Audit) ..+ Error_code
    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
    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)
      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
      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
      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
    Waukesha WI

    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!


Posting Permissions

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