Home RAP
New Blog Posts: Merging Reports - Part 1 and Part 2

Field SQL Alias

edited April 2008 in RAP
Is there any way to change the Field SQL Alias?

I added a calculated field:

MIN(EntityID)

the Field SQL Alias it created was:

MIN_Entity_ID

Our program expects a field named EntityID so it is failing.

Thanks,
Rob

Comments

  • edited April 2008
    Hi Rob,

    The Field SQL Alias is assigned by ReportBuilder to ensure a unique name is
    given to each field. This value is essentially only used by the database,
    ReportBuilder uses the FieldAlias property to define and show each field.

    What exactly are you trying to do? Are you accessing the SQL generated by
    ReportBuilder? The only way to access the TdaField.SQLFieldName property
    from RAP is with a passthru function.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited April 2008
    We have a screen where you can pick which buildings to include in the
    report, and select how to order the report, etc.

    These different selections can be added to the report by the user.

    So for example the order by has:
    lSQL.AddOrderByField(lSQL.GetFieldForAlias('EntityID'),true);

    The entityID is the building or lot or parking space, etc. So if the select
    the radio button to order by building we run that line of code.


    For a more complicated example of what we do:
    'ds.FieldByName('CriteriaTable').AsString' will resolve to either TenantID,
    OwnerID or EntityID

    s := 'SELECT * from tblReportConditionMatch WHERE ReportID = ' +
    InttoStr(GetReportID())+' And ConditionType=0';
    ds := CreateDataSet(dmMain.MainConnection, s);
    ds.First;
    if ds.Eof then GetSQLObject(rptMain, lSQL);

    while not ds.Eof do
    begin
    // to gettenantlist getownerlist getentitylist
    GetSQLObjectByViewName(rptMain,ds.FieldByName('CriteriaTable').AsString,
    lSQL)
    ds.Next;
    end;
    ds.Close;


    Also some of our Just in time fields expect certain fields to be present.
    For example this field groups by building and in order to do this it needs
    the field EntityID

    if(aFieldName = 'MINOneGroupAddress') then
    begin
    GetSQLObject(rptMain, lSQL);
    if lSQL=nil then exit;
    //lTable:=lSQL.GetTableForSQLAlias(tblName);
    lFld:=lSQL.GetFieldForAlias('MIN(OneGroup)');
    if lFld=nil then exit;

    Result :=
    DisplayADOStoredProcAddressData(JIT_Entity_Address,q,lFld.SQLFieldName,'EntityID');
    end


  • edited April 2008
    Hi Rob,

    The TdaSQL object is a very complex and sometimes confusing item to use
    (even for us). This is why a few years back we decided to create the
    TdaSQLBuilder class which makes using the SQL object much easier and
    straight forward. For instance I noticed you are using the AddOrderByField
    routine to add a new OrderBy field (seems logical) however the proper way to
    do this would be to use the SelectOrderByField routine. This can be done
    very easily using the SQLBuilder with the following code.

    lSQLBuilder.OrderByFields.Add('MyTableNameOrAlias', 'MyFieldNameOrAlias');

    Take a look at the TdaSQLBuilder topic in the ReportBuilder help for
    extensive documentation of the object and how to use it with code examples.
    I cannot recommend strongly enough that you use this rather than try to
    access the TdaSQL object directly as you are doing below.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
This discussion has been closed.