invalid relational operator in SQL

Question

select
create_date
,resolved_date
,to_char(create_date, 'YYYY') as year_create
,to_char(create_date, 'MM') as month_create
,to_char(create_date, 'WW') as week_create
,to_char(create_date,'Q') as quarter_create
,to_char(resolved_date, 'YYYY') as year_resolved
,to_char(resolved_date, 'MM') as month_resolved
,to_char(resolved_date, 'WW') as week_resolved
,to_char(resolved_date,'Q') as quarter_resolved
,item
,site
,status
,contact_time
,impact_label

from mytable

where
create_date between to_date('2013/03/01','YYYY/MM/DD') and to_date('2015/08/06','YYYY/MM/DD')
and case item 
when '1' then 'a'
when '2' then 'b'
when '3' then 'c'
else null 
end 

group by
create_date
,resolved_date
,to_char(create_date, 'YYYY') 
,to_char(create_date, 'MM') 
,to_char(create_date, 'WW') 
,to_char(create_date,'Q') 
,to_char(resolved_date, 'YYYY') 
,to_char(resolved_date, 'MM') 
,to_char(resolved_date, 'WW') 
,to_char(resolved_date,'Q') 
,item
,site
,status
,contact_time
,impact_label

order by item, site, create_date;

Can someone please help me spot the error of

invalid relational operator

(it says at group by line). I don't see where I possibly left out ><= ?

First, why do you have a group by clause at all? It doesn't appear that you're doing any aggregation. Second, in your where clause, you have a case statement but you're not comparing the result of that statement to anything. I have no idea what you're trying to accomplish with that case statement so it's hard to guess what you intended. — Justin Cave
– Justin Cave, Commented Aug 6, 2015 at 23:45
1. I thought group by is neccessary for any cases, if not, I could try to remove it. 2. In my case statement, I'm trying to generate different results for my item column, so if it has a result of 1 -> it will show a instead... This is actually not my case statement, I just made up an example of it. Thanks! — lbrrrr
– lbrrrr, Commented Aug 6, 2015 at 23:51
A group by is only used when you want to group rows together and aggregate data. It sounds like you can remove the group by. If you want the case statement to change what is presented, you'd want that to be in the select clause as an additional column in the projection not in the where clause. — Justin Cave
– Justin Cave, Commented Aug 6, 2015 at 23:57
techonthenet.com/sql/group_by.php isn't group by necessary for NOT aggregrate function? — lbrrrr
– lbrrrr, Commented Aug 6, 2015 at 23:59
No. You use a group by when your query includes an aggregate function on some columns. Otherwise, there is no point to grouping the rows. You can include a group by without an aggregate function. It just slows the query down for no reason and makes it more painful to support. — Justin Cave
– Justin Cave, Commented Aug 7, 2015 at 0:03

Fabienne B. · Accepted Answer · 2015-08-07 13:55:15Z

your case is not well structured. Try this :

SELECT create_date,
         resolved_date,
         TO_CHAR (create_date, 'YYYY') AS year_create,
         TO_CHAR (create_date, 'MM') AS month_create,
         TO_CHAR (create_date, 'WW') AS week_create,
         TO_CHAR (create_date, 'Q') AS quarter_create,
         TO_CHAR (resolved_date, 'YYYY') AS year_resolved,
         TO_CHAR (resolved_date, 'MM') AS month_resolved,
         TO_CHAR (resolved_date, 'WW') AS week_resolved,
         TO_CHAR (resolved_date, 'Q') AS quarter_resolved,
         item,
         site,
         status,
         contact_time,
         impact_label,
         (CASE
             WHEN item = '1' THEN 'a'
             WHEN item = '2' THEN 'b'
             WHEN item = '3' THEN 'c'
             ELSE NULL
          END)
            AS new_column_name
    FROM mytable
   WHERE create_date BETWEEN TO_DATE ('2013/03/01', 'YYYY/MM/DD')
                         AND TO_DATE ('2015/08/06', 'YYYY/MM/DD')
ORDER BY item, site, create_date;

Justin Cave · Accepted Answer · 2015-08-07 00:17:38Z

I'm guessing that you want

    select
       create_date
      ,resolved_date
      ,to_char(create_date, 'YYYY') as year_create
      ,to_char(create_date, 'MM') as month_create
      ,to_char(create_date, 'WW') as week_create
      ,to_char(create_date,'Q') as quarter_create
      ,to_char(resolved_date, 'YYYY') as year_resolved
      ,to_char(resolved_date, 'MM') as month_resolved
      ,to_char(resolved_date, 'WW') as week_resolved
      ,to_char(resolved_date,'Q') as quarter_resolved
      ,item
      ,site
      ,status
      ,contact_time
      ,impact_label
      ,(case item 
        when '1' then 'a'
        when '2' then 'b'
        when '3' then 'c'
        else null 
         end) as new_column_name 
  from mytable
 where create_date between to_date('2013/03/01','YYYY/MM/DD') 
                      and to_date('2015/08/06','YYYY/MM/DD')
 order by item, site, create_date;

I've removed the group by clause since you're not doing any sort of aggregation. And since you apparently want the case statement to change the way that the item column is displayed, I moved that to the select list.

If that's not what you want, it would be very helpful if you would edit your question to include a test case. Show us the table definition, show us the DML to insert a few rows, show us the result that you want, and show us the result that you're getting.

Collectives™ on Stack Overflow

invalid relational operator in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related