[This topic is migrated from our old forums. The original author name has been removed]
In qmf I can use column usages 'across' . Then I searched for a possibility in db visualizer, I found something but it is very complicated if there are many expressions for a database field. For this query I have 12 'carr' expressions , for row 'flight' if have 155 expressions. I need all informations in one row, so that 'KTLG', 'RATG', 'NR', 'CODE', 'VERS' will not be repeated. Can someone help me?
with hilfstabelle as
(select rbel_carr_code, rownumber () over (order by rbel_carr_code) as carr
from (select distinct rbel_carr_code from nur.vrbel000 where vast_code = '03' and rbel_termin > '2013-02-28') as R )
select b.ktlg_code_ext, a.ratg_ra_nr, a.ratg_versionsnr, b.sang_angebotscode, c.vsan_angebotscode
--,min(case when carr = 1 then a.rbel_carr_code end) carr1
--,min(case when carr = 2 then a.rbel_carr_code end) carr2
--,min(case when carr = 3 then a.rbel_carr_code end) carr3
--,min(case when carr = 4 then a.rbel_carr_code end) carr4
,min(case when carr = 5 then a.rbel_carr_code end) carr5
,min(case when carr = 6 then a.rbel_carr_code end) carr6
--,min(case when carr = 7 then a.rbel_carr_code end) carr7
--,min(case when carr = 8 then a.rbel_carr_code end) carr8
--,min(case when carr = 9 then a.rbel_carr_code end) carr9
--,min(case when carr = 10 then a.rbel_carr_code end) carr10
,min(case when carr = 11 then a.rbel_carr_code end) carr11
,min(case when carr = 12 then a.rbel_carr_code end) carr12
,a.rbel_flug_nr
from nur.vrbel000 a, hilfstabelle h, nur.vxapl900 b, nur.vrapv000 c
where a.rbel_carr_code = h.rbel_carr_code
and a.vast_code = '03'
and a.vast_code = b.vast_code
and a.ratg_ra_nr = b.ratg_ra_nr
and a.ratg_versionsnr = b.ratg_versionsnr
and a.vast_code = c.vast_code
and a.ratg_ra_nr = c.ratg_ra_nr
and a.ratg_versionsnr = c.ratg_versionsnr
and b.ratg_aktuell_kz = 'J'
and b.rapo_aender_Kz ^= 'S'
group by
b.ktlg_code_ext
, a.ratg_ra_nr
, a.ratg_versionsnr
, b.sang_angebotscode
, c.vsan_angebotscode
, a.rbel_flug_nr
with ur
KTLG RATG NR CODE VERS CARR5 CARR6 CARR11 CARR12 FLIGHT
VER 39735448 4 83493 00100 (null) DE (null) (null) 2156
VER 39735448 4 83493 00100 (null) DE (null) (null) 2157
VER 39735448 4 83493 00100 (null) (null) (null) LH 989
VER 39735448 4 83493 00100 (null) (null) (null) LH 996
VER 44346190 4 83493 00100 (null) (null) (null) LH 1003
VER 44346190 4 83493 00100 (null) DE (null) (null) 2157
VER 44346190 4 83493 00100 (null) DE (null) (null) 6156
VER 44346190 4 83493 00100 (null) (null) (null) LH 996
VER 46813934 3 83493 00100 (null) DE (null) (null) 2156
VER 46813934 3 83493 00100 (null) DE (null) (null) 2157
VER 46813934 3 83493 00100 (null) (null) (null) LH 989
VER 46813934 3 83493 00100 (null) (null) (null) LH 996
1 Comment
Roger Bjärevall
said
over 10 years ago
[This reply is migrated from our old forums.]
Re: How can I transponse rows into column
Hi,
You will need to check with the DB2 community (assuming QMF is an IBM product) how to optimize your query, Once you've determined how to optimize it DbVisualizer will most likely be able to execute the select.
Regards
Roger
anonymous