Field SQL Alias

rbuser

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

nicocizik

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

rbuser

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

nicocizik

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