This list is for discussion of the development of the NH code itself. For user issues, please direct your questions to the (more) appropriate list at http://groups.google.com/group/nhusers.
Steve Bohlen [email protected] http://blog.unhandled-exceptions.com http://twitter.com/sbohlen On Thu, Dec 22, 2011 at 10:35 AM, kimbered <[email protected]> wrote: > 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; } > > } >
