BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Daylon_Hunt
Obsidian | Level 7

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
If PIFSTOP is char, your constant in the expression must also be defined as char, '0'.
Data never sleeps

View solution in original post

13 REPLIES 13
Haikuo
Onyx | Level 15

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Jade | Level 19

@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
Obsidian | Level 7

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

FreelanceReinh
Jade | Level 19

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
Tourmaline | Level 20
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
Daylon_Hunt
Obsidian | Level 7

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
Super User

I think the code is then:

 

 
CASE WHEN PIFSTOP  = '0' THEN 'O' ELSE 'C' END
LinusH
Tourmaline | Level 20
@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
LinusH
Tourmaline | Level 20
If PIFSTOP is char, your constant in the expression must also be defined as char, '0'.
Data never sleeps
Daylon_Hunt
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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