Select statement on fields

Finance and Operations/AX Developer Forum
1

Hi All,

I would write a select like this at sql server:

select name, (select age from agetable where name=people.name) as age from people

I couldn’t write select like that with Axapta. Is it possible with axapta or not?

I tried that with no success:

select name, (select age from agetable where name=people.name).age from people

2

You definitely don’t need subqueries (which aren’t directly supported by AX), just use a join. This topic is covered in Development II, for instance.

Example:

select Name from people
    join Age from ageTable
    where ageTable.name == people.name;
info(strFmt("%1: %2", people.Name, ageTable.Age));
3

You can write the select statement as follows:

Select fiedname from table join fieldname2 from table 2 where table1.relatedfield == table2.relatedfield;

Note : The two tables must be related with one field.

Now the fields as per your query will be selected.

Now, you assisgn the selected values as

controlname.text = selectedfield.value;

Meanwhile, the control you want to assign value should be set to " AutoDeclaration = Yes" in the property Sheet…

4

Actually I have three joins in my select command and there are more than one rows at my table which I want to add with subquery.

It seem I have to use group by clause for my select. I know distinct doesn’t supported too with Axapta… :slight_smile:

5

What are the three tables you are using and the rows you want to join