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

CASE expression not working in Extract Transformation

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

CASE expression not working in Extract Transformation

I know I've used CASE in the past, but for some reason in my current job I'm unable to use a CASE statement as an expression in an Extract.

The expression is as simple as:

CASE

     WHEN sourcecolumn = 'Y' then targetcolumn = 'Yes'

ELSE sourcecolumn

END

I've tried many variations, but none validate. It seems there is a syntax error at the very beginning. When I look at the validation log, it appears as if the code actually says:

WHERE CASE

WHEN...

Where does that WHERE come from? I think it is causing the error. (I have nothing in the WHERE tab of the Extract Transformation)

Also, I recall in the expression field of the target column in the MAPPINGS tab, if I were to right click a CASE option would be available. A co-worker also sees this option. I don't

Is there something off in my job settings preventing the use of a CASE statement in an Extract? How do I get rid of the WHERE? Any other solutions?

Thanks!


Accepted Solutions
Solution
‎12-03-2014 10:58 AM
Super User
Posts: 5,424

Re: CASE expression not working in Extract Transformation

This is a flaw in DI Studio, when validating an expression, it often (not always) puts the expression in a where clause, even if the expression is in a select clause.

In those cases, just ignore the error. You are better off validating by running the transformation instead - if your syntax is valid, it will succeed.

Tip: DI Studio will default output to a view. Temporarily switch to a table - that will reveal any error at once (instead of when reading the view from succeeding transformations).

Data never sleeps

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

Re: CASE expression not working in Extract Transformation

Try this.

CASE

     WHEN sourcecolumn = 'Y' then  'Yes'

ELSE sourcecolumn

END as targetcolumn

Frequent Contributor
Posts: 89

Re: CASE expression not working in Extract Transformation

Still getting the error.

Here's the CASE statement in the Expression:

c1.PNG

Here's the error:

c2.PNG

Still, any idea why CASE isn't an option in the expression field?

c3.png

Trusted Advisor
Posts: 1,228

Re: CASE expression not working in Extract Transformation

Just a small modification in END as statement. Hopefully, this can provide the desired results.

CASE

     WHEN group = 'Y' then  'Yes'

ELSE group

END as group

Frequent Contributor
Posts: 89

Re: CASE expression not working in Extract Transformation

Made the modification to the CASE statement:

c4.PNG

Still getting the error:

c5.PNG

Like I mentioned, I wonder if it has anything with that WHERE syntax being inserted on line 9. Could it be causing the issue, and where is it coming from?

Super Contributor
Posts: 308

Re: CASE expression not working in Extract Transformation

Hello,

If I see correctly from your print screen you are trying to use CASE expression in a where clause. Try the following:

proc sql;

select *, CASE

     WHEN sex = 'M' then  'Male'

ELSE sex

END as newsex

from sashelp.class

having newsex='Male' ;

quit;

Frequent Contributor
Posts: 89

Re: CASE expression not working in Extract Transformation

Well, I'm not trying to use CASE within a WHERE clause. How is this WHERE clause getting generated? Is it by default?

Frequent Contributor
Posts: 89

Re: CASE expression not working in Extract Transformation

ok, I tried that proc sql statement:

c6.PNG

It appeared to validate, but when I opened the validation log...still an error:

c7.PNG

Super User
Super User
Posts: 7,942

Re: CASE expression not working in Extract Transformation

Now this is just a guess, but where are you posting that sql code.  I assume from the Data Management section that you are using some kind of graphical interface tool, I would assume that you are trying to put into what equates to a Where Condition window.  So the "where" is the default text, which then takes the text you put in Expression column to try to evaluate rows, so in Loko's example, the case statement has been moved from the conditional part, up to the selection part.  You would need to put the case in another column on your table, not the Expression one.

Sorry, can't be more specific, I avoid all types of graphical interface for these kind's of reason - faster and simpler to write code!

Frequent Contributor
Posts: 89

Re: CASE expression not working in Extract Transformation

Ha! Great point...I forgot to mention that this is in Data Integration Studio. It is an interface tool.

Solution
‎12-03-2014 10:58 AM
Super User
Posts: 5,424

Re: CASE expression not working in Extract Transformation

This is a flaw in DI Studio, when validating an expression, it often (not always) puts the expression in a where clause, even if the expression is in a select clause.

In those cases, just ignore the error. You are better off validating by running the transformation instead - if your syntax is valid, it will succeed.

Tip: DI Studio will default output to a view. Temporarily switch to a table - that will reveal any error at once (instead of when reading the view from succeeding transformations).

Data never sleeps
Frequent Contributor
Posts: 89

Re: CASE expression not working in Extract Transformation

LinusH, thanks!

I just found this out...I ignored the validation error, executed the job, and the Extract ran fine.

Odd, but good to know!

🔒 This topic is solved and locked.

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

Discussion stats
  • 11 replies
  • 1828 views
  • 0 likes
  • 5 in conversation