Jiwa

Posted: **Thu May 20, 2021 5:51 pm**

Hi, I have the following code to create a record in DB_CustomSettingValues but keep 0 rows effected? Dr#3289 doesn't have a Custom record 'AlarmCode' ? Not sure where I am going wrong as I have used this code before with minor changes. Is it to do with constraints?? Any help appreciated.

Code: Select all: /****************** INSERT CustomSettingValues rec where one doesn't exist for a Specific CustomSetting ******/ DECLARE @SettingName VARCHAR(255) SET @SettingName = 'AlarmCode' INSERT INTO DB_CustomSettingValues SELECT NEWID(), '1AFE3222-E804-47C9-8EA1-39B04283C085', DB_Main.DebtorID, DB_Cust_DH1.AlarmCode,'20 May 2021' FROM DB_Main Left JOIN DB_Cust_DH1 ON DB_Main.AccountNo = DB_Cust_DH1.AccountNo WHERE DB_Main.DebtorID NOT IN (SELECT DB_Main.DebtorID FROM DB_CustomSettingValues WHERE SettingID = (SELECT TOP 1 SettingID FROM DB_CustomSetting WHERE SettingName = @SettingName)) AND DB_Main.TradingStatus = 1 AND DB_Main.AccountNo = '3289'

Posted: **Fri May 21, 2021 11:03 am**

Code: Select all: DECLARE @SettingName VARCHAR(255) SET @SettingName = 'AlarmCode' INSERT INTO DB_CustomSettingValues SELECT NEWID(), '1AFE3222-E804-47C9-8EA1-39B04283C085', DB_Main.DebtorID, DB_Cust_DH1.AlarmCode,'20 May 2021' FROM DB_Cust_DH1 INNER JOIN dbo.DB_Main ON DB_Cust_DH1.AccountNo = dbo.DB_Main.AccountNo LEFT OUTER JOIN dbo.DB_CustomSetting INNER JOIN dbo.DB_CustomSettingValues ON dbo.DB_CustomSetting.SettingID = dbo.DB_CustomSettingValues.SettingID AND dbo.DB_CustomSetting.SettingName = @SettingName ON dbo.DB_Main.DebtorID = dbo.DB_CustomSettingValues.DebtorID WHERE (dbo.DB_CustomSetting.SettingID IS NULL) AND (dbo.DB_Main.TradingStatus = 1)

Posted: **Fri May 21, 2021 11:55 am**

Thanks Perry. Much appreciated!

Jiwa

V175 Create DB_customSettingValue recs

V175 Create DB_customSettingValue recs

Re: V175 Create DB_customSettingValue recs

Re: V175 Create DB_customSettingValue recs