Evgenii Ermolaev · Apr 29, 2021

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, 
 From (values (1), (2), (3), (4), (5)) v(valueId)
     left Join otherTable m
        on = v.valueId

Is it supported in Cache SQL or not?

Product version: Caché 2018.1
1 0 6 84
Log in or sign up to continue


I don't quite understand what you want to get.

If in the forehead, then so:

 From (Select valueId Union Select Union Select Union Select Union Select 5) v
     left Join otherTable m
        on 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.Branchnvl(sum(%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)) from yourtable where year("Date")=2021 group by Branchm
on m.Branch %inlist v.Branches
group by v.Branch

Sorry, 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