1. Gbenga Owolabi
  2. SnapObjects
  3. Tuesday, 21 April 2020 12:58 PM

I have been on this for days.

Please help with the snapobject v.2.0 usage below.

How do i make snapobject to run the multi-table sql query defined in DwSelectAttribute on the D_Rpt_Mm_Tbills poco class


public List<D_Rpt_Mm_Tbills> Test(params object[] parameters)
{
var test = _context.SqlModelMapper.Load<D_Rpt_Mm_Tbills>(parameters).ToList();
 return mapper;
}

D_Rpt_Mm_Tbills class
#region DwSelectAttribute
[DwSelect("SELECT I.transid, \r\n "
+"I.initiating_id, \r\n "
+"CASE WHEN C.CLASS_TYPE = 'TB' then '' Else I.description end description , \r\n "
+"C.CLASS_TYPE, \r\n "
+"T.DESCRIPTION, \r\n "
+"I.face_value, \r\n "
+"f.face_value initial_face_value, \r\n "
+"I.COST, \r\n "
+"I.value_date, \r\n "
+"I.maturity_date, \r\n "
+"I.accrued_interest, \r\n "
+"I.unearned_income, \r\n "
+"I.CURRENT_PRICE, \r\n "
+"I.current_value, \r\n "
+"I.instrument_id, \r\n "
+"I.interest_rate, \r\n "
+"I.relevant_yield yield, \r\n "
+"ACCRUED_CHARGES, \r\n "
+"s.scheme_name, \r\n "
+"I.currency_code, \r\n "
+"I.current_rate, \r\n "
+"I.report_date, \r\n "
+"I.reference_no, \r\n "
+"Datediff(dd,I.value_date,I.maturity_date) tenor, \r\n "
+"case when I.REPORT_DATE > I.maturity_date then 0 else Datediff(dd,I.REPORT_DATE , I.maturity_date) - (CASE WHEN I.MATURITY_DATE > I.REPORT_DATE Then 1 Else 0 END) end tenor_left, \r\n "
+"Datediff(dd,I.value_date, CASE WHEN I.REPORT_DATE > I.maturity_date then I.maturity_date Else I.REPORT_DATE END) + \r\n "
+"+ (CASE WHEN I.MATURITY_DATE > I.REPORT_DATE Then 1 Else 0 END) tenor_lapsed, \r\n "
+"(I.CURRENT_VALUE/DATA.SUM_TOTAL) * I.RELEVANT_YIELD way, \r\n "
+"(I.CURRENT_VALUE/DATA.SUM_TOTAL) * I.INTEREST_RATE w_rate, \r\n "
+"(I.CURRENT_VALUE/DATA.SUM_TOTAL) * (Datediff(dd,I.value_date,I.maturity_date)) w_tenor, \r\n "
+"(I.CURRENT_VALUE/DATA.SUM_TOTAL) * (case when I.REPORT_DATE > I.maturity_date then 0 else Datediff(dd,I.REPORT_DATE , I.maturity_date) - (CASE WHEN I.MATURITY_DATE > I.REPORT_DATE Then 1 Else 0 END) end) w_tenorleft \r\n "
+"FROM investment_reports I inner join schemes s on I.scheme_id = s.scheme_id \r\n "
+"inner join fixed_income f on I.initiating_id = f.transid \r\n "
+"inner join (SELECT SCHEME_ID,INSTRUMENT_ID,maturity_date,SUM(CURRENT_VALUE) SUM_TOTAL \r\n "
+"FROM INVESTMENT_REPORTS R \r\n "
+"WHERE SCHEME_ID in (:schemeid) \r\n "
+"and INSTRUMENT_ID = :instrumentid \r\n "
+"and REPORT_DATE = :thisday \r\n "
+"GROUP BY SCHEME_ID,INSTRUMENT_ID,maturity_date) DATA on I.scheme_id = DATA.SCHEME_ID and \r\n "
+"I.instrument_id = DATA.instrument_id and \r\n "
+"I.maturity_date = data.maturity_date \r\n "
+"inner join FINANCIAL_INSTRUMENT T on I.INSTRUMENT_ID = T.TEMPLATE_ID \r\n "
+"inner join ASSET_CLASS C on T.ASSET_CLASS = C.CLASS_ID \r\n "
+"WHERE ( I.scheme_id = :schemeid) and \r\n "
+"(I.instrument_id = :instrumentid ) and \r\n "
+"(I.report_date = :thisday) and \r\n "
+"(I.trans_type = 'M')")]
#endregion
[DwParameter("schemeid", typeof(int))]
[DwParameter("instrumentid", typeof(string))]
[DwParameter("thisday", typeof(DateTime?))]
[DwSort("scheme_name A maturity_date A")]
[DwGroupBy(1, "scheme_name")]
[DwGroupBy(2, "scheme_name", "maturity_date")]
public class D_Rpt_Mm_Tbills
{
[Identity]
[DwColumn("transid")]
public int TransId { get; set; }

[DwColumn("initiating_id")]
public int? Initiating_Id { get; set; }

[DwColumn("description")]
public string Description { get; set; }

[DwColumn("class_type")]
public string Class_Type { get; set; }

[DwColumn("description")]
public string Financial_Instrument_Description { get; set; }

[DwColumn("face_value")]
public decimal? Face_Value { get; set; }

[DwColumn("initial_face_value")]
public decimal? Initial_Face_Value { get; set; }

[DwColumn("cost")]
public decimal? Cost { get; set; }

[DwColumn("value_date")]
public DateTime? Value_Date { get; set; }

[DwColumn("maturity_date")]
public DateTime? Maturity_Date { get; set; }

[DwColumn("accrued_interest")]
public decimal? Accrued_Interest { get; set; }

[DwColumn("unearned_income")]
public decimal? Unearned_Income { get; set; }

[DwColumn("current_price")]
public decimal? Current_Price { get; set; }

[DwColumn("current_value")]
public decimal? Current_Value { get; set; }

[DwColumn("instrument_id")]
public string Instrument_Id { get; set; }

[DwColumn("interest_rate")]
public decimal? Interest_Rate { get; set; }

[DwColumn("yield")]
public decimal? Yield { get; set; }

[DwColumn("accrued_charges")]
public decimal? Accrued_Charges { get; set; }

[DwColumn("scheme_name")]
public string Scheme_Name { get; set; }

[DwColumn("currency_code")]
public string Currency_Code { get; set; }

[DwColumn("current_rate")]
public decimal? Current_Rate { get; set; }

[DwColumn("report_date")]
public DateTime? Report_Date { get; set; }

[DwColumn("reference_no")]
public string Reference_No { get; set; }

[DwColumn("tenor")]
public int? Tenor { get; set; }

[DwColumn("tenor_left")]
public int? Tenor_Left { get; set; }

[DwColumn("tenor_lapsed")]
public int? Tenor_Lapsed { get; set; }

[DwColumn("way")]
public decimal? Way { get; set; }

[DwColumn("w_rate")]
public decimal? W_Rate { get; set; }

[DwColumn("w_tenor")]
public decimal? W_Tenor { get; set; }

[DwColumn("w_tenorleft")]
public decimal? W_Tenorleft { get; set; }
}
Accepted Answer
Logan Liu @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 22 April 2020 03:47 AM
  2. SnapObjects
  3. # Permalink
0
Votes
Undo

Hi Gbenga,

.NET DataStore and SqlModelMapper (SnaoObjects) belong to different products and they are using different series of attributes on their models.

.NET DataStore model uses DwSelectAttribute to specify the SQL query converted from DataWindow, but SqlModelMapper model requires more attributes to build the SQL query.

For a single table, you can modify a .NET DataStore model and use it with SqlModelMapper easily. But it's not easy to modify a multi-table SQL query from .NET DataStore model to SqlModelMapper model attributes.

So please report a .NET DataStore enhancement requirement in the Appeon Standard Support at https://www.appeon.com/standardsupport/. 

Regards,

Logan

 

Comment
0

Votes

Thank you for the vital information. It has guided me to further search and focus on documentation here. https://docs.appeon.com/appeon_online_help/snapobjects/Type-Safe_Queries_Using_SnapObjects_API/index.html#querying-one-value



There are quite a number of attributes. I cannot see how to create complex query using SnapObject API.



How do i create complex features like but not limited to:



1. Subquery



2. Union statement



3. Case when statement



4. Certain sql functions like datediff . etc



These are the invaluable tools in dataobjects that make life simple for database business developers.
  1. Gbenga Owolabi
  2. Wednesday, 22 April 2020 20:21 PM
I finally found it. SqlRawBuilder works for me as i have full control over my select query.

Thank you
  1. Gbenga Owolabi
  2. Thursday, 23 April 2020 12:37 PM
Thanks for sharing the solution!
  1. Armeen Mazda @Appeon
  2. Thursday, 23 April 2020 14:22 PM
There are no comments made yet.
Gbenga Owolabi Accepted Answer Pending Moderation
  1. Wednesday, 22 April 2020 12:53 PM
  2. SnapObjects
  3. # 1
0
Votes
Undo

Thank you for the vital information. It has guided me to further search and focus on documentation here. https://docs.appeon.com/appeon_online_help/snapobjects/Type-Safe_Queries_Using_SnapObjects_API/index.html#querying-one-value

There are quite a number of attributes. I cannot see how to create complex query using SnapObject API. 

How do i create complex features like but not limited to:

1. Subquery

2. Union statement

3. Case when statement

4. Certain sql functions like datediff . etc

These are the invaluable tools in dataobjects that make life simple for database business developers.

Comment
Hi Gbenga,



You can get more details about all supported attributes with SqlModelMapper at:

https://docs.appeon.com/appeon_online_help/snapobjects2.0/api_reference/SnapObjects.Data/ModelAttribute/ModelAttribute.html



For you questions, you can find suitable attributes there:

1. "Subquery"

Refer to SubQueryAttribute.

2. "Union statement "

Refer to SqlUnionAttribute.

3. "Case when statement"

Refer to SqlComputeAttribute.

4. "Certain sql functions like datediff . etc"

Refer to SqlComputeAttribute.



Regards, Logan
  1. Logan Liu @Appeon
  2. Thursday, 23 April 2020 08:18 AM
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.