Skip to Content
0

SAP HANA XSA SQL Expression WHEN CASE for entity .hdbcds

Apr 06 at 10:59 AM

46

avatar image

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!

SQL | xsa | hdbcds
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Florian Pfeffer
Apr 06 at 12:21 PM
2

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

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>
0

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

2

Yes, really thank you very much! This works!

0
ASHOK Ashok kumar Apr 10 at 10:44 AM
-1

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..
Share
10 |10000 characters needed characters left characters exceeded