Returning various parts of the same string

[rhythmz]

I am developing an audit report in which I want to obtain the folder path to a report. However, the best result set that I can get to is something similar to:

/content/folder[@name='Credit Group']/folder[@name='Account Management']/folder[@name='Loan Officer Portfolios']/report[@name='Credit Line Approvals']

I greatly desire to achieve an output like:

Credit Group/Account Management/Loan Officer Portfolios/Credit Line Approvals

However, I am literally  :-\ brain dead :-\ on how to accomplish this...SUBSTR comes to mind, however, the length of the string and occurrences of "']/folder[@name='" can vary according to the actual folder path. I am trying to determine those reports in my catalog that are NOT getting used regularly to archive....I can see 'last run date' but cannot determine the frequency of use with the current given RS parameters. BTW, I have no admin panel access.

Thank so much for any help provided,
Dennis

[Lynn]

Yes, this is hideous. You should get something like this:

Credit Group > Account Management > Loan Officer Portfolios > Credit Line Approvals

Our audit database is SQL Server so this exact syntax may or may not work if you have a different database. Perhaps others have more elegant expressions to do the same.

Code Select Expand


replace (
replace (
replace (
replace (
replace (
replace (
replace (
case
  when position ( 'CAMID',  [Audit Physical].[COGIPF_RUNREPORT].[COGIPF_REPORTPATH] ) = 1
            and
            position ( 'My Folders',  [Audit Physical].[COGIPF_RUNREPORT].[COGIPF_REPORTPATH] ) > 0
      then
          [Audit Physical].[COGIPF_USERLOGON_SESSION].[COGIPF_USERNAME] + ' > My Folders'
  when position ( '/report[@name=',  [Audit Physical].[COGIPF_RUNREPORT].[COGIPF_REPORTPATH] ) > 0
      then
          substring ( [Audit Physical].[COGIPF_RUNREPORT].[COGIPF_REPORTPATH],
                  1,
                  position ( '/report[@name=',  [Audit Physical].[COGIPF_RUNREPORT].[COGIPF_REPORTPATH] )-1
           )
   when [Audit Physical].[COGIPF_RUNREPORT].[COGIPF_REPORTNAME] = 'adHocReport'
       then 'Unknown Location'
   else [Audit Physical].[COGIPF_RUNREPORT].[COGIPF_REPORTPATH]
end
, '/content', '' )
, '/folder[@name=''', '' )
, '/package[@name=''', '' )
, '/folder[@name="', '' )
, '/dashboard[@name=''', '' )
, ''']', ' > ' )



[rhythmz]

Unfortunately we are using Oracle...

Not sure if "[Audit Physical].[COGIPF_USERLOGON_SESSION].[COGIPF_USERNAME]" is on the SQL Server side of the house or in the package specific to SQL Server...I have never seen it before...

I'll try to play with it and see what I can create.

Thanks so much for responding to this "inquiring minds need to know" moment for me!!!

[Lynn]

The Audit Physical namespace is unique to our environment since I have enhanced the audit model beyond what IBM delivers as standard. You should, however, find all the COGIPF stuff in your audit database. Oracle is a more robust database than SQL Server so you should have no problem finding equivalent functionality for replace and substring. If you have support for regular expressions (REGEXP_REPLACE) then you could make this expression more elegant.

Good luck!