Did you have any problems with HANA Expert Script transformations? I've faced error during DTP, if source and target table structs are different, and DTP delivers >= 1 record.
I'm facing the same problem. I'm trying to create a Hana Expert Script transformation in BW but don't find any resources or sample codes. Could you please post your solution?
Check SQLScript reference for syntax of such transformation. SAP® HANA Expert routine is a db stored procedure with read-only privs (as you can see in routine header) with input parameter as structure of an source table (table type) and output parameter as structure of a target table (table type also). To add such transformation go to "Edit -> Routines -> SAP HANA Expert Script create". Considering target table like "0SOURSYSTEM, ZKEY, ZATTR, ZMES" and source table like "0SOURSYSTEM, ZKEY, ZKEY2, ZATTR, ZATTR2, ZDATE, ZMES" you should write a code like:
outTab =
select
SOURSYSTEM
, case
when ZATTR = 'A'
then ZKEY
else ZKEY || ZKEY2
end as "/BIC/ZKEY"
, "/BIC/ZATTR"
, lag("/BIC/ZMES", 1, 0) over(
partition by
SOURSYSTEM
, ZKEY
, ZATTR
order by
to_date(ZDATE, 'yyyymmdd') asc --BW stores date as yyyymmdd in HANA
) as "/BIC/ZMES"
from :inTab;
You could use another tables in your routine. For more details check reference doc.
But I faced some limitations like:
cannot create target attributes different from source (I've got an error about absence of this attrs in some internal auxiliary structure)
all fields in select list must match with target fields by name and by position. You shoud rename them using "<fieldname> as <target_field_name>" sql feature
I've created two identical DSOs for testing purposes and tried to simply copy the data from DSO1 to DSO2.
I'm using following code:
-- In follow you can see the fields of the input table inTab
-- and the field of the output table outTab.
-- inTab fields:
-- "/BIC/MNUIO1"
-- "RECORDMODE"
-- "CALYEAR"
-- "/BIC/MNUIO2"
-- "/BIC/MNUKYF1"
-- "RECORD"
-- outTab fields:
-- "/BIC/MNUIO1"
-- "RECORDMODE"
-- "CALYEAR"
-- "/BIC/MNUIO2"
-- "/BIC/MNUKYF1"
outTab = select "/BIC/MNUIO1", "RECORDMODE", "CALYEAR", "/BIC/MNUIO2", "/BIC/MNUKYF1" from inTab;
When I try to activate the routine I get this error:
"invalid table name: Could not find table/view INTAB in schema SAPBT2: line 22 col 92 (at pos 845)"
Do you have any clue what I'm doing wrong? In my opinion the syntax is fulfilling the requirements of the SQLscript reference.
And I have a second small question. If I create a routine, I can save and test it. If I try to modify it afterwards, it allways forgets my changes. That means, I have to erase the old routine and rebuild it. Do you experience the same bug or is it a specific problem of my test system?
And I have a second small question. If I create a routine, I can save and test it. If I try to modify it afterwards, it allways forgets my changes. That means, I have to erase the old routine and rebuild it. Do you experience the same bug or is it a specific problem of my test system?
...
Yes, it's very strange bug, I've faced the same issue for the first time I've tried HANA Expert routine. And my solution was the same But then I've found, that if you try to edit routine after activation, it really is not in "edit" mode, but in a "view" mode. So try to click "edit" for one time (you'll go to real "view" mode and see glasses at the toolbar) and then click it twice to return to real "edit" mode. It works great, but very very weird way.
According to your script: go to SAP® HANA SQLScript Reference, p 49 (6. Declarative SQLScript Logic). there you can see how to bind table variables and how to select them with examples. In your case inTab must be prefixed by colon (:inTab). It must solve your problem.
If this is the exact copy of your script, then you should write all capitals between double quotes (so "/BIC/zEBELN" is absent in source and target). Columns with double-quoted names will have the same name as you entered, and you'll need to double-quote them all the time you refer them (HANA distinguishes "plant" and plant).
And also no need to put 0-infoobjects into double quotes, you can refer them as normal columns (plant, recordmode, material and so on). Try fix your code in than way.
Also you could try the following. Considering your source tab is ZTST_D01 DSO , go to HANA Studio and write select "/BIC/ZEBELN", "RECORDMODE", "/BIC/ZVENDOR" from <your HANA schema name>."/BIC/AZTST_D0100"; and then execute it. If it will work, copy all statement (with ";" sign, paste it into the transformation and activate it. It should work. Next you can simply replace tablename with :inTab input table-variable.
Check if generated transformation corresponds to inTab and outTab structures (you can find inTab and outTab fields as commented lines at the beginning of transformation. Also you can try to drop and regenerate this transformation with the same script. Or try to paste your statement in the following manner:
outTab =
SELECT
"/BIC/ZOI_EBELN",
RECORDMODE,
"/BIC/ZEPMPNL"
FROM
:inTab;
What is an "error line" this time?
Best regards,
Andrew
P.S.
You can ask your customer to provide you an install package of HANA Studio as it's usage included in BW on HANA license.
According to my test, the error is in hidden lines. Because if I make a mistake in real select statement, it shows the line other, than 1. And when I've inserted some random letters at the first line, I've got "error at line 1, pos 235" (but I've put it at the very beginning of the editor window). Pos 221, as I think, is something near the end of the procedure header (generated at the top of your screenshot). Check your comments under the white rectangle, they should be enclosed into /* for the beginning and */ for the end.
The last thing I can suggest is to delete everything in editor window and write the next code: outTab = select * from :outTab;. Then to add columns one by one, and in the end to replace :outTab with :inTab.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.