use of nested statment (CASE or if...else...) in derive column transfromation in SSIS

Posted on 2011-09-20
Last Modified: 2012-05-12

I need to add large case statemnt in a derive column  tranformation in SSIS. The query is similar to below however I need to nested more than the sample below. The simple query like below is throwing an error., i'm not sure what wrong with that syntax. I would appreciate if I can get samples nested query for SSIS derive column transformation.

ISNULL(amt) ? "1":Amt && iif(Amt)=="$0.01 > $1,000" ? "2":Amt

Question by:keplan
  • 3

Expert Comment

ID: 36571874
Try this

case when isnull(Amt,1) then
      case Amt when 0.01 > 1,000 then 2
      else Amt end
else Amt end

Author Comment

ID: 36571929
Please note: i need in use in derive column transformation in SSIS, you sample is for t-sql
LVL 21

Accepted Solution

Alpesh Patel earned 250 total points
ID: 36573194
You cannot use Case or If need to use Ternary operator

Condition ? True : False

Author Comment

ID: 36577298
thanks for that, sorry i am not familiar with Ternary operator, kindly post some samples if possible, and also ca ternary operator be nested?

Author Closing Comment

ID: 36945713

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now