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:
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?
Can 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?
We 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 ????
I 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?
I 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!
I 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?
I 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.
Yesterday 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?
I 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
I 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
I 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
I 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?
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
Hello 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
I'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
I 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
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
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
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?
-- 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).
I 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
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 ....
hi, 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
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?
-- insert this case inside --case: --when @type = 'daily' then invoice_date = today --when @type = 'monthly' then month(invoice_date) = thisMonth and year--(invoice_date) = thisYear --when @type = 'yearly' then year(invoice_year) = thisYear
i want to filter the result by giving the user to view for daily, monthly and yearly. And i'm not sure too if the switch-case is suitable or there are other better solution for this. thx
