Is ENUM data type is not supported in HANA SQL?

[Former Member]

Hello Experts,

Greetings !!!

Is ENUM data type is not supported in HANA SQL? OR is there any work around to define ENUM data types in SAP HANA?

Regards,

Krishna Tangudu

[former_member697035]

Today I ran into the same issue. My solution was to replace the enum options with a static integrity check.

MySQL:

...

event ENUM(

'visitWasRequested',

'visitWasCancelled',

'addedAttendeeToVisit',

'removedAttendeeFromVisit',

'mealRestrictionForAttendeeDidChange'

) NOT NULL,

...

HANA:

...

event VARCHAR(255) NOT NULL,

CHECK (

event = 'visitWasRequested' OR

event = 'visitWasCancelled' OR

event = 'addedAttendeeToVisit' OR

event = 'removedAttendeeFromVisit' OR

event = 'mealRestrictionForAttendeeDidChange'

),

...

[rama_shankar3]

Guys:

  Great points - thanks.

  Krishna, a special thanks to you to open this question.

Rama

[Former Member]

Hi Krishna,

ENUM is refer to use particular set of value by a specific numeric value.You can achieve the functionality of enum using reference of value to another table using referential key.

If not,then in what scenario you need ENUM in database?

-Ruchi

Message was edited by: Ruchi Jain

[Former Member]

Hello Ruchi,

Thanks for your reply.

I want to implement a following scenario , example :

STATUS ENUM('PENDING','APPROVED','REJECTED') NOT NULL,

Here I am defining a Field called "Status" which can accept only "'PENDING','APPROVED','REJECTED'". Now can you explain me how do i achieve the same in SAP HANA DB?

Regards.

Krishna Tangudu

[Former Member]

Hi Krishna,

That's your scenario. I thought you need Enum as Week{monday=1,Tuesday=2,..} but as your scenario we need some CHECK Constraint or on INSERT trigger, as I search I didn’t find any such things in HANA DB.

-Ruchi

[lbreddemann]

Hello Krishna,

right now, HANA doesn't support check constraints, so you cannot prevent that data get's inserted into a column that isn't a member from the value domain.

However, if you do store your different values e.g. as integer values and want to map these to the status names, you can use the MAP() function.

It works the same way, Oracles DECODE function works.

MAP(1, 'PENDING', 2, 'APPROVED', 'UNKNOWN') would map every 1 to "PENDING", every 2 to "APPROVED" and all other values to "UNKNOWN".

For better readability you may also consider to use the CASE function instead.

regards,

Lars

[Former Member]

Hi Lars,

Good Point!!

How we come to know about such functions available here.?

-Ruchi

[Former Member]

Hello Ruchi,

Nice question. Even i wanted to ask the same thing to you lars.

Regards,

Krishna Tangudu

[lbreddemann]

Hi Ruchi,

well, I can only tell how I got to know this function and that was simply by looking into the definition of some system catalog views.

I just go and look at how the SQL in them is written and eventually I find such stuff.

BTW: the MAP() function is available in SQL but as it seems not in SQLScript.

Another thing with those undocumented features is that one has to consider that these might be changed or completely taken away without notice in later versions. So I probably wouldn't build my productive application on that...

regards,

Lars

[Former Member]

Hello Lars,

I have a question, if we create an attribute view or calculation view it creates an column view on which i can write an "Select" statement. But on analytic view, it is not possible as it is an OLAP function. Then how do i write a "Select" statement on Analytic view? Please help me in this

[lbreddemann]

Hi there,

no clue why you think that there is no column view for analytic views, because there is!

Just run your select on the "_SYS_BIC"."<package_name>/<AV_NAME>" column view instead of the "_SYS_BIC"."<package_name>/<AV_NAME>/olap" view...

regards,

Lars

[Former Member]

Hello Lars,

When i try to to the same on an an anlytic view called "ANALYSIS" in cloudshare. I get the following error "[7] (at 25): feature not supported: cannot execute select * on olap cube: package/ANALYSIS: line 1 col 26 (at pos 25)"

Please advice me on how to proceed.

Regards,

Krishna Tangudu

[lbreddemann]

Please provide the full SQL and the error message.

[Former Member]

Hello Lars,

Am working on hanasvr-04.

SQL Statement : SELECT * FROM "_SYS_BIC"."package/ANALYSIS"

Error Message : SAP DBTech JDBC: [7] (at 25): feature not supported: cannot execute select * on olap cube: package/ANALYSIS: line 1 col 26 (at pos 25)

But this select statement on Attribute views or Calculation views are working but not on Analytic views

This is the error message am facing.

Regards,

Krishna Tangudu

[lbreddemann]

Ok - please do run a

select col1, col2..., SUM( measure_col1), SUM( measure_col2)

FROM "_SYS_BIC"."package/ANALYSIS"

group by col1, col2....

instead.

[Former Member]

Is it in this way?

select "ANALYSIS"."exchange","ANALYSIS"."stock",SUM("ANALYSIS"."PRICEOPEN"),SUM("ANALYSIS"."PRICECLOSED") FROM "_SYS_BIC"."package/ANALYSIS" group by "ANALYSIS"."exchange","ANALYSIS"."stock"

This is not working,

Regards,

Krishna Tangudu

[lbreddemann]

Hi Krishna,

that's really odd - I cannot reproduce this in my test instance.

Is the package name really "package" ??

What's the error message for this command?

*puzzled*

Lars

[Former Member]

Hello Lars,

Yes the package name is "package". it is present in hanasvr-04.

Can you test select on any analytic views you have created and share the result ?

Am unable to do select on any of the analytic views i have created

Help me in this.

Regards,

Krishna Tangudu

[former_member182277]

Hello Krishna,

ENUM data type is not supported in SAP HANA and there is no work around to define ENUM data types in SAP HANA.

you can use Predictive Analysis for the same also.

Hope it is useful.

Regards,neha

[Former Member]

Hello Neha,

thanks for your reply. Can you tell me how do i use Predictive Analysis for this ?

Regards,

Krishna Tangudu