OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[solved] sqlite3 and outer joins

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
kadil
Power User
Power User


Joined: 13 Feb 2008
Posts: 50

PostPosted: Tue Apr 15, 2008 5:25 am    Post subject: [solved] sqlite3 and outer joins Reply with quote

Hello,

I am writing an app using OOo (upgraded to 2.4without issues) on Ubuntu and using SQLite via ODBC (unixODBC). Anyway when I assemble a saved query using sqlite syntax, as per their website i get the following error

"[unixODBC][SQLLite]RIGHT and FULL OUTER JOINs are not currently supported(1)"

Does anybody know if this is a limitation of:
(1) the odbc driver, or
(2) unixODBC, or
(3) OOo's interaction with ODBC, or
(4) the linux version of OOo?

Any possible solutions or suggestions would be appreciated. I probably would have taken a different approach if I knew this would be a limitation.

Rgds,
Kim


Last edited by kadil on Tue Apr 15, 2008 11:48 pm; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Apr 15, 2008 7:45 am    Post subject: Reply with quote

Kim:

You asked:
Kim wrote:
using SQLite via ODBC (unixODBC). Anyway when I assemble a saved query using sqlite syntax, as per their website i get the following error

"[unixODBC][SQLLite]RIGHT and FULL OUTER JOINs are not currently supported(1)"

I am using SQLite3 with ODBC under Windows, with OpenOffice 2.4 . I too, get the same error message. I think ( yes, I know, Sliderule thinking is the same as an error message Laughing ) the following might help.

According to: http://www.shokhirev.com/nikolai/abc/sql/joins.html

http://www.shokhirev.com/nikolai/abc/sql/joins.html wrote:

RIGHT OUTER JOIN and FULL OUTER JOIN

Currently SQLite supports neither of the above syntax. However there is easy workaround: use LEFT OUTER JOIN with interchanged tables.

The FULL OUTER JOIN clause is not supported as well.


An additional link from the SQLite site at: http://www.sqlite.org/omitted.html says:
http://www.sqlite.org/omitted.html wrote:
RIGHT and FULL OUTER JOIN

LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.


I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
kadil
Power User
Power User


Joined: 13 Feb 2008
Posts: 50

PostPosted: Tue Apr 15, 2008 11:45 pm    Post subject: Reply with quote

Thank you Sliderule, problem solved. My new query syntax is:

SELECT "t_invoices"."credit_due", "t_invoices"."invoice_number", "t_invoices"."amount", "t_bank_transactions"."date", "t_bank_transactions"."amount", "t_invoices"."paid"
FROM "t_invoices" LEFT OUTER JOIN "t_bank_transactions" ON "t_invoices"."transaction" = "t_bank_transactions"."ui"
ORDER BY "t_invoices"."credit_due" ASC


My old syntax based on sqlite3 docs was:

SELECT "t_invoices"."credit_due", "t_invoices"."amount", "t_bank_transactions"."date", "t_bank_transactions"."amount", "t_invoices"."paid"
FROM ( "t_bank_transactions" RIGHT JOIN "t_invoices" ON "t_invoices"."transaction" = "t_bank_transactions"."ui" )
ORDER BY "t_invoices"."credit_due" ASC


Cheers,
Kim
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group