simplify script

[peac3]

Hi,

I am trying to simplify the 2 queries below into one query but I'm running out of idea...

		INSERT INTO SYST_DETL
		(
		  [SYST_DETL_I]
		 ,[SYST_DETL_TABL_I]
		 ,[PL_1_I]


		)	
	    SELECT
	     @Syst_DETL_I
	     ,(SELECT SYST_DETL_TABL_I FROM SYST_DETL_TABL WHERE TABL_NAME_M = 'EMPLOYEE') AS SYST_DETL_TABL_I
	     ,f.GEMS_EMPLOYEE_I
	    FROM LOAD..CREDIT_EMPLOYEE_FPX crc
			INNER JOIN EMPLOYEE f ON crc.EMPLOYEE_BRCH_ACCT_N = f.EMPLOYEE_BRCH_ACCT_N
	    WHERE ( f.GEMS_CLNT_I != @AggrClientID AND crc.GEMS_EMPLOYEE_I IS NOT NULL) OR ( crc.GEMS_EMPLOYEE_I IS NULL)
	    

	    
	    INSERT INTO SYST_DETL
		(
		  [SYST_DETL_I]
		 ,[SYST_DETL_TABL_I]
		 ,[PL_1_I]
		 ,[PL_2_I]
		 ,[PL_3_I]
		)	
	    SELECT
	      @Syst_DETL_I
	     ,(SELECT SYST_DETL_TABL_I FROM SYST_DETL_TABL WHERE TABL_NAME_M = 'EMPLOYEE_PX') AS SYST_DETL_TABL_I
	     ,f.GEMS_EMPLOYEE_I
	     ,CONVERT(VARCHAR(50),@Exposure_Date,121)
	     ,@Currency_Code
	    FROM LOAD..CREDIT_EMPLOYEE_FPX crc
			INNER JOIN EMPLOYEE f ON crc.EMPLOYEE_BRCH_ACCT_N = f.EMPLOYEE_BRCH_ACCT_N

Anyone could help is really appreciated

 

[bborissov]

Not exactly simplyfied:

DECLARE @Employee int -- change the type if SYST_DETL_TABL.SYST_DETL_TABL_I has different type
DECLARE @EMPLOYEE_PX int
SET @Employee     = 0 -- If these have different type just change the zero to empty value of the type.
SET @@EMPLOYEE_PX = 0


-- Read from SYST_DETL_TABL
SELECT @Employee    = CASE WHEN TABL_NAME_M = 'EMPLOYEE'    THEN SYST_DETL_TABL_I ELSE @Employee    END
      ,@EMPLOYEE_PX = CASE WHEN TABL_NAME_M = 'EMPLOYEE_PX' THEN SYST_DETL_TABL_I ELSE @EMPLOYEE_PX END
       FROM SYST_DETL_TABL
WHERE TABL_NAME_M IN ('EMPLOYEE', 'EMPLOYEE_PX')

INSERT INTO SYST_DETL ( [SYST_DETL_I]
                       ,[SYST_DETL_TABL_I]
                       ,[PL_1_I])	
SELECT @Syst_DETL_I
     , @Employee
     , f.GEMS_EMPLOYEE_I
   FROM LOAD..CREDIT_EMPLOYEE_FPX crc
INNER JOIN EMPLOYEE f ON crc.EMPLOYEE_BRCH_ACCT_N = f.EMPLOYEE_BRCH_ACCT_N
WHERE ( f.GEMS_CLNT_I != @AggrClientID AND crc.GEMS_EMPLOYEE_I IS NOT NULL) 
   OR ( crc.GEMS_EMPLOYEE_I IS NULL)
	    
INSERT INTO SYST_DETL( [SYST_DETL_I]
                      ,[SYST_DETL_TABL_I]
                      ,[PL_1_I]
                      ,[PL_2_I]
                      ,[PL_3_I])	
SELECT @Syst_DETL_I
      ,@EMPLOYEE_PX
      ,f.GEMS_EMPLOYEE_I
      ,CONVERT(VARCHAR(50),@Exposure_Date,121)
      ,@Currency_Code
   FROM LOAD..CREDIT_EMPLOYEE_FPX crc
INNER JOIN EMPLOYEE f ON crc.EMPLOYEE_BRCH_ACCT_N = f.EMPLOYEE_BRCH_ACCT_N

Borislav Borissov
VFP9 SP2, SQL Server