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
Key | Other data |
---|---|
1 | sdsd |
2 | asdsa |
3 | fgdfgd |
Config
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 |
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
Key | T | d1 | d2 |
---|---|---|---|
1 | b | 3 | 4 |
1 | c | 5 | 6 |
2 | a | 7 | 8 |
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:
#T |
---|
a |
b |
c |
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?
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:
order by
is not required and was only added to make the output easier to readKey
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 optionFor 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:
order by
is only for readability purposesSybase ASE 16.0 SP04 PL04
instance[Key]
with Key
, and isnull
with coalesce
, and this will generate the same results when plugged into Cetin's Postgres fiddle