Hi there, 

I found that I need to build some of my queries in SQL and use prompting to pull the data; I've had a lot of luck with using #prompt('PROMPTNAME')# in my queries, which is great. 

In this instance, I want to use a query that I've built that would normally run using a prompt like this, but I want to pass it a piece of data from a query as the starting point (basically, pass something like a singleton in place of the prompt). Is this doable?

Are there ways to refer to a singleton in SQL in Cognos? To a column from a query (e.g., something in (<expression for output of a query>))?

I can just rewrite the SQL, but was hoping I could just repurpose it and replace the #prompt reference with some way to refer to something already generated by output from a normal Cognos query.

Not too many releases ago, when relative date functionality as added to Cognos Analytics, the macro function queryValue was introduced. Macro expressions are evaluated at query planning time.
Here is an example using an FM model for the GOSALES database. 

I added the following filter to the [gosales_8_2].[PRODUCT_TYPE] query subject.

[gosales_8_2].[PRODUCT_TYPE].[PRODUCT_LINE_CODE] =
# queryValue ('[gosales_8_2].[PRODUCT_LINE].[PRODUCT_LINE_CODE]' ,
'[gosales_8_2].[PRODUCT_LINE].[PRODUCT_LINE_EN] like ''Pers%''' )#

During planning the macro expression is evaluated. It runs a query that returns the PRODUCT_LINE_CODE of the product line that matches the 'Pers%' pattern.
(Please note the double single quotes around the Pers% pattern.)

When you now look at the generated SQL for the [PRODUCT_TYPE] query subject you will see a literal value as in:

WITH
PRODUCT_TYPE0 AS
(
SELECT
PRODUCT_TYPE.PRODUCT_TYPE_CODE AS PRODUCT_TYPE_CODE,
PRODUCT_TYPE.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE,
PRODUCT_TYPE.PRODUCT_TYPE_EN AS PRODUCT_TYPE_EN
FROM
gosales_8_2.gosales_8_2.gosl.PRODUCT_TYPE PRODUCT_TYPE
WHERE
PRODUCT_TYPE.PRODUCT_LINE_CODE = 3
)
SELECT
PRODUCT_TYPE0.PRODUCT_TYPE_CODE AS PRODUCT_TYPE_CODE,
PRODUCT_TYPE0.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE,
PRODUCT_TYPE0.PRODUCT_TYPE_EN AS PRODUCT_TYPE_EN
FROM
PRODUCT_TYPE0 FETCH FIRST 25 ROWS ONLY

You can also use this macro function in Data Modules and Reports.

// Henk

------------------------------
Kind regards,
HENK CAZEMIER
------------------------------