Query structure remains the same

[former_member589702]

Hi All,

I have been searching around for quite some time now and have yet to find a solution to this.

Basically I have a and APD which loads data to a CRM system. Inside this APD, I have a query and a target node which is pointing to CRM. This APD is called in a process chain.

Now, there is a program which is a mass update program which executes the chain multiple times. What I have noticed is that in the APD, the number of records structure remains the same for all the loads eventhough it supposedly to vary in the number of records. This only happens when the APD is ran in 1 execution a few times through the program. I have checked my program all is well.

I was checking around if it was the query cache issue because it seems like it's keeping the query result structure somewhere and reusing it every time the APD is ran, therefore I tried to set the query cache to inactive and tried many other things as well. But it did not work.

Has anyone out there have experience the similar issue? or if you have any idea why is this happening? or even maybe how to clear the cache?

Your input are greatly appreciated!!!

Thank you.

Best regards,

Amanda Ho

[former_member185132 · Accepted solution]

Hi,

If the PC (and therefore the APD inside it) is called multiple times, it would still give the same result as long as the query variant within the APD remains unchanged.

For instance, if the variant within the APD filters the query for Aug 2015, then every time you run the APD you'll get the same of results.

Does the mass update program modify the query variant? If not, then why would the query results be expected to change?

Regards,

Suhas

[former_member589702 · Accepted solution]

Hi Suhas,

Yes the mass program updates the RSRVARIANT table before the APD is executed. I have checked that part as well to ensure the variant is updating before the APD executes. All is well but still this issue is happening. As I've stated in my previous reply, the values are changing but somehow the structure is fixed to the same size as my first load.

Is there some kind of session or cache that I need to refresh?

Thank you.

Best regards,

Amanda

[former_member185132 · Accepted solution]

We need to get a bit deeper check if the problem is with fetching the query or with writing it to CRM.

So please simulate the APD once by checking the intermediate results of just the query step. Change the variant and repeat the same exercise. Compare these results with each other and also with the results of the query in BEx/RSRT.

If the intermediate result is working correctly, then the problem would be in the CRM writing step.

[former_member589702 · Accepted solution]

Hi Suhas,

Regarding your advice above, I actually have a routine in between the query node and the CRM target node in the APD. I put a breakpoint there and there is this internal table which stores the query result data. Everytime the APD is run through the mass program loop, it breaks there and when I check the table in the debugger, the amount of records in those tables remains the same as the first load.

I cant explain why this happens but I finally found a work around.

Somehow when the process chain is executed in synchronous using RSPC_API_CHAIN_START. I experience this issue however, when I tried to run in without the synchronous 'X', it creates background job and this structure issue did not happen anymore.

Maybe there is something storing the query structure in the back end which I have yet to locate. Whether it is some kind of cache or sessions, I am not sure.

If you have any insight on this, I would love to hear it.

Thank you.

Best regards,

Amanda Ho

[BenedictV]

Hi Amanda,

Can you check if the APD has 'intermediate results' in any of its nodes? Happened to me once

If the intermediate results are not deleted the APD keeps updating the same records.

Delete al intermediate results if this is the case.

Benedict

[former_member589702]

Hi Benedict,

Hmm, no I did not generate any intermediate result. the weird thing is the query result structure seems to follow the 1st run. For example if the first run has 3 records, the subsequent will also remain as 3 records, value in those records will change accordingly but if there is more than 3 records on the next run, it will still only populate 3 records.

Do you have any idea regarding this?

Thank you.

Best regards,

Amanda