Cognos 10.1.1 Report Studio - Reusing Columns in a Single Pass-through Query

[locus]

Hi All,

I have a query as below that works 100% as a standalone extraction from an Informix data source. However, when I try to build a report in Report Studio with it, I get the error.

''The server returned an unrecognizable query framework response''

The problem is clearly where I reuse a table called 'type_detail' as 'type_detail1' and 'type_detail2' with different 'where' clauses for the repeated columns. If I remove this from the query, selecting only 'type_detail' it works fine, but this piece is a critical part of the query.

I have tried to edit governers by fiddling with SQL Join Syntax and Parameter Syntax in FM to take them away from the server but no difference.
How can I achieve this in Cognos SQL without having to do any DB work??
How do I use table 'type_detail' as 'type_detail1' and 'type_detail2' in the single SQL??

Many thanks.

*****

SELECT type_detail1.ldesc,
br_ord_line.br_ref_no,
br_ord_hdr.br_ord_date,
br_ord_line.stock_no,
stocks.descr,
stocks.color_size,
stocks.pack_size,
type_detail2.ldesc,
br_ord_line.update_user,
"'" || br_ord_line.update_dtime,
(CASE WHEN br_ord_line.bo_type IN (3,6) THEN br_ord_line.qty_picked * br_ord_line.pack_size
WHEN br_ord_line.qty_picked IS NOT NULL THEN (br_ord_line.qty_ordered - br_ord_line.qty_picked) * stocks.pack_size
ELSE (br_ord_line.qty_ordered * stocks.pack_size) END)
FROM br_ord_hdr, br_ord_line, stocks, type_detail type_detail1, type_detail type_detail2
WHERE br_ord_hdr.branch_no = 36021
AND br_ord_line.br_ord_no = br_ord_hdr.br_ord_no
AND (br_ord_line.bo_type IN (1,2,3)
OR br_ord_line.bo_type > 6
AND EXISTS ( SELECT 1
FROM ord_stats
WHERE ord_stats.br_ref_no = br_ord_line.br_ref_no
AND ord_Stats.branch_no = 36021 )
AND (br_ord_line.update_user LIKE "%XWEL%dup%"
OR br_ord_line.bo_type <> 17 ) )
AND stocks.stock_no = br_ord_line.stock_no
AND stocks.dept_no = 55
AND type_detail1.type_id = "br_ord_type"
AND type_detail1.lang_id = "U"
AND type_detail1.type_det_id = br_ord_hdr.br_ord_type::char(2)
AND type_detail2.type_id = "bo_type"
AND type_detail2.lang_id = "U"
AND type_detail2.type_det_id = br_ord_line.bo_type::char(2)
ORDER BY br_ord_line.br_ref_no,
br_ord_line.stock_no,
type_detail2.ldesc

[MFGF]

Hi,

Is there a reason you are attempting this in a hard-coded SQL query rather than modelling it in Framework Manager? Regardless, you appear to have some native syntax embedded in your query, which is probably not legal in Cognos SQL.

You might try encasing the whole query in braces {} to denote native SQL. This may be enough to remove the parsing that is causing your issue. It's worth a try...

Cheers!

MF.

[squish88]

Have you tried changing it from Native to PassThrough by any chance?

[locus]

Thanks for the responses....

@squish....yes it is a pass through query already.

@MFGF....bracing didn't help, but in the end I did I took it back to FM (You're right should do this in the first place), and have pretty much resolved the original issue but have now ended up with a new one. I hope it's cool to ask a new question as part of the same post.

For the type_detail.ldesc I am given a status...could be 'Backorder', 'Cancelled, etc.
The value for which is calculated with this:

CASE WHEN br_ord_line.bo_type IN (3,6) THEN br_ord_line.qty_picked * br_ord_line.pack_size
WHEN br_ord_line.qty_picked IS NOT NULL THEN (br_ord_line.qty_ordered - br_ord_line.qty_picked) * stocks.pack_size
ELSE (br_ord_line.qty_ordered * stocks.pack_size) END)

There will be a hit for each entry...mutiple entries per status.
What I am trying to do is return a column/value for each status type.
So rather than list them vertically for each entry:

Description1|Value
Description2|Value
Description1|value
Description1|Value
Etc

To Have:

Description1 Tot Value|Description2 Tot Value|Description3 Tot Value...etc. Horizontally in the list report

So the total for each description type, each in their own column.
What I have read is that a nested case statement may be the way to achieve this, as IF/ELSE cannot be used together with CASE. Is this correct?

Cheers,
Ant