IF statement / Variables.. problems!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » IF statement / Variables.. problems!

IF statement / Variables.. problems!

Rate Topic

Display Mode

Topic Options

Author

Message

cephasknapp

Posted Tuesday, September 18, 2012 4:15 AM

Forum 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

SGT_squeequal

Posted Tuesday, September 18, 2012 4:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, March 30, 2013 9:39 AM
Points: 261, Visits: 966

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 w00t

Post #1360673

cephasknapp

Posted Tuesday, September 18, 2012 4:47 AM

Forum 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

cephasknapp

Posted Tuesday, September 18, 2012 4:52 AM

Forum 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

Luis Cazares

Posted Tuesday, September 18, 2012 9:42 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 5:52 PM
Points: 960, Visits: 1,921

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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1360862

davidandrews13

Posted Tuesday, September 18, 2012 9:53 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 456, Visits: 2,673

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 »

Permissions