Alternative To Case When With Subqquery To Avoid Error (illegal Expression In When Clause Within Case Statement)

My table structure looks similiar to this Customer_id Country item_type Order_Size Dates Codes A401 US Fruit Small 3/14/2016 11 A401 US

Solution 1:

You can change your code to connect to the table that drives the answer to "is this code supposed to be included" before the other rules. Be sure to do an outer join to a dataset that is distinct on the code field to prevent dupes.

This approach includes the result of your conditional before all the other rules and won't raise an error. Below I created a volatile table with a unique primary index on the code to do this but you could join to a derived table and get a similar result.

create volatile table vt_fruit_exp
( Customer_id char(4)
, Country char(2)
, item_type varchar(20)
, Order_Size char(5)
, Dates date
, Codes byteint)
primary index (Customer_id) oncommit preserve rows;

insertinto vt_fruit_exp values('A401','US','Fruit'    ,'Small' ,'2016-03-14', 11);
insertinto vt_fruit_exp values('A401','US','Fruit'    ,'Big'   ,'2016-05-22', 12);
insertinto vt_fruit_exp values('A401','US','Vegetable','Small' ,'2016-07-12', 11);
insertinto vt_fruit_exp values('B509','US','Vegetable','Small' ,'2015-03-25', 92);
insertinto vt_fruit_exp values('B509','US','Vegetable','Big'   ,'2014-03-15', 11);
insertinto vt_fruit_exp values('B509','US','Vegetable','Small' ,'2014-03-01', 34);
insertinto vt_fruit_exp values('A402','CA','Fruit'    ,'Small' ,'2016-03-14', 56);
insertinto vt_fruit_exp values('A402','CA','Fruit'    ,'Big'   ,'2016-05-22', 76);
insertinto vt_fruit_exp values('A402','CA','Fruit'    ,'Small' ,'2016-07-12', 85);
insertinto vt_fruit_exp values('A403','CA','Vegetable','Small' ,'2016-07-12', 11);
insertinto vt_fruit_exp values('A403','CA','Vegetable','Small' ,'2015-03-25', 16);
insertinto vt_fruit_exp values('A403','CA','Vegetable','Big'   ,'2014-03-15', 17);
insertinto vt_fruit_exp values('A403','CA','Vegetable','Small' ,'2014-03-01', 12);

create volatile table Table1
( Codes byteint,Code_In_flg byteint) uniqueprimary index (Codes) 
oncommit preserve rows
;
insertinto Table1 values (11,1); 
insertinto Table1 values (76,1);
insertinto Table1 values (12,1);

-- Each country-> how many repeated customers for each item_type are present AFTER they purchased Order_size=Big.  Only items purchased with order_size<>Big-- Country item_type   Count(Distinct(Customer_id))-- CA  Vegetable   1-- US  Vegetable   1-- CA  Fruit       1SELECT
  Country
, item_type
, count(customer_id) 
FROM (
  select Country,customer_id, t.item_type, count(*)  as REPEATS
  from (
    Select
      t.*
    , Min(CaseWhen Order_Size ='big'Then Dates End) Over (PartitionBy Customer_Id, Item_Type) As Min_Big
    From vt_fruit_exp As T
  ) t
where dates > min_big
groupby1,2,3) D
groupby1,2;

-- This works now but I wanted to add one more condition as to only when the codes are within certain table with condition so I wanted to add multiple conditions with one being subquery with the case when I modified my code.-- use a join to the table that refers to whether the code is to be included or not instead of attempting a subquery withing ordered analyticSELECT
  Country
, item_type
, count(customer_id)
FROM (
  select Country,customer_id, t.item_type, count(*)  as REPEATS
  from (
    Select
      t.*
    , Min(CaseWhen Order_Size ='big'And b.Code_In_flg=1Then Dates End) Over (PartitionBy Customer_Id, Item_Type) As Min_Big
  from vt_fruit_exp T leftouterjoin Table1 B on t.Codes=b.Codes 
 ) t
where dates > min_big
groupby1,2,3) D
groupby1,2