i need to select 12 months of sales data for multiple items select item, ym_sales, 0, 0, 0, ... from ... where year - 2016 and month = 1 union select item, 0, ym_sales, 0, 0, ... from ... where year = 2016 and month = 2 more unions follow follow i want the result set to contain 1 row per item with the 12 months of sales data. i tried this based on an example in the SQL documentation select item, sum( C1 ), sum( c2 ), sum( c3 ), .... from ( select item, ym_sales, 0, 0, 0, ... from ... where year - 2016 and month = 1 union select item, 0, ym_sales, 0, 0, ... from ... where year = 2016 and month = 2 ) group by item which gave me an error Thanks |
If you use a derived query (i.e. a SELECT block within parantheses in the FROM clause), you have to supply a name for it and name the columns, such as
That being said, you might also be able to achive your aim by joining you data with a sa_rowgenerator(1, 12, 1) call to simply supply a dummy table with all month numbers... adding the DT did the trick. Thanks for your help.
(06 Dec '16, 12:20)
Tom Mangano
|
Hi Tom, HTH I'm using SA 16
(06 Dec '16, 11:52)
Tom Mangano
|