SQL & PL/SQL

Hey guys!

I am struggling with a query and would like to ask for your opinion. When running the statement I always receive a 17410 error. No data is yielded as data can't be read from the socket.

In my query I am simply trying to join two results. When running the queries individually everything is okay and no error occurs.

Already tried to convert the output columns to the same data types to enforce compatibiliy. However, to no avail.

Here's the query! Do you have an idea?

with w as
((select b.ba_lang, x.lng_baumart, to_char(x.ba_kat) ba_kat,
 x.flag, 0 disable_status
from 
vt_tbl_qb_zu_bestand v,
vt_qb_join q,
vt_tbl_baumart b,
vt_tbl_matrix x
where 
(q.cnt_gebiet = :p2024_cnt_gebiet)
and (v.lng_baumart = b.cnt_baumart) 
and (v.lng_qb_sub = q.lng_qb_sub)
and (q.str_lrt_main = :p2024_str_lrt)
and (q.int_wg = :p2024_wg)
and (q.str_be_main = :p2024_be)
and (q.int_status_sub = 3)
and (x.lrt_class = :p2024_str_lrt_mx)
and (x.lng_baumart = b.cnt_baumart)
group by b.ba_lang, x.lng_baumart, x.ba_kat, b.ba_gruppe, x.flag
) 
union
(select b.ba_lang, x.lng_baumart,
    x.ba_kat,
   x.flag, 0 disable_status
  from vt_tbl_qb_verjuengung v,
    vt_qb_join q,
    vt_tbl_baumart b,
    vt_tbl_matrix x
  where (q.cnt_gebiet   = :p2024_cnt_gebiet)
  and (v.lng_baumart    = b.cnt_baumart)
  and (v.lng_qb_sub     = q.lng_qb_sub)
  and (q.str_lrt_main   = :p2024_str_lrt)
  and (q.int_status_sub = 3)
  and (q.int_wg         = :p2024_wg)
  and (q.str_be_main    = :p2024_be)
  and (x.lrt_class      = :p2024_str_lrt_mx)
  and (x.lng_baumart    = b.cnt_baumart)
  group by b.ba_lang, x.lng_baumart, x.ba_kat, b.ba_gruppe, x.flag))
  
 (
  (select ba_lang, lng_baumart,
    to_char(ba_kat) ba_kat,
    decode(ba_kat,'H',1,'N',2,'B',3,'S',4,'P',5,'hG',6,'nG',7) as cs,
    flag, 
    disable_status,
    null str_comment
  from w
  ) 
union all
  (select distinct r.ba_lang, d.lng_baumart, to_char(d.ba_kat) ba_kat, decode(d.ba_kat,'H',1,'N',2,'B',3,'S',4,'P',5,'hG',6,'nG',7) as cs,
    d.flag, 
case when d.lng_baumart in (78, 74, 72,71, 54, 53) then 1
else null end as disable_statusx, 'not valid' str_comment
  from vt_tbl_baumart r,
    vt_tbl_matrix d
  where r.cnt_baumart    = d.lng_baumart
  and d.lrt_class        = :p2024_str_lrt_mx
  and d.ba_kat in ('S','B','H','P','N')
  and r.cnt_baumart not in (select lng_baumart from w)))

Many thanks for your help!

I appreciate it!

Edited by: skahlert on 01.06.2010 09:02