trouble with sub-select

[alcaky5]

Hi,

I run the following sub-select SQL statement in Firebird.   However, I have to use it using Cognos 10.2 Report Studio.  I am new with Cognos.  Could someone  tell me how to do this, please?

SELECT a.EMPLOYEE, a.EMPLOYEE_CODE, a.APPROVED_AMOUNT, a.PAYMENT_TYPE, a.REPORT_INDICATOR_NAME,
FROM TEST1 a
where a.EMPLOYEE_CODE = (SELECT b.EMPLOYEE_CODE FROM TEST1 b
     where (b.PAYMENT_TYPE <> a.PAYMENT_TYPE) and (b.APPROVED_AMOUNT = a.APPROVED_AMOUNT))
order by a.APPROVED_AMOUNT desc

[Robl]

Well, you could create a query that's sourced from a SQL statement rather than a package,
Although that's a fairly ugly way of doing it.

[sunny bachan prasad]

Hi Alcaky5,
Robl is right.You can generate the data item in cognos report studio by writting your own sql query and then you can use it to generate the report.Let me know if you are facing any issue.

[bdbits]

Since you are "new with Cognos", I feel obligated to point out that putting SQL directly into reports really defeats the purpose for using a tool like Cognos in the first place. The correct answer is to create a proper Framework Manager model and build your reports based on the package.

[MFGF]

Hi,

I run the following sub-select SQL statement in Firebird.   However, I have to use it using Cognos 10.2 Report Studio.  I am new with Cognos.  Could someone  tell me how to do this, please?

SELECT a.EMPLOYEE, a.EMPLOYEE_CODE, a.APPROVED_AMOUNT, a.PAYMENT_TYPE, a.REPORT_INDICATOR_NAME,
FROM TEST1 a
where a.EMPLOYEE_CODE = (SELECT b.EMPLOYEE_CODE FROM TEST1 b
     where (b.PAYMENT_TYPE <> a.PAYMENT_TYPE) and (b.APPROVED_AMOUNT = a.APPROVED_AMOUNT))
order by a.APPROVED_AMOUNT desc

I'd second Bob (bdbits) here - although you can hand-code SQL queries in reports if you absolutely must, it really defeats the purpose of having the tool, which will code these kinds of queries for you as you drag-and-drop the relevant items into your report and apply filters and sorts in the report. The key to this is defining a metadata model in Framework Manager, which describes your tables and joins. I'd hearlily recommend you spend some time getting to know how to do this successfully - it will make your life much easier in the long run.

On a side note, Firebird is the new(ish) name for Interbase, isn't it? Wow! I haven't used this for what feels like centuries! :-) Do you still use gdef to create databases?

Cheers!

MF.