|
Hi
Can anyone help with an SQL statement im having problems with ?
i want to do something like the below
select date_in, type= CASE date_in
when datepart(hour,date_in) > 12 THEN 'Afternoon'
ELSE 'Morning'
END
from my_table
where date_in >='2007-2-1'
so it will return something like
2007-02-01 08:22:32.663 Morning
2007-02-01 09:22:32.773 Morning
2007-02-01 14:42:32.803 Afternoon
2007-02-01 15:56:32.803 Afternoon
however im getting an error thrown back at me when i try to execute it
Line 2: Incorrect syntax near '>'.
Any ideas ??
thanks
Simon
|
|
|
|
|
correct your query like this
select date_in, type= CASE
when datepart(hour,date_in) > 12 THEN 'Afternoon'
ELSE 'Morning'
END
from my_table
where date_in >='2007-2-1'
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
|
|
|
|
|
You do not have a BEGIN for the END specified below ELSE
Deepali
|
|
|
|
|
CASE has two syntaxes. You can either use it like a C/C++/C# switch or VB Select Case statement, where you specify an expression to be tested against multiple possibilities, or you can use it more like a multi-armed if statement, where you supply boolean expressions - the first one to evaluate to true is then returned.
The syntax for the first is:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE defaultresult
END The syntax for the second:
CASE
WHEN booleanexpression1 THEN result1
WHEN booleanexpression2 THEN result2
ELSE defaultresult
END
|
|
|
|
|
Hello,
Is it better to use define data type in sql tables, What is advantage and disadvantage of user define data types ?
Regards,
S Kumar
|
|
|
|
|
I like defined data types. They are a great way to enforce standards. For instance, if I have a need for Name fields throughout the database and they are always going to be 30 characters then it is much better for me to be able to define a name data type and then use this wherever there is a Name field.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi John,
Thanks a lot for your reply.
Best Regards,
S Kumar
|
|
|
|
|
Hi,
I've heard that there is an sql helper file which has got all the stuff regarding the connectivity to the databse, like connectio, command objects....
Is there any any sort of refernce/link where i could get that?
Thanks!
Gautham
|
|
|
|
|
|
Which index is good to apply.clustered or nonclustered?
|
|
|
|
|
It really depends on the situation, as each type of index has its strengths and weaknesses. If you were offering a phone book, for instance, and wanted to have quick retrieval based on the surname then forename, then a clustered index is much better than an ordinary index because there is a great deal of internal sorting going on there. So searching on Smith John would quickly retrieve the Smiths and then fine filter from there to return the Johns.
The downside of the clustered index is the fact that everytime an insert occurs, then significant reordering needs to be done to guarantee that rows are kept adjacent. This means that clustered indexes can become a real performance hit in high-insert tables.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Generally it is best to have a clustered index defined on every SQL Server table - otherwise it is treated as a heap. By default SQL-Server creates clustered indexes for primary keys. The engine contains a specific optimisation that makes it efficient to have a clustered index that is based upon an incrementing identity column.
A clustered index contains the table's data in its leaf nodes, so the records are physically sorted - and so is ideal for "range" scans. The following scenario takes advantage of this:- A "Customer" table has a clustered primary key on "CustId".
- An "Invoice" table has a non-clustered primary key on "InvoiceId", and a unique clustered index on "CustId, InvoiceId".
It is now very efficient to join between the Customer and Invoice tables.
Another technique that may prove useful, is to define a non-clustered index, but include additional columns that you would not normally be searching upon. SQL-Server will automatically use the data from the index (and not attempt to read the table data) if all of the information it requires for a query is located in a suitable index. This is called "covering".
http://www.sql-server-performance.com/tips_performance.asp[^] contains a bunch of articles and tips that you may find useful.
|
|
|
|
|
Good points, but the OP never mentioned SQL Server . I'm not an expert in other databases, so I hope you can tell me is this the same with other engines?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi Pete
I know that the following engines have clustered indexes:- Sybase (as you would expect because of SQL-Server's heritage).
- Informix had them back in 87.
- Oracle has Index Organized Tables (IOTs), which amounts to the same thing.
All of these conform broadly to the guidelines I provided earlier. If you google for "Clustered Index" then SQL-Server gives your the most hits.
The "covered" index thing has been supported by most high-end engines for many years. SQL Server 2005 has a new "include" clause for non-clustered indexes that is well worth checking-out. There are circumstances where this has better query performance than a clustered index.
On small databases there isn't much to be gained - so most of the lower league engines (MS-Access, etc) do not seem to support the concept. Interestingly, it looks like MySql supports them.
Regards
Andy
|
|
|
|
|
Andy
Thanks for that. Guess which database I specialise in . It's always good to find out how things work from somebody who knows about them.
Many thanks.
Pete
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
anujose wrote: Which index is good to apply.clustered or nonclustered?
If you are using SQL Server (and I presume any other type of database) you can only have one clustered index. A clustered index is where the actual rows are sorted in the order of the index. A non-clustered index means that a separate index is set up that points to the real rows.
|
|
|
|
|
Dear All,
I tried to get the views that are defined using a particular table name. Can any one give me the query for that? I already tried with user_views in the following way.
select view_name from user_views where text like '%tablename%';
The problem here is that the text column datatype is LONG. So I am getting inconsistent datatype err.
Thanks
Surendran
|
|
|
|
|
Hello,
I am attempting to create an autocomputed column with persisted values. The column's value is defined by this function:
CREATE FUNCTION [dbo].[GetMinsFromSStart]
(
@DayOfWeekID tinyint,
@Hour tinyint,
@Minute tinyint,
@Second tinyint
)
returns smallint
as
begin
declare @MinsFromSessionStart smallint;
set @MinsFromSessionStart = 420 + (@DayOfWeekID - 1) * 1440 + @Hour * 60 + @Minute;
if (@Second > 0)
set @MinsFromSessionStart = @MinsFromSessionStart + 1;
if (@MinsFromSessionStart > 0)
return @MinsFromSessionStart;
else if (@MinsFromSessionStart < 0)
begin
set @MinsFromSessionStart = 10080 + @MinsFromSessionStart;
return @MinsFromSessionStart;
end
return 10080;
end
Unfortunately, I am getting this error:
'Tick' table
- Unable to modify table.
Computed column 'MinsFromSStart' in table 'Tmp_Tick' cannot be persisted because the column is non-deterministic.
Why is the above function considered non-deterministic?
Thank you for any input,
Michal Kreslik
|
|
|
|
|
Your table has a computed column. You have not included the code for the computed column so I can't say.
Some functions are non-deterministic. There is a list of non-deterministic functions in the Books-on-line. For example GETDATE() is non-deterministic because, given the same inputs, the output can be different.
|
|
|
|
|
The code for the computed column is simply using the above function:
dbo.GetMinsFromSStart([DayOfWeekID], [Hour], [Minute], [Second])
I think there's nothing non-deterministic in that.
Michal
|
|
|
|
|
This link[^] might help. I'm going out on a limb here and guessing that since your function has three different return statements in it, that is making it non-deterministic. Also I'm more used to using 'end if;' to close off any if conditions, but your code has none. Are you sure it is logically what you are after?
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Hello, Chris,
thanks for the link - but I have already read these guidelines before posting a question here.
Also, quite naturally, I have been testing my function whether it's returning the correct results according to the intended logic: and it does. Just for info, the logic behind this function is to return the number of minutes elapsed from the start of Forex trading session. The first minute of the forex trading session starts every week on Sunday at 17:00:01 EST. The inputs are DayOfWeek index (0 for Sunday thru 6 for Saturday) and hour, minute & second values.
The three different return statements are there because it makes the function work faster (which is a concern as I'm running that on an OLAP db with 255 million+ rows). The first condition is true for most input sets, so the function doesn't have to go thru all of the other logical branches.
I'm certain my function complies to the Microsoft definition of a "deterministic function":
Determinism
Deterministic functions always return the same result any time they are called with a
specific set of input values and given the same state of the database. Nondeterministic
functions may return different results each time they are called with a specific set of input
values even if the database state that they access remains the same.
Also, there's no trace of "endif" keyword in the T-SQL reference for IF..ELSE:
http://msdn2.microsoft.com/en-us/library/ms182717.aspx
So where might be the problem?
Thanks for help,
Michal
|
|
|
|
|
I have now written a simple function that returns the higher value of the two:
CREATE FUNCTION [dbo].[GetHigherValue]
(
@Value1 int,
@Value2 int
)
RETURNS int
AS
BEGIN
declare @ReturnVal int;
if (@Value1 > @Value2)
set @ReturnVal = @Value1;
else
set @ReturnVal = @Value2;
return @ReturnVal;
END
There's only one return statement in the above function and it's by all means deterministic, but I'm stil getting the same "non-deterministic" error when I attempt to use it in the computed column specification.
Any ideas?
Thanks,
Michal
|
|
|
|
|
It seems like every UDF (user-defined funcion) is by default non-deterministic.
Is there any way to change this?
Thanks,
Michal
|
|
|
|
|
Ok, guys,
so the correct answer is that it was not created with the "WITH SCHEMABINDING" option.
Now it works ok.
Michal
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
