SQL statement to Modify Items and comments

(0) Share

Report

Posted on by Community Member Microsoft Employee

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

All responses (6)

Answers (0)

Suggested answer

Alex A 2,348 on at

Like (0)

Report

RE: SQL statement to Modify Items and comments

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?
Suresh Kulla 43,745 on at

Like (1)

Report

RE: SQL statement to Modify Items and comments

To improve your SQL Skills you are doing it in SQL ?
Community Member Microsoft Employee on at

Like (0)

Report

RE: SQL statement to Modify Items and comments

Hi does anyone have a solution for this?
Community Member Microsoft Employee on at

Like (0)

Report

RE: SQL statement to Modify Items and comments

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: SQL statement to Modify Items and comments

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

Suresh Kulla 43,745 on at

Like (0)

Report

RE: SQL statement to Modify Items and comments

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