-
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.
-
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'
/
---------------
-
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."
-
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
-
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
-
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
-
why not create a procedure or function?
---------------
-
just get late with the reply.
but it looks like the same sql that you previously post.
---------------
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|