CASE expression not working in Extract Transformation

[jwhite]

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!

[stat_sas]

Try this.

CASE

     WHEN sourcecolumn = 'Y' then  'Yes'

ELSE sourcecolumn

END as targetcolumn

[jwhite]

Still getting the error.

Here's the CASE statement in the Expression:

Here's the error:

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

[stat_sas]

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

[jwhite]

Made the modification to the CASE statement:

Still getting the error:

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?

[Loko]

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;

[jwhite]

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

[jwhite]

ok, I tried that proc sql statement:

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

[RW9]

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!

[jwhite]

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

[LinusH · Accepted solution]

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

[jwhite]

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!