CASE In UD Funcitons
Aug 31, 2001Is there some reason why I can't use a CASE Statement inside a User-Defined Function?
View 1 Replies
ADVERTISEMENT
Extend GetUserID Function Or Create Additional Custom Information Funcitons
Jan 25, 2008Hi there
I'm relatively new to Reporting Services and .Net development so bear with me if I've missed something obvious.
I've set up Reporting Services to run under forms authentication successfully.
I want to filter the data (both within Report Builder Models and .rdl files) to the company the user belongs to.
To avoid joining the users table to all other tables on the companyID I'm wondering if it's possible to create a custom funciton which could use the UserID to lookup the companyID and then pass this through as a parameter, and have this "GetCompanyID" function available when building Models and/or reports...???
When building a model the Information Functions available are GETUSERID and GETUSERCULTURE. Is it possible to modify how these work and/or create additional functions here???
Thanks in advance for your help.
Cheers,
Todd.
SQL Server 2008 :: Change Text Format From Case Sensitive To Case Insensitive?
Aug 31, 2015How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies View RelatedCase Insensitivity Is On Server Wide: Tables Render Case Sensative...
Jan 6, 2005Hello:
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Any suggestions?
Case Insensitive Searching In Sql Server 2000 When It's Case Sensitive
May 4, 2007Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
HELP! Case Insensitive Database On Case Sensitive Server
Aug 17, 2005We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies View RelatedDoing A Case-sensitive Query In A Case-insensitive Database
May 29, 2008I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob
Transact SQL :: Upper Case To Lower Case Conversion
May 4, 2015I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies View RelatedCan You Use Replication From A Case Sensitive Db To A Case Insensitive Db?
Aug 19, 2007I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
View 2 Replies View RelatedProblem Using Result From CASE In Another CASE Statement
Nov 5, 2007I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
Thanks,
Jason
Case Sensitivity When A User Enters Data Into The Database. How To Deal With Case Sensitivity.
Sep 6, 2007I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.
Restore Of Case Insensitive Database To A Case Sensitive Database - SQL Server 2000
Jul 20, 2005Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
View 2 Replies View RelatedNeed Help With A CASE When
Aug 28, 2007I just can't get the case to work for me in this view.
CASE WHEN Isnull(dbo.Payments.AmountPaid,'No') THEN 'No' WHEN dbo.Payments.AmountPaid >0 THEN 'Yes' END AS Payment_StatusThe error I get is:
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
ALTER VIEW [dbo].[AffiliationPayments]ASSELECT dbo.Affiliations.AffiliationType, dbo.Affiliations.AffiliationDescription, dbo.Affiliations.AffiliationEnd, dbo.Payments.PaymentDate, dbo.Payments.AmountPaid, dbo.Affiliations.Client_ID,CASE WHEN Isnull(dbo.Payments.AmountPaid,'No') THEN 'No' WHEN dbo.Payments.AmountPaid >0 THEN 'Yes' END AS Payment_StatusFROM dbo.Affiliations LEFT OUTER JOIN dbo.Payments ON dbo.Affiliations.Client_ID = dbo.Payments.Client_ID
CASE,IF,etc??
May 8, 2006I have a deadline that is set ahead of time and will not change. I need to insert this deadline for all new users, so I want to set in the DB and I have tried to insert the deadline into the db using a case(below)
Error -2147217900
Error -2147217900
Incorrect syntax near the keyword 'CASE'.
ALTER PROCEDURE prcStartWeeks @UserID VARCHAR(50) ASSET NOCOUNT ONDECLARE @Result INTDECLARE @ID INTDECLARE @Weeks INTDECLARE @DeadLine VARCHAR(8)SET @ID = 0SET @Weeks = 1SELECT @ID = ISNULL((SELECT peID from tblUserInfo where UserID = @UserID),0)IF @ID = 0 BEGIN SELECT @Result = -1 ENDELSE BEGIN WHILE @Weeks < 18 BEGIN CASE @Weeks WHEN '1' THEN @DeadLine = '5/9/2006' WHEN '2' THEN @DeadLine = '9/15/2006' WHEN '3' THEN @DeadLine = '9/22/2006' WHEN '4' THEN @DeadLine = '9/29/2006' WHEN '5' THEN @DeadLine = '10/6/2006' WHEN '6' THEN @DeadLine = '10/13/2006' WHEN '7' THEN @DeadLine = '10/20/2006' WHEN '8' THEN @DeadLine = '10/27/2006' WHEN '9' THEN @DeadLine = '11/3/2006' WHEN '10' THEN @DeadLine = '11/10/2006' WHEN '11' THEN @DeadLine = '11/17/2006' WHEN '12' THEN @DeadLine = '11/21/2006' WHEN '13' THEN @DeadLine = '11/28/2006' WHEN '14' THEN @DeadLine = '12/5/2006' WHEN '15' THEN @DeadLine = '12/12/2006' WHEN '16' THEN @DeadLine = '12/19/2006' WHEN '17' THEN @DeadLine = '12/28/2006' END INSERT INTO tblUserWeekly(PEID,WeekID,Points,DeadLine) VALUES(@ID,@Weeks,0,@DeadLine) SET @Weeks = @Weeks + 1 END SELECT @Result = 0 END
Between W/case
Aug 26, 2002I want to use case in my date range to get last weeks range. I have a similar proc that uses variables, but my application UI will not allow this in production.
Error incorrect syntax near '=' on line 15
-- begin
select
Company
,Carrier
,Client
,DatePaid
,DateBilled
,cast(PremiumReceived as money)
,cast(PolicyAmount as money)
from view_billing
where
cast(datebilled as datetime) between
(cast(datebilled as datetime) =
case
when datepart(dw,getdate()) = 2 then getdate()-8
when datepart(dw,getdate()) = 3 then getdate()-9
when datepart(dw,getdate()) = 4 then getdate()-10
when datepart(dw,getdate()) = 5 then getdate()-11
when datepart(dw,getdate()) = 6 then getdate()-12
when datepart(dw,getdate()) = 7 then getdate()-13
end)
and
cast(datebilled as datetime) =
case
when datepart(dw,getdate()) = 2 then getdate()-1
when datepart(dw,getdate()) = 3 then getdate()-2
when datepart(dw,getdate()) = 4 then getdate()-3
when datepart(dw,getdate()) = 5 then getdate()-4
when datepart(dw,getdate()) = 6 then getdate()-5
when datepart(dw,getdate()) = 7 then getdate()-6
end
and and company like '%Comp%'
-- end
Case
Apr 27, 2005is it possible to test two fields in a case statement in SQL ?
View 3 Replies View RelatedIf Else Or Case When, Then
Dec 14, 2004I have a query using Transact-SQL and it is moderately complex. I was wondering if someone could tell me if it would be better to use IF...ELSE statements or CASE, WHEN...THEN statements.
What I have is something like this...
Code:
SELECT
something something
code = CASE
WHEN something IN (A list of things)
IF something else
THEN 4
I have just though of doing IF...ELSE statements to keep it simply, but this query needs to run in a relative quick time frame. I guess my question is, is IF...ELSE faster or is it faster to use a CASE statement?
Case Within Having ?
Sep 30, 2005Hi all,
I tried the following code and obviously doesnt work, is there a way to simulate a case within having?
CREATE TABLE LTG (Name VARCHAR(10), B1 INT, B2 INT, B3 INT);
INSERT INTO LTG VALUES
('Luis',1,0,0)
INSERT INTO LTG VALUES
('Hector',0,1,0)
INSERT INTO LTG VALUES
('Alejandro',0,0,1)
DECLARE @S INT
SET @S = 1
SELECTName,
SUM (B1),
SUM (B2),
SUM (B3)
FROMLTG
GROUP BY Name
HAVING
CASE @S
WHEN 1 THEN SUM (B1) != 0
WHEN 2 THEN SUM (B1) != 0
WHEN 3 THEN SUM (B1) != 0
END
Thanks for your help
Luis Torres
Sum With Case
Nov 1, 2005How do I get 1 sum out of the following varchar fileld?
Select 'USER_LEVEL' =
CASE
WHEN USER_LEVEL = 'Unlimited' THEN 0
ELSE USER_LEVEL
END
From Table1
Help With CASE And LIKE
Sep 4, 2007Hello guys! i'm having problem with my stored procedure..can anybody please help me.
I have a stored procedure below that is successfully executed/saved/"Compiled"(whatever you called it) but when I try to use it by supplying value to its paramaters it throws an error (Please see the error message below). I suspected that the error occurs from line with the Bold Letters becuase "@SeacrhArg" variable is of type varchar while columns "Transac.Item_ID" and "Transac.Item_TransTicketNo" is of type Int. What you think guys?
ERROR:
Msg 245, Level 16, State 1, Procedure sp_Transaction_Search, Line 9
Syntax error converting the varchar value 'Manlagnit' to a column of data type int.
STORED PROCEDURE:
USE [RuslinCellPawnShoppeDB]
GO
/****** Object: StoredProcedure [dbo].[sp_Transaction_Search] Script Date: 09/04/2007 08:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Transaction_Search]
@SeacrhArg varchar(20),
@SearchBy varchar(20),
@TransType varchar(20),
@FromDate datetime,
@Todate datetime
AS
BEGIN
SELECT Customer.Customer_LastName,Customer.Customer_Middl eInitial, Customer.Customer_FirstName, Customer.Customer_Address,
Items.Item_Description,Items.Item_Principal, Transac.ItemTrans_Date_Granted, Transac.ItemTrans_DateCreated,
Transac.ItemTrans_Status, Transac.Item_ID,Transac.Item_TransID,Transac.Item_ TransTicketNo
FROM RCPS_TF_ItemTransaction Transac
INNER JOIN RCPS_Customer Customer
ON Transac.CustomerID = Customer.CustomerID
INNER JOIN RCPS_Items Items
ON Items.ItemID = Transac.Item_ID
WHERE
CASE
WHEN @SearchBy = 'FirstName' THEN Customer.Customer_FirstName
WHEN @SearchBy = 'LastName' THEN Customer.Customer_LastName
WHEN @SearchBy = 'Item ID' THEN Transac.Item_ID
WHEN @SearchBy = 'Ticket No' THEN Transac.Item_TransTicketNo
END
LIKE @SeacrhArg AND
Transac.ItemTrans_DateCreated BETWEEN @FromDate AND dateadd(day,1,@Todate) AND
(
(@TransType = 'Pawned' AND Transac.ItemTrans_Status = 1) OR
(@TransType = 'Renewed' AND Transac.ItemTrans_Status = 2) OR
(@TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3) OR
(@TransType = 'Sold' AND Transac.ItemTrans_Status = 5)
)
END
CALL STORED PROCEDURE
USE [RuslinCellPawnShoppeDB]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_Transaction_Search]
@SeacrhArg = '%man%',
@SearchBy = 'LastName',
@TransType = 'Pawned',
@FromDate = N'9/01/2007 12:00:00 AM',
@Todate = N'9/6/2007 12:00:00 AM'
SELECT'Return Value' = @return_value
GO
Case Within A Case
Jan 21, 2004I was just wondering if it is possible to have nested case statements (case within a case). If so, what would the syntax be? Here is what I have now:
case when isdate(my_date)=1 then convert(char(10),convert(datetime,cast(my_date as varchar(8))),101)
else null end "my_date"
I need to put another case outside this one. Any ideas?
Thanks.
CASE Within A WHERE
Mar 4, 2015I'm trying to put something like below within a where clause.I have a parameter @Units which can have one of the values on YES, NO or BOTH. The Idea is
if @Units = Yes I only want USER_DEFINED_DATA.CAD213 = 'on'
if @Units = No I only want USER_DEFINED_DATA.CAD214 = 'on'
IF @UNITS = 'Both' .... I want both
[code]....
Help With IF/ELSE Or Case
Apr 8, 2008I am trying to write the following using either an if/else or case, any help would be aprreciated.
Set the OPPORTUNITY.account_manager_override to false when the COMPANY.account_manager = 'xxx'. In all other cases, set the OPPORTUNITY.account_manager_override to true. Sounds simple enough....
This may help:
select opportunity_name, o.account_manager_id opp_manager, o.account_manager_override opp_override,
co.account_manager_id company_manager, co.account_manager_override company_override
from opportunity o
inner join company co on co.company_id = o.company_id
where co.account_manager_id = xxx
Case Else Help
Apr 10, 2008hi,
SELECT CASE WHEN data_Flag ='5' THEN Avg_Binaryvalue ELSE NULL END AS Avg1,
CASE WHEN data_Flag ='5' THEN Min_Binaryvalue ELSE NULL END Min1,
CASE WHEN data_Flag ='6' THEN Avg_Binaryvalue ELSE NULL END AS Avg2,
CASE WHEN data_Flag ='6' THEN Min_Binaryvalue ELSE NULL END AS Min2
FROM MeasurementData where sensor_serial_no='4' and month(date_time)='3'
this is my qrery.
i have 14 rows for data_flag =5 and 14 rows for data_flag=6
so if i use this query i am getting 26 rows.
my current output:
avg1 min1 avd2 min2
null null 10 20
23 24 null null
null null 54 57
43 12 null null
ets
my output should be like this:
avg1 min1 avd2 min2
23 24 10 20
43 12 54 57
so please give me sample query for this please
Case Help
May 22, 2008Hi guys,
Bit of background on this query... daily report that tells me each product we sell that we were unable to supply to our customers.
What I need to include is the earliest unfulfilled purchase order and the date that purchase order is due for delivery into our warehouse.
I've used a case statement with MIN to get the earliest unfulfilled PO (t9.docnum), but I have been unable to get the due date (t9.docduedate) to show correctly... it just gives me the due dates of every unfulfilled purchase order, and doesn't link the purchase order returned by the case statement. Any ideas? Thanks!
SELECT t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname ,
MIN(
CASE
WHEN t8.linestatus = 'O'
THEN t9.docnum
ELSE NULL
END) AS 'PO'
FROM
(SELECT t0.product product ,
SUM(
CASE
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
ELSE 0
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-1, 101)
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
LEFT JOIN
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry
WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname
ORDER BY t2.u_vlgx_plc,
t6.cardname ,
t3.product
Using Sql Case
Jun 8, 2008Hi,
am using case in sql, my query is below..
select eC_ID,
case eC_FormID
when '1' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'ITMGR')
when '2' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS')
when '3' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FCTL')
else 'None'
end
from eC_Master
My question is how do I use a case inside a case statement in sql. i.e., when case is '2' .,for below line.,
when '2' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS')
I need to check first EMPLOYEE_DIVISION..If its 'FIN',then i need to execute below query,
(SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FVP')else i need to execute,
when '2' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS')
So i have to use 2 tables..one is eC_Master and another Users. Can i use like below.
select A.eC_ID,
case A.eC_FormID
when '1' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'ITMGR')
when '2' then
case B.EMPLOYEE_DIVISION
WHEN 'FIN' THEN
(SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FVP') ELSE
(SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS') END
when '3' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FCTL')
else 'None'
end
from eC_Master A inner join Users B on A.eC_Requestor_ID=B.EMPLOYEE_ID
THANKS TO HELP.
CASE In SP
Nov 26, 2005hi i didnt know SQL server could use case statements to assign a string that can be returned so it can be displayed. I have the following SP:
CREATE PROCEDURE GetProductsInCategory
(@CategoryID int)
AS
SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath,
Product.OnDepartmentPromotion, Product.OnCatalogPromotion, Product.ImageALT,
Product.Stock, Artist.[ArtistName], AlbumSingleDetails.CoverQualityID, AlbumSingleDetails.QualityID
FROM (Product INNER JOIN
(Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID=AlbumSingleDetails.ArtistID)
ON Product.ProductID=AlbumSingleDetails.ProductID)
INNER JOIN ProductCategory ON Product.ProductID=ProductCategory.ProductID
WHERE ProductCategory.CategoryID=@CategoryID
ORDER BY Artist.[ArtistName], Product.Name
RETURN
not sure how to add the CASE ini have tried the following:
SQLCoverQuality = CASE AlbumSingleDetails.CoverQualityID
WHEN AlbumSingleDetails.CoverQualityID = 1 THEN 'MINT NEW'
WHEN AlbumSingleDetails.CoverQualityID = 2 THEN 'Excellent'
WHEN AlbumSingleDetails.CoverQualityID = 3 THEN 'Very Good'
WHEN AlbumSingleDetails.CoverQualityID = 4 THEN 'Good'
ELSE 'Unknown'
END
SQLQuality = CASE AlbumSingleDetails.QualityID
WHEN AlbumSingleDetails.QualityID = 1 THEN 'MINT NEW'
WHEN AlbumSingleDetails.QualityID = 2 THEN 'Excellent'
WHEN AlbumSingleDetails.QualityID = 3 THEN 'Very Good'
WHEN AlbumSingleDetails.QualityID = 4 THEN 'Good'
ELSE 'Unknown'
END
i get a syntax error re the CASE and = if i remove it!!! any ideas? also would i not have to declare the two fields i have created? SQLQuality? and SQLCoverQuality?
Can anyone help?
Tuppers
WHERE CASE?
Feb 16, 2006I want to pass four variables to a stored procedure all which default to NULL
Example:
CREATE PROCEDURE spSOMETHING
(@varA nvarchar(50)=NULL,
@varB nvarchar(50)=NULL,
@varC nvarchar(50)=NULL,
@varD nvarchar(50)=NULL)
AS
SET NOCOUNT ON
-- Pseudocode:
--IF varA IS NOT NULL
-- SELECT * FROM table WHERE varA = @varA
-- elseif varB IS NOT NULL
-- SELECT * FROM table where varB = @varB
-- elseif varC IS NOT NULL
-- SELECT * FROM table where varC = @varC
-- else
-- SELECT * FROM table where varD = @varD
What I've done is create 4 stored procedures and I'll call each one going through a daisychain of IF...ELSE statements (there is no actual ELSEIF in the code, as I don't know if this is valid in T-SQL). I'd like to just have the code from the other 4 stored procedurs in this one, conditionally selecting WHERE clauses. I'd even like to be able to select multiple if possible (i.e. WHERE varA=@varA AND varD=@varD if varA and varD are not null and varB and varC are not passsed).
Any ideas?
Using Sql Case
May 18, 2006I need to get the data in sorted form from a column of type varchar but stores different datatypes(numeric,string,data) (only one kind of data) i tried to use case how ever its not working . even for numeric data it ineterprets as datetime . here is my sqal query
select case when DistinctValue in ('[NULL]', '[SPACES]','[EMPTY]') then null
when IsNumeric(DistinctValue) = 1 then cast(DistinctValue as float)
when IsDate(DistinctValue) = 1 then cast(DistinctValue as datetime)
else null
end as distValue, DistinctValue as actualVal from table order by 1
CASE Help?
Jan 23, 2007hi,
I want to use 2 fields(result(varchar),deep(decimal)) of my table in a case statement. But it did NOT work. Would you please help me?
...
case
when s.result ='E' then 'E'
when s.result ='A' then 'GELMEYEN'
case
when s.deep < 1.0 then 'MDL-0.99'
when s.deep between 1.0 and 4.9 then '1.0-4.9'
when s.deep between 5.0 and 9.9 then '5.0-9.9'
when s.deep between 10.0 and 14.9 then '10.0-14.9'
when s.deep between 15.0 and 19.9 then '15.0-19.9'
when s.deep between 20.0 and 29.9 then '20.0-29.9'
when s.deep > 30.0 then '>30.0'
when s.deep > 0.1 then 'MDL'
END as SONUC
....
CASE Help
Mar 11, 2007hi, syntax question: i'm getting compile errors from this function (messages point to the CASE but it may be the preceding IF statement) and can't figure out what's wrong. I think I need some fresh eyes....if anyone can figure out what I'm doing wrong I would be indebted to you.
thanks and regards,
IP
CREATE FUNCTION hasSIC(@fac_id INT, @sic CHAR(4), @type INT)
RETURNS INT
AS
BEGIN
DECLARE @amount INT
SET @amount = 0
IF @fac_id IN (SELECT f.fac_id FROM fac_sic_dimension f JOIN sic_dimension s ON f.sic_id = s.sic_id WHERE s.sic_code = @sic)
BEGIN
CASE @type
WHEN 1 THEN SET @amount = (SELECT total_release FROM air_releases WHERE fac_id = @fac_id)
WHEN 2 THEN SET @amount = (SELECT total_release FROM land_releases WHERE fac_id = @fac_id)
WHEN 3 THEN SET @amount = (SELECT a_amount + b_amount + c_amount + d_amount + e_amount + f_amount FROM water_releases
WHERE fac_id = @fac_id)
END
END
RETURN @amount
END;
Case When
Mar 11, 2007Hi all. what is wrong with my query?
DECLARE @empDcno varchar(20), @secnum int
set @empDcno = '205'
set @secnum = 1
select l.* , e.paytype
from hrempleaveledger as l
inner join hremployees as e on l.empdcno = e.empdcno
inner join hrsetbenefitsleave as b on l.type = b.type
inner join mscsections as s on e.sectioncode = s.sectioncode
inner join mscdivisions as d on e.divisioncode = d.divisioncode
inner join mscbranches as br on e.branchcode = br.branchcode
inner join CSVTable(@empDcno) as R on e.empdcno = R.stringval
where e.sectioncode =
case @secnum
when 1 then '1001'
when 2 then '1002'
else ''
end
did i use the case/when statement properly? if not, how could i query this in correct format?
Edit .:. this is my changes. still not working...
Edit .:. Ok got it...thanks. @secnum instead.
Thanks.
-Ron-
Help With CASE
Apr 18, 2007I am trying to write a query that needs a decision, probably using CASE.
Here is the problem.
There are 2 observation posts, one of which measures water level only, while the other measures water level and water temperature.
My table looks like this:
date, post, data_kind, value
2007/04/19 12:45, A, 1, 999
2007/04/19 12:45, B, 1, 998
2007/04/19 12:45, B, 2, 19
2007/04/19 13:55, A, 1, 996
2007/04/19 14:00, B, 2, 20.5
data_kind 1: water level, 2: temperature
The end result should look like this:
date, A_water_level,B_water_level,B_water_temp
2007/04/19 12:45, 999, 998, 19
2007/04/19 13:55, 996, null, null
2007/04/19 14:00, null, null, 20.5
(I used comma to separate data, so that it might be easier to read(?))
How do I start? Can anyone give me a hint, please?
Thank you.