Query within Query

Finance and Operations/AX Technical Forum
1

Hello,

I have query like this

select * from table1,table2 where table1.id == table2.id && table1.noofamt >= (select Noofamt from table3 where table3.id == table1.id group by id)

How to write this query in Dynamcis Ax using querybuilddatasource?

Thanks in Advance

Nimisha Patel

2

Hi,

This will not be the exact equivalent, but basing on what You wrote, if should be something like:

select * from table1 join table2 where table2.id == table1.id exists join table3 where table1.id == table3.id && table1.noofamt >= table3.noofamt.

As for creating a query, I’m not sure if that’s possible (somebody correct me if I’m wrong!) because of the last part where it would be necessary to create a range based on other table field’s values. If You want to use this query for a form or a report, consider creating a temporary table and filling it using the x++ statement I wrote earlier. That will surely be easier and possibly also faster,

Regards,

Lukens

3

Also, similar posts should be created in the developer forum, not the technical one.

Lukens

4

I hope it is not an x++ statement

In a single x++ select statement you cannot select multiple tables.

And what exactly your select statement will do.

5

Thanks ,

This is my sql server query like "select * from table1,table2 where table1.id == table2.id && table1.noofamt >= (select Noofamt from table3 where table3.id == table1.id group by id) "

and I write this query in dynamics Ax like this,

q = new Query();
qbds = q.addDataSource(tablenum(Table1), ‘Table1’);

q1 = new Query();

qbds3 = q1.addDataSource(tablenum(Table3), ‘Table3’);
qbds3.addSelectionField(fieldnum(Table3, amount), SelectionField::Sum);
qbds3.addRange(fieldnum(Table3, Id)).
value(strfmt(’(%1 == %2.%3)’, fieldstr(Table3, Id),
q.dataSourceTable(tableNum(Table1)).name(),
fieldstr(Table1, Id) ));

qbds1=qbds.addDataSource(tablenum(Table2),‘Table2’);
qbl = qbds1.addLink(fieldnum(Table1, Id), fieldnum(Table2,id));

qbds1.addRange(fieldnum(Table2, Amount)).
value(strfmt(’(%1 > “%2”)’, fieldstr(Table2,Amount), q1.dataSourceName(“Table3”).toString()));

which step I missing? plz suggest me.

Thanks in Advance

Nimisha Patel

6

Hi,

Did You try to make a “where” statement in the instruction qdbs3.addrange(…) by creating a range? That won’t work this way, You should do it by addLink method. Also, the last instruction with adding the range will not work the way You expect it to, You have to define specific values in the range.

Doesn’t creating a temporary table out of a normal while select … loop satisfy You? I think that will really be a better option, right now I think You are just running around in circles, trying to force the query to behave the way You want it to, which will not work.

7

Thanks,

ya that i know that the sub queries are works in SqlServer but the Important thing is sub queries are how to work in Dyanamic ax…did u know any syntax or any examples of that will very help ful for me…we can easly manage the main query but i need to know about Query within query…i was tried but i didnt get the any result…so if u have possible than send me as soon as possible …

NImisha Patel

8

I’m trying to do something similar. I’m trying to retrieve the dimension descriptions for a journal. I am building a view and since Dynamics doesn’t support subselects I am trying to create computed columns to bring back the values that would normally be subselected. The SQL looks like this but not sure how to code the subselects as computed columns.

Select

year(ledg.transdate) Fiscal_Year,

month(ledg.transdate) Accounting_Period,

(Select acct.name

From mainaccount acct,

dimensionattributelevelvalue dalv

Where dalv.dimensionattributevaluegroup = davg.recid

And acct.MainAccountID = dalv.displayvalue

And dalv.ordinal = 1) AccountName,

(Select dpty.name

From omoperatingunit oper,

dirpartytable dpty,

dimensionattributelevelvalue dalv

Where dalv.dimensionattributevaluegroup = davg.recid

And dalv.ordinal =2

And oper.omoperatingunitnumber = dalv.displayvalue

And oper.omoperatingunittype =1

And dpty.recid = oper.recid) DeptName

From

ledgerjournaltrans ledg

CROSS JOINdimensionattributevaluegroupcombination dvgc

CROSS JOINdimensionattributevaluegroup davg

WHERE dvgc.dimensionattributevaluecombination = ledg.ledgerdimension

And davg.recid = dvgc.dimensionattributevaluegroup

And Not (Select acct.mainaccountid

From mainaccount acct,dimensionattributelevelvalue dalv

Where dalv.dimensionattributevaluegroup = davg.recid

And acct.MainAccountID = dalv.displayvalue

And dalv.ordinal = 1) is null