SQL statement to Modify Items and comments

(0) Share

Report

Posted on by Community Member

Hi All,

I have a SQL Query to show the Production BOM Lines where there is a item with a Quantity per less then 1. With this list of parts I would like to do two things. Set a field called 'Blocked' to true. Secondly I would like to create a comment in the comment table for that item with the comment field saying 'Item Blocked'.

How can I achieve this is SQL?

My query to find the lines

select DISTINCT [Production BOM No_], [Version Code] from dbo.[TIOGA$Production BOM Line]
where [Unit of Measure Code] = 'EACH'
and [Quantity per] < 1
AND [Quantity per] != 0
AND [Version Code] = ''

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (0)

Report

First of all why are you doing that using SQL. It is not recommeded to update using SQL Statement.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I could write this in CAL very easily, I'm just trying to improve SQL Skills.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I've wrote this, but I'm getting the following error

Msg 515, Level 16, State 2, Line 35
Cannot insert the value NULL into column 'Line No_', table 'LH_RESTORE_20171211.dbo.TIOGA$Comment Line'; column does not allow nulls. INSERT fails.

DECLARE @ItemNo VARCHAR(50)
DECLARE @Comment VARCHAR(250)
DECLARE @LineNo INT;
-- Set the variables
SET @Comment = 'Unit of Measure parts must be reviewed'

-- Select the Items which need amending
DECLARE db_Cursor CURSOR FOR
SELECT [No_]
from dbo.[TIOGA$Production BOM Line] L
where [Unit of Measure Code] = 'EACH'
and [Quantity per] < 1
AND [Quantity per] != 0
AND [Version Code] = ''

OPEN db_Cursor
FETCH NEXT FROM db_Cursor INTO @ItemNo

WHILE @@FETCH_STATUS = 0
BEGIN

-- Get the last Line No.
select @LineNo = max([Line No_]) from dbo.[TIOGA$Comment Line] C
where [No_] = @ItemNo
and [Table Name] = 3

SET @LineNo = @LineNo + 10000;

-- Set Blocked on the Item
update dbo.[TIOGA$Item]
set [Blocked] = 1
where [No_] = @ItemNo

-- Insert the comment line
INSERT INTO dbo.[TIOGA$Comment Line] ([Table Name],[No_],[Line No_],[Date],
[Code],[Comment],[Comment Type],[Indentation],[Complete])
VALUES (3,@ItemNo,@LineNo,'20/12/2017','',@Comment,0,0,0)

FETCH NEXT FROM db_cursor INTO @ItemNo
END
CLOSE db_Cursor
DEALLOCATE db_cursor

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi does anyone have a solution for this?

Was this reply helpful? Yes No
Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (1)

Report

To improve your SQL Skills you are doing it in SQL ?

Was this reply helpful? Yes No
Suggested answer

Alex A 2,857 on at

Like (0)

Report

Hello,

If you can do this within the C/AL language and the safety of the NAV Development Environment, then by all means you should do it that way. Those of us who know T-SQL/DML only use it as a last resort to fix problems when it cannot be done functionally or when it isn't possible to do it through the NAV Development Environment - usually only at the recommendation of Microsoft.

It's fine if you're writing reports (read only), but when it comes to inserting records, why not use RapidStart to accomplish this?

Was this reply helpful? Yes No