#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    21
    Rep Power
    0

    WHERE, Extract and basic inequality testing


    Hello all,
    I am relatively new to Oracle DBs and much more experienced in MySQL, and I'm bumping into a few things that I don't really understand - firstly, how the "where" operator works.

    Here's the basic setup: I have a table of events, with a date value called start_dt. I want to exclude any Sunday events in my query, so, simple enough, I enter:

    Code:
    Select * 
    from events
    where to_char(start_dt, 'DAY') != 'SUNDAY'
    However, events with a Sunday date still show up. To prove this I used:

    Code:
    select e.*, to_char(start_dt, 'DAY')
    from events
    where to_char(start_dt, 'DAY') != 'SUNDAY'
    low and behold, I do see 'SUNDAY' in the last field. So for whatever reason, the WHERE is not being applied in the way I expect.

    Thinking that it could be a type comparison issue, I did the following:

    Code:
    select e.*, to_char(start_dt, 'DAY'), to_char(start_dt, 'DAY') = 'SUNDAY'
    from events
    where to_char(start_dt, 'DAY') != 'SUNDAY'
    However, this generates an error ("FROM keyword not found where expected").

    2 questions: 1 - is it possible to get true/false values in the select statement to check these (there are more fields where this is not behaving as I would wish), and 2, why am I still getting sunday events in my query in the first place?

    Thanks a lot everyone!
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    21
    Rep Power
    0
    Oh, I should add my issue with Extract!

    I have the same start_dt date field, and I can to_char(start_dt, "yyyy-mm-dd hh24:mi:ss") just fine. I can also extract(YEAR FROM start_dt) just fine. However, if I extract(hour from start_dt) it generates an error: "invalid extract field for extract source"

    However, year, month, date, day, etc all seem to work just fine. None of the sub-day (hour, minute, second) seem to work.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2016
    Location
    United Kingdom
    Posts
    9
    Rep Power
    0
    Hi,

    Try formatting your date as like to_char(to_date('03/09/1982','dd/mm/yyyy'), 'DAY') and see the results. to_char(start_dt, 'DAY') = 'SUNDAY' is more MS SQL type and not available in Oracle. You should try CASE or DECODE in Oracle if you are looking for comparison and result in select query.

IMN logo majestic logo threadwatch logo seochat tools logo