Cannot create temporary table having identity column

[Former Member]

Hi experts,

I saw the above error msg while running the following statement:

       create local temporary column table #tmp_table (c1 int GENERATED by default AS IDENTITY (start with 1 increment by 1), c2 int)

     Could not execute 'create local temporary column table #tmp_table(c1 int GENERATED by default AS IDENTITY (start with ...'

     SAP DBTech JDBC: [7]: feature not supported: cannot create temporary table having identity column: C1: line 1 col 48 (at pos 47)

I understand we can support normal column table creation with identity column, but don't know why cannot support temporary column tables with identity column. Is there any configuration that can enable it for temporary column table? Or what can I do to support it indirectly, like writing a trigger to support it or something else?

If not, then is there any future plan for this feature?

Regards,

Hubery

[lbreddemann · Accepted solution]

Hi Hubery,

there is no "setting" to give you a feature that is just not there.

As the identity column feature is just a shortcut for using sequences as primary key value generators, the alternative to them is - wait for it - use sequences manually.

However, as "primary key" barely has any meaning for a temporary table (remember, the _content_ is always only visible to your own session), the questions is: what's your use case here?

- Lars

[Former Member · Accepted solution]

Hi Lars,

Thank you for your quick response!

Customer's procedures are written in ASE Sybase format, and now we propose they to use HANA in order to improve the performance as putting HANA as a backend. However, customer does not want to change their procedures, so we should translate them with ASE and pass-thru to HANA.

They have a lot of temporary table definitions (we also will pass-thru them to HANA) with identity columns, and use them to do some calculations. So we cannot just remove them or mark them as normal columns.

So maybe we can consider to use sequences manually.

Thanks again for your help.

Regards,

Hubery

[lbreddemann · Accepted solution]

Hi Hubery,

I've heard this trail of arguments before...

Customer has a solution... they want it on HANA... but they don't want to change the solution.

Well, fair call, I'd say.

The problem here is: there's a mix-up of solution and implementation here.

It should be clear now, that changing DBMS systems (in any direction) will require some effort in changing the implementation. Every DBMS works a bit different than the others, given "standard" SQL or not.

So I don't agree with the notion of "we cannot change the implementation".

In fact, you will have to change the implementation anyhow.

Rather than imitating the existing solution implementation on ASE, implement it on SAP HANA.

Filling up tons of temporary tables is not a great idea in SAP HANA - you would rather try to create calculation views that present the data ad hoc in the desired way.

That's my 2 cts on that.

- Lars

[Former Member · Accepted solution]

Hi Lars,

To use HANA as back end is our proposal, customer said if you want us to change the procedures, we would rather to use oracle.

This should be a transition version, and if we won the deal, then we can ask customer to change to HANA completely without any other front end systems. For now, I think we should support this because we've already saw some great performance improvement.

So thanks for your great help again, we will discuss within team for the best solution.

Regards,

Hubery

[Former Member · Accepted solution]

Hi Hubery,

      I have worked in a similar migration project, and I have written my experience as a blog http://scn.sap.com/community/developer-center/hana/blog/2014/08/04/sybase-ase-to-hana. this may help you.

As you said you have to write sequences manually.

Thanks & Regards

  A.Dinesh

[Former Member · Accepted solution]

Thanks, A.Dinesh, it gives us such a good example!

Regards,

Hubery

[Former Member · Accepted solution]

ASE adheres to TSQL standard  and HANA adheres to ANSCII standard so its not very easy , but if you some basic PLSQL skill you can achive it.But as lars mentioned direct mapping and converting may not give you full advantage on HANA.So Procedure  has to be tuned to HANA later. Thanks& Regards A.Dinesh

[Former Member · Accepted solution]

Hi A.Dinesh,

ASE already has some capability to convert the TSQL to ANSCII format, but there are also some aspects we are improving. We will use HANA directly after we win the contract, .

Regards,

Hubery