Is it possible to use Table Value Constructors outside of INSERT/UPDATE statements?
I need to show the absence of data, so I have to join the list of predefined values with a result of a select statement.
However, it seems like Table Value Constructors in JOIN are either not supported, or I do not understand the syntax.
Basically, I am going for something like this:
Select v.valueId, m.name
From (values (1), (2), (3), (4), (5)) v(valueId)
left Join otherTable m
on m.id = v.valueIdIs it supported in Cache SQL or not?
Comments
I don't quite understand what you want to get.
If in the forehead, then so:
Select v.valueId, m.name From (Select 1 valueId Union Select 2 Union Select 3 Union Select 4 Union Select 5) v left Join otherTable m on m.id = v.valueId
But you can achieve the same thing more easily through IN or %INLIST.
Union seems to achieve what I want.
I am unsure what did you mean by using IN though.
The problem I was facing was aking to this:
I have a table called 'Sales' with a column called 'Branch' which holds the data of where the sale occured. I need to show all sales that occured in a specific time period for all branches, even if a branch had zero sales. The catch is there is no separate table that holds Branches so there's nothing I can join with AND I need to modify the values of the column 'Branch' a bit (e.i. some branches should be considered as one, some branches should have their name changed, etc.).
So the best possible solution I could find was to use Table Value Constructor to create the list of needed branches in the query and join it with a partially filtered table to get those Branches with 0 sales.
Can you provide a small sample table with the data and the result you want to get?
Assuming a table like this (omitting the ID column and some others)
| Branch | Date | Sum |
|---|---|---|
| Western branch | 2021-03-15 | 35,000 |
| Eastern branch | 2020-12-11 | 37,000 |
| Eastern branch | 2021-01-29 | 12,000 |
| Northern branch | 2021-02-03 | 14,500 |
And I need to show the amount of sales in 2021 with the result like this:
| Branch | Count |
|---|---|
| Northwest | 1 |
| Oriental | 1 |
| Southern | 0 |
Where Northwest matches sales that happened either in a Northern or Western branches, Oriental matches sales that happened in Eastern branches, and Southern matches sales that happened in Southern branches.
How do you count "Count"? Why is "Northwest" 1 instead of 2 for 2021?
For now so:
select v.Branch, nvl(sum(c %FOREACH(v.Branch)),0) "Count" from
(
select 'Northwest' Branch,$listbuild('Northern','Western') Branches union
select 'Oriental',$listbuild('Eastern') union
select 'Southern',$listbuild('Southern')
) v
left join
(select replace(%exact(Branch),' branch','') Branch,count(* %FOREACH(Branch)) c from yourtable where year("Date")=2021 group by Branch) m
on m.Branch %inlist v.Branches
group by v.BranchSorry, Northwest indeed was meant to be 2, that was a typo.
Count is simply the amount of sales.
The code sample you provided is quite interesting, although I am not yet familier with some of the Cache SQL parts