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]

@AudieCruz 

Ah, good old PeopleSoft.  Hey, have you tried just pasting the full SQL statement in the SQL Editor window in the Input tool?  That looks like valid Oracle SQL syntax so it should run just fine.

Best,

David

[AudieCruz]

@David-Carnes 

I tried, but the naming convention is different. I don't know if it has to do with the way our company setup the PS DB connection to alteryx, but for example:

PS would do:

PS_CA_DETAIL_UAR A, which would then allow me to name the fields A.PROJECT etc

Alteryx does: 

SYSADM.PS_CA_DETAIL_UAR, and the fields are named SYSADM.PS_CA_DETAIL_UAR.PROJECT etc. 

If I want to do a sub select to effective date my table, then I have to rename the sub select. Which I would do on PS by doing PS_CA_DETAIL_UAR A_E or something like that. I don't know how to do that on the SQL editor on alteryx given how the tables are named. 

[David-Carnes · Accepted solution]

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)

[AudieCruz]

@David-Carnes Thank you!1 that did the trick!