[Solved] Using db table view in stand alone form

[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.

[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.

[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

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.

[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

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

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

[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.