2 Replies Latest reply: Oct 18, 2012 10:17 AM by Ralph Förster RSS

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

        • Re: Transform Oracle SQL to Qlikview
          Gysbert Wassenaar

          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

              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