Using a CASE statement within an Insert statement

Hi there,
I was wondering if you can point me in the right direction; I would like to enter data into a table, but would like to alter some fields, depending on the value of the data: (ie. AccountID, ExpenditureClassID)

I tried using a CASE statement, but am having some issues, or lack of knowledge in this type...this is what I have so far:

DECLARE @AccountID varchar(8)
DECLARE @ClassID char(2)

set @AccountID = AFEs.AccountID
set @ClassID = AEFs.ExpenditureClassID

declare @strSQL varchar(4000)

set @strSQL = 'INSERT INTO CancelledAFEs ( AFE, AFENumber, AFEYear, Account, AFEClass, AFEStatus, D_SQL, Process )
              SELECT AFEs.AFENumber, AFEs.AFENumber, YEAR(AFEs.CreationDateTime),'

print '1. ' + @strSQL

set @strSQL = @strSQL +
      CASE @AccountID
            WHEN 10 THEN '147300,'
            WHEN 11 THEN '147301,'
            WHEN 12 THEN '147305,'
            WHEN 13 THEN '147399,'
      END

print '2. ' + @strSQL

set @strSQL = @strSQL +
      CASE @ClassID
            WHEN 1 THEN '1N,'
            WHEN 2 THEN '1X,'
            WHEN 3 THEN '2S,'
            WHEN 4 THEN '3G,'
            WHEN 5 THEN '4H,'
            WHEN 6 THEN '5C,'
            WHEN 7 THEN '6E,'
            WHEN 8 THEN '7M,'
            WHEN 9 THEN '9A,'
      END

PRINT '3. ' + @strSQL

set @strSQL = @strSQL + '99, CONVERT(SMALLDATETIME, GETDATE()), 1
FROM         AFEs LEFT OUTER JOIN CancelledAFEs ON AFEs.AFENumber = CancelledAFEs.AFENumber
WHERE     YEAR(AFEs.CreationDateTime)=YEAR(GETDATE()) AND (AFEs.AFEStatusID = 16) AND (AFEs.AccountID BETWEEN 10 AND 13) AND
                      (NOT (AFEs.AFENumber IN
                          (SELECT DISTINCT AFENumber
                            FROM          CancelledAFEs)))'

PRINT '4. ' + @strSQL

--exec (@strSQL)

So, for example, if the AccountID is 10 and the ExpenditureClassID is 1, then the Insert statement should look like this:

INSERT INTO CancelledAFEs ( AFE, AFENumber, AFEYear, Account, AFEClass, AFEStatus, D_SQL, Process )
              SELECT AFEs.AFENumber, AFEs.AFENumber, YEAR(AFEs.CreationDateTime),147300,1N,99, CONVERT(SMALLDATETIME, GETDATE()), 1
FROM         AFEs LEFT OUTER JOIN CancelledAFEs ON AFEs.AFENumber = CancelledAFEs.AFENumber
WHERE     YEAR(AFEs.CreationDateTime)=YEAR(GETDATE()) AND (AFEs.AFEStatusID = 16) AND (AFEs.AccountID BETWEEN 10 AND 13) AND
                      (NOT (AFEs.AFENumber IN
                          (SELECT DISTINCT AFENumber
                            FROM          CancelledAFEs)))

I thought that I could set the @AccountID and @ClassID to their respective fields, but that didn't work. (Gave me an error: "The mult-part identifier AFEs.AccountID could not be bound.

I was thinking about maybe having to  loop to check each row, change the data, then insert it into the table, am I on the right path, or am I missing something?

(** Actually, I think there could be another error, after reading this; I think I might have to put the 'VALUES' syntax in there as well...)

Any input would be most appreciative...

Thanks,
Classic1

P.S. This would be run as a job...
Start Free Trial