Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-17-2016
09:10 AM

06-17-2016
09:10 AM

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

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-22-2016
06:55 AM

1 ACCEPTED SOLUTION

6 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-17-2016
12:05 PM

06-17-2016
12:05 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-17-2016
12:13 PM

06-17-2016
12:13 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-17-2016
12:21 PM

06-17-2016
12:21 PM

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);

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-19-2016
11:56 PM

06-19-2016
11:56 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-20-2016
11:37 PM

06-20-2016
11:37 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-22-2016
06:55 AM