Re: Oracle 7 - long input

From: Hernan Cortez <HernanCortez1_at_msn.com>
Date: Mon, 23 Feb 2004 23:52:26 +0100
Message-ID: <c1e075$1higkp$1@ID-221087.news.uni-berlin.de>

Hello

Primary problem:
ORA-01790: expression must have same datatype as

Solution:
The view$.text is a long field
<' ' "SOURCE"> and <v.text "SOURCE"> are not the same better is <null "SOURCE">

After you "repare" the script, I think you become more problems ... For example: select distinct with a long field is not possible ... For example: where name like 'VUE_ETAT_%' is not possible, because nobody knows what NAME do you meen ... you have three NAME fields.

Whatever ... here I have a possible solution for you. My comments are inline in the statement.

Good look :-)

select "OWNER#",                       -- Alias is needed
       "NAME_1",                       -- Alias is needed
       "NAME_2",                       -- Alias is needed
       "SOURCE",                       -- Alias is needed
       "NB DEP"                        -- Alias is needed
from  ((select --distinct              -- can not be used with long-fields
                o.owner# "OWNER#",     -- Alias is usefull
                o.name   "NAME_1",     -- Alias is usefull
                po.name  "NAME_2",     -- Alias is usefull
                null     "SOURCE",     -- ' ' or '' do not merge with
long-field
                count(*) "NB DEP"      -- Alias is usefull
         from   sys.obj$ o,
                sys.obj$ po,
                sys.dependency$ d
         where  o.obj# = d.d_obj#
         and    po.obj# = d.p_obj#
         and    o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)
         and    o.type  in (2,  4, 7, 8, 9, 11)
         and    po.type in (2,  4, 7, 8, 9, 11)
         group by o.owner#,
                  o.name,
                  po.name,
                  o.type,             -- not selected, therefore can not be
grouped
                  po.type)            -- not selected, therefore can not be
grouped
        union all
       (select --distinct              -- can not be used with long-fields
                o.owner# "OWNER#",     -- Alias is usefull
                o.name   "NAME_1",     -- Alias is usefull
                ''       "NAME_2",     -- Alias is usefull
                --'',                  -- missing merge field in first
select
                --'',                  -- missing merge field in first
select
                --'',                  -- missing merge field in first
select
                v.text   "SOURCE",     -- long-field
                0        "NB DEP"      -- Alias is usefull
         from   sys.obj$ o,
                sys.view$ v
         where  o.obj# = v.obj#(+)
        ))

where name_1 like 'VUE_ETAT_%'
or NAME_2 like 'VUE_ETAT_%';

"@rennes" <l.sass_at_omp-transport.com> schrieb im Newsbeitrag news:c1d8pp$r38$1_at_s1.read.news.oleane.net...
> Hello, i have a problem (oracle 7) in an union like that :
> =====================================
> select *
> from
> ((
> select distinct
> o.owner#, o.name, po.name,
> ' ' "SOURCE",
> count(*) "Nb dep"
> from sys.obj$ o, sys.obj$ po, sys.dependency$ d
> where o.obj# = d.d_obj#
> and po.obj# = d.p_obj#
> and o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)
> and o.type in (2, 4, 7, 8, 9, 11)
> and po.type in (2, 4, 7, 8, 9, 11)
> group by o.owner#, o.name, po.name,
> o.type, po.type
> )
> union all
> (
> select distinct
> o.owner#, o.name, '', '', '', '', v.text "SOURCE", 0
> from sys.obj$ o, sys.view$ v
> where o.obj# = v.obj#(+)
> ))
> where name like 'VUE_ETAT_%'
> =====================================

>

> Oracle 7 say me :
> corresponding expression
> How can i do that ?

>

> Thanks

>
> Received on Mon Feb 23 2004 - 16:52:26 CST