Sie sind nun wieder eingeladen, die Diskussion spezieller technischer Probleme mit zu verfolgen. Bitte schicken Sie Fragen, Anregungen oder Antworten zu den vorgestellten Themen – ebenso wie Ihre Kritik – an unsere e-Mail-Adressen: dieter.bender@MidrangeMagazin.de oder Redaktion@MidrangeMagazin.de

Frage:

Wir haben eine Datei, in der in einem Character-Feld numerische Werte drinstehen. Gibt es eine Möglichkeit, die Inhalte dieser Felder in ein numerisches Feld zu übertragen. Wir haben es mit den SQL-Funktionen CAST und auch einigen Umwandlungsfunktionen – wie DECIMAL oder INTEGER – versucht, bekommen aber immer Abbrüche beim ersten Satz, wo ein ungültiger Inhalt gefunden wird.

Antwort:

Das Problem liegt in der Tat darin, dass die Fehlerbedingung beim so genannten searched Update mit SQL nicht abgefangen werden kann. Die Operation wird beim ersten Fehler abgebrochen und SQL geht davon aus, dass die gesamte Transaktion dann mit Rollback zurückgenommen wird. Hier wird erneut sichtbar, dass die Verwendung von SQL ohne Transaktionssteuerung mit Commit das Prinzip Hoffnung mit beinhaltet.

In einem Programm mit embedded SQL könnte man nun selbstverständlich das Update Satz für Satz vornehmen. Denn für jeden einzelnen Satz bekommt man dann die Fehlerinformation als SQL-Code an das Programm geliefert und kann dann entsprechend reagieren.
SQL ist allerdings so mächtig, dass hier das Motto des Eisenwarenhändlers – „Geht nicht, gibt’s nicht“ – meist richtig ist, man muss nur zuweilen ein wenig Mühe zum Nachdenken aufwenden.

create table

testnum
(
Testnum_id int NOT NULL,
alfafeld char (20),
numfeld dec (15, 2)
)

Abbildung 1

Für mein kleines Beispiel habe ich zunächst eine Testdatei mit obigem SQL-Statement erzeugt und mit ein paar Werten gefüllt: gültige und ungültige, wie zum Beispiel Blanks, Null und Alfazeichen. Die Grundidee ist nun, mit einer CASE-Konstruktion im Update-Statement immer dann NULL in das Zielfeld zu stellen, wenn ein ungültiger Wert im Alfa-Feld steht und ansonsten die Zeichenkette zu konvertieren. Der Wert NULL bedeutet hierbei, dass dieses Feld nicht gefüllt wird und ist nicht identisch mit dem Inhalt 0 oder Blank.

Der einfachste Fall ungültiger Werte liegt vor, wenn in dem Character-Feld Blanks stehen; diese Konstellation wird mit einer separaten When-Klausel abgefangen und das numerische Feld mit dem Null-Wert belegt.

Mit einer weiteren When-Klausel werden als Nächstes die gültigen Werte ermittelt, und für diesen Fall wird die Zeichenkette mit der Typ-Umwandlungsfunktion Decimal in eine kaufmännische Dezimalzahl gewandelt, die noch mit der relativ neuen Round-Funktion entsprechend gerundet werden könnte.

Im Else-Zweig der Case-Klausel wird in allen übrigen Fällen wieder der Null-Wert an das Zielfeld zugewiesen.

UPDATE testnum

SET numfeld =
CASE
WHEN trim(alfafeld) = ‚ ‚ THEN null
WHEN trim(
translate(
translate(substring(trim(alfafeld), 1, 1), ‚ ‚, ‚+-‚) !!
substring(trim(alfafeld), 2, 19), ‚ ‚, ‚0123456789‘))
IN(‚ ‚, ‚,‘)
THEN decimal(alfafeld, 15, 2)
ELSE NULL
END

Abbildung 2

Woran ist nun aber ein gültiger, konvertierbarer Wert erkennbar? Er kann mit einem Vorzeichen „+“ oder „-„ beginnen und enthält außer Ziffern maximal ein Dezimalkomma. Die Prüfung auf diese Eigenschaften erfolgt durch das Aufsplitten des Feldes in das erste Zeichen und den Rest; das erste Zeichen wird, soweit es ein Vorzeichen ist, mit der Translate-Funktion gegen ein Blank getauscht und anschließend werden die beiden Felder wieder zusammengesetzt; die Trim-Funktion entfernt bei diesem Vorgang unerwünschte Blanks am Anfang und Ende. Bei diesem Zwischenwert werden dann – wieder mit Translate – alle Ziffern gegen Blanks getauscht und anschließend darf nach erneutem Trimmen maximal ein Komma oder nichts mehr übrig bleiben.
Wenn man statt Dezimalkomma einen Dezimalpunkt zulässt oder wenn noch ein Währungskennzeichen folgen darf, muss diese Prüfung entsprechend modifiziert werden.

Daten anzeigen

Datenbreite . . . . . : 59
Anfang auf Zeile . . . . . . Anfang in Spalte . . . . . .
….+….1….+….2….+….3….+….4….+….5….+….
TESTNUM_ID ALFAFELD NUMFELD
1 17 17,00
2 -137 137,00-
3 +12 12,00
4 123,6 123,60
5 -23,5 23,50-
6 +24,5 24,50
7 otto76 –
8 o76 –
9 1,4,76 –
10 17 17,00
11 17 17,00
12 –
13 – –
14 17 17,00
******** Datenende ********

Ende
F3=Verlassen F12=Abbrechen F19=Links F20=Rechts F24=Weitere Tasten

Abbildung 3

An diesen Beispieldaten ist nun erkennbar, wie das ganze funktioniert hat. Im Satz 7 und 8 sind die Buchstaben übrig geblieben und der Else-Zweig ist zum Zug gekommen. In Spalte 9 war es das überzählige Komma. Satz Nummer 12 enthält Blank im ALFAFELD und wurde in der ersten When-Bedingung erwischt. Satz 13 enthält NULL im ALFAFELD und NULL liefert mit allen Bedingungen False, also schlägt hier wieder der Else-Zweig zu. Wie sagt doch mein Eisenwarenhändler so schön, „Sie wissen doch, da wo man Nägel und Schrauben noch einzeln bekommt: geht nicht – gibt’s nicht.“

Den Autor Dieter Bender erreichen Sie unter dieter.bender@midrangemagazin.de