on 2013 Dec 04 3:17 AM
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!
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)...
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!
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
60 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.