Results 1 to 7 of 7

Thread: Writing SQL

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Writing SQL

    Hi guys

    Stuck with some SQL here. Hope I can explain somewhat clearly.

    I'm trying to write something like

    Code:
    where to_char(datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy') and CLASS like ('%ABC%','%DEF%')
    but this is not working.

    If I do

    Code:
    where to_char(datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy') and CLASS like ('%ABC%') and ('%DEF%')
    it takes

    Code:
    where to_char(datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy') and CLASS like ('%ABC%')
    Code:
    and ('%DEF%')
    separately.

    Could someone please help me out with the correct syntax to use?

    Regards
    Shajju

  2. #2
    Join Date
    Dec 2013
    Posts
    14
    Like operator you cannot specify in, as it would always try to read the complete text to search.
    Use this as you need both values
    where to_char(datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy') and (CLASS like ('%ABC%') or CLASS like ('%DEF%'))

  3. #3
    Join Date
    Aug 2008
    Posts
    464

    Thanks

    Thanks. Slightly off topic but I have to order the results according to datetime which is being selected as:

    07/2013
    09/2013
    04/2013
    01/2013
    to 04/2014.

    but doing a simple order by doesn't sort it properly.
    Last edited by shajju; 05-14-14 at 03:46.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can ORDER BY columns in the tables(not only columns in the select list).
    So, how about to specify datetime(column name of a table in the FROM clause)?

    Oracle® Database SQL Language Reference 11g Release 2 (11.2)
    order_by_clause
    ...
    ...

    expr expr orders rows based on their value for expr. The expression is based on
    columns in the select list or columns in the tables, views, or materialized views in the
    FROM clause.

  5. #5
    Join Date
    Dec 2013
    Posts
    14
    you can add
    order by to_char(datetime, 'mm.yyyy') to your query

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    I did add to_char(datetime, 'mm.yyyy') to the query but then when running the query it gives:

    Invalid Number for the group by clause.

    Code:
    group by to_char(datetime, 'mm/yyyy')
    order by to_char(datetime, 'mm/yyyy')
    However, if use 'yyyy/mm', it orders the month/year correctly.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    do the following

    Code:
    where trunc(datetime, 'month') = trunc(add_months(sysdate, -1), 'month') 
    and (CLASS like '%ABC%' or CLASS like'%DEF%')
    order by datetime
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •