topic SQL subquery (SELECT * FROM table WHERE x IN (SELECT....) ) in QlikView

SQL subquery (SELECT * FROM table WHERE x IN (SELECT....) )

Thu, 16 Jul 2009 19:01:01 GMT

Hi all,

i want to make a query with a subquery in QV, but i get an error.

What am i doing wrong?

Here's my query:


SQL SELECT * FROM JEST WHERE OBJNR IN (SELECT OBJNR FROM JCDS WHERE UDAT >= '$(vDateFrom)');

$(vDateFrom) is a date i use to extract from a certain timestamp.

Does anybody know if this statement is possible or if im doing something wrong?

Tnx

Rey-man

SQL subquery (SELECT * FROM table WHERE x IN (SELECT....) )

Thu, 16 Jul 2009 19:12:59 GMT

Ciao!

Try this:

Completo:
Load OBJNR as OBJNR_OK,
UDAT
WHERE UDAT >= $(vDateFrom);
SELECT OBJNR, UDAT
FROM JCDS;

Noconcatenate

Carico:
LOAD *
where EXISTS(OBJNR_OK, OBJNR);
SELECT * FROM JEST;

Drop table Completo;

Daniela

SQL subquery (SELECT * FROM table WHERE x IN (SELECT....) )

Thu, 16 Jul 2009 19:24:41 GMT

Hi Daniela,

i dont want to load the complete JCDS table first.

It contains so much date, it would kill the performance of the system.

Thats why i want to extract only the data from JEST where the objnr is updated in the JCDS table.

Rey-man

SQL subquery (SELECT * FROM table WHERE x IN (SELECT....) )

Thu, 16 Jul 2009 20:10:03 GMT

I have tried this and it works perfectly!
Then must go for your

Set vFornit = 'F0000095';

Carico:
LOAD
CHIAVE as Ofr_Chiave,
text (ARTICOLO) as Articolo;
SQL SELECT
CHIAVE,
ARTICOLO
FROM M_ORDFORR
WHERE chiavetes in (select chiave
from M_ordforT
where FORNITORE = '$(vFornit)');

Daniela

SQL subquery (SELECT * FROM table WHERE x IN (SELECT....) )

Thu, 16 Jul 2009 20:40:45 GMT

First of all I would test the SQL query.

That should work normally but maybe there is an issue to the variable replacement in the query.

Therefore I would run a Debugger and when the step is on the query, I read the generated query in the middle window. There you should see the exact query Qlikview is trying to run and check if the variable value is well replaced in it.

Hope this will help you.

Actually what is the error message ?

Rgds,

Sébastien