|
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! |
|
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. 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)...
(05 Dec '13, 08:18)
Volker Barth
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!
(05 Dec '13, 10:31)
Simon Door
|
|
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! 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...
(05 Dec '13, 06:34)
Volker Barth
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...
(05 Dec '13, 06:37)
Volker Barth
|
A small sample with your tables would be helpful - for a "select count(*)" query, it's difficult to guess why a sort order would be necessary at all...
(sorry for the "count(*)", pasted it without carefully looking at it ...)
hope the following example makes it more clear:
select relatie.naam, relatie_B.naam, telecom.number from (telecom left outer join contact left outer join relatie) left outer join relatie as relatie_B
How can I sort on relatie.naam and relatie_B.naam (actually the same column ...) at the same time, so they can be viewed as belonging to the same entity?
Sorry, I still don't get the point. You certainly can specify several columns as sort order, such as
However, I guess that's not what you are asking for...
The same column name, but of different instances of the same table... - how do you specify the join condition here? (When using a self-reference, i.e. a join between two instances of the same table, you would usually want to join between different rows by using different columns as join condition - such as "FROM person child left join person parent on child.parent_id = parent.id", otherwise you would simply join each row with itself...)
Could you give a small sample of the desired output?
Please show us the whole view, something that has ON conditions that define how the joins work, something that doesn't throw this error...