SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

CASE Statement Not Working

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

CASE Statement Not Working

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. 


Accepted Solutions
Solution
‎02-28-2016 01:10 PM
Super User
Posts: 5,256

Re: CASE Statement Not Working

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: CASE Statement Not Working

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.

Contributor
Posts: 20

Re: CASE Statement Not Working

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, ^, ^=, |, ||, ~, ~=.

Super User
Posts: 17,829

Re: CASE Statement Not Working

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?

 

Contributor
Posts: 20

Re: CASE Statement Not Working

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.

Trusted Advisor
Posts: 1,115

Re: CASE Statement Not Working


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').

Contributor
Posts: 20

Re: CASE Statement Not Working

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

Trusted Advisor
Posts: 1,115

Re: CASE Statement Not Working

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.

Super User
Posts: 5,256

Re: CASE Statement Not Working

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.
Data never sleeps
Contributor
Posts: 20

Re: CASE Statement Not Working

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, ^, ^=, |, ||, ~, ~=.

Super User
Posts: 17,829

Re: CASE Statement Not Working

I think the code is then:

 

 
CASE WHEN PIFSTOP  = '0' THEN 'O' ELSE 'C' END
Super User
Posts: 5,256

Re: CASE Statement Not Working

@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.
Data never sleeps
Solution
‎02-28-2016 01:10 PM
Super User
Posts: 5,256

Re: CASE Statement Not Working

If PIFSTOP is char, your constant in the expression must also be defined as char, '0'.
Data never sleeps
Contributor
Posts: 20

Re: CASE Statement Not Working

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!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 752 views
  • 4 likes
  • 5 in conversation