Case / Switch Function Help

Jun 9, 2004

Starting to play around with SQL server at work and this is my question:

In the query design mode in access I can make one of the fields an expression that is driven by a built-in switch function.
i.e. Switch([CategoryName] Like "Beverage","Drink",[CategoryName] Like "Cheese","Dairy")
This results in the additional column field I created to display "Drink" for each record that has the CategoryName = "Beverage", and "Dairy" for "Cheese".

Can I do something like this in SQL server in the view designer itself, or do I need to make a user defined function and call it?

Thanks in advance for any help.

View 3 Replies


ADVERTISEMENT

Switch Case Is There

Oct 4, 2007

Dear all,
is there any switch case like we have in C language....
in sql server?

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 13 Replies View Related

How To Use Switch - Case Statement In T-SQL..?

Apr 5, 2007

Hi,

I want to use switch - case statement in T-SQL stored procedure.

Can any one help regarding the same..?



for e.g.

switch (exp)

{

case 1 : stmt 1; break;

case 2 : stmt 2; break;

case 3 : stmt 3; break;

& so on.......

}



View 8 Replies View Related

Power Pivot :: Switch Statement And Function Calls

Nov 24, 2015

I created a Switch statement below that surprisingly doesn't throw any errors and some of it actually works. The problem is that the function calls in the 3rd and 4th sections of it below (in bold) are not working.

=switch(HASONEVALUE('s1JudgeIds'[JudgeName]),
values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(Time[ExitMonthCategory]),Time[ExitMonthCategory] <= MAX(Time[ExitMonthCategory]))),
values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",calculate([Numerator],all('s1JudgeIds'[JudgeName])),
values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare],all('s1JudgeIds'[JudgeName])),
values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare])

View 24 Replies View Related

Case Function In T-SQL

Sep 13, 2007

Wondering if possible to use case function in where clause of T-SQL statement. I have this application that needs to get the recordsets based on the day of the date selected that is @dDay in one of these set of values {0,7,14,21, more). Though, I know that if I use either of this:
datediff(dd,@startdate,@today) = @dDay
or convert(varchar(12), @startDate, 101) between convert(varchar(12), @today, 101) and convert(varchar(12), dateadd(dd,@dDay, @today), 101)
It will work for only the values that are specific as in number but what of if "more" is selected which means that from that day upward, can i use this T-SQL to accomplish this. PLease help
Any suggestion is welcome. thanks
 
 

View 6 Replies View Related

IIF Function Or Case

Nov 27, 2006

Hi I am wanting to use some thing like the IIF function in Access in a SQL view I did some looking n your Forum and found the case function its awsome does what I need but i can not use it in a view. Does any one have an alternate solution .. Thanks Jakes

My "Case"


USE SysproCompanyB
GO
SELECT dbo.ZZCuCostValue.Supplier, dbo.ZZCuCostValue.StockCode, dbo.ZZCuCostValue.[Year], dbo.ZZCuCostValue.[Month],'RandCost' =
CASE
WHEN BuyMulDiv IS NULL THEN '0'
WHEN BuyMulDiv = 'M' THEN round(dbo.ZZCuCostValue.UnitCost * dbo.ZZCuCostValue.ExchangeRate,4)
WHEN BuyMulDiv = 'D' THEN round(dbo.ZZCuCostValue.UnitCost / dbo.ZZCuCostValue.ExchangeRate,4)
ELSE 0
END
FROM dbo.ApSupplier INNER JOIN
dbo.TblCurrency ON dbo.ApSupplier.Currency = dbo.TblCurrency.Currency INNER JOIN
dbo.ZZCuCostValue ON dbo.ApSupplier.Supplier = dbo.ZZCuCostValue.Supplier

GO

View 6 Replies View Related

Case Function Again:(

May 9, 2008


Hi,

I'd like to ask my question with the data sample this time. Below is my table with 3 phone numbers, null, zero, less than 10 digits, I'd to get as an out put phone with not null, zero, or less than 10 digits .I couldn't run that CASE statement. Please HELP ......SOS.. Thanks






Home_phone
Work_phone
cell_phone_1

0
0
0

12344567890
0
0

0
NULL
32547821

12344567891
NULL
0

12344567892
NULL
0

0
0
12344567893

0
0
12344567894

0
0
NULL

12344567895
0
0

0
12345
0

12344567896
0
0


12344567897
0

Null
0
0

12344567898
0
0









OUTPUT



12344567890



12344567891



12344567892



12344567893



12344567894



12344567895



12344567896



12344567897



12344567898


View 7 Replies View Related

Function Or CASE Statement?

Feb 25, 2008



Hi,

I have a Query some thing like this,

Select Table1.Code, 'field2' = CASE




WHEN 1 THEN (Select name From Table2 where Table2.Code=Table1.Code)
WHEN 2 THEN (Select name From Table3 where Table3.Code=Table1.Code)
WHEN 3 THEN (Select name From Table4 where Table4.Code=Table1.Code)
END

FROM Table1

Do I need to use Function instead of CASE for better performance ?


Regards

Mujeeb






View 5 Replies View Related

Create User Function W/Case

Jul 23, 2005

I keep getting an error message "incorrect syntax near keyword case"when trying to run this:USE DEDUPEGOCREATE FUNCTION fnCleanString(@mString varchar (255))RETURNS varchar(255)ASBEGINDECLARE@mChar char(1),@msTemp varchar(255),@miLen int,@i int,@iAsc intBEGINset @mChar = ''set @msTemp = ''set @miLen = Len(@mString)set @i = 1while @i <= @miLenbeginset @mChar = substring(@mString,@i,1)set @iAsc = Ascii(@mChar)casewhen @iAsc >= 87 And iAsc <= 122 Then set @mChar = @mCharwhen iAsc >= 65 And iAsc <= 90 Then set @mChar = @mCharwhen iAsc >= 49 And iAsc <= 57 Then set @mChar = @mCharelse @mChar = ""endset @msTemp = @msTemp & @mCharset @i = @i + 1endENDRETURN @msTempENDCan anybody point out what I'm doing wrong?Thanks.Randy

View 3 Replies View Related

How To Recall Column Name Defined In CASE Function

Aug 17, 2007

Hello all,

I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @Term, SQL Server returned an error: Invalid column name 'SLA'

If anyone knows a solution, please kindly let me know.

Thank you!

Here is the sample code:

ALTER PROCEDURE [dbo].[sp_premium_register]
@PolicyType AS VARCHAR(10),
@ReportFrom AS DATETIME,
@ReportTo AS DATETIME,
@Business AS VARCHAR(1),
@Term AS VARCHAR(1)

SELECT
ColumnA,
ColumnB,

SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L'
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S'
END

FROM DNIssue D
LEFT OUTER JOIN KILRIShare S
ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo)
LEFT OUTER JOIN KILPolicy P
ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo)
LEFT OUTER JOIN v_report_KILDNFund F
ON (D.DebitNote = F.DebitNote)
LEFT OUTER JOIN PolicyProfile R
ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType


WHERE
SLA = @Term

Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDate

View 3 Replies View Related

T-SQL (SS2K8) :: Case Query - Unpivot Function

May 6, 2015

I've this result from my 'case' query;

Jan Feb Mar April
1 2 3 4

I want ;

Month Value
JAN 1
Feb 2
Mar 3
April 4

View 3 Replies View Related

Can Insert Statement Works In Case When Function

Nov 5, 2007

Hi

Im beginer in sql,Please guide

can insert statement works fine in case when function

for example

case when condition1=true then (first insert statement based on some condition) when condition2=true then (second insert statement based on some other condition)
end

View 5 Replies View Related

SQL 2012 :: Using Count Function And Case In One Select Statement

Jul 9, 2014

I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.

Right now I am achieving this by the following case statement

SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class'
WHEN Stcount>=10 and StCount<=50THEN 'Medium Class'
WHEN Stcount>50 THEN 'Big Class'
END
FROM(
select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl

But can I do this with just one select statement?

View 2 Replies View Related

Problem With PATINDEX Function For Case-sensitive Information

Aug 21, 2007

Hi,

My database is not case-sensitive, but I want output like...

SELECT patindex('%[A-Z]%','gaurang Ahmedabad')

The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.

Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).

Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?

Thanks,

Gaurang Majithiya

View 7 Replies View Related

Joining On And Grouping By CASE Function Column Alias (URGENT)

Apr 14, 2004

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
CASE
WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
END,
Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC Ross

View 5 Replies View Related

Transact SQL :: CASE With Subselect And DATEADD Function Returning NULL Values

Jun 15, 2015

I'm running the following test query on a single table:

SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp),
CASE DATENAME(dw, sph.datestamp)    
WHEN 'Monday' then 'Monday'  
ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA') 
END AS TestCase,

[Code] ....

And here's an example of the output I'm getting:

Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?

View 7 Replies View Related

CASE Function Result With Result Expression Values (for IN Keyword)

Aug 2, 2007

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

View 3 Replies View Related

/3GB Switch

Sep 2, 2007

Hi All
Server 2003 standard edition, MSsql 2005 standard edition
As I understand it server 2003 standard only supports 4gb. Does this mean I can only use up to 4gb as the maximum memory for server 2005?
If that is the case I would like to use the 3GB switch, and leave the other gig for other apps. If I set the 3GB switch in boot.ini do I then also have to enable AWE (which I can't seem to do) or can I forget about AWE altogether?

Many thanks
Martin

View 5 Replies View Related

3gb Switch And AWE

Apr 10, 2007

in configuring AWE whether i need use both 3GB in boot.ini and AWE Enabling option in SQL Server

View 3 Replies View Related

CASE Function Home Phone Work Phone Cell Phone

May 8, 2008



Hi,

I'm trying to create a case function for home phone ,work phone and cell phone. The thing is some of the home phone numbers either null, zero or less than 10 digits then i'd like to get either cell phone or work phone if they are not null, zero or less than 10 digits.

I'd appreciate it if you could help me on this?

Thanks in advance.

View 19 Replies View Related

Adding 3 Gig Switch

Dec 12, 2002

Hi,

We have a standard Win 2K server and SQL2000 Enterprise edition on our DELL server.

Will it be possible to add a /3 Gig switch to boot.ini and make sql server to use 3 gig of RAM and the OS with 1 Gig of RAM.

This a live server website with heavy transactions more than 100 Transactions per minute.

We are planning to do a Transactional replication against 4 databases (publisher) with remote ditributor.

The reason why I am asking about this RAM increase is for this replication. Or is it Ok to have the 2 Gig RAM for this environment +replication.

Please give me a suggestion, how to proceed with this issue as i am concerned about memory especially for replication.

(4 Database sizes are 20 gig,7 gig,5 gig,5 gig)

Thanks,
Anu

View 10 Replies View Related

Date Switch

Nov 9, 2005

How do I replace month with day value and day value with month.
Example:
2005-02-09 00:00:00.000
replace with
2005-09-02 00:00:00.000

View 1 Replies View Related

Switch To Another User

Jul 20, 2005

Hi,Is it possible to switch to another user's credentials after logging in? Iseem to remember that there is a stored procedure for this, but I can't finddetails anywhere. I want to temporarily suspend my login's admin privilegesand become an "ordinary" user, for testing purposes.Thanks,Peter

View 1 Replies View Related

Context Switch

Sep 28, 2007


There are some concept about context switch block me .

---executor: dbo
create user u1 without login


go

create table t

(

col int

)

go


create proc proc1

with execute as 'u1'
as

insert into t values(1)


go

exec proc1

I think proc1 can't be run successfully beacuse the executor of proc1 is u1 and
I didn't grant any permission to user u1. So it will return a error message like The INSERT permission was denied on the object 't', database 'tempdb', schema 'dbo'

To my surprise, the "exec p1" command could run successfully..

Why?

View 12 Replies View Related

Table Switch Won't!

Aug 17, 2007

I am trying my first sliding partition scheme with 2005, and I am getting the following error...


ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table "ScanStoreAggregateLoad' allows values that are not allowed by check constraints or partition function on target table "ScanStoreAggregateTest'.


I have gone over the functions and constraints a gazillion times. The columns are set to Not Null. The constraints are set to AND NOT NULL. The constraint values align and align with the function. But still I get the failure. Any help would be greatly appreciated. Here are the definitions:


CREATE PARTITION FUNCTION pfn_ScanStoreAggregate(int)

AS

RANGE LEFT FOR VALUES

(19224,

19225,

19226,

19227)


CREATE TABLE dbo.ScanStoreAggregateTest(

ProductID int NOT NULL,

DateKey int NOT NULL CONSTRAINT ck_ScanStoreAggregateRange CHECK ((DateKey BETWEEN 19224 AND 19227) AND (DateKey IS NOT NULL)),

Span tinyint NOT NULL,

LocationID smallint NOT NULL,

WeeksTY tinyint NOT NULL,

WeeksLY tinyint NOT NULL,

UnitsTY numeric(12, 2) NOT NULL,

UnitsLY numeric(12, 2) NOT NULL,

SalesTY money NOT NULL,

SalesLY money NOT NULL,

CostTY money NOT NULL,

CostLY money NOT NULL,

ACVTY money NOT NULL,

ACVLY money NOT NULL,

SalesPerMMACVTY money NOT NULL,

SalesPerMMACVLY money NOT NULL,

) ON ps_ScanStoreAggregate (DateKey)


CREATE TABLE dbo.ScanStoreAggregateLoad(

ProductID int NOT NULL

,DateKey int NOT NULL

,Span tinyint NOT NULL

,LocationID smallint NOT NULL

,WeeksTY tinyint NOT NULL

,WeeksLY tinyint NOT NULL

,UnitsTY numeric(12, 2) NOT NULL

,UnitsLY numeric(12, 2) NOT NULL

,SalesTY money NOT NULL

,SalesLY money NOT NULL

,CostTY money NOT NULL

,CostLY money NOT NULL

,ACVTY money NOT NULL

,ACVLY money NOT NULL

,SalesPerMMACVTY money NOT NULL

,SalesPerMMACVLY money NOT NULL

) ON DataAggregates



ALTER TABLE dbo.ScanStoreAggregateLoad

WITH CHECK

ADD CONSTRAINT ck_ScanStoreAggregate19227_DateKey

CHECK ((DateKey = 19227)

AND (DateKey IS NOT NULL))

View 6 Replies View Related

Booi.ini Switch

Dec 10, 2006

Hello,



I read a lot of artical but still can't understand.

I have SQL 2005 ent edition 32BIT on Windows 2003 Ent R2 32 bit.

I have 4GB on my machine.

What switch I need ton the boot.ini

/3GB

/Userva=2900

/PAE (is this only if I have more than 4GB?)



Thanks

View 11 Replies View Related

Memtoleave And -g-switch

Jun 7, 2007

hi out there
On our Windows 2003 servers w. sp1 and running MS SQL Server 2000 w. sp4 we
see from time to time that we get this error "cannot allocate 64k
continous memory" or "SQL Server could not spawn process_loginread thread"
which could be caused by nothing left in the "Memtoleave" pool - I have now
search for advice on how to determine the values for the -g-switch - but
without much success - and if I just go for the "try&error" concept my
sql-server just allocates less and less ??? - ehh - in which units are the
parameters for the -g option specified - bytes, kilobytes, mbytes - 4k block
???? Any suggestions for measuring the actual running value of this pool -
memtoleave ??

best regards /ti

View 7 Replies View Related

Help With A Switch Statement.

Jun 18, 2007

Hey again,



So here's what I'm trying to do: I have three columns of data. Sometimes only one of the columns will contain a value while others may contain a null. If two or three contain a value it will be the same. So if I'm building a table in the layout designed and I want the value of the table to be the value stored in these columns. In pseudocode it looks like this:



Switch(column1 and column2 are null, value = column3, if column1 and column3 are null, value = column2, otherwise, value = column1)



Something like that where column1 is the default so if column 1 has a value then set the textbox value to it otherwise find a column that has it. I know that at least one column will definitely have a value. Anyone that can provide guidance on how to execute these I would appreciate it greatly.



Thank you!

View 10 Replies View Related

How I Use The N' Switch In Stored Procedure

Jun 26, 2004

Any One Tell me how i can use N'Switch in SQL Server Stored Procedure .
When I use Such a this syntax i am encountered and stoped with a error.
the is below(Stoerd Prdocedure Code)
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
CREATE proc sp_check_userpass(@username nvarchar(50),@password nvarchar(50),@first nvarchar(50),@last nvarchar(50),@email nvarchar(50),@stage int )as
declare @cnt int
begin
select @cnt=count(*) from member_info where (@username=username and @password=password)
if(@cnt=0)
begin
insert into atable(first,last,username,password,stage) values(@first,@last,@username,@password,@stage)
end
else
return @cnt
end
GO
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Other thing is that when exec this store proc it must be return a value return back to the @cnt but it is return only This( The command(s) Completed succefully).
Any One guide me how i can must be do .
i must clear it that i am using this query in ASP.NET SqlClient Object To Execute Query.
Thanx In Advance. ArQa

View 1 Replies View Related

.rdlc Table Use Of SUM And SWITCH Together?

Jan 19, 2007

Hello everyone, thanks in advance for reading. I'm new to reports and have tried searching for my answer with no luck. Any direction would be great.

Here is the issue, I have 3 colums in the dataset which display String data ("High", "Medium", or "Low")

I've added a column to the table on the report where I would like to convert the value of "High", "Medium" and "Low" to 3,2,1 respectively then SUM these numbers.

Is this possible? I've tried several variations of the code below--maybe it's not possible to do what I want? Thanks again for your time and help.



Fields!PP.Value is set to:
=Switch(Fields!Avail_Rank.Value = "High", 3, Fields!Avail_Rank.Value = "Medium", 2, Fields!Avail_Rank.Value = "Low", 1)

=Switch(Fields!Volume.Value = "High", 3, Fields!Volume.Value = "Medium", 2, Fields!Volume.Value = "Low", 1)

=Switch(Fields!Integ_Rank.Value = "High", 3, Fields!Integ_Rank.Value = "Medium", 2, Fields!Integ_Rank.Value = "Low", 1)

=Sum(Fields!Avail_Rank.Value + Fields!Volume.Value + Fields!Integ_Rank)

View 3 Replies View Related

SQL Edition And Licensing Switch

Aug 16, 2007

I have two SQL servers. One licensed for 2000 Standard Edition licensed under the processor model. The other is SQL 2000 Enterprise Edition licensed under the user cal model.



I want to switch these Editions and licensing models. My production server needs the Enterprise Edition and I want to put the processor licensing model with it. I want to put the Standard Edition on my reporting server with the user cal licensing model.



I have the instructions to upgraded Editions and it is straight forward. Can I use this procedure for both servers and not have issues?


Thanks

View 1 Replies View Related

Proc To Switch Two Values In Table

May 29, 2008

I need to switch two int values in my table, but I have no clue how to do it.  If I switch the 6 to a 5, then I can't distinguish between the 5 that's supposed to be a 5 and the 5 that needs to be a 6. Any help is greatly appreciated - thanks in advance! 

View 1 Replies View Related

Need A Way To Switch Specific Data From Columns

Jul 13, 2005

Basically I have 635k records in a table with a person's first name, and date of birth (other stuff but it's not relavent). I imported all the data from excel files, but somehow a bunch of records got the first name and date of birth mixed up, so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort. Now records are in fact repeated so another possible but more time taking solution is to write a stored procedure that I give the date of birth and it does the switching around for the respective date of birth when it's found inside the First name. Any suggestions? All the code I've written has proved useless :/

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved