March 14, 2018 at 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);
March 14, 2018 at 8:00 am
justin.randolph - Wednesday, March 14, 2018 7:48 AMHello 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.
😎
March 14, 2018 at 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);
March 14, 2018 at 8:10 am
HanShi - Wednesday, March 14, 2018 8:01 AMYou 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?
March 14, 2018 at 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 AMHanShi - Wednesday, March 14, 2018 8:01 AMYou 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
March 14, 2018 at 9:02 am
HanShi - Wednesday, March 14, 2018 8:35 AMIn the code posted in my previous post it is just the letter "x".justin.randolph - Wednesday, March 14, 2018 8:10 AMHanShi - Wednesday, March 14, 2018 8:01 AMYou 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?
March 14, 2018 at 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]);
March 14, 2018 at 10:02 am
Lynn Pettis - Wednesday, March 14, 2018 9:08 AMI 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!
March 14, 2018 at 10:08 am
justin.randolph - Wednesday, March 14, 2018 10:02 AMLynn Pettis - Wednesday, March 14, 2018 9:08 AMI 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.
March 14, 2018 at 10:13 am
Lynn Pettis - Wednesday, March 14, 2018 10:08 AMjustin.randolph - Wednesday, March 14, 2018 10:02 AMLynn Pettis - Wednesday, March 14, 2018 9:08 AMI 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.
March 14, 2018 at 10:45 am
justin.randolph - Wednesday, March 14, 2018 10:13 AMI'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).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply