By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,350 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

MYSQL if statement syntax

P: 5
Hi,
currently im using MYSQL workbench to extract some features needed for my project. However im having a problem with this query,

select newtime from new_event
IF newtime < 80101
than set newtime = 0
elseif newtime > 80000 and newtime < 160101 than set newtime = 1
elseif newtime >160000 than set newtime = 2
end if

I have converted the time into numeric; hence newtime. Im trying to use newtime to create a time frame ( 0 , 1 , 2) . Unfortunately when i compiled it keeps giving an error code 1064

new_event is a new table created while doing the first initial query.

I hope you could really help me.

thank u
Dec 9 '11 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,349
Is this supposed to be SQL code? Because the first line looks like SQL but the rest of it is not.
Dec 9 '11 #2

P: 5
hi,
i tried something else, but it still gives the same error.


DELIMITER $$

select * from new_event where for each row
begin
set a = select newtime from new_event where cid
if a < 80101 then set newtime = 0
else set newtime = 1

end if

DELIMITER;

can you help me with the syntax.

thank u.
Dec 10 '11 #3

Rabbit
Expert Mod 10K+
P: 12,349
I'm not exactly sure what it is you're trying to do. I don't know if you're using just SQL or not. And I have no idea if you're trying to run an update or if you're just trying to calculate a field.
Dec 11 '11 #4

P: 5
first and foremost , yes im using SQL. only SQL query language. I am using MYSQL workbench to extract some features needed for my project. I have managed to extract the features needed and store it in a new table call new event. I have also converted the time into integer. Below is the sql code used :-

select iphdr.cid ,iphdr.ip_dst, date(event.timestamp), convert(time(event.timestamp), unsigned) as newtime, event.signature
from iphdr, event
where iphdr.cid = event.cid
order by event.signature;


result (some of the output)

cid | ip_add | date | time | signature

480 | 3232284675 | 2011-11-19 | 4328 | 1

482 | 3232284675 | 2011-11-19 | 4328 | 1

1 | 3232284675 | 2011-11-19 | 113928 | 1

2 | 3232284675 | 2011-11-19 | 235959 | 2


Since I have converted the time into numeric; hence newtime, now i need to segregate the output form the query above into three different time frame (time frame 0 , 1 and 2). Here is the problem .... when i try to do it using the codes i posted previously i keep getting the 1064 error code. I thought that by using IF then Else , i could easily separate the data into 3 different time frame.
The output i need is like below

cid | ip_add | date |time | sig.|timeFrame

480 | 3232284675 |2011-11-19 |4328 | 1 | 0

482 | 3232284675 |2011-11-19 |4328 | 1 | 0

1 | 3232284675 |2011-11-19 |113928 | 1 | 1

2 | 3232284675 |2011-11-19 |235959 | 2 | 2

the code i used earlier (with 1064 error code) was suppose to do that.

I hope im making more sense now. Im a complete klutz when it comes to programming. im sorry .
Im also trying to make this work by browsing the net. Ill be working hard while waiting for your reply.

thank you .
Dec 11 '11 #5

Rabbit
Expert Mod 10K+
P: 12,349
What you're doing is basically trying to create a calculated column. It needs to be within the select clause, not out of it. Also, use CASE rather than IF. And you're spelling THEN incorrectly. And finally, you can't use SET, that is used for variables.

Expand|Select|Wrap|Line Numbers
  1. select 
  2.    case when newtime < 80101
  3.       then 0
  4.    when newtime between 80101 and 160100
  5.       then 1
  6.    when newtime > 160100
  7.       then 2
  8.    end as timesegment
  9. from new_event
Dec 11 '11 #6

P: 5
thank you so very much.
Dec 12 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.