Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Unanswered: Invalid number error

    It works fine when i remove distinct from the below query but when i keep distinct keyword, it gives invalid number error.

    select distinct rep_id, logdate,SessId
    from
    (select to_number(REGEXP_REPLACE (uel.params, '^.+periodid=I[0-9+]:I([0-9]+):I.+$', '\1')) rep_id,
    to_date(substr(uel.starttimestamp,0,10), 'YYYY-MM-dd') logdate,
    uel.SessId
    from usereventlog uel, usersessionlog usl
    where uel.SessId = usl.SessId
    and substr(uel.starttimestamp,0,10)>=to_char(to_date(' 01/01/2015','dd/MM/YYYY'),'YYYY-MM-DD')
    and substr(uel.starttimestamp,0,10)<=to_char(add_month s(to_date('01/12/2015','dd/MM/YYYY'),1)-1,'YYYY-MM-DD'))

    I see the problem is with "rep_id". It should throw error without "distinct" also but throws error only on use of distinct.
    Any solution?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Which tool do you use? Is it TOAD or SQL Developer or any similar GUI?

    If that's so, switch to good, old SQL*Plus and run the same query without DISTINCT - I presume that you'll get the same INVALID NUMBER error.

    Why? Because GUI "fools" users as it returns the first 100, 500 or 1000 rows. None of these raises the INVALID NUMBER error. However, if you force it to return ALL rows (for example, by hitting <Ctrl + End> in TOAD) or - in your case - by using the DISTINCT keyword (as it needs to review all rows returned by query in order to be able to extract only the distinct ones), you'll still hit the same INVALID NUMBER.

    That's what SQL*Plus does - it selects all rows, not only a limited subset.

    What is the UEL.STARTTIMESTAMP's datatype? Could you use BETWEEN (instead of <= and >=)? The right side is a DATE (why converting it to a string), so - why wouldn't you rather use the same datatype on the left side (i.e. not apply SUBSTR on STARTTIMESTAMP, but TO_DATE)?

Posting Permissions

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