Select statment not returning data even the data exists

Finance and Operations/AX Developer Forum
1

There is a simple select statement in AX 2009 inside a find method of MarkupTrans table, it always working fine except for some records even though the data exists inside that MarkupTrans table.

Thru SQL statement I am able to see the data,

Using a X++ job if I call that method by passing the same parameters its giving me the data.

I wonder if any one had the same issue with a simple select statement inside the DAX not returning any data even though the data exists.

Find method :

static MarkupTrans findFreightRec(MarkupTransRefTableId vMarkupTransRefTableId,
MarkupTransRefRecId vMarkupTransRefRecId,
Noyes vfreightFlag = NoYes::Yes,
boolean update = false)
{
MarkupTrans markupTrans;
;

markupTrans.selectForUpdate(update);

if (vMarkupTransRefTableId && vMarkupTransRefRecId)
{
select firstonly markupTrans
index hint TableRecIdIdx
where markupTrans.TransTableId == vMarkupTransRefTableId
&& markupTrans.TransRecId == vMarkupTransRefRecId
&& markuptrans.SLlineNum == 1
&& markupTrans.FreightFlag == vfreightFlag;
}

return markupTrans;
}

Thanks in advance.

2

First of all, make sure that you’re looking into the same company.

If it’s correct, please give as X++ select statement with concrete values (because your code above doesn’t show them) and your SQL code, so we can compare them.

3

Hi Martin,

Thanks for your quick reply,

X++ Job I am using to call the same method and it worked (but the same not working from that find method don’t know why)

static void Job103(Args _args)
{
MarkupTrans tMarkupTrans;
TableId tableId = 366;
RecId refREcId = 5641609509;

;

info(int642str(freightCalculation::findFreightRec(366,5641609509,NoYes::Yes,true).RecId));

select forupdate firstonly tMarkupTrans
index hint TableRecIdIdx
where tMarkupTrans.TransTableId == tableId
&& tMarkupTrans.TransRecId == refRecId
&& tMarkupTrans.SLlineNum == 1
&& tMarkupTrans.FreightFlag == NoYes::Yes;

info(int642str(tMarkupTrans.RecId));

}

SQL Statement :

select transrecid,recid,VALUE,FREIGHTFLAG,SLlineNum, * from MarkupTrans mt where
mt.dataareaid = ‘151’
and mt.transtableid = 366
and mt.TRANSRECID = 5641609509
and mt.FREIGHTFLAG = 1
and mt.SLLINENUM = 1

Just an additional info :

I tried flush all the data and AOD, Usage data and restarted AOS too but no luck.

I did not tried clearing sql cache.

4

I’m sorry, but it doesn’t make sense. If the code and the context was the same, it would have to work the same. I can only assume that there is a problem not shown in the code above or the problem is somewhere in debugging.

If I got stuck in this situation, I would log queries directly in SQL Server, to see how the actual query differs from the expected query.

5

Can you please tell me how to log queris directly in SQL.

Thanks