1 Reply Latest reply: Jun 30, 2014 8:46 PM by J_K RSS

    Use Query to filter Data in extract without SQLEXEC

    J_K

      Folks,

       

      I would like to use lookup query to filter data in extract without using SQLEXEC.

       

      Due to SQLEXEC already consumed for execute SPNAME, is there any other Options available.

       

       

      TABLE INV.dummy_table,WHERE (ORG_ID = 83),

      SQLEXEC (ID ABCUSER.DUMMY_PACKAGE_NAME.ITEMS_PROC,SPNAME ABCUSER.DUMMY_PACKAGE_NAME.ITEMS_PROC,

      PARAMS (P_DBNAME=@GETENV("DBENVIRONMENT","DBNAME"),

      P_INVENTORY_ITEM_ID=INVENTORY_ITEM_ID,P_ORG_ID=ORG_ID,

      P_COMMAND=@CASE(@GETENV("GGHEADER", "OPTYPE"), "INSERT", "INSERT", "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE","DELETE","DELETE"),

      P_TRANSACTION_ID=@GETENV("TRANSACTION", "XID")));

       

      I would like to use something like below query to filter data.

       

      select ORG_ID from ABCUSER.dummy_parameter mp where mp.master_orga_id=INV.dummy_table.org_id"

       

      I tried below.

       

      TABLE INV.dummy_table,WHERE ('select ORG_ID from ABCUSER.dummy_parameter mp where mp.master_orga_id=INV.dummy_table.org_id'),

      SQLEXEC (ID ABCUSER.DUMMY_PACKAGE_NAME.ITEMS_PROC,SPNAME ABCUSER.DUMMY_PACKAGE_NAME.ITEMS_PROC,

      PARAMS (P_DBNAME=@GETENV("DBENVIRONMENT","DBNAME"),

      P_INVENTORY_ITEM_ID=INVENTORY_ITEM_ID,P_ORG_ID=ORG_ID,

      P_COMMAND=@CASE(@GETENV("GGHEADER", "OPTYPE"), "INSERT", "INSERT", "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE","DELETE","DELETE"),

      P_TRANSACTION_ID=@GETENV("TRANSACTION", "XID")));

       

      This did not work.

       

      TABLE INV.dummy_table,SQLPREDICATE WHERE = ('select ORG_ID from ABCUSER.dummy_parameter mp where mp.master_orga_id=INV.dummy_table.org_id'),

      SQLEXEC (ID ABCUSER.DUMMY_PACKAGE_NAME.ITEMS_PROC,SPNAME ABCUSER.DUMMY_PACKAGE_NAME.ITEMS_PROC,

      PARAMS (P_DBNAME=@GETENV("DBENVIRONMENT","DBNAME"),

      P_INVENTORY_ITEM_ID=INVENTORY_ITEM_ID,P_ORG_ID=ORG_ID,

      P_COMMAND=@CASE(@GETENV("GGHEADER", "OPTYPE"), "INSERT", "INSERT", "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE","DELETE","DELETE"),

      P_TRANSACTION_ID=@GETENV("TRANSACTION", "XID")));

       

      This works no syntax error but does not filter it out.

       

       

      Any Help appreciated.

       

      Thanks