CASE Missing Keyword

[tridith]

What I have is one table that I have a "heading" it is a number that is between 0 and 360. What I want to do is change it to a N,E,S,W,NW,SW,NE,SE heading.
N = (0-22.4)
NE = (22.5-67.4)
E = (67.5-112.4)
SE = (112.5-157.4)
S = (157.5-202.4)
SW = (202.5-247.4)
W = (247.5-292.4)
NW = (292.5-337.4)
N = (337.5-360)

So I decided to use a case statement.(Not sure if right way to do this)

but with my code I am getting a missing keyword error, I dont know if I am doing this right at all, if anyone has any suggestions, feel free to point me in another direction.

SELECT QATECH_DATASTORE."cust_trucks"."truck_name", 
    "a"."date_time", 
    "a"."speed", 
    "a"."heading", 
    (case "somthing" 
          when "a"."heading" > '0' then 'N' 
          when "a"."heading" > '22.4' then 'NE' 
          when "a"."heading" > '67.4' then 'E' 
          when "a"."heading" > '112.4' then 'SE' 
          when "a"."heading" > '157.4' then 'S' 
          when "a"."heading" > '202.4' then 'SW' 
          when "a"."heading" > '247.4' then 'W' 
          when "a"."heading" > '292.4' then 'NW' 
          when "a"."heading" > '337.4' then 'N' 
          else 'N'), 
    "a"."location3distance", 
    "a"."location3" 
FROM  QATECH_DATASTORE."cust_positions" "a", 
      QATECH_DATASTORE."cust_trucks" 
WHERE QATECH_DATASTORE."cust_trucks".OID = "a"."asset_id" and 
("a"."date_time" between to_date('10/02/2006 12:00PM', 'MM/DD/YYYY HH:MIAM') and 
to_date('10/02/2006 4:30PM', 'MM/DD/YYYY HH:MIAM')) AND 
QATech_DataStore."cust_trucks".OID IN (2);

Thanks

Chad

 

[carp]

I notice you are using the double-quotes inconsistently throughout your SQL statement. Try removing the double-quotes.
As for your CASE statement, try

.
.
.
(case when a.heading > '0' then 'N' 
      when a.heading > '22.4' then 'NE' 
      when a.heading" > '67.4' then 'E' 
      when a.heading > '112.4' then 'SE' 
      when a.heading > '157.4' then 'S' 
      when a.heading > '202.4' then 'SW' 
      when a.heading > '247.4' then 'W' 
      when a.heading > '292.4' then 'NW' 
      when a.heading > '337.4' then 'N' 
          else 'N'), 
.
.
.

However, won't this code always return 'N'? Since 22.5 is > 0, the first WHEN will evaluate to TRUE and give you 'N', when you really want 'NE'. I would suggest listing these in descending values.

 

[tridith]

I was missing the "END" at the end....hahaha

 

[carp]

Oh yeah - that too!
Good job!

 

[Beilstwh]

Your case statement will NOT work, It will always return N, a case statement STOPS on the first true statement. Reverse the order and it will work.

SELECT QATECH_DATASTORE.cust_trucks.truck_name,
a.date_time,
a.speed,
a.heading,
(case somthing
when a.heading > 337.4 then 'N'
when a.heading > 292.4 then 'NW'
when a.heading > 247.4 then 'W'
when a.heading > 202.4 then 'SW'
when a.heading > 157.4 then 'S'
when a.heading > 112.4 then 'SE'
when a.heading > 67.4 then 'E'
when a.heading > 22.4 then 'NE'
when a.heading > 0 then 'N'
else 'N'),
a.location3distance,
a.location3
FROM QATECH_DATASTORE.cust_positions a,
QATECH_DATASTORE.cust_trucks
WHERE QATECH_DATASTORE.cust_trucks.OID = a.asset_id and
a.date_time between to_date('10/02/2006 12:00PM', 'MM/DD/YYYY HH:MIAM') and to_date('10/02/2006 4:30PM', 'MM/DD/YYYY HH:MIAM'))
AND QATech_DataStore.cust_trucks.OID = 2;

Bill
Oracle DBA/Developer
New York State, USA

 

[tridith]

Beilstwh, yours doesnt work either...you are missing "end" hehee, but I got it working.

Thanks for ur post.

SELECT 
	QATECH_DATASTORE."cust_trucks"."truck_name", 
	"a"."date_time", 
	"a"."speed", 
	"a"."heading",
	trunc("a"."date_time") as THEDAY,
    (case 
          when "a"."heading" < '22.4' then 'N'
          when "a"."heading" < '67.4' then 'NE'
          when "a"."heading" < '112.4' then 'E'
          when "a"."heading" < '157.4' then 'SE'
          when "a"."heading" < '202.4' then 'S'
          when "a"."heading" < '247.4' then 'SW'
          when "a"."heading" < '292.4' then 'W'
          when "a"."heading" < '337.4' then 'NW'
          else 'N' [b]end[/b]) "heading1",