Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Incident Reports » Incident Reports Reported for Version 4.19 » View Incident Report |
Serious |
Reported By: Brandt van der Merwe Reported On: 4/10/2005 For: Version 4.19 Build 1 |
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');
This web page was last updated on Wednesday, March 20, 2024 at 07:22 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |