Tables Creating Syn keys although queries are in two different sections

bruce_sorge — Wed, 19 Mar 2025 21:46:28 GMT

Hello everyone, I have two SQL queries where the tables have the same names. I assumed that if I put these two queries into their own sections that I'd get data from the two and have a relationship between all of my tables, but instead I get syn keys generated. If I give the field names a unique alias then the tables don't create a relationship. If I use Qualify* at the top of the SQL, the fields are loaded as tablename.fieldname, and again no joy on a relationship. I have googled this several different ways but no joy. Below are the two queries.

[Upcoming PI Reports]: Load *; [Upcoming PI Reports]: SQL SELECT distinct (case when AB.EPA_ID is not null then AB.EPA_ID else RAB.EPA_ID end) EPA_ID , ARS.ABSTRACT_ID as ABS_ID , (case when AB.ABS_TYPE is not null then AB.ABS_TYPE else RAB.ABS_TYPE end) AS AbsType , ARS.RPT_YR AS RptYr , ARS.START_DATE AS RptSDate , ARS.END_DATE AS RptEDate , ARS.DUE_DATE AS RptDue , (CASE WHEN ARS.RPT_YR = 'F' THEN 'Final Report' ELSE ARS.RPT_YR || ' Annual Report' END) AS RptYrLbl , ARS.PO FROM ABSTRACT AB, RPU_ABSTRACT RAB , APU_REPORT_STATUS ARS WHERE ARS.ABSTRACT_ID = AB.ABSTRACT_ID (+) AND ARS.ABSTRACT_ID = RAB.ABSTRACT_ID (+) and ON_WEB = 0 and IS_SUPPRESS = 0 and IS_IN_PROCESS = 0 and IS_PUB_WF = 0 and IS_REQUIRE = 1 AND (lower(AB.ABS_TYPE) in ('grant','centers','sbir') OR lower(RAB.ABS_TYPE) in ('grant','centers','sbir')) and (ROUND(ARS.DUE_DATE - SYSDATE) > 0) and (ROUND(ARS.DUE_DATE - SYSDATE) <90) ORDER BY ARS.DUE_DATE, 1, ARS.RPT_YR; [Late PI Report]: Load *; [Late PI Report]: SQL SELECT distinct (case when AB.EPA_ID is not null then AB.EPA_ID else RAB.EPA_ID end) EPA_ID , ARS.ABSTRACT_ID as ABS_ID , (case when AB.ABS_TYPE is not null then AB.ABS_TYPE else RAB.ABS_TYPE end) AS AbsType , ARS.RPT_YR AS RptYr , ARS.START_DATE AS RptSDate , ARS.END_DATE AS RptEDate , ARS.DUE_DATE AS RptDue , (CASE WHEN ARS.RPT_YR = 'F' THEN 'Final Report' ELSE ARS.RPT_YR || ' Annual Report' END) AS RptYrLbl , ARS.PO FROM ABSTRACT AB, RPU_ABSTRACT RAB , APU_REPORT_STATUS ARS WHERE ARS.ABSTRACT_ID = AB.ABSTRACT_ID (+) AND ARS.ABSTRACT_ID = RAB.ABSTRACT_ID (+) and ON_WEB = 0 and IS_SUPPRESS = 0 and IS_IN_PROCESS = 0 and IS_PUB_WF = 0 and IS_REQUIRE = 1 AND (lower(AB.ABS_TYPE) in ('grant','centers','sbir') OR lower(RAB.ABS_TYPE) in ('grant','centers','sbir')) and (ROUND(SYSDATE - ARS.DUE_DATE) > 0) and (ROUND(SYSDATE - ARS.DUE_DATE) <150) ORDER BY ARS.DUE_DATE, 1, ARS.RPT_YR;

Thanks in advance

Re: Tables Creating Syn keys although queries are in two different sections

Clement15 — Wed, 18 Dec 2024 16:12:57 GMT

Hello,
If you only have one field that serves as a key you can add

Qualify *;
Unqualify KeyField ;

Load ...

This will keep different names between tables to avoid synthetic keys, except for the key field that will serve as a link.

Re: Tables Creating Syn keys although queries are in two different sections

bruce_sorge — Wed, 18 Dec 2024 16:32:04 GMT

That did the trick. Thanks.

topic Tables Creating Syn keys although queries are in two different sections in Data Movement & Streaming

Tables Creating Syn keys although queries are in two different sections

Re: Tables Creating Syn keys although queries are in two different sections

Re: Tables Creating Syn keys although queries are in two different sections