web
You’re offline. This is a read only version of the page.
close
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 / Microsoft Dynamics AX (Archived) / SQL query in X++ help ...
Microsoft Dynamics AX (Archived)

SQL query in X++ help required

(0) ShareShare
ReportReport
Posted on by Dave W 380

I have the following query that i am running fine in SQL Server

Select * from Inventtransorigin ito
join  inventtrans it on it.INVENTTRANSORIGIN = ito.recid
where it.DATEPHYSICAL = '' and it.DATEINVENT = '' and ito.REFERENCEID = '' and ito.REFERENCECATEGORY = '150';


In X++ i'm running

static void SUPJob(Args _args)
{
    Inventtransorigin ito;
    InventTrans it;
    ;
    
    Select * from ito
    join it where it.INVENTTRANSORIGIN == ito.recid &&
    it.DATEPHYSICAL == "" && it.DATEINVENT == "" && ito.REFERENCEID == "" &&  ito.REFERENCECATEGORY == "150";
}


Unfortunately i get the error 'operand types are not compatible with the operator'

Can anyone recommend the best method to run this query?

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Jonathan Halland Profile Picture
    Jonathan Halland 11,310 on at

    Hi. Your query should be as follows

    Inventtransorigin ito;

       InventTrans it;

       Select ito   join it where it.inventTransOrigin == ito.recid &&   it.DATEPHYSICAL == dateNull() && it.DATEINVENT == dateNull() && ito.REFERENCEID == "" &&    ito.REFERENCECATEGORY == InventTransType::Statement;

  • Suggested answer
    Jonathan Halland Profile Picture
    Jonathan Halland 11,310 on at

    Notes:

    1. You can't use * in X++

    2. You don't need to use "from", unless you are trying to restrict returned fields

    3. Using "" will not return all records that are empty, it will return everything.

    4. Using a string e.g. "" for a date field will not compile. You either need to specify a proper date or a null date.

  • Dave W Profile Picture
    Dave W 380 on at

    Many thanks Jonathan, working how i want it now.

  • Suggested answer
    Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    Well you are using the wrong datatype for 3 of your fields, i quickly changed your code to run, so perhaps you can take it from there.

    static void SUPJob(Args _args)
{
    Inventtransorigin inventtransorigin;
    InventTrans       inventTrans;
    ;
    
    Select * from inventtransorigin
    join inventTrans
    where inventTrans.INVENTTRANSORIGIN == inventtransorigin.recid 
    &&    inventTrans.DATEPHYSICAL == datenull()
    &&    inventTrans.DATEINVENT == datenull()
    &&    inventtransorigin.REFERENCEID == "" 
    &&    inventtransorigin.REFERENCECATEGORY == InventTransType::Asset;
}



  • Dave W Profile Picture
    Dave W 380 on at

    Hi Rudi,

    Thanks for your input, regarding pasting when i posted the question the editor gives a button on the taskbar (bottom line far right, looks bit like a pencil) Basically lets you paste code in and then makes it look good.

    For replies just click the 'Use Rich Text' link to open up the options.

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    Yes thanks Dave, i just found it and edited my post :-D

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans