Need help with subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Need help with subquery

Author

Topic

CHAR
Starting Member

2 Posts

Posted - 2011-09-07 : 21:31:16

I am doing this report for work, long overdue. OK, I think I almost got this, but I am still getting a "Incorrect syntax near keyword "FROM" or "(" Can you tell me what I am not seeing on this?

SELECT fgc, sum(Totpay) as Totpay, Sum(TotChg) as TotChg
from
(
SELECT fgc, sum(pay) as TotPay, 0 as TotChg

(SELECT fgc, pay,
CASE
WHEN [date]<= 30 THEN 'pmt 0-30'
WHEN [date]> 30 AND [date] <= 60 THEN 'pmt 30-60'
WHEN [date]> 61 AND [date] <= 90 THEN 'pmt 61-90'
WHEN [date]> 91 AND [date] <= 120 THEN 'pmt 91-120'
WHEN [date]> 121 AND [date] <= 150 THEN 'pmt 121-150'
WHEN [date]> 151 AND [date] <= 180 THEN 'pmt 151-180'
ELSE 'pmt 181+'
END)
FROM @Pay
UNION

SELECT fgc, 0 as TotPay, sum(chg) as TotChg

(SELECT fgc, chg,
CASE
WHEN [date]<= 30 THEN 'pmt 0-30'
WHEN [date]> 30 AND [date] <= 60 THEN 'pmt 30-60'
WHEN [date]> 61 AND [date] <= 90 THEN 'pmt 61-90'
WHEN [date]> 91 AND [date] <= 120 THEN 'pmt 91-120'
WHEN [date]> 121 AND [date] <= 150 THEN 'pmt 121-150'
WHEN [date]> 151 AND [date] <= 180 THEN 'pmt 151-180'
ELSE 'pmt 181+'
END)
FROM @Chg
)
GROUP BY fgc

bobmcclellan
Starting Member

46 Posts

Posted - 2011-09-08 : 08:42:01

try naming the subquery...

)x --<--
GROUP BY fgc
hth,
..bob

ssdeveloper
Starting Member

37 Posts

Posted - 2011-09-08 : 09:20:37

WITH #CTE
(
b.fgc, sum(b.Totpay) as Totpay, Sum(b.TotChg) as TotChg
)
AS
(
SELECT b.*
FROM
(
SELECT a.fgc, sum(a.pay) as TotPay, 0 as TotChg
from
(
SELECT fgc, pay,
CASE
WHEN [date]<= 30 THEN 'pmt 0-30'
WHEN [date]> 30 AND [date] <= 60 THEN 'pmt 30-60'
WHEN [date]> 61 AND [date] <= 90 THEN 'pmt 61-90'
WHEN [date]> 91 AND [date] <= 120 THEN 'pmt 91-120'
WHEN [date]> 121 AND [date] <= 150 THEN 'pmt 121-150'
WHEN [date]> 151 AND [date] <= 180 THEN 'pmt 151-180'
ELSE 'pmt 181+'
END as (you have to name case stmt)
FROM @Pay
) a
) b
UNION

SELECT
fgc, 0 as TotPay, sum(chg) as TotChg
FROM
(
SELECT fgc, chg,
CASE
WHEN [date]<= 30 THEN 'pmt 0-30'
WHEN [date]> 30 AND [date] <= 60 THEN 'pmt 30-60'
WHEN [date]> 61 AND [date] <= 90 THEN 'pmt 61-90'
WHEN [date]> 91 AND [date] <= 120 THEN 'pmt 91-120'
WHEN [date]> 121 AND [date] <= 150 THEN 'pmt 121-150'
WHEN [date]> 151 AND [date] <= 180 THEN 'pmt 151-180'
ELSE 'pmt 181+'
END as (name your case stmt)
FROM @Chg
)
)

SELECT *
FROM #CTE as x

Try this!
I think you need to name your case statements and you missed from in front of the derived tables. Hope this helps!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources