Thanks for the question, Scott.
Asked: January 04, 2001 - 4:06 pm UTC
Last updated: January 04, 2001 - 4:06 pm UTC
Version: 805
Viewed 1000+ times
You Asked
I have the two description fields in a table as follows:
SQL> desc exav.work_history_sv
Name Null? Type
------------------------------- -------- ----
WORK_ID NOT NULL NUMBER(38)
PROBLEM_ID NOT NULL VARCHAR2(15)
USER_ID NOT NULL VARCHAR2(15)
WORK_BEGIN_DATE NOT NULL DATE
WORK_BEGIN_TIME NOT NULL VARCHAR2(10)
WORK_END_DATE NOT NULL DATE
WORK_END_TIME NOT NULL VARCHAR2(10)
DESCRIPTION VARCHAR2(254)
DESC_OVRFLW LONG
MODIFY_DATETIME NOT NULL NUMBER(38)
Tivoli puts whatever doesn't fit into description into desc_ovrflw. I don't know why they didn't use a longer description field but I need to report on the entire description. How do I write an SQL query that will return the text from description and desc_ovrflw as one column?
SELECT DESCRIPTION || DESC_OVRFLW ...
Does Not Work because of inconsistent datatypes.
Regards,
Scott
and Tom said...
we can get the first 4000 bytes in SQL only (the first 4000 bytes of DESCRIPTION || DESC_OVRFLW ). As long as the desc_ovrflw does not exceed 32k -- this will work fine:
ops$tkyte@ORA8I.WORLD> create table work_history_sv
2 ( description varchar2(254),
3 desc_ovrflw long
4 )
5 /
Table created.
ops$tkyte@ORA8I.WORLD> create or replace
2 function description4000( p_rowid in rowid ) return varchar2
3 as
4 l_c1 varchar2(254);
5 l_c2 long;
6 begin
7 select description, desc_ovrflw
8 into l_c1, l_c2
9 from work_history_sv
10 where rowid = p_rowid;
11
12 return substr( l_c1||l_c2, 1, 4000 );
13 end;
14 /
Function created.
ops$tkyte@ORA8I.WORLD> create or replace view work_history_sv_view
2 as
3 select a.*, description4000(rowid) desc_4000
4 from work_history_sv a
5 /
View created.
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> insert into work_history_sv values( 'hello world', ' this is the overflow' );
1 row created.
ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> select * from work_history_sv_view
2 /
DESCRIPTION
----------------------------------------------------------------------------------------------------
DESC_OVRFLW
--------------------------------------------------------------------------------
DESC_4000
----------------------------------------------------------------------------------------------------
hello world
this is the overflow
hello world this is the overflow
Is this answer out of date? If it is, please let us know via a Comment