Showing posts with label raised. Show all posts
Showing posts with label raised. Show all posts

Thursday, March 22, 2012

Adding User Define fields together

This is driving me crazy! The SQL Statement refenced is shown at the end of this email.

When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?

SQL View......

SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,

Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,

Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR

(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total

FROM dbo.QRY_FacNew_Term

WHERE (Exclude <> 'Y')

ORDER BY Category, Client_NameUse brackets if name includes space:

[drawn_GT5MIL_LE365Days ] = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,