Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
agigliotti
Honored Contributor II

Linking Direct Discovery table twice

Hello,

I'd need to write an sql statement for direct query (DB2/400).

Specifically I need to join the same table twice using different where clause something similar below:

DIRECT QUERY

DIMENSION

"ORDNUM" as "AliasColumn1",

"ORDNRG" as "AliasColumn2",

"ORDAGO" as "AliasColumn3"

MEASURE

ORDNUM,

ORDIFT,

ORDCRD

FROM "library1"."table"

JOIN "library2"."table1"

ON (SUBSTR("library2"."table1"."ATBKEY", 14, 2) ="library1"."table"."ORDTIP" and "library2"."table1"."ATBCOD" = 'C21')

JOIN "library2"."table1"

ON (SUBSTR("library2"."table1"."ATBKEY", 14, 2) ="library1"."table"."ORDTPR" and "library2"."table1"."ATBCOD" = 'C58')

...

but It give me an error for "library2"."table1" duplicated !


I read on direct discovery documentation as below:

It is not possible to refer to tables by alias in the FROM clause, you need to use the full table name.


Can someone help me with this ?


Many thanks in advance.


Best Regards

Andrea

Tags (1)
4 Replies

Re: Linking Direct Discovery table twice

Hi Andrea,

I'm not a huge expert on complex SQL statements, but it looks like the problem is caused by using the same table twice in the same SQL statement. I think you might be able to go around the problem by creating two separate views for the same table, with different WHERE conditions, and then refer to different views in your JOIN ON statements.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

agigliotti
Honored Contributor II

Re: Linking Direct Discovery table twice

I guess the only way to workaround this direct query limitation could be using a database view.

Thanks for your quick reply.

BR

Andrea

Re: Linking Direct Discovery table twice

The Technical Addendum for the Direct Discovery documentation states some limitations when using multiple tables, which is supported and should work.

Nevertheless, you may want to try as in the examples (see 3.8.2 or 3.8.3 in the doc above).

See also 3.8 with regards to limitations for multiple tables, for example the cardinality rule. Indeed, creating a view, if possible, would be also my preferred solution.

agigliotti
Honored Contributor II

Re: Linking Direct Discovery table twice

does is possible to use CASE statement in DIRECT QUERY ?

Community Browser