How to subquery from SQL Input

AudieCruz

Hello, 

I have a case in which I'm using SQL to modify my data input. One of the tables that I use is effective dated. For example, it looks like this when I use query manager: 

SELECT A.PROJECT_ID
  FROM PS_CA_DETAIL_UAR A
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_CA_DETAIL_UAR A_ED
        WHERE A.CONTRACT_NUM = A_ED.CONTRACT_NUM
          AND A.CONTRACT_LINE_NUM = A_ED.CONTRACT_LINE_NUM
          AND A_ED.EFFDT <= SYSDATE))

It would look like this on Alteryx without the effective date select:

SELECT SYSADM.PS_CA_DETAIL_UAR.PROJECT_ID
  FROM SYSADM.PS_CA_DETAIL_UAR 

How do I add the second select given the structure of the way the tables are named on Alteryx? Doesn't seem to allow for adding a rename for a secondary version of the same table. 
 

David-Carnes

OK, your connection from Alteryx to the database is requiring the table owner's name.  Try this:

select a.project_id
from sysadm.ps_ca_detail_uar a
where a.effdt =
        (select max(a_ed.effdt)
         from sysadm.ps_ca_detail_uar a_ed
         where a.contract_num = a_ed.contract_num
         and a.contract_line_num = a_ed.contract_line_num
         and a_ed.effdt <= sysdate)