Dropping view if existing before creating it. Similar to DROP IF EXISTS in SQL

[former_member760614]

Have a use case where flyway will execute the migration for script which creates a view in the Hana.

generally in SQL, we use DROP IF EXIST my_user_view; which prevents the script from erroring out for duplicate names. But in Hana database exists function can only be used in blocks and block doesn't allow DDL DROP statement in it.

tried following approach but didn't work..

DO
BEGIN
    IF EXISTS(SELECT 1 FROM views v WHERE v.view_name = 'my_user_view')
    THEN
	DROP VIEW "my_user_view";-- CREATE/DROP statement within anonymous block feature not supported error 
    END IF;
END;

[pfefferf · Accepted solution]

What HANA version you are using? Would it be an option for you to use the CREATE OR REPLACE option for a view which is available since HANA 2.0 SPS04 (CREATE VIEW Statement (Data Definition) - SAP Help Portal)?

With the "OR REPLACE" addition your existing view would be replaced with the new definition. No drop is necessary in that case.

[former_member760614 · Accepted solution]

version is sps03.

As we are using the sps04 in other than local, I just upgraded the local engine. It fix the use case. thankyou