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] Union statement in SQL view

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


Joined: 18 Feb 2011
Posts: 92
Location: Czech Republic
PostPosted: Wed Jul 27, 2011 6:14 am    Post subject: [Solved] Union statement in SQL view Reply with quote
Hi all .
I have this simple test code
Code:

SELECT "Datum" FROM "Vykaz_Prace"
UNION
SELECT "Datum" FROM "Vykaz_Stroje"


It doesn’t allow me to use UNION statement due to error The given command is not a SELECT statement , Only queries are allowed . Does it mean that I can’t make an UNION in query ?

Thanks for advice .
Cenda

Last edited by Cenda on Thu Jul 28, 2011 2:54 am; edited 1 time in total
Back to top
View user's profile Send private message Yahoo Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Wed Jul 27, 2011 6:32 am    Post subject: Reply with quote
Cenda:

You said / asked:

Cenda wrote:
Does it mean that I can’t make an UNION in query ?

No. You can create and use a UNION or UNION ALL ( UNION will eliminate duplicates, whereas, UNION ALL allows duplicates Smile ) query.

Define your SQL as you wrote it above, BUT, before you RUN and / or SAVE it, you must EITHER :
  1. On the Query Toolbar, click on the icon with green check mark and letters SQL . . . Run SQL command directly
  2. From the Menu: Edit -> Run SQL command directly . . . should contain the check mark
Explanation: The OpenOffice Base parser does NOT recognise the UNION or UNION ALL commands. BUT, you can still use it by passing the SQL directly to the database driver ( in your case, the built-in HSQL 1.8 driver ). To do this, you tell the OpenOffice Base that this Query should be run directly withOUT Base first checking / parsing it. Smile

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
Cenda
Power User
Power User


Joined: 18 Feb 2011
Posts: 92
Location: Czech Republic
PostPosted: Thu Jul 28, 2011 2:53 am    Post subject: Reply with quote
Hi Sliderule
Thank you for explanation . It helps me lot . The details like that are the hardest to get over . I will mark this tread as solved .
Cenda
Back to top
View user's profile Send private message Yahoo Messenger
Philip Marlowe
Power User
Power User


Joined: 15 May 2011
Posts: 94
PostPosted: Wed Sep 07, 2011 2:10 pm    Post subject: Reply with quote
i'm trying to use union as well but can't do it the way you say: i've a sql botton on the toolbar but pushing it still give me error... help!!!
Back to top
View user's profile Send private message Send e-mail
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Wed Sep 07, 2011 2:15 pm    Post subject: Reply with quote
I canNOT help you based on the description you gave above, since, I canNOT see you SQL.

Are the number of fields returned AND the field types the same in both / all Select statements?

Sliderule
Back to top
View user's profile Send private message
Philip Marlowe
Power User
Power User


Joined: 15 May 2011
Posts: 94
PostPosted: Wed Sep 07, 2011 2:24 pm    Post subject: Reply with quote
here's the file
http://dl.dropbox.com/u/4395035/aaa.odb
the query with the union is Query1
as you can see iniz and somma_cons have the same number and names of colum and data type...
thanks for helping!
Back to top
View user's profile Send private message Send e-mail
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Wed Sep 07, 2011 2:46 pm    Post subject: Reply with quote
Philip Marlowe:

The file you provided, the SQL for Query1 reads as:

Code:
SELECT
   "id_art",
   "somma_cons"
FROM "iniz"

   UNION

SELECT
   "id_art",
   "somma_cons"
FROM "somma_cons"

BUT, the database you provide only contains the following tables:
  1. art
  2. art_1
  3. dett_cons
and there are no tables or views by the name of "iniz" and "somma_cons" . . . therefore . . . the SQL will fail.

Since, you created your UNION from two Queries, rather than from TABLES or VIEWS, you can create the UNION query using the following SQL, AND, be sure to press the SQL icon on the toolbar:

Code:
SELECT
   "id_art",
   "qta_0" AS "somma_cons"
FROM "art"

  UNION   --  <<< Be sure to press SQL on Toolbar because of UNION

SELECT
   "id_art",
   "qta_0" AS "somma_cons"
FROM "art"

Alternative: You can make the two Queries as two VIEWS, and, use the VIEW names in the UNION query. Smile

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

Sliderule

Last edited by Sliderule on Wed Sep 07, 2011 3:03 pm; edited 1 time in total
Back to top
View user's profile Send private message
Philip Marlowe
Power User
Power User


Joined: 15 May 2011
Posts: 94
PostPosted: Wed Sep 07, 2011 3:02 pm    Post subject: Reply with quote
oh thanks! it works with tables!
so to make it work with queries (tha's what i need) i've to create them as views? i've tried but couldn't make it!
Back to top
View user's profile Send private message Send e-mail
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Wed Sep 07, 2011 3:05 pm    Post subject: Reply with quote
Since, once again, I canNOT see what you have done, I cannot help you.

Either repost the OpenOffice database so I can see it with VIEWS, OR, describe in words EXACTLY what you have done, including the SQL query.

Sliderule
Back to top
View user's profile Send private message
Philip Marlowe
Power User
Power User


Joined: 15 May 2011
Posts: 94
PostPosted: Wed Sep 07, 2011 3:15 pm    Post subject: Reply with quote
here it is:
http://dl.dropbox.com/u/4395035/aaa.odb

query2 works: union between 2 tables

i really need to use union with the query somma_cons

you said i can create it as a view: how?

thanks again
Back to top
View user's profile Send private message Send e-mail
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Wed Sep 07, 2011 3:34 pm    Post subject: Reply with quote
Philip Marlowe:
  1. Open your OpenOffice database file ( *.odb )
  2. Click on the Queries icon on the left, under Database
  3. Under Queries, right click on the query somma_cons
  4. From the drop-down menu, choose: Create as View
  5. Assign a name, I would suggest: somma_cons_view
  6. Create the Query: using the UNION clause, and you can use any Table or View that exists in your database, but, you canNOT use Query names Smile
  7. After you create the Query, be sure to press the SQL icon on the tooolbar
  8. Run the Query
  9. Save the Query, so you can just double click on the NEW QUERY next time you want to run it
I hope this helps, please be sure to let me / us know.

Sliderule
Back to top
View user's profile Send private message
Philip Marlowe
Power User
Power User


Joined: 15 May 2011
Posts: 94
PostPosted: Wed Sep 07, 2011 3:40 pm    Post subject: Reply with quote
everything's working fine, grazie mille!!! that's thank you very much in italian!
Back to top
View user's profile Send private message Send e-mail
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