Hello,
I am trying to sort on a field that can be null. when i sort on this field, none of the objects with the null field are returned. If i don't sort, then all the objects are returned.
In concrete terms, in the mapping, etc.. below, i have the following
AlertLog ---> contains alerts for a Device, cannot be null
Device ------> belongs to a DeviceGroup, but can be null
when i try to sort on the DeviceGroup object's name field, i do not get any of the of the AlertLog objects for Device's that have a null DeviceGroup.
I can see in the SQL below, why it is happening. When i do the sort, the DeviceGroupId is explicitly being selected When i don't do the sort, the DeviceGroupId is NOT being explicitly selected . I have a feeling that i am doing something wrong in the mapping file, but i don't know what i need to change to get it to work.
Any suggestions or help would be greatly appreciated!!!
Thanks,
Joe
Hibernate version:2.1
Mapping documents:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping package="com.netkey.platform.data">
<class name="DeviceGroup" table="KIOSK_GROUP">
<id name="id" column="Id" type="java.lang.Integer">
<generator class="assigned"/>
</id>
<property name="customerId" column="CUSTOMER_Id" type="java.lang.Integer" not-null="true" />
<property name="name" column="Name" type="java.lang.String" not-null="true" />
<property name="description" column="Description" type="java.lang.String" />
<!--
<many-to-one name="kioskProfile" column="KIOSK_PROFILE_Id" class="KioskProfile" />
-->
</class>
<class name="Device" table="KIOSK">
<id name="deviceId" column="Id" type="java.lang.String">
<generator class="assigned"/>
</id>
<property name="name" column="Name" type="java.lang.String" not-null="true" />
<property name="description" column="Description" type="java.lang.String" />
<property name="location" column="Location" type="java.lang.String" />
<property name="lastAccess" column="LastUpdatedOn" type="java.util.Date" not-null="true" />
<property name="agentName" column="AgentName" type="java.lang.String" />
<property name="customerId" column="CUSTOMER_Id" type="java.lang.Integer" not-null="true" />
<property name="timeZone" column="TimeZone" type="java.lang.Integer" />
<property name="ipAddress" column="IP_Address" type="java.lang.String" not-null="true" />
<property name="currentOrderNo" column="KioskCurrentOrderNo" type="java.lang.Integer" not-null="true" />
<property name="designId" column="DesignID" type="java.lang.Short" />
<property name="customerLocalPrinterName" column="CustomerLocalPrinterName" type="java.lang.String" />
<property name="managerLocalPrinterName" column="ManagerLocalPrinterName" type="java.lang.String" />
<property name="connectionType" column="ConnectionType_ID" type="java.lang.Integer" />
<property name="deviceGroup" column="KIOSK_GROUP_Id" type="java.lang.Integer" />
<property name="deviceStatus" column="Status_ID" type="java.lang.Integer" />
<property name="path" column="PathID" type="java.lang.Integer" />
<property name="retailStore" column="RetailStoreKey" type="java.lang.Integer" />
<property name="setupProfile" column="SETUP_PROFILE_Id" type="java.lang.Integer" />
<many-to-one name="deviceGroupObj"
class="DeviceGroup" column="KIOSK_GROUP_Id" not-null="false" insert="false" update="false"/>
</class>
<class name="AlertLog" table="ALERT_LOG" >
<id name="id" column="Id" type="java.lang.Integer">
<generator class="identity"/>
</id>
<property name="kioskGroupId" column="Kiosk_Group_Id" type="java.lang.Integer" />
<property name="kioskId" column="KIOSK_Id" type="java.lang.String" />
<property name="errorType" column="ErrorType" type="java.lang.String" />
<property name="description" column="Description" type="java.lang.String" />
<property name="date" column="Date" type="timestamp" not-null="true" />
<property name="userAck" column="UserAck" type="java.lang.Integer" />
<many-to-one name="device" class="Device" column="Kiosk_Id"
insert="false" update="false" not-null="true">
</many-to-one>
<many-to-one name="acknowledgedBy" class="User" column="UserAck"
insert="false" update="false" not-null="false">
</many-to-one>
</class>
</hibernate-mapping>
None
Name and version of the database you are using:SQL Server 2000 SP3
The generated SQL (show_sql=true):select top 5000 alertlog0_.Id as Id0_, device1_.Id as Id1_, user2_.Id as Id2_, a
lertlog0_.Kiosk_Group_Id as Kiosk_Gr2_0_, alertlog0_.KIOSK_Id as KIOSK_Id0_,
alertlog0_.ErrorType as ErrorType0_, alertlog0_.Description as Descript5_0_,
alertlog0_.Date as Date0_, alertlog0_.UserAck as UserAck0_,
alertlog0_.Kiosk_Id as Kiosk_Id0_, device1_.Name as Name1_,
device1_.Description as Descript3_1_, device1_.Location as Location1_,
device1_.LastUpdatedOn as LastUpda5_1_, device1_.AgentName as AgentName1_,
device1_.CUSTOMER_Id as CUSTOMER7_1_, device1_.TimeZone as TimeZone1_,
device1_.IP_Address as IP_Address1_, device1_.KioskCurrentOrderNo as KioskCu10_1_,
device1_.DesignID as DesignID1_, device1_.CustomerLocalPrinterName as Custome12_1_,
device1_.ManagerLocalPrinterName as Manager13_1_, device1_.ConnectionType_ID as Connect14_1_,
device1_.KIOSK_GROUP_Id as KIOSK_G15_1_, device1_.Status_ID as Status_ID1_,
device1_.PathID as PathID1_, device1_.RetailStoreKey as RetailS18_1_,
device1_.SETUP_PROFILE_Id as SETUP_P19_1_, user2_.FirstName as FirstName2_,
user2_.LastName as LastName2_, user2_.LoginName as LoginName2_, user2_.Password as Password2_,
user2_.Phone as Phone2_, user2_.Fax as Fax2_, user2_.Email as Email2_,
user2_.Pager as Pager2_, user2_.HintAnswer as HintAnswer2_,
user2_.ActiveInactive as ActiveI11_2_, user2_.WebAddress as WebAddress2_,
user2_.PasswordCounter as Passwor13_2_
from ALERT_LOG alertlog0_
left outer join KIOSK device1_ on alertlog0_.Kiosk_Id=device1_.Id
left outer join [USER] user2_ on alertlog0_.UserAck=user2_.Id, KIOSK_GROUP devicegrou3_
where device1_.KIOSK_GROUP_Id=devicegrou3_.Id
and ((alertlog0_.Date>? )
and(alertlog0_.Date<? )
and(device1_.CUSTOMER_Id=? ))
order by devicegrou3_.Name ASC
Code:
Login
FAQ
Search