Dynamic union statement

[Patrick77]

Hi,

Lets say I have a table that might have from 1 to several fields/columns, something like this

ID1 ID2 ID3 1     4     7 2     5     8 3     6     9

Essentially, I am trying to create a work around for Cognos 10.2.2 external data size restriction of 20K rows (I only have author privileges for Report Studio, no other abilities). My text file is smaller than the 2.5MB limit, but can be much larger than 20K rows (perhaps 60-70K rows). I have found that if I basically just make the input file multiple columns (with 60K total values) and then I can use this to get the single column back:

Code Select Expand

Select ID1 AS ID
From ExtDat
UNION
Select ID2 AS ID
From ExtDat
UNION
Select ID3 AS ID
From ExtDat


Is there any way to make a flexible union statement such that it will incorporate as many ID columns as there are present in the table?  Or if I make it a maximum of 3 ID columns, can this be written to ignore the final select statement if ID3 isn't present so it would essentially execute just this:

Code Select Expand

Select ID1 AS ID
From ExtDat
UNION
Select ID2 AS ID
From ExtDat


[stan.parker]

Ask your Framework modeler to increase the number of rows allowed in an external data source.

[Patrick77]

Thanks Stan,
I have put in the request, but sometimes we don't get what we want. I am trying to plan for the worst case scenario. Obviously, I can just alter the report depending on the current needs, I just hoped to find a somewhat easy work around.

Ask your Framework modeler to increase the number of rows allowed in an external data source.


Sent from my iPhone using Tapatalk