topic Re: how to use with clause (pl sql) in qlikview in QlikView

how to use with clause (pl sql) in qlikview

Anonymous — Fri, 14 Aug 2015 07:42:07 GMT

Hi everyone,

I was just wondering if it is possible to use a with clause when doing a sql select within qlikview?

I tried but it doesnt seem to work.

If anyone maybe have an example of how they use a with clause in qlikview that will be brilliant

What I want to do is to use the with clause to extract data from source and save my results in a qvd.

Kind regards,

Stefan

Re: how to use with clause (pl sql) in qlikview

ikarimov — Fri, 14 Aug 2015 07:52:37 GMT

Hello!

Create new document QV, press ctrl+e and copy-past this code

(you need change connect string offcourse and change pl_sql script to your script)

//Connect to oracle db, use wizard

OLEDB CONNECT32 TO [Provider=MSDAORA.1;User ID=;Data Source=dload.world]

//PL-SQL script to execute on oracle base

Test_Table: // Name of table

SQL SELECT * FROM TABLE1

WHERE TABLE1.FIELD1 like '407%';

//Create qvd file

STORE Test_Table into Test_Table.qvd;

//Optional, delete table.

drop table Test_Table;

Re: how to use with clause (pl sql) in qlikview

maxgro — Fri, 14 Aug 2015 07:59:04 GMT

I tried a with in QlikView and it works

I've just copied a working sql statement in Qlik

sql

WITH flusso_count AS (

SELECT id_flusso, COUNT(*) AS f_count

FROM stg_code_etl

GROUP BY id_flusso)

SELECT id_key, s.id_flusso,

dc.f_count

FROM stg_code_etl s,

flusso_count dc

WHERE s.id_flusso = dc.id_flusso;

EDIT

oledb 32 connection (oracle provider) to oracle

Re: how to use with clause (pl sql) in qlikview

Anonymous — Fri, 14 Aug 2015 07:59:28 GMT

Hi there,

I know how to do a sql select using connect string and then saving to qvd.

My problem is I want to use a WITH clause in qlikview using sql select statement but it doesnt work

Re: how to use with clause (pl sql) in qlikview

ikarimov — Fri, 14 Aug 2015 08:29:35 GMT

Its working for me:

ODBC CONNECT32 TO [base_32b;DBQ=dload.world];

SQL WITH table1 AS (

SELECT fio, COUNT(*) AS cnt

FROM spisrab

GROUP BY fio)

select tn,cnt from spisrab aa,table1 bb

where aa.fio=bb.fio

but not working by:

OLEDB CONNECT32 same base.

Try this.

Re: how to use with clause (pl sql) in qlikview

awhitfield — Fri, 14 Aug 2015 08:57:26 GMT

Hi Stefan,

the use of the WITH clause relates to COMMON TABLE EXPRESSIONS, check out the attached link:

https://msdn.microsoft.com/en-GB/library/ms175972.aspx

HTH-Andy

Re: how to use with clause (pl sql) in qlikview

Anonymous — Fri, 14 Aug 2015 10:04:50 GMT

Thanks for the feedback. I fiddled around a bit and finally got it to work

Re: how to use with clause (pl sql) in qlikview

awhitfield — Fri, 14 Aug 2015 10:12:49 GMT

Good Stuff!

Andy

Re: how to use with clause (pl sql) in qlikview

leblev — Wed, 27 Mar 2019 20:31:12 GMT

S33pie007, I know this is old but how did you make the CTE work in QlikView so that you could save it to a QVD file?