Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unhappy Unanswered: Using timestamp in union all view

    I am having a silly problem with one of my views. It is a union all with two select statements. There is a timestamp(6) in one select but not in the other so I have a to_timestamp(null) to match up the columns.

    The problem is that no matter how I code the to_timestamp(null,fmt) the structure of the view is always timestamp(0). I even changed the 2nd select so that it uses a timestamp(6) column. If I create 2 views from both selects they have the correct syntax but as soon as I use a union the timestamp loses it precision.

    When I acutally do a select from the view I can still see the milliseconds precision. My real problem occurs in that I have a ref cursor that is of type view_row%rowtype. When I select into the cursor I lose the precision.

    Any ideas?

    thx

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > My real problem occurs .......
    Since you provided nothing of value in your post, I save time by not guessing
    what the actual SQL is or a description of the "VIEWS" which are not behaving for you.
    The problem is obviously PEBKAC.
    http://download-west.oracle.com/docs...a96524/toc.htm
    http://download-west.oracle.com/docs...a96540/toc.htm
    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
    Aug 2004
    Posts
    2
    I thought the problem was pretty laid out... I described the problem in simple terms, gave an exception, and then explained why the precision loss was a problem.

    here is a basic test scenario in oracle 9.2 to further highlight the issue.

    create table t1 (
    ttest timestamp(6))

    create view v1 as select ttest from t1;

    describe v1 shows ttest as timestamp(6);

    create view v2 as
    select ttest from t1
    union
    select ttest from t1;

    describe v2 shows ttest as timestamp(0).

    When I then do a select to a cursor of type v2%rowtype I lose the fractional seconds. Your sarcasm and then sending a link to the TOC doesn't help too much and probably wasted more of your time. Thanks for the effort though.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >describe v1 shows ttest as timestamp(6);
    For the life of me, I just don't understand why many folks will talk about what
    they see as opposed to simply doing a cut & paste of EXACTLY what they've done and how the system responded.
    I have a number of questions which may or may not be relevant to determining the answer.
    1) What OS?
    2) What version of OS?
    3) EXACTLY which version of Oracle 9.2?
    4) Since (6) is the default precision, what happens with no precision & with a different value.
    5) what happens with UNION on the base tables (avoiding the views)?
    (Is the problem the UNION or the VIEW on VIEWs?)

    I ALWAYS try to avoid VIEWs on VIEWs due to the overhead of recursive SQL.
    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.

Posting Permissions

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