FillWithDASql return error

Data Abstract
1

Hi,
I’m using the following statement that returns an error.

MONoSQL := ‘Select MONo From (Select Top ’ + inttostr(500) + ’ MONo From tMOM Order By EditTime Desc ) M’;
ClientDataModule.RemoteDataAdapter.FillWithDASql(msqry_MONo, MONoSQL, nil);

image

2

From the error message, it looks like the order by in the nested query is not supported.

Try rewriting it to:
MONoSQL := ‘Select MONo From (Select Top ’ + inttostr(500) + ’ MONo, EditTime From tMOM) M Order By EditTime Desc ’;

3

Hi,

Your suggestion doesn’t match with my expected result. I need to get the result by EditTime first. Then using the result to doing the sorting by MONo. My workaround solution is using the following statement and then set the index properties in DAMemDataTable.

4

Hi,

why you can’t use simple statement w/o nested query like

MONoSQL := 'Select Top ' + inttostr(500) + ' MONo From tMOM Order By EditTime Desc';

?

5

Hi,
I explained the reason. Your simple statement is not my expected result. I need to find top 500 records that are latest records and then sort the MONo. Simple statement only do the first part without sorting by MONo. It needs to using index properties in DAMemDataTable and then fulfill my expected result.

6

Hi,

I can recommend to add Order By EditTime Desc into table declaration inside Schema Modeller.

after this, required sorting will be applied automatically

I mean, tMOM table in Schema Modeller should be like

SELECT
   ....
FROM tMOM
WHERE {WHERE}
ORDER BY EditTime Desc

your DA SQL will be

Select M.MONo From (Select Top 500 MONo From tMOM) M
7

Hi,

Should I “Add New Statement” in tMOM’s Schema Statements? Because my DA SQL is using for LookUPComboBox only. But tMOM is a transactions history for user enquiry.

8

Hi,

you can add a new table into schema like tMOM_dasql.
this table will be designed for dasql only

9

Noted with thanks.

1 Like