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 2005 Forums
 Transact-SQL (2005)
 Row limit exceed error

Author  Topic 

niranjankumark
Posting Yak Master

164 Posts
Posted - 2008-12-01 : 05:24:54
Hi while execute this qry i am getting error row limit is exceed.. so one slution is work file group space shud be increased. other is qry need to be modified.
ACTUAL QRY :
===============

WITH
ORIG_DEPT_LKP AS (
SELECT
CODE.C_CODE ORIG_DEPT_CD,
CODE_DECODE.T_SHORT_DESC SHORT_DESCRIPTION,
CODE_DECODE.T_LONG_DESC LONG_DESCRIPTION
FROM
D3162RCA.CODE CODE,
D3162RCA.CODE_CATEGORY CODE_CATEGORY,
D3162RCA.CODE_DECODE CODE_DECODE
WHERE
CODE.C_CATEGORY = CODE_DECODE.C_CATEGORY AND
CODE.C_CODE = CODE_DECODE.C_CODE AND
CODE.C_CATEGORY = CODE_CATEGORY.C_CATEGORY AND
CODE_CATEGORY.C_CATEGORY = 424 ),

JURDC_TYP_LKP AS (
SELECT
CODE.C_CODE JURDC_TYP_CD,
CODE_DECODE.T_SHORT_DESC SHORT_DESCRIPTION,
CODE_DECODE.T_LONG_DESC LONG_DESCRIPTION
FROM
D3162RCA.CODE CODE,
D3162RCA.CODE_CATEGORY CODE_CATEGORY,
D3162RCA.CODE_DECODE CODE_DECODE
WHERE
CODE.C_CATEGORY = CODE_DECODE.C_CATEGORY AND
CODE.C_CODE = CODE_DECODE.C_CODE AND
CODE.C_CATEGORY = CODE_CATEGORY.C_CATEGORY AND
CODE_CATEGORY.C_CATEGORY = 442 )

SELECT
DISTINCT
CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( PRTCP.PRTCP_SBTYP_CD = 'NAMI' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END
AS "INSURED",

MTR.MTR_FUL_NM AS "MATTER NAME",

CAST( MTR.MTR_NBR AS CHAR ) || MTR.MTR_YR AS "MATTER ID",

CASE
WHEN ( CAST( ORIG_DEPT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( ORIG_DEPT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( ORIG_DEPT_LKP.LONG_DESCRIPTION )
ELSE ( '' )
END
AS "ORIGINATING DEPARTMENT",


CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( CONTACT.CNTC_CLSS_CD = 'B' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END
AS "BUSINESS CONTACT",

CASE
WHEN ( CAST( MTR_ASGN.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( MTR_ASGN.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
AS "IN-HOUSE LITIGATION MANAGER",

CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( PRTCP.PRTCP_SBTYP_CD = 'CFTR' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END
AS "COUNSEL FOR TRV",

CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( PRTCP.PRTCP_SBTYP_CD = 'PHCD' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END
AS "POLICY HOLDER COUNSEL DEFENCE",

CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( PRTCP.PRTCP_SBTYP_CD = 'CDTI' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END
AS "COUNSEL ADVERSED TO INSURED",


CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( PRTCP.PRTCP_SBTYP_CD = 'OPPC' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END
AS "OPPOSING COUNSEL",

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'ISS'
THEN TXT.VAR_TXT
ELSE ( '' )
END
AS "ISSUES",

CASE
WHEN ( CAST( LEG_PROC.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( LEG_PROC.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( JURDC_TYP_LKP.LONG_DESCRIPTION )
ELSE ( '' )
END
AS "JURISDICTION",

CASE
WHEN ( CAST( LEG_PROC.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( LEG_PROC.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( CONTACT.ORG_NM )
ELSE ( '' )
END
AS "VENUE",

LEG_PROC.SRV_DT AS "SERVICE DATE",

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'SN'
THEN TXT.VAR_TXT
ELSE ( '' )
END
AS "SYNOPSIS",

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'CV'
THEN TXT.VAR_TXT
ELSE ( '' )
END
AS "ALLEGED COVERAGE",

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'DMIN'
THEN TXT.VAR_TXT
ELSE ( '' )
END
AS "DESCRIPTION OF MATTERS & ISSUES",

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'STN'
THEN TXT.VAR_TXT
ELSE ( '' )
END
AS "STRATEGY",

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'NN'
THEN TXT.VAR_TXT
ELSE ( '' )
END
AS "NEGOTIATIONS",

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'UP'
THEN TXT.VAR_TXT
ELSE ( '' )
END
AS "UPDATE SINCE LAST REPORT",

SUM(
CASE
WHEN ( RSRV.RSRV_TYP_CD = 'IND' AND
CAST( RSRV.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( RSRV.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( RSRV.GRS_RSRV_AMT )
ELSE ( 0 )
END )
AS "RESERVE - INDMENITY",

SUM(
CASE
WHEN ( RSRV.RSRV_TYP_CD = 'EXP' AND
CAST( RSRV.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( RSRV.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( RSRV.GRS_RSRV_AMT )
ELSE ( 0 )
END )
AS "RESERVE - EXPENSE",


SUM(
CASE
WHEN ( RSRV.RSRV_TYP_CD = 'EXP' AND
INVC.CHK_ISS_DT <= CURRENT DATE AND PAY_STS.PAY_STS_CD = 'ISSD' AND
CAST( PAY_STS.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PAY_STS.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( RSRV.GRS_RSRV_AMT )
ELSE ( 0 )
END )
AS "INDEMNITY PAID",

SUM(
CASE
WHEN ( RSRV.RSRV_TYP_CD = 'EXP' AND
INVC.CHK_ISS_DT <= CURRENT DATE AND PAY_STS.PAY_STS_CD = 'ISSD' AND
CAST( PAY_STS.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PAY_STS.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( RSRV.GRS_RSRV_AMT )
ELSE ( 0 )
END )
AS "EXPENSE PAID"
FROM
(
PRTCP PRTCP
JOIN
CONTACT CONTACT
ON ( PRTCP.CNTC_UID = CONTACT.CNTC_UID )
JOIN
MTR MTR
ON ( MTR.MTR_UID = PRTCP.PRTCP_OWNR_UID )
JOIN
MTR_STS MTR_STS
ON ( MTR.MTR_UID = MTR_STS.MTR_UID )
JOIN
MTR_INVC_ALLOC MTR_INVC_ALLOC
ON ( MTR.MTR_UID = MTR_INVC_ALLOC.MTR_UID )
JOIN
.INVC INVC
ON ( MTR_INVC_ALLOC.INVC_UID = INVC.INVC_UID )
JOIN
PAY_STS PAY_STS
ON ( INVC.INVC_UID = PAY_STS.INVC_UID )
JOIN
INVC_PAY_ALLOC INVC_PAY_ALLOC
ON ( MTR_INVC_ALLOC.MTR_UID = INVC_PAY_ALLOC.MTR_UID AND MTR_INVC_ALLOC.INVC_UID = INVC_PAY_ALLOC.INVC_UID )
JOIN
MTR_ASGN MTR_ASGN
ON ( MTR.MTR_UID = MTR_ASGN.MTR_UID AND MTR_ASGN.PRTCP_UID = PRTCP.PRTCP_UID )
JOIN
ORIG_DEPT ORIG_DEPT
ON ( MTR.MTR_UID = ORIG_DEPT.MTR_UID )
JOIN
ORIG_DEPT_LKP
ON ( ORIG_DEPT.ORIG_DEPT_CD = ORIG_DEPT_LKP.ORIG_DEPT_CD )
JOIN
RSRV RSRV
ON ( MTR.MTR_UID = RSRV.MTR_UID )
JOIN
NOTE_OWNR NOTE_OWNR
ON ( MTR.MTR_UID = NOTE_OWNR.NOTE_OWNR_UID )
JOIN
NOTE NOTE
ON ( NOTE.NOTE_UID = NOTE_OWNR.NOTE_UID )
JOIN
TXT TXT
ON ( NOTE.NOTE_UID = TXT.NOTE_UID )
)
LEFT OUTER JOIN
(
LEG_PROCEEDING LEG_PROC
JOIN
JURDC_TYP_LKP
ON ( LEG_PROC.JURDC_TYP_CD = JURDC_TYP_LKP.JURDC_TYP_CD )
)
ON
( MTR.MTR_UID = LEG_PROC.MTR_UID AND LEG_PROC.CRT_CNTC_UID = CONTACT.CNTC_UID )


GROUP BY
CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( PRTCP.PRTCP_SBTYP_CD = 'NAMI' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END,

MTR.MTR_FUL_NM,

CAST( MTR.MTR_NBR AS CHAR ) || MTR.MTR_YR,

CASE
WHEN ( CAST( ORIG_DEPT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( ORIG_DEPT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( ORIG_DEPT_LKP.LONG_DESCRIPTION )
ELSE ( '' )
END,


CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( CONTACT.CNTC_CLSS_CD = 'B' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END,

CASE
WHEN ( CAST( MTR_ASGN.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( MTR_ASGN.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END,

CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( PRTCP.PRTCP_SBTYP_CD = 'CFTR' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END,

CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( PRTCP.PRTCP_SBTYP_CD = 'PHCD' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END,

CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( PRTCP.PRTCP_SBTYP_CD = 'CDTI' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END,


CASE
WHEN ( CAST( PRTCP.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( PRTCP.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN (
CASE
WHEN ( PRTCP.PRTCP_SBTYP_CD = 'OPPC' )
THEN ( CONTACT.LST_NM || ', ' || CONTACT.FST_NM )
ELSE ( '' )
END
)
ELSE ( '' )
END,

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'ISS'
THEN TXT.VAR_TXT
ELSE ( '' )
END,

CASE
WHEN ( CAST( LEG_PROC.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( LEG_PROC.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( JURDC_TYP_LKP.LONG_DESCRIPTION )
ELSE ( '' )
END,

CASE
WHEN ( CAST( LEG_PROC.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( LEG_PROC.EXPIR_TS AS DATE ) >= CURRENT DATE AND
CAST( CONTACT.EFF_TS AS DATE ) <= CURRENT DATE AND CAST( CONTACT.EXPIR_TS AS DATE ) >= CURRENT DATE )
THEN ( CONTACT.ORG_NM )
ELSE ( '' )
END,

LEG_PROC.SRV_DT,

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'SN'
THEN TXT.VAR_TXT
ELSE ( '' )
END,

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'CV'
THEN TXT.VAR_TXT
ELSE ( '' )
END,

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'DMIN'
THEN TXT.VAR_TXT
ELSE ( '' )
END,

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'STN'
THEN TXT.VAR_TXT
ELSE ( '' )
END,

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'NN'
THEN TXT.VAR_TXT
ELSE ( '' )
END,

CASE
WHEN NOTE.NOTE_SUBTPC_CD = 'UP'
THEN TXT.VAR_TXT
ELSE ( '' )
END

In this qry problem area is txt.var_txt column , this has length of varchar(3000) ... if u see below select criteria also available in group by ... distinct is must .. because this qry used in reports .. so sure repeated value will occur ( but main reason of this error due to distinct only ) ... so how can be qry splitted ( aggregate and other part finally 2 qry need to be joined with distinct ) ...

issue area need to bes olved is...
case
when note.note_subtpc_cd = 'sn'
then txt.var_txt
else ( '' )
end
as "synopsis",

case
when note.note_subtpc_cd = 'cv'
then txt.var_txt
else ( '' )
end
as "alleged coverage",

case
when note.note_subtpc_cd = 'dmin'
then txt.var_txt
else ( '' )
end
as "description of matters & issues",

case
when note.note_subtpc_cd = 'stn'
then txt.var_txt
else ( '' )
end
as "strategy",

case
when note.note_subtpc_cd = 'nn'
then txt.var_txt
else ( '' )
end
as "negotiations",

case
when note.note_subtpc_cd = 'up'
then txt.var_txt
else ( '' )
end
as "update since last report",
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site