SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
USE [RC_APP] GO /****** Object: StoredProcedure [dbo].[spLGD_Main] Script Date: 01/07/2024 14:33:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spLGD_Main] -- Add the parameters for the stored procedure here @D_STARTDATE varchar(6), @D_ENDDATE varchar(6), @T_JOB nvarchar(100), @ID_EXECJOB INT, @C_STEP INT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- SET XACT_ABORT ON for error handling SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRY /*** STEP 0: Declare parameteres ***/ DECLARE @T_STEPNAME nvarchar(4000), @T_STEPDESC nvarchar(4000), --ALTERADO DE VARCHAR(8000) @C_STATUS INT, @T_STATUS nvarchar(100), @ID_LGDSCENARIO INT, @T_LGDSCENARIODESC nvarchar(100), @ID_LGDTREE INT, @ID_LGDTREEPARMTREE INT, @C_SEGMAPPROACH INT, @C_EXRAPPROACH INT, @F_WOFFLOSSEVENT BIT, @C_REESTRUCTUREDAPPROACH INT, @ID_LGDEXCLUSIONSCENARIO INT, @D_MINREFERENCEDATE varchar(6), @D_MAXREFERENCEDATE varchar(6), @T_INPUTTBL nvarchar(MAX) = 'RC_APP..Account', @C_OUTTBL INT = 0, @C_OTHSEGM INT = 0, @F_LINKREESACCOUNTS BIT = 0, @F_INCLUDEFIRSTMOMENTDEF BIT, @F_NETEADCALCULATION BIT DECLARE @SP_NAME nvarchar(255) = (SELECT DB_NAME() + '..' + OBJECT_NAME(@@PROCID)) EXEC RC_APP..spMain @SP_NAME /*** STEP 1: Get active configuration ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Configuration' SET @T_STEPDESC = 'Getting process active configuration.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_GetActiveConfig SET @F_LINKREESACCOUNTS = ISNULL((SELECT MAX(CASE WHEN F_LinkReestructuredAccounts = 1 THEN 1 ELSE 0 END) FROM LGD_ConfigScenarios), 0) SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 2: Create accounts input ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Accounts Input' SET @T_STEPDESC = 'Creating accounts data input.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CreateInputAccounts @D_STARTDATE, @D_ENDDATE -- '201101', '201408' SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 3: Create workouts input ***/ IF @F_LINKREESACCOUNTS = 1 BEGIN SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Workouts Input' SET @T_STEPDESC = 'Creating workouts data input.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CreateInputWorkouts @D_STARTDATE, @D_ENDDATE -- '201101', '201408' SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP END /*** STEP 4: Create segmentation input ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Segmentation Input' SET @T_STEPDESC = 'Creating segmentation data input.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spTreeSegm_RunSegm @T_INPUTTBL, @C_OUTTBL, @D_STARTDATE, @D_ENDDATE, @C_OTHSEGM -- 'RC_APP..TreeSegm_InputAccount', 0, '201101', '201408', 0 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5: Iterate through scenarios ***/ IF CURSOR_STATUS('global','CursorScenarios')>=-1 BEGIN CLOSE CursorScenarios DEALLOCATE CursorScenarios END DECLARE CursorScenarios CURSOR FAST_FORWARD FOR SELECT DISTINCT ID_LGDScenario, T_LGDScenarioDescription, ID_LGDTree, ID_LGDTreeParmTree, C_SegmApproach, C_ExRApproach, F_WOffLossEvent, C_ReestructuredApproach, ID_LGDExclusionScenario, F_LinkReestructuredAccounts, F_IncludeFirstMomentDefault, F_NetEADCalculation FROM RC_APP..LGD_ConfigScenarios OPEN CursorScenarios FETCH NEXT FROM CursorScenarios INTO @ID_LGDSCENARIO, @T_LGDSCENARIODESC, @ID_LGDTREE, @ID_LGDTREEPARMTREE, @C_SEGMAPPROACH, @C_EXRAPPROACH, @F_WOFFLOSSEVENT, @C_REESTRUCTUREDAPPROACH, @ID_LGDEXCLUSIONSCENARIO, @F_LINKREESACCOUNTS, @F_INCLUDEFIRSTMOMENTDEF, @F_NETEADCALCULATION WHILE @@FETCH_STATUS=0 BEGIN /*** STEP 5.1: Create scenario input ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Scenario Input' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Creating LGD scenario data input.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CreateInputScenario @ID_LGDSCENARIO, @ID_LGDTREE, @ID_LGDTREEPARMTREE, @C_SEGMAPPROACH, @ID_LGDEXCLUSIONSCENARIO, @F_INCLUDEFIRSTMOMENTDEF -- 7, 1049, 1048, 0 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5.2: Identify LGD Strategies ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - LGD Strategies' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Identifying LGD strategies.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_IdentifyStrategies @D_STARTDATE, @D_ENDDATE, @C_EXRAPPROACH, @F_WOFFLOSSEVENT, @C_REESTRUCTUREDAPPROACH -- '201101', '201408', 0, 1, 0 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP IF @F_LINKREESACCOUNTS = 1 BEGIN /*** STEP 5.3: Create scenario input for workouts ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Workouts Scenario Input' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Creating LGD scenario data input for workouts.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CreateInputScenarioWorkouts @ID_LGDSCENARIO, @ID_LGDTREE, @ID_LGDTREEPARMTREE, @C_SEGMAPPROACH, @ID_LGDEXCLUSIONSCENARIO -- 7, 1049, 1048, 0 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5.4: Identify LGD Strategies for workouts ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Workouts LGD Strategies' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Identifying LGD strategies for workouts.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_IdentifyStrategiesWorkouts @D_STARTDATE, @D_ENDDATE, @C_EXRAPPROACH, @F_WOFFLOSSEVENT, @C_REESTRUCTUREDAPPROACH -- '201101', '201408', 0, 1, 0 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP END /*** STEP 5.5: Calculate Recovery Flows ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Recovery Flows' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Calculating recovery flows.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CalcRecoveryFlows @F_WOFFLOSSEVENT SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP IF @F_LINKREESACCOUNTS = 1 BEGIN /*** STEP 5.6: Calculate Recovery Flows for workouts ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Workouts Recovery Flows' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Calculating recovery flows for workouts.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CalcRecoveryFlowsWorkouts @F_WOFFLOSSEVENT SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5.7: Calculate Losses for workouts ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Workouts Losses' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Calculating LGD losses for workouts.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CalcLossesWorkouts @ID_LGDTREE, @ID_LGDTREEPARMTREE, @F_NETEADCALCULATION -- 1049, 1048 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP END ELSE BEGIN TRUNCATE TABLE LGD_RecoveryFlowsWorkouts TRUNCATE TABLE LGD_RecoveryFlowsByTimeProfileWorkouts END /*** STEP 5.8: Calculate Probs and Losses ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Probs and Losses' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Calculating LGD probabilities and losses.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CalcProbsAndLosses @ID_LGDTREE, @ID_LGDTREEPARMTREE, @F_NETEADCALCULATION -- 1049, 1048 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5.9: Calculate Collateral Indexes Before Repossession ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Idxs to Repossession' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Calculating collateral indexes before repossession.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CalcRepossessionIdxs @ID_LGDTREE, @ID_LGDTREEPARMTREE -- 1049, 1048 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5.10: Store Outputs ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Output Results' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Storing LGD output results.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_StoreOutputResults @ID_LGDSCENARIO, @ID_LGDTREE, @ID_LGDTREEPARMTREE, @D_ENDDATE -- 7, 1049, 1048, '201408' SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP FETCH NEXT FROM CursorScenarios INTO @ID_LGDSCENARIO, @T_LGDSCENARIODESC, @ID_LGDTREE, @ID_LGDTREEPARMTREE, @C_SEGMAPPROACH, @C_EXRAPPROACH, @F_WOFFLOSSEVENT, @C_REESTRUCTUREDAPPROACH, @ID_LGDEXCLUSIONSCENARIO, @F_LINKREESACCOUNTS, @F_INCLUDEFIRSTMOMENTDEF, @F_NETEADCALCULATION END IF CURSOR_STATUS('global','CursorScenarios')>=-1 BEGIN CLOSE CursorScenarios DEALLOCATE CursorScenarios END END TRY BEGIN CATCH DECLARE @ID_LOG INT, @ErrorMessage nvarchar(4000), @ErrorSeverity INT, @ErrorState INT SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END GO
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear