Database
Enthusiast

## Recursive Query Vs UNION

Hi Experts,

I have below tables

`Column A    Column B  Column C  Column D123         001       1/1/2016  60123         002       1/1/2016  -10126         003       1/1/2016  -10124         002       2/1/2016  40125         002       8/1/2016  60 table BcolumnA    columnB1/1/2016   WK12/1/2016   WK18/1/2016   WK2and so on `

My requirement is to exclude all those values in column A where values in column D is less than 0

what i am doing right now is making sets of data based on week number and then making a UNION of it

Sel A,B from table A where

A not IN(

sel distinct A from table A  INNER JOIN  table B

on column A=Column A

where table B.columnB IN ('WK1','WK2') and table A.Column D<0)

UNION

Sel A,B from table A where

A not IN(

sel distinct A from table A  INNER JOIN  table B

on column A=Column A

where table B.columnB IN ('WK2','WK3) and table A.Column D<0)

Since i need to do this UNION repetedly for 10 weeks so making these many UNIONS is hampering the performance .

Please let me know if the same can be achieved with Recursive query

Tags (1)

Accepted Solutions
Highlighted
Enthusiast

## Re: Recursive Query Vs UNION

Hi Dieter,

Thanks a lot , this saved our day :)

Thanks

1 ACCEPTED SOLUTION
6 REPLIES

## Re: Recursive Query Vs UNION

Im not totally clear on wht you want but I think it is a list of unique table1.a values where any of the table1.d values is less than zero...

SELECT a FROM table1 GROUP BY a HAVING MIN(d)>=0;

Enthusiast

## Re: Recursive Query Vs UNION

Hi Tod,

Actually i want to exclude all those values from colum a where values in column D is less than 0 and then roll it up at week level based on my second table which us B

the final data set should be as below

`Column A  Column B  Column C124       002         WK1125       002         WK2   `

I am excluding 123 and 126 from column A because is has one of the negative values in column D of table A

Thanks

## Re: Recursive Query Vs UNION

Not sure what "roll it up" means since the result doesn't obviously include an aggregate of any of the columns. The previous answer is the beginning but becomes a subquery:

SELECT t1.a, t1.b, t2.b FROM t1, t2

WHERE t1.c=t2.a

AND t1.a IN (SELECT a FROM table1 GROUP BY a HAVING MIN(d)>=0);

Enthusiast

## Re: Recursive Query Vs UNION

`Table T1Column A    Column B  Column C  Column D123         001       1/1/2016   60123         002       1/1/2016  -10            126         003       1/1/2016  -10124         002       1/1/2016   10         --> This will be included in WK1124         002       2/1/2016   40         --> This will be included in WK1124         002       8/1/2016   -10125         002       8/1/2016   60         --> This will be included in WK2126         003       17/1/2016  10         --> This will be included in WK3table T2 columnA    columnB1/1/2016   WK12/1/2016   WK18/1/2016   WK2and so on-------------------------------------------------------------------------------------------------------------------Query Prepared for Week 1 and Week 2 and Week 3sel                         /* Displaying the final result set for week 1 and week 2 */         T1.aT1.b,'W1' as Week Formatfrom T1INNER JOIN T2on T1.C=T2.Awhere T1.A not in (sel distinct T1.A from T1  INNER JOIN  T2             /* Inner query to exclude all values from T1 table where value of D is <= 0 in any of the day for week 1 and Week 2*/ON T1.C=T2.Awhere  T1.D <= 0and T2.B IN ('WK1','WK2'))and T2.B in ('WK1','WK2')group by 1,2UNION sel                            /* Displaying the final result set for week 1 and week 2 */         T1.aT1.b,'W1' as Week Formatfrom T1INNER JOIN T2on T1.C=T2.Awhere T1.A not in (sel distinct T1.A from T1  INNER JOIN T2               /* Inner query to exclude all values from T1 table where value of D is <= 0 in any of the day for week 1 and Week 2*/ON T1.C=T2.Awhere  T1.D <= 0and T2.B IN ('WK2','WK3'))and T2.B in ('WK2','WK3')group by 1,2------------------------------------------------------------------------------------------------------Final Result SETColumn A  Column B  WeekFormat124        002       WK1125        002       WK2126        003       WK3`

Hi Todd,

I think i need to rephrase it a little bit.

So here in table A i have 124 and 002 for date 2/1/2016 and 8/1/2016 so it means it lies in W1 and W2 both and it has value of column D negative for date 8/1/2016 so this should be excluded and this needs to be repeated for all the weeks W1-W2, W2-W3, W3-W4 ....... and then the final result has to be aggregated at week level. Above is the query which i prepared hope this helps in understanding the requirement better.

Since I need to do this for 13 weeks so i am making 13 joins in my query as i did above for WK1, WK2 and WK3.

So is there any other way apart from UNION which i can use .

Thanks

Nitesh

Junior Contributor

## Re: Recursive Query Vs UNION

Seems like you want to return only the weeks with no negative value in the current and next week.

This should be a simple task using OLAP-functions like this:

`select a,b,cfrom (   select       T1.a,      T1.b,      T2.b,      -- NULL if any negative value      case when min(d) >= 0 then 1 else 0 end as positiveFlag   from T1   INNER JOIN    (      select a,b,         -- need a sequence to check for next week         dense_rank() over (order by b) as weeknumber      from  T2    ) as T2   on T1.C=T2.A   group by 1,2,3 ) as dtqualify positiveFlag = 1   -- current week not negative     and min(weeknumber)    -- next row is from next week        over (partition by a               order by weeknumber              rows between 1 following and 1 following) = weeknumber +1    and min(positiveFlag)  -- next week not negative        over (partition by a              order by weeknumber              rows between 1 following and 1 following) = 1`
Highlighted
Enthusiast

## Re: Recursive Query Vs UNION

Hi Dieter,

Thanks a lot , this saved our day :)

Thanks