Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    113

    Unanswered: This is getting frustrating....

    I am trying to run this query and I keep getting the following error:

    ORA-01722 Invalid number on line 6

    insert into tv
    select list_acct_no, market_code, sum(event_counter) tv
    from jug_final
    where event_code in (222, 922, 1122)
    and month_no <= 7
    group by list_acct_no, market_code;

    These are brand new tables. I am not sure what is causing the problem.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >These are brand new tables.
    I know that Oracle does not care about how old or new any table may be.
    I'm not sure why you think this is relavant information.

    >I am not sure what is causing the problem.
    Neither do I, because you neglected to provide any meaningful details; such as
    SQL> DESC TV
    SQL> DESC JUG_FINAL
    SQL> SELECT * FROM JUG_FINAL;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    113
    I am sorry:

    TV
    (
    MARKET_CODE CHAR(2) NOT NULL,
    LIST_ACCT_NO NUMBER(25) NOT NULL,
    TV NUMBER(25) NOT NULL
    )
    TABLESPACE TV
    NOLOGGING
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    STORAGE(BUFFER_POOL DEFAULT)
    NOPARALLEL
    NOCACHE
    /


    JUG_FINAL
    (
    MARKET_CODE CHAR(2) NOT NULL,
    LIST_ACCT_NO NUMBER NOT NULL,
    EVENT_CODE NUMBER NULL,
    EVENT_COUNTER NUMBER NOT NULL,
    MONTH_NO NUMBER NOT NULL,
    YEAR_NO NUMBER NOT NULL
    )
    TABLESPACE JUG
    NOLOGGING
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    STORAGE(BUFFER_POOL DEFAULT)
    NOPARALLEL
    NOCACHE
    /



    Select * from jug_final;



    ** 9531 222 2 7 2004
    ** 9531 222 2 9 2003
    ** 9619 222 4 2 2004
    ** 9620 80 1 6 2004
    ** 9645 222 1 3 2003
    ** 9650 222 1 8 1999
    ** 9688 222 2 6 2004
    ** 9771 222 1 1 2003
    ** 9773 222 1 3 2003

  4. #4
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106

    Try...

    insert into tv
    select list_acct_no, market_code, sum(event_counter)
    from jug_final
    where event_code in (222, 922, 1122)
    and month_no <= 7
    and event_code is not null
    group by list_acct_no, market_code;
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    why would adding "is not null" help?

    the problem is that, like most bad programmers, the insert columns are not listed out, and he assumes that the columns will magically line up. Look at the desc of TV. Now look at the select list. Notice any problems?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A hint,

    Look at the second column in TV (Number(25))
    and the second column of your select (market_code)

    Notice anything funny about the two columns?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    always helps to add the columns you will be inserting into.
    What would happen if you add a column to the TV table?
    BOOM! all you code would be broken and need to be rewritten

    PHP Code:
    insert into tv (
      
    MARKET_CODE,
      
    LIST_ACCT_NO,
      
    TV )
    select 
      market_code

      
    list_acct_no
      
    sum(event_countertv
    from jug_final
    where event_code in 
    (2229221122)
    and 
    month_no <= 7
    group by list_acct_no
    market_code
    as mentioned in the above post, you can see what needed to be switched. you were trying to enter list_acct_no into the market_code column.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Apr 2004
    Posts
    113
    Thank you so much Duck!

    Why does your replies always have PHP Code:

    What does that mean?

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    PHP code (or ordinary code) helps you structure your post. Notice the difference in The Duck's query without using the (php) code and query he provided above (this one is simply copied/pasted from above):

    insert into tv
    MARKET_CODE,
    LIST_ACCT_NO,
    TV )
    select
    market_code,
    list_acct_no,
    sum(event_counter) tv
    from jug_final
    where event_code in (222, 922, 1122)
    and month_no <= 7
    group by list_acct_no, market_code;

    I hope you don't mind me answering in your behalf, The Duck
    Last edited by Littlefoot; 08-18-04 at 17:21.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    Die littlefoot!!!!


    It is SO much easier to read sql enclosing it in
    PHP Code:
    PHP 
    or
    Code:
    CODE
    formats.

    Personally, I enjoy the color that PHP brings to the table.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Dropped dead

Posting Permissions

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