Information Link subquery with prompts inside subquery

[Mark Herrmann]

Hi,

I'm trying to build an Information Link which uses a subquery and prompts () like this:

SELECT

*

FROM

"SCHEMA"."TABLE" S1

WHERE

(S1."BATCHID" IN (

SELECT DISTINCT

BATCHID

FROM

SCHEMA.TABLE

WHERE

 

))So basically the user should be able to use prompts to identify items out of manufacturing (not knowing which other items where produced within the same batchID).

Therefore a subquery should then create a distinct list of batchIDs from those items and the final query would retrieve data for all items in those batchIDs (not just the items which were specified using the prompts).

I've tried a few things, the closest I get is using a column element like this:

 

which gives me this SQL:

SELECT

*

FROM

"SCHEMA"."TABLE" S1

WHERE

(S1."BATCHID" IN (

SELECT DISTINCT

BATCHID

FROM

SCHEMA.TABLE

))

AND

Does anyone have an idea how to achieve the desired SQL w/o using custom SQL (which is very error prone from a maintenance point of view)

Thanks, Mark

[Mark Herrmann]

Actually even with custom SQL I don't get this to work...When working with just the original table and manually moving the <conditions> inside the brackets I get this:

SELECT	*FROM   "SCHEMA"."TABLE" S1WHERE	(S1."BATCHID" IN (		SELECT DISTINCT			BATCHID		FROM			SCHEMA.TABLE      WHERE         (S1."ORIGINAL_LOT_NAME" = 'XYZ')	))

So since <conditions> is always pre-fixed with here an S1. it filters out all the additional lots which were generated from the subquery..When I use an alias so I can prompt for non-S1 columns and <conditions> will be pre-fixed with an S2. I need to use a join between S1 and S2 and I get this:

SELECT	*FROM	"SCHEMA"."TABLE" S1,	"SCHEMA"."TABLE" S2,WHERE	(S1."BATCHID"=S2."BATCHID")	AND (S2."BATCHID" IN (		SELECT DISTINCT			BATCHID		FROM			SCHEMA.TABLE		WHERE			(S2."ORIGINAL_LOT_NAME" = 'XYZ')   ))

So this multiplies everything with everything and I get thousands of duplicates and adding a DISTINCT to the whole query a) kills the performance and b) after a very long time results in this error: Failed to execute query: [tibcosoftwareinc][Oracle JDBC Driver][Oracle]ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

 (HRESULT: 80131500) ...

[Mark Herrmann]

I had raised this question with support and here is the answer:

We enquired about this requirement with experts and seems this functionality is not

yet available.

The maximum that we could do was to move the inside the sub query. But that

query will eventually result in a correlated sub query.

As of now, the sub queries are not flexible enough to implement prompts.

We would request you to file an enhancement for this at our IDEA's portal So here is the idea in case you want to vote: https://ideas.tibco.com/ideas/TS-I-8262