Hello,
When using Oracle SQL against a Baan table with NULL dates, I am having problems selecting on the NULL dates.

The example I am working with is the tibom010 table (bill of material) and the field is exdt (expiry date).

Oracle displays the dates as :

T$MITM T$SITM T$EXDT
---------------- ---------------- --------------------
A4400 43700 00-000-00


But when I try to include the expiry date field in my where clause, such as:


select t$mitm, t$sitm, t$exdt
from baandb.ttibom010200
where t$exdt = '00-000-00'


Oracle returns the following error

T$MITM T$SITM T$EXDT
---------------- ---------------- --------------------
ORA-01847: day of month must be between 1 and last day of month


I can work around the problem by using a where clause like
where t$exdt < '01-JAN-72' but that is not very elegant.

Any suggestions ?

Baan stores dates as long . So empty date means 0 .

So can write ur SQL as


select t$mitm, t$sitm, t$exdt
from baandb.ttibom010200
where t$exdt = 0

In your db_resource file, if ora_date is set to 0 (zero is the default if ora_date is not specified), then the Baan NULL date value is 01/01/-9999. If ora_date is set to 1, then the Baan NULL date value is 01/01/-4712. Oracle does not support the use of the value 01/01/-9999 in SQL statements, but does support the value 01/01/-4712. So if you will try using the following, it should work for you.

select t$mitm, t$sitm, t$exdt
from baandb.ttibom010200
where t$exdt <= to_date('01/01/-4712','MM/DD/SYYYY')


Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Thanks Gilbert,
This has been bugging me for a while, so it is really nice to know the correct way of using the NULL date in the where clause,

Thanks again,
John

All Baan columns have a NOT NULL constraint so your query should not give any results. Baan is filling all columns with a value, even when it look likes if you have an empty field from enduser perspective.

Hope this helps,
Dick

Hi,

This might be a workable sollution for you:

We are using Triton 3.1 with Oracle V7.3.4.5.0.

Our current default 0 date is also 01-JAN-99 before christ.


We have an oracle-package function that returns the current 0 date:
That might help you, but does not replace/solve the issue. It is a work arround
to get some oracle sql statements running outside of Triton.
I do appologize for the dutch text in here.
If you need more info, just aks.

-- f_def_date
-- doel : teruggeven van een dataveld met de 'default triton-date'
--
-- geen argumenten
--
-- resultaat : date 1/jan/9999 00h00min00s voor christus
--
-- voorbeeld : update ...
-- set t$.... = ster.f_def_date
-- where ...
--
-- select ...
-- from ...
-- where t$.... = ster.f_def_date
--
-- rev 1 pvb 7/9/98
-- na upgrade van oracle 7.3.2.1 naar 7.3.4.0.0
-- verscheen een ora-01841 fout
-- (datums voor 1/1/4713 voor christus niet geldig)
-- Oracle Belgie omschreef dit als een 'opgeloste bug'
-- (lognummer 147683) - het had dus nooit mogen werken
-- workaround : tabel dummy_date met 1 veld v_date
-- en 1 rij welke de triton default bevat
-- de terug-te-geven waarde wordt uit deze hulptabel opgehaald
-- ipv via een to_date functie
--
--
-- rev 2 pvb 22/4/99
-- default mask van f_nvl_date datum op RR ipv YY gezet
--
-- rev 3 gerhey 17 may 2000: extra fie: get unix username for current user
--
--
-- f_nvl_date
-- doel : vervangt waarde van een datum veld door een opgegeven waarde
-- als dit datum veld de triton-default heeft (01-jan-9999 bc)
-- anders wordt originele datum teruggegeven
-- (cfr de nvl functie voor null-velden)
--
-- argumenten
-- - datum veld
-- - een datum als char met default oracle date formaat ('dd-mon-yy')
-- - optioneel een geldige datum-format-mask
-- (dit mask wordt indien aanwezig op vorig argument toegepast)
--
-- resultaat
-- - een datum
--
-- voorbeeld : select ...
-- from ...
-- where ster.f_nvl_date(t$.... , '01-jan-97') = ...
--
-- select ...
-- from ...
-- where ster.f_nvl_date(t$.... , '01-jan-2000','dd-mon-yyyy')=.
--
--


create or replace package triton.ster as

function f_def_date
return date;

function f_nvl_date
(p_date in date,
p_nvl in char,
p_mask in char default 'DD-MON-RR')
return date;

function f_get_osuser
return varchar2;

-- pragma's nodig om functies binnen sql te kunnen gebruiken

pragma restrict_references (f_def_date , WNDS, WNPS);
pragma restrict_references (f_nvl_date , WNDS, WNPS);
pragma restrict_references (f_get_osuser, WNDS, WNPS);
pragma restrict_references (ster , WNDS, WNPS);

end ster;
/

sho errors

create or replace package body triton.ster as

v_def_date date;

cursor c_date is
select v_date
from dummy_date;

function f_def_date
return date
is
begin
return v_def_date;
end;

function f_nvl_date
(p_date in date,
p_nvl in char,
p_mask in char)
return date
is
begin
if p_date = v_def_date then
return to_date(p_nvl,p_mask);
else
return p_date;
end if;
end f_nvl_date;

function f_get_osuser
return varchar2
is
osuser v$session.osuser%type;
begin
select osuser into osuser
from v$session
where audsid=(select userenv('sessionid') from dual);

return osuser;
end f_get_osuser;

-- initialisatie van de package

begin
open c_date;
fetch c_date into v_def_date;
close c_date;
--
-- onderstaande statements werken dus niet meer in 7.3.4
--
-- v_def_date :=
-- to_date('01-jan-9999 bc 00:00:00','dd-mon-yyyy bc hh24:mi:ss');
end ster;

/

sho errors

create public synonym ster for triton.ster;

grant execute on triton.ster to public;

-------------------------------------------------
The table dummy date looks as follows:
SQL> desc dummy_date
Name Null? Type
------------------------------- -------- ----
V_DATE DATE

SQL> select * from dummy_date;

V_DATE
---------
01-JAN-99


To get that table created, use an sql statement, that get's the date from any triton table with the 0 date in it.
(e.g. create table dummy_date as (select t$ddta from ttdsls041333 where ...)

Below is a test is did in Oracle 9.2

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select t$cont,t$item,t$pref from baandb.ttdssc001300 where t$pref=0;
select t$cont,t$item,t$pref from baandb.ttdssc001300 where t$pref=0
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER


1* select t$cont,t$item,t$pref from baandb.ttdssc001300 where t$pref=to_date('01/01/-4712','MM/DD/SYYYY')

no rows selected

1* select t$cont,t$item,t$pref from baandb.ttdssc001300 where to_char(t$pref,'YYYYMMDD')='00000000'

1873 rows selected.
SQL>


Roy