Monday 25 January 2010

CTE maximum recursion error- SQL

I was running a CTE an got the following Error message:

'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'

The statement would undertake over 100 recursions hence the message. To rectify, I added the following query hint:


OPTION (MAXRECURSION 500);

Overriding the default of 100 for this particular statement.

Saturday 16 January 2010

Syntax for checking objects existence - SQL

SET NOCOUNT ON;
USE
database;
GO
IF OBJECT_ID('dbo.table) IS NOT NULL
DROP TABLE dbo.table
GO
CREATE TABLE .....

Friday 15 January 2010

Example Select Case to get time period bandings - CR

Example Select Case to get time period bandings:

select ({fldName}-currentdate)
case upfrom_ 365 : "12 months"
case 271 to_ 365 : "9 months"
case 181 to_ 271 : "6 months"
case 91 to_ 181 : "3 months"
case 31 to_ 91 : "1 month"
case 0 to_ 31 : "IMMINENT"
case -90 to_ 0 : "OVERDUE - by up to 3 Months"
case -180 to_ -90 : "OVERDUE - by up to 6 Months"
case -365 to_ -180 : "OVERDUE - by up to 12 Months"
case -545 to_ -365 : "OVERDUE - by up to 18 Months"
case -730 to_ -545 : "OVERDUE - by up to 24 Months"
default: "CHECK STATUS"