Distinct and CASE

Database
Enthusiast

Distinct and CASE

Hi,

We have two tables stores_a and stores_b.

Stores_a will have store_number and date.
Stores_b will have store_number , start_date and end date.

If we want to run a report which will provide the all store numbers
and the condition should be---
1) the stores which are in stores_b and,
2) and for which the stores_a date falls between stores b start and end date that store number should be suffixed with *

Query :

Select Distinct stores_a.store_number ||
Case
When stores_a.store_number=stores_b.store_number
And
stores_a.date_s Between stores_b.start_date And stores_b.end_date
Then '*'
Else ' '
End
From
stores_a,
stores_b
;

Result:

100
100*
200
300
300*
400

For distinct 100 and 100* are not duplicates.

They are the same store numbers.

How can we eliminate duplicate store numbers ?

---------------------
Show table stores_a;
CREATE SET TABLE USER01.stores_a ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Store_Number INTEGER,
date_s DATE FORMAT 'DD/MM/YYYY')
PRIMARY INDEX ( Store_Number );

200 11/01/2005
400 01/01/2005
100 10/01/2005
300 12/01/2005

--------------
show table stores_b
CREATE SET TABLE USER01.stores_b ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Store_Number INTEGER,
start_date DATE FORMAT 'DD/MM/YYYY',
end_date DATE FORMAT 'DD/MM/YYYY')
PRIMARY INDEX ( Store_Number );

store_number start_date end_date
100 01/01/2004 01/01/2006
300 01/01/2004 01/01/2006

----------

Thanks,
Syed

5 REPLIES
Enthusiast

Re: Distinct and CASE


Not sure if I understood things correctly

but if store number is unique in both tables and you meant to have all store numbers from table A And if that store number's date fell in between start and end dates in B, then append a '*' , then this might be what you are looking for.

SELECT TRIM(A.STORE_NUMBER) || CASE WHEN A.DATE_S BETWEEN B.START_DATE AND B.END_DATE THEN '*' ELSE '' END
FROM STORES_A A LEFT OUTER JOIN STORES_B B
ON A.STORE_NUMBER = B.STORE_NUMBER

200
400
100*
300*
Enthusiast

Re: Distinct and CASE

Hi Joe,

Store Numbers are not unique in either of the tables.

Query gives following result if there are duplicate store numbers.

I have inserted some duplicate store numbers in both tables...

In the desired result set the --100 without * should not be there.

100
100*
200*
300*
400*

I have tried with distinct also.

Thanks,
Syed
Enthusiast

Re: Distinct and CASE

Try if this one works for you.

SELECT TRIM(A.STORE_NUMBER) || MAX(CASE WHEN B.STORE_NUMBER IS NULL THEN '' ELSE '*' END )
FROM STORES_A A LEFT OUTER JOIN STORES_B B
ON A.STORE_NUMBER = B.STORE_NUMBER
AND A.DATE_S BETWEEN B.START_DATE AND B.END_DATE
GROUP BY A.STORE_NUMBER
Enthusiast

Re: Distinct and CASE


Hi,

I have tried with max() in the THEN part of case ...
Group by WEBI.Data_Level_Code.. is giving error...

SELECT DISTINCT

CASE WHEN
WEBI.Year_Week_Number between DXWX_P57_DLWORK_DB01.VWI0DQX_STORE_EXCLUSION_WTC.START_WEEK and DXWX_P57_DLWORK_DB01.VWI0DQX_STORE_EXCLUSION_WTC.END_WEEK
and
WEBI.Data_Level_Code=DXWX_P57_DLWORK_DB01.VWI0DQX_STORE_EXCLUSION_WTC.RETAIL_OUTLET_NUMBER

then
MAX( trim(substr((WEBI.Data_Level_Code || ' ' || DXWX_P57_DLWORK_DB01.VWI0ROT_RETAIL_OUTLET.Store_Name),2,99))||'*'
||''|| ' for Year Week Number ' || WEBI.Year_Week_Number)

else

trim(substr((WEBI.Data_Level_Code || ' ' || DXWX_P57_DLWORK_DB01.VWI0ROT_RETAIL_OUTLET.Store_Name),2,99))||''
||''|| ' for Year Week Number ' || WEBI.Year_Week_Number
END

FROM

DXWX_P57_DLWORK_DB01.VWI0DIK_DAILY_IDQ_KPIS WEBI,
DXWX_P57_DLWORK_DB01.VWI0ROT_RETAIL_OUTLET,
DXWX_P57_DLWORK_DB01.VWI0DQX_STORE_EXCLUSION_WTC,
DXWX_P57_DLWORK_DB01.VWI0IRK_IDQ_REPORT_KPIS,
DXWX_P57_DLWORK_DB01.VWI0IRW_IDQ_RANK_REPORT_WEEKS

WHERE

( WEBI.Data_Level_Code=DXWX_P57_DLWORK_DB01.VWI0ROT_RETAIL_OUTLET.Retail_Outlet_Number )
AND ( DXWX_P57_DLWORK_DB01.VWI0IRW_IDQ_RANK_REPORT_WEEKS.Year_Week_Number=WEBI.Year_Week_Number )
AND ( DXWX_P57_DLWORK_DB01.VWI0ROT_RETAIL_OUTLET.Retail_Outlet_Number in (3500 , 3506, 3553) )
AND ( WEBI.KPI_ID=DXWX_P57_DLWORK_DB01.VWI0IRK_IDQ_REPORT_KPIS.KPI_ID )

AND (
( upper(trim(DXWX_P57_DLWORK_DB01.VWI0IRW_IDQ_RANK_REPORT_WEEKS.Latest_Week_Ind)) = 'Y' )
AND ( DXWX_P57_DLWORK_DB01.VWI0IRK_IDQ_REPORT_KPIS.SORT_ID <= 3 )
)

group by WEBI.Data_Level_Code

Error : 3504: Selected non-aggregate values must be part of the associated group.
------------------
-----------------

Result set without max :

3500 MAL for Year Week Number 200649
3506 GES for Year Week Number 200649
3553 THUES for Year Week Number 200649 <<<<<<<<<<
3553 THUES* for Year Week Number 200649

Store '3553 THUES for Year Week Number 200649 ' is repeated.

Thanks,
Syed

Enthusiast

Re: Distinct and CASE



Thanks Joe.

I have missed out the outer join part..

After Outer Join the query works fine..

...........

SELECT distinct

trim(substr((WEBI.Data_Level_Code || ' ' || DXWX_P57_DLWORK_DB01.VWI0ROT_RETAIL_OUTLET.Store_Name),2,99))||

CASE WHEN ( WEBI.Data_Level_Code=DXWX_P57_DLWORK_DB01.VWI0DQX_STORE_EXCLUSION_WTC.RETAIL_OUTLET_NUMBER )

THEN case when (WEBI.Year_Week_Number between DXWX_P57_DLWORK_DB01.VWI0DQX_STORE_EXCLUSION_WTC.START_WEEK and DXWX_P57_DLWORK_DB01.VWI0DQX_STORE_EXCLUSION_WTC.END_WEEK)

THEN '*'

else

''

end

else

''

END

|| ' for Year Week Number ' || WEBI.Year_Week_Number

from

...
.......

left outer join DXWX_P57_DLWORK_DB01.VWI0DQX_STORE_EXCLUSION_WTC

on WEBI.Data_Level_Code=DXWX_P57_DLWORK_DB01.VWI0DQX_STORE_EXCLUSION_WTC.RETAIL_OUTLET_NUMBER,

...
....

where

...
...
-----------------------------------------------------------------------

Thanks,
Syed