Hi, I am calling a stored procedure that selects with a join and returns columns from both tables:

CREATE OR REPLACE FUNCTION FIND_STAGE_TWO_AUDIT_STATIONS


RETURN SYS_REFCURSOR
AS
st_cursor SYS_REFCURSOR;



BEGIN



OPEN st_cursor FOR

select



shl.STATION,
shl.AREA_MGR_CODE,
shl.ZONE,
shl.REGION,
shl.STATION_DESC,
shl.STATION_MGR_NAME,
shl.STATION_MGR_LID,
shl.STATION_STATUS,
shl.AREA_MANAGER_NAME,
shl.AREA_MGR_LID,
shl.ZONE_DESC,
shl.ZONE_MGR_NAME,
shl.ZONE_MGR_LID,
shl.REGION_NAME,
shl.REGION_MGR_NAME,
shl.REGION_MGR_LID,
shl.WH_UPDATE_DAT,
shl.STATE,
shl.STATION_P,
shl.STATION_N,
AM.AUDIT_ID as AUDIT_ID,
AM.STOP_DATE as STOP_DATE,
AM.Status_TYPE_ID as Status_TYPE_ID

from Cpdw_Stat_Hier_Local shl
join RTFA.AUDIT_MAIN AM on AM.STATION_NUMBER = shl.station
where shl.station = '1052'

RETURN st_cursor;
END FIND_STAGE_TWO_AUDIT_STATIONS;
/

So all shl.* columns get mapped accordingly but the AM.* columns do not.


Here is my call from the mapping file:

<sql-query name="findStageTwoAuditStations" callable="true">
<return alias="emp" class="CpdwStatHierLocal">
<return-property name="station" column="STATION"/>
<return-property name="areaMgrCode" column="AREA_MGR_CODE"/>
<return-property name="zone" column="ZONE"/>
<return-property name="region" column="REGION"/>
<return-property name="stationDesc" column="STATION_DESC"/>
<return-property name="stationMgrName" column="STATION_MGR_NAME"/>
<return-property name="stationMgrLid" column="STATION_MGR_LID"/>
<return-property name="stationStatus" column="STATION_STATUS" />
<return-property name="areaManagerName" column="AREA_MANAGER_NAME"/>
<return-property name="areaMgrLid" column="AREA_MGR_LID"/>
<return-property name="zoneDesc" column="ZONE_DESC"/>
<return-property name="zoneMgrName" column="ZONE_MGR_NAME"/>
<return-property name="zoneMgrLid" column="ZONE_MGR_LID"/>
<return-property name="regionName" column="REGION_NAME"/>
<return-property name="regionMgrName" column="REGION_MGR_NAME"/>
<return-property name="regionMgrLid" column="REGION_MGR_LID"/>
<return-property name="whUpdateDat" column="WH_UPDATE_DAT"/>
<return-property name="state" column="STATE"/>
<return-property name="stationP" column="STATION_P"/>
<return-property name="stationN" column="STATION_N"/>
<return-property name="auditId" column="AUDIT_ID"/>
<return-property name="stopDate" column="STOP_DATE"/>
<return-property name="statusTypeId" column="STATUS_TYPE_ID"/>
</return>
{ ? = call find_Stage_Two_Audit_Stations() }

</sql-query>


now, I did not put the last 3 attribute as <property> mappers in the mapping file because they are from another table. I do have setters and getters for them though.
I get no errors but I do get nulls for the last 3 fields.
Can someone put some eyes on this and tell me what a Moron I am (has to be correct answer before you call me Moron)?
:)
Thanks,
B

UPDATE:
If I take any of the 3 return-properties (auditId,statusTypeId, or stopDate) and create
<property name="auditId" unique="true" insert="false" update="false" type="long" column="STATION_N"/>
It works! the strange part is I just picked any column in the table to point this attribute to. This will look like a HUGE hack if someone comes in behind me to update this mapping so is there a better way to map the property that explains to the developers it is simply a place holder? something like NO-COLUMN="true" would be nice...
Thanks for reading the thread!
B