Use the same Query resultset as 'fileds' in different, non related documents

[dreamquartz]

Hi All,

I have a need to re-use the resultset of a Query multiple times for different documents.
I cannot re-run the Query, because the outcome is random.
The Query is run, based on User input

Is there a way to access the resultset in different Writer Documents?
The documents are:
1. An overview listing of the resultset, containing Surname, GivenName (1 page)
2. An personalized document, containing Surname, GivenName, and random information spread over multiple pages (10 pages)
3. An overview listing of the resultset, containing Surname, GivenName & the same random information as under 2. (1 page)

The documents are pre-formatted and the layout is not under the control of the User.

Any suggestions are much appreciated,

Dream

[Mountaineer]

You can store either the input of the user (to be able to re-run the query) or your result.

The first would be a similiar approach as using a filter-table to show a subset of your table. Your problem with this may be to prevent changing the filter-table while you run your different tasks.

Or you may store the resultset by using

Code: Select all

INSERT INTO myResult SELECT your_current_query

You will need a separate table myResult to store the resultset and delete the contents (previous query) before you do the insert. Your documents will then reference the new myResult instead of the the current query.

[dreamquartz]

I like that principle,

Thank you so much.

Will work on that and get back to you on that one.

Dream

[dreamquartz]

That almost works perfectly.

I could not make 2 issues work:
1. It appears that User input is not possible for an INSERT INTO.
I have a Query that requires a User Input to provide the correct information

2. Trying to add a CURRENT TIMESTAMP for the INSERT INTO, appears not possible

However, the main principle works for an INSERT INTO using DIRECT SQL.

Question:
Is the way around these 2 issues, of which 1. 'creating a Query with User Input' is very important?

Dream

[Mountaineer]

How do you get user input for your query?
parameter-query like :UserInput

Actually I never tested before. Anyway it should always be possible to use the same approach used by power-filters and generate a filter-table, wich can be filled through a form and referenced by a sub-SELECT replacing :User-Input by (SELECT field FROM input-table)

A current timestamp should be no problem. Will test it on the weekend. I assume you use the default HSQLDB?

[dreamquartz]

How do you get user input for your query?
parameter-query like :UserInput

Actually I never tested before. Anyway it should always be possible to use the same approach used by piwer-filters and generate a filter-table, wich can be filled through a form and referenced by a sub-SELECT replacing :User-Input by (SELECT field FROM input-table)

A current timestamp should be no problem. Will test it on the weekend. I assume you use the default HSQLDB?

The following is an example of we normally use for finding a Person:
1. We create a View to prepare for finding a Person.
The creation of the View is based on may years of experience on how the User receives personal information.

Code: Select all

 (
		 "vIDCheck"."PersonID" LIKE '%' || :Person || '%' OR
		 "vIDCheck"."PersonInfo" LIKE '%' || :Person || '%' OR
		 "vIDCheck"."Surname, GivenName" LIKE '%' || :Person || '%' OR
		 "vIDCheck"."Surname, Nickname" LIKE '%' || :Person || '%' OR
		 "vIDCheck"."GivenName Surname" LIKE '%' || :Person || '%' OR
		 "vIDCheck"."Nickname Surname" LIKE '%' || :Person || '%' OR
		 "vIDCheck"."PersonInfo" LIKE '%' || :Address || '%')

The second User Input 'Person' provides the User to even find someone, based on Gender, and DOB
The last User Input ':Address' provides the User to even find someone based on Address.
The pipes '||' combined with '%" provide the User to enter just a part of the name, either beginning or end....

I am reading up on 'INSERT INTO' and so far I am finding that an ':UserInput' can not be used.......
Still do not find anything about the 'TIMESTAMP'.

I am using Split HSQLDB 2.6.1. on Lo 7.3.3.2 and RazorSQL 10.0.4

Dream