Matt Farrell Matt Farrell - 1 month ago 6
SQL Question

Application Express SQL Query to show meaningful information

I am trying to write a query that 1) works and 2) shows meaningful information.

However, I can't seem to complete both scenarios. Both bits of code do work to a degree. My SQL query does work by showing all the useful information a user wants but when you click the edit button it doesn't link properly so it won't allow the user to update that row. The other shows only keys and rowid but when you click edit does show the information and allows it to be updated.

So as not to get another down-voted question, I have taken pictures of each scenario to show the problem, but, ultimately, I need to show meaningful information: an id or key isn't meaningful to the vast majority of users.

Here is my code

SELECT APPLICATIONS.APP_ID, APPLICATIONS.SRN, STUDENTS.SURNAME, STUDENTS.FORENAME, APP_STATUS.STATUS, METHODS.METHOD, JOBS.JOB_TITLE, APPLICATIONS.APP_DATE
FROM APPLICATIONS
JOIN STUDENTS
ON APPLICATIONS.SRN = STUDENTS.SRN
JOIN APP_STATUS
ON APPLICATIONS.STATUS_ID = APP_STATUS.STATUS_ID
JOIN METHODS
ON APPLICATIONS.METHOD_ID = METHODS.METHOD_ID
JOIN JOBS
ON APPLICATIONS.JOB_ID = JOBS.JOB_ID;


and here are the pictures of it in action
My query in action

my query not allowing update

below is the code that does not show meaningful information but does work.

select "ROWID",
"APP_ID",
"SRN",
"STATUS_ID",
"METHOD_ID",
"JOB_ID",
"APP_DATE"
from "#OWNER#"."APPLICATIONS"


In action

working as it should

If i knew how to properly use rowid i am sure this is a simple feat but i dont so if i could get any help it would be useful

Answer

In the second, simple query, apex can determine which table (and record) you are trying to edit.

In the first query, with the joins, it can't tell which of the five tables in query you want to edit. You probably want to have the edit link pass the primary key of the row from APPLICATIONS to the child page. You would need to build into that page any logic (lists of values etc) that map lookup tables (such as status) to the values needed in the APPLICATIONS table.