- Using a variable within a variable
- If you want to use a ODI variable within a variable simply type variable name in refresh statement. Here is an exampls how you can do. Below SQL statement connects to MSSQL Server and returns if job (named “My_BI_Job”) succesfully finished or not in specific date . Variable #V_SYSDATE is a variable that is refreshed as SYSDATE from DUAL in Oracle database. If this doesn’t work try typing PROJECT_NAME after “#” sign. If you have multiples projects ODI confuses which project to get variable.
select count(1) from msdb.dbo.sysjobhistory
where step_id = 0
and run_status = 1
and job_id = (select job_id from msdb.dbo.sysjobs where name = ‘My_BI_Job’)
and run_date = CAST(SUBSTRING(‘#V_SYSDATE’, 1,8) as integer)
- Using ODI functions within a variable
- If you need to use ODI reference functions within a variable you need to use below syntax. This SQL statement gives the current session’s explanation if ODI gets error on runtime. Current session’s session number is gathered from ODI reference function.
SELECT * FROM ODIWD.SNP_SESSION SESS, ODIWD.SNP_STEP_LOG STEP, ODIWD.SNP_SESS_TASK SESS_TASK, ODIWD.SNP_EXP_TXT EXP_TXT
WHERE SESS.SESS_NO = STEP.SESS_NO
AND SESS.SESS_NO = SESS_TASK.SESS_NO
AND STEP.NNO = SESS_TASK.NNO
AND STEP.I_TXT_STEP_MESS = EXP_TXT.I_TXT
AND STEP.STEP_STATUS = ‘E’
AND EXP_TXT.TXT_ORD = 0
AND SESS.SESS_NO = <%=odiRef.getSession(“SESS_NO”)%>
- Gathering schema name from ODI ref functions.
- It is obvious that you may change your infrastructure and migrate to any other database due to business or technical needs. You need to find and replace all the inner select statements and/or variables to replace new schema name. Here is a tooltip to avoid this problem. All you need to do is to use ODI Ref function in order to use Logical Schema instead of writing plain SQL statement. Underlined statement always gives the physical schema name of database “MYDB” and schema “DWH”. If logical schema is chaned somehow, you don’t need to change the code, this code always looks at physical schema in Topology Manager in execution context.
SELECT NVL(MAX(ALARM_ID), 0) FROM <%=odiRef.getSchemaName(“MYDB.DWH”, “D”)%>.TABLE_NAME
Returns below statement if Logical Schema of “MYDB.DWH” is assigned to MYDB database and DWH schema in appropiate Execution Context in Topology Manager. And you will see below statement in Operator.sd
SELECT NVL(MAX(ALARM_ID), 0) FROM DWH.TABLE_NAME
- Using database functions within a variable
- If you need to use database packages and/or functions, you can use it as follows. Below statement calls “get_param_value” function within “my_lib” package with variable “ETL_DATE”.
select to_number(my_lib.get_param_value(‘ETL_DATE’)) from dual