I'm attempting to make a select union statement into it's own temp table. Essentially, the end of my query has
SELECT * FROM PAY_BILL
UNION
SELECT * FROM BILL_ADJ_WE
And I want to create a more consolidated select statement, because I don't need all of the fields these two selects are retrieving. I also want to add a section that provides values from one table that correlate with values from another table. Any suggestions as to where I should begin? This is what I've attempted but it didn't work. It should also be noted, the actual query works, but when I attempt to add this last temp table, the query blows up.
,
PRELIM_DATA (INVOICE, CUST_ID, CUSTOMER_NAME, ASSIGNMENT_OWNING_DEPTID, ORDER_OWNING_DEPTID,
VENDOR_ID, VENDOR_NAME, BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, EMPLID, EMPL_RCD, NAME,
FOBU, ORDER_ID, ASSIGNMENT_ID,JOBTITLE, CLIENT_JOB_TITLE,
PROJECT_DESCR, PROJECT_TYPE, LINE_OF_BUSINESS, SERVICE_LINE,
ACCOUNTING_DT, WE_DATE, ANALYSIS_TYPE, RESOURCE_CATEGORY,
TIME_RPTG_CD, HOURS, AMOUNT)
AS
(
SELECT * FROM PAY_BILL
UNION
SELECT * FROM BILL_ADJ_WE
)
,
This will provide me with Resource category values
RES_CAT(RESOURCE_CATEGORY, TRC)
AS
(
SELECT DISTINCT COALESCE (LN.TGT_RES_CATEGORY, ' ') AS RESOURCE_CATEGORY, TR.TRC
FROM PS_TL_TRC_TBL TR LEFT OUTER JOIN PS_FO_RATE_PROFILE RP
ON TR.TRC = RP.TIME_RPTG_CD
LEFT OUTER JOIN PS_FO_RATE_PRFL_LN LN
ON LN.SETID = RP.SETID
AND LN.RATE_PROFILE_TYPE = RP.RATE_PROFILE_TYPE
AND LN.DETAIL_NO = RP.DETAIL_NO
AND LN.SEQ_NUM = RP.SEQ_NUM
)
Final Select statement
SELECT A.INVOICE,
A.CUST_ID,
A.CUSTOMER_NAME,
-- A.ASSIGNMENT_OWNING_DEPTID,
A.ORDER_OWNING_DEPTID,
A.VENDOR_ID,
A.VENDOR_NAME,
-- A.BUSINESS_UNIT,
-- A.PROJECT_ID,
-- A.ACTIVITY_ID,
A.EMPLID,
A.EMPL_RCD,
A.NAME,
-- A.FOBU,
-- A.ORDER_ID,
-- A.ASSIGNMENT_ID,
-- A.JOBTITLE,
A.CLIENT_JOB_TITLE,
A.PROJECT_DESCR,
-- A.PROJECT_TYPE,
A.LINE_OF_BUSINESS,
A.SERVICE_LINE,
A.ACCOUNTING_DT,
B.END_DT AS WE_DATE,
A.ANALYSIS_TYPE,
A.RESOURCE_CATEGORY,
A.TIME_RPTG_CD,
SUM(B.QTY) AS HOURS,
SUM(B.NET_EXTENDED_AMT) AS AMOUNT
FROM PRELIM_DATA A INNER JOIN RES_CAT B
ON A.RESOURCE_CATEGORY = B.RESOURCE_CATEGORY
GROUP BY A.INVOICE,
A.CUST_ID,
A.CUSTOMER_NAME,
-- A.ASSIGNMENT_OWNING_DEPTID,
A.ORDER_OWNING_DEPTID,
A.VENDOR_ID,
A.VENDOR_NAME,
-- A.BUSINESS_UNIT,
-- A.PROJECT_ID,
-- A.ACTIVITY_ID,
A.EMPLID,
A.EMPL_RCD,
A.NAME,
-- A.FOBU,
-- A.ORDER_ID,
-- A.ASSIGNMENT_ID,
-- A.JOBTITLE,
A.CLIENT_JOB_TITLE,
A.PROJECT_DESCR,
-- A.PROJECT_TYPE,
A.LINE_OF_BUSINESS,
A.SERVICE_LINE,
A.ACCOUNTING_DT,
B.END_DT AS WE_DATE,
A.ANALYSIS_TYPE,
A.RESOURCE_CATEGORY,
A.TIME_RPTG_CD
As you can see, I am attempting to take the information from the initial query and connect it to a new temp table that that will provide me with values that will populate sections that were originally left blank. Any help is much appreciated. I don't know if I have explained myself correctly, so I will be monitoring in case anyone needs any more information. Thank you very much.
SELECT * FROM PAY_BILL
UNION
SELECT * FROM BILL_ADJ_WE
And I want to create a more consolidated select statement, because I don't need all of the fields these two selects are retrieving. I also want to add a section that provides values from one table that correlate with values from another table. Any suggestions as to where I should begin? This is what I've attempted but it didn't work. It should also be noted, the actual query works, but when I attempt to add this last temp table, the query blows up.
,
PRELIM_DATA (INVOICE, CUST_ID, CUSTOMER_NAME, ASSIGNMENT_OWNING_DEPTID, ORDER_OWNING_DEPTID,
VENDOR_ID, VENDOR_NAME, BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, EMPLID, EMPL_RCD, NAME,
FOBU, ORDER_ID, ASSIGNMENT_ID,JOBTITLE, CLIENT_JOB_TITLE,
PROJECT_DESCR, PROJECT_TYPE, LINE_OF_BUSINESS, SERVICE_LINE,
ACCOUNTING_DT, WE_DATE, ANALYSIS_TYPE, RESOURCE_CATEGORY,
TIME_RPTG_CD, HOURS, AMOUNT)
AS
(
SELECT * FROM PAY_BILL
UNION
SELECT * FROM BILL_ADJ_WE
)
,
This will provide me with Resource category values
RES_CAT(RESOURCE_CATEGORY, TRC)
AS
(
SELECT DISTINCT COALESCE (LN.TGT_RES_CATEGORY, ' ') AS RESOURCE_CATEGORY, TR.TRC
FROM PS_TL_TRC_TBL TR LEFT OUTER JOIN PS_FO_RATE_PROFILE RP
ON TR.TRC = RP.TIME_RPTG_CD
LEFT OUTER JOIN PS_FO_RATE_PRFL_LN LN
ON LN.SETID = RP.SETID
AND LN.RATE_PROFILE_TYPE = RP.RATE_PROFILE_TYPE
AND LN.DETAIL_NO = RP.DETAIL_NO
AND LN.SEQ_NUM = RP.SEQ_NUM
)
Final Select statement
SELECT A.INVOICE,
A.CUST_ID,
A.CUSTOMER_NAME,
-- A.ASSIGNMENT_OWNING_DEPTID,
A.ORDER_OWNING_DEPTID,
A.VENDOR_ID,
A.VENDOR_NAME,
-- A.BUSINESS_UNIT,
-- A.PROJECT_ID,
-- A.ACTIVITY_ID,
A.EMPLID,
A.EMPL_RCD,
A.NAME,
-- A.FOBU,
-- A.ORDER_ID,
-- A.ASSIGNMENT_ID,
-- A.JOBTITLE,
A.CLIENT_JOB_TITLE,
A.PROJECT_DESCR,
-- A.PROJECT_TYPE,
A.LINE_OF_BUSINESS,
A.SERVICE_LINE,
A.ACCOUNTING_DT,
B.END_DT AS WE_DATE,
A.ANALYSIS_TYPE,
A.RESOURCE_CATEGORY,
A.TIME_RPTG_CD,
SUM(B.QTY) AS HOURS,
SUM(B.NET_EXTENDED_AMT) AS AMOUNT
FROM PRELIM_DATA A INNER JOIN RES_CAT B
ON A.RESOURCE_CATEGORY = B.RESOURCE_CATEGORY
GROUP BY A.INVOICE,
A.CUST_ID,
A.CUSTOMER_NAME,
-- A.ASSIGNMENT_OWNING_DEPTID,
A.ORDER_OWNING_DEPTID,
A.VENDOR_ID,
A.VENDOR_NAME,
-- A.BUSINESS_UNIT,
-- A.PROJECT_ID,
-- A.ACTIVITY_ID,
A.EMPLID,
A.EMPL_RCD,
A.NAME,
-- A.FOBU,
-- A.ORDER_ID,
-- A.ASSIGNMENT_ID,
-- A.JOBTITLE,
A.CLIENT_JOB_TITLE,
A.PROJECT_DESCR,
-- A.PROJECT_TYPE,
A.LINE_OF_BUSINESS,
A.SERVICE_LINE,
A.ACCOUNTING_DT,
B.END_DT AS WE_DATE,
A.ANALYSIS_TYPE,
A.RESOURCE_CATEGORY,
A.TIME_RPTG_CD
As you can see, I am attempting to take the information from the initial query and connect it to a new temp table that that will provide me with values that will populate sections that were originally left blank. Any help is much appreciated. I don't know if I have explained myself correctly, so I will be monitoring in case anyone needs any more information. Thank you very much.
