MYSQL if statement syntax

[ashabanu]

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

[Rabbit]

Is this supposed to be SQL code? Because the first line looks like SQL but the rest of it is not.

[ashabanu]

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.

[Rabbit]

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.

[ashabanu]

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 .

[Rabbit]

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.

select 
   case when newtime < 80101
      then 0
   when newtime between 80101 and 160100
      then 1
   when newtime > 160100
      then 2
   end as timesegment
from new_event

[ashabanu]

thank you so very much.