HSQL Parameter Queries

Creating tables and queries

HSQL Parameter Queries

Postby DynV » Mon Aug 08, 2011 12:07 am

I'm using an important query using a Set operation (UNION, INTERSECT, EXCEPT) so I need to toggle the mode Run SQL command directly ; using that mode I can't use a Parameter Query in it or running it (the query) won't yield anything (beside the column titles). I saw a few days ago a page about HSQL/HQL that it's domain name had nothing to do with OO which contained the format used by the later but unfortunately my search to find it again was in vain. I saw there is a way to use Parameter Queries with HSQL (NamedParameterJdbcTemplate) but I woner if there's a way I can use them through OO query interface (having a space aside the input text box which will display the received/outputted data in a grid).

So I'm stuck using numbers instead of Parameter Queries thus I won't save it as it ; or I'd have to give it a very specific name, in addition to parts about tables involved add, I'd have to add details about the data for those numbers. An alternative would be to create a form but as it's for internal use only, there's no need to be so thorough (adding work charge), so a query using parameters would do quite nicely.

Thank you for your help
AOO414m5 on Windows 7 Home Premium SP1. Je suis francophone.
User avatar
DynV
 
Posts: 181
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: HSQL Parameter Queries

Postby Sliderule » Mon Aug 08, 2011 12:44 am

Problem: In order ( database pun intended ) for OpenOffice Base to create a Parameter Query . . . where ( database pun intended ) a user is prompted for input . . . the Base Parser MUST be used.

But, the Base Paser will NOT accept a Union Query.

Solution: Write your UNION query as a VIEW ( withOUT a Where clause ). And, run this VIEW as a Query . . . that includes a Where clause with the Parameter syntax.

Example:

Code: Select all   Expand viewCollapse view
-- Create a View from the following Query
-- save it for example as "MyTableUnion_View"
Select
   *
From "MyTable"

   UNION

Selct
   *
From "MyOtherTable"


Now, you can create your Parameter Query from the View above . . . for example:

Code: Select all   Expand viewCollapse view
-- Now Create your Parameter Query from the VIEW above
Select
   *
From "MyTableUnion_View"
Where "MyDateField" BETWEEN :Enter_From_Date and ":Enter_To_Date


Explanation: The above gives you the best of all possible worlds. That is, you have a View that operates just like a table, with your UNION clause, and, your Query against the View, can include one or more Parameter options. :super:

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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1170
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQL Parameter Queries

Postby DynV » Mon Aug 08, 2011 1:53 am

The problem is the variable names that would be transformed from a into data, when the Parameter Queries are parsed being Bases from your affirmation, is part of the Set operation. The following is a exmaplification:
Code: Select all   Expand viewCollapse view
SELECT DISTINCT p1."columnA"
FROM "long_table" p1
WHERE p1."columnB" = :to_number_first
INTERSECT
SELECT DISTINCT p2."columnA"
FROM "long_table" p2
WHERE p2."columnB" = :to_number_second

notice both side of the INTERSECT have the same table names and both column names, so only the aliases and the parameter names are different.

I don't see how I can proceed your way with this case. Notice that the long table (named long_table) part of that set operation contain most of the meaningful data of that DB.
AOO414m5 on Windows 7 Home Premium SP1. Je suis francophone.
User avatar
DynV
 
Posts: 181
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: HSQL Parameter Queries

Postby Sliderule » Mon Aug 08, 2011 2:33 am

A 'simple' Query . . . that MUST be run with the Base Parser . . .

Code: Select all   Expand viewCollapse view
SELECT DISTINCT p1."columnA"
FROM "long_table" p1
WHERE p1."columnB" = :to_number_first
  AND p1."columnB" = :to_number_second


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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1170
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQL Parameter Queries

Postby Sliderule » Mon Aug 08, 2011 3:23 am

Perhaps, for following SQL Query is what you want:

Code: Select all   Expand viewCollapse view
Select DISTINCT
   pl."columnA"
From (
       SELECT "columnA",
              "columnB"
       FROM "long_table"
     ) as pl
WHERE pl."columnB" = :to_number_first
   OR pl."columnB" = :to_number_second


Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1170
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQL Parameter Queries

Postby DynV » Mon Aug 08, 2011 4:41 am

Sliderule wrote:A 'simple' Query . . . that MUST be run with the Base Parser . . .

Code: Select all   Expand viewCollapse view
SELECT DISTINCT p1."columnA"
FROM "long_table" p1
WHERE p1."columnB" = :to_number_first
  AND p1."columnB" = :to_number_second


1NF (First normal form) make that query so that the only possibility or it returning a result is if both parameters result to the same data thus same characters have been entered in both prompt boxes.

Here are actual results from the query behind the example:
... Well I've hit an issue when I tried to switch the proper window so I'm not able to give you a real example but I can make an approximate one:
before intersect = 2, 4, 6, 8, 9.
after intersect = 1, 3, 4, 7, 8.
altogether = 4, 8.

I don't see any difference with your second example other than some of the columns of the long table are skipped/cropped.

Update1:

In my approximated results parts, it was to split my exemplified query.

So, all being examples,
Code: Select all   Expand viewCollapse view
SELECT DISTINCT p2."columnA"
FROM "long_table" p2
WHERE p2."columnB" = :to_number_second

would yield: 1, 3, 4, 7 & 8.
Last edited by DynV on Mon Aug 08, 2011 7:44 am, edited 1 time in total.
AOO414m5 on Windows 7 Home Premium SP1. Je suis francophone.
User avatar
DynV
 
Posts: 181
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: HSQL Parameter Queries

Postby Sliderule » Mon Aug 08, 2011 4:49 am

This was the SQL Query you gave above:

Code: Select all   Expand viewCollapse view
SELECT DISTINCT p1."columnA"
FROM "long_table" p1
WHERE p1."columnB" = :to_number_first
INTERSECT
SELECT DISTINCT p2."columnA"
FROM "long_table" p2
WHERE p2."columnB" = :to_number_second


Since, BOTH SELECT clauses use the SAME TABLE ( "long_table" ) and you are using either identical OR different WHERE clause on the same column ( "columnB" ) . . . AND . . . you are using an INTERSECT ( instead of UNION ) . . . by definition . . . they will have to be the same value or nothing will be returned.

Either, you do not know what you want, or, I do not understand what you want.

In either case, I will leave it to others to answer your question(s).

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1170
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQL Parameter Queries

Postby rudolfo » Mon Aug 08, 2011 7:58 pm

The distinct applies to the column A not to the column B that is used in the where clause. Think of the following table:
Code: Select all   Expand viewCollapse view
A | B
--+--
5 | 1
4 | 2
3 | 3
3 | 4
3 | 5

The original query by DynV would return no rows for :to_number_first = 1 and :to_number_second = 3 but for :to_number_first = 5 and :to_number_second = 3 it would return a row with 3.
While SELECT columnA FROM long_table WHERE columnB = 5 AND columnB = 3 would match no records.
And likewise with the OR operator SELECT columnA FROM long_table WHERE columnB = 1 OR columnB = 3 would return 2 rows as result set.
Code: Select all   Expand viewCollapse view
:first,:second |  ORIGINAL  | AND clause | OR clause
---------------+------------+------------+-----------
  (1, 3)       | no rows    | no rows    | two rows: 5 and 3
  (5, 3)       | one row: 3 | no rows    | one row:  3
  (1, 1)       | one row: 5 | one row: 5 | one row:  5

Let's make it a real world example: B is the product Id and A is one attribute, maybe the price. So the question would be: Give me the price of both products if the price is the same for these two products. That's somehow assuming that B is a unique key in the table. But if we think of this table as a history table the same product may have several prices over the time.

My stomach feeling tells me that this can be either solved with, right the INTERSECT as DynV used it or with a (maybe) complicated CASE-WHEN in the where clause. Don't won't to invest to much logic in the case-when approach, because I am pretty sure that the Base parser won't be able to process this with parameters, either.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests