SQL: String-Spalten für numerischen Vergleich verwenden

[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