iam using oracle database.the below query is not working as expected.
select *
from cst_cust_attributes
where attribute_value='event'
and attribute_value='reg'
and attribute_value != 'guest';
i need all the customer data who has only attribute_values in both 'event' and 'reg'. And not in 'guest'.but iam getting the correct output.the records consist of mixed of customer data who are having all 3 attribute_values.
below is structure of the table
Name Null? Type
--------------- -------- -------------
ORGANIZATION_ID NOT NULL NUMBER(19)
CUST_ID NOT NULL VARCHAR2(32)
ATTRIBUTE_ID NOT NULL NUMBER(19)
ATTRIBUTE_SEQ NOT NULL NUMBER(10)
ATTRIBUTE_VALUE NOT NULL VARCHAR2(254)
ACTIVE_FLAG NOT NULL NUMBER(3)
CREATE_DATE DATE
CREATE_USER VARCHAR2(254)
UPDATE_DATE DATE
UPDATE_USER VARCHAR2(254)
CodePudding user response：
You can use the COUNT analytic function and conditional aggregation:
SELECT *
FROM (
SELECT a.*,
COUNT(CASE attribute_value WHEN 'event' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_event,
COUNT(CASE attribute_value WHEN 'reg' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_reg,
COUNT(CASE attribute_value WHEN 'guest' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_guest
FROM cst_cust_attributes a
)
WHERE num_event > 0
AND num_reg > 0
AND num_guest = 0;
Which, for the sample data:
INSERT INTO cst_cust_attributes (
organization_id,
cust_id,
attribute_id,
attribute_seq,
attribute_value,
active_flag
)
SELECT 1, 'C1', 1, 1, 'event', 1 FROM DUAL UNION ALL
SELECT 1, 'C1', 2, 1, 'reg', 1 FROM DUAL UNION ALL
SELECT 1, 'C1', 3, 1, 'guest', 1 FROM DUAL UNION ALL
SELECT 1, 'C2', 1, 1, 'event', 1 FROM DUAL UNION ALL
SELECT 1, 'C2', 2, 1, 'reg', 1 FROM DUAL UNION ALL
SELECT 1, 'C3', 1, 1, 'event', 1 FROM DUAL;
Outputs:
ORGANIZATION_ID CUST_ID ATTRIBUTE_ID ATTRIBUTE_SEQ ATTRIBUTE_VALUE ACTIVE_FLAG CREATE_DATE CREATE_USER UPDATE_DATE UPDATE_USER NUM_EVENT NUM_REG NUM_GUEST 1 C2 1 1 event 1 NULL NULL NULL NULL 1 1 0 1 C2 2 1 reg 1 NULL NULL NULL NULL 1 1 0
could you please tell me where to add the below condition and
trunc(CREATE_DATE) >='05-MAY-2019' AND trunc(CREATE_DATE)<='13-APR-2022' group by cust_id;
Adding a GROUP BY does not make sense if you are trying to return all the rows.
As for the date range, it depends on whether you want to check if:
eventandregand notguestvalues are only within that range; or- any
eventandregand notguestrows exists, either inside or outside that date range, and then return the rows that are within that range.
For the former, the you would add a WHERE clause inside the sub-query:
SELECT *
FROM (
SELECT a.*,
COUNT(CASE attribute_value WHEN 'event' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_event,
COUNT(CASE attribute_value WHEN 'reg' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_reg,
COUNT(CASE attribute_value WHEN 'guest' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_guest
FROM cst_cust_attributes a
WHERE create_date >= DATE '2019-05-05'
AND create_date < DATE '2022-04-13' INTERVAL '1' DAY
)
WHERE num_event > 0
AND num_reg > 0
AND num_guest = 0;
For the latter, the filters would be added to the outer query:
SELECT *
FROM (
SELECT a.*,
COUNT(CASE attribute_value WHEN 'event' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_event,
COUNT(CASE attribute_value WHEN 'reg' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_reg,
COUNT(CASE attribute_value WHEN 'guest' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_guest
FROM cst_cust_attributes a
)
WHERE num_event > 0
AND num_reg > 0
AND num_guest = 0
AND create_date >= DATE '2019-05-05'
AND create_date < DATE '2022-04-13' INTERVAL '1' DAY;
Which, for the sample data:
INSERT INTO cst_cust_attributes (
organization_id,
cust_id,
attribute_id,
attribute_seq,
attribute_value,
active_flag,
create_date
)
SELECT 1, 'C1', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C1', 2, 1, 'reg', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C1', 3, 1, 'guest', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C2', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C2', 2, 1, 'reg', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C3', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C4', 1, 1, 'event', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C4', 2, 1, 'reg', 1, DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 1, 'C4', 3, 1, 'guest', 1, DATE '2018-01-01' FROM DUAL;
Then the former query returns the C2 and C4 rows and the latter query returns only the C2 rows (as a reg value exists for C4 but it is outside of the date range).
db<>fiddle here
I want join the column
MAIL_IDfrom another tablecst_mailusing joins. to refer the correspondingmaild idfor thecust_id
Something like:
SELECT a.*,
m.mail_id
FROM (
SELECT a.*,
COUNT(CASE attribute_value WHEN 'event' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_event,
COUNT(CASE attribute_value WHEN 'reg' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_reg,
COUNT(CASE attribute_value WHEN 'guest' THEN 1 END)
OVER (PARTITION BY cust_id) AS num_guest
FROM cst_cust_attributes a
WHERE create_date >= DATE '2019-05-05'
AND create_date < DATE '2022-04-13' INTERVAL '1' DAY
) a
JOIN cst_mail m
ON (a.cust_id = m.cust_id)
WHERE num_event > 0
AND num_reg > 0
AND num_guest = 0;
CodePudding user response：
you need to use a query like below which uses exists to check for all conditions
select *
from cst_cust_attributes c
left join cst_mail n on c.CUST_ID =n.CUST_ID
where c.attribute_value='event'
and exists
(
select 1 from cst_cust_attributes b where c.CUST_ID = b.CUST_ID
and b.attribute_value='reg'
)
and not exists
(
select 1 from cst_cust_attributes a where c.CUST_ID = a.CUST_ID
and a.attribute_value = 'guest'
)
CodePudding user response：
So you basically just want attribute_value to be "event" and "reg" ? So why just dont use the following select ?
select * from cst_cust_attributes where attribute_value in('event','reg');