Issue writing a query in sql and implementing it in Webi report

Question

I am trying to execute a query in sql to get only the records of agents associated with multiple descriptions

Example: Table name: AGENT

AGENCY_ID   PRODUCT_DESC  AGENT number
100            ABC        2000
101            ABC        2001
101            XYZ        2002
102            XYZ        2003

AGENCY_ID 101 has both ABC and XYZ descriptions.From the table, I want to pull only records from the overlapping agency

In the above case I want to pull only:

AGENCY_ID   PRODUCT_DESC  AGENT number
101         ABC           2001
101         XYZ           2002

I am also trying to execute this in the Business objects webi report query filters.

Please advise me in getting to the solution of my issue.

Thanks in advance!

Please provide sample data, the code you have tried, your table schemas and expected results — Daniel Marcus, May 03 '18 at 17:46
Tag the DBMS (MySQL, SQL Server, etc..) which you are using. — Yogesh Sharma, May 03 '18 at 18:41

score 0 · Accepted Answer · answered May 03 '18 at 18:40

0

In SQL you can use subquery

select t.*
from table t
where exists (select 1 from table  where AGENCY_ID = t.AGENCY_ID and PRODUCT_DESC <> t.PRODUCT_DESC);

Most of DBMS doesn't work with <> so use != instead in case.

answered May 03 '18 at 18:40

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 2 · answered May 03 '18 at 18:41

One of the way

DECLARE @tblActivity AS TABLE (AGENCY_ID INT , PRODUCT_DESC VARCHAR(100), AGENTnumber INT)
INSERT INTO @tblActivity (Agency_id,Product_Desc,AGentNumber)
VALUES
(100,'ABC', 2000),
(101,'ABC', 2001),
(101,'XYZ', 2002),
(102,'XYZ', 2003)

SELECT tmp.*
FROM @tblActivity tmp 
INNER JOIN @tblActivity tmp1 ON tmp1.AGENCY_ID = tmp.AGENCY_ID
                      AND tmp1.AGENTnumber != tmp.AGENTnumber
                      AND tmp1.PRODUCT_DESC != tmp.PRODUCT_DESC

score 0 · Answer 3 · answered Jul 27 '18 at 21:15

Alternatively if your RDBMS supports window/analytic functions; one could get the count of duplicate agent_IDs and then use that count as a filter.

Example: The cte here is just to setup data in a Hana database so I use from dummy to get a derived table called CTE.

Then we use that derived table in a subquery with the count() analytic to get a count of records by agency_ID and then we show all such records Where the count is > 1

A subquery/inline view is need as we need the CNT result to be generated before we can limit by it; and one can't use a analytic/window function in a having / where clause.

With CTE (AGENCY_ID,PRODUCT_DESC,AGENT_number) AS (
SELECT 100,            'ABC',        2000 from dummy union all
SELECT 101,            'ABC',        2001 from dummy union all
SELECT 101,            'XYZ',        2002 from dummy union all
SELECT 102,            'XYZ',        2003 from dummy)

SELECT B.* 
FROM (SELECT *, count(*) over (partition by Agency_ID) cnt 
      FROM CTE) B
WHERE cnt > 1

Issue writing a query in sql and implementing it in Webi report

3 Answers3