sql - Oracle SQL DB select muti join error join error - ORA-00933: SQL command not properly ended

Question

i am trying to join few tables in order to fetch data, the problem over here is when i apply the filter i was not able to fetch the data and i am getting the below error.

below are the tables

In my case the foriegn keys referenced while joining the table is ending with _UID

TBL_ABC_CUSTOMER_D
TBL_BBC_CUSTOMER_STATUS_D
TBL_NBC_CUSTOMER_TYPE_D
TBL_KBC_CUSTOMER_SEGMENT_TYPE_D
TBL_NCC_CUSTOMER_SEGMENT_D

SQL Query

SELECT
   ABC.ABC_DES_NAME,
   ABC.ABC_DES_LASTNAME,
   ABC.ABC_DAT_BIRTH_DATE,
   ABC.ABC_LOC,
   ABC.ABC_MOBILE_NUM,
   ABC.ABC_FLG_GENDER,
   ABC.ABC_EMAIL,
   ABC.TBL_SYSTEM,
   ABC.TBL_EFFECTIVE_ON,
   ABC.TBL_EXPIRED_ON 
FROM
   DWH_SCHEMA.TBL_ABC_CUSTOMER_D ABC    /*CUSTOMER 1*/
   JOIN
      DWH_SCHEMA.TBL_BBC_CUSTOMER_STATUS_D BBC      /*CUSTOMER STATUS 2*/
      ON ABC.BBC_COD_CUSTOMER_STATUS_UID = BBC.BBC_COD_CUSTOMER_STATUS_UID 
      and ABC.TBL_EXPIRED_ON = TO_DATE('31.12.9999', 'DD.MM.YYYY')      --/*IT ALLOWS YOU TO CONSIDER THE LAST SNAPSHOT FOR EACH TABLE*/
      and BBC.TBL_EXPIRED_ON = TO_DATE('31.12.9999', 'DD.MM.YYYY')      --/*IT ALLOWS YOU TO CONSIDER THE LAST SNAPSHOT FOR EACH TABLE*/
   JOIN
      DWH_SCHEMA.TBL_NBC_CUSTOMER_TYPE_D NBC        /*CUSTOMER SUPPLIER 3*/
      ON ABC.NBC_COD_CUSTOMER_TYPE_UID = NBC.NBC_COD_CUSTOMER_TYPE_UID 
      and ABC.TBL_EXPIRED_ON = TO_DATE('31.12.9999', 'DD.MM.YYYY')      /*IT ALLOWS YOU TO CONSIDER THE LAST SNAPSHOT FOR EACH TABLE*/
      and NBC.TBL_EXPIRED_ON = TO_DATE('31.12.9999', 'DD.MM.YYYY')      /*IT ALLOWS YOU TO CONSIDER THE LAST SNAPSHOT FOR EACH TABLE*/
   JOIN
      DWH_SCHEMA.TBL_KBC_CUSTOMER_SEGMENT_TYPE_D KBC        /*CUSTOMER SEGMENT TYPE 4*/
      ON ABC.KBC_COD_SEGMENT_TYPE_UID = KBC.KBC_COD_SEGMENT_TYPE_UID 
      and ABC.TBL_EXPIRED_ON = TO_DATE('31.12.9999', 'DD.MM.YYYY')      /*IT ALLOWS YOU TO CONSIDER THE LAST SNAPSHOT FOR EACH TABLE*/
      and KBC.TBL_EXPIRED_ON = TO_DATE('31.12.9999', 'DD.MM.YYYY')      /*IT ALLOWS YOU TO CONSIDER THE LAST SNAPSHOT FOR EACH TABLE*/
   JOIN
      DWH_SCHEMA.TBL_NCC_CUSTOMER_SEGMENT_D NCC         /*CUSTOMER SEGMENT 5*/
      ON ABC.NCC_COD_SEGMENT_UID = NCC.NCC_COD_SEGMENT_UID 
      and ABC.TBL_EXPIRED_ON = TO_DATE('31.12.9999', 'DD.MM.YYYY')      /*IT ALLOWS YOU TO CONSIDER THE LAST SNAPSHOT FOR EACH TABLE*/
      and NCC.TBL_EXPIRED_ON = TO_DATE('31.12.9999', 'DD.MM.YYYY')      /*IT ALLOWS YOU TO CONSIDER THE LAST SNAPSHOT FOR EACH TABLE*/

  

Error

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
Error at Line: 9 Column: 1

how to resolve the above error ?

标签： sqloracle

Answer 1

Joins first, conditions next.

No:

from emp e
where e.job = 'SALESMAN'
join dept d on d.deptno = e.deptno
where d.loc = 'LONDON'

Yes:

from emp e join dept d on d.deptno = e.deptno
where e.job = 'SALESMAN'
  and d.loc = 'LONDON'

Apply that to your code.