Base : How to replace query name with its original SQL

[lonk]

SQL of query UpdatedStatusPSIF :

SELECT DISTINCT "pi"."PersonnelIDCode", FIRST_VALUE ( "ti"."TextItemListLocal" ) OVER ( PARTITION BY "pi"."PersonnelIDCode" ORDER BY CASE WHEN "TextItemListLocal" IS NOT NULL THEN "StartDate" END DESC ) "TextItemListLocal", FIRST_VALUE ( "pi"."FirstNameLocal" ) OVER ( PARTITION BY "pi"."PersonnelIDCode" ORDER BY CASE WHEN "pi"."FirstNameLocal" IS NOT NULL THEN "StartDate" END DESC ) "FirstNameLocal", FIRST_VALUE ( "pi"."LastNameLocal" ) OVER ( PARTITION BY "pi"."PersonnelIDCode" ORDER BY CASE WHEN "pi"."LastNameLocal" IS NOT NULL THEN "StartDate" END DESC ) "LastNameLocal", FIRST_VALUE ( "pi"."TitleEnglish" ) OVER ( PARTITION BY "pi"."PersonnelIDCode" ORDER BY CASE WHEN "pi"."TitleEnglish" IS NOT NULL THEN "StartDate" END DESC ) "TitleEnglish", FIRST_VALUE ( "pi"."FirstNameEnglish" ) OVER ( PARTITION BY "pi"."PersonnelIDCode" ORDER BY CASE WHEN "pi"."FirstNameEnglish" IS NOT NULL THEN "StartDate" END DESC ) "FirstNameEnglish", FIRST_VALUE ( "pi"."LastNameEnglish" ) OVER ( PARTITION BY "pi"."PersonnelIDCode" ORDER BY CASE WHEN "pi"."LastNameEnglish" IS NOT NULL THEN "StartDate" END DESC ) "LastNameEnglish" FROM "PersonnelInformation" "pi" LEFT JOIN "TextItem" "ti" ON ( "pi"."TitleCodeLocalSourceCode" = "ti"."TextSourceCode" AND "pi"."TitleCodeLocalGroupCode" = "ti"."TextGroupCode" AND "pi"."TitleCodeLocalIndex" = "ti"."TextIndex" )

SQL of query UpdatedStatusPSIB :

SELECT DISTINCT "PersonnelIDCode", "PersonnelBranchIDCode", FIRST_VALUE ( "BranchNameEnglish" ) OVER ( PARTITION BY "PersonnelBranchIDCode" ORDER BY CASE WHEN "BranchNameEnglish" IS NOT NULL THEN "StartDate" END DESC ) "BranchNameEnglish", FIRST_VALUE ( "BranchNameLocal" ) OVER ( PARTITION BY "PersonnelBranchIDCode" ORDER BY CASE WHEN "BranchNameLocal" IS NOT NULL THEN "StartDate" END DESC ) "BranchNameLocal" FROM "PersonnelInformationBranch"

SQL of query UpdatedStatusPSIFPSIB :

SELECT "UpdatedStatusPSIF"."PersonnelIDCode", "UpdatedStatusPSIF"."FirstNameEnglish", "UpdatedStatusPSIF"."LastNameEnglish", "UpdatedStatusPSIF"."TitleEnglish", "UpdatedStatusPSIF"."FirstNameLocal", "UpdatedStatusPSIF"."LastNameLocal", "UpdatedStatusPSIF"."TextItemListLocal", "UpdatedStatusPSIB"."PersonnelBranchIDCode", "UpdatedStatusPSIB"."BranchNameEnglish", "UpdatedStatusPSIB"."BranchNameLocal" FROM "UpdatedStatusPSIB", "UpdatedStatusPSIF" WHERE "UpdatedStatusPSIB"."PersonnelIDCode" = "UpdatedStatusPSIF"."PersonnelIDCode"

I used the menu Create Query in Design View… selecting 2 queries, UpdatedStatusPSIF and UpdatedStatusPSIB, for getting UpdatedStatusPSIFPSIB.

Can you please help convert query UpdatedStatusPSIF and query UpdatedStatusPSIB in query UpdatedStatusPSIFPSIB into their original SQL? I need to use it in coding.

Database30.ods (11.9 KB)
Database30.odb (5.4 KB)

LibreOffice:
Version: 7.3.4.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.4-0ubuntu0.22.04.1
Calc: threaded

Base: Embedded Firebird

OS: Ubuntu 22.04 LTS

[RobertG]

Go to UpdatedStatusPSIFPSIB.
Right mouse click → Create as View.
Type something like viw_PSIFPSIB.
Go to tables → Right mouse click on viw_PSIFPSIB.
Edit in SQL View… will show the code.

Views don’t know anything about queries you have created. So the code must be created without using the queries. Queries will be like different tables in the view.

[cpb]

@lonk,
i am not entirely sure exactly what it is you are asking or what precisely it is you are attempting to achieve.
i think you wish to join the code contained in the queries “UpdatedStatusPSIB” and “UpdatedStatusPSIF” thus making a single query which can then be easily altered/edited.
i have added the queries “qPSIB”, “qPSIF” and “qPSIB_PSIF” to your attachment, they run in direct mode i.e Base parser is ignored.
Database31.odb (6.0 KB)

[lonk]

Thank you so much, this is what I need.

And sorry for uncleared question, the required SQL is the relationship of updated status of both tables, PersonnelInformation and PersonnelInformationBranch.

[RobertG]

What is the difference to the code of the view?

[lonk]

The code gives the result as needed.
I opened the View but I overlooked for there are still 2 query names in over there.
After you have reminded, I went there again and saw that all queries were translated at the end of SQL.