how to use with clause (pl sql) in qlikview

[Anonymous]

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

[ikarimov]

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;

[maxgro]

I tried a with in QlikView and it works

I've just copied a working sql statement in Qlik

T:

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

[Anonymous]

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

[ikarimov · Accepted solution]

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.

[awhitfield]

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

[Anonymous]

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

[awhitfield]

Good Stuff!

Andy

[leblev]

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?