simplify complex query

[peac3]

HI guys,

I have the code as below and it's working.

SELECT @ERR_MSG = EXC_X
FROM ExceptionCode WHERE EXC_C = 'G1'

      
UPDATE LOAD
SET ERR_F = 'W'
OUTPUT INSERTED.*,
      'G1',
      GETDATE(),
      @ERR_MSG + ' : ' + ACCT,
      1
INTO EXCEPTION      
WHERE CRIS_Code in (
            SELECT CRIS_Code
            FROM test 
      )

Is there anyway to make them as one query...
Between table ExceptionCode and LOAD table there is no link

the problem is @ERR_MSG I need to get it from table (ExceptionCode) mapped from exception code which is G1.

Appreciate for your help in advance.

 

[markros]

I think your code is OK, but you can also do:

UPDATE LOAD
SET ERR_F = 'W'
OUTPUT INSERTED.*,
      'G1',
      GETDATE(),
      COALESCE((SELECT top (1) EXC_X
FROM ExceptionCode WHERE EXC_C = 'G1')
 + ' : ','') + ACCT,
      1
INTO EXCEPTION      
WHERE CRIS_Code in (
            SELECT CRIS_Code
            FROM test 
      )

PluralSight Learning Library