• Registered: 2011-02-04
  • Posts: 3

Topic: multiple union

Hi all,

In my database I have three tables: "table_201009", "table_201010", "table_201011" etc.
Basically I get a new table for every month with the same naming convention as above. In my query I was trying to chain for each month the new table with a union statement.

hard coded it would look like this:

SELECT a,b,c,d 
FROM database.table_201009 
UNION
SELECT a,b,c,d 
FROM database.table_201010
UNION
SELECT a,b,c,d 
FROM database.table_201011
UNION
SELECT a,b,c,d 
FROM database.table_201012

The problem with the code below is that it ends with a "UNION", which is wrong.

parameter :start_date do
201009 # right?
end

parameter :end_date do
1.day.ago.to_date # today
end

helper :query do |date|
"""
SELECT a,b,c,d 
FROM database.table_#{date.ym} 
"""
end

extract :SourceDatabase do

  start_date.until(end_date) to |date|
  with_sql """
  SELECT  
  #{query(today)}
  UNION
  """
end

Does anyone know a better approach doing that?

Appreciate any help.
Thanks