You’re offline. This is a read only version of the page.
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
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?
To improve your SQL Skills you are doing it in SQL ?
Hi does anyone have a solution for this?
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
I could write this in CAL very easily, I'm just trying to improve SQL Skills.
First of all why are you doing that using SQL. It is not recommeded to update using SQL Statement.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,219
Super User 2024 Season 2
#2
Martin Dráb
230,056
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (