Same Query different results in Database and AX client.

(0) Share

Report

Posted on by Community Member

We are trying to get paid checks from BankChequeTable with null cleared date from BankAccountTrans Table.

When we run the below Sql query in database its returning some 3000 records.

select C.CHEQUENUM, C.CHEQUESTATUS , C.TRANSDATE , C.ACCOUNTID, C.AMOUNTCUR , C.BANKNEGINSTRECIPIENTNAME , C.VOUCHER,

C.PROCESSEDBYPOSITIVEPAY, C.CREATEDDATETIME, C.MODIFIEDDATETIME,T.CLEAREDDATE

from dbo.BANKCHEQUETABLE C LEFT JOIN dbo.BANKACCOUNTTRANS T ON C.ACCOUNTID = T.ACCOUNTID AND c.CHEQUENUM = T.CHEQUENUM

WHERE

C.CHEQUESTATUS = 4 and t.CLEAREDDATE is null

When we run below similar AX query in its returning more than 100k records.

select count(ChequeNum) from chequeTable
where chequeTable.ChequeStatus == ChequeStatus::Payment
outer join cleareddate from bankAccountTrans
where bankAccountTrans.ChequeNum == chequeTable.ChequeNum
&& bankAccountTrans.AccountId == chequeTable.AccountID
&& bankAccountTrans.ClearedDate == dateNull();

3000 records returned by SQL query are the results we are expecting.

What could be the possible reason for returning different number of records. Am I missing something here?

Please suggest.

Thanks

Siva

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Sort by

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi, it looks like you're using an outer join in the bottom query. The top query is using a left join, which returns all the rows from the select, and then joins only the rows from the second table if they match on the key. An outer join would return all the rows from both the tables.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Kanoa,

I was using outerjoin in Ax query since its considered as left join in AX.

Thanks

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Is the field ClearedDate is custom as I couldn't find that field in my system(AX2012 R3).

I just executed your both queries and could see its returning same data i.e count of records. So, I feel its better to execute AX query, loop through the records and see if its returning any bad data that is causing this record count mismatch.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

I did not know that..

Maybe try moving the line:

&& bankAccountTrans.ClearedDate == dateNull();

up under the first select?

I believe in the top query you're selecting from:

dbo.BANKCHEQUETABLE

where C.CHEQUESTATUS = 4 and t.CLEAREDDATE is null - Then joining on the left.

versus the second:

select count(ChequeNum) from chequeTable

where chequeTable.ChequeStatus == ChequeStatus::Payment - Then joining on the left ???

Was this reply helpful? Yes No
Suggested answer

Joris dG 17,780 on at

Like (0)

Report
Copy link

Link copied!

Check the output of dateNull(). I don't think it enters NULL in the database but rather a really low date. So, ==dateNull() is not the same as "is null" in SQL, I think.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thank you all for your input.

@sukrut - I tried your suggestion still returning same results.

@Chaitanya, Joris - while looping through the query with ClearedDate == datenull(), I have noticed that its fetching the records which has cleared date. so is there any way I can get records with blank value (minimum date) in cleared date field.

Thanks

Siva

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Siva, you need to use a range because it seems that on AX you have a date (for example) 01/01/2017 which should be 01/01/2017 12:00 on SQL, but if SQL for some reason stored it as 01/01/2017 12:01 then the query for date == 01\01\2017 wont found it. So the "solution" could be to use a range from 31.12.2016 to 02.01.2016 or to normalize all your dates on SQL to have the time part equal to 12:00

Was this reply helpful? Yes No
Suggested answer

Mahmoud Hakim 17,887 on at

Like (0)

Report
Copy link

Link copied!

can you filter on dataareaid in sql query ?

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Copy link

Link copied!
Hi,

Can you try the following instead of DateNull.

select count(ChequeNum) from chequeTable where chequeTable.ChequeStatus == ChequeStatus::Cancelled outer join bankAccountTrans where bankAccountTrans.ChequeNum == chequeTable.ChequeNum && bankAccountTrans.AccountId == chequeTable.AccountID // && bankAccountTrans.ClearedDate == dateNull(); && bankAccountTrans.ClearedDate == mkdate(1,1,1900);

Was this reply helpful? Yes No