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?
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])
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
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
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
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 ?
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
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
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
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?
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'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?
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.
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?
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.
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
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..
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)),
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 ??
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.
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
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)
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?
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!
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 :/
