How to get the opposite value of a selected prompt value present in the table

[laxmanrao83]

Hi All,

I have a report with HMGNS_FLOW_ID Prompt. User will select a HMGNS_FLOW_ID and there is HMGNS_LNK_ID associated with each HMGNS_FLOW_ID in the table.

Data
HMGNS_FLOW_ID   HMGNS_LNK_ID
746939                  44091
746938                  44091

For each HMGNS_FLOW_ID, there will be opposite HMGNS_FLOW_ID with common HMGNS_LNK_ID (as mentioned above).

So when user selects HMGNS_FLOW_ID(746939) in the propmt, report should get filtered for opposite HMGNS_FLOW_ID (746938).

I found the SQL query solution for this, but not able to convert this logic in Report Studio.
Please provide me the solution to this issue.

SQL Query:
SELECT HMGNS_FLOW_ID,HMGNS_LNK_ID FROM "db2DWH".TIS_LOC_HTVN_HMGNS_FLOW_LNK_RLTSHP WHERE HMGNS_LNK_ID=
(select HMGNS_LNK_ID from "db2DWH".TIS_LOC_HTVN_HMGNS_FLOW_LNK_RLTSHP WHERE HMGNS_FLOW_ID=746939) AND HMGNS_FLOW_ID<>746939

Data
HMGNS_FLOW_ID   HMGNS_LNK_ID
746939   44091
746938   44091

Query OutPut
HMGNS_FLOW_ID HMGNS_LNK_ID
------------- ------------
        746938        44091

[pricter]

Create two queries
In the first one use the filter
HMGNS_FLOW_ID<>746939
and in the second one
HMGNS_FLOW_ID=746939

Joined them with the  HMGNS_LNK_ID.

The in the query that is produced from the join
use the HMGNS_LNK_ID from the second query
and the HMGNS_FLOW_ID from the first query

[laxmanrao83]

Hi pricter

The logic which you gave is working fine.

Thanks very much