cancel
Showing results for 
Search instead for 
Did you mean: 

How to sort on a recurring table

Former Member
0 Kudos
2,197

I have this "view" on three tables:

select count(*) from (telecom left outer join contact left outer join relatie) left outer join relatie as relatie_B

(the table telecom effectively always points to the same parent table "relatie", but this can be via an intermediate table (contact)

How can sort on columns of tbis table "relatie" (and "relatie_B") so that rows can be properly kept together. I currently create a temporary table for this which I "manually" fill with de values I need to sort on, but there must be a more elegant (and faster) way

Thanks in advance!

Accepted Solutions (0)

Answers (2)

Answers (2)

Breck_Carter
Participant

None of the code you have posted can possibly work, including your "this works!" answer:

select relatie.naam, 
       relatie_B.naam,  
       telecom.number,  
       if relatie.naam is not null 
          then relatie.naam 
          else relatiealias.naam 
       endif as sortable_col 
  from (telecom 
           left outer join contact  
           left outer join relatie)  
        left outer join relatie as relatie_B 
 order by sortable_col

Could not execute statement.
Correlation name 'relatiealias' not found
SQLCODE=-142, ODBC 3 State="42S02"
Line 5, column 1

-- The REAL problem (no join conditions) is revealed after fixing
-- the above error...

select relatie.naam, 
       relatie_B.naam,  
       telecom.number,  
       if relatie.naam is not null 
          then relatie.naam 
          else 'dummy value' 
       endif as sortable_col 
  from (telecom 
           left outer join contact  
           left outer join relatie)  
        left outer join relatie as relatie_B 
 order by sortable_col

Could not execute statement.
There is no way to join 'telecom' to 'contact'
SQLCODE=-146, ODBC 3 State="42000"
Line 1, column 1

We are happy to help, but you must post actual code.

VolkerBarth
Contributor
0 Kudos

I second your , hm, suggestion...

That being said, in case there are FKs specified between the involved tables, then the JOIN without an ON condition can certainly work (and will be a KEY JOIN by default). However, I don't think it will work here because of the self-join - even if there is a FK relationship within the table's columns I guess a KEY JOIN would fail to know which table alias would be assigned which end of the FK relationship...

Another reason why we need actual code (including the tables's definition)...

Former Member
0 Kudos

Sorry, I, indeed, should have posted more info and had better checked the labels I used before posting.

The actual query was generated by an application and had a lot of unneccessary stuff in it. I tried to simplify things by omitting this en translating some labels ...

And there indeed are fk relations specified between the tables ("telecom" has fk's to "contact" and "relatie" and the "contact" has a fk to "relatie". I know to little of sql to know for sure wether this is a KEY JOIN or not ...

Again sorry for the inconvenience!

Former Member
0 Kudos

The problem lies in the fact that either relatie.naam contains null values or relatie_B.naam contains null values. But I've found an easy solution

I've created a "virtual" column and can apparently also sort on this column too!

select relatie.naam, relatie_B.naam, telecom.number, if relatie.naam is not null then relatie.naam else relatiealias.naam endif as sortable_col from (telecom left outer join contact left outer join relatie) left outer join relatie as relatie_B order by sortable_col

and this works!

VolkerBarth
Contributor
0 Kudos

Ah, I see. Well, then you can shorten your query by replacing your IF expression with an ISNULL (or COALESCE) function call, such as

...telecom.number, isnull(relatie.naam, relatiealias.naam) as sortable_col...
VolkerBarth
Contributor
0 Kudos

Just to add: It is good practise to explain the particular problem (sorting when the according columns can both have nulls) already in the original posting - that would have given many folks the chance to help you fast with a working solution instead of counter-questions...