Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Conditional Left outer Join |
Tue, Sep 8 2009 2:28 AM | Permanent Link |
Ries van der Velden | Hello,
I want to do a left outer join on an table which has a condition to be found in another table. How can I put this in a query? The following code shows what I want to do, but doesn't work. It generates an error #11949. select P.ID_POSITIE, sum(PK.BEDRAG) as BEDRAG from POSITIES P left outer join POSITIE_KOSTEN PK on P.ID_POSITIE = PK.ID_POSITIE and Upper(PK.ID_KOSTENTYPE) in ( select Upper(ID_KOSTENTYPE) from POSITIE_KOSTEN_TYPES where Upper(BEREKENING) = Upper ('B') ) where P.ID_POSITIE between 810000 and 812000 group by ID_POSITIE I know there is a solution by first combining the second and third table [POSITIE_KOSTEN and POSITIE_KOSTEN_TYPES] and then performing the left outer join, but that is way too slow.. Does anyone know how to do this properly? Thanks in advance, Regards, Ries van der Velden |
Tue, Sep 8 2009 3:03 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ries
Does moving the second join condition to the WHERE clause work? eg select P.ID_POSITIE, sum(PK.BEDRAG) as BEDRAG from POSITIES P left outer join POSITIE_KOSTEN PK on P.ID_POSITIE = PK.ID_POSITIE where P.ID_POSITIE between 810000 and 812000 AND Upper(PK.ID_KOSTENTYPE) in ( select Upper(ID_KOSTENTYPE) from POSITIE_KOSTEN_TYPES where Upper(BEREKENING) = Upper ('B') ) group by ID_POSITIE Roy Lambert [Team Elevate] |
Tue, Sep 8 2009 12:32 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ries,
<< The following code shows what I want to do, but doesn't work. It generates an error #11949. >> In the interest of saving time, what is the exact error message that you're getting ? Thanks, -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Sep 9 2009 6:38 AM | Permanent Link |
Ries van der Velden | >In the interest of saving time, what is the exact error message that you're
>getting ? DBISAM Engine Error # 11949 SQL parsing error - Invalid expression seelct found in SELECT SQL statement at line 5 column 36 Roy: although I do not really understand why, your solution is working..! As far as I understand, the POSITIE_KOSTEN table [PK] should not be valid in the where clause, as the where clause implies to the 'from POSITIES' part.. Could you please explain why this works? Regards, Ries |
Wed, Sep 9 2009 5:48 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ries,
<< DBISAM Engine Error # 11949 SQL parsing error - Invalid expression seelct found in SELECT SQL statement at line 5 column 36 >> Okay, the only option is to put the IN condition in the WHERE clause instead. However, please note that this may cause slightly different results because the IN condition will not be used to generate NULL values for any PK rows that do not match a P row on the P.ID_POSITIE = PK.ID_POSITIE condition. When a condition is in a LOJ clause (during-join clause), then it is NULL-generating. When a condition is in a WHERE clause (after-join clause), then it is applied to the NULL values that are possibly generated from the LOJ condition(s). << Roy: although I do not really understand why, your solution is working..! As far as I understand, the POSITIE_KOSTEN table [PK] should not be valid in the where clause, as the where clause implies to the 'from POSITIES' part.. >> No, the WHERE clause conditions can be applied to any of the tables in the FROM clause. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |