Derived Column Transform using up to 6 OR Statements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Data Transformation Services (DTS) » Derived Column Transform using up to 6 OR...

Derived Column Transform using up to 6 OR Statements

Rate Topic

Display Mode

Topic Options

Author

Message

trinityr

Posted Thursday, November 03, 2011 2:45 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, November 17, 2011 8:38 AM
Points: 11, Visits: 59

I have a derived column that I am trying transform based on a range.

(lv <= "20") ? "1" : (lv <= "50") ? "2" : (lv <= "80") ? "3" : (lv <= "100") ? "4" : (lv <= "120") ? "5" : (lv <= "150") ? "6" : (lv <= "300") ? "7" : ""

Here is what I have so far. All values after the 4, 5, 6, and 7 do not seem to get read properly and are changed to 1 and 2.

Would a look up prove to be better due the extended amount of OR statements?

Post #1200239

Jack Corbett

Posted Friday, November 04, 2011 11:43 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 10,310, Visits: 11,506

I think your issue is that you are using strings in the comparison not numbers. What is the data type of lv? If it is a number stored in a string you might want to convert it to a numeric data type and do the comparison. For example when comparing strings "100" < "20" and every 2 digit "number" is between 80 and 99 is > "100". For example "91" > "100".

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #1200802

trinityr

Posted Friday, November 04, 2011 12:05 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, November 17, 2011 8:38 AM
Points: 11, Visits: 59

Instead of using a Derived Column Expression I used a CASE Statement after the Data load.

UPDATE DimTable
SET [rank] = CASE
WHEN [lv] BETWEEN 1 and 19 THEN 1
WHEN [lv] BETWEEN 20 and 49 THEN 2
WHEN [lv] BETWEEN 50 and 79 THEN 3
WHEN [lv] BETWEEN 80 and 99 THEN 4
WHEN [lv] BETWEEN 100 and 119 THEN 5
WHEN [lv] BETWEEN 120 and 149 THEN 6
WHEN [lv] BETWEEN 150 and 300 THEN 7
ELSE ''
END
GO

Post #1200816

Jack Corbett

Posted Friday, November 04, 2011 12:14 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 10,310, Visits: 11,506

That works. One difference is that you are using numeric data and not strings to do the comparison.

Post #1200819

trinityr

Posted Friday, November 04, 2011 12:21 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, November 17, 2011 8:38 AM
Points: 11, Visits: 59

Whoops. Thanks for the other suggestion as well. It might be very handy in a future project. Always learning new tricks Thanks!

Post #1200828

smecharrafie

Posted Thursday, January 05, 2012 2:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 12, 2012 9:32 AM
Points: 46, Visits: 151

Try to substitute the derived column transformation by adding the column in the OLE DB Source form the start and set the required conditions using Case statement.

Regards,
Samer

Post #1230556

« Prev Topic | Next Topic »

Permissions