Results 1 to 7 of 7

Thread: oracle view

  1. #1
    Join Date
    Jan 2002
    Posts
    77

    Unanswered: oracle view

    Hello All,

    Trying to create the following view

    create or replace view phys_id as
    select distinct PHYSN_ATNDG_ID, "RWJ" from rwj_nj_cfdl
    union
    select distinct PHYSN_ATNDG_ID, "PAL" from PAL_nj_cfdl;


    but I get the following error:

    ORA-00904: "RWJ": invalid identifier.
    Basically I want to append a dummy column value depending on the table I get the values from. I can run this query in ms access, but doesn't work in oracle. Any know how to implement this in oracle.

    Thanks much,

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Character literals are quoted using single quotes, not double quotes, so you'll have to use:
    Code:
    create or replace view phys_id as
    select distinct PHYSN_ATNDG_ID, 'RWJ' from rwj_nj_cfdl
    union
    select distinct PHYSN_ATNDG_ID, 'PAL' from PAL_nj_cfdl;
    Double quotes are used to quote object/column names that are either case-sensitive or a reserved word.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    btw, the use of

    Code:
    select DISTINCT...
    UNION
    select DISTINCT...
    is the same as

    Code:
    select ...
    UNION 
    select ...
    -cf

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Actually in this case it is not the same.
    Both selects add a constant character literal to their rows which makes all rows from the first select distinct from all rows from the second select. So the "implicit" distinct of the UNION will not remove any rows.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    The DISTINCT effect of the UNION keyword is applied to the entire resultset. The Query Plan is different for each, but the result is the same. Just thought it might be more expensive for a large resultset to look for duplicates 3 times instead of once. Plus it keeps the intent of the query more straight-forward:

    Code:
    create table rwj_nj_cfdl
    (PHYSN_ATNDG_ID number);
    
    Table created.
    
    create table pal_nj_cfdl
    (PHYSN_ATNDG_ID number);
    
    Table created.
    
    insert into rwj_nj_cfdl values (1);
    insert into rwj_nj_cfdl values (1);
    insert into rwj_nj_cfdl values (2);
    
    insert into pal_nj_cfdl values (1);
    insert into pal_nj_cfdl values (2);
    insert into pal_nj_cfdl values (2);
    
    
    select distinct PHYSN_ATNDG_ID, 'RWJ' from rwj_nj_cfdl
    union
    select distinct PHYSN_ATNDG_ID, 'PAL' from PAL_nj_cfdl;
    
    PHYSN_ATNDG_ID 'RW
    -------------- ---
                 1 PAL
                 1 RWJ
                 2 PAL
                 2 RWJ
    
    4 rows selected.
    
    select PHYSN_ATNDG_ID, 'RWJ' from rwj_nj_cfdl
    union
    select PHYSN_ATNDG_ID, 'PAL' from PAL_nj_cfdl;
    
    PHYSN_ATNDG_ID 'RW
    -------------- ---
                 1 PAL
                 1 RWJ
                 2 PAL
                 2 RWJ
    
    4 rows selected.
    
    select distinct PHYSN_ATNDG_ID, 'RWJ' from rwj_nj_cfdl
    union all
    select distinct PHYSN_ATNDG_ID, 'PAL' from PAL_nj_cfdl;
    
    PHYSN_ATNDG_ID 'RW
    -------------- ---
                 1 RWJ
                 2 RWJ
                 1 PAL
                 2 PAL
    
    4 rows selected.
    
    select PHYSN_ATNDG_ID, 'RWJ' from rwj_nj_cfdl
    union all
    select PHYSN_ATNDG_ID, 'PAL' from PAL_nj_cfdl;
    
    PHYSN_ATNDG_ID 'RW
    -------------- ---
                 1 RWJ
                 1 RWJ
                 2 RWJ
                 1 PAL
                 2 PAL
                 2 PAL
    
    6 rows selected.
    -cf

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    chuck is right, union will distinct the whole resulset plus sort it.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chuck_forbes
    The DISTINCT effect of the UNION keyword is applied to the entire resultset.
    You live and learn

    I always thought it didn't, but then when I started to think about it in more detailed after your answer it doesn't even seem logical what I assumed...

Posting Permissions

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