Nested Case How-To - 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

General SQL Server Forums

New to SQL Server Programming

Nested Case How-To

Author

Topic

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-20 : 11:59:56

Hi, I'm not new to SQL however I get confused when I transfer from different DBMS like Oracle, SQL Server, Firebird, MySQL and Access.

in SQL Server T-SQL, How do I go about a nested select case like the ff: And I supposed to use this as source for a Cross-Tab.

CREATE PROCEDURE SP_SAMPLE(
@DateStart datetime,
@DateEnd datetime,
@Param1 char(3),
@Param2 char(1),
@Param3 decimal(7,4))
AS
BEGIN

SELECT
Field1,
Field2,
Field3,
CASE
WHEN FieldSomething = 0.0000 THEN '*'
WHEN FieldSomething < @Param3 THEN '_'
ELSE
CASE @Param2
WHEN 'A' THEN SomeOtherField1
WHEN 'B' THEN SomeOtherField2
WHEN 'C' THEN SomeOtherField3
END
END Alias1
FROM SampleTable
WHERE UPPER(Field2) = UPPER(@Param1)
AND Field1 BETWEEN @DateStart AND @DateEnd
ORDER BY Field2, Field3, Field1

END

it has this error:

Error converting data type varchar to numeric.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 12:04:09

You need to make sure fields SomeOtherField1,SomeOtherField2,SomeOtherField3 are all of same datatype (varchar or char)

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-20 : 12:05:01

Additional Info:
FieldSomething and SomeOtherFields are of type Decimal(15,3)
Field1 is DateTime
Field2 is Char(3)
Field3 is Varchar(30)

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-20 : 12:06:20

Or convert them

CONVERT(varchar(n),Col)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-20 : 12:10:09

The issue seems to be in the 2 Case statements.

What I am trying to accomplish is.
1) Check FieldSomething if zero or below the specified value
2) If otherwise, display the value of another field (SomeOtherFieldx) depending on another parameter (Param2)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 12:15:33

quote:
Originally posted by sw0rdf1sh7

The issue seems to be in the 2 Case statements.

What I am trying to accomplish is.
1) Check FieldSomething if zero or below the specified value
2) If otherwise, display the value of another field (SomeOtherFieldx) depending on another parameter (Param2)

The rule of thumb is all return fields used with CASe should be of sme type. As you've used '*','-' as some of return values within case, you need to CAST() decimal values to varchar to make it work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-20 : 12:19:51

If you use front end application to show data, then format your data there

Madhivanan

Failing to plan is Planning to fail

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-20 : 12:23:53

Ahh, I get what you guys mean.

Should it be like this?

CASE
WHEN FieldSomething = 0.0000 THEN '*'
WHEN FieldSomething < @Param3 THEN '_'
ELSE
CASE @Param2
WHEN 'A' THEN Cast(SomeOtherField1 as Varchar(15))
WHEN 'B' THEN Cast(SomeOtherField2 as Varchar(15))
WHEN 'C' THEN Cast(SomeOtherField3 as Varchar(15))
END
END Alias1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 12:25:35

quote:
Originally posted by sw0rdf1sh7

Ahh, I get what you guys mean.

Should it be like this?

CASE
WHEN FieldSomething = 0.0000 THEN '*'
WHEN FieldSomething < @Param3 THEN '_'
ELSE
CASE @Param2
WHEN 'A' THEN Cast(SomeOtherField1 as Varchar(15))
WHEN 'B' THEN Cast(SomeOtherField2 as Varchar(15))
WHEN 'C' THEN Cast(SomeOtherField3 as Varchar(15))
END
END Alias1

Yup.That should be it

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-20 : 12:26:05

OR

CASE
WHEN FieldSomething = 0.0000 THEN '*'
WHEN FieldSomething < @Param3 THEN '_'
ELSE
Cast(
CASE @Param2
WHEN 'A' THEN SomeOtherField1
WHEN 'B' THEN SomeOtherField2
WHEN 'C' THEN SomeOtherField3
END
as Varchar(15))
END Alias1

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-20 : 12:32:44

Wahooo... Thanks, it's fun learning from you guys. It's really better to consult directly those who know than search for the answers yourself via google. More power...

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-20 : 15:39:55

quote:
Originally posted by sw0rdf1sh7

Wahooo... Thanks, it's fun learning from you guys. It's really better to consult directly those who know than search for the answers yourself via google. More power...

Actually, that would be a dis-service to yourself

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-20 : 16:00:51

=) what I meant is that I won't be wasting time looking for a topic that is closely related to the one I am looking for. Google may probably have millions of responses to a single question I need answered. Why not go directly to the source, the people who wrote them? Don't worry, it's not in my league to rely on spoon-feeding.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-21 : 12:24:47

quote:
Originally posted by sw0rdf1sh7

=) what I meant is that I won't be wasting time looking for a topic that is closely related to the one I am looking for. Google may probably have millions of responses to a single question I need answered. Why not go directly to the source, the people who wrote them? Don't worry, it's not in my league to rely on spoon-feeding.

But i believe always you explore more and get more depth of the topic when you search & do it by yourself.
Alternatively you can follow some experts advice and solve your problem,but make it a point to understand the approach & methodologies used thoroughly.

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-22 : 10:15:41

Yes, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources