First only in AOT query Object

(0) Share

Report

Posted on by Karunakaran nainar

452

HI All,

I trying to fetch firt only in AOT query object , but my view that has the query has all the records,Also want to know if firstonly and sort can be used to fetch only the last record.

thanks

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Community Member on at

Like (0)

Report

RE: First only in AOT query Object

Hi,

You may instantiate de query object in your code, set the property selectfirstonly to 'true'.

If you want to fetch only the last record you could set the ordering descending using the method addorderbyfield (sortorder::descending).

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 236,394 Most Valuable Professional on at

Like (0)

Report

RE: First only in AOT query Object

Query datasources have FirstOnly property.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: First only in AOT query Object

That's correct, i skipped that, thanks.

Was this reply helpful? Yes No
Karunakaran nainar 452 on at

Like (0)

Report

RE: First only in AOT query Object

i had already set the first only proprty to yes , just to get it to work i tried with a single datasource , even then my view that has this query shows all values of the table.

is it possible to override any query methods to instantiate the query and add through code

Was this reply helpful? Yes No
Sohaib Cheema 48,887 User Group Leader on at

Like (0)

Report

RE: First only in AOT query Object

if there are more than one datasources check joins, joins can effect number of records.

fix your join types in that case

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

RE: First only in AOT query Object

Hi,

Do you mean instatiate the query object through x++ code?, if that's the case:

query = new query(querystr("yourquery")):

querybuilddatasource = query.datasourcetable or datasourceno or ....(yourdatasource);

querybuilddatasource.firstonly(true);

//add another datasource into your query object

newquerybuilddatasource = querybuilddatasource.adddatasource(tablenum(yourtable));

//to fetch the last record

querybuilddatasource.addorderbyfield(fieldnum(yourtable, yourfield),sortOrde::descending);

if not, then ignore the message

Was this reply helpful? Yes No
Karunakaran nainar 452 on at

Like (0)

Report

RE: First only in AOT query Object

Was this reply helpful? Yes No
Martin Dráb 236,394 Most Valuable Professional on at

Like (0)

Report

RE: First only in AOT query Object

You're trying to debug two things at once and then you don't know which one is wrong.

First test your query. If it doesn't work, trying to use in a view is a waste of time. Fix your query first.

If it works, there is no point discussing it and trying to fix it - it's fine and the problem must be somewhere else. Maybe views don't respect the property. Open the view definition in SSMS and compare it with the SQL statement of the query. If it doesn't respect the property, you'll see that the view removed the TOP 1 statement.

In either case, you'll know which part is wrong and where you have to turn your attention.

Was this reply helpful? Yes No
Karunakaran nainar 452 on at

Like (0)

Report
RE: First only in AOT query Object

You are right Martin

the view is not considering the first only property,

SELECT POSITIONID,PARTITION,RECID FROM dbo.HCMPosition AS T1

on the other hand i tried the query in a job and got only the first record.IS there any other way of populating a view with the conditions.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 236,394 Most Valuable Professional on at

Like (0)

Report

RE: First only in AOT query Object

It doesn't seem to be supported. But you still have exactly the same options as if it was a table with several records. For example, you can use the view in a query with FirstOnly = Yes.

If you think you need it in views as well, please log a feature request on Connect and explain it there.

Was this reply helpful? Yes No