how to use variables in a CE_PROJECTION function?

[Former Member]

Dear,

after setting up a scenario in SQLScript with pure SQL statements only, I'm now trying to set up the same using calculation engine functions. One tiny part of my setup fails with an error "Inconsistent calculation model (34011)...  -> filter: '"DATE_FROM" <= today and "DATE_TO" >= today' -> Attribute 'today' is missing in node contact_text_res_2".

My "code" looks as follows:

today nvarchar(8);

today := now();

contact_text = ce_column_table("HANA"."ADRP", ["PERSNUMBER", "DATE_FROM", "DATE_TO", "NAME_TEXT"]);

contact_text_res = ce_projection(:contact_text, ["PERSNUMBER", "DATE_FROM", "DATE_TO", "NAME_TEXT"], '"DATE_FROM" <= today and "DATE_TO" >= today');

can anyone tell me what's wrong here? If I use the "now()" directly in the projection view, the second part of the error message is "-> filter: Expression cannot be converted to query entries: Expression may not be converted to query entries"

Kind regards,

Raf

[Former Member · Accepted solution]

hello Raf Boudewijns,

case 1: Timestamp

If your DATE_TO and DATE_TO columns have timestamp ('yyyy:mm:dd:hh:mm:ss') then use variable  now();

today = now();

Case 2: Date

If you  the two columns have date in yyyy-mm-dd format use the below code as variable

today= select to_dats('current_date') from dummy;

this will fetch date in yyyy-mm-dd format.

Regards,

Raja

[RafkeMagic · Accepted solution]

Euh... ok, thanks!

This question was posted when working on a POC with HANA SP3... no longer on that project, but I assume your answer would work on SP6/7 (no HANA access available right now to check and can't remember the details of the project 2 years ago either).

And apparently posted on my "old" userid (which is still not linked to my new one, but apparently to the same email address... hence I reveived an email stating there was a reply to this message). Will try to log on with the "old" account to "close" the message...

Message was edited by: Raf Boudewijns

[Former Member · Accepted solution]

Hi Raf Boudewijns,

I have gone through your replies on hana issues specially on CE codes. which are helpfull.

I am new to hana development, at presents I am working on below code I find answer for the same issue but the person did't show how he corrected the code.

Could you please help me with the below code: I am getting the below error even after replacing to_date with  date ican CE_CALC area.

Please help me ...

Error:Deploy Calculation View: SQL: transaction rolled back by an internal error: Attribute not found in column table: DT_END:

BEGIN

V_TAB = CE_COLUMN_TABLE("SCH_1"."ZCP",["ID_IDENT", "BU_ID","DT_END"]);

V_TODAY = select to_dats(current_date) from dummy;

V_TAB1 = CE_PROEJCTION(:V_TAB,[ "ID_IDENT", "BU_ID", CE_CALC('DATE("DT_END")',DATE) as "V_END"],'"V_END" >= '':V-TODAY'' ');

var_out = CE_PROJECTION (:V_TAB1,["ID_IDENT", "BU_ID"]);

END /

Thanks in advance.

Regards,

raja

[RafkeMagic · Accepted solution]

since this thread is closed, I suggest you create a new one for that issue

(but it seems your field DT_END is not in table ZCP (from schema SCH_1))

[Former Member · Accepted solution]

I posted this issue under below subject.

DATE COMPARISON ISSUE IN CE VIEW

DT_END field is there in the zcp table. this field contains timestamp data from there I am planning to get only date .

Regards,

Raja