Possibility to use with clause result as sub query in 2 queries in later which are outer joined

SQL++
1

I have an use case where 2 select results are left outer joined to produce a combined result. These 2 selects are having common inner query. So we decided to move that with clause and use the with result in both selects inner query. With clause result is available for only the first select and it is became empty for the second one and not returning any rows for that select. Is there any way to achieve this?

2

Could you give an example of your statement ?

Is it something along these lines:

cbq> with w as ({"v":1})
   2 select *
   3 from
   4     (select t.a from {"a":1} t join w on t.a = w.v) x
   5   left outer join
   6     (select u.c from {"c":1} u join w on u.c = w.v) y
   7   on x.a = y.c
   8 ;
{
...
    "results": [
    {
        "x": {
            "a": 1
        },
        "y": {
            "c": 1
        }
    }
    ],

?

The scope of WITH clauses changed in 7.1.1 (MB-52412) and some alias resolution issues were corrected in 7.2.

What version and edition are you using?

3

Sorry for the late reply!!!

yes indeed, we are looking for something like this. Currently we are using 6.6 version and enterprise edition. We are planning to migrate to 7.2 in coming days.

4

OK, you need the scope of WITH changes I noted for such a statement to work. Once you upgrade to 7.2 you should be able to use a statement of this form successfully.

HTH.