TSQL case syntax

Question

Hi I need help with the syntax on this code:

IF OBJECT_ID('TEMPDB..#LTS_MAP') IS NOT NULL
BEGIN DROP TABLE #LTS_MAP END


SELECT
dtMicIssue as  LATE_CHARGE_FACTOR
,CASE   
WHEN (dtMicIssue is NOT NULL) AND (dtMicIssue  <> '1900-01-01 00:00:00') 
 THEN CONVERT(SMALLDATETIME,dtMicIssue)  
     ELSE NULL END 


INTO    #LTS_MAP
FROM    SBAURSQL001.LTSArchive.dbo.LoanMaster_0609

receiving error: Msg 8155, Level 16, State 1, Line 5 No column was specified for column 2 of '#LTS_MAP'.

Trying to combine case functionality with select of the dtMicIssue and messing up on the syntax...

Answer 1

Exactly what it says

Every column needs a name in the table #LTS_MAP that you are creating...

SELECT
dtMicIssue as  LATE_CHARGE_FACTOR
,CASE   
WHEN (dtMicIssue is NOT NULL) AND (dtMicIssue  <> '1900-01-01 00:00:00') 
 THEN CONVERT(SMALLDATETIME,dtMicIssue)  
     ELSE NULL END  AS SomethingMeaningfulHere   --you're missing this: a column name!


INTO    #LTS_MAP
FROM    SBAURSQL001.LTSArchive.dbo.LoanMaster_0609

Answer 2

You need to alias the CASE Statement so that it has a column name:

IF OBJECT_ID('TEMPDB..#LTS_MAP') IS NOT NULL
BEGIN DROP TABLE #LTS_MAP END


SELECT
dtMicIssue as  LATE_CHARGE_FACTOR
,CASE   
WHEN (dtMicIssue is NOT NULL) AND (dtMicIssue  <> '1900-01-01 00:00:00') 
 THEN CONVERT(SMALLDATETIME,dtMicIssue)  
     ELSE NULL END AS DATE


INTO    #LTS_MAP
FROM    SBAURSQL001.LTSArchive.dbo.LoanMaster_0609

Answer 3

Since you are doing a SELECT INTO, SQL Server needs to know a name for every column. Your second column (the one with the CASE) does not have an explicit name. Decide what you want to call it, then put

AS ColumnName

immediately after the END that ends the CASE

Answer 4

Try this

SELECT dtMicIssue as LATE_CHARGE_FACTOR,
   CASE WHEN (dtMicIssue is NOT NULL) AND 
          (dtMicIssue <> '1900-01-01 00:00:00') 
        THEN CONVERT(SMALLDATETIME,dtMicIssue)
        ELSE NULL END As SomeColumnName

INTO #LTS_MAP 
FROM SBAURSQL001.LTSArchive.dbo.LoanMaster_0609