I am a newby in QV and trying to run some reports. First reports were OK, but now i am facing a challenge (for me)....
I have a query in PL SQL und want to show the result in a QV File
Can someone tell me how to load the data into the qvw so that i can work with it?
Here is the query:
select
tn.id_teilnehmer,
trunc(geb.angebotsdatum , 'MM') ,
count (geb.id_objekt) "Geb"
,sum(case when eff.rang = 1 then 1 else 0 end) "HG"
,sum(case when obe.ob_art = 'FL' then 1 else 0 end) "Flotte"
from pb_teilnehmer tn, pb_obj_gebot geb, pb_obj_gebot_effektiv eff, objextrakt obe
where geb.id_teilnehmer_bieter = eff.id_teilnehmer(+)
and geb.seqgebot = eff.seq(+)
and geb.id_objekt = eff.id_objekt(+)
and eff.id_objekt = obe.id_objekt(+)
and geb.id_teilnehmer_bieter(+) = tn.id_teilnehmer
and 1 = case
when :scope = 1 and (tn.adr_plz like '2%' or tn.adr_plz like '17%' or tn.adr_plz like '18%' or tn.adr_plz like '19%') then 1
when :scope = 2 and (tn.adr_plz like '0%' or tn.adr_plz like '3%' or tn.adr_plz like '10%' or tn.adr_plz like '11%' or tn.adr_plz like '12%' or tn.adr_plz like '13%' or tn.adr_plz like '14%' or tn.adr_plz like '15%' or tn.adr_plz like '16%') then 1
when :scope = 3 and (tn.adr_plz like '6%' or tn.adr_plz like '7%' or tn.adr_plz like '8%' or tn.adr_plz like '9%' or tn.adr_plz like '54%' or tn.adr_plz like '55%' or tn.adr_plz like '56%' ) then 1
when :scope = 4 and (tn.adr_plz like '4%' or tn.adr_plz like '50%' or tn.adr_plz like '51%' or tn.adr_plz like '52%' or tn.adr_plz like '53%' or tn.adr_plz like '57%' or tn.adr_plz like '58%' or tn.adr_plz like '59%') then 1
when :scope = 5 then 1
else 0 end
and tn.adr_land = 'D'
and tn.ist_bieter = 1
and tn.ist_supervisor = 0
and tn.ist_gesperrt = 0
and geb.angebotsdatum(+) between :startdate and :enddate
group by tn.id_teilnehmer, trunc(geb.angebotsdatum , 'MM')
That looks like pure sql and no pl, so it should be straight forward. You will need to change the :scope parameter with a variable.
To show an input box that asks for a value and creates a variable vScope use a line like this:
LET vScope = Input('Input scope', 'Warning!');
Then replace :scope with $(vScope) in the sql statement. That should be enough to load the data. Don't forget to create a connection to the database first though.
Thanks for the response ( I was sick a few days so not able to work on this)
I did the following:
set vqvdPath='qvd\';
LET vScope = Input('Input scope', 'Warning!');
sub Regions
Regions:
LOAD *
;
SQL Select
tn.id_teilnehmer,
trunc(geb.angebotsdatum , 'MM') ,
count (geb.id_objekt) "Geb"
,sum(case when eff.rang = 1 then 1 else 0 end) "HG"
,sum(case when obe.ob_art = 'FL' then 1 else 0 end) "Flotte"
from pb_teilnehmer tn, pb_obj_gebot geb, pb_obj_gebot_effektiv eff, objextrakt obe
where geb.id_teilnehmer_bieter = eff.id_teilnehmer(+)
and geb.seqgebot = eff.seq(+)
and geb.id_objekt = eff.id_objekt(+)
and eff.id_objekt = obe.id_objekt(+)
and geb.id_teilnehmer_bieter(+) = tn.id_teilnehmer
and 1 = case
when $(vScope)= 1 and (tn.adr_plz like '2%' or tn.adr_plz like '17%' or tn.adr_plz like '18%' or tn.adr_plz like '19%') then 1
when $(vScope) = 2 and (tn.adr_plz like '0%' or tn.adr_plz like '3%' or tn.adr_plz like '10%' or tn.adr_plz like '11%' or tn.adr_plz like '12%' or tn.adr_plz like '13%' or tn.adr_plz like '14%' or tn.adr_plz like '15%' or tn.adr_plz like '16%') then 1
when $(vScope) = 3 and (tn.adr_plz like '6%' or tn.adr_plz like '7%' or tn.adr_plz like '8%' or tn.adr_plz like '9%' or tn.adr_plz like '54%' or tn.adr_plz like '55%' or tn.adr_plz like '56%' ) then 1
when $(vScope) = 4 and (tn.adr_plz like '4%' or tn.adr_plz like '50%' or tn.adr_plz like '51%' or tn.adr_plz like '52%' or tn.adr_plz like '53%' or tn.adr_plz like '57%' or tn.adr_plz like '58%' or tn.adr_plz like '59%') then 1
