Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
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
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
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.
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
Thanks for the feedback. I fiddled around a bit and finally got it to work
Good Stuff!
Andy
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?