Trouble with <field> in (list) using SQL EXECUTE in load script.

[AndrewS]

I'm using the SQL EXECUTE() function in my data load editor to bring in some complex fields, but I'm getting errors of "Conversion failed when converting the varchar value '4AU' to data type int"

I trimmed back my load script to find the error, and its this select clause:

SUM(CASE WHEN
FACT_BUD_ACTUAL.BUCKET_ID in (1,3)
AND FACT_BUD_ACTUAL.STRU_ID = 41
AND FACT_BUD_ACTUAL.LVL_ID = 2
AND FACT_BUD_ACTUAL.OBJ_CD in (304)
THEN FACT_BUD_ACTUAL.PSTNG_AM ELSE 0 END) as P_304_Total,

Specifically its the OBJ_CD in (304) line. OBJ_CD is a varchar, and has primarily numeric entries but also some alphanumeric like 4AU referenced in the error.

For this instance I was able to side step the issue with:

TRY_CONVERT(INT,FACT_BUD_ACTUAL.OBJ_CD) in (304)

My question is, how am I supposed to structure my IN LIST queries to avoid unnecessary conversions?

[AndrewS · Accepted solution]

Through some trial and error I have discovered that the answer is using two single quotes(apostrophes) on either end of a value. 

That's a '', not a " (quotation mark). The distinction is nearly invisible, but its there.

So my solution is:

SUM(CASE WHEN
FACT_BUD_ACTUAL.BUCKET_ID in (1,3)
AND FACT_BUD_ACTUAL.STRU_ID = 41
AND FACT_BUD_ACTUAL.LVL_ID = 2
AND FACT_BUD_ACTUAL.OBJ_CD in (''304'')
THEN FACT_BUD_ACTUAL.PSTNG_AM ELSE 0 END) as P_304_Total,