Wrong Syntax In Where Smth In Case ?

Jul 23, 2005

Hello all,

I belive, my problem is probably very easy to solve, but still, I
cannot find solution:

declare @iint
declare @zint


create table bubusilala (
[bubu] [int] NOT NULL ,
[gogo] [int] NOT NULL ,
[lala] [varchar] (3) NOT NULL )

insert into bubusilala (bubu,gogo,lala) values (1,2,'ala')
insert into bubusilala (bubu,gogo,lala) values (10,20,'aca')
insert into bubusilala (bubu,gogo,lala) values (100,200,'bbb')
insert into bubusilala (bubu,gogo,lala) values (11,21,'ccc')
insert into bubusilala (bubu,gogo,lala) values (12,22,'abc')
insert into bubusilala (bubu,gogo,lala) values (13,23,'cbd')

set @i = 10
set @z = 2

select * from bubusilala
where bubu in (
case when @i > @z then (1,2)
when @i < @z then (10,13) end)
and gogo like '%a%'

I get error, that statement is wrong in case near ','.
I supose, it is not possible, to get from case a group of values.
But why then, this works:


select * from bubusilala
where bubu in (
case when @i > @z then (1)
when @i < @z then (select gogo from bubusilala) end)
and gogo like '%a%'

This data are totaly simplified.

agrh ... any ideas??

Thank You in advance,

Mateusz

View 3 Replies


ADVERTISEMENT

What Is Wrong With This Syntax?

May 21, 2006

Hello
I am trying to insert a hyperlink to display on my web site, via the database, for example:
http://www.tressleworks.ca/Modules/SQLGridSelectedView/LiveExamples/Example4Links/tabid/91/Default.aspx
Name of my table is:  Biz_Names
Cols:
ID_numberFNameLNameBusiness
I know that this works:
SELECT FName, LName, BusinessFROM BizNamesWHERE FName = 'John'
ORDER BY LName ASC
I want to add a hyperlink and I have since included extra columns:
ID_numberFNameLNameBusinessurltitleLink

I want to add the hyperlink from the url column and use the title from the title column to fill the Link column, I don't want to include the url and the title column on the site.
I only want to display Link data on the site, but so far my syntax is not working:
SELECT FName, LName, Business, '<a href='" + url '"> + Title +'</a>' as LinkFROM BizNamesWHERE FName = 'John'
And url like 'http:%'
ORDER BY LName ASC
What am I doing wrong?
Thanks
 
Regards
Lynn
 
 

View 2 Replies View Related

What's Wrong With This Having Syntax

Jul 28, 2007

It works in ASP-Access, but not on the Unix sql:

SELECT SUM(result.pt) as SumOfpt FROM result,team GROUP BY sref,tref,team.id HAVING sref=2 and team.id=tref;

it says - and only on the unix server:

SQLState: 4 Native Error Code: 1054 [TCX][MyODBC]Unknown column 'sref' in 'having clause'
/test/teamstandings.asp, line 20

View 11 Replies View Related

What Is Wrong W/ This Syntax?

May 24, 2007

"*** " + @[System::MachineName] + " - SSIS Package Error - [ " + @[System:ackageName] + " ] @ " + (DT_STR, @[System::ContainerStartTime]) + " ***"



Get this error:



TITLE: Expression Builder
------------------------------

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Attempt to parse the expression ""*** " + @[System::MachineName] + " - SSIS Package Error - [ " + @[System:ackageName] + " ] @ " + (DT_STR, @[System::ContainerStartTime]) + " ***"" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

(Microsoft.DataTransformationServices.Controls)

------------------------------
BUTTONS:

OK
------------------------------



Thanks

View 3 Replies View Related

Whats Wrong With This Syntax..

Oct 2, 2007

CASE field_val
WHEN 'M5' THEN EXEC ( @query)
ELSE THEN print ''
END



I have a SP , and i state a query .. but i only
want this query to be executed based on the value
of field_val , otherwise i print a blank ..
but when i check the syntax (ms sql server 2000)
i get "invalid syntax near CASE ..

View 5 Replies View Related

SQL Syntax Returning Wrong Records

Nov 23, 2007

Hi
I have a table called DiaryDate2 and a query called DiaryDateOver8 and want to have a sql query to return the records from the table that are not in the query based on two linked fields username and Diarydate1
The SQL I have written however returns all records
SelectCommand="SELECT  DiaryDate2.Username, DiaryDate2.DiaryDate1 FROM DiaryDate2
WHERE(DiaryDate2.Username = ?)AND NOT EXISTS (SELECT 1 from DiaryDateover8 where DiaryDateover8.Username=DiaryDate2.Username AND DiaryDateover8.DiaryDate1=DiaryDate2.DiaryDate1)ORDER BY DiaryDate2.DiaryDate1">
Please could someone help
Many thanks Colin

View 5 Replies View Related

Creating SQL View - Syntax Is Wrong

Apr 14, 2004

We are creating a view in SQL to format data to DTS to another database. Here is the section of the view which does not work -- eliminating this section from the query allows the view to be created:

CASE APTran.LineNbr
WHEN < '0' THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN > '0' THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,


I am attaching the whole query stmt here.

Thanks very much for all assistance.

View 6 Replies View Related

Syntax Error, Whats Wrong??

Mar 21, 2007

Hi heres my script, why does it not work?

CREATE PROC [dbo].[sp_append_CemexSQLDB_to_EQuIS5_V2]
@DBINnvarchar(50),
@DBOUTnvarchar(50)

AS

DECLARE @SQLStrVarchar(1000)

/* Start Transaction, incase of error */
BEGIN TRAN

/* Appending Ref tables */
SET @SQLStr = 'INSERT INTO ' + QUOTENAME(@DBOUT) '.dbo.rt_action_level_type'
SET @SQLStr = 'SELECT *'
SET @SQLStr = 'FROM ' + QUOTENAME(@DBIN) '.dbo.rt_action_level_type;' + ' OPTION (KEEP PLAN)'
EXEC (@SQLStr)
IF @@ERROR <> 0 GOTO ERRORHANDLER
COMMIT TRAN

RETURN 0

ERRORHANDLER:
PRINT "Unexpected Error Ocurred!"
ROLLBACK TRAN

RETURN 1
GO

The error is 'Server: Msg 170, Level 15, State 1, Procedure sp_append_SQLtables_to_NewDB_V2, Line 13
Line 13: Incorrect syntax near '.dbo.rt_action_level_type'.'


can anybody help

View 5 Replies View Related

What's The Case When I Run The Code Is Right In SQLEXPRESS2005 But Wrong In Sql2000

Mar 21, 2007

 Hi everyone
when i run the below code using sql2000 ,the error was occured,anyone can tell me what's wrong and how to solve it thanks
 the error is :
Appointed conversion is invalidation
InvalidCastException: Appointed conversion is invalidation]   PhotoManager.GetPhotos(Int32 AlbumID) +571
[TargetInvocationException: ]   System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0   System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +358   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29   System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +482   System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2040   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70   System.Web.UI.WebControls.GridView.DataBind() +4   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69   System.Web.UI.Control.EnsureChildControls() +87   System.Web.UI.Control.PreRenderRecursiveInternal() +41   System.Web.UI.Control.PreRenderRecursiveInternal() +161   System.Web.UI.Control.PreRenderRecursiveInternal() +161   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
 
well i plaster the source's code in the below :
1γ€?the test.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="test.aspx.cs" Inherits="test" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">    <title>ζ— ζ ‡ι’˜ι‘΅</title></head><body>    <form id="form1" runat="server">    <div>        &nbsp;        &nbsp;&nbsp;<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1" AutoGenerateColumns="False">            <Columns>                <asp:BoundField DataField="AlbumCategoryID" HeaderText="AlbumCategoryID" ReadOnly="True"                    SortExpression="AlbumCategoryID" />                <asp:BoundField DataField="Caption" HeaderText="Caption" ReadOnly="True" SortExpression="Caption" />                <asp:BoundField DataField="LastModified" HeaderText="LastModified" ReadOnly="True"                    SortExpression="LastModified" />                <asp:BoundField DataField="ViewCount" HeaderText="ViewCount" ReadOnly="True" SortExpression="ViewCount" />                <asp:BoundField DataField="PhotoID" HeaderText="PhotoID" ReadOnly="True" SortExpression="PhotoID" />                <asp:BoundField DataField="Location" HeaderText="Location" ReadOnly="True" SortExpression="Location" />                <asp:BoundField DataField="AlbumID" HeaderText="AlbumID" ReadOnly="True" SortExpression="AlbumID" />            </Columns>        </asp:GridView>        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetPhotos"            TypeName="PhotoManager">            <SelectParameters>                <asp:Parameter DefaultValue="1" Name="AlbumID" Type="Int32" />            </SelectParameters>        </asp:ObjectDataSource>    </div>    </form>    </body></html>
 
2γ€?the PhotoManager's class is : public static List GetPhotos(int AlbumID)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("GetPhotos", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@AlbumID", AlbumID));
command.Parameters.Add(new SqlParameter("@IsPublic", 1));
connection.Open();
List list = new List();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{

Photo temp = new Photo(
(int)reader["PhotoID"],
(int)reader["AlbumID"],
(string)reader["Caption"],
(string)reader["Location"],
(int)reader["ViewCount"],
(DateTime)reader["LastModified"],
(int)reader["AlbumCategoryID"]
);

list.Add(temp);



}

}
return list;

}
}


}
 
 
3γ€?photo.cs is using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.IO;
using System.Web;
public class Photo
{

private int _id;
private int _albumid;
private string _caption;
private string _location;

private int _ViewCount;

private DateTime _LastModified;
private int _AlbumCategoryID;


public int PhotoID { get { return _id; } }
public int AlbumID { get { return _albumid; } }
public string Caption { get { return _caption; } }
public string Location
{
get
{
return _location;
}
}

public int ViewCount
{
get
{
return _ViewCount;
}
}

public DateTime LastModified
{
get
{
return _LastModified;
}
}

public int AlbumCategoryID
{
get
{
return _AlbumCategoryID;
}
}


public Photo(int id, int albumid, string caption, string location, int viewcount, DateTime lastmodified,int AlbumCategoryID)
{
_id = id;
_albumid = albumid;
_caption = caption;
_location = location;
_ViewCount = viewcount;
_LastModified = lastmodified;
_AlbumCategoryID = AlbumCategoryID;

}

}

4γ€?the StoredProcedure
"GetPhotos" is : ALTER PROCEDURE [dbo].[GetPhotos] @AlbumID int, @IsPublic bitAS SELECT [Photos].[AlbumID],[Photos].[PhotoID],[Photos].[Caption],[Photos].
[Location],[Photos].[ViewCount],[Photos].[LastModified], [Albums].[AlbumCategoryID]
 FROM [Photos] LEFT JOIN [Albums]  ON [Albums].[AlbumID] = [Photos].[AlbumID]   WHERE [Photos].[AlbumID] = @AlbumID  AND ([Albums].[IsPublic] =@IsPublic OR [Albums].[IsPublic] = 1)  ORDER BY [Photos].[Caption] ASC
 
RETURN 

View 1 Replies View Related

C# Ado.net MSDE: Wrong Syntax Near The User Keyword

Apr 11, 2004

************* Edited by moderator Adec ***************
Inserted missing < code></ code> tags. Always include such
tags when including code in your postings. Many readers
disregard postings without the code tags.
**************************************************

Hello,

I have a big problem with some piece of code. I am new to C# and ado.net.
My MSDE 2000 is set up and running. But the following code produces the error
(translated from german) like "wrong syntax near the user keyword"

The connection to the database works fine. But trying to create a table or to drop
a table alwas leads to the error. Whats wrong with it?

try
{
SqlConnection myConnection = new SqlConnection( "server=(local);" +
"Trusted_Connection=yes;" +
"database=ergofun; " +
"connection timeout=10");
SqlCommand sqlCommand = new SqlCommand();

sqlCommand.Connection = myConnection;
sqlCommand.CommandText = "CREATE TABLE user ( userid INT," +
"nickname CHAR(20),"+
"password CHAR(20),"+
"birthday DATETIME,"+
"weight INT,"+
"size INT,"+
"puls INT,"+
"name CHAR(20),"+
"surname CHAR(20))";

sqlCommand.Connection.Open();
sqlCommand.ExecuteNonQuery();
sqlCommand.Connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

View 1 Replies View Related

Table Scalar Function Syntax. . How Wrong And How Far Am I?

Aug 3, 2006

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION DetailedView

AS

BEGIN

Declare @fieldname varchar(10)

Declare @stmt varchar(4000)

Declare Fields Cursor For Select Amounttype From Amounttypes

Set @stmt = 'Select pono, myid, billtype'

Open Fields

Fetch Next From Fields Into @fieldname

While @@Fetch_Status = 0 Begin

Set @stmt = @stmt + ', sum(amountfc * Case When amounttype = ''' + @fieldname + ''' Then 1 Else 0 End) As ' + @fieldname

Fetch Next From Fields Into @fieldname

End

Close Fields

Deallocate Fields

Set @stmt = @stmt + ' From multiplebillsviewall Group By pono, myId,billtype '

return Exec(@stmt)

END

View 3 Replies View Related

Wrong Evaluation Of A Variable With Expression In Case It Is Used Simultaneously By Several Tasks

Aug 22, 2007



Hi guys,

I have experienced problem while trying to use variable with expression based on several other variables in tasks running parallel.

The details are as following:

There is a SSIS package with simple Control flow: one Script Task which actually do nothing and two Execute Process Tasks, they run after Script Task in parallel. Then there are three simple (EvaluateAsExpression = False) string variables ServerName, Folder and JobNumber with values ServerName = €œ\test€?, Folder = €œtest€? and JobNumber = €œ12345€?. And there is one variable FullPath with expression @[User:: ServerName] + "\" + @[User::Folder] + "_" + @[User::JobNumber]. All the variables are of the Package scope. Then in Execute Process Tasks I have similar expressions based on FullPath variable: Execute Process Task 1 has expression @[User::FullPath] + "\date.bat" and Execute Process Task 2 has @[User::FullPath] + "\time.bat" one. As you understand these expressions define what exactly task should execute.

Then I€™m going to execute package from command line so appropriate XML configuration file has been created. The file contains following values for variables described above: ServerName = €œ\LiveServer€?, Folder = €œJob€? and JobNumber = €œ33091€?.

After series of consequent executions I have got following log file:

€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\Test est_12345date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?



€¦



As you can see one of Execute Process Tasks usually receive correct value of the expression (based on values of variables from the configuration file) while another - incorrect one (based on €œdefault€? values of variables set directly in package). Sometimes wrong value appears in Task 1, next time in Task 2. Situations when both expressions in tasks evaluated correctly are very rare.

Then if you add some more Execute Process Tasks with similar expressions in the package (for ex. simply by copying existing tasks) you€™ll get a good chance to catch error like this:



OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073450774,0x,Reading the variable "User::FullPath" failed with error code 0xC0047084.



OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073647613,0x,The expression "@[User::FullPath] + "\time.bat"" on property "Executable" cannot be evaluated. Modify the expression to be valid.



Seems variable with expression FullPath is locked during evaluation by one of the parallel tasks in such a way that another task can€™t read it value correctly. Can someone help me with the issue? Maybe there are some options I missed which could prevent such behavior of application? Please let me know how I can make the package work correctly.

View 5 Replies View Related

Case Syntax Help

May 6, 2008

Hello i wrote this case statement to choose the password for a certain user but seems not to work. I have to get custom1 from another table.
select cast(custom1 as varchar(5)) from dbo.newhiresraw where id =userid) when custom1 'C%' then password = abcd4321 when not 'C%' then password = [postalcode] end [postalcode].

Not sure if custom1 = C then password =abcd4321
If custom1 not = C then it will be a zipcode.

not sure on the syntax any suggestions

View 4 Replies View Related

Problem With CASE Syntax?

Dec 18, 2001

I use the following statement:

SELECT OH.OHONR, CASE WHEN LV.LVTYP='VIL' THEN LV.LVT30 ELSE NULL END AS LE, CASE WHEN LV.LVTYP='SΔL' THEN LV.LVT30 ELSE NULL END AS LS INNER JOIN LV ON OHONR=LVONR

The result gives one row for each hit on EITHER of the CASE statements. (Two rows if both case stantements are true)
OHONR LE
OHONR LV

I want to have one row with the two fields after each other.
OHONR LE LV

I've also tried to use ' ' instead of NULL

How can I change the statement to achieve this!

/matss

View 3 Replies View Related

Problem With Case Syntax

Jan 9, 2001

Hi,
I require some help with a SP. I have written the following CASE
block but it is giving me an syntax error with Case statement.

Here @loc_id, @id_val and @inid are input parameters to the SP.

Basically what I am doing is I am selecting the column from table based on
the input parameter @loc_id I want to use Case and avoid IF statements as Case will reduce the execution time.

If any of you have faced such problem and have a sloution then do let me know.

-- SP section starts

CASE @loc_id
WHEN 1 THEN select @id_val = val1 from id_val where name = @inid
WHEN 2 THEN select @id_val = val2 from id_val where name = @inid
WHEN 3 THEN select @id_val = val3 from id_val where name = @inid
WHEN 4 THEN select @id_val = val4 from id_val where name = @inid
WHEN 5 THEN select @id_val = val5 from id_val where name = @inid
WHEN 6 THEN select @id_val = val6 from id_val where name = @inid
WHEN 7 THEN select @id_val = val7 from id_val where name = @inid
WHEN 8 THEN select @id_val = val8 from id_val where name = @inid
WHEN 9 THEN select @id_val = val9 from id_val where name = @inid
END
-- SP section ends

Thanks & Regards
____________________
Mukund Joshi
J.Mukund@zensar.com

View 1 Replies View Related

CASE Statement Syntax

Jun 21, 2000

Can someone tell me what is wrong with my syntax? I am getting several errors:

Incorrect syntax near keyword 'CASE'
Incorrect syntax near keyword 'WHEN'


Incorrect syntax near '@Tablename'


CREATE PROCEDURE al_readcampsignup


@User_ID int,


@Pagenumber smallint,


@Tablename varchar(10)


AS


CASE @Mypagenumber WHEN 1 THEN SELECT Blah FROM tempcampsignup WHERE Camp_ID = @User_ID WHEN 2 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID WHEN 3 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID END

Thanks

View 1 Replies View Related

Find No Case SQL Syntax

Mar 15, 2005

Im looking to strip all non numeric values out of a string at the SQL level in order to compare it to similar user entered data.

Something like

SELECT REReplace(PhoneNumber,"[^0-9]","","ALL") AS NewPN
FROM Numbers
WHERE NewPN = 'UserEnteredNumber'

View 3 Replies View Related

Help With Syntax (select Case)

Mar 4, 2008

Hi,

I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..

I have my SPROC configured as below, can anyone help me out as to why its not working?

btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)


IF @genderID > 2
..
ELSE
........WHERE genderID = @genderID


thanks very much once again!
mike123



CREATE PROCEDURE [dbo].[select_123]
(
@genderID tinyint
)

AS SET NOCOUNT ON


SELECT

CASE @genderID
WHEN 1 THEN

SELECT TOP 40 *

FROM tbl
WHERE .......


WHEN 2 THEN

SELECT TOP 40 *

FROM tbl
WHERE .......
ELSE
SELECT TOP 40 *

FROM tbl
WHERE .......



END

View 3 Replies View Related

CASE Statement Syntax Problem

Jan 16, 2008

I am attempting to mask a user's SS# based on Users.DisplaySSN. I have gone over the code time and time again and can't find where the error is.

Error I'm getting:
Incorrect syntax near the keyword 'AS'.

Here's where the error lies:
CASE
(SELECT DisplaySSN FROM Users WHERE Users.UserID = @UserID)
WHEN False THEN
'xxx-xx-xxxx' + right(SocialSecurityNumber,0) AS SocialSecurityNumber
ELSE
vwEmp.SocialSecurityNumber
END

View 6 Replies View Related

Syntax Check Needed On Case Statement

Feb 26, 2008

Hello experts!

I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....





Code Snippet

WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime)
>= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR
datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)


Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.


Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.


Any help is appreciated.

View 3 Replies View Related

SQL Server 2012 :: Incorrect Syntax Near Keyword CASE

Aug 4, 2015

I'm getting an error as "Incorrect syntax near the keyword 'CASE' ". When trying to run this query.

CREATE PROCEDURE dbo.SP_CDB_EA2
@RiskRef varchar(100)
--drop table #Tmp
AS
BEGIN

[code]....

View 9 Replies View Related

Transact SQL :: Finding Syntax To Combine IN And CASE In A WHERE Clause?

Oct 29, 2015

I cannot seem to find the syntax to combine IN + CASE in a WHERE clause

WHERE
ses.BK_MS_SESSION <= '2015-03'
AND vis.CAT_DRAW_STATUS =
(CASE ses.BK_MS_SESSION
WHEN '2015-03' THEN vis.CAT_DRAW_STATUS
ELSE
CASE stat.BK_MS_VISIT_STATUS
WHEN 'T' THEN 'X'
ELSE vis.CAT_DRAW_STATUS
END
END
) IN ('D','R')

View 7 Replies View Related

Newbie Case/search Question In Derived Column Expression Syntax

Feb 6, 2008



Hi everyone,

I am a newbie to SSIS and looking for answer to a simple search/replace style question.

I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.

A bit like a case statement:

CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S1' THEN 'Other sale items'
ELSE 'Not for sale'
END,


The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.

Thanks for all your help,

regards,

Chris

View 12 Replies View Related

SQL Server 2014 :: Case Syntax In Store Procedure Evaluating 2 Query Options

May 4, 2015

What will be the best way to write this code? I have the following code:

BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal

[Code] ...

then if Signal Sent.... do the following query:

Select * from package.....

if signal not sent don't do anything

How to evaluate the first query with the 2 options and based on that options write the next query.

View 2 Replies View Related

Newbie Search/replace Case Style Functionality In Derived Column Expression Syntax

Feb 6, 2008



Hi guys,

I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.

similar to this SQL case statement

CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,


the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.

thanks for any assistance you can provide.

regards,

Chris

View 1 Replies View Related

Sql Server 2005 Inserting Prbblem..wrong SQL? Wrong Parameter?

Feb 19, 2006

Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code:        Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _        ByVal Email As String, ByVal Gender As Integer, _        ByVal FirstName As String, ByVal LastName As String, _        ByVal CellPhone As String, ByVal Street As String, _        ByVal StreetNumber As String, ByVal StreetAddon As String, _        ByVal Zipcode As String, ByVal City As String, _        ByVal Organization As String _        ) As Boolean            'returns true with success, false with failure            Dim MyConnection As SqlConnection = GetConnection()            Dim bResult As Boolean            Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection)            MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName))            MyCommand.Parameters.Add(New SqlParameter("@Password", Password))            MyCommand.Parameters.Add(New SqlParameter("@Email", Email))            MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender))            MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName))            MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName))            MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone))            MyCommand.Parameters.Add(New SqlParameter("@Street", Street))            MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber))            MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon))            MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode))            MyCommand.Parameters.Add(New SqlParameter("@City", City))            MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization))            Try                MyConnection.Open()                MyCommand.ExecuteNonQuery()                bResult = True            Catch ex As Exception                bResult = False            Finally                MyConnection.Close()            End Try            Return bResult        End FunctionThanks!

View 1 Replies View Related

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

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

May 20, 2008

Why does the following call to a stored procedure get me this error:


Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.




Code Snippet

EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'




The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.

I can't find anything wrong in the syntax for CONVERT or any nearby items.


Help me please. Thank you.

View 7 Replies View Related







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