on 04-15-2015 8:27 AM
Hello Experts ,
We have a requirement for creating change history details report for Project . I am aware of that we can see change history details for each documents but the requirement is to create a report which we can run and see change history details for all documents .
The approach we have taken so far is - creating query definition joining FCI_DOC_CHG_HISTORY and FCI_PRO_PROJECTS and in the result field we have defined EVENT_RESOURCE for capturing document change details and field type is - string resource . However , while running the query we are getting result as
The value of '{0}' was changed from '{2}' to '{3}'
User account {0} was created
I believe we need to replace the values with respective parameters . Now while I also add the column - EVENT_PARAMS I am getting parameter values as below
paramcount=5 4=Service Registration 3=true 2=false 1=AGCLMD21-21-00 0=DKT0014
So looks like the value for {0} to be replaced with the value appearing for parameter 0 ( which is DKT0014 )
Can anyone help me how to replace the values ?
Thanks in advance
Sudipta
Thanks everyone for suggesting me .
However , while executing the query in SAP sourcing , one of the column is -
The value of '{0}' was changed from '{2}' to '{3}'
and next column is parameter values -
paramcount=5 4=Service Registration 3=true 2=false 1=AGCLMD21-21-00 0=DKT0014 ( in a singe row )
, So somehow in the query result , I have to replace the values so that user can run this report and extract in spreadsheet .
Can you please guide me ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This is the gist of how to do it with a SQL expression:
how
to replace multiple strings together in Oracle - Stack Overflow
In SAP CLM’s “Query Definition” editor, you will add a new field on the “Result Fields” tab and you write your expression in the “Database Column Name” field.
The example in the link is from a function and not a simple query, but you can find what you need by focusing on this piece:
replace( replace(mycol, '{1}', 'myoneval'), '{2}', mytwoval)
This covers replacing the parameter tag in the string but, of course, you have to extract the appropriate value from the EVENT_PARAMS column. I think this is the most powerful way to do it: REGEXP_SUBSTR. You basically will insert this function in place of myoneval, mytwoval etc in the example.
I don't have time at the moment, but if you want to post the SQL from the first tab, I can try to look at it later, if you need help-- or ask a follow-up question here. I’m also interested in this for my own use and don’t mind helping out, if I can.
HTH,
Mike
It's a little tricky and gets ugly if not organized, but this can also be done right in the query by using combinations of standard Oracle functions in the SQL:
HTH,
Mike
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
This type of message format is actually java-specific.
You do have a possibility of implementing the same in Oracle DB, and have it available in the select list but it is not recommended because it requires direct changes at database level.
I have not tested this and I cannot confirm if it will work, because there are some differences between Oracle standard DB and Oracle DB for SAP.
Take a look at Oracle's details for implementing this in the database and decide if it is actually mandatory to have:
Regards,
Bogdan Toma
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.