SQL Error

Question

SQL Error

Justin Randolph

Old Hand

Points: 311
More actions
March 14, 2018 at 7:48 am

#333936

Hello Community! As I further my professionally career. I figured it was time to join a SQL Server forum as our Peoplesoft applications are on SQL Server instead of Oracle. We are in the process of upgrading to 9.2 from 8.9 if anyone has any questions in regards to Peoplesoft Upgrades.
Can someone assist me with the SQL below? I am running in to the following error.
Msg 156, Level 15, State 1, Line 60
Incorrect syntax near the keyword 'FROM'.
select ASSET_ID, DEPTID, FUND_CODE, PROJECT_ID, BUDGET_REF, SUM(TOTAL_FE)AS TOTAL_FE,
SUM(TOTAL_DEPR) AS TOTAL_DEPR, SUM(TOTAL_DEPR) + SUM(TOTAL_FE) AS EXCESS, MAX(MAX_OPEN_TRAN_DT) AS LAST_TRAN_DT
FROM(   select A.ASSET_ID, A.DEPTID, A.FUND_CODE, A.PROJECT_ID, A.BUDGET_REF,
        sum(A.AMOUNT)AS TOTAL_FE, 0 AS TOTAL_DEPR,
        MAX(A.DTTM_STAMP)
         AS MAX_OPEN_TRAN_DT
        FROM PS_DIST_LN A
        WHERE A.BUSINESS_UNIT = ''
        AND A.ACCOUNT BETWEEN '160000' AND '160099'
        AND A.DISTRIBUTION_TYPE IN ('FA', 'TI')
--and a.asset_id = ::1
        GROUP BY A.ASSET_ID, A.DEPTID, A.FUND_CODE, A.PROJECT_ID, A.BUDGET_REF
        UNION
        select B.ASSET_ID, B.DEPTID, B.FUND_CODE, B.PROJECT_ID, B.BUDGET_REF,
        0 AS TOTAL_FE, sum(B.AMOUNT)AS TOTAL_DEPR, MAX(B.DTTM_STAMP)
        AS MAX_OPEN_TRAN_DT
        FROM PS_DIST_LN B
        WHERE B.BUSINESS_UNIT = ''
        AND B.ACCOUNT BETWEEN '160000' AND '160099'
        AND B.DISTRIBUTION_TYPE IN ('AD', 'GA')
--and b.asset_id = ::1
    GROUP BY B.ASSET_ID, B.DEPTID, B.FUND_CODE, B.PROJECT_ID, B.BUDGET_REF
        UNION
        SELECT N.ASSET_ID, N.DEPTID, N.FUND_CODE, N.PROJECT_ID, N.BUDGET_REF,
        0 AS TOTAL_FE, 0 AS TOTAL_DEPR, MAX((N.TRANS_DT) AS MAX_OPEN_TRAN_DT
        FROM PS_COST N
        WHERE N.BUSINESS_UNIT = ''
        GROUP BY N.ASSET_ID, N.DEPTID, N.FUND_CODE, N.PROJECT_ID, N.BUDGET_REF)
GROUP BY ASSET_ID, DEPTID, FUND_CODE, PROJECT_ID, BUDGET_REF
HAVING -SUM(TOTAL_DEPR) - SUM(TOTAL_FE) > 0 OR SUM(TOTAL_DEPR) > SUM(TOTAL_FE);

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 1

justin.randolph - Wednesday, March 14, 2018 7:48 AM
Hello Community! As I further my professionally career. I figured it was time to join a SQL Server forum as our Peoplesoft applications are on SQL Server instead of Oracle. We are in the process of upgrading to 9.2 from 8.9 if anyone has any questions in regards to Peoplesoft Upgrades.
Can someone assist me with the SQL below? I am running in to the following error.
Msg 156, Level 15, State 1, Line 60
Incorrect syntax near the keyword 'FROM'.
select ASSET_ID, DEPTID, FUND_CODE, PROJECT_ID, BUDGET_REF, SUM(TOTAL_FE)AS TOTAL_FE,
SUM(TOTAL_DEPR) AS TOTAL_DEPR, SUM(TOTAL_DEPR) + SUM(TOTAL_FE) AS EXCESS, MAX(MAX_OPEN_TRAN_DT) AS LAST_TRAN_DT
FROM(   select A.ASSET_ID, A.DEPTID, A.FUND_CODE, A.PROJECT_ID, A.BUDGET_REF,
        sum(A.AMOUNT)AS TOTAL_FE, 0 AS TOTAL_DEPR,
        MAX(A.DTTM_STAMP)
         AS MAX_OPEN_TRAN_DT
        FROM PS_DIST_LN A
        WHERE A.BUSINESS_UNIT = ''
        AND A.ACCOUNT BETWEEN '160000' AND '160099'
        AND A.DISTRIBUTION_TYPE IN ('FA', 'TI')
--and a.asset_id = ::1
        GROUP BY A.ASSET_ID, A.DEPTID, A.FUND_CODE, A.PROJECT_ID, A.BUDGET_REF
        UNION
        select B.ASSET_ID, B.DEPTID, B.FUND_CODE, B.PROJECT_ID, B.BUDGET_REF,
        0 AS TOTAL_FE, sum(B.AMOUNT)AS TOTAL_DEPR, MAX(B.DTTM_STAMP)
        AS MAX_OPEN_TRAN_DT
        FROM PS_DIST_LN B
        WHERE B.BUSINESS_UNIT = ''
        AND B.ACCOUNT BETWEEN '160000' AND '160099'
        AND B.DISTRIBUTION_TYPE IN ('AD', 'GA')
--and b.asset_id = ::1
    GROUP BY B.ASSET_ID, B.DEPTID, B.FUND_CODE, B.PROJECT_ID, B.BUDGET_REF
        UNION
        SELECT N.ASSET_ID, N.DEPTID, N.FUND_CODE, N.PROJECT_ID, N.BUDGET_REF,
        0 AS TOTAL_FE, 0 AS TOTAL_DEPR, MAX((N.TRANS_DT) AS MAX_OPEN_TRAN_DT
        FROM PS_COST N
        WHERE N.BUSINESS_UNIT = ''
        GROUP BY N.ASSET_ID, N.DEPTID, N.FUND_CODE, N.PROJECT_ID, N.BUDGET_REF)
GROUP BY ASSET_ID, DEPTID, FUND_CODE, PROJECT_ID, BUDGET_REF
HAVING -SUM(TOTAL_DEPR) - SUM(TOTAL_FE) > 0 OR SUM(TOTAL_DEPR) > SUM(TOTAL_FE);

The number of output columns are differ between the select statements, union requires that those are the same and also the data types of each of those.
😎

HanShi SSC-Dedicated Points: 33508 More actions · Answer 2

You must give a name or alias to the sub select part.
I you enter a "x" (or even better: a more descriptive name) between the closing bracket and the last GROUP BY, the error would disappear.
The final part of your code would look like:
GROUP BY N.ASSET_ID , N.DEPTID , N.FUND_CODE , N.PROJECT_ID , N.BUDGET_REF ) x -- > this is the part where you give the sub query a name/alias GROUP BY ASSET_ID , DEPTID , FUND_CODE , PROJECT_ID , BUDGET_REF HAVING -SUM(TOTAL_DEPR) - SUM(TOTAL_FE) > 0 OR SUM(TOTAL_DEPR) > SUM(TOTAL_FE);

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Justin Randolph Old Hand Points: 311 More actions · Answer 3

HanShi - Wednesday, March 14, 2018 8:01 AM
You must give a name or alias to the sub select part.
I you enter a "x" (or even better: a more descriptive name) between the closing bracket and the last GROUP BY, the error would disappear.
The final part of your code would look like:
GROUP BY N.ASSET_ID , N.DEPTID , N.FUND_CODE , N.PROJECT_ID , N.BUDGET_REF ) x -- > this is the part where you give the sub query a name/alias GROUP BY ASSET_ID , DEPTID , FUND_CODE , PROJECT_ID , BUDGET_REF HAVING -SUM(TOTAL_DEPR) - SUM(TOTAL_FE) > 0 OR SUM(TOTAL_DEPR) > SUM(TOTAL_FE);

My apologies, maybe I'm writing it wrong. What is the syntax to give a sub query an alias?

HanShi SSC-Dedicated Points: 33508 More actions · Answer 4

In the code posted in my previous post it is just the letter "x".

justin.randolph - Wednesday, March 14, 2018 8:10 AM
HanShi - Wednesday, March 14, 2018 8:01 AM
You must give a name or alias to the sub select part.
I you enter a "x" (or even better: a more descriptive name) between the closing bracket and the last GROUP BY, the error would disappear.
The final part of your code would look like:
GROUP BY N.ASSET_ID , N.DEPTID , N.FUND_CODE , N.PROJECT_ID , N.BUDGET_REF ) x -- > this is the part where you give the sub query a name/alias GROUP BY ASSET_ID , DEPTID , FUND_CODE , PROJECT_ID , BUDGET_REF HAVING -SUM(TOTAL_DEPR) - SUM(TOTAL_FE) > 0 OR SUM(TOTAL_DEPR) > SUM(TOTAL_FE);
My apologies, maybe I'm writing it wrong. What is the syntax to give a sub query an alias?

In my previous post it is just the letter "x". The alias you state can be used if you use the two-part naming convention in the outer SELECT.

Perhaps the code below is a more clear example:
select alias_name.* -- here in the outer part the alias can be use in the two-part naming convention from ( -- the subquery is stated between brackets select 1 as val union select 2 ) as alias_name -- you need to provide a name/alias for the sub query (after you stated the closing bracket) -- (in the above case I've named it "alias_name"; the "as" key word is optional)
If you just end the code with the closing bracket (without the " as alias_name" part) it will generate an error.

If you want to extend the code with a WHERE or ORDER BY on the outer most SELECT you can add it after the alias name of the sub query like in the sample below.
select * from ( select 1 as val union select 2 ) as alias_name order by val desc

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Justin Randolph Old Hand Points: 311 More actions · Answer 5

HanShi - Wednesday, March 14, 2018 8:35 AM
In the code posted in my previous post it is just the letter "x".
justin.randolph - Wednesday, March 14, 2018 8:10 AM
HanShi - Wednesday, March 14, 2018 8:01 AM
You must give a name or alias to the sub select part.
I you enter a "x" (or even better: a more descriptive name) between the closing bracket and the last GROUP BY, the error would disappear.
The final part of your code would look like:
   GROUP BY N.ASSET_ID         , N.DEPTID         , N.FUND_CODE         , N.PROJECT_ID         , N.BUDGET_REF     ) x -- > this is the part where you give the sub query a name/alias GROUP BY ASSET_ID     , DEPTID     , FUND_CODE     , PROJECT_ID     , BUDGET_REF HAVING -SUM(TOTAL_DEPR) - SUM(TOTAL_FE) > 0     OR SUM(TOTAL_DEPR) > SUM(TOTAL_FE);
My apologies, maybe I'm writing it wrong. What is the syntax to give a sub query an alias?
In my previous post it is just the letter "x". The alias you state can be used if you use the two-part naming convention in the outer SELECT.
Perhaps the code below is a more clear example:
select alias_name.* -- here in the outer part the alias can be use in the two-part naming convention from (     -- the subquery is stated between brackets     select 1 as val     union     select 2     ) as alias_name     -- you need to provide a name/alias for the sub query (after you stated the closing bracket)     -- (in the above case I've named it "alias_name"; the "as" key word is optional)
If you just end the code with the closing bracket (without the " as alias_name" part) it will generate an error.
If you want to extend the code with a WHERE or ORDER BY on the outer most SELECT you can add it after the alias name of the sub query like in the sample below.
select * from ( select 1 as val     union     select 2     ) as alias_name order by val desc

HanShi, can you do me a huge favor? When you have time can you just edit my SQL to fit gap my knowledge barrier so I can then just compare my old SQL with the new one provided by you to give a better understanding?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I found an extraneous ( where a MAX function is being called. After deleting that extra ( I was able to format the code using SQL Prompt.
SELECT [ASSET_ID] , [DEPTID] , [FUND_CODE] , [PROJECT_ID] , [BUDGET_REF] , SUM([TOTAL_FE]) AS [TOTAL_FE] , SUM([TOTAL_DEPR]) AS [TOTAL_DEPR] , SUM([TOTAL_DEPR]) + SUM([TOTAL_FE]) AS [EXCESS] , MAX([MAX_OPEN_TRAN_DT]) AS [LAST_TRAN_DT] FROM ( SELECT [A].[ASSET_ID] , [A].[DEPTID] , [A].[FUND_CODE] , [A].[PROJECT_ID] , [A].[BUDGET_REF] , SUM([A].[AMOUNT]) AS [TOTAL_FE] , 0 AS [TOTAL_DEPR] , MAX([A].[DTTM_STAMP]) AS [MAX_OPEN_TRAN_DT] FROM [PS_DIST_LN] [A] WHERE [A].[BUSINESS_UNIT] = '' AND [A].[ACCOUNT] BETWEEN '160000' AND '160099' AND [A].[DISTRIBUTION_TYPE] IN ( 'FA' , 'TI' ) --and a.asset_id = ::1 GROUP BY [A].[ASSET_ID] , [A].[DEPTID] , [A].[FUND_CODE] , [A].[PROJECT_ID] , [A].[BUDGET_REF] UNION SELECT .[ASSET_ID] , .[DEPTID] , .[FUND_CODE] , .[PROJECT_ID] , .[BUDGET_REF] , 0 AS [TOTAL_FE] , SUM(.[AMOUNT]) AS [TOTAL_DEPR] , MAX(.[DTTM_STAMP]) AS [MAX_OPEN_TRAN_DT] FROM [PS_DIST_LN] WHERE .[BUSINESS_UNIT] = '' AND .[ACCOUNT] BETWEEN '160000' AND '160099' AND .[DISTRIBUTION_TYPE] IN ( 'AD' , 'GA' ) --and b.asset_id = ::1 GROUP BY .[ASSET_ID] , .[DEPTID] , .[FUND_CODE] , .[PROJECT_ID] , .[BUDGET_REF] UNION SELECT [N].[ASSET_ID] , [N].[DEPTID] , [N].[FUND_CODE] , [N].[PROJECT_ID] , [N].[BUDGET_REF] , 0 AS [TOTAL_FE] , 0 AS [TOTAL_DEPR] , MAX([N].[TRANS_DT]) AS [MAX_OPEN_TRAN_DT] FROM [PS_COST] [N] WHERE [N].[BUSINESS_UNIT] = '' GROUP BY [N].[ASSET_ID] , [N].[DEPTID] , [N].[FUND_CODE] , [N].[PROJECT_ID] , [N].[BUDGET_REF] ) [dt] GROUP BY [ASSET_ID] , [DEPTID] , [FUND_CODE] , [PROJECT_ID] , [BUDGET_REF] HAVING -SUM([TOTAL_DEPR]) - SUM([TOTAL_FE]) > 0 OR SUM([TOTAL_DEPR]) > SUM([TOTAL_FE]);

Justin Randolph Old Hand Points: 311 More actions · Answer 7

Lynn Pettis - Wednesday, March 14, 2018 9:08 AM
I found an extraneous ( where a MAX function is being called. After deleting that extra ( I was able to format the code using SQL Prompt.
SELECT [ASSET_ID] , [DEPTID] , [FUND_CODE] , [PROJECT_ID] , [BUDGET_REF] , SUM([TOTAL_FE]) AS [TOTAL_FE] , SUM([TOTAL_DEPR]) AS [TOTAL_DEPR] , SUM([TOTAL_DEPR]) + SUM([TOTAL_FE]) AS [EXCESS] , MAX([MAX_OPEN_TRAN_DT]) AS [LAST_TRAN_DT] FROM ( SELECT [A].[ASSET_ID] , [A].[DEPTID] , [A].[FUND_CODE] , [A].[PROJECT_ID] , [A].[BUDGET_REF] , SUM([A].[AMOUNT]) AS [TOTAL_FE] , 0 AS [TOTAL_DEPR] , MAX([A].[DTTM_STAMP]) AS [MAX_OPEN_TRAN_DT] FROM [PS_DIST_LN] [A] WHERE [A].[BUSINESS_UNIT] = '' AND [A].[ACCOUNT] BETWEEN '160000' AND '160099' AND [A].[DISTRIBUTION_TYPE] IN ( 'FA' , 'TI' ) --and a.asset_id = ::1 GROUP BY [A].[ASSET_ID] , [A].[DEPTID] , [A].[FUND_CODE] , [A].[PROJECT_ID] , [A].[BUDGET_REF] UNION SELECT .[ASSET_ID] , .[DEPTID] , .[FUND_CODE] , .[PROJECT_ID] , .[BUDGET_REF] , 0 AS [TOTAL_FE] , SUM(.[AMOUNT]) AS [TOTAL_DEPR] , MAX(.[DTTM_STAMP]) AS [MAX_OPEN_TRAN_DT] FROM [PS_DIST_LN] WHERE .[BUSINESS_UNIT] = '' AND .[ACCOUNT] BETWEEN '160000' AND '160099' AND .[DISTRIBUTION_TYPE] IN ( 'AD' , 'GA' ) --and b.asset_id = ::1 GROUP BY .[ASSET_ID] , .[DEPTID] , .[FUND_CODE] , .[PROJECT_ID] , .[BUDGET_REF] UNION SELECT [N].[ASSET_ID] , [N].[DEPTID] , [N].[FUND_CODE] , [N].[PROJECT_ID] , [N].[BUDGET_REF] , 0 AS [TOTAL_FE] , 0 AS [TOTAL_DEPR] , MAX([N].[TRANS_DT]) AS [MAX_OPEN_TRAN_DT] FROM [PS_COST] [N] WHERE [N].[BUSINESS_UNIT] = '' GROUP BY [N].[ASSET_ID] , [N].[DEPTID] , [N].[FUND_CODE] , [N].[PROJECT_ID] , [N].[BUDGET_REF] ) [dt] GROUP BY [ASSET_ID] , [DEPTID] , [FUND_CODE] , [PROJECT_ID] , [BUDGET_REF] HAVING -SUM([TOTAL_DEPR]) - SUM([TOTAL_FE]) > 0 OR SUM([TOTAL_DEPR]) > SUM([TOTAL_FE]);

Your a genius! thank you so much!
Why do you use so brackets? Is it just best practice? The query returned 17000 Rows :Wow:
Next is going to be adding criteria to limit 'I' (Inactive) assets & ones that were already depreciated. Thank you Lynn!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

justin.randolph - Wednesday, March 14, 2018 10:02 AM
Lynn Pettis - Wednesday, March 14, 2018 9:08 AM
I found an extraneous ( where a MAX function is being called. After deleting that extra ( I was able to format the code using SQL Prompt.
SELECT [ASSET_ID] , [DEPTID] , [FUND_CODE] , [PROJECT_ID] , [BUDGET_REF] , SUM([TOTAL_FE]) AS [TOTAL_FE] , SUM([TOTAL_DEPR]) AS [TOTAL_DEPR] , SUM([TOTAL_DEPR]) + SUM([TOTAL_FE]) AS [EXCESS] , MAX([MAX_OPEN_TRAN_DT]) AS [LAST_TRAN_DT] FROM ( SELECT [A].[ASSET_ID] , [A].[DEPTID] , [A].[FUND_CODE] , [A].[PROJECT_ID] , [A].[BUDGET_REF] , SUM([A].[AMOUNT]) AS [TOTAL_FE] , 0 AS [TOTAL_DEPR] , MAX([A].[DTTM_STAMP]) AS [MAX_OPEN_TRAN_DT] FROM [PS_DIST_LN] [A] WHERE [A].[BUSINESS_UNIT] = '' AND [A].[ACCOUNT] BETWEEN '160000' AND '160099' AND [A].[DISTRIBUTION_TYPE] IN ( 'FA' , 'TI' ) --and a.asset_id = ::1 GROUP BY [A].[ASSET_ID] , [A].[DEPTID] , [A].[FUND_CODE] , [A].[PROJECT_ID] , [A].[BUDGET_REF] UNION SELECT .[ASSET_ID] , .[DEPTID] , .[FUND_CODE] , .[PROJECT_ID] , .[BUDGET_REF] , 0 AS [TOTAL_FE] , SUM(.[AMOUNT]) AS [TOTAL_DEPR] , MAX(.[DTTM_STAMP]) AS [MAX_OPEN_TRAN_DT] FROM [PS_DIST_LN] WHERE .[BUSINESS_UNIT] = '' AND .[ACCOUNT] BETWEEN '160000' AND '160099' AND .[DISTRIBUTION_TYPE] IN ( 'AD' , 'GA' ) --and b.asset_id = ::1 GROUP BY .[ASSET_ID] , .[DEPTID] , .[FUND_CODE] , .[PROJECT_ID] , .[BUDGET_REF] UNION SELECT [N].[ASSET_ID] , [N].[DEPTID] , [N].[FUND_CODE] , [N].[PROJECT_ID] , [N].[BUDGET_REF] , 0 AS [TOTAL_FE] , 0 AS [TOTAL_DEPR] , MAX([N].[TRANS_DT]) AS [MAX_OPEN_TRAN_DT] FROM [PS_COST] [N] WHERE [N].[BUSINESS_UNIT] = '' GROUP BY [N].[ASSET_ID] , [N].[DEPTID] , [N].[FUND_CODE] , [N].[PROJECT_ID] , [N].[BUDGET_REF] ) [dt] GROUP BY [ASSET_ID] , [DEPTID] , [FUND_CODE] , [PROJECT_ID] , [BUDGET_REF] HAVING -SUM([TOTAL_DEPR]) - SUM([TOTAL_FE]) > 0 OR SUM([TOTAL_DEPR]) > SUM([TOTAL_FE]);
Your a genius! thank you so much!
Why do you use so brackets? Is it just best practice? The query returned 17000 Rows :Wow:
Next is going to be adding criteria to limit 'I' (Inactive) assets & ones that were already depreciated. Thank you Lynn!

Because in the database I support it is safer. Columns with reserved names, people creating indexes with special characters. I write a lot of dynamic SQL as well and it has just become a defense mechanism to prevent errors because others think they know what they are doing.

Also, have fun with PeopleSoft. I provided a small group of PeopleSoft developers with SQL Server support at a previous employer. It was fun, but I never had to actually do anything more than SQL stuff.
They handled everything else including upgrades and migrations.

Justin Randolph Old Hand Points: 311 More actions · Answer 9

Lynn Pettis - Wednesday, March 14, 2018 10:08 AM
justin.randolph - Wednesday, March 14, 2018 10:02 AM
Lynn Pettis - Wednesday, March 14, 2018 9:08 AM
I found an extraneous ( where a MAX function is being called. After deleting that extra ( I was able to format the code using SQL Prompt.
SELECT [ASSET_ID] , [DEPTID] , [FUND_CODE] , [PROJECT_ID] , [BUDGET_REF] , SUM([TOTAL_FE]) AS [TOTAL_FE] , SUM([TOTAL_DEPR]) AS [TOTAL_DEPR] , SUM([TOTAL_DEPR]) + SUM([TOTAL_FE]) AS [EXCESS] , MAX([MAX_OPEN_TRAN_DT]) AS [LAST_TRAN_DT] FROM ( SELECT [A].[ASSET_ID] , [A].[DEPTID] , [A].[FUND_CODE] , [A].[PROJECT_ID] , [A].[BUDGET_REF] , SUM([A].[AMOUNT]) AS [TOTAL_FE] , 0 AS [TOTAL_DEPR] , MAX([A].[DTTM_STAMP]) AS [MAX_OPEN_TRAN_DT] FROM [PS_DIST_LN] [A] WHERE [A].[BUSINESS_UNIT] = '' AND [A].[ACCOUNT] BETWEEN '160000' AND '160099' AND [A].[DISTRIBUTION_TYPE] IN ( 'FA' , 'TI' ) --and a.asset_id = ::1 GROUP BY [A].[ASSET_ID] , [A].[DEPTID] , [A].[FUND_CODE] , [A].[PROJECT_ID] , [A].[BUDGET_REF] UNION SELECT .[ASSET_ID] , .[DEPTID] , .[FUND_CODE] , .[PROJECT_ID] , .[BUDGET_REF] , 0 AS [TOTAL_FE] , SUM(.[AMOUNT]) AS [TOTAL_DEPR] , MAX(.[DTTM_STAMP]) AS [MAX_OPEN_TRAN_DT] FROM [PS_DIST_LN] WHERE .[BUSINESS_UNIT] = '' AND .[ACCOUNT] BETWEEN '160000' AND '160099' AND .[DISTRIBUTION_TYPE] IN ( 'AD' , 'GA' ) --and b.asset_id = ::1 GROUP BY .[ASSET_ID] , .[DEPTID] , .[FUND_CODE] , .[PROJECT_ID] , .[BUDGET_REF] UNION SELECT [N].[ASSET_ID] , [N].[DEPTID] , [N].[FUND_CODE] , [N].[PROJECT_ID] , [N].[BUDGET_REF] , 0 AS [TOTAL_FE] , 0 AS [TOTAL_DEPR] , MAX([N].[TRANS_DT]) AS [MAX_OPEN_TRAN_DT] FROM [PS_COST] [N] WHERE [N].[BUSINESS_UNIT] = '' GROUP BY [N].[ASSET_ID] , [N].[DEPTID] , [N].[FUND_CODE] , [N].[PROJECT_ID] , [N].[BUDGET_REF] ) [dt] GROUP BY [ASSET_ID] , [DEPTID] , [FUND_CODE] , [PROJECT_ID] , [BUDGET_REF] HAVING -SUM([TOTAL_DEPR]) - SUM([TOTAL_FE]) > 0 OR SUM([TOTAL_DEPR]) > SUM([TOTAL_FE]);
Your a genius! thank you so much!
Why do you use so brackets? Is it just best practice? The query returned 17000 Rows :Wow:
Next is going to be adding criteria to limit 'I' (Inactive) assets & ones that were already depreciated. Thank you Lynn!
Because in the database I support it is safer. Columns with reserved names, people creating indexes with special characters. I write a lot of dynamic SQL as well and it has just become a defense mechanism to prevent errors because others think they know what they are doing.

I'll be honest here. I don't know anything, just enough to take down a database. I'm under the mentorship of a few coworkers as I grow in my career and it's nice to bounce things off of people as well but I need to refine and engrain my SQL skills deeply. Do you have any recommendations for books or other websites that I can use for a community?

Thank you! I deal mostly with the HCM side of PeopleSoft. I was initially hired on in HR... But now I'm in Finance and dealing with Payroll/Project Costing/Accounting. Its a good way to broaden my skill set. I'm still in college though at the old age of 21.
Do you have any recommendations for Table Locking on SQL Server? I know that's a broad question but our vendor is clueless (CIBER) - to that there are some wonderful people at CIBER but its been an interesting upgrade to say the least.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

justin.randolph - Wednesday, March 14, 2018 10:13 AM
I'll be honest here. I don't know anything, just enough to take down a database. I'm under the mentorship of a few coworkers as I grow in my career and it's nice to bounce things off of people as well but I need to refine and engrain my SQL skills deeply. Do you have any recommendations for books or other websites that I can use for a community?
Thank you! I deal mostly with the HCM side of PeopleSoft. I was initially hired on in HR... But now I'm in Finance and dealing with Payroll/Project Costing/Accounting. Its a good way to broaden my skill set. I'm still in college though at the old age of 21.
Do you have any recommendations for Table Locking on SQL Server? I know that's a broad question but our vendor is clueless (CIBER) - to that there are some wonderful people at CIBER but its been an interesting upgrade to say the least.

Where I worked, our PeopleSoft team did all the support. We got PeopleSoft software directly from PeopleSoft (read that as Oracle and yes, we ran on SQL Server).