ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Variable Detail Query

This query provides all the detail of the Variables created under various project and their associated  default properties.

SELECT DISTINCT
CASE
WHEN SNP_VAR.I_PROJECT IS NULL
THEN 'GLOBAL_VARIABLE'
ELSE SNP_PROJECT.PROJECT_NAME
END                 AS PROJECT_NAME,
SNP_VAR.VAR_NAME    AS VARIABLE_NAME,
CASE
WHEN SNP_VAR.VAR_DATATYPE='A' THEN 'ALPHANUMERIC'
WHEN SNP_VAR.VAR_DATATYPE='N' THEN 'NUMERIC'
WHEN SNP_VAR.VAR_DATATYPE='D' THEN 'DATE'
ELSE 'TEXT' END     AS DATATYPE,
CASE WHEN SNP_VAR.VAR_DATATYPE='T' THEN V_TXT.TXT
ELSE SNP_VAR.DEF_v  END      AS DEFAULT_VALUE,
SNP_VAR.lschema_name AS VARIABLE_QUERY_SCHEMA,
SNP_TXT.TXT         AS VARIABLE_QUERY,
S_TXT.TXT           AS DESCRIPTION
FROM SNP_VAR
LEFT OUTER JOIN SNP_PROJECT ON SNP_VAR.I_PROJECT=SNP_PROJECT.I_PROJECT
LEFT OUTER JOIN SNP_TXT ON SNP_TXT.I_TXT=SNP_VAR.I_TXT_VAR_IN
LEFT OUTER JOIN SNP_TXT S_TXT ON S_TXT.I_TXT=SNP_VAR.I_TXT_VAR
LEFT OUTER JOIN SNP_TXT V_TXT ON V_TXT.I_TXT=SNP_VAR.i_txt_var_val_txt
ORDER BY project_name

image

2 Comments

Leave a Reply

Required fields are marked *.


This site uses Akismet to reduce spam. Learn how your comment data is processed.