Problem z datą IBM DB2 Character in CAST argument not valid

[Ralliart]

Całkiem niedawno przerabiałem jedną z procedur mojego kolegi, która nie uwzględniała w swoich wyliczeniach możliwości istnienia roku dwucyfrowego. Nie zagłębiając się w szczegóły kodu podam tylko, że po aktualizacji podczas wykonywania procedury wyboru liczb z daty serwer zwracał błąd niedozwolonej wartości: Character in CAST argument not valid

Problematyczna funkcja wygląda tak:

INT(RTRIM(SUBSTR(CURRENT DATE) , 4 , 1) CONCAT (SUBSTR(CURRENT DATE) , 6 , 2)  CONCAT (SUBSTR(CURRENT DATE) , 9 , 10))

O co w tym chodzi? Funkcja zwraca datę sformatowaną pod konkretną tabelę w bazie danych gdzie jej format wygląda tak: RMMDD. Samo polecenie CAST jest użyte w sposób niejawny, ponieważ nie ma potrzeby podwójnego formatowania po zastosowaniu typu INTIGER dla pola wynikowego.

Oczywistym wydawało się poprawienie funkcji w ten sposób:

INT(RTRIM(SUBSTR(CURRENT DATE) , 3 , 2) CONCAT (SUBSTR(CURRENT DATE) , 6 , 2)  CONCAT (SUBSTR(CURRENT DATE) , 9 , 10))

Dzięki prostemu zabiegowi - przesunięciu pierwszego substringa o jedno pole wcześniej powinien zostać wybrany rok w formacie RR i tym samym problem z głowy. Dodatkowo formatowanie wartości jako INTIGER powinno obciąć zbędne 0 w dacie jednocyfrowej. Jest to istotne o tyle, że daty w kolumnie tabeli z której wybieramy rekordy z jedną cyfrą roku nie posiadają zera z przodu, a co za tym idzie próba porównania daty w zapisie np 090801 do 90801 nie powiedzie się, ponieważ te dane nie są tożsame.

Tyle tytułem wstępu i wyjaśnień potrzebnych dla zrozumienia takiego a nie innego układu danych.

I tu zaczyna się cyrk: Procedura została skompilowana poprawnie, ale w przy każdym uruchomieniu procedury serwer zaczyna zwracać błąd z tematu: Character in CAST argument not valid. Ponieważ procedura była zakładana spod iseries navigatora jest podejrzenie, że powodem błędu jest zła strona kodowa, tym bardziej, że na 2 serwerach działa poprawnie. Szybkie wpisanie procedury spod aplikacji klienckiej serwera i ponowna kompilacja kończ się tym samym błędem.

Rozpoczyna się sprawdzanie procedury:

Deklaracja

INT(RTRIM(SUBSTR(CURRENT DATE) , 3 , 2) CONCAT (SUBSTR(CURRENT DATE) , 6 , 2)  CONCAT (SUBSTR(CURRENT DATE) , 9 , 10)

rzeczywiście zwraca jako wynik kwerendy znaki zapytania - zostaje sprawdzona deklaracja CURRENT DATE, która zwraca wynik 06.01.2010 i faktycznie wychodzi na to, że zapytanie jest źle skonstruowane, bo wybiera

rok: .0

miesiąc: .2

dzień 10

co daje w sumie błędną wartość INTIGER .0.210, która nie składa się z samych cyfr.

Szybka poprawka na

INT(RTRIM(SUBSTR(CURRENT DATE) ,  9 , 2) CONCAT (SUBSTR(CURRENT DATE) , 4 , 2)  CONCAT (SUBSTR(CURRENT DATE) , 1 , 2)

zwraca wynik

rok: 10

miesiąc: 01

dzień 06

co daje w sumie poprawną wartość INTIGER 100106, która składa się z samych cyfr.

Teoretycznie problem rozwiązany, ale od teorii daleko do praktyki... bo naszym oczom ponownie ukazuje się komunikat Character in CAST argument not valid... Deja vu totalne.

Rozwiązanie problemu: szybkie zerknięcie do SQL reference for IBM UDB kieruje nas na właściwy trop - winny jest format daty. Serwer nie może zdecydować się na jedno z dostępnych kodowań (podam cztery dla przykładu jest ich setki)

• Europejskie (EUR) 06.01.2010
• ISO (ISO) 2010-01-06
• Amerykańskie (USA) 10/01/06
• Japońskie przemysłowe (JIL) 2010-01-06

Poprawny zapis funkcji z wymuszonym kodowaniem sprawia, że procedura wreszcie wykonuje się poprawnie i nie ma problemów z formatem daty a my możemy wypić browara za kolejny rozwiązany problem i zapłakać nad brakiem kompilatora z prawdziwego zdarzenia dla SQL

INT(RTRIM(SUBSTR((CURRENT DATE),ISO) ,  9 , 2) CONCAT (SUBSTR((CURRENT DATE),ISO) , 4 , 2)  CONCAT (SUBSTR((CURRENT DATE),ISO) , 1 , 2)

Mam nadzieję, że to rozjaśni nieco problem osobom które się z nim spotykają. Oczywiście w przypadku MYSQL czy MSSQL ten sam błąd również może się pojawić i rozwiązanie z DB2 powinno zadziałać. Formaty dat to jeden z największych problemów do rozwiązania w zastosowaniach bazodanowych a opisany błąd to świetny przykład na to, że pisząc kod trzeba uważać na wszystkie wprowadzane dane i nie unikać formatowania dat, ponieważ brak odpowiednich deklaracji może być zgubny.

PS. Na koniec ode mnie dla zainteresowanych: w przypadku zastosowania jawnego funkcji CAST prawidłowy kod powinien wyglądać tak:

CAST((RTRIM(SUBSTR((CURRENT DATE),ISO) ,  9 , 2) CONCAT (SUBSTR((CURRENT DATE),ISO) , 4 , 2)  CONCAT (SUBSTR((CURRENT DATE),ISO) , 1 , 2)) AS NUMERIC(6, 0)

Post jest własnością intelektualną autora i jako taki stanowi jego własność.

[BigKoma]

Miałem ostatnio podobny problem. Co prawda operowałem w VB , nie mniej było łatwiej

1. Tak jak zaznaczyłeś ważne jet formatowanie wszystkich zmiennych "data"

2. Pożądane cyfry "wyciągamy" za pomocą funkcji MID. To nie będzie koniecznie jeżeli mamy już sformatowaną datę "RRMMDD" .

2. Później konwersja na int i dalej znów do string , by pozbyć się zera.

[Ralliart]

W VB jest dopuszczalne dowolne mieszanie w dacie, wbrew pozorom w SQL jest łatwiej Założę się, że Twoje makro w pewnych warunkach nie będzie działać dobrze. Wszystko zależy od ustawień regionalnych komputera - jeżeli będzie miał datę krótką formatowanie zdefiniowane w makrze się rozjedzie.