Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Transform Oracle SQL to Qlikview

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')

2 Replies

Re: Transform Oracle SQL to Qlikview

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Transform Oracle SQL to Qlikview

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

when $(vScope) = 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;

store Regions into $(vqvdPath)Regions.qvd;

end sub

But as result I get only a error message

Table not found

store Regions into qvd\Regions.qvd

Community Browser