Hello,
I am trying to join two tables and I am not sure if I wrote the command right.
SELECT "PRT_CURRENT__TIME"."Period_End_Date", "PRT_CURRENT__TIME"."Units", "PRT_CURRENT__TIME"."Pay_ID", "PRT_CURRENT__TIME"."Job", "JCM_MASTER__JOB"."Description", "PRT_CURRENT__TIME"."Amount", "PRT_CURRENT__TIME"."Employer_Taxes", "PRT_CURRENT__TIME"."Fringes", "PRT_CURRENT__TIME"."Cash_Fringes", "PRM_MASTER__EMPLOYEE"."Employee_Name", "JCM_MASTER__JOB"."Cost_Account_Prefix"
FROM ("\\TEST\Timberline Data\TEST\"."PRT_CURRENT__TIME" "PRT_CURRENT__TIME" INNER JOIN "\\TEST\Timberline Data\TEST\"."JCM_MASTER__JOB" "JCM_MASTER__JOB" ON "PRT_CURRENT__TIME"."Job"="JCM_MASTER__JOB"."Job") INNER JOIN "\\TEST\Timberline Data\TEST\"."PRM_MASTER__EMPLOYEE" "PRM_MASTER__EMPLOYEE" ON "PRT_CURRENT__TIME"."Employee"="PRM_MASTER__EMPLOYEE"."Employee"
WHERE "PRT_CURRENT__TIME"."Period_End_Date">={d '2019-12-12'} AND "JCM_MASTER__JOB"."Cost_Account_Prefix"='20'
ORDER BY "JCM_MASTER__JOB"."Cost_Account_Prefix", "PRT_CURRENT__TIME"."Job", "PRM_MASTER__EMPLOYEE"."Employee_Name"
UNION
SELECT "PRT_EXECCURENT__TIME"."Period_End_Date", "PRT_EXECCURENT__TIME"."Units", "PRT_EXECCURENT__TIME"."Pay_ID", "PRT_EXECCURENT__TIME"."Job", "JCM_EXECMASTER__JOB"."Description", "PRT_EXECCURENT__TIME"."Amount", "PRT_EXECCURENT__TIME"."Employer_Taxes", "PRT_EXECCURENT__TIME"."Fringes", "PRT_EXECCURENT__TIME"."Cash_Fringes", "PRM_EXECMASTER__EMPLOYEE"."Employee_Name", "JCM_EXECMASTER__JOB"."Cost_Account_Prefix"
FROM ("\\TEST\Timberline Data\TEST\"."PRT_EXECCURENT__TIME" "PRT_EXECCURENT__TIME" INNER JOIN "\\TEST\Timberline Data\TEST\"."JCM_EXECMASTER__JOB" "JCM_EXECMASTER__JOB" ON "PRT_EXECCURENT__TIME"."Job"="JCM_EXECMASTER__JOB"."Job") INNER JOIN "\\TEST\Timberline Data\TEST\"."PRM_EXECMASTER__EMPLOYEE" "PRM_EXECMASTER__EMPLOYEE" ON "PRT_EXECCURENT__TIME"."Employee"="PRM_EXECMASTER__EMPLOYEE"."Employee"
WHERE "PRT_EXECCURENT__TIME"."Period_End_Date">={d '2019-12-12'} AND "JCM_EXECMASTER__JOB"."Cost_Account_Prefix"='20'
ORDER BY "JCM_EXECMASTER__JOB"."Cost_Account_Prefix", "PRT_EXECCURENT__TIME"."Job", "PRM_EXECMASTER__EMPLOYEE"."Employee_Name"
UNION and UNION ALL are similar operations. If you want all the rows from both tables, use UNION ALL. Otherwise the duplicates will be filtered out.
Your sort columns are from the different tables. The UNION/UNION ALL operators require that the columns in both queries match in number and data type. Sorting on rows from each query suggests that the column lists may not match.
What happens when you run it?