Page 1 of 1

[Solved] Using db table view in stand alone form

PostPosted: Wed Oct 09, 2019 12:50 am
by cwdavi1
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.

Re: using db table view in stand alone form

PostPosted: Wed Oct 09, 2019 12:24 pm
by F3K Total
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.

Re: using db table view in stand alone form

PostPosted: Thu Oct 10, 2019 2:40 am
by cwdavi1
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.

Re: using db table view in stand alone form

PostPosted: Thu Oct 10, 2019 2:21 pm
by F3K Total
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

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

PostPosted: Thu Oct 10, 2019 5:46 pm
by cwdavi1
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.