CODEWITHSUNDEEP
sqlif-statementsubquerycase
Jamie Tilma
Jamie Tilma

Reputation: 69

IF/CASE statement within SELECT subquery

My script works almost perfectly, however the below part is the only part which is causing me problems. This part of the code works perfectly when a record exists in the FSFSR table however if no rows exists I want the result to be 1 rather than the MAX value from the SCFSR table +1 but everything I have tried doesn't seem to work.

(SELECT MAX(SCFSR.FSR_Num) FROM SCFSR WHERE SCFSR.FSR_Call_Num = T_Call_Num)+1

Full code below:

BEGIN TRY
BEGIN TRANSACTION
USE Tesseracttestv5

--------- DELCARE ARRAY ---------

DECLARE @RANGE TABLE(T_Call_Num INT);

INSERT @RANGE(T_Call_Num) VALUES (441925),(429021),(393852);

--------- SET CALL TO COMP ---------

UPDATE SCCall
SET
Call_Status = 'COMP',
Call_CDate = GETDATE()
WHERE Call_Num IN (SELECT T_Call_Num FROM @RANGE)

--------- INSERT SERVICE REPORT ---------

INSERT INTO SCFSR (FSR_Call_Num,    FSR_Call_Status,    FSR_Start_Date,     FSR_Complete_Date,  FSR_Last_Update, 
                   FSR_Symp_Code,   FSR_Fault_Code,     FSR_Rep_Code,       FSR_Solution,       FSR_User, 
                   FSR_Num,         FSR_Cost_Centre,    FSR_Site_Num,       FSR_Area_Code,      FSR_Employ_Num,
                   FSR_Prod_Num)

SELECT      T_Call_Num, 
            'COMP', GETDATE(), GETDATE(), GETDATE(), 'GEN', 'OPN', 0, 'Closed as part of database cleanse', 'JTY',
            (SELECT MAX(SCFSR.FSR_Num) FROM SCFSR WHERE SCFSR.FSR_Call_Num = T_Call_Num)+1,             
            (SELECT SCCall.Call_Cont_Num FROM SCCall WHERE SCCall.Call_Num = T_Call_Num),
            (SELECT SCCall.Call_Site_Num FROM SCCall WHERE SCCall.Call_Num = T_Call_Num),
            (SELECT SCCall.Call_Area_Code FROM SCCall WHERE SCCall.Call_Num = T_Call_Num),
            (SELECT SCCall.Call_Employ_Num FROM SCCall WHERE SCCall.Call_Num = T_Call_Num),
            (SELECT SCCall.Call_Prod_Num FROM SCCall WHERE SCCall.Call_Num = T_Call_Num)        
FROM @RANGE

COMMIT TRANSACTION
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

SELECT  ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_LINE() AS ErrorLine
END CATCH

Upvotes: 1

Views: 131

Answers (1)

Blank
Blank

Reputation: 12378

Use COALESCE:

COALESCE((SELECT MAX(SCFSR.FSR_Num) FROM SCFSR WHERE SCFSR.FSR_Call_Num = T_Call_Num)+1, 1)

If there is no record existed in table, this sub query get you null, COALESCE will return first non-null element in parameter list. So here it will give you 1 when record does not exist.

Upvotes: 1

Related Questions