Skip to Content
0
Former Member
May 17, 2010 at 05:17 PM

Maximal Size of Query and number of Crystal Variables CR 11.5 & 12

140 Views

I am running the following Command query against SQL-Server 2005

SELECT DISTINCT
  "PatientProfile"."Last",
  "PatientProfile"."First",
  "PatientProfile"."PatientId",
...
  "PROBLEM"."CODE",
  "PROBLEM"."DESCRIPTION",
  "PROBLEM"."ONSETDATE",
  "PROBLEM"."CHANGE" ,
  "PROBLEM"."STOPDATE",
  "PROBLEM"."QUALIFIER",

  "LASTLABS1"."HDID" "HDID1",
  "RPTOBS1"."obsdate" "obsdate1",
  "RPTOBS1"."change" "change1",
  "RPTOBS1"."obsvalue" "obsvalue1",

...
 
  "LASTLABS9"."HDID" "HDID9",
  "RPTOBS9"."obsdate" "obsdate9",
  "RPTOBS9"."change" "change9",
  "RPTOBS9"."obsvalue" "obsvalue9"

 FROM
  (("CentricityPS"."dbo"."PatientProfile" "PatientProfile" INNER JOIN
    "CentricityPS"."dbo"."PROBLEM" "PROBLEM" ON
        "PatientProfile"."PId"="PROBLEM"."PID") LEFT OUTER JOIN

    "CentricityPS"."dbo"."LASTLABS" "LASTLABS1" ON
     ("PatientProfile"."PId"="LASTLABS1"."PID" AND
      "LASTLABS1"."HDID"=28) LEFT OUTER JOIN
    "CentricityPS"."dbo"."RPTOBS" "RPTOBS1" ON
     ("LASTLABS1"."HDID"="RPTOBS1"."hdid" AND
      "LASTLABS1"."MAXLABDATE"="RPTOBS1"."obsdate" AND
      "LASTLABS1"."PID"="RPTOBS1"."pid" AND
      "RPTOBS1"."change"=2) LEFT OUTER JOIN

... 

    "CentricityPS"."dbo"."LASTLABS" "LASTLABS9" ON
     ("PatientProfile"."PId"="LASTLABS9"."PID" AND
      "LASTLABS9"."HDID"=18892) LEFT OUTER JOIN
    "CentricityPS"."dbo"."RPTOBS" "RPTOBS9" ON
     ("LASTLABS9"."HDID"="RPTOBS9"."hdid" AND
      "LASTLABS9"."MAXLABDATE"="RPTOBS9"."obsdate" AND
      "LASTLABS9"."PID"="RPTOBS9"."pid" AND
      "RPTOBS9"."change"=2))

 WHERE
   "PatientProfile"."ispatient"='Y' AND
   "PatientProfile"."pstatus"='A' AND
   "PatientProfile"."Birthdate"<{ts '1992-03-04 00:00:01'} AND
    ("PROBLEM"."CODE" LIKE 'ICD-249%' OR
      "PROBLEM"."CODE" LIKE 'ICD-250%' OR
      "PROBLEM"."CODE"='ICD-V77.1') AND
   "PROBLEM"."CHANGE"=2 AND
   "PROBLEM"."QUALIFIER"<>'FH OF' AND
     ("PROBLEM"."STOPDATE" IS  NULL OR
      "PROBLEM"."STOPDATE">={ts '2010-03-05 00:00:00'})

There are a total of 9 Left Outer Joins to give a total of 9 Observations and Obsdates in the result table besides the demograhics. Running the Query in the SQL-Server Query window gives the expected result.

However, running a Crystal Report in either 11.5 or 12 with a Summary Cross-Tab Tables and Charts works for the first 6 Obs, then gives randm results in the last 3.