1

Here is my query. I am new to Cognos and using Cognos 10. And I am having a bit difficulty in developing a report which uses a Sub Query and an Inner Join Query.

1.

SELECT ID, BATCH_DT, LOCIT FROM AOI.TEMP_BRICK
             WHERE BATCH_DT < (SELECT MAX(DATE) FROM CALENDAR)

2.

SELECT A.ID, B.SAL FROM TABLE as A LEFT OUTER JOIN TABLE as B
         WHERE A.ID=B.ID
Damienknight
  • 1,876
  • 2
  • 18
  • 34
user2134130
  • 11
  • 1
  • 2
  • I think you will need to explain a bit further. What are you trying to do? 'inner loop' is not a term usually used when writing SQL. – Nick.Mc Mar 05 '13 at 23:17
  • Also if this probably has more to do with SQL with Tm1 so I suggest you remove the tm1 tag and add in a SQL tag – Nick.Mc Mar 06 '13 at 03:20

1 Answers1

0

First, you must understand that Cognos generates its own SQL. In order for it to do that, you must define relationships between tables in Cognos Framework Manager. Once that is done your report has 3 major parts, The Report Page(s), the Prompt Page(s) and the Query(s).

  1. a.) Setup the CALENDAR and AOI.TEMP_BRICK tables in Cognos. You may want to define a relationship between TEMP_BRICK.BATCH_DT and CALENDAR.DATE (assuming your calendar has date records for every date that may be present in TEMP_BRICK).

    b.) Next you would create a new List Report. You would grab your ID, BATCH_DT and DATE fields into the list. (Date would come from the calendar if you decided to link the two tables in step A, otherwise you use the BATCH_DT field in TEMP_BRICK.)

    c.) You would open the Query pages and see that Cognos has already created one query, called Query1. You need to create a new query (we will call it qryMaxDate). That query would have one element, 'DATE' from CALENDAR. On the properties of the 'DATE' field in Data Items, you would chagne the 'Agregate Function' from None to 'Maximum'.

    d.) Now edit your Query1, add a Filter on the Date from that query. In the Expression Definition, select the Queries tab and drag the 'DATE' field from your qryMaxDate. Should look something like this [Batch Date] = [qryMaxDate].[Date]

    e.) You are done! Run the report. in this case, the user running the report is giving no input, so no Prompt page is necessary.

  2. a.) Setup Table A and B in the Framework Manager. You need to define a relationship between Table A and B in Framework Manager via a Star Schema (define A.ID = B.ID and specify 1 to n, or n to 1).

    b.) Create a new report and simply drag in elements from table a and table b. Their relationship is already defined in Framework manager, so there is no need to re-define it while writing reports.

Your second example is a great demonstration of the power of BI programs like Cognos. Report Authors dont need to fully understand the ways that two tables are joined... they simply pull out elements from each table and they work, as the relationships are already defined in the Framework.

Damienknight
  • 1,876
  • 2
  • 18
  • 34
  • 1
    So that's how it's supposed to work! As a report author I write a lot of SQL because some items were not added to our Framework. There are a lot of reports requiring rarely used information that has to be pulled in via SQL that wasn't in the Framework. – Rick Henderson Mar 07 '18 at 16:37
  • 1
    Custom SQL is quick and easy, and I often use it for basic reporting solutions that only an admin will maintain. If the data will need to be accessed by Report Authors (who may not have access to testing queries against the database directly) or if the data is going to be used often in many different reports, put it in framework. – Damienknight Mar 12 '18 at 17:23