Case When

Mar 11, 2007

Hi 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-

View 20 Replies


ADVERTISEMENT

SQL Server 2008 :: Change Text Format From Case Sensitive To Case Insensitive?

Aug 31, 2015

How can I change my T-SQL text editor from text sensitive to text insensitive?

View 2 Replies View Related

Case Insensitivity Is On Server Wide: Tables Render Case Sensative...

Jan 6, 2005

Hello:

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?

View 4 Replies View Related

Case Insensitive Searching In Sql Server 2000 When It's Case Sensitive

May 4, 2007

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?
 
thanks in advance.

View 3 Replies View Related

HELP! Case Insensitive Database On Case Sensitive Server

Aug 17, 2005

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 ????

View 4 Replies View Related

Doing A Case-sensitive Query In A Case-insensitive Database

May 29, 2008

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?

Jacob

View 5 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

I 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 Related

Can You Use Replication From A Case Sensitive Db To A Case Insensitive Db?

Aug 19, 2007

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! 

View 2 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

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?

Thanks,

Jason

View 1 Replies View Related

Case Sensitivity When A User Enters Data Into The Database. How To Deal With Case Sensitivity.

Sep 6, 2007

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.

View 2 Replies View Related

Restore Of Case Insensitive Database To A Case Sensitive Database - SQL Server 2000

Jul 20, 2005

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?

View 2 Replies View Related

Need Help With A CASE When

Aug 28, 2007

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 

View 2 Replies View Related

CASE,IF,etc??

May 8, 2006

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

View 3 Replies View Related

Between W/case

Aug 26, 2002

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

and and company like '%Comp%'
-- end

View 3 Replies View Related

Case

Apr 27, 2005

is it possible to test two fields in a case statement in SQL ?

View 3 Replies View Related

If Else Or Case When, Then

Dec 14, 2004

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?

View 6 Replies View Related

Case Within Having ?

Sep 30, 2005

Hi 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

View 2 Replies View Related

Sum With Case

Nov 1, 2005

How 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

View 1 Replies View Related

Help With CASE And LIKE

Sep 4, 2007

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


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

View 4 Replies View Related

Case Within A Case

Jan 21, 2004

I 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.

View 14 Replies View Related

CASE Within A WHERE

Mar 4, 2015

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

[code]....

View 2 Replies View Related

Help With IF/ELSE Or Case

Apr 8, 2008

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

View 4 Replies View Related

Case Else Help

Apr 10, 2008

hi,


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

View 7 Replies View Related

Case Help

May 22, 2008

Hi 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

View 2 Replies View Related

Using Sql Case

Jun 8, 2008

Hi,
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.

View 3 Replies View Related

CASE In SP

Nov 26, 2005

hi 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

View 4 Replies View Related

WHERE CASE?

Feb 16, 2006

I 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?

View 4 Replies View Related

Using Sql Case

May 18, 2006

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

View 18 Replies View Related

CASE Help?

Jan 23, 2007

hi,

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
....

View 4 Replies View Related

CASE Help

Mar 11, 2007

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

RETURN @amount
END;

View 1 Replies View Related

Help With CASE

Apr 18, 2007

I 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.

View 3 Replies View Related

If Or Case...

May 11, 2007

Hi
I wanna select a load of data ..

but in case of a null i wanna replaced it with certain data ..

so its something like
SELECT
x if null then '',
y if null then '',
z if null then '',
FROM table_name

what is the exact syntax ?
cheers..

View 14 Replies View Related

Where And Case

Jun 14, 2007

can someone help me to solve this sql statement?

where
invoiceSum >= 1000
and

-- 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

View 6 Replies View Related







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