CASE Statement Not Working

[Daylon_Hunt]

I'm trying to create a CASE statement in the Target Table within a job in DI Studio.  The field being referenced from the Source Table is a Character Type.  My CASE statement is:

CASE WHEN PIFSTOP  = 0 THEN '' ELSE 'C' END

This seems like a fairly straight forward Expression, but with no luck. 

[Haikuo]

Syntaxwise seems fine to me, unless your '' is actually 1x double quote instead of 2X single quotes. If you could eleborate more on 'no luck', error messages, logs, expected output given input, etc.

[Daylon_Hunt]

The '' is actually two single quotes ' ' that I'm trying to use to essentially indicate a null value.  So the statement is supposed to be saying, when PIFSTOP equals 0 (ZERO), then ' ' (Blank), ELSE 'C'.  

The error that it's returning is:

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, GE, GT, IN, IS, LE, LIKE, LT, NE, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

[Reeza]

Do you need to give it a name?

CASE WHEN PIFSTOP  = 0 THEN '' ELSE 'C' END AS NEW_VARIABLE

Or is that handled in the DI studio somewhere else?

[Daylon_Hunt]

Still no luck but this is the error I receive when trying to view the data after using your CASE Statement:

Unable to execute query: SQL passthru expression contained these errors: ERROR: Expression using equals (=) has components that are of different data types....  ERROR: Character expression requires a character format.

[FreelanceReinh]

---

@Daylon_Hunt wrote:

(...) ERROR: Expression using equals (=) has components that are of different data types. ...

---

This is the error message indicating that PIFSTOP is of different type (i.e. character vs. numeric) than the constant on the other side of the equals sign (0 or '0').

[Daylon_Hunt]

I would agree with that assessment.  So now what's the solution?  

[FreelanceReinh]

I'm not familiar with DI studio, but the condition PIFSTOP=0 requires that PIFSTOP is a numeric variable. However, you wrote that it "is a Character Type" (in which case a condition like PIFSTOP='0' would be syntactically correct).

That said, your error message is different from what I would anticipate in case of a type mismatch.

[LinusH]

The tricky thing about DI Studio is that you need to check both the Metadata definition (which should be correct) and the physical data. So check that your input column really is numeric.
Also, check that you have defined your target column as char.
You can use Analyze - Contents and the View Data functionality for this.

[Daylon_Hunt]

I've confirmed that the Source data is indeed Character, and my Target field will also be Character.  With that said, I'm now attempting the following CASE statement, which also is not working:

CASE WHEN PIFSTOP  = 0 THEN 'O' ELSE 'C' END

I'm using the exact same Informat and Format types as the Source field, but getting the below error:

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, GE, GT, IN, IS, LE, LIKE, LT, NE, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

[Reeza]

I think the code is then:

 
CASE WHEN PIFSTOP  = '0' THEN 'O' ELSE 'C' END

[LinusH]

@Reeza: the column name is defined in the transformation Metadata. Thus, it's not specified in the expression. This way, you can quite easily change column definition in a whole program flow via a single point and click operation without having to change expressions.

[LinusH · Accepted solution]

If PIFSTOP is char, your constant in the expression must also be defined as char, '0'.

[Daylon_Hunt]

The problem was that I wasn't putting ' ' around the 0, which since zero is a numeric value then I didn't think that it was required but your explanation as to why it has to be, makes perfect sense.  Thank you LinusH!!