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 GP (Archived) / Need a SQL Script crea...
Microsoft Dynamics GP (Archived)

Need a SQL Script created - Multi-Part Identifier not bound

(0) ShareShare
ReportReport
Posted on by Bill Campbell 12

I am trying to write a script that will update the value of the Safety Stock and Order Upto Quantity in the Item Resource Planning table.

I know the basics of what I want, however, I can not get the syntax figured out - I think  am close, but the error in SQL is Multi-part Identifier can not be bound keeps coming up.

set the value of IV00102.Field 1 = IV00102_Bup.Field 2

set the value of IV00102.Field 3 = IV00102_Bup.Field 4

Fields will get updated if -

IV00102.Locncode = 'blank'

IV00102_Bup.Locncode = 'EDM'

IV00102.itemnmbr = IV00102_bup.itemnmbr

IV00102_bup is a copy of the IV00102 table.

Each of the inventory items in the IV00102 table will have many line items, as there are many location codes, for each location code there is a complete line of data.

I am only interested in the values when the location code (locncode) in IV00102 is blank and the value in the IV00102_bup - for the same ITEMNMBR - is equal to "EDM"

I know, pretty simple for all you SQL brains, but, I am missing one small thing.

Help

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at
    Hi Bill, Please check the below query, UPDATE IV00102 SET IV00102.Field_1 = IV00102_Bup.Field_2,IV00102.Field_3 = IV00102_Bup.Field_4 FROM (SELECT * FROM IV00102_Bup WHERE IV00102_Bup.LOCNCODE = 'EDM') IV00102_Bup1 INNER JOIN IV00102 on IV00102.ITEMNMBR = IV00102_Bup1.ITEMNMBR AND IV00102.LOCNCODE = '' Hope this query meets your requirement. Regards, Santosh G
  • Verified answer
    Bill Campbell Profile Picture
    Bill Campbell 12 on at

    THis is finally what we came up with to make this matter work.

    ****

    declare G_cursor CURSOR for select ITEMNMBR from IV00101

    set nocount on

    OPEN G_cursor

    FETCH NEXT FROM G_cursor INTO @Item

    WHILE (@@FETCH_STATUS <> -1)

    begin

    update IV00102

    set ORDRUPTOLVL = CASE WHEN (select ORDRPNTQTY - FXDORDRQTY from IV00102 where LOCNCODE = 'EDM' and ITEMNMBR = @Item) IS NULL then 0.00 else (select ORDRPNTQTY - FXDORDRQTY from IV00102 where LOCNCODE = 'EDM'  and ITEMNMBR = @Item) end,

    SFTYSTCKQTY = CASE WHEN (select FXDORDRQTY from IV00102 where LOCNCODE = 'EDM' and ITEMNMBR = @Item) IS NULL then 0.00 else (select FXDORDRQTY from IV00102 where LOCNCODE = 'EDM' and ITEMNMBR = @Item) end

    where LOCNCODE = '' and ITEMNMBR = @Item

    FETCH NEXT FROM G_cursor INTO @Item

    END

    CLOSE G_cursor

    DEALLOCATE G_cursor

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 GP (Archived)

#1
Community Member Profile Picture

Community Member 2

#2
mtabor Profile Picture

mtabor 1

#2
Victoria Yudin Profile Picture

Victoria Yudin 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans