Muiltple inner joins - SQL Command not properly ended

Question

I am trying to run the below statement and I keep getting an error stating - "SQL Command not properly ended". I am not sure what I did wrong or what I am missing ? Any help is greatly appreciated . I did run the statement without the last join and it ran perfectly but as soon as I added the "icue.mbr_adr mb" table I got an error.

Select Distinct
   cm.HSC_ID,
   ac.creat_dttm,
   cm.CNTC_NM,
   cm.fax_nbr,
   ac.actv_strt_dttm,
   mb.st_cd

   From icue.cmnct_trans cm

   Inner Join icue.actv ac
      On cm.HSC_ID = ac.HSC_ID
          Where trunc(ac.actv_strt_dttm) between to_date('19-FEB-2018','DD-MON-YYYY') and to_date('06-MAR-2018','DD-MON-YYYY')
          AND cm.FAX_NBR = '201-553-7889'
          AND cm.CNTC_NM ='CHILDRENS HOSP PHILADELPHIA'



   Inner Join icue.mbr_adr mb
     On ac.mbr_id = mb.mbr_id
        Where mb.st_cd ='PA' or 'NJ'

I have noticed now multiple questions by you tagged as plsql instead of sql. Please investigate the difference and tag your questions appropriately. — Michael O'Neill
Thanks. I did not realize the difference between the two and did remove the tag. — cardonas
I believe the Where clause needs to be after all the Joins, so you'd want to combine both Where clauses into one clause. — PKatona
Thank you so much! PKatonna, I did remove the WHERE and replaced with an "AND" since I want all to have a specific condition . I will post the statement on my answers. Thanks again . — cardonas
On a sidenote: Don't convert month names without specifying the language. to_date('19-FEB-2018','DD-MON-YYYY') can easily fail when setting the session to another language than English. Don't make your query that vulnarable. Use ANSI date literals instead: where ac.actv_strt_dttm >= date '2018-02-19' and ac.actv_strt_dttm < date '2018-03-07'. — Thorsten Kettner

cardonas cardonas · Accepted Answer · 2018-03-14T14:57:52

Here is what I did and it worked . Thank you again PKatona. I just changed the Where into an "AND"

Select Distinct
   cm.HSC_ID,
   ac.creat_dttm,
   cm.CNTC_NM,
   cm.fax_nbr,
   ac.actv_strt_dttm,
   mb.st_cd

   From icue.cmnct_trans cm

   Inner Join icue.actv ac
      On cm.HSC_ID = ac.HSC_ID
          AND trunc(ac.actv_strt_dttm) between to_date('19-FEB-2018','DD-MON-YYYY') and to_date('06-MAR-2018','DD-MON-YYYY')
          AND cm.FAX_NBR = '201-553-7889'
          AND cm.CNTC_NM ='CHILDRENS HOSP PHILADELPHIA'

    Inner Join icue.mbr_adr mb
     On ac.mbr_id = mb.mbr_id

Muiltple inner joins - SQL Command not properly ended

1 Answers