Can't figure out what I'm doing wrong here. It says field HasNoteDate is invalid. I just want it to return 1 if there's a date, 0 if there's not and I wanted it grouped so I can get the counts and the sum of loan amounts. I've looked at the stackoverflow answers for grouping by a case and none of them are solving it for me. Thanks!
SELECT dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION, COUNT(*) AS TierRegionCount,
SUM(dbo.LOAN.LOAN_AMT) AS LoanAmtSum, dbo.LOAN.DEAL_STATUS, CASE WHEN dbo.WORKFLOW.NOTE_DATE IS NULL
THEN 0 ELSE 1 END AS HasNoteDate
FROM dbo.BRKRMAST INNER JOIN
dbo.BRKRREF ON dbo.BRKRMAST.BRKRMASTID = dbo.BRKRREF.BRKRMASTID INNER JOIN
dbo.LOAN ON dbo.BRKRREF.LoanId = dbo.LOAN.LoanId INNER JOIN
dbo.WORKFLOW ON dbo.LOAN.LoanId = dbo.WORKFLOW.LoanId LEFT OUTER JOIN
dbo.BDONAMES ON dbo.BRKRMAST.BDONAMESID = dbo.BDONAMES.BDONAMESID
WHERE (LEN(dbo.BDONAMES.UserID) > 0) AND (dbo.BRKRMAST.Tier > 0) AND (LEN(dbo.BDONAMES.REGION) > 0)
GROUP BY dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION, dbo.LOAN.LOAN_AMT, dbo.LOAN.DEAL_STATUS, HasNoteDate
ORDER BY dbo.BDONAMES.UserID, dbo.BRKRMAST.Tier, dbo.BDONAMES.REGION
logic of SQL query execution is not straightforward as common programming languages
here are sample execution stages for your query:
FROM CLAUSEGROUP BY - this will cause error, as HasNoteDate is not defined for this momentORDER BYSELECT - only during this stage column HasNoteDate is definedso, you cannot use undefined column HasNoteDate in GROUP BY, you have to replace it with your statement (CASE WHEN dbo.WORKFLOW.NOTE_DATE IS NULL THEN 0 ELSE 1 END), then sql server will be able to GROUP
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句