Insert statement with Case Function

[kpereira]

I have tried several times in several ways to use an Insert Into Statement which includes a CASE WHEN in a select statement to insert into the table. I have gotten errors that there weren't the same number of fields in the select as the insert. I tried altering it and can't seem to get it. Here is a clip of the statement without all the joins. Can someone enlighten me? Please?

Insert TCSFile (SASID, USID, Teacher_Last_Name, Teacher_First_Name, Teacher_Middle_Name, School_Code, LocalSectionID, LocalCourseID, LocalCourseName, Section_Entry_Date, Section_Exit_Date)
(SELECT DISTINCT
dbo.CSL_STD_DEMO_DISTRICT.STATE_NUMBER,
dbo.CSL_STAFF_DEMO_DISTRICT.LICENSE_NUMBER,
dbo.CSL_SMS_USER.LAST_NAME,
dbo.CSL_SMS_USER.FIRST_NAME,
dbo.CSL_SMS_USER.MIDDLE_NAME,
dbo.CSL_SET_SCHOOL_INFO.DISTRICT_NUMBER,
dbo.CSL_SMS_CLASS.CLASS_NUMBER,
dbo.CSL_SMS_DISTRICT_SUBJ_ITEM.NUMBER,
(convert(char(10),dbo.CSL_SMS_TERM_DATE.START_DATE,101)),
((Test=Case
when dbo.CSL_SMS_TERM_DATE.END_DATE >@end then null
when dbo.CSL_SMS_TERM_DATE.END_DATE <=@end then convert(char(10),dbo.CSL_SMS_TERM_DATE.END_DATE,101)
END ))
FROM

 

[Gixonita]

Hi,

Take away the "Test="

just leave the Case .....

Hope this helps

 

[gmmastros]

This part...

[tt][blue]Insert TCSFile (SASID, USID, Teacher_Last_Name, Teacher_First_Name, Teacher_Middle_Name, School_Code, LocalSectionID, LocalCourseID, LocalCourseName, Section_Entry_Date, Section_Exit_Date)[/blue][/tt]

specifies 11 columns. Your select appears to only have 10 columns. Something is missing.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kpereira]

Thank you both! You were both correct. After working on this project for so long I was getting tunnel vision. You were both VERY helpful!

Regards,

Karen

 

[Gixonita]

Glad I could help

 

[ESquared]

Test=" is just fine except for the superfluous parentheses sprinkled throughout.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]