I am trying to use the 'case' statement in a view. It looks like this.
select column1,column2
case when substring(column3,1,2) like '01' then my_column ='city_name'
end
from table1
I keep getting an incorrect syntax near 'like'. column1 and column2 exist in the table that the view is dependent on, but my_column is a virtual column, meaning I am creating this column on the fly.
Thanks
I have a view where I'm trying to populate a field base on the values on the USEJSLOAD field. The USEJSLOAD it's a (bit,null) field and there are only two types of values on it they are 'True' or 'False' so if it's true grab the data from one field else the other field. When I try to run the view I get the following error, 'Conversion failed when converting the varchar value '1-1S ' to data type int. Any help would be greatly be appreciated.
(CASE WHEN dbo.ftlibr_base.USEJSLOAD = 1 THEN dbo.trn_transport.TRN_LOADNODERIVED ELSE trn_transport.TRN_DISPATCHNO END)
Sample data:
ftlibr_base
Work_Order | USEJSLOAD
1 | True
2 | False
3 | True
dbo.trn_transport
Work_Order | TRN_LOADNODERIVED | TRN_DISPATCHNO
1 | 1-1S |1
2 | 4-2S |10
3 | 1-30S |2
I would like:
Work_Order | Load #
1 | 1-1S
2 | 10
3 | 3-20S
have created a view in MS SQL2000 as followed:
Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;
my question is: If the email @ sign exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the @ sign does not exist then I would need the EMAILflag to be a N.
This what I have but the emailaddress column is not always null it just has spaces in the field.
Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress, CASE WHEN emailaddress IS NULL THEN 'N' ELSE 'Y' END AS emailflag
FROM orders;
Any HELP would be GREAT.
Thank You!!
I am executing a case statement list below,USE NorthwindSELECTMONTH(OrderDate) AS OrderMonth,SUM(CASE YEAR(OrderDate)WHEN 1996 THEN 1ELSE 0END) AS c1996,SUM(CASE YEAR(OrderDate)WHEN 1997 THEN 1ELSE 0END) AS c1997,SUM(CASE YEAR(OrderDate)WHEN 1998 THEN 1ELSE 0END) AS c1998FROM OrdersGROUP BY MONTH(OrderDate)ORDER BY MONTH(OrderDate)According to BOL I should be able to save this query as a view.However when I try to save the query as a view I get a error messagestating"View definition includes no output columns or includes no items inthe FROM clause"According to what I have read although the case statement is notsupported via the enterprise query pane, the query should still runand be saved. In my case however I cannot seem to save it no matterwhat I try.Can anyone shed any light on the matter?Thanks in advanceBryan
View 3 Replies View RelatedCASE is not supported Error in a View
In SQL 7, when a view parses the below code, it generates a 'CASE is not Supported' error'; however, it will still generate and show the field with the CASE statements correctly; What might cause this error?
(Other useful info: this code is from a converted SQL 6.5 DB and the SQL 7 is running in compatibility mode, i inserted '' to remove concantenation of NULL problems;)
Example of display:
10132 Hampton, VA: A. Deepak Publishing
SELECT PublisherID, Info = CASE WHEN City IS NOT NULL
THEN City + CASE WHEN State IS NOT NULL
THEN ', ' + State ELSE '' END + ': ' ELSE ''
END + Publisher
FROM tblLibraryPublishers
Thank you!
Llyal
The below would work if all of the values in A.Value were numbers but they are not. So I need to restrict the view to only look at the following measures but still show all the other row.
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07')
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
[Code] ....
Is there any way that I can create a select statement in the case when to only look for them measures that I know contain numbers?
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies View RelatedHello:
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?
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.
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 RelatedI 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
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 RelatedI 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 RelatedI 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
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?
View 2 Replies View RelatedI am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedWrite a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedI had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)
View 1 Replies View RelatedA colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.
Can anyone explain why?
Thanks
-Jamie
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
and and company like '%Comp%'
-- end
is it possible to test two fields in a case statement in SQL ?
View 3 Replies View RelatedI 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?
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
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
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
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.