DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: decode help

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    decode help

    Hi

    Code:
    create table test (
    x number,
    y number,
    z integer
    )
    
    select COLUMN_NAME, DATA_TYPE,
    DATA_PRECISION,DATA_SCALE
    from USER_TAB_COLUMNS
    where TABLE_NAME = 'TEST'
    
    
    COLUMN_NAME    DATA_T DATA_PRECISION DATA_SCALE
    -------------- ------ -------------- ----------
    X              NUMBER             38          0
    Y              NUMBER
    Z              NUMBER              0
    
    
    since z i defined as intger i would like to see
    
    
    select COLUMN_NAME, DATA_TYPE
    from USER_TAB_COLUMNS
    where TABLE_NAME = 'TEST'
    
    
    COLUMN_NAME   DATA_T 
    -----------   ------ 
    X             NUMBER     
    Y             NUMBER
    Z             INTEGER
    
    INTEGER if the scale is 0 and precision is null
    Last edited by hrishy; 05-20-2005 at 01:24 AM.

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    hi hrisky,
    Can this help?
    SELECT COLUMN_NAME, decode(DATA_SCALE,0,'INTEGER',DATA_TYPE) "DATA_TYPE", DATA_PRECISION, DATA_SCALE
    FROM USER_TAB_COLUMNS
    WHERE TABLE_NAME = 'TEST'
    /
    ---------------

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Hope this helps

    Code:
    scott@ORCL> create table test (
      2  x number,
      3  y number,
      4  z integer
      5  )
      6  /
    
    Table created.
    
    scott@ORCL> select COLUMN_NAME, DATA_TYPE,DATA_PRECISION, DATA_SCALE
      2  from USER_TAB_COLUMNS
      3  where TABLE_NAME = 'TEST'
      4  /
    
    COLUMN_NAME                    DATA_TYPE                      DATA_PRECISION DATA_SCALE
    ------------------------------ ------------------------------ -------------- ----------
    X                              NUMBER
    Y                              NUMBER
    Z                              NUMBER                                                 0
    
    3 rows selected.
    
    scott@ORCL> alter table test modify(x number(38));
    
    Table altered.
    
    scott@ORCL> select COLUMN_NAME, DATA_TYPE,DATA_PRECISION, DATA_SCALE
      2  from USER_TAB_COLUMNS
      3  where TABLE_NAME = 'TEST'
      4  /
    
    COLUMN_NAME                    DATA_TYPE                      DATA_PRECISION DATA_SCALE
    ------------------------------ ------------------------------ -------------- ----------
    X                              NUMBER                                     38          0
    Y                              NUMBER
    Z                              NUMBER                                                 0
    
    3 rows selected.
    
    scott@ORCL> SELECT column_name,
      2         CASE
      3            WHEN data_scale = 0
      4               THEN (CASE
      5                        WHEN data_precision IS NULL
      6                           THEN 'INTEGER'
      7                        WHEN data_precision IS NOT NULL
      8                           THEN 'NUMBER'
      9                     END
     10                    )
     11            ELSE 'NUMBER'
     12         END
     13    FROM user_tab_columns
     14   WHERE table_name = 'TEST'
     15  /
    
    COLUMN_NAME                    CASEWHE
    ------------------------------ -------
    X                              NUMBER
    Y                              NUMBER
    Z                              INTEGER
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Reydp

    Nope we need to use a nested decode .

    i tried this but its failing with a syntax error

    Code:
    select column_name,
    decode(decode(data_precision,null,0,1),
    decode(data_scale,0,0,1),0,'INTEGER','NUMBER') X
    from user_tab_columns
    where table_name='TEST'
    
    where table_name='TEST'
               *
    ERROR at line 5:
    ORA-01722: invalid number
    regards
    Hrishy

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Amar

    welcoem back from hibernation :-) good to have a monk here :-)
    But i am on 7.3.4 need to use decode only :-)

    regards
    Hrishy

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Folks

    Oops i got it :-).Thank you your help is very much appreciated :-D

    Code:
    select column_name,
    decode(decode(data_precision,null,0,1),
    decode(data_scale,0,0,1),'INTEGER','NUMBER') X
    from user_tab_columns
    where table_name='TEST'

    regards
    Hrishy

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    why not create a procedure or function?
    ---------------

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    just get late with the reply.

    but it looks like the same sql that you previously post.
    ---------------

  9. #9
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Originally posted by hrishy
    Hi Folks

    Oops i got it :-).Thank you your help is very much appreciated :-D

    Code:
    select column_name,
    decode(decode(data_precision,null,0,1),
    decode(data_scale,0,0,1),'INTEGER','NUMBER') X
    from user_tab_columns
    where table_name='TEST'

    regards
    Hrishy
    Hi Rishy,
    The above query may yield correct result in your case but this is not correct in general.
    Here is a situation.
    Code:
    SQL> create table dec_test (x number,y number(4,2), z integer);
    
    Table created.
    
    SQL> select column_name,data_type,data_precision,data_scale from user_tab_columns
      2  where table_name='DEC_TEST';
    COLUMN_NAME         DATA_TYPE              DATA_PRECISION DATA_SCALE
    ------------------- ---------------------- -------------- ----------
    X                   NUMBER          
    Y                   NUMBER                              4          2
    Z                   NUMBER                                         0
    
    SQL> select column_name,
      2  decode(decode(data_precision,null,0,1),
      3  decode(data_scale,0,0,1),'INTEGER','NUMBER') X
      4  from user_tab_columns
      5  where table_name='DEC_TEST';
    
    COLUMN_NAME                    X
    ------------------------------ -------
    X                              NUMBER
    Y                              INTEGER
    Z                              INTEGER
    Note the 2nd column the number data type has been wrongly represented as integer.this happened because both data_precision and data_scale values yeilded not null and non zero values forcing final decode to return 1 and hence return integer.
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Oops so need to work on some other decode solution :-).Thanks simply DBA we need to work on another solution

    regards
    Hrishy

Posting Permissions

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


Click Here to Expand Forum to Full Width