Hi Jeremy,
The reason for using a subquery is completely reasonable, there are cases where the joinpath chosen by the planning software is not desirable or not possible. Good thing is this can be done.
Attached is a report spec with an example.
Query2 is used to select the desired codes based on a description field. It logically is:
Project Order_method.order_code
the optional filter is:
Order_method.order_method_en contains #prompt('achar', 'string')#
Query1 is used for the report, it has an optional filter that uses the same prompt name:
OrderHeader.OrderMethodCode
in
([Query2].[Order Method Code])
and
#prompt('achar', 'string', " 'not null' ")# is not null
When no value is given for 'achar', then both optional filters will be dropped and the generated sql looks like:
SELECT "ORDER_HEADER0"."RETAILER_NAME" AS "Retailer_Name",
SUM("ORDER_DETAILS0"."QUANTITY") AS "Quantity"
FROM "gosales_8_2"."gosl"."ORDER_HEADER" "ORDER_HEADER0"
INNER JOIN "gosales_8_2"."gosl"."ORDER_DETAILS" "ORDER_DETAILS0"
ON "ORDER_HEADER0"."ORDER_NUMBER" = "ORDER_DETAILS0"."ORDER_NUMBER"
GROUP BY "ORDER_HEADER0"."RETAILER_NAME"
ORDER BY "Retailer_Name" ASC
When a value is given the generated sql looks like:
SELECT "ORDER_HEADER0"."RETAILER_NAME" AS "Retailer_Name",
SUM("ORDER_DETAILS0"."QUANTITY") AS "Quantity"
FROM "gosales_8_2"."gosl"."ORDER_HEADER" "ORDER_HEADER0"
INNER JOIN "gosales_8_2"."gosl"."ORDER_DETAILS" "ORDER_DETAILS0"
ON "ORDER_HEADER0"."ORDER_NUMBER" = "ORDER_DETAILS0"."ORDER_NUMBER"
WHERE "ORDER_HEADER0"."ORDER_METHOD_CODE" IN
( SELECT "ORDER_METHOD0"."ORDER_METHOD_CODE" AS "Order_Method_Code"
FROM "gosales_8_2"."gosl"."ORDER_METHOD" "ORDER_METHOD0"
WHERE "ORDER_METHOD0"."ORDER_METHOD_EN" LIKE '%e%'
GROUP BY "ORDER_METHOD0"."ORDER_METHOD_CODE"
)
GROUP BY "ORDER_HEADER0"."RETAILER_NAME"
I've also attached the CognosSQL version of the query when a value is supplied.
It shows the AND-clause of the filter.
//Henk
------------------------------
HENK CAZEMIER
------------------------------
Original Message:
Sent: Wed May 22, 2019 06:22 PM
From: Jeremy Aude
Subject: Eliminate Sub-query When Optional Prompt Not Passed
Hi Henk,
Thanks for the detailed response. I am using a subquery because these are two fact tables with no common dimension. I do have it set to optional but also provided a default value to the prompt macro. I understand the Boolean error but just don't see it in this scenario.
------------------------------
Jeremy Aude
Original Message:
Sent: Wed May 22, 2019 12:47 PM
From: HENK CAZEMIER
Subject: Eliminate Sub-query When Optional Prompt Not Passed
Hi Jeremy,
The first approach should work, but it appears that the filter usage is set as 'required'. What happens when you set the filter usage to be 'optional'.
I tried a simple report based on the gosales sample model.
Logically the query is as follows: The data comes from table Order_Header and Order_Detail. The filter is specified as
Order_Method.Order_Method_en contains # prompt ('achar', 'string', 'a')#
The filter is marked as optional. It will drop the filter if no value is provided for the prompt achar.
When the prompt value is provided then the generated SQL will contain an additional join and the filter.
I noticed that you're trying to use a subquery, but would this approach be sufficient in this case?
Not entering a prompt value:
SELECT "ORDER_HEADER0"."RETAILER_NAME" AS "Retailer_Name",
SUM("ORDER_DETAILS0"."QUANTITY") AS "Quantity"
FROM "gosales_8_2"."gosl"."ORDER_HEADER" "ORDER_HEADER0"
INNER JOIN "gosales_8_2"."gosl"."ORDER_DETAILS" "ORDER_DETAILS0"
ON "ORDER_HEADER0"."ORDER_NUMBER" = "ORDER_DETAILS0"."ORDER_NUMBER"
GROUP BY "ORDER_HEADER0"."RETAILER_NAME"
ORDER BY "Retailer_Name" ASC
Entering a value for the prompt:
SELECT "ORDER_HEADER0"."RETAILER_NAME" AS "Retailer_Name",
SUM("ORDER_DETAILS0"."QUANTITY") AS "Quantity"
FROM "gosales_8_2"."gosl"."ORDER_HEADER" "ORDER_HEADER0"
INNER JOIN "gosales_8_2"."gosl"."ORDER_DETAILS" "ORDER_DETAILS0"
ON "ORDER_HEADER0"."ORDER_NUMBER" = "ORDER_DETAILS0"."ORDER_NUMBER"
INNER JOIN "gosales_8_2"."gosl"."ORDER_METHOD" "ORDER_METHOD0"
ON "ORDER_METHOD0"."ORDER_METHOD_CODE" = "ORDER_HEADER0"."ORDER_METHOD_CODE"
WHERE "ORDER_METHOD0"."ORDER_METHOD_EN" LIKE '%e%'
GROUP BY "ORDER_HEADER0"."RETAILER_NAME"
ORDER BY "Retailer_Name" ASC
//Henk
------------------------------
HENK CAZEMIER
Original Message:
Sent: Tue May 21, 2019 06:39 PM
From: Jeremy Aude
Subject: Eliminate Sub-query When Optional Prompt Not Passed
Hi,
I have a list report with two queries. The first query populates the list and has an optional prompt. The first query will issue a sub-query to query 2 to search for the existence of action values related to the prompt. Example:
select *
from query1
where TransactionID in
(select TransactionID from query 2 where action in ( #promptmany('Action Occurred', 'integer', '-1', '', '')# )
This works great for me. What I would like to do is avoid the sub-query when a user does not use the prompt, but Cognos doesn't seem to want to omit that sub-query by itself. This is a simplified version would be something like this:
select *
from query1
where 1=1
My thought was to use nested prompt macros to check for the existence of a parameter value and substitute 1=1 if not. Something like this:
#prompt ( 'Validate Filter', 'token', prompt( 'Action Occurred', 'token', '1=1' ), '[Path ID] = [Action Occurred].[Path ID]' )#
I am missing something in my syntax as I get an expression error: XQE-PLN-0312 Filter expressions must evaluate to Boolean.
Does anyone have suggestions?
Thanks,
Jeremy
------------------------------
Jeremy Aude
------------------------------
#CognosAnalyticswithWatson