ORA-00936: missing expression Oracle Apex

Question

SELECT crime_name || 'Was Committed On' ||, crime_date, victim.firstname AS "VICTIM" || 'Is The Victim', ||witness.firstname AS "WITNESS" || 'Witnessed The Crime' ||, Suspect.firstname AS "SUSPECT" || 'Is Suspected Of Committing The Crime' || FROM Crime, Victim, Witness, Suspect
WHERE victim.crime_no = crime.crime_no AND witness.crime_no = crime.crime_no AND suspect.crime_no = crime.crime_no;  

Can any one help me? I keep getting the missing expression error, I just cant seem to figure it out. Any help would be appreciated.

I'm using oracle Apex by the way

Thanks!

Answer 1

Accomodating for Many-to-One Relations in an Apex Report Page

The previously posted answers have responded to the specific question of managing the output of a one-to-one relationship between multiple tables: CRIME, VICTIM, WITNESS and SUSPECT. I find the problem-space of this schema interesting and would like to expand on the initial design to consider some "real-ish" use cases and scenarios.

Since this OP was originally tagged as an oracle-apex problem, it might also be useful to show how to fit in semi-complex schema structures into an Apex page design or report.

Designing for Many-to-One Data Relationships

The following solution is an expansion of the OP to consider the possibility of displaying output for situations where there are multiple records within VICTIM, WITNESS and SUSPECT that may match for a given crime.

New Requirements: Let's include four more entities (and consolidate three of the original ones) into the design:

1. Crime Report Entity: This is the physical record (whether written or electronic) which captures the details of the crime under investigation. I assume that is the idea behind the concatenated string constructed by the OP.

   SELECT crime_name || ' Was Committed On ' || to_char(crime_date,'MM/DD/YYYY) || victim.firstname || ' Is The Victim. ' || witness.firstname || ' Witnessed The Crime. ' || Suspect.firstname || ' Is Suspected Of Committing The Crime.'

   ... (adapted from a previously posted solution by @mureinik)

   A few additional assumptions included are that many different reports may be filed for the same crime: Multiple law enforcement representatives may either split the investigation task, or file completely different reports independent of one another. The associated CRIME remains in common in that case, but each account would be uniquely different reports.

2. Persons Entity: Separating three different tables for VICTIMS, SUSPECTS and WITNESSES adds more joins in querying a full account of a crime instance or report. This also confuses a simpler commonality that anyone in these three tables are also just "PERSONS" of varying roles in a crime event.

3. Staff Role Associative Entity: Law enforcement officers and personnel are a special kind of "PERSON" in this data model. They have special meta-information not relevant to the general pool of person entities, such as

   • STAFF_TITLE (OFC., DET., INV., or some other rank designation, etc.)
   • STAFF_ROLE (Officer, Detective, Investigator, etc.)
   • DATE_ASSIGNED/SUSPENDED This one will affect the LOV's associated with Staff Names. The same staff over time will quit, move up from a promotion or transition to a different capacity. Their titles and ranks should follow them.

4. Crime Association Entity: Each PERSON record is associated to a crime report and has a role, such as VICTIM, SUSPECT, and WITNESS..

Building the "DOJ" (Department-of-Justice) Schema Demonstration

The schema design put together to fulfill the OP and the new requirements are specified through these DDL SQL statements: (Sample data in .csv format follows)

DOJ_CRIME table

 CREATE TABLE  "DOJ_CRIME" 
    (   "CRIME_NO" NUMBER(15,0) NOT NULL ENABLE, 
     "CRIME_DETAIL" VARCHAR2(100), 
     "LOCATION" VARCHAR2(40), 
     "DATE_REPORTED" DATE NOT NULL ENABLE, 
      CONSTRAINT "DOJ_CRIME_PK" PRIMARY KEY ("CRIME_NO") ENABLE
    )
 /


 "CRIME_NO","CRIME_DETAIL","LOCATION","DATE_REPORTED"
 "21","Kidnapping","WOODBURY, PA","01/31/2014"
 "4","Domestic Violence","LIBERTY CITY, PA","01/30/2014"
 "2","Grand Theft Auto","LIBERTY CITY, PA","02/25/2014"
 "1","Suspected Arson Event","BOSTON, MA","02/25/2014"
 "3","Aggravated Assault and Battery","PHILADELPHIA, PA","03/01/2014"

DOJ_PERSON table

 CREATE TABLE  "DOJ_PERSON" 
    (   "PERSON_ID" NUMBER(10,0) NOT NULL ENABLE, 
     "PERSON_NAME" VARCHAR2(100) NOT NULL ENABLE, 
     "DATE_OF_BIRTH" DATE, 
     "DATE_ADDED" DATE, 
     "DATE_MODIFIED" DATE, 
      CONSTRAINT "DOJ_PERSON_PK" PRIMARY KEY ("PERSON_ID") ENABLE
    )
 /


 "PERSON_ID","PERSON_NAME","DATE_OF_BIRTH","DATE_ADDED","DATE_MODIFIED"
 "1","PATRICK GUILBERT","","01/10/2014",""
 "2","STEFANO MILAN","","01/10/2014",""
 "3","ORLANDO VIGO","","01/10/2014",""
 "4","RACHEL MARTIN","","01/10/2014",""
 "5","LOUIS HATCHER","","01/10/2014",""
 "6","MOIRA BOUVIER","","02/04/2014",""
 "7","RHETT BUTLER","","02/04/2014",""
 "8","PATRICIA NASH","","02/04/2014",""
 "9","WANDA YAVISH","","02/04/2014",""
 "10","CARL GRIMES","","02/25/2014",""
 "11","KIMBERLY POTTS","","02/25/2014",""
 "12","VANDER NEILS","","02/25/2014",""
 "13","CLARK HENDERSON","","02/25/2014",""
 "14","SARAH BLACKTHORNE","","02/25/2014",""
 "15","KILLIAN LAGER","","02/25/2014",""
 "16","PHILLIP FOGBAY","","02/25/2014",""
 "17","CHARLES HARRIS","","03/02/2014",""
 "18","PENNY SHALE","","03/02/2014",""
 "19","LAWRENCE NEVILLE","","03/02/2014",""
 "20","BONNIE DEWITT","","03/02/2014",""
 "21","JUNE LEWIS","","03/02/2014",""
 "22","TARA YOUNG","","03/20/2014",""
 "23","WENDY NORRIS","","03/20/2014",""
 "24","AIDEN LIGHTFOOT","","03/20/2014",""

DOJ_STAFF_ROLE table

 CREATE TABLE  "DOJ_STAFF_ROLE" 
    (   "PERSON_ROLE_ID" NUMBER(10,0) NOT NULL ENABLE, 
     "PERSON_ID" NUMBER(10,0) NOT NULL ENABLE, 
     "STAFF_ROLE" VARCHAR2(40) NOT NULL ENABLE, 
     "STAFF_TITLE" VARCHAR2(40) NOT NULL ENABLE, 
     "DATE_ASSIGNED" DATE NOT NULL ENABLE, 
     "DATE_SUSPENDED" DATE, 
      CONSTRAINT "DOJ_STAFF_ROLE_PK" PRIMARY KEY ("PERSON_ROLE_ID") ENABLE
    )
 /
 ALTER TABLE  "DOJ_STAFF_ROLE" ADD CONSTRAINT "DOJ_STAFF_ROLE_FK" FOREIGN KEY ("PERSON_ID")
       REFERENCES  "DOJ_PERSON" ("PERSON_ID") ENABLE
 /

 "PERSON_ROLE_ID","PERSON_ID","STAFF_ROLE","STAFF_TITLE","DATE_ASSIGNED","DATE_SUSPENDED"
 "21","16","OFFICER","OFC.","02/25/2014",""
 "1","1","DETECTIVE","DET.","02/01/2014",""
 "2","8","INVESTIGATOR","LT.","02/04/2014",""
 "22","11","INVESTIGATOR","SGT.","02/25/2014",""
 "23","5","OFFICER","OFC.","01/15/2014",""

DOJ_CRIME_REPORT table

 CREATE TABLE  "DOJ_CRIME_REPORT" 
    (   "REPORT_ID" NUMBER(10,0) NOT NULL ENABLE, 
     "CRIME_NO" NUMBER(10,0) NOT NULL ENABLE, 
     "INVESTIGATOR" NUMBER(10,0) NOT NULL ENABLE, 
     "FILE_ID" VARCHAR2(50), 
     "DISPOSITION" VARCHAR2(40), 
     "CRIME_DESCRIPTION" VARCHAR2(100) NOT NULL ENABLE, 
     "REPORT_DATE" DATE NOT NULL ENABLE, 
      CONSTRAINT "DOJ_CRIME_REPORT_PK" PRIMARY KEY ("REPORT_ID") ENABLE
    )
 /
 ALTER TABLE  "DOJ_CRIME_REPORT" ADD CONSTRAINT "DOJ_CRIME_REPORT_FK" FOREIGN KEY
 ("CRIME_NO")
  REFERENCES  "DOJ_CRIME" ("CRIME_NO") ENABLE
 /
 ALTER TABLE  "DOJ_CRIME_REPORT" ADD CONSTRAINT "DOJ_CRIME_REPORT_FK2" FOREIGN KEY 
 ("INVESTIGATOR")
  REFERENCES  "DOJ_STAFF_ROLE" ("PERSON_ROLE_ID") ENABLE
 /


 "REPORT_ID","CRIME_NO","INVESTIGATOR","FILE_ID","DISPOSITION","CRIME_DESCRIPTION","REPORT_DATE"
 "1","4","21","AFK-TCPIP-1025","FILED","Responded to call for backup at a Liberty City residence.  Detained suspect for further questioning.","02/01/2014"

DOJ_CRIME_ASSOC table

 CREATE TABLE  "DOJ_CRIME_ASSOC" 
    (   "CRIME_ASSOC_ID" NUMBER(10,0) NOT NULL ENABLE, 
     "REPORT_ID" NUMBER(10,0) NOT NULL ENABLE, 
     "PERSON_ID" NUMBER(10,0) NOT NULL ENABLE, 
     "PRIMARY_ROLE" VARCHAR2(40) NOT NULL ENABLE, 
      CONSTRAINT "DOJ_CRIME_ASSOC_PK" PRIMARY KEY ("CRIME_ASSOC_ID") ENABLE, 
      CONSTRAINT "DOJ_CRIME_ASSOC_UK1" UNIQUE ("REPORT_ID", "PERSON_ID", "PRIMARY_ROLE") ENABLE
    )
 /
 ALTER TABLE  "DOJ_CRIME_ASSOC" ADD CONSTRAINT "DOJ_CRIME_ASSOC_FK" FOREIGN KEY ("REPORT_ID")
      REFERENCES  "DOJ_CRIME_REPORT" ("REPORT_ID") ENABLE
 /
 ALTER TABLE  "DOJ_CRIME_ASSOC" ADD CONSTRAINT "DOJ_CRIME_ASSOC_FK2" FOREIGN KEY ("PERSON_ID")
  REFERENCES  "DOJ_PERSON" ("PERSON_ID") ENABLE
 /       


 "CRIME_ASSOC_ID","REPORT_ID","PERSON_ID","PRIMARY_ROLE"
 "22","1","18","WITNESS"
 "1","1","22","VICTIM"
 "21","1","3","SUSPECT"
 "2","1","12","WITNESS"

Integrating the DOJ Schema Design into Oracle Application Express (APEX)

Here is a sample "Crime Report" designed in APEX using this schema. A sophisticated search-page is possible to search by the other discrete values of the crime database. A report based off of REPORT_ID would be one of the basic search criteria and might look like this:

Crime Report by Report ID, (TOP)

Crime Report by Report ID, (END)

One of the larger queries involved is the one for the section titled "Report Information". Still, there are fewer joins and more flexibility in the data relationships between each entity involved:

Report Information Region SQL Query

 select DOJ_CRIME_REPORT.REPORT_ID as REPORT_ID,
     DOJ_CRIME_REPORT.CRIME_NO as CRIME_NO,
     DOJ_STAFF_ROLE.STAFF_TITLE || ' ' || 
     DOJ_PERSON.PERSON_NAME as ASSIGNED_STAFF,
     DOJ_CRIME_REPORT.FILE_ID as FILE_ID,
     DOJ_CRIME_REPORT.DISPOSITION as DISPOSITION,
     DOJ_CRIME_REPORT.CRIME_DESCRIPTION as CRIME_DESCRIPTION,
     DOJ_CRIME_REPORT.REPORT_DATE as REPORT_DATE 
  from DOJ_PERSON DOJ_PERSON,
     DOJ_STAFF_ROLE DOJ_STAFF_ROLE,
     DOJ_CRIME_REPORT DOJ_CRIME_REPORT 
  where DOJ_CRIME_REPORT.INVESTIGATOR = DOJ_STAFF_ROLE.PERSON_ROLE_ID
     and DOJ_STAFF_ROLE.PERSON_ID = DOJ_PERSON.PERSON_ID
     and DOJ_CRIME_REPORT.REPORT_ID = :P13_REPORT_ID

Comments and Discussion

• The approach of creating a NORMALIZED data schema for this solution will help with building the LOV queries used to create various Crime Reports for storage in this database.

• Consolidating on common entity concepts such as PERSONS and ROLES reduces the number of joins, and lowers the chance of duplicate values (i.e., same persons defined in multiple tables).

• Associative tables reduces creation of duplicate data as well. The only thing stored in these linking entities are singular ID values/keys.

• Leveraging APEX Page design, the use of different page regions splits a single, complex query into discrete parts that are independent of one another except for a common reference key/id. (such as REPORT_ID). This skips most outer joins intended for managing optional data relationships.

• Tip: Build Shared LOV queries instead of coding it in each item on your input page(s). Many of these values are used multiple times and would otherwise cause needless repetition.

Happy Coding...!

Answer 2

Try this:

SELECT crime_name || ' Was Committed On ' || crime_date, 
       victim.firstname || ' Is The Victim' AS "VICTIM" , 
       witness.firstname || ' Witnessed The Crime'  AS "WITNESS",
       Suspect.firstname || ' Is Suspected Of Committing The Crime' AS "SUSPECT"
FROM   Crime, Victim, Witness, Suspect
WHERE  victim.crime_no = crime.crime_no AND 
       witness.crime_no = crime.crime_no AND 
       suspect.crime_no = crime.crime_no; 

The aliases need to be in the end.

Answer 3

You have several fields with both || and , separating them. You should remove the redundant operators:

SELECT crime_name || ' Was Committed On ' || crime_date, 
       victim.firstname AS "VICTIM" || ' Is The Victim', 
       witness.firstname AS "WITNESS" || ' Witnessed The Crime',
       Suspect.firstname AS "SUSPECT" || ' Is Suspected Of Committing The Crime' 
FROM   Crime, Victim, Witness, Suspect
WHERE  victim.crime_no = crime.crime_no AND 
       witness.crime_no = crime.crime_no AND 
       suspect.crime_no = crime.crime_no;