Multiple Selection In Using Select in Multiple Tables

Finance and Operations/AX Developer Forum
1

static void HelpMe(Args _args)

{

InventTrans _inventTrans;

InventDim _inventDim;

;

while select * from _inventTrans

join _inventDim

group by _inventTrans.ItemId ,_inventDim.InventColorID, _inventDim.InventSizeId,_inventDim.configId

where _inventDim.inventDimId == _inventTrans.inventDimId

{

print _inventTrans.ItemId + _inventDim.InventColorId + _inventDim.InventSizeId + _inventDim.configId;

Pause;

}

}

Working perfectly…:slight_smile:

but

but below statement is giving error…

static void HelpMe(Args _args)

{

InventTrans _inventTrans;

InventDim _inventDim;

;

while select ItemId,_inventTrans.InventColorId,_inventTrans.InventSizeId,_inventTrans.ConfigId from _inventTrans

join _inventDim

group by _inventTrans.ItemId ,_inventDim.InventColorID, _inventDim.InventSizeId,_inventDim.configId

where _inventDim.inventDimId == _inventTrans.inventDimId

{

print _inventTrans.ItemId + _inventDim.InventColorId + _inventDim.InventSizeId + _inventDim.configId;

Pause;

}

}

2

Your query is syntactically wrong - InventColorId, InventSizeId and ConfigId fields are defined in InventDim table and they have to be placed appropriately in the select statement:

while select ItemId from _inventTrans
    **join InventColorId, InventSizeId, ConfigId from _inventDim**
        group by _inventTrans.ItemId ,_inventDim.InventColorID, _inventDim.InventSizeId,_inventDim.configId
        where _inventDim.inventDimId == _inventTrans.inventDimId
{    …    }
3

thanks Sir,

4

Hi martin,

I am running into a similar problem with joins.

while select PersonnelNumber, RecId, Person from hcmWorker

join Worker, RecId, LegalEntity from hcmEmployment

where hcmWorker.RecId == hcmEmployment.Worker

//join hcmPersonPrivateDetails

//where hcmPersonPrivateDetails.Person == hcmWorker.Person

//join hcmPersonDetails

//where hcmPersonDetails.Person == hcmWorker.Person

// join Worker, SeniorityDate, OfficeLocation from hcmWorkerTitle

where hcmWorkerTitle.Worker == hcmWorker.RecId

join hcmEmploymentDetail

where hcmEmployment.RecId == hcmEmploymentDetail.Employment

&& hcmEmploymentDetail.WorkerStartDate >=_fromDateUtc

&& hcmEmploymentDetail.WorkerStartDate <_toDateUtc

{

When I run this query as it is - it works fine, but when I try to remove the ‘//’ it does not even enter here. What I am missing?

}

5

It may be related to invalid or missing data, but just want to make sure that the query is correct.

6

nath, it sounds to me like something unrelated to the problem discussed in this thread.

If I understand your problem, the query works, it just returns no data, because one of those joined tables has no related record.