SQLTeam.com | Weblogs | Forums

SQL Query required

#1

Hi.

I am wanting to know if there is a way to make this query a range.

This is the main component:
dd_user_wave_57_syn a, dd_user_log_wave_57 b

I have to change each time I want to refer to a new location, so for example

dd_user_wave_58_syn a, dd_user_log_wave_58 b -- then once done go to the next one
dd_user_wave_59_syn a, dd_user_log_wave_59 b -- and so on

See full SQL below, any advice on how to make it a range of wave numbers would be great instead of 1 by 1.

select a.tls_id, a.processed_user, a.action, a.comments, a.processed_date,
b.RECORD_OPEN_DATE, b.RECORD_CLOSE_DATE
from dd_user_wave_57_syn a, dd_user_log_wave_57 b
where
a.trs = b.TRS
and a.processed_user = b.PROCESSED_USER
and action not like '%ss'
and a.processed_user in (select USERNAME from dwd_user where dwd_user.GROUPS = 'DAN')
and to_char(a.PROCESSED_DATE, 'MMYYYY') = '042019' ----- Change the month as per request e.g. 062018
order by processed_date desc

#2

How many of these wave tables do you have and is there a set number of them or new ones are created always and old ones discarded?

And is this microsoft SQL server?

#3

Hi Yosiasz.

The wave number starts at 50, currently upto 73.

No, they don't get discarded.

Yes using SQL Server

Thanks

#4

So by you saying currently it means new ones will be created above 73?

#5

yes there will be. at this stage I don't expect a new wave number to come in for probably 3months. but it will grow.

#6

Just curious why was this design approach chosen? Do you have control over it? What do these numbers suffix mean?

Anyways what I would recommend, without fully understanding this design approach, is to create a view for all of these tables. Then use that view in your select statement.

Create view waves
As
Select a,b,c, 57 as wave_number
dd_user_wave_57_syn a
 join dd_user_log_wave_57 b on a.trs = b.trs
 and a.processed_user = b.PROCESSED_USER 
Union 
Select a,b,c, 58 
From dd_user_wave_58_syn a
 join dd_user_log_wave_58 b on a.trs = b.trs 
and a.processed_user = b.PROCESSED_USER

Etc

#8

Then use as follows

select a.wave_number, a.tls_id, a.processed_user, a.action, a.comments, a.processed_date,
a.RECORD_OPEN_DATE, a.RECORD_CLOSE_DATE
from waves a 
where  action not like '%ss'
and a.processed_user in (select USERNAME from dwd_user where dwd_user.GROUPS = 'DAN')
and to_char(a.PROCESSED_DATE, 'MMYYYY') = '042019' ----- Change the month as per request e.g. 062018
order by processed_date desc
#9

This does not look like SQL Server - which does not have a to_char function. In the view - I would recommend using UNION ALL instead, unless you know that you will have duplicates that should be eliminated (the view as written will never have duplicates).

You should also look at partitioned views - and determine if these tables fall into that category. There are ways of setting up a partitioned view that could improve performance.

1 Like
#10

wave_number should allow for duplicates

#11

How so - it is hard-coded to each wave number so the results from dd_user_wave_57_syn will all have the value 57, and all of the rows from dd_user_wave_58_syn will have 58.

For example
dd_user_wave_57_syn has the following values: 1, 1, 1, 1, 57
dd_user_wave_58_syn has the following values: 1, 1, 1, 1, 58

UNION will attempt to identify the rows as duplicate - will not find a duplicate (ever). Better to use UNION ALL and avoid the extra overhead of a sort that will never find duplicate values.