Cognos DLookup equivalent

[Danielbryanuk]

Hi all

I need an efficient method of obtaining the value of cross section of a table (manual and ad hoc table generated using sql query) in a query. I would like to avoid using "Case When" of "If" constucts as the table is very large.

I have investigated using "tuple" but understand this is only to be used with a dimensional database, whereas mine is relational.

Something similar to using DLookup in MS Access would be ideal (I used to use it quite a lot).

So...any ideas?

Obviously getting the adhoc table into the database is the best way to go, but my admins won't let me do this!!

Thanks
Dan

I have

 

[blom0344]

DLookup is typical Jet SQL, AFAIK there is no equivalent in Cognos. However you still need to address a database somehow, which leads to the question on where to store the table. How would you query the data involved if you are not allowed to store it in a database?

Ties Blom

 

[Danielbryanuk]

As you can probably already guess I don't know too much about these things...The temporary table (I call it a "table" but it is an sql query that produces another normal query) is created using the following sql:

"select 'Unconstrained' as Text,
'A' as Category,
2.0 as part_1000,
1.3 as part_2500,
1.2 as part_5000,
1.0 as part_10000,
0.9 as part_25000,
0.8 as part_more,
5000 as min from dual
union all
select 'Dynamic Control Portfolio' as Text,
'B' as Category,
2.0 as part_1000,
1.3 as part_2500,
1.2 as part_5000,
1.0 as part_10000,
0.9 as part_25000,
0.8 as part_more,
5000 as min from dual"

etc etc for mulitple rows...

It has the following properties:
SQL Syntax - Native
Data Source - GMISD1

I need to reference the output query, for example here to return the value where,

column = part_1000
row = "Unconstrained" (from field "text")

i.e. 2

Is there another way apart from using case when, if etc?

 

[blom0344]

You CAN define the entire expression as an SQL query subject and - to keep things tidy - use a model query subject on it. As a SQL object Cognos - if allowed - will use a 'common table expression' (CTE) to store the SQL you are using. A common table expression is a sort of temp table:

http://www.sqlservercentral.com/articles/T-SQL/68651/

The same should be available in Oracle.

Cognos has a governor setting on the 'with' clause , which is another moniker for a CTE

Once you have modelled this, you can use the model query subject as if it represents a real table and use it in joins, filters and use the query items from the model query subject.

Ties Blom