Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / SQL Query returning da...
Finance | Project Operations, Human Resources, ...
Suggested answer

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

(1) ShareShare
ReportReport
Posted on by Ch Motla 27
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
  • Martin Dráb Profile Picture
    Martin Dráb 231,925 Most Valuable Professional on at
    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 Profile Picture
    Waed Ayyad 7,897 Super User 2025 Season 1 on at
    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 Profile Picture
    Layan Jwei 7,725 Super User 2025 Season 1 on at
    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 Profile Picture
    Ch Motla 27 on at
    SQL Query returning data but in x++ it is not returning data
    Mr.Marting getting this. after your code run.
  • Ch Motla Profile Picture
    Ch Motla 27 on at
    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 Profile Picture
    Ch Motla 27 on at
    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 Profile Picture
    Martin Dráb 231,925 Most Valuable Professional on at
    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 Profile Picture
    Layan Jwei 7,725 Super User 2025 Season 1 on at
    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,245 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,925 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans