SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow

Object_Id and Object_Name Functions for HANA SQLScript Developers


Object_Id and Object_Name SQL functions are missing on SAP HANA SQLScript for database developers. Similar functions exist on SQL Server and other database management systems. I'ld like to share SQL programmers two sample user-defined functions that can be used to convert object_id to object_name and object_name to object_id in this HANA database tutorial.


Object_Id SQL User Defined Function for HANA Database

For SQLScript developers coding on SAP HANA database, mapping for object_oid from object_name is more complex when compared with mapping to object_name from object_oid. The reason behind this complexity is on a SAP HANA database, there may be multiple objects that have been created in different schemas. For example database tables. Two different tables with the same name could be created in different schemas.

Following SQL function Object_ID checks the availibility of a database object validating the object name criteria first on the current schema. If there is a database object with given object name passed using the UDF function input parameter, the Object_OID of that object from SYS.OBJECTS system view is returned.

If there is no object matching the given parameter for object name in the current database schema, other schema objects are queried. If there is more than one which means multiple objects sharing the same name, then 0 is returned by the SQLScript Object_Id() function.

On the other hand, if there is no object using the given object name NULL value is returned by the user-defined SQL function.

CREATE FUNCTION OBJECT_ID(object_name NVARCHAR(256))
RETURNS object_id bigint
LANGUAGE SQLSCRIPT AS
BEGIN
/*
Returns Object_OID for object that is in current schema preferably
Returns 0 if there are multiple objects with the same name
Returns NULL if object does not exist
*/

declare inSchema int;
declare rowCount int;

select
 sum(case when schema_name = current_schema then 1 else 0 end),
 count(*)
into "INSCHEMA", "ROWCOUNT"
from sys.objects
where object_name = :object_name;

if rowCount = 1 then
 select OBJECT_OID into "OBJECT_ID" from sys.objects where object_name = :object_name;
elseif rowCount > 1 then
 if inSchema = 1 then
  select OBJECT_OID into "OBJECT_ID" from sys.objects where object_name = :object_name and schema_name = current_schema;
 else
  -- select top 1 OBJECT_OID into "OBJECT_ID" from sys.objects where object_name = :object_name;
  object_id := 0;
 end if;
end if;

END;
Code

In the above SQL user function code, ABAP programmers can realize easily multiple SELECT list items are mapped into multiple SQL variables.


Object_Name SQL User Defined Function for HANA Database

Since Object_OId value is unique, Object_Name() user function code is easier to read. Using the object_oid field, SQL function filters system view and if an object row is returned, the Object_Name is used as the output of the HANA database function.

CREATE FUNCTION OBJECT_NAME(object_id bigint)
RETURNS object_name nvarchar(256)
LANGUAGE SQLSCRIPT AS
BEGIN

declare rowCount int;
select count(*) into "ROWCOUNT" from sys.objects where object_oid = :object_id;

if rowCount > 0 then
 select object_name into "OBJECT_NAME" from sys.objects where object_oid = :object_id;
end if;

END;
Code

Object_Name and Object_Id SQLScript Functions Sample

Let's now see how SAP SQLScript programmers can use Object_Id() and Object_Name() user-defined functions on HANA database.

select
 table_name,
 Object_ID(table_name) as object_id,
 table_oid,
 Object_Name(table_oid) as object_name
from sys.tables
where
 schema_name = current_schema and
 is_column_table = 'TRUE'
Code

The output of above SQL Select statement is as seen in below screenshot. object_id and object_name column values are produced by the SQLScript user functions created in this HANA database tutorial.

SAP HANA database user-defined SQL function for Object_Id and Object_Name



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.