SQL: String-Spalten für numerischen Vergleich verwenden

[M@tthias]

Liebe SQL-Experten,

leider verzweifle ich gerade an einer scheinbar banalen Aufgabe. Ich weiß nicht, ob meine SQL-Kenntnisse in den letzten Jahren zu sehr eingerostet sind, oder ob ich einfach nur ein Brett vorm Kopf habe.

Es geht um folgendes: ich habe eine SQL-Tabelle mit u.a. den Spalten Wert_Min und Wert_Max. Beide sind vom Typ CHAR(10). Die Tabelle wird verwendet, um für globale Einstellungen zulässige Werte vorzudefinieren. Zum einen konkrete Werte (die werden dann jeweils nur in Wert_Min geschrieben), aber auch tatsächlich Wertebereiche (i.W. numerische Intervalle). Dafür wollte ich gerne keine zusätzlichen Spalten anlegen, sondern die Char-Spalten universell einsetzen.

Nun mein Problem: wenn ich einen Wertebereich von 10 bis 30 zulassen will und die Vergleiche mit Strings vornehme (... WHERE Wert_Min <= :wert AND Wert_Max >= :wert), ist z.B. '2000' ein gültiger Wert. Daher möchte ich hier wirklich numerisch vergleichen. Ich scheitere daran, die Datentypen kompatibel zu bekommen. Habe es sogar mit einer WITH AS Tabelle versucht:

WITH numerischeBereiche AS (
  SELECT cast(Wert_Min AS int) AS min, cast(Wert_Max AS int) AS max
FROM SqlTest.WP_Wertebereich
  WHERE Parameter_Id = 4 AND Wert_Max IS NOT NULL
)
SELECT *

  FROM numerischeBereiche
  WHERE min <= 15

Liefert den Fehler
[SQL0802] Fehler bei Datenumsetzung oder beim Zuordnen von Daten. Ursache . . . . : Fehler der Art 6 ist aufgetreten: Fehlerarten und ihre Bedeutung: [...] 6 -- Ungültige numerische Daten

Ohne die WHERE-Bedingung am Ende klappt es. Das lustige ist, dass die Daten in der mittels WITH AS erzeugten Tabelle wirklich numerisch zu sein scheinen. Ersetze ich bei den CASTs int durch z.B. real, werden die Zahlen mit Dezimalpunkt angezeigt. Ich komme hier einfach nicht weiter und hoffe sehr auf eure Ratschläge.

Beste Grüße
Matthias

[andreaspr@aon.at]

Klingt für mich so als ob irgendwo in WP_Wertebereich es einen Wert gibt der nicht in INT umgewandelt werden kann.
Hast du schon mal alle Daten geprüft?

[B.Hauser]

Kann es sein, dass die Spalte Wert_Min manchmal auch nur *Blanks enthält, also keine numerischen Werte?

Wenn ja solltest Du in diesem Fall eine Case-Anweisung integrieren.

SELECT cast(Case When Wert_Min <= '' or Wert_Min is NULL 
                 then 0 
                 else Wert_Min End AS int) AS min ...

Birgitta

[M@tthias]

Vielen Dank für die wertvollen Hinweise. Euer Verdacht war korrekt, es gibt auch alphanumerische Inhalte in der Spalte. Da ich mit dem ersten WHERE allerdings auf Datensätze eingeschränkt hatte, die nur numerische Werte enthalten, dachte ich es würde funktionieren. Vor allem da das Statement ohne das WHERE am Ende auch funktionierte. Ich habe kurzerhand mal in einer Test-Tabelle alle anderen Zeilen (mit nicht-numerischen Werten) gelöscht und siehe da, es klappt.

Mit der vorgeschlagenen CASE-Anweisung ist es leider noch nicht ganz getan, da eben außer Blanks auch Buchstaben vorkommen können. Soweit ich weiß, gibt es bei DB2 leider kein isNumeric(). Ich werde dann mal nach Alternativen suchen. Bei einem ersten Überfliegen hatte ich Ansätze mit der translate() Funktion gesehen. Ursprünglich wollte ich noch mit einem LIKE auf numerische Inhalte einschränken, aber das scheint mittels WHERE ja grundsätzlich nicht zu funktionieren.

Aber mit der gewonnenen Erkenntnis kann ich endlich sinnvoll weiter nach einer geeigneten Lösung suchen.

Vielen Dank!

[andreaspr@aon.at]

Du könntest dir auch eine eigene isNumeric() Funktion schreiben.

lg Andreas

[Pikachu]

Warum so viel Umstand? Für numerische Werte gibt es in DB2 for i extra numerische Felder, um das Arbeiten damit einfacher zu machen.

[Fuerchau]

Simple IsNumeric:

where
len(trim(translate(field, ' ', '0123456789', ' ')) = 0

Das Problem mit SQL ist aber z.T. dass durch Optimierungen Ausdrücke ausgewertet werden, die noch nicht auszuwerten sind, Beispiel:

where
len(trim(translate(field, ' ', '0123456789', ' ')) = 0
and
cast(trim(field) as integer) > 0

Durch die And-Klausel wird die 2. Bedingung ggf. geprüft obwohl sie durch die 1. Bedingung bereits rausfällt, die Prüfung also eigentlich nicht mehr nötig ist.
Dies liegt aber am Optimizer begründet, der SQL's "umbaut" und dann solche Konstrukte auf die Nase fliegen.
Dies gilt auch für Felder in der Select-Liste.

Leider funktioniert das auch nicht mit Subselect, CTE oder auch einer View.
Ich hatte sowas bis V5R4 am laufen, nach der Umstellung auf V6R1 klappte der SQL genau deswegen nicht mehr und wir mussten den Teil neu schreiben.

[M@tthias]

Hallo nochmal und vielen Dank für die weiteren Vorschläge. Ich habe es nun offenbar mit einer TRANSLATE-Variante hinbekommen. Die Inspiration dazu habe ich hier gefunden: check for numeric in db2 sql - dBforums

Allerdings wollte mir die Einschränkung auf numerische Werte mit geeigneter WHERE-Bedingung ja schon anfangs nicht zum Erfolg helfen, daher habe ich es wie in Brigittas Vorschlag vorne beim SELECT untergebracht. So sieht die Lösung aktuell aus.

WITH
numerischeBereiche AS (
SELECT CASE WHEN translate(Wert_Min, ' ', '0123456789') = ' ' THEN cast(Wert_Min AS int) 
ELSE 0 
END AS min
FROM


SqlTest.WP_Wertebereich
WHERE Parameter_Id = 4 AND Wert_Max IS NOT NULL
)
SELECT


*
FROM numerischeBereiche
WHERE min <= 15;

Näher werde ich mir die Sache erst am Montag wieder ansehen, aber so scheint es nun zu funktionieren.

Nochmals Danke und ein schönes Wochenende
Matthias

[Obv]

Wert_Min darf dann allerdings nie blank sein sonst stimmt translate(Wert_Min, ' ', '0123456789') = ' ' trotzdem

[M@tthias]

Das war doch nur ein relevanter Auszug der Lösung ;-)

Insgesamt sieht es nun so aus und scheint wunderbar zu funktionieren (sorry, der Editor hier zerhaut die Formatierung immer total, bekomme es nicht übersichtlicher):

ExecSQL
WITH numerischeBereiche AS(
SELECT CASE WHEN translate(Wert_Min,' ','0123456789')=' ' AND
translate(Wert_Max,' ','0123456789')=' ' AND
Wert_Min !=' ' AND Wert_Max !=' '
THEN cast(Wert_Min AS int)
ELSE 0
END AS min,
CASE WHEN translate(Wert_Min,' ','0123456789')=' ' AND
translate(Wert_Max,' ','0123456789')=' ' AND
Wert_Min !=' ' AND Wert_Max !=' '
THEN cast(Wert_Max AS int)
ELSE 0
END AS max
FROM WP_Wertebereich
WHERE Parameter_Id = :paramID AND Wert_Max IS NOT NULL
)
SELECT count(min)
INTO :resultCount
FROM numerischeBereiche
WHERE max > min AND min <= :paramWert AND max >= :paramWert;

Dazu hätte ich noch eine kleine Frage: gibt es eine Möglichkeit, die beiden CASE im SELECT zusammenzufassen? Ich habe keine Möglichkeit gefunden, beim THEN mehrere Spalten zu selektieren. Oder muss ich das Konstrukt wohl oder übel doppelt mitschleppen?

Viele Grüße
Matthias

[Fuerchau]

Du kannst es dir schon einfacher machen, da der Case mehrere Zweige erlaubt.
Allerdings ist natürlich je Spalte ein Ausdruck zu definieren.

case
when wert_min = ' ' then NULL
when translate(....) <> ' ' then NULL
else cast(...)
end as min