How to get the latest Records from Table

[Former Member]

Hi Experts,

We are querying on a table CDPOS(Change Log). Our query requiredment is to get the latest change record for any sales Order / Customer / etc from CDPOS table.

Could some one please guide me how can I get this done in HANA?

As of i know i could sort the table based on Time stamp and get the latest record. But this is a cost effective as the table contains huge amount of data.

Please suggest if any other alternative.

Best regards,

Krishna.

[Former Member]

I think you could try an simple approach to adress this problem:

When we talk about SQL, SQL Script and XSJS, you should give variables a try.

In detail:

Write a Procedure with executes following steps:

1, Make a projection of time stamps, key

2, If time stamp exists (from step 4) cut the table like where  timestamps > time stamp

3, Sort time stamps

4, Save latest time stamp for further usage

5, Retrieve resulting objects by key

I don`t know if this works with the Database only or if you need some other server side scripting.

But in my view this should be a comman programming problem which should be already adressed by someone.

Edit:

In conclusion this isn`t a sap specific problem, so i recommend to search also other sites like stackoverflow in order to adjust a solution to sap enviroment.

[justin_molenaur2]

Krishna, can you shed a little more light on your requirement? Are you simply wanting to read into the table for a single document and retrieve the last change OR are you trying to use this in some type of join on a very large set of data?

Regards,

Justin

[praveen_kumar235]

try leveraging $trex_udiv$ (order by desc).

(as I find it influences default sorting)

caveat: this is an internal column, and likely not supported for the productive usage.

what is the context here (in business sense), that translates in this requirement?

how do you expect get timestamp in CDPOS (it doesn't exist by default)?

can you explain more?

BR

[lbreddemann]

Sorry, but this advice is nonsense.

$trex_udiv$ is an internal column that does not expose any functionality to the SQL user.

It may be sorted or not. It may even be filled or not. What you get is never guaranteed and accessing this column directly is not the least bit supported.

Not sure how often this has to be repeated in every DB forum on this planet: relations (aka tables) don't have a built-in order.

If you want to express a time-semantic like "the most current record", then your data design must expose this information.

CDPOS does not hold any time related information. CDHDR does.

Anyway, it has been sufficiently discussed here on SCN and elsewhere how to evaluate these tables. E.g.

There is no SAP HANA specific method to deal with these tables, so implementing your queries should work the same way as on anyDB.

- Lars

[praveen_kumar235]

"It may even be filled or not. What you get is never guaranteed "

=> That is the actual nonsense.

"Not sure how often this has to be repeated in every DB forum on this planet: "

=> and you still don't get it after so many repetitions?

"Anyway, it has been sufficiently discussed here on SCN and elsewhere how to evaluate these tables. E.g. INFORMATION ABOUT CDHDR AND CDPOS TABLES"

=> Which is why everyone must stop exploring new/different ways?

Krishna can try as proposed, and share his feedback on outcome as achieved.

With given caveat, expectation is set.

BR

[lbreddemann]

Praveen,

not sure why you as a SAP employee recommend to use unsupported and internal functionality here.

This is not about "a new way" of dealing with time dependencies in data.

It's just wrong to use the udiv column and never correct.

Have you ever checked what happens to your udiv values after a delta merge? Guess what they get reassigned - there goes the relation to the application time and with it the notion of "last change".

Sure, everyone is entitled to try out whatever, but recommending things that cannot possibly end up in a working solution is simply not appropriate for a forum like this.

Here most people come to get assistance with their actual, real-life development challenges.

That's what I and many other try to provide. No idea though what your intention was.

- Lars

[praveen_kumar235]

What makes you see above as a recommendation?

With additive caveat and open questions, far from it.

"Have you ever checked what happens to your udiv values after a delta merge? Guess what they get reassigned - there goes the relation to the application time and with it the notion of "last change"."

==>

Even after re-assignment the relative sequencing is retained.

With this 'latest change..' still get answered.

Let the thread pan out, and fail, if it must, on its own weakness.

Or succeed positively on its strength.

This is what is actually wrong:

* Ballooning above proposal focussed on this thread question -> all they way to generically "dealing with time dependencies in data".

* Articulating an explorative discussion as 'recommendation'.

I view this forum as collaborative platform.

The way you do things (or expect to work) is not necessarily the only right way.

BR

[lbreddemann]

Dude, there is an SAP logo right next to your name.

This evokes, whether or not you entertain that, the notion that you know what you're writing about.

So, if you write "oh, you might go and try this and that" this is taken as a recommendation.

Pointing to "legal fine print" is not helping much here.

And of course you're right - my ideas and ways are not the only right ones.

But your recommendation vague idea of a possible solution to the problem is provably wrong.

The values in UDIV don't affect "default" ordering (this is decided during table compression; at least for the main store). And the re-assignment of values in this column also fills gaps created by records that became invalid. This finally makes it impossible to link (even relative) change time to the value of UDIV.

I've tried that out and based my recommendation on factual knowledge.

Pseudo community-darwinistic stanzas like "Let the thread pan out, and fail, if it must, on its own weakness. Or succeed positively on its strength." don't cover up that you didn't.

Cheers,

Lars

[praveen_kumar235]

“So, if you write "oh, you might go and try this and that" this is taken as a recommendation.

Pointing to "legal fine print" is not helping much here.”

=> Nonsense. Opinions - 2 cents aplenty.

Dude? (refresher )

On the topic,

Regarding re-assignment & records becoming invalid - CDPOS normally will not see this happening - approach usable here.

Archiving-deletion event will fracture this approach, noticing observation above on relative sequencing being retained with record removals, as incorrect.

“The values in UDIV don't affect "default" ordering.”

=> default sorting of select queries (not ordering during compression).

BR

[lbreddemann]

And again: there is no default sorting of a table.

The remark about UDIV affecting the "default order" doesn't make sense therefore.

Also: besides the factual behavior of the UDIV column, there is no API contract for this column.

Any solution build on that will only work by chance.

Just as you didn't realize before that the values aren't monotonously increasing every aspect of this column (data type, content, availability) can (and likely will) be changed in future versions.

So, no, the approach is not usable at all. It's a partly working hack at best that is not sufficient for enterprise applications.

Relying on this internal, undocumented, non-supported column is a bug.