3 Replies Latest reply: May 15, 2014 10:00 AM by Grant Perkins RSS

    Exporting Summaries to Oracle - Field Name Problem

    Bill Watson

      We have a Datapump 8.5 process whereby we are trying to populate an oracle table using a rather nasty little source report.

       

      Due to the nature of the report layout which had a account identifier on one line with three fields on the line above which may or may not contain product types. We tried various append scenarios but couldn't get it working so ended up pulling the product codes in using a single append and field names Prod_Cde1, Prod_Cde2 and Prod_Cde3 with the intention of getting clever using summaries:

       

      Summary1 has the Account Field and Prod_Cde1 named as Prod_Cde

       

      Summary2 has the Account Field and Prod_Cde2 named as Prod_Cde

       

      Summary3 has the Account Field and Prod_Cde3 named as Prod_Cde

       

      We have filters on Summary2 & Summary3 to only include where Prod_Cde2 & Prod_Cde3 are populated.

       

      The problem is when we try to run the Datapump process (which is effectively 3 appends to an Oracle table) it states it cannot find an output field called Prod_Cde1 in the table despite the summary field being called Prod_Cde which is in the table.

       

      If we amend the Prod_Cde1 field to simply be Prod_Cde it then cannot find Prod_Cde2 to append.

       

      I must admit I am stumped as to why it would be trying to use the Table Field Name as opposed to the Summary Field Name. Any Ideas?

        • Exporting Summaries to Oracle - Field Name Problem
          Grant Perkins

          Bill,

           

          I may have missed this in what you reported but how are the options for the Monarch export set ("General" tab)?

           

          The "Get column names ...." options should offer the use of either Field Names or Column titles. The default is Field Names iirc. If you have set Column Titles then you appear to have uncovered an anomaly.

           

           

          Grant

          • Exporting Summaries to Oracle - Field Name Problem
            Bill Watson

            Originally posted by Grant Perkins:

            Bill,

             

            I may have missed this in what you reported but how are the options for the Monarch export set ("General" tab)?

             

            The "Get column names ...." options should offer the use of either Field Names or Column titles. The default is Field Names iirc. If you have set Column Titles then you appear to have uncovered an anomaly.

             

             

            Grant /b[/quote]Grant

             

            Thanks for that. Obvious when it is pointed out really. We had the option set to Field Names. Changing to Column Titles solved the error

             

            We are just beginning to use Datapump to feed data into some Oracle tables so it is a bit of a learning curve. As there isn't a lot of info out there on this specifically we weren't sure if it was Monarch or Oracle stuffing us up    Or as is more likely (in this case) the problem was between the chair and the keyboard!

             

            The other good thing about this problem is that I had another go at it the model this morning and managed to pull the Product Code data from the report using a Multi Column Region meaning it is now a one pass table export rather than three summary appends. This did involve use of a rather fuzzy trap on the footer (the GL AC) to have it include a footer above the first detail line so that the multicolumn region could have a start and stop template but it's all good.

            • Exporting Summaries to Oracle - Field Name Problem
              Grant Perkins

              Originally posted by Bill Watson:

              As there isn't a lot of info out there on this specifically we weren't sure if it was Monarch or Oracle stuffing us up      Or as is more likely (in this case) the problem was between the chair and the keyboard![/b][/quote]I often have the same concerns here.     

               

               

              Originally posted by Bill Watson:

               

              The other good thing about this problem is that I had another go at it the model this morning and managed to pull the Product Code data from the report using a Multi Column Region meaning it is now a one pass table export rather than three summary appends. This did involve use of a rather fuzzy trap on the footer (the GL AC) to have it include a footer above the first detail line so that the multicolumn region could have a start and stop template but it's all good. /b[/quote]Sounds like so neat work there.

               

              MCR is a great feature but I have come across a few reports which, by their layout, present serious challenges. With a couple of lines swapped around they would be easy.

               

              And then there are the ones where the columns are simply not of consistent width, presumably because check the consistency was not part of the spec or quality standard for the report.

               

              Oh well, life would be boring if it was too easy. Or so people tell me ...

               

              Glad to hear of the Data Pump progress. I am convinced it offers huge potential that many have yet to take advantage of.

               

              Grant