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

All responses (8)

Answers (0)

Martin Dráb 230,214 Most Valuable Professional on at

Like (0)

Report

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

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.
Suggested answer

Waed Ayyad 6,332 Super User 2024 Season 2 on at

Like (1)

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

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
Layan Jwei 7,347 Super User 2024 Season 2 on at

Like (1)

Report

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

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
Ch Motla 27 on at

Like (0)

Report

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

Mr.Marting getting this. after your code run.
Ch Motla 27 on at

Like (0)

Report

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

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.
Ch Motla 27 on at

Like (0)

Report

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

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.
Martin Dráb 230,214 Most Valuable Professional on at

Like (0)

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

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());
Layan Jwei 7,347 Super User 2024 Season 2 on at

Like (1)

Report

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

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

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

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

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

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates