Recursive Query Vs UNION

Database
Enthusiast

Recursive Query Vs UNION

Hi Experts,

I have below tables 

Column A    Column B  Column C  Column D
123 001 1/1/2016 60
123 002 1/1/2016 -10
126 003 1/1/2016 -10
124 002 2/1/2016 40
125 002 8/1/2016 60

table B

columnA columnB
1/1/2016 WK1
2/1/2016 WK1
8/1/2016 WK2
and 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
Enthusiast

Re: Recursive Query Vs UNION

Hi Dieter,

Thanks a lot , this saved our day :) 

Thanks

1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

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 C
124 002 WK1
125 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 

Teradata Employee

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 T1

Column A    Column B  Column C  Column D
123         001       1/1/2016   60
123         002       1/1/2016  -10
126         003       1/1/2016  -10
124         002       1/1/2016   10 --> This will be included in WK1
124         002       2/1/2016   40 --> This will be included in WK1
124         002       8/1/2016   -10
125         002       8/1/2016   60 --> This will be included in WK2
126         003       17/1/2016  10 --> This will be included in WK3

table T2
 
columnA    columnB
1/1/2016   WK1
2/1/2016   WK1
8/1/2016   WK2
and so on
-------------------------------------------------------------------------------------------------------------------

Query Prepared for Week 1 and Week 2 and Week 3

sel          /* Displaying the final result set for week 1 and week 2 */         
T1.a
T1.b
,'W1' as Week Format
from T1
INNER JOIN T2
on T1.C=T2.A
where 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.A
where  T1.D <= 0
and T2.B IN ('WK1','WK2')
)
and T2.B in ('WK1','WK2')
group by 1,2

UNION

sel          /* Displaying the final result set for week 1 and week 2 */         
T1.a
T1.b
,'W1' as Week Format
from T1
INNER JOIN T2
on T1.C=T2.A
where 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.A
where  T1.D <= 0
and T2.B IN ('WK2','WK3')
)
and T2.B in ('WK2','WK3')
group by 1,2

------------------------------------------------------------------------------------------------------

Final Result SET

Column A Column B WeekFormat
124 002 WK1
125 002 WK2
126 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,c
from
(
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 dt
qualify 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
Enthusiast

Re: Recursive Query Vs UNION

Hi Dieter,

Thanks a lot , this saved our day :) 

Thanks