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) / insert_recordset with ...
Microsoft Dynamics AX (Archived)

insert_recordset with select statement containing outer join throws error

(0) ShareShare
ReportReport
Posted on by Andy Adamak 4,074

AX 2012 R2 CU7

I was trying to update the CustAging report with some extra data.  One of the joins I add to the insertCustAgingReportTmp method on the CustAgingReportDP class has to be an outer join, but when I run the report I get a "Cannot insert NULL into field" error:

Cannot insert the value NULL into column [column], table 'tempdb.dbo.t10601_D40915EB7D084EFABAF016E580EE7CFA'; column does not allow nulls. INSERT fails.

I know AX usually doesn't deal with NULL values, but since this is a tmp table in sql that I'm both selecting from and inserting into... perhaps this outer join is grabbing NULL in SQL as apposed to 'blank' in AX?  

Anyone know more about insert - select - outer join statements?

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Thats currently just a limitation on the insert_recordset. The only way I have been able to get around this is to do an insert_recordset without the left join, and then doing an update_recordset to set the data afterwards.

    Not the most ideal situation. I'd love to see the insert_recordsets handle the nulls like the rest of AX does.

  • Andy Adamak Profile Picture
    Andy Adamak 4,074 on at

    This is exactly what I ended up doing yesterday.  Instead of trying to do it all on the insert, just ran an update after.  Seems silly, hopefully the insert gets fixed somewhere down the line.

  • Douglas Noel Profile Picture
    Douglas Noel 3,905 on at

    Hey Andy,

    maybe you can try the following if you want to skip the required updates:

    1st: create a query view (with oter joins) which represents your select (which you want to insert)

    2nd: to overcome the NULLS, create a calculatedCoumn on the view (for each field you want to insert and which comes from an outer joined table - so it can be NULL) using the SQL IsNull(field,replcvement) which always contains a NONNULL value (0,0.0,'')

    3rd: use this view from within insert recordset but only use the calculated fields from the outer joined tables

    maybe this will work (I haven't checked that)

    regards

    Douglas

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