7 Replies Latest reply on Oct 25, 2019 1:41 PM by pgeorge1

    Usage of Volatile tables in SQL Transformation

    Naveen Manoharan New Member

      I'm facing an issue in Infa 9.6 environment. I'm trying to use SQL transformation in query mode.

       

      CREATE VOLATILE TABLE ADMIN_TEST
      (
      CA_ID INTEGER,
      CASE_ID INTEGER
      )
      PRIMARY INDEX(CA_ID)
      ON COMMIT PRESERVE ROWS;

      COMMIT WORK;

      insert into ADMIN_TEST (CA_ID,CASE_ID)
      SELECT 
      ca.CA_ID,
      ca.CASE_ID
      FROM
      STG_TBL ca
      WHERE ca.client_id =  ('Admin');

      SELECT
      CA_ID,
      CASE_ID
      FROM  ADMIN_TEST;

       

      I have 10 records in STG_TBL, when i run this is TD SQL assistant. It works fine. For some reason it is not working in Informatica SQL transformation. I'm getting 4 rows as output will all NULLs