UNION Query: Error 8618

[NWChowd]

I get the following error message when I try to use a view that contains 2 separate SELECT statements "UNION"ed together.

Server: Msg 8618, Level 16, State 2, Line 1
Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY clause exceeds 8000 bytes.

The funny thing is: I tested this view a few weeks ago, and encountered no problems. Now, I get this error message every time.

At the moment, I am running SQL server 7.0, but I may be upgrading to SQL server 2000 very soon.

I can supply the query behind the view if neccssary. It's about 325 lines long.

Does any one have any suggestions?

Thanks,
NWCHOWD

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[mrdenny]

Check the field definations for the fields in the group by. Can you provide the schema for the tables, and the group by line of the query.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[NWChowd]

Here's the GROUP BY clause:

-----
GROUP BY claimlin.claimno,claimlin.formcd,claimlin.poscod,claimlin.grpnum,claimlin.membno,claimlin.altclm,claimlin.diagn1, claimlin.deneop,claimlin.svccod,claimlin.statcd,
claimlin.poscod,claimlin.svcdat,claimlin.enddat,claimlin.pidate,
hcfaext.diagn1,hcfaext.diagn2,hcfaext.diagn3,hcfaext.diagn4,
vendor.fmtcod,vendor.lstnam, vendor.fstnam,vendor.midnam,vendor.titlcd,vendor.adrln1,vendor.citycd,vendor.stacod,vendor.zipcod,vendor.fedtax,
provider.facind,provider.fstnam,provider.lstnam,provider.midnam,provider.titlcd,provider.fednum,provider.specd1,provider.altprv,provider.prvupn, member.lstnam,member.fstnam,member.midnam,member.titlcd,
demograp.sexcod,demograp.adrln1, demograp.citycd,demograp.stacod,demograp.zipcod,demograp.bthdat,demograp.altnum,
HARCCrosswalk.HARC
-----





======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[mrdenny]

What's the schema for these columns look like? If you add up all the field lengths what does it comes out to?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[vongrunt]

49 columns in GROUP BY... *** gulp ***

Is that really necessary?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NWChowd]

Here's some info on field lengths:

field datatype length
claimlin.claimno char 20
claimlin.formcd char 1
claimlin.grpnum char 6
claimlin.membno char 15
claimlin.altclm char 30
claimlin.diagn1 char 6
claimlin.deneop char 3
claimlin.svccod char 6
claimlin.statcd char 1
claimlin.poscod char 2
claimlin.svcdat datetime 8
claimlin.enddat datetime 8
claimlin.pidate datetime 8
hcfaext.diagn1 char 6
hcfaext.diagn2 char 6
hcfaext.diagn3 char 6
hcfaext.diagn4 char 6
vendor.fmtcod char 1
vendor.lstnam char 20
vendor.fstnam char 15
vendor.midnam char 1
vendor.titlcd char 4
vendor.adrln1 varchar 25
vendor.citycd char 16
vendor.stacod char 2
vendor.zipcod char 10
vendor.fedtax char 16
provider.facind char 1
provider.fstnam char 15
provider.lstnam char 20
provider.midnam char 1
provider.titlcd char 4
provider.fednum char 15
provider.specd1 char 5
provider.altprv char 15
provider.prvupn char 6
member.lstnam char 20
member.fstnam char 15
member.midnam char 1
member.titlcd char 4
demograp.sexcod char 1
demograp.adrln1 varchar 25
demograp.citycd char 16
demograp.stacod char 2
demograp.zipcod char 10
demograp.bthdat datetime 8
demograp.altnum char 15
HARCCrosswalk.HARC varchar 2



Also, as I mentioned above, there are two SELECT statement "union"ed together. Unfortunately I am working under the constraints of a third-party application that is requiring that the two SELECT statements be placed in one view.

I thought I found the resolution on the MS website:

http://support.microsoft.com/kb/225141/EN-US/

but updatingthe Service Pack did not help at all.

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[mrdenny]

It shouldn't be complaining at all. Your total field length of your columns is only 447 bytes.

Was anything on the server changed between a few weeks ago and now?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[NWChowd]

Not that I am aware of.

Is there anything in particular I should look for?

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[mrdenny]

Any SQL Patches, MDAC changes, OS patches. It really could be anything.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[NWChowd]

so, I just noticed that the view seems to work if I change the following in the query:

REPLACE(REPLACE(claimlin.claimno,'-', ''),'.','') AS 'ClaimNumber', ----

to

LEFT(claimlin.claimno,2) + SUBSTRING(claimlin.claimno, 9,2) + SUBSTRING(claimlin.claimno, 12,2) + SUBSTRING(claimlin.claimno, 6,2) + SUBSTRING(claimlin.claimno, 15,3) + RIGHT(claimlin.claimno, 2)) AS 'ClaimNumber', ----


They are obviously not equivalent. I had originally used the 2nd option, but the functional dept using this view needed the 1st option.

Any idea why/how this might affect anything?

+

Here's the entire sql statement behind the view:

CREATE VIEW dbo.vwMCSHeader
AS
-- MCS Header File Query

--***** HCFA CLAIMS (837 Professional) ***** --
SELECT DISTINCT
COALESCE(
CASE RTRIM(claimlin.altclm)
WHEN '' THEN NULL
ELSE claimlin.altclm
END,


LEFT(claimlin.claimno,2) + SUBSTRING(claimlin.claimno, 9,2) + SUBSTRING(claimlin.claimno, 12,2) +
SUBSTRING(claimlin.claimno, 6,2) + SUBSTRING(claimlin.claimno, 15,3) + RIGHT(claimlin.claimno, 2)
) AS 'ClaimNumber', ----

-- REPLACE(REPLACE(claimlin.claimno,'-', ''),'.','')
-- ) AS 'ClaimNumber', ----

CASE claimlin.formcd
WHEN 'H' THEN 'P'
WHEN 'U' THEN
CASE claimlin.poscod
WHEN 21 THEN 'I'
ELSE 'O'
END
END AS 'ClaimType', ----
SPACE(1) AS 'BillingProviderSecondaryIDNumber', ----
CASE vendor.fmtcod
WHEN 9 THEN vendor.lstnam + vendor.fstnam + vendor.midnam + vendor.titlcd
ELSE RTRIM(vendor.fstnam) + ' ' + RTRIM(vendor.midnam) + ' ' + RTRIM(vendor.lstnam) + ' ' + RTRIM(vendor.titlcd)
END AS 'BillingProviderName', ----
vendor.adrln1 AS 'BillingProviderAddress', ----
vendor.citycd AS 'BillingProviderCityCode', ----
vendor.stacod AS 'BillingProviderStateCode', ----
vendor.zipcod AS 'BillingProviderZipCode', ----
'USA' AS 'BillingProviderCountryCode', ----
'34' AS 'BillingProviderIDCodeQualifier', ----
vendor.fedtax AS 'BillingProviderIDCode', ----
SPACE(1) AS 'BillingProviderTaxonomyCode', --XXXX
provider.prvupn AS 'RenderingProviderSecondaryID', ----
'82' AS 'RenderingIDType', ----
CASE provider.facind
WHEN 'Y' THEN '2'
WHEN 'N' THEN '1'
END AS 'RenderingIDTypeQualifier', ----
provider.fstnam AS 'RenderingProviderFirstName', ----
provider.lstnam AS 'RenderingProviderLastName', ----
provider.midnam AS 'RenderingProviderMiddleName', ----
provider.titlcd AS 'RenderingProviderSuffix', ----
24 AS 'RenderingProviderIDCodeQualifier', ----
provider.altprv AS 'RenderingProviderIDCode', ---- OMAP ID
provider.specd1 AS 'RenderingProviderTaxonomyCode', ----
SPACE(1) AS 'InsuranceTypeCode', --XXXX
'MC' AS 'ClaimFilingIndicatorCode', ----
COALESCE(
CASE RTRIM(demograp.altnum)
WHEN '' THEN NULL
WHEN NULL THEN NULL
ELSE demograp.altnum
END,
claimlin.membno
) AS 'MemberIdentificationNumber', ----
member.lstnam AS 'PatientLastName', ----
member.fstnam AS 'PatientFirstName', ----
member.midnam AS 'PatientMiddleName', ----
member.titlcd AS 'PatientSuffix', ----
demograp.sexcod AS 'PatientSex', ----
demograp.adrln1 AS 'PatientAddress', ----
demograp.citycd AS 'PatientCity', ----
demograp.stacod AS 'PatientState', ----
demograp.zipcod AS 'PatientZipCode', ----
'USA' AS 'PatientCountryCode', ----
SUM(claamt) AS 'ClaimTotalAmount', ---- (SUM ALL CLAIM LINES)
0 AS 'PatientPaidAmount', ----
SPACE(1) AS 'AdjustmentGroupCode', --XXXX
CASE RTRIM(claimlin.deneop)
WHEN '' THEN NULL
WHEN NULL THEN NULL
ELSE HARCCrosswalk.HARC
END AS 'AdjustmentReasonCode', ----
SPACE(1) AS 'ContractTypeCode', --XXXX
claimlin.poscod AS 'FacilityCode', ----
SPACE(1) AS 'ClaimFrequencyType', --XXXX (REQUIRED PER OMAP) !!!
SPACE(1) AS 'ProviderSignature', --XXXX
SPACE(1) AS 'ProviderAcceptsAssignment', --XXXX
SPACE(1) AS 'BenefitsAssignmentCertification', --XXXX
SPACE(1) AS 'ReleaseOfInformationCode', --XXXX
SPACE(1) AS 'PatientSignatureSourceCode', --XXXX
SPACE(1) AS 'SpecialProgramCode', --XXXX
SPACE(1) AS 'EOBIndicator', ---- (NOT USED)
SPACE(1) AS 'HospitalAdmitType', ---- (NOT USED)
SPACE(1) AS 'HospitalAdmitDate', ---- (NOT USED)
SPACE(1) AS 'HospitalAdmitSource', ---- (NOT USED)
RIGHT('0'+ CAST(DatePart(MM,claimlin.svcdat) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimlin.svcdat) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimlin.svcdat) AS VARCHAR(4)),4)
AS 'DateOfServiceBeginDate', ----
SPACE(1) AS 'DateOfServiceBeginTime', ---- (NOT USED)
RIGHT('0'+ CAST(DatePart(MM,claimlin.enddat) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimlin.enddat) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimlin.enddat) AS VARCHAR(4)),4)
AS 'DateOfServiceEndDate', ----
SPACE(1) AS 'DateOfServiceEndTime', ---- (NOT USED)
SPACE(1) AS 'PatientStatus', ---- (NOT USED)
RIGHT('0'+ CAST(DatePart(MM,demograp.bthdat) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,demograp.bthdat) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,demograp.bthdat) AS VARCHAR(4)),4)
AS 'PatientDateOfBirth', ----
hcfaext.diagn1 AS 'Diagnosis1', ----
hcfaext.diagn2 AS 'Diagnosis2', ----
hcfaext.diagn3 AS 'Diagnosis3', ----
hcfaext.diagn4 AS 'Diagnosis4', ----
SPACE(1) AS 'Diagnosis5', ---- (NOT USED)
SPACE(1) AS 'Diagnosis6', ---- (NOT USED)
SPACE(1) AS 'Diagnosis7', ---- (NOT USED)
SPACE(1) AS 'Diagnosis8', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode1', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode1DOS' , ---- (NOT USED)
SPACE(1) AS 'ProcedureCode2', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode2DOS', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode3', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode3DOS', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode4', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode4DOS', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode5', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode5DOS', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode6', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode6DOS', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode7', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode7DOS', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode8', ---- (NOT USED)
SPACE(1) AS 'ProcedureCode8DOS', ---- (NOT USED)
claimlin.pidate AS 'PaidDate'
FROM claimlin
LEFT OUTER JOIN hcfaext ON claimlin.claimno = hcfaext.claimno
LEFT OUTER JOIN vendor ON claimlin.vendor = vendor.vendor
LEFT OUTER JOIN provider ON claimlin.provno = provider.provno
LEFT OUTER JOIN member ON claimlin.member = member.member
LEFT OUTER JOIN demograp ON claimlin.membno = demograp.membno
LEFT OUTER JOIN HARCCrosswalk ON claimlin.deneop = HARCCrosswalk.deneop
GROUP BY claimlin.claimno,claimlin.formcd,claimlin.grpnum,claimlin.membno,claimlin.altclm,claimlin.diagn1, claimlin.deneop,claimlin.svccod,claimlin.statcd,
claimlin.poscod,claimlin.svcdat,claimlin.enddat,claimlin.pidate,
hcfaext.diagn1,hcfaext.diagn2,hcfaext.diagn3,hcfaext.diagn4,
vendor.fmtcod,vendor.lstnam, vendor.fstnam,vendor.midnam,vendor.titlcd,vendor.adrln1,vendor.citycd,vendor.stacod,vendor.zipcod,vendor.fedtax,
provider.facind,provider.fstnam,provider.lstnam,provider.midnam,provider.titlcd,provider.fednum,provider.specd1,provider.altprv,provider.prvupn,
member.lstnam,member.fstnam,member.midnam,member.titlcd,
demograp.sexcod,demograp.adrln1, demograp.citycd,demograp.stacod,demograp.zipcod,demograp.bthdat,demograp.altnum,
HARCCrosswalk.HARC
HAVING
claimlin.grpnum LIKE 'H%' AND -- H% = Medicaid
claimlin.formcd='H' AND -- H = HCFA (Professional claims)
claimlin.membno is NOT NULL AND
claimlin.statcd NOT IN ('E','O','A') AND -- E = Estimated ; O = Open ; A = Adjudicated but not paid
claimlin.svccod NOT IN ('COINON','COINS','DEDOON','DEDUCT') AND
COALESCE(claimlin.deneop,'') NOT IN ('003','DUP','61','ID','035','M9') AND
claimlin.diagn1 <> '999.99' AND
claimlin.svcdat >= '01/01/2004'

UNION

-- MCS Header File Query

--***** UB92 CLAIMS (Institutional) ***** --
SELECT DISTINCT
COALESCE(
CASE RTRIM(claimlin.altclm)
WHEN '' THEN NULL
ELSE claimlin.altclm
END,

LEFT(claimlin.claimno,2) + SUBSTRING(claimlin.claimno, 9,2) + SUBSTRING(claimlin.claimno, 12,2) +
SUBSTRING(claimlin.claimno, 6,2) + SUBSTRING(claimlin.claimno, 15,3) + RIGHT(claimlin.claimno, 2)
) AS 'ClaimNumber', ----

-- REPLACE(REPLACE(claimlin.claimno,'-', ''),'.','')
-- ) AS 'ClaimNumber', ----


CASE claimlin.formcd
WHEN 'H' THEN 'P'
WHEN 'U' THEN
CASE claimlin.poscod
WHEN 21 THEN 'I'
ELSE 'O'
END
END AS 'ClaimType', ----
SPACE(1) AS 'BillingProviderSecondaryIDNumber', --XXXX
CASE vendor.fmtcod
WHEN 9 THEN vendor.lstnam + vendor.fstnam + vendor.midnam + vendor.titlcd
ELSE RTRIM(vendor.fstnam) + ' ' + RTRIM(vendor.midnam) + ' ' + RTRIM(vendor.lstnam) + ' ' + RTRIM(vendor.titlcd)
END AS 'BillingProviderName', ----
vendor.adrln1 AS 'BillingProviderAddress', ----
vendor.citycd AS 'BillingProviderCityCode', ----
vendor.stacod AS 'BillingProviderStateCode', ----
vendor.zipcod AS 'BillingProviderZipCode', ----
'USA' AS 'BillingProviderCountryCode', ----
'34' AS 'BillingProviderIDCodeQualifier', ----
vendor.fedtax AS 'BillingProviderIDCode', ----
SPACE(1) AS 'BillingProviderTaxonomyCode', --XXXX
provider.prvupn AS 'AttendingProviderSecondaryID', ----
'71' AS 'AttendingIDType', ----
CASE provider.facind
WHEN 'Y' THEN '2'
WHEN 'N' THEN '1'
END AS 'AttendingIDTypeQualifier', ----
provider.fstnam AS 'AttendingProviderFirstName', ----
provider.lstnam AS 'AttendingProviderLastName', ----
provider.midnam AS 'AttendingProviderMiddleName', ----
provider.titlcd AS 'AttendingProviderSuffix', ----
24 AS 'AttendingProviderIDCodeQualifier', ----
provider.altprv AS 'AttendingProviderIDCode', ----
provider.specd1 AS 'AttendingProviderTaxonomyCode', ----
SPACE(1) AS 'InsuranceTypeCode', --XXXX
'MC' AS 'ClaimFilingIndicatorCode', ----
COALESCE(
CASE RTRIM(demograp.altnum)
WHEN '' THEN NULL
WHEN NULL THEN NULL
ELSE demograp.altnum
END,
claimlin.membno
) AS 'MemberIdentificationNumber', ----
member.lstnam AS 'PatientLastName', ----
member.fstnam AS 'PatientFirstName', ----
member.midnam AS 'PatientMiddleName', ----
member.titlcd AS 'PatientSuffix', ----
demograp.sexcod AS 'PatientSex', ----
demograp.adrln1 AS 'PatientAddress', ----
demograp.citycd AS 'PatientCity', ----
demograp.stacod AS 'PatientState', ----
demograp.zipcod AS 'PatientZipCode', ----
'USA' AS 'PatientCountryCode', ----
SUM(claamt) AS 'ClaimTotalAmount', ---- (SUM ALL CLAIM LINES)
0 AS 'PatientPaidAmount', ----
SPACE(1) AS 'AdjustmentGroupCode', --XXXX
CASE RTRIM(claimlin.deneop)
WHEN '' THEN NULL
WHEN NULL THEN NULL
ELSE HARCCrosswalk.HARC
END AS 'AdjustmentReasonCode', ----
SPACE(1) AS 'ContractTypeCode', --XXXX
LEFT(claimlin.biltyp,2) AS 'FacilityCode', ----
RIGHT(claimlin.biltyp,1) AS 'ClaimFrequencyType', ----
SPACE(1) AS 'ProviderSignature', --XXXX
SPACE(1) AS 'ProviderAcceptsAssignment', --XXXX
SPACE(1) AS 'BenefitsAssignmentCertification', --XXXX
SPACE(1) AS 'ReleaseOfInformationCode', --XXXX
SPACE(1) AS 'PatientSignatureSourceCode', --XXXX
SPACE(1) AS 'SpecialProgramCode', --XXXX
SPACE(1) AS 'EOBIndicator', --XXXX
claimext.admtyp AS 'HospitalAdmitType', ----
RIGHT('0'+ CAST(DatePart(MM,claimext.admdat) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimext.admdat) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimext.admdat) AS VARCHAR(4)),4)
AS 'HospitalAdmitDate', ----
claimext.admsrc AS 'HospitalAdmitSource', ----
RIGHT('0'+ CAST(DatePart(MM,claimlin.svcdat) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimlin.svcdat) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimlin.svcdat) AS VARCHAR(4)),4)
AS 'DateOfServiceBeginDate', ----
claimext.admtim + '00' AS 'DateOfServiceBeginTime', ----
RIGHT('0'+ CAST(DatePart(MM,claimlin.enddat) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimlin.enddat) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimlin.enddat) AS VARCHAR(4)),4)
AS 'DateOfServiceEndDate', ----
claimext.admtim + '00' AS 'DateOfServiceEndTime', ----
PatientStatusCrosswalk.OMAPPatientStatus
AS 'PatientStatus', ----
RIGHT('0'+ CAST(DatePart(MM,demograp.bthdat) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,demograp.bthdat) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,demograp.bthdat) AS VARCHAR(4)),4)
AS 'PatientDateOfBirth', ----
claimext.diagcd1 AS 'Diagnosis1', ----
claimext.diagcd2 AS 'Diagnosis2', ----
claimext.diagcd3 AS 'Diagnosis3', ----
claimext.diagcd4 AS 'Diagnosis4', ----
claimext.diagcd5 AS 'Diagnosis5', ----
claimext.diagcd6 AS 'Diagnosis6', ----
claimext.diagcd7 AS 'Diagnosis7', ----
claimext.diagcd8 AS 'Diagnosis8', ----
claimext.prccod1 AS 'ProcedureCode1', ----
RIGHT('0'+ CAST(DatePart(MM,claimext.prcdat1) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimext.prcdat1) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimext.prcdat1) AS VARCHAR(4)),4)
AS 'ProcedureCode1DOS' , ----
claimext.prccod2 AS 'ProcedureCode2', ----
RIGHT('0'+ CAST(DatePart(MM,claimext.prcdat2) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimext.prcdat2) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimext.prcdat2) AS VARCHAR(4)),4)
AS 'ProcedureCode2DOS', ----
claimext.prccod3 AS 'ProcedureCode3', ----
RIGHT('0'+ CAST(DatePart(MM,claimext.prcdat3) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimext.prcdat3) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimext.prcdat3) AS VARCHAR(4)),4)
AS 'ProcedureCode3DOS', ----
claimext.prccod4 AS 'ProcedureCode4', ----
RIGHT('0'+ CAST(DatePart(MM,claimext.prcdat4) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimext.prcdat4) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimext.prcdat4) AS VARCHAR(4)),4)
AS 'ProcedureCode4DOS', ----
claimext.prccod5 AS 'ProcedureCode5', ----
RIGHT('0'+ CAST(DatePart(MM,claimext.prcdat5) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimext.prcdat5) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimext.prcdat5) AS VARCHAR(4)),4)
AS 'ProcedureCode5DOS', ----
claimext.prccod6 AS 'ProcedureCode6', ----
RIGHT('0'+ CAST(DatePart(MM,claimext.prcdat6) AS VARCHAR(2)),2) + '/' +
RIGHT('0'+ CAST(DatePart(DD,claimext.prcdat6) AS VARCHAR(2)),2) + '/' +
RIGHT(CAST(DatePart(YY,claimext.prcdat6) AS VARCHAR(4)),4)
AS 'ProcedureCode6DOS', ----
SPACE(1) AS 'ProcedureCode7', ----
SPACE(1) AS 'ProcedureCode7DOS', ----
SPACE(1) AS 'ProcedureCode8', ----
SPACE(1) AS 'ProcedureCode8DOS', ----
claimlin.pidate AS 'PaidDate'
FROM claimlin
LEFT OUTER JOIN claimext ON claimlin.claimno = claimext.claimno
LEFT OUTER JOIN vendor ON claimlin.vendor = vendor.vendor
LEFT OUTER JOIN provider ON claimlin.provno = provider.provno
LEFT OUTER JOIN member ON claimlin.member = member.member
LEFT OUTER JOIN demograp ON claimlin.membno = demograp.membno
LEFT OUTER JOIN PatientStatusCrosswalk ON claimlin.statcd =PatientStatusCrosswalk.OMAPPatientStatus
LEFT OUTER JOIN HARCCrosswalk ON claimlin.deneop = HARCCrosswalk.deneop
GROUP BY claimlin.claimno,claimlin.formcd,claimlin.grpnum,claimlin.membno,claimlin.altclm,claimlin.diagn1, claimlin.deneop,claimlin.svccod,claimlin.statcd,
claimlin.poscod,claimlin.svcdat,claimlin.enddat,claimlin.biltyp,claimlin.pidate,
claimext.diagcd1,claimext.diagcd2,claimext.diagcd3,claimext.diagcd4, claimext.diagcd5,claimext.diagcd6,claimext.diagcd7,claimext.diagcd8,
claimext.admtyp,claimext.admdat,claimext.admsrc,claimext.admtim,claimext.prccod1,claimext.prcdat1,claimext.prccod2,claimext.prcdat2,
claimext.prccod3,claimext.prcdat3,claimext.prccod4,claimext.prcdat4,claimext.prccod5,claimext.prcdat5,claimext.prccod6,claimext.prcdat6,
vendor.fmtcod,vendor.lstnam, vendor.fstnam,vendor.midnam,vendor.titlcd,vendor.adrln1,vendor.citycd,vendor.stacod,vendor.zipcod,vendor.fedtax,
provider.facind,provider.fstnam,provider.lstnam,provider.midnam,provider.titlcd,provider.fednum,provider.specd1,provider.altprv,provider.prvupn,
member.lstnam,member.fstnam,member.midnam,member.titlcd,
demograp.sexcod,demograp.adrln1, demograp.citycd,demograp.stacod,demograp.zipcod,demograp.bthdat,demograp.altnum,
HARCCrosswalk.HARC,
PatientStatusCrosswalk.OMAPPatientStatus
HAVING
claimlin.grpnum LIKE 'H%' AND -- H% = Medicaid
claimlin.formcd='U' AND -- U = UB92 (Institutional claims)
claimlin.membno is NOT NULL AND
claimlin.statcd NOT IN ('E','O','A') AND -- E = Estimated ; O = Open ; A = Adjudicated but not paid
claimlin.svccod NOT IN ('COINON','COINS','DEDOON','DEDUCT') AND
COALESCE(claimlin.deneop,'') NOT IN ('003','DUP','61','ID','035','M9') AND
claimlin.diagn1 <> '999.99' AND
claimlin.svcdat >= '01/01/2004'

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[mrdenny]

no idea.

The only thing I can think of (which probably won't make a bit of difference) would be to put the replace command in the group by as well. It sholdn't make a bit of difference, but who knows.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[NWChowd]

you and me both, mr denny.

I tried putting the replace command in the group by...still the same error.


Back to the drawing board.


======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[zhavic]

At first, sorry for my english

You have 2 SELECT statements and 2 GROUP BY statements,
so the problem should be at second GROUP BY statement.

But, based on Was anything on the server changed between a few weeks ago and now? and Not that I am aware of.

I assume that GROUP BY clauses are OK, and what was changed on the server was your data

There is one thing you need to look on: DISTINCT clause

DISTINCT clause is grouping rows by each column of the result, and there is a problem - you need to check size of all colums of the result

Some columns in yor query has not assigned size, for example this one

 CASE vendor.fmtcod
            WHEN 9 THEN vendor.lstnam + vendor.fstnam + vendor.midnam + vendor.titlcd
            ELSE RTRIM(vendor.fstnam) + ' ' + RTRIM(vendor.midnam) + ' ' + RTRIM(vendor.lstnam) + ' ' + RTRIM(vendor.titlcd)
        END                     AS 'BillingProviderName'

so SQL server dynamically changes size of that columns based on your data

And your view was working because size of all values in your columns of your tables was short, but one day, someone puts into some of that columns some long text, and size of column in the result of your select statement increased, and reached limit 8000 chars.

So all you need is to assign size to all your columns in select list containing strings, for example:

[COLOR=blue]LEFT([/color]
 CASE vendor.fmtcod
            WHEN 9 THEN vendor.lstnam + vendor.fstnam + vendor.midnam + vendor.titlcd
            ELSE RTRIM(vendor.fstnam) + ' ' + RTRIM(vendor.midnam) + ' ' + RTRIM(vendor.lstnam) + ' ' + RTRIM(vendor.titlcd)
        END                     
[COLOR=blue], 100 )[/color] AS 'BillingProviderName'

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[SajidAttar]

I am not sure what the problem is. But . . .

Test your queries individually if they work properly then Union should work.

if there is problem with one of the query, then try to resolve it.



Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell