Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IF statement / Variables.. problems! Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 4:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 8:28 AM
Points: 7, Visits: 28
Hello

I am trying to write a view and have a column that looks for a value in a column, if it finds it then, move onto the next etc...

When it finds the value it is looking for I want it to return the value contained in another column.

EG:
Table Headings:

Type 1, Type 2, Type 3, Type 4, Detail 1, Detail 2, Detail 3, Detail 4

So it looks for a value in the Type column, when it finds it, it moves on to the next Type. When it doesn't find the value, it returns the Detail column. If it finds the value in all columns, then it returns "All" or some such.

I can work it out in Excel, but I'm new to SQL Server 2005 so it is a bit of a battle.

Thanks

Peter
Post #1360661
Posted Tuesday, September 18, 2012 4:39 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020
can you past in some DDL and representative data so that we can see what your trying to explain?

*************************************************************

The first is always the hardest
Post #1360673
Posted Tuesday, September 18, 2012 4:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 8:28 AM
Points: 7, Visits: 28
The "Type" fields contain various categories in a 3 number/figure format.

I'm looking for the first Type field where "RFS" does NOT appear.

When it finds this first occurance, I want to create a column that returns the corresponding detail.

So I want it to look at existing columns and return the result in a new column...

I don't know if that explains it better. Let me know what else you would want to see.

Regards


Peter
Post #1360676
Posted Tuesday, September 18, 2012 4:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 8:28 AM
Points: 7, Visits: 28
So the equivalent in Excel is:

=IF(Type1="RFS",
IF(Type2="RFS",
IF(Type3="RFS",
IF(Type4="RFS","ALL",Detail4),
Detail3),
Detail2),
Detail1)


Sorry for format, I'm a little unfamiliar with the forum layout
Post #1360679
Posted Tuesday, September 18, 2012 9:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:26 PM
Points: 3,653, Visits: 7,970
I guess you can do this or several nested CASEs as you had on Excel.

CASE WHEN Type1 <> 'RFS' THEN Detail1
WHEN Type2 <> 'RFS' THEN Detail2
WHEN Type3 <> 'RFS' THEN Detail3
WHEN Type4 <> 'RFS' THEN Detail4
ELSE 'ALL' END

When a condition evaluates TRUE, the following conditions will be ignored.
What would happen when you have 'RFS' in Type2 and Type4? What would be your precedence?



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1360862
Posted Tuesday, September 18, 2012 9:53 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:30 AM
Points: 577, Visits: 3,416
perhaps like this?
select case when @type1 = 'RFS' THEN
case when @type2 = 'RFS' THEN
case when @type3 = 'RFS' THEN
case when @type4 = 'RFS' THEN 'ALL' ELSE @type4 END ELSE @type3 END ELSE @type2 END ELSE @type1 END

Post #1360869
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse