SAP HANA XSA SQL Expression WHEN CASE for entity .hdbcds

[draschke]

Hi,

is this possible to set the current date and current time statement for each new record in the table, if I use this case sql expression in the table?

At this moment it works only for all the rows, which is not what I want.

    entity Patient {
        key PatientID      : Integer    not null;
            ID: String(64) not null; // GUID32 SYSUUID
            AnmeldeDatum   : LocalDate  =
            case
                when ID <> NULL then current_date
            end;
            
            AnmeldeZeit    : LocalTime  =
            case
                when ID <> NULL then current_time
            end;

Thanks for helping us!

[pfefferf · Accepted solution]

I'm not sure if I got your question right? You wanna set for each new inserted record the current date and current date in the corresponding columns? Why you are not just define a default value for your columns ( ... AnmeldeDatum: LocalDate default current_date; AnmeldeZeit: LocalTime default current_time ...)?

Regards,
Florian

[draschke · Accepted solution]

Hi Florian,

I'm still struggling with the default value.

I would like to assume that number 2. should work with a default value (don't need to set the values). But it doesn't.

If I set the values by myself (3.) then it works as expected. What is wrong?

Sample:

1. insert into test2" values(1,'','') --> result Null

2. insert into "test2" values(1) --> doesn't work (not enough values)

3. insert into "test2" values(1,CURRENT_DATE,CURRENT_TIME) --> works

Tried the sample with WebIDE (db-explorer) and Hana Studio

CREATE COLUMN TABLE "test2"(
	"ID" INTEGER CS_INT NOT NULL,
	"AnmeldeDatum" DATE CS_DAYDATE DEFAULT CURRENT_DATE,
	"AnmeldeZeit" TIME CS_SECONDTIME DEFAULT CURRENT_TIME,
	PRIMARY KEY (
		"ID"
	)
) UNLOAD PRIORITY 5 AUTO MERGE;

<br>

[pfefferf · Accepted solution]

Option 1 and 3 are not valid by default in case you wanna use the default values, cause you pass the values by the insert statement.

So only option 2 with one correction needs to be used. As you did not pass values for all columns, cause you wanna use the default values, to have to define the column list, so that the statement knows in which column the value has to be inserted (how else the db should know that, especially if you have columns with the same type).

INSERT INTO "test2" ("ID") values (1);

Regards,
Florian

[draschke · Accepted solution]

Yes, really thank you very much! This works!

[former_member128219]

Hope the cursor function will works with the Function current date and current time.

CREATE PROCEDURE "<SCHEMA_NAME>"."CURSOR_EXAMPLE" (
IN ip_rate DECIMAL(15,2),
OUT ex_products "<SCHEMA_NAME>"."TT_PRODUCT_ DETAILS")
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
/*****************************
Write your procedure logic
*****************************/
DECLARE v_new_price DECIMAL(15,2);
DECLARE CURSOR c_products FOR
SELECT PRODUCT_ID, PRODUCT_NAME, PRICE
FROM "<SCHEMA_NAME>"."PRODUCT_DETAILS";
FOR cur_row as c_products DO
v_new_price := cur_row.PRICE + (cur_row.PRICE * :ip_rate);
UPDATE "<SCHEMA_NAME>"."PRODUCT_DETAILS"
SET PRICE = v_new_price where PRODUCT_ID = cur_row.PRODUCT_ID;
END FOR;
ex_products = select PRODUCT_ID, PRODUCT_NAME, PRICE
FROM "<SCHEMA_NAME>"."PRODUCT_DETAILS" ;
END;

we need to update the sales price of each record. We will pass the increased rate and use a cursor to update each record.

Not sure, on the above just a thought came to mind..