I have a table in a SQL database where rows are not stored if all values are default.

Currently this is in Sybase but it also needs to work in Postgres in the near future.

i.e:

Parties

I have a table in a SQL database where rows are not stored if all values are default.

Currently this is in Sybase but it also needs to work in Postgres in the near future.

i.e:

Parties

Config

For performance reasons, if the default values are d1=1, d2=2 then those are not saved in the database. So the actual saved data looks like this:

Config

I'm trying to write a view that "fills in" this missing data dynamically and can't work out how to do it.

Basically for every row in the parties table I want to see three rows row in the view - one for each of a,b,c - and it should fill in 1,2 for d1 and d2.

My first thought was to create a temporary table containing:

It feels like I should then be able to do this with outer joins onto that table, but I can't get the database to cooperate. In particular the additional rows never appear in the result set no matter how I try to do the join.

Alternatively, doing it as a Union might work for a single Party but I don't see how to do it to fill in the missing rows for every Party.

Is there any way to do this, please?

• sql
• postgresql
• sybase

• consider updating the question (and tags) to indicate which Sybase RDBMS (ASE? IQ? SQLAnywhere? Advantage?) and version; these RDBMS products have different SQL dialects so a working answer is going to depend on knowing which Sybase RDBMS, and to a lesser degree the version) – markp-fuso Commented Mar 10 at 22:39
• What are the performance reasons for not storing every row? Assuming there are any performance gains with this approach, do they justify the extra complexity and potential efficiency issues caused by "filling in the blanks" using a view? – JohnH Commented Mar 11 at 6:54
• @JohnH Obviously this is a much simplified example. In the real case the main problem was the lengths of write queues and time take for them to clear when thousands of new entries were created, each having many rows of config and it needed to create tens of thousands of "default" values. So, yes, the change was needed. – Tim B Commented Mar 11 at 11:49
• In most use cases the service rehydrates the data as it loads it basically for free, but some of the reporting is now not showing the defaults as that's done purely in SQL so that's what I am looking at now. – Tim B Commented Mar 11 at 11:51

2 Answers 2

with letters as (select T from (values ('a'), ('b'), ('c')) t(T)),
     fulldata as (select p.Key, l.T
                  from parties p
                           cross join letters l)
select ft.Key, ft.t, coalesce(c.d1, 1) d1, coalesce(c.d2, 2) d2
from fulldata ft
         left join config c on c.Key = ft.Key and c.T = ft.T;

Here is DBfiddle demo

Here is an explanation of the code:

For every key in parties table, there should be a T value of a, b, c as a requirement. Thus with:

select T from (values ('a'), ('b'), ('c')) t(T))

we build a virtual table of (CTE named letters):

Cross joining this table with keys from parties table (CTE named fulldata):

select p.Key, l.T
                  from parties p
                           cross join letters l

we get:

We are using an initial CTE naming it letters then we use it to create another CTE named fulldata. We use fulldata (shown above) in main query.

fulldata contains all key, T combinations we need. Thus we left join this with the Config table to generate final result. We connect the tables using key, T fields and if there isn't a match d1 and d2 would be null. In that case we set the default values using coalesce:

-- letters and fulldata CTEs here 
select ft.Key, ft.t, coalesce(c.d1, 1) d1, coalesce(c.d2, 2) d2
from fulldata ft
         left join config c on c.Key = ft.Key and c.T = ft.T;

This generates:

If running Sybase ASE, or any other database, where CTEs are not supported ...

Start by generating a dynamic table of all possible T characters:

select 'a' as T union select 'b' union select 'c'
go

 T 
 - 
 a 
 b 
 c 

Next we generate a cartesian product (aka cross join) with the parties table:

select  p2.[Key],
        dt2.T as T
from    parties p2
join    (select 'a' as T union select 'b' union select 'c') dt2
on      1=1
order by 1,2
go

 Key         T 
 ----------- - 
           1 a 
           1 b 
           1 c 
           2 a 
           2 b 
           2 c 
           3 a 
           3 b 
           3 c 

NOTES:

• the order by is not required and was only added to make the output easier to read
• Key is a reserved keyword in Sybase ASE hence the use of brackets ([ / ]); brackets tend to be a bit cleaner than wrangling with (ASE) T-SQL's quoted identifier option

For the main attraction we perform a left join from this cartesian product to the config table:

select  dt1.[Key],
        dt1.T,
        isnull(c.d1,1) as d1,
        isnull(c.d2,2) as d2

from    (select p.[Key],
                dt2.T
         from   parties p
         join   (select 'a' as T union select 'b' union select 'c') dt2
         on     1=1
        ) dt1

left
join    config c
on      dt1.[Key] = c.[Key]
and     dt1.T     = c.T

order by 1,2
go

 Key         T d1          d2          
 ----------- - ----------- ----------- 
           1 a           1           2 
           1 b           3           4 
           1 c           5           6 
           2 a           7           8 
           2 b           1           2 
           2 c           1           2 
           3 a           1           2 
           3 b           1           2 
           3 c           1           2 

NOTES:

• again, the order by is only for readability purposes
• above was tested against a Sybase ASE 16.0 SP04 PL04 instance
• this is fairly basic SQL so it should work in most (all?) RDBMSs, obviously tweaking to match the target SQL dialect, eg ...
• replace [Key] with Key, and isnull with coalesce, and this will generate the same results when plugged into Cetin's Postgres fiddle