SQL Server VIEWS, Large Text Fields in a JOIN, and Crystal Reports 9

[hsandwick]

SQL Server 2000
Crystal Reports 9

(1) I have a main report with 67 or so subreports. Several of these subreports have complex SQL queries. So, as I revamp them, I am converting these complex queries into SQL VIEWS. The first test ran successfully, and is importing in a split second which is excellent. But, there is a large text field in the query that is not showing results in the Crystal Report (although I do see the results when I run the VIEW in SQL Enterprise Manager).

How do I handle large fields in a VIEW, please? See below, I'm referring to the "Notes.NoteText" field.

(2) Is there a limit to the number of VIEWS that one can create in a single report? I.e., if necessary, is it possible to convert all 67 subreport queries to VIEWS without contention? Also, is it possible to have some subreports with commands, and some subreports with VIEWS?

Thank you in advance,

Helen

SELECT
NameCard.NameFull AS Plaintiff,
NameCard.NameCardID AS PlaintiffID,
FileHdr.NameFile,
FileHdr.OpenFileNo,
CustomDataM.EntityID,
CustomDataM.CustomDataMID,
Notes.NoteText,
(SELECT NC1.NameFull
FROM NameCard NC1, CustomDataD CDD1
WHERE NameCardId = CDD1.ValuePickLong AND
CDD1.CustomTplDID = '4B641469CVS030' AND
CDD1.CustomDataMID = CustomDataM.CustomDataMID) AS RecordsProvider,
(SELECT CDD2.ValueDDLB
FROM CustomDataD CDD2
WHERE CDD2.CustomTplDID = '4BO3C3FA74S030' AND CDD2.CustomDataMID = CustomDataM.CustomDataMID AND
CDD2.ValueDDLB <> 'films/photographs') AS Type,
(SELECT CDD3.ValueEntry
FROM CustomDataD CDD3
WHERE CDD3.CustomTplDID = '5CG56A9BEFS030' AND CDD3.CustomDataMID = CustomDataM.CustomDataMID) AS Comment,
(SELECT CDD4.ValueDDLB
FROM CustomDataD CDD4
WHERE CDD4.CustomTplDID = '4BO3CF0CDKS030' AND CDD4.CustomDataMID = CustomDataM.CustomDataMID AND
CDD4.ValueDDLB = 'Not Available') AS Status,
(SELECT NC2.NameFull
FROM NameCard NC2, CustomDataD CDD5
WHERE NameCardId = CDD5.ValuePickLong AND
CDD5.CustomTplDID = '5CG56BA26LS030' AND
CDD5.CustomDataMID = CustomDataM.CustomDataMID) AS SourcePersonNamedRecord,
(SELECT NC3.NameCardID
FROM NameCard NC3, CustomDataD CDD6
WHERE NameCardId = CDD6.ValuePickLong AND
CDD6.CustomTplDID = '4B641469CVS030' AND
CDD6.CustomDataMID = CustomDataM.CustomDataMID) AS RecProvID,
(SELECT CDD7.ValueEntry
FROM CustomDataD CDD7
WHERE CDD7.CustomTplDID = '5CG55FDB86S030' AND CDD7.CustomDataMID = CustomDataM.CustomDataMID) AS BegBatesNumber,
(SELECT CDD8.ValueWebURL
FROM CustomDataD CDD8
WHERE CDD8.CustomTplDID = '5CG55FDB86S030' AND CDD8.CustomDataMID = CustomDataM.CustomDataMID) AS EndBatesNumber,
(SELECT CDD9.ValueEntry
FROM CustomDataD CDD9
WHERE CDD9.CustomTplDID = '5CG560852ZS030' AND CDD9.CustomDataMID = CustomDataM.CustomDataMID) AS Reference,
(SELECT CDD10.ValueEntry
FROM CustomDataD CDD10
WHERE CDD10.CustomTplDID = '4ER4EFE0C5S030' AND CDD10.CustomDataMID = CustomDataM.CustomDataMID) AS BegImageKey,
(SELECT CDD11.ValueWebURL
FROM CustomDataD CDD11
WHERE CDD11.CustomTplDID = '4ER4EFE0C5S030' AND CDD11.CustomDataMID = CustomDataM.CustomDataMID) AS EndImageKey,
(SELECT CDD12.ValueDate
FROM CustomDataD CDD12
WHERE CDD12.CustomTplDID = '4B6400715XS030' AND CDD12.CustomDataMID = CustomDataM.CustomDataMID) AS DateThru,
(SELECT CDD13.ValueDate
FROM CustomDataD CDD13
WHERE CDD13.CustomTplDID = '4IK41D22BAS030' AND CDD13.CustomDataMID = CustomDataM.CustomDataMID) AS DateSent,
(SELECT CDD14.ValueDate
FROM CustomDataD CDD14
WHERE CDD14.CustomTplDID = '4IK41D969JS030' AND CDD14.CustomDataMID = CustomDataM.CustomDataMID) AS DateSHReq,
(SELECT CDD15.ValueCheckBox
FROM CustomDataD CDD15
WHERE CDD15.CustomTplDID = '5DQ538D28CS030' AND CDD15.CustomDataMID = CustomDataM.CustomDataMID) AS KeySame,
(SELECT CDD16.ValueDate
FROM CustomDataD CDD16
WHERE CDD16.CustomTplDID = '4IK41E6CFGS030' AND CDD16.CustomDataMID = CustomDataM.CustomDataMID) AS DateSHRcvd,
(SELECT CDD17.ValueDate
FROM CustomDataD CDD17
WHERE CDD17.CustomTplDID = '4B63FFB2CCS030' AND CDD17.CustomDataMID = CustomDataM.CustomDataMID) AS RecordFrom,
(SELECT NC4.NameFull
FROM Users U1, NameCard NC4, CustomDataD CDD5
WHERE U1.UserID = CDD5.ValuePickLong AND
NC4.NameCardID = U1.NameCardID AND
CDD5.CustomTplDID = '4IK41DDFA6S030' AND CDD5.CustomDataMID = CustomDataM.CustomDataMID) AS RequestedBy,
(SELECT CDD18.ValueEntry
FROM CustomDataD CDD18
WHERE CDD18.CustomTplDID = '4B63FE21B9S030' AND CDD18.CustomDataMID = CustomDataM.CustomDataMID) AS RecordDescription,
(SELECT CDD19.ValueDate
FROM CustomDataD CDD19
WHERE CDD19.CustomTplDID = '4B6400DEBKS030' AND CDD19.CustomDataMID = CustomDataM.CustomDataMID) AS DateLocCslReq,
(SELECT CDD20.ValueDDLB
FROM CustomDataD CDD20
WHERE CDD20.CustomTplDID = '4BO2E33A6RS030' AND CDD20.CustomDataMID = CustomDataM.CustomDataMID) AS ReqMethod,
(SELECT NC5.NameFull
FROM NameCard NC5, CustomDataD CDD21
WHERE NameCardId = CDD21.ValuePickLong AND CDD21.CustomTplDID = '5CG55D7C4GS030' AND
CDD21.CustomDataMID = CustomDataM.CustomDataMID) AS RecordReqFrom,
(SELECT NC6.NameFull
FROM NameCard NC6, CustomDataD CDD22
WHERE NameCardId = CDD22.ValuePickLong AND CDD22.CustomTplDID = '4B64154AF6S030' AND
CDD22.CustomDataMID = CustomDataM.CustomDataMID) AS ReqbyLocalCounsel
FROM dbo.FileHdr FileHdr
INNER JOIN dbo.CustomDataM CustomDataM ON CustomDataM.EntityID = FileHdr.FileID AND CustomDataM.CustomTplMID = '4B63F8BDFES030'
INNER JOIN dbo.CustomDataD CustomDataD ON CustomDataD.CustomDataMID = CustomDataM.CustomDataMID
LEFT OUTER JOIN dbo.Notes Notes ON Notes.NoteID = CustomDataD.NoteIDCustomDataD AND CustomDataD.CustomTplDID = '4BO3BB0B0QS030'
INNER JOIN dbo.NameCard NameCard ON CustomDataD.ValuePickLong = NameCard.NameCardID
INNER JOIN dbo.FileRel FileRel ON NameCard.NameCardID = FileRel.NameCardID
INNER JOIN dbo.XTABLE XTABLE_Role ON XTABLE_Role.XTableId = FileRel.RoleID AND XTABLE_Role.Description = 'Plaintiff'

 

[synapsevampire]

Yes, you can mix data sources (Commands vs. Views).

Also Crystal 9 can recognize large data types (unlike CR 8.5). It may be that the ODBC driver is seeing it as a blob type...

Try just adding in the view to a new report, does it not show what's in the field?

You might try a CAST or CONVERT against the field to convert it to a varchar2 in your SQL to see if that works, it should.

-k

 

[hsandwick]

Thank you, synapsvampire, for all of your helpful suggestions and feedback.

I read up about BLOBS and varchar2 (remembering those from Oracle days) and then tested out CAST/CONVERT char and varchar since this relates to SQL Server, as you suggested. I still was not retrieving results in the subreport, but yes, they did show in the new test main report so I knew we were heading in the right direction. I played with the idea of nesting the statement for Notes, like most of the other fields (this had caused errors in my Command statement), and finally I got results! I've maxed it out to 8000 as our audience likes to write novels.

This is a big step forward as we've been having real performance issues and now we have a resolution to work with.

Helen

(Select CONVERT(CHAR(8000), NTS1.NoteText)
From
Notes NTS1, CustomDataD CDD23
Where NoteID = CDD23.NoteIDCustomDataD
and CDD23.CustomTplDID = '4BO3BB0B0QS030' --Notes
and CDD23.CustomDataMID = CustomDataM.CustomDataMID) as Notes
-- end block for Notes