Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> null as DateTime? in Oracle gives ORA-00932
 

Pages: 1
Bugs & Issues
null as DateTime? in Oracle gives ORA-00932
Page:1/1 

  Print all messages in this thread  
Poster Message
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 12-Apr-2019 02:09:27.  
This (contrived example of a real word issue) against northwind in SQL server is fine
Code:
from o in Order
select new
{
    ShippedDateDerived = o.ShipVia == 1
                    ? null as DateTime?
                    : (o.ShipVia == 2
                        ? o.ShippedDate.Value.AddDays(5)
                        : o.ShippedDate.Value.AddDays(10)),
    o.OrderDate,
    o.OrderId,
    o.RequiredDate,
    o.ShippedDate,
    o.ShipVia
}
but run the same against Oracle and get a 'ORA-00932: inconsistent datatypes: expected CHAR got DATE' error.
I have a workaround to replace null as DateTime? with System.Data.SqlTypes.SqlDateTime.MinValue.Value e.g
Code:
from o in Order
select new
{
    ShippedDateDerived = o.ShipVia == 1
                    ? System.Data.SqlTypes.SqlDateTime.MinValue.Value
                    : (o.ShipVia == 2
                        ? o.ShippedDate.Value.AddDays(5)
                        : o.ShippedDate.Value.AddDays(10)),
    o.OrderDate,
    o.OrderId,
    o.RequiredDate,
    o.ShippedDate,
    o.ShipVia
}
but this is not ideal.

V5.5.2
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38090 posts
# Posted on: 12-Apr-2019 09:07:43.  
We'll look into it.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38090 posts
# Posted on: 12-Apr-2019 09:53:36.  
It's reproducible with just 1 ?:.

Cause is that all 'then' elements have to have the same type in a case structure. (ref: https://stackoverflow.com/a/49111721 )

The problem here is that the NULL is a string typed parameter. So my test query:

Code:
SELECT CASE
         WHEN CASE
                WHEN ("LPLA_1"."DEPTNO" = :p1) THEN 1
                ELSE 0
             END = 1 THEN :p3
         ELSE "LPLA_1"."HIREDATE"
     END            AS "ShippedDateDerived",
     "LPLA_1"."JOB" AS "Job"
FROM "SCOTT"."EMP" "LPLA_1"

where p3 is NULL. However its type is 'String' (varchar2). SQL Server doesn't care, Oracle apparently does. I have to check, but my guess is that because it's NULL, we can't determine the type on the fly for the parameter based on the value, and therefore it picks the default. (it's NULL after all... ).

This works fine in all other cases, but in this particular case it has to have the same type as the rest of the 'then' clauses. As the second one is a DATE, you get the error *there*, because the first one is a char type (the parameter) and the second one therefore has to be as well...

We'll see what we can do about this.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38090 posts
# Posted on: 12-Apr-2019 10:25:00.  
The problem is indeed that there's no type info when the function call (which represents the case when then end fragment) is converted to SQL: it has as value 'NULL' and that's it. For oracle "" and NULL are the same so the choice for varchar2 isn't wrong, but in the case of this complete case expression it is.

NULL is a specific value, we could in-line it without problems at that point. It otherwise requires a lot of refactoring as we need to keep the type around of the element that's assigned NULL. Which might not be that easy to determine in the first place.

We'll inline it as NULL and not create a parameter (which is a bit silly anyway).


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38090 posts
# Posted on: 12-Apr-2019 10:55:35.  
Inlining the NULL literal works fine Regular Smiley See new 5.5.3 hotfix build for the fix.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 17-Apr-2019 19:27:28.  
Thanks for the fix! Regular Smiley

Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.