[yunqa.de] DISQLite3UniDirQuery results don't match same query run in db manager
- From: H M <docshotmail2@xxxxxxxxxxx>
- To: DISQLite3 mailing list <yunqa@xxxxxxxxxxxxx>
- Date: Tue, 24 Sep 2013 13:54:07 +0000
I have some sql that, when run through my db management s/w (SQLiteExpert Pro),
produces 4 columns of numbers (3 integers and 1 float).
However, the same sql, copy/pasted into DISQLite3UniDirQuery, produces two
columns of integers and two columns of just zeros!
I don't use persistant fields as the query component is general purpose and
gets loaded with many different sql statements.
I do have an OnInitFieldDefs event that prevents string truncation as shown
below.
I have DISQLite3UniDirQuery --> TdatasetProvider --> TClientDataSet -->
TdataSource --> TdbGrid (as usual)
I noticed that in SQLiteExpert Pro all the columns are left aligned, but they
contain the correct data, unlike DISQLite3UniDirQuery .
I've had no problems with this component for years and the app I am
working on currently uses it extensively with no problems until this one.
Anyone any suggestions why it converts the numbers to zero in just two of the
columns?
.....................................
the OnInitFieldDef code
.....................................
const MaxStringSize = 300; //used to set string size in the DISQLite3UniDirQuery
//the line is AFieldDef.Size := MaxStringSize;
//4000 is about 1 page of A4 characters size 12
begin
case AFieldDef.DataType of
{$IFNDEF COMPILER_5_UP}
{ TClientDataSet in D4 does not support WideStrings. This works around this
problem by changing the ftWideString type to ftString. It has also
problems assigning ftLargeInt (Int64) to a Variant so scale it down to
ftInteger. }
ftLargeInt: begin
AFieldDef.DataType := ftInteger;
end;
{$ENDIF !COMPILER_5_UP}
ftWideString: begin
{$IFNDEF COMPILER_5_UP}
AFieldDef.DataType := ftString;
{$ENDIF !COMPILER_5_UP}
{ Increase size for string fields to max possible (default is
20 and will give truncated strings). }
AFieldDef.Size := MaxStringSize; //was 20 , changed by hsm
***********
end;
end; //case
.................
The SQL
(its the columns NumMeetingCriteria and PcntMeetingCriteria that show zero in
DISQLiteUniDirQuery but not in the manager)
................
SELECT HistoricalKS3Levels.classcode,
(SELECT COUNT(HistoricalKS3Levels.upn)
FROM HistoricalKS3Levels
WHERE HistoricalKS3Levels.classcode = t.classcode
AND HistoricalKS3Levels.progresschecknumber = 6
) AS NumStudentsInClass ,
COUNT(HistoricalKS3Levels.upn) AS NumStudentsUsed ,
SUM(
CASE
WHEN HistoricalKS3Levels.[Points] > PointsMin
THEN 1
ELSE 0
END) AS NumMeetingCriteria,
ROUND((100.0 * SUM(
CASE
WHEN HistoricalKS3Levels.[Points] > PointsMin
THEN 1
ELSE 0
END) )/SUM(
CASE
WHEN Points IS NOT NULL
THEN 1
ELSE 0
END),1) AS PcntMeetingCriteria
FROM HistoricalKS3Levels
INNER JOIN
(SELECT classcode ,
upn ,
MIN(progressCheckNumber) AS PCmin,
Points AS PointsMin
FROM HistoricalKS3Levels
WHERE HistoricalKS3Levels.Points IS NOT NULL
GROUP BY classcode,
upn
) AS T
ON HistoricalKS3Levels.[ClassCode] = t.classcode
AND HistoricalKS3Levels.upn = t.upn
WHERE HistoricalKS3Levels.progresschecknumber = 6
AND HistoricalKS3Levels.progresschecknumber <> PCmin
GROUP BY HistoricalKS3Levels.classcode
ORDER BY PcntMeetingCriteria DESC
I don't use any persistant fields as the query component is used for many
different sql
Other related posts:
