Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    japan
    Posts
    35

    Unhappy Unanswered: Select Statement Problem

    Hi to all experts there,
    I had 2 questions regarding 'select statement'.

    Q1.
    select c_code, c_name, c_dob, c_loc, c_trxtyp
    from cct_trx
    where c_credate >= '01-Jan-2005'

    Base on above select statement, even i had create index on c_credate,
    is it the index are work ,since i using ' greater or equal ' on c_credate at my select statement condition?

    Q2.
    select count(*), c_code
    from cct_trx
    where c_code ='W0001'
    group by c_code

    output

    C_CODE
    ----------
    W0001
    W0001
    W0001
    W0001
    W0001

    5 rows selected.

    If i want it juz count as 1 record selected, then how should i write
    my select statement ?


    Hope u all experts can help me to solve my problem.
    Thanks.

    regards,
    natalie

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Q1.
    select c_code, c_name, c_dob, c_loc, c_trxtyp
    from cct_trx
    where c_credate >= to_date('01-Jan-2005','dd-mon-yyyy');

    This is assuming that c_credate is of type date and has a trunced date. If it is a varchar2 (VERY bad practice) then use the following.

    select c_code, c_name, c_dob, c_loc, c_trxtyp
    from cct_trx
    where to_date(c_credate,'dd-mon-yyyy') >= to_date('01-Jan-2005','dd-mon-yyyy');

    Q2.
    To display one code use:

    select count(*)
    from cct_trx
    where c_code ='W0001';

    For counts for ALL codes:

    select count(*), c_code
    from cct_trx
    group by c_code;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Q1.
    It will use an index, as long as the optimizer decides it to, whether it is of type date or varchar2. It is just what you indexed (c_credate), there's nothing constraining Oracle from doing so.

    Q2.
    There's missing ouput on your data. Anyways, try what beilstwh suggested.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    is it the index are work
    I assume you want to know whether Oracle is using that index, when you run your query.
    You can find that out on your own. Simply use the EXPLAIN PLAN command, and Oracle will tell you if it's using the index.

    For more details see:
    http://download-west.oracle.com/docs...ments_911a.htm

Posting Permissions

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