topic Transform Oracle SQL to Qlikview in QlikView

Transform Oracle SQL to Qlikview

Wed, 10 Oct 2012 14:06:13 GMT

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

Re: Transform Oracle SQL to Qlikview

Gysbert_Wassenaar — Wed, 10 Oct 2012 14:44:56 GMT

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.

Re: Transform Oracle SQL to Qlikview

Thu, 18 Oct 2012 14:17:11 GMT

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