Finance | Project Operations, Human Resources, ...

SQL Query returning data but in x++ it is not returning data

(1) Share

Report

Posted on by Ch Motla

Hi Technical folks

Help me out to get rid of below.

in Sql. query is returning data but in x++ not showing any data. what can be the cause?

below are the details

In X++

select Notes from docuRef
order by docuRef.createddatetime desc
where docuRef.RefTableId == tableNum(SalesTable)
where docuRef.RefRecId == salesTable.RecId
&& docuRef.ActualCompanyId == curext();
exists join docuType
&& where docuType.TypeId == docuRef.TypeId
&& docuType.TypeGroup == DocuTypeGroup::Note;

In SQL

select top 1 notes from docuref
inner join salestable on SALESTABLE.RECID=DOCUREF.REFRECID
inner join DOCUTYPE on DOCUTYPE.TYPEID=DOCUREF.TYPEID
where DocuType.TypeGroup=0-- like 'Note'
and docuref.ACTUALCOMPANYID like 'companyName'
--where DOCUREF.REFTABLEID=
order by DOCUREF.CREATEDDATETIME desc

I have the same question (0)

All responses (8)

Answers (0)

Layan Jwei 8,112 Super User 2025 Season 2 on at

Like (1)

Report

Hi Ch,

In x++, how do u define salesTable, because you used it but you didn't show us how salesTable is defined.

Also are u sure the x++ code is running in a dataArea that has data and not "DAT"

In general there are four differences in your SQL and x++

1. Join to salesTable -- but yes maybe you don't need it if you have the recId already

2. Exist join in SQL while you are using inner join in x++

3. Like company in SQL instead of equal

4. You use refTableId in x++ but u don't use it in SQL

Thanks,

Layan Jweihan

Was this reply helpful? Yes No
Martin Dráb 237,884 Most Valuable Professional on at

Like (0)

Report
This can't be the X++ you executed, because it won't even compile.

Also, the queries are quite different, therefore it's completely possible that they return different results. Most importantly, your SQL code ignores DataAreaId and Partition and doesn't filter sales orders.

The following code will give you actual SQL code of your X++ code (after I fixed the bugs):

select generateOnly forceLiterals Notes from docuRef order by docuRef.CreatedDateTime desc where docuRef.RefTableId == tableNum(SalesTable) && docuRef.RefRecId == salesTable.RecId && docuRef.ActualCompanyId == curext() exists join docuType where docuType.TypeId == docuRef.TypeId && docuType.TypeGroup == DocuTypeGroup::Note; info(docuRef.getSQLStatement());

Was this reply helpful? Yes No
Ch Motla 27 on at

Like (0)

Report

Thank you Mr. Martin

when i run the visual studio. the world wide web publishing services shutdown.

secondly even if ignores the joins it is still not populating the records. but actually the records are there.

Was this reply helpful? Yes No
Ch Motla 27 on at

Like (0)

Report

Layan Jwei

if ignor or comment out the join or where clauses. the simple select statement is also not fetching the records but actually the records exist there.

Was this reply helpful? Yes No
Ch Motla 27 on at

Like (0)

Report

Mr.Marting getting this. after your code run.

Was this reply helpful? Yes No
Layan Jwei 8,112 Super User 2025 Season 2 on at

Like (1)

Report

Hi Ch,

As i said earlier, are you sure you are running in a dataArea that has some data and not "DAT"?
when you debugged, what is the dataArea value? if you are running a runnable class, make sure you define the dataArea in the project properties.

Thanks,
Layan Jweihan

Was this reply helpful? Yes No
Suggested answer

Waed Ayyad 9,039 Super User 2025 Season 2 on at

Like (1)

Report
Hello @Ch Motla,

Try this code and tell me if it is helped you.

select Notes from docuRef order by docuRef.createddatetime desc where docuRef.RefTableId == tableNum(SalesTable) && docuRef.RefRecId == salesTable.RecId && docuRef.ActualCompanyId == curext() exists join docuType where docuType.TypeId == docuRef.TypeId && docuType.dataAreaId == docuRef.ActualCompanyId && docuType.TypeGroup == DocuTypeGroup::Note;

Thanks

Waed Ayyad

Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future

Was this reply helpful? Yes No
Martin Dráb 237,884 Most Valuable Professional on at

Like (0)

Report

Unfortunately your screenshot doesn't show the return value of getSQLStatement() (which is added to infolog). You only showed us that docuRef record is empty, which we already know, because the query uses generateOnly keyword and therefore it doesn't fetch any data.

Note that I do have a pretty good idea what SQL code is generated from your X++ code. But you don't seem to, therefore this information (and the knowledge how to obtain it) will likely help you.

Was this reply helpful? Yes No