topic Re: HELP WITH SQL in QlikView

HELP WITH SQL

fdelacal — Mon, 29 Oct 2012 09:19:19 GMT

I need information to pull data from a SQL database.

I've never worked with SQL, and the computer company where I have to extract the data does not make me much attention.

The code associated with the company delegation will be located in the parameter table for enterprise delegation (ridppar where ridppar.cod_ent = rid.cod_ent AND ridppar.cod_del = rid.cod_del AND ridppar.cod_param = 2421)

Each company / delegation to treat stocks will locate the item (ridel where ridel.cod_ent = rid.cod_ent AND ridel.cod_del = rid.cod_del), but only the articles that are published on the Group (ridel.char8_al = "S "), and accumulate their stocks available and publishable. The stock is equal to the stock publishable available (ridel.sre_art - ridel.crs_art), but considering only the publishable percentage (ridel.dec4_al), and adjusting the minimum limits (ridel.dec5_al) and maximum (ridel.dec6_al), if these are reported.

In addition to the above details, also extract the selling price of the article that will be offered to members of the group. This information will be extracted from the rate table sales (tvl), namely a rate previously be preset in parameter 2422 (ridpparwhere ridppar.cod_ent = rid.cod_ent AND ridppar.cod_del = rid.cod_del AND ridppar.cod_param = 2422).

Only be exported items have a greater than zero stock available and priced sales rate preset in parameter 2422.

These are the fields I need to get

ridel.cod_art.. no codition
artic.ref_art ---------------artic where artic.art_amb = ridel.art_amb AND artic.cod_art = ridel.cod_art
artic.ctl_art
galmarca.nom_mar--------------- marca where marca.cod_mar = artic.mar_art
artic.dep_art
ridel.cod_uea_st
tvl.cuea_tvl-------------- tvl. where tvl.cve_amb = rid.cve_amb AND

tvl.cod_tav = int(ridppar.val_param) AND

tvl.art_amb = artic.art_amb AND

tvl.cod_art = artic.cod_art AND

tvl.cod_sas = “EUR” AND

tvl.can_tvl = 0 AND

tvl.cod_uea = ridel.cod_uea_st

tvl.cuea_tvl-------------- tvl. where tvl.cve_amb = rid.cve_amb AND

tvl.cod_tav = int(ridppar.val_param) AND

tvl.art_amb = artic.art_amb AND

tvl.cod_art = artic.cod_art AND

tvl.cod_sas = “EUR” AND

tvl.can_tvl = 0 AND

tvl.cod_uea = ridel.cod_uea_st

tvl.pre_tvl-------------- = As in the field tvl.cuea_tvl
prove.cif_pro--------------- prove where prove.pro_amb = prove.pro_amb AND

prove.cod_pro = ridel.cod_pro

prove.raz_pro

Thnks for all.

Re: HELP WITH SQL

Gysbert_Wassenaar — Mon, 29 Oct 2012 10:30:17 GMT

This line doesn't make sense: prove.cif_pro--------------- prove where prove.pro_amb = prove.pro_amb

I suspect one of the prove.pro_amb is supposed to be some other field. You'll have to add the correct line to the where clauses.

If the sql statement below doesn't work, start with one table and build it a table at a time.

SELECT

artic.ref_art,

artic.ctl_art,

artic.dep_art,

galmarca.nom_mar,

prove.cif_pro,

prove.raz_pro,

ridel.cod_art,

ridel.cod_uea_st,

tvl.cuea_tvl,

tvl.pre_tvl

FROM

artic,

galmarca,

prove,

ridel,

ridpar,

tvl

WHERE

artic.art_amb = ridel.art_amb AND

artic.cod_art = ridel.cod_art AND

galmarca.cod_mar = artic.mar_art AND

prove.cod_pro = ridel.cod_pro AND

tvl.cve_amb = rid.cve_amb AND

tvl.cod_tav = int(ridppar.val_param) AND

tvl.art_amb = artic.art_amb AND

tvl.cod_art = artic.cod_art AND

tvl.cod_sas = "EUR" AND

tvl.can_tvl = 0 AND

tvl.cod_uea = ridel.cod_uea_st

In case the above doesn't work:

Step one, start with just one table:

SELECT

artic.ref_art,

artic.ctl_art,

artic.dep_art

FROM

artic

WHERE

artic.art_amb = ridel.art_amb AND

artic.cod_art = ridel.cod_art

Step two, we add a second table:

SELECT

artic.ref_art,

artic.ctl_art,

artic.dep_art,

galmarca.nom_mar

FROM

artic,

galmarca

WHERE

artic.art_amb = ridel.art_amb AND

artic.cod_art = ridel.cod_art AND

galmarca.cod_mar = artic.mar_art

etc...

Re: HELP WITH SQL

fdelacal — Mon, 29 Oct 2012 11:05:52 GMT

Thank you for your prompt response.
I will try and I commented!

Re: HELP WITH SQL

fdelacal — Mon, 29 Oct 2012 14:35:40 GMT

hi, again i tried the first select that you do, but no working good,

and now i am trying select step by step.

it works, at first level but when i add table tvl. i think it do a bad relationship create 300.000.000 line.. something no work well

Re: HELP WITH SQL

Gysbert_Wassenaar — Mon, 29 Oct 2012 15:12:32 GMT

Without knowledge of the database schema this is going to be very difficult to solve. What you can try is load a limited set of data from each table into qlikview separately. If the database supports the TOP statement you can do: select top 100 * from artic

For oracle databases you can use this: select * from artic where rowno < 100

That way you can see what fields each table has and figure out on what fields the tables should be joined. Once you know that you can try to construct the correct sql statement to get the data you need with only one sql statement.

Re: HELP WITH SQL

fdelacal — Mon, 29 Oct 2012 15:37:53 GMT

That´s a good idea, i will trie with this..
Thanks a lot for all.