Derived transformation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Derived transformation

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-25 : 15:52:42

[code]

Currently,I have below code in the derived column but i want to extend the below condition set the TED to 0 if it null or "No" and If it is "YES" then to 1.

TED == "Yes" ? 1 : 0

When TED is null or '' then 0
if "YES" = 1
if "No" = 0[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 15:58:21

Use a CASE statement.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-25 : 16:28:54

I did tried with below code in derived showing an error.
Please let me know what i missing..

CASE WHEN TED ="Yes" THEN 1
WHEN TED ="No" THEN 0
WHEN TED = "" THEN 0
WHEN TED is null THEN 0
ELSE TED END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 16:36:47

Use single quotes: 'Yes', 'No', ''. You may need to do a CONVERT too since you are going from varchar data to an int.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-25 : 16:54:29

I did updated with mentioned changes it is still showing the error..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 17:02:52

Show us, please.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog