Hi,
I'm using NHibernate with Fluent and am pretty new to it. I have
gotton some basic stuff working, but am now trying to update a Mode
and Mappings to join a table.
Basically, I'm trying to get this logic to work with NHibernate
ICriteria .... is there a way?
-------
select efs.EFS_PRODUCT_CODE, efs.efs_status_code,
efs.efs_file_create_date, trunc(nvl(efs.efs_file_create_date,
sysdate)) theDateUsed, sysdate-ddp.DDP_DAYS_ON_LINE diffDate,
efs.EFS_FILE_ID, efs.EFS_FILE_NAME, efs.EFS_ACCOUNT_ID,
ddp.DDP_DAYS_ON_LINE, ddp.DDP_PROCESS_TYPE
>From Echo_File_status efs,
Data_DELETION_PARAMETER ddp
WHERE
efs.EFS_PRODUCT_CODE='TPS'
AND
efs.EFS_PRODUCT_CODE = DDP.DDP_PRODUCT_CODE(+)
AND
DDP.DDP_PROCESS_TYPE = 'D'
AND
( ( trunc(nvl(efs.efs_file_create_date, sysdate)) > sysdate -
ddp.DDP_DAYS_ON_LINE ) or ( efs.efs_status_code != 'ACKED' ) )
order by efs.efs_product_code,
decode(efs.efs_status_code, 'READY', 1, 'TRANS', 2 ,
'FAERR', 3, 'FCERR', 4, 'PRERR', 5, 'TRERR', 6, 'PREP', 7, 'PRCOM', 8,
'FCREA', 9 , 'TRCOM', 10, 'ACKED', 11, 1),
efs.efs_file_create_date DESC
-------
I've updated my Model & Mappings based on the example I found here:
http://stackoverflow.com/questions/1241005/how-to-join-table-in-fluent-nhibernate
.
But, I'm receiving a :Could Not Execute Query message. NOT for the
logic above, but a very simple query that seems to have been created
properly and was running prior to my joining the tables. When I cut &
paste the NHibernate generated query into Toad it runs fine. [SQL:
SELECT count(*) as y0_ FROM ECHO.ECHO_FILE_STATUS this_ inner join
DATA_DELETION_PARAMETER this_1_ on
this_.EFS_PRODUCT_CODE=this_1_.DDP_PRODUCT_CODE]
Mappings & Models below.
Thanks.
Kim
-----------------------
Here are the Mappings:
public FitsEchoFileStatusMap()
{
Schema("ECHO");
Table("ECHO_FILE_STATUS");
Id(o => o.ProductCode, "EFS_PRODUCT_CODE");
Map(o => o.RowId, "RowID");
Map(o => o.FileId, "EFS_FILE_ID");
Map(o => o.AccountId, "EFS_ACCOUNT_ID").Nullable();
Map(o => o.AckedBy, "EFS_ACKED_BY").Nullable();
Map(o => o.AckedDate, "EFS_ACKED_DATE").Nullable();
Map(o => o.BalanceFile, "EFS_BALANCE_FILE_YN");
Map(o => o.BankId, "EFS_BANK_ID");
Map(o => o.BatchCount, "EFS_BATCH_COUNT").Nullable();
Map(o => o.ConfirmDate, "EFS_CONFIRM_DATE").Nullable();
Map(o => o.ConfirmNumber,
"EFS_CONFIRM_NUMBER").Nullable();
Map(o => o.ConfirmUser, "EFS_CONFIRM_USER").Nullable();
Map(o => o.CreditAmt, "EFS_CREDIT_AMT").Nullable();
Map(o => o.CreditCount, "EFS_CREDIT_COUNT").Nullable();
Map(o => o.DebitAmt,
"EFS_DEBIT_AMT").Nullable();
Map(o => o.DebitCount, "EFS_DEBIT_COUNT").Nullable();
Map(o => o.HashTotal, "EFS_HASH_TOTAL").Nullable();
Map(o => o.FileAckDate, "EFS_FILE_ACK_DATE").Nullable();
Map(o => o.FileCreateDate,
"EFS_FILE_CREATE_DATE").Nullable();
Map(o => o.FileName, "EFS_FILE_NAME").Nullable();
Map(o => o.FileModId, "EFS_FILE_MOD_ID").Nullable();
Map(o => o.FileTransCompDate,
"EFS_FILE_TRANS_COMP_DATE").Nullable();
Map(o => o.FileTransmitDate,
"EFS_FILE_TRANSMIT_DATE").Nullable();
Map(o => o.FileType, "EFS_FILE_TYPE");
Map(o => o.ModifiedBy, "EFS_MODIFIED_BY");
Map(o => o.ModifiedDate, "EFS_MODIFIED_DATE");
Map(o => o.ModifiedSource, "EFS_MODIFIED_SOURCE");
Map(o => o.PayxCreditAmt,
"EFS_PAYX_CREDIT_AMT").Nullable();
Map(o => o.PayxDebitAmt, "EFS_PAYX_DEBIT_AMT").Nullable();
//Map(o => o.ProductCode, "EFS_PRODUCT_CODE");
Map(o => o.StatusCode, "EFS_STATUS_CODE");
Join("DATA_DELETION_PARAMETER", m =>
{
m.Fetch.Join();
m.KeyColumn("DDP_PRODUCT_CODE");
m.Map(t => t.DaysOnLine);
});
}
public FitsEchoDataDeletionMap()
{
Schema("ECHO");
Table("DATA_DELETION_PARAMETER");
Id(o => o.RowId);
Map(o => o.DaysOnLine, "DDP_DAYS_ON_LINE");
Map(o => o.ErrorMessage, "DDP_ERROR_MESSAGE").Nullable();
Map(o => o.FieldName, "DDP_FIELD_NAME");
Map(o => o.FileIdPrefix, "DDP_FILE_ID_PREFIX").Nullable();
Map(o => o.ModifiedBy, "DDP_MODIFIED_BY");
Map(o => o.ModifiedDate, "DDP_MODIFIED_DATE");
Map(o => o.ModifiedSource, "DDP_MODIFIED_SOURCE");
Map(o => o.ProcessOrder, "DDP_PROCESS_ORDER");
Map(o => o.ProcessType, "DDP_PROCESS_TYPE");
Map(o => o.Process_YN, "DDP_PROCESS_YN");
Map(o => o.DdpProductCode, "DDP_PRODUCT_CODE");
Map(o => o.TableAbbrev, "DDP_TABLE_ABBREV");
Map(o => o.TableName, "DDP_TABLE_NAME");
}
-----------------------
Here are the Models:
[DataContract]
public class FitsEchoFileStatusModel : BaseBll
{
[DataMember]
public virtual int AccountId { get; set; }
[DataMember]
public virtual string AckedBy { get; set; }
[DataMember]
public virtual DateTime? AckedDate { get; set; }
[DataMember]
public virtual string BalanceFile { get; set; }
[DataMember]
public virtual string BankId { get; set; }
[DataMember]
public virtual int BatchCount { get; set; }
[DataMember]
public virtual DateTime? ConfirmDate { get; set; }
[DataMember]
public virtual string ConfirmNumber { get; set; }
[DataMember]
public virtual string ConfirmUser { get; set; }
[DataMember]
public virtual decimal CreditAmt { get; set; }
[DataMember]
public virtual int CreditCount { get; set; }
[DataMember]
public virtual decimal DebitAmt { get; set; }
[DataMember]
public virtual int DebitCount { get; set; }
[DataMember]
public virtual DateTime? FileAckDate { get; set; }
[DataMember]
public virtual DateTime? FileCreateDate { get; set; }
[DataMember]
public virtual int FileId { get; set; }
[DataMember]
public virtual string FileModId { get; set; }
[DataMember]
public virtual string FileName { get; set; }
[DataMember]
public virtual DateTime? FileTransCompDate { get; set; }
[DataMember]
public virtual DateTime? FileTransmitDate { get; set; }
[DataMember]
public virtual string FileType { get; set; }
[DataMember]
public virtual int HashTotal { get; set; }
[DataMember]
public virtual string ModifiedBy { get; set; }
[DataMember]
public virtual DateTime? ModifiedDate { get; set; }
[DataMember]
public virtual string ModifiedSource { get; set; }
[DataMember]
public virtual decimal PayxCreditAmt { get; set; }
[DataMember]
public virtual decimal PayxDebitAmt { get; set; }
[DataMember]
public virtual string ProductCode { get; set; }
[DataMember]
public virtual string RowId { get; set; }
[DataMember]
public virtual string StatusCode { get; set; }
#region Referenced fields from FitsEchoDataDeletionModel
public virtual decimal DaysOnLine { get; set; }
public virtual string DdpProductCode { get; set; }
#endregion
}
[DataContract]
public class FitsEchoDataDeletionModel : BaseBll
{
[DataMember]
public virtual string RowId { get; set; }
[DataMember]
public virtual decimal DaysOnLine { get; set; }
[DataMember]
public virtual string ErrorMessage { get; set; }
[DataMember]
public virtual string FieldName { get; set; }
[DataMember]
public virtual string FileIdPrefix { get; set; }
[DataMember]
public virtual string ModifiedBy { get; set; }
[DataMember]
public virtual DateTime? ModifiedDate { get; set; }
[DataMember]
public virtual string ModifiedSource { get; set; }
[DataMember]
public virtual int ProcessOrder { get; set; }
[DataMember]
public virtual string ProcessType { get; set; }
[DataMember]
public virtual string Process_YN { get; set; }
[DataMember]
public virtual string DdpProductCode { get; set; }
[DataMember]
public virtual string TableAbbrev { get; set; }
[DataMember]
public virtual string TableName { get; set; }
}
