编写SQL查询获取多描述关联代理记录并在Webi报表实现

To get only the agent records where an AGENCY_ID is linked to more than one distinct PRODUCT_DESC, you can use a subquery to first identify those agencies, then fetch all their related records. Here's the SQL query that does exactly that:

SELECT a.AGENCY_ID, a.PRODUCT_DESC, a.[AGENT number]
FROM AGENT a
WHERE a.AGENCY_ID IN (
    SELECT AGENCY_ID
    FROM AGENT
    GROUP BY AGENCY_ID
    HAVING COUNT(DISTINCT PRODUCT_DESC) > 1
);

How This Works: #

1. Subquery: The inner query groups the AGENT table by AGENCY_ID and counts how many unique PRODUCT_DESC values each agency has. The HAVING clause filters this list to only include agencies with more than one distinct product description (like AGENCY_ID 101 in your example).
2. Outer Query: This selects all columns from the AGENT table where the AGENCY_ID is in the list returned by the subquery. This gives you every record for those multi-product agencies, matching your desired output exactly.

For Business Objects Webi: #

1. Open your Webi report and create a new query.
2. Switch to Custom SQL mode (look for options like "Edit SQL" or "Use Custom SQL" depending on your Webi version—this lets you write your own query instead of using the drag-and-drop panel).
3. Paste the query above, adjusting field quoting if needed for your database (e.g., use "AGENT number" instead of [AGENT number] if you’re using Oracle or PostgreSQL).
4. Run the query, and you’ll see only the records for agencies associated with multiple product descriptions.

内容的提问来源于stack exchange，提问作者Vindhya Giri