[Solved] Using db table view in stand alone form

Discuss the database features

[Solved] Using db table view in stand alone form

Postby cwdavi1 » Wed Oct 09, 2019 12:50 am

I have a form that uses a table view (to get a table union to work). The final form query is against the view. Works fine. Until I create a stand alone writer document from the base form. I edited the Form properties and added the database source but when I run it I get object not found: Table_View_Name. Is there any way to add the view to the stand alone form?

Thank you.
Last edited by cwdavi1 on Thu Oct 10, 2019 5:42 pm, edited 2 times in total.
Libreoffice 6.3 on Windows 10 Pro
cwdavi1
 
Posts: 14
Joined: Fri Sep 13, 2019 9:47 pm

Re: using db table view in stand alone form

Postby F3K Total » Wed Oct 09, 2019 12:24 pm

Hi,
just tried it, no problem using AOO 4.1.7.
Did you reselect the view as Content after selecting the Data source and Content type "Table"?
Reason is, that the Content disappears when selecting the Datasource.
Attachments
Standalone.PNG
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 924
Joined: Fri Dec 16, 2011 8:20 pm

Re: using db table view in stand alone form

Postby cwdavi1 » Thu Oct 10, 2019 2:40 am

Nope, different case.

Data source is "DB_Name" that contains the tables and the view. Content did disappear after selecting data source and content type but I pasted it back in, it's an sql command. This is how it looks:

Code: Select all   Expand viewCollapse view
Data source:  DB_Name
Content type:  SQL Command
Content:  select "title", "aid", "description" from "masterT" where "aid" IN
(select DISTINCT "aid" from "ViewAids" WHERE UPPER ( "title" ) LIKE UPPER ( '%' || :P1 || '%' ) OR UPPER ( "otitle" ) LIKE UPPER ( '%' || :P1 || '%' ) )


Result is:
"The data could not be loaded."
"user lacks privilege or object not found: ViewAids

ViewAids is:
SELECT "aid", "title", "otitle" FROM "contextT1"
union
SELECT "aid", "htitle" as "title", "otitle" FROM "contextT2"

contextT1 and 2 are tables in DB_Name. When the odb file is open the view is listed as a table in DB_Name.
Last edited by robleyd on Thu Oct 10, 2019 3:50 am, edited 1 time in total.
Reason: Added Code tags
Libreoffice 6.3 on Windows 10 Pro
cwdavi1
 
Posts: 14
Joined: Fri Sep 13, 2019 9:47 pm

Re: using db table view in stand alone form

Postby F3K Total » Thu Oct 10, 2019 2:21 pm

i think your SQL Comand is a query not running in direct-SQL-Mode, 'cause you are using parameters.
It's not possible to use a direct-SQL-Mode View in a not-direct-SQL-Mode query.
You need another solution, using a query without parameters.
I would create a one row filter-table, named T_FILTER, having 2 columns, ID (Boolean), F_TITLE(VARCHAR(50)).
F_TITLE is filled in the MainForm, then you can use the following query in direct SQL-Mode:
Code: Select all   Expand viewCollapse view
select "title", "aid", "description" from "masterT" where "aid" IN
    (select DISTINCT "aid" from "ViewAids" WHERE UPPER ( "title" ) LIKE UPPER ( '%' ||(SELECT F_TITLE FROM T_FILTER)|| '%' ) OR UPPER ( "otitle" ) LIKE UPPER ( '%' || (SELECT F_TITLE FROM T_FILTER) || '%' ) )

Otherwise you can create a new table, containing both, contextT1 and contextT2, that you do not need a UNION View.
Execute the following command once via Tools/SQL...
Code: Select all   Expand viewCollapse view
CREATE TABLE "context"("aid" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"title" VARCHAR(100),"otitle" VARCHAR(100));
insert into "context"("title", "otitle") SELECT "title", "otitle" FROM "contextT1" union SELECT "htitle", "otitle" FROM "contextT2"

If you now go to Tables-Area, Menue View/Refresh Tables, you have a new table "context" containing all you titles.
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 924
Joined: Fri Dec 16, 2011 8:20 pm

Re: [SOLVED] using db table view in stand alone form

Postby cwdavi1 » Thu Oct 10, 2019 5:46 pm

Thank you, I didn't realize the direct SQL mode view conflict with the parameter query. I'm still fumbling with base. I appreciate the support.
Libreoffice 6.3 on Windows 10 Pro
cwdavi1
 
Posts: 14
Joined: Fri Sep 13, 2019 9:47 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 6 guests