New post
Question
· Apr 29, 2021

Is it possible to use Table Value Constructors outside of INSERT/UPDATE statements?

#SQL #Caché

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.valueId

Is it supported in Cache SQL or not?

Product version: Caché 2018.1
Discussion (6)0
Log in or sign up to continue
Vitaliy Serdtsev · Apr 29, 2021

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.

0 0
Evgenii Ermolaev · Apr 29, 2021

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.

0 0
Evgenii Ermolaev · Apr 29, 2021

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.

0 0
Vitaliy Serdtsev · Apr 30, 2021

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.Branch
0 0