How to determine a HANA column datatype

[former_member511603]

Hi,

Is there a way to determine the datatype of a column, i.e whether it is an integer or datetime or numeric..etc?

Regards,

Sau-Boon

[pfefferf · Accepted solution]

Hello Sau-Boon,

systems views (e.g. TABLE_COLUMNS, VIEW_COLUMNS) deliver that information in column DATA_TYPE_NAME.

Regards,

Florian

[former_member511603]

I am trying to build a function to check for a column type. 

I have not finished this but it seems I can't put the SQL statement this inside a UDF.  Any workaround?

3> CREATE FUNCTION ISDATE (schema_string varchar(255), table_string varchar(255), column_string varchar(255))

4> RETURNS found INT

5> LANGUAGE SQLSCRIPT AS

6> coltype varchar(255) := '';

7> BEGIN

8>      SELECT DATA_TYPE_NAME FROM TABLE_COLUMNS

9>              WHERE SCHEMA_NAME = :schema_string AND

10>             TABLE_NAME = :table_string AND

11>             COLUMN_NAME = :column_string;

12>     found := 1;

13> END;

ODBC error [SQLExecDirect] S1000:1:7:[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;7 feature not supported: Scalar UDF does not support SQL statements

Regards,

Sau-Boon

[former_member182302]

You can use like below:

CREATE  FUNCTION GET_COLUMN_DATA_TYPE (schema_string varchar(255), table_string varchar(255), column_string varchar(255)) 
RETURNS table ( DATA_TYPE_NAME VARCHAR(1000)) 
LANGUAGE SQLSCRIPT  
SQL SECURITY INVOKER AS 
BEGIN 
   RETURN    SELECT DATA_TYPE_NAME FROM TABLE_COLUMNS
             WHERE SCHEMA_NAME = :schema_string AND
             TABLE_NAME = :table_string AND
             COLUMN_NAME = :column_string;
END; 

Regards,

Krishna Tangudu

[muthuram_shanmugavel2]

Hi Sau-Boon,

I am using SP10.

UDF supports SQL.

Can please try with this sql query?

SELECT DATA_TYPE_NAME INTO VAR_DATA_TYPE_NAME

FROM TABLE_COLUMNS

WHERE SCHEMA_NAME = :schema_string

AND TABLE_NAME = :table_string

AND COLUMN_NAME = :column_string;

IF VAR_DATA_TYPE_NAME = 'DATE' THEN

found = 1;

ELSE

found = 0;

END IF;

[former_member511603]

Yes.  This would work but can the entire logic put into a user defined function?

Regards,

Sau-Boon

[muthuram_shanmugavel2]

Hi Sau-Boon,

Yes. This IF logic, We can put it into UDF itself.

I assume your requirement is,

Schema Name, Table_Name and Column_Name will be given as Input Parameters.

If Column Data Type is DATE, Then Function should return as 1.

In this case, you can write this logic in UDF itself.

Regards,

Muthuram

[former_member511603]

Thanks.

Another question.  Besides checking if it is a valid datetime type, is there a way to check if the column value is a valid datetime value?

[muthuram_shanmugavel2]

Hi Sau-Boon,

If Column is defined as DATE Datatype,

It will be in 'YYYY-MM-DD' format only.

I don't think, It would be displayed in other formats.

Regards,

Muthuram

[RashmiAcharya]

Hi Sau-Boon,

If you are using SAP HANA Studio, then expand  Catalog folder > expand the schema where your table is there > context click on table and select Open Definition. It will show datatype of all the columns of the table.

Regards,

Rashmi

[former_member182302]

You can check from SYS.COLUMNS

Regards,

Krishna Tangudu