Icon View Incident Report

Serious Serious
Reported By: Brandt van der Merwe
Reported On: 4/10/2005
For: Version 4.19 Build 1
# 2010 SQL IF Function Not Evaluating Properly and Repeating Old Values

When querying a master and detail table and concatenating string fields with the "+" operator, the query returns the incorrect results. This error has been observed in previous versions. The query is as follows:

select T.TestSurname +
if ( not T.TestFirstname is null then ', ' + T.TestFirstname else '' 
) +
if ( not T.TestInitials is null then ' (' + T.TestInitials + ')' else 
'' ) as Fullname,
D.DetailName
from TestDetail D left join Test T on D.TestID = T.TestID
order by Fullname

An export of the result is as follows :

Fullname,DetailName
"Surname 1, Firstname 1 (Init 1)","Detail 1 1"
"Surname 1, Firstname 1 (Init 1)","Detail 1 2"
"Surname 1, Firstname 1 (Init 1)","Detail 1 3"
"Surname 2, Firstname 2 (Init 1)","Detail 2 1"
"Surname 2, Firstname 2 (Init 1)","Detail 2 2"
"Surname 2, Firstname 2 (Init 1)","Detail 2 3"
"Surname 3, Firstname 2 (Init 3)","Detail 3 1"
"Surname 5, Firstname 2 (Init 3)","Detail 5 1"
"Surname 6, Firstname 6 (Init 6)","Detail 6 1"
"Surname 7, Firstname 6 (Init 6)","Detail 7 1"
"Surname 8, Firstname 8 (Init 6)","Detail 8 1"
"Surname 9, Firstname 8 (Init 9)","Detail 9 1"

Examples of incorrect result are:

"Surname 2, Firstname 2 (Init 1)","Detail 2 1"
"Surname 7, Firstname 6 (Init 6)","Detail 7 1"

In "Surname 2, Firstname 2 (Init 1)","Detail 2 1"
the "Init 1" is incorrectly retrieved.

The correct value for this line is:

"Surname 2, Firstname 2","Detail 2 1"

In "Surname 7, Firstname 6 (Init 6)","Detail 7 1"
the ", Firstname 6 (Init 6)" is incorrectly retrieved.

The correct value for this line is:

"Surname 7","Detail 7 1"

The two tables with data is included. Here is the script
to generate them:

/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "Test"
(
   "TestID" INTEGER NOT NULL,
   "TestName" VARCHAR(20) NOT NULL,
   "TestInitials" VARCHAR(10),
   "TestSurname" VARCHAR(50),
   "TestFirstname" VARCHAR(50),
NOCASE PRIMARY KEY ("TestID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

CREATE UNIQUE NOCASE INDEX IF NOT EXISTS "idxAlt" ON "Test" ("TestName");

INSERT INTO "Test" VALUES (1, 
         'Test 1', 
         'Init 1', 
         'Surname 1', 
         'Firstname 1');
INSERT INTO "Test" VALUES (2, 
         'Test 2', NULL, 
         'Surname 2', 
         'Firstname 2');
INSERT INTO "Test" VALUES (3, 
         'Test 3', 
         'Init 3', 
         'Surname 3', NULL);
INSERT INTO "Test" VALUES (4, 
         'Test 4', NULL, 
         'Surname 4', 
         'Firstname 4');
INSERT INTO "Test" VALUES (5, 
         'Test 5', NULL, 
         'Surname 5', NULL);
INSERT INTO "Test" VALUES (6, 
         'Test 6', 
         'Init 6', 
         'Surname 6', 
         'Firstname 6');
INSERT INTO "Test" VALUES (7, 
         'Test 7', NULL, 
         'Surname 7', NULL);
INSERT INTO "Test" VALUES (8, 
         'Test 8', NULL, 
         'Surname 8', 
         'Firstname 8');
INSERT INTO "Test" VALUES (9, 
         'Test 9', 
         'Init 9', 
         'Surname 9', NULL);

CREATE TABLE IF NOT EXISTS "TestDetail"
(
   "TestID" INTEGER NOT NULL,
   "TestDID" INTEGER NOT NULL,
   "DetailName" VARCHAR(20) NOT NULL,
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);


INSERT INTO "TestDetail" VALUES (1, 1, 
         'Detail 1 1');
INSERT INTO "TestDetail" VALUES (1, 2, 
         'Detail 1 2');
INSERT INTO "TestDetail" VALUES (1, 3, 
         'Detail 1 3');
INSERT INTO "TestDetail" VALUES (2, 1, 
         'Detail 2 1');
INSERT INTO "TestDetail" VALUES (2, 2, 
         'Detail 2 2');
INSERT INTO "TestDetail" VALUES (2, 3, 
         'Detail 2 3');
INSERT INTO "TestDetail" VALUES (3, 1, 
         'Detail 3 1');
INSERT INTO "TestDetail" VALUES (5, 1, 
         'Detail 5 1');
INSERT INTO "TestDetail" VALUES (6, 1, 
         'Detail 6 1');
INSERT INTO "TestDetail" VALUES (7, 1, 
         'Detail 7 1');
INSERT INTO "TestDetail" VALUES (8, 1, 
         'Detail 8 1');
INSERT INTO "TestDetail" VALUES (9, 1, 
         'Detail 9 1');



Resolution Resolution
Fixed Problem on 4/11/2005 in version 4.20 build 1


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM CLX Client-Server
DBISAM CLX Client-Server with Source
DBISAM CLX Standard
DBISAM CLX Standard with Source
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source

Image