SQL - UNION QUERY REVIEW COMMAND

Lani Vriezema
Lani Vriezema used Ask the Experts™
on
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"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Kent OlsenData Warehouse / Database Architect

Commented:
In general, the query looks OK.  

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?
Lani Vriezema

Author

Commented:
Failed to retrieve data
Lani Vriezema

Author

Commented:
Should I join the tables and not specific columns from each table?
Data Warehouse / Database Architect
Commented:
What's your database engine?  The syntax looks a bit odd to be one of the majors.

Here's the query, reformatted for readability.  

- The first sort is ignored since the last sort will sort all of the rows, not just the lower query.

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"

Open in new window

Lani Vriezema

Author

Commented:
Kent,

Thank you for your help!

Best Regards,

Lani

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial