Solved

FROM Keyword not found where expected

Posted on 2013-05-22
5
640 Views
Last Modified: 2013-05-22
HELP!! I continue to get this Oracle ORA-00923: FROM keyword not found where expected.  I'm stumped!!


SELECT  PAT_ENC.VISIT_PROV_ID
  , CLARITY_TDL_TRAN.BILLING_PROVIDER_ID
      , CLARITY_TDL_TRAN.PERFORMING_PROV_ID
      , CLARITY_SER.PROV_NAME
      , CLARITY_SER_1.PROV_NAME
      , CLARITY_SER_2.PROV_NAME
CASE
      WHEN (CASE
  WHEN PAT_ENC.ENC_CLOSE_TIME IS NULL THEN SYSDATE
  ELSE PAT_ENC.ENC_CLOSE_TIME
  END
  -CASE
  WHEN TO_CHAR(PAT_ENC.APPT_TIME,'D') IN (4,5,6)
  THEN
  PAT_ENC.APPT_TIME +2
  WHEN TO_CHAR(PAT_ENC.APT_TIME,'D')=7
  THEN
  PAT_ENC.APPT_TIME +2
  -(PAT_ENC.APPT_TIME
  -TRUNC(PAT_ENC.APPT)TIME,'DD'))
  WHEN TO_CHAR(PAT_ENC.APPT_TIME, 'D')=1
  THEN
  PAT_ENC.APPT_TIME +1
  -(PAT.ENC.APPT_TIME
  -TRUNC(PAT_ENC.APPT_TIME,'DD'))
  ELSE
    PAT_ENC.APPT_TIME
    END)
    *24>72
    THEN
    'NO'
    ELSE
    'YES'
    END
    AS "CLOSED UNDER 72H?"
    ZC_DISP_ENC_TYPE."NAME" AS "ENC TYPE",
    COUNT (DISTINCT PAT_ENC.PAT_ENC_CSN_ID) AS "UNIQUE VISITS"  
FROM
      (((((CLARITY_REPORT.CLARITY_TDL_TRAN CLARITY_TDL_TRAN
      INNER JOIN
      CLARITY_REPORT.PAT_ENC PAT_ENC
      ON (CLARITY_TDL_TRAN.TDL_ID = PAT_ENC.PAT_ENC_CSN_ID))
  LEFT OUTER JOIN
      CLARITY_REPORT.ARPB_TX_VOID ARPB_TX_VOID
      ON (CLARITY_TDL_TRAN.TX_ID = ARPB_TX_VOID.TX_ID))
      LEFT OUTER JOIN
      CLARITY_REPORT.CLARITY_SER CLARITY_SER_1
      ON (CLARITY_TDL_TRAN.BILLING_PROVIDER_ID = CLARITY_SER_1.PROV_ID))
      LEFT OUTER JOIN
      CLARITY_REPORT.CLARITY_SER CLARITY_SER_2
      ON (CLARITY_TDL_TRAN.PERFORMING_PROV_ID = CLARITY_SER_2.PROV_ID))
      LEFT OUTER JOIN
      CLARITY_REPORT.ZC_DISP_ENC_TYPE ZC_DISP_ENC_TYPE
      ON (PAT_ENC.ENC_TYPE_C = ZC_DISP_ENC_TYPE.DISP_ENC_TYPE_C))
      LEFT OUTER JOIN
      CLARITY_REPORT.CLARITY_SER CLARITY_SER
      ON (PAT_ENC.VISIT_PROV_ID = CLARITY_SER.PROV_ID)
WHERE (PAT_ENC.APPT_STATUS_C IN (2,6))
      AND (PAT_ENC.APPT_TIME = calendar__gregorian__last_month__)
      AND (PAT_ENC.ENC_TYPE_C NOT IN ('231', '235', '233', '230', '204', '222', '70'))
      AND (CLARITY_TDL_TRAN.DETAIL_TYPE = 1)
      AND ( "ARPB_TX_VOID"."DEL_REVERSE_DATE" = IS NULL )
GROUP BY CASE
      WHEN (CASE
  WHEN PAT_ENC.ENC_CLOSE_TIME IS NULL THEN SYSDATE
  ELSE PAT_ENC.ENC_CLOSE_TIME
  END
        CASE
    WHEN TO_CHAR(PAT_ENC.APPT_TIME, 'D') IN (4, 5, 6)
    THEN
    PAT_ENC.APPT_TIME +2
    WHEN TO_CHAR(PAT_ENC.APPT_TIME, 'D') =7
    THEN
    PAT_ENC.APPT_TIME +2
    -(PAT_ENC.APPT_TIME
    -TRUNC (PAT_ENC.APPT_TIME,'DD'))
    WHEN TO_CHAR (PAT_ENC.APPT_TIME, 'D') =1
    THEN
    PAT_ENC.APPT_TIME +1
          (PAT_ENC.APPT_TIME
          TRUNC(PAT_ENC.APPT_TIME, 'DD'))
    ELSE
    PAT_ENC.APPT_TIME
    END)
    *24>72
    THEN
    'NO'
    ELSE
    'YES'
    END,
    ZC_DISP_ENC_TYPE."NAME",
    CLARITY_SER.PROV_NAME,PAT_ENC.VISIT_PROV_ID,
    PAT_ENC.VISIT_PROV_ID,
    CLARITY_TDL_TRAN.BILLING_PROVIDER_ID,
    CLARITY_SER_1.PROV_NAME,
    CLARITY_TDL_TRAN.PERFORMING_PROV_ID,
    CLARITY_SER_2.PROV_NAME
0
Comment
Question by:SheriReyna
  • 4
5 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39189343
Missing a comma in front of case:

SELECT  PAT_ENC.VISIT_PROV_ID
  , CLARITY_TDL_TRAN.BILLING_PROVIDER_ID
      , CLARITY_TDL_TRAN.PERFORMING_PROV_ID
      , CLARITY_SER.PROV_NAME
      , CLARITY_SER_1.PROV_NAME
      , CLARITY_SER_2.PROV_NAME
,CASE
...

Open in new window



You also have a syntax error here:
  -TRUNC(PAT_ENC.APPT)TIME,'DD'))


Not sure what you are trying to do so I'm not sure how to fix it.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39189354
>>Not sure what you are trying to do so I'm not sure how to fix it.

I figured it out... it's an underscore:
  -TRUNC(PAT_ENC.APPT_TIME,'DD'))

Another missing comma here:
    ,ZC_DISP_ENC_TYPE."NAME" AS "ENC TYPE",


Then you have an issue with:
      AND (PAT_ENC.APPT_TIME = calendar__gregorian__last_month__)


that variable is to long.


Then an issue with:
      AND ( "ARPB_TX_VOID"."DEL_REVERSE_DATE" = IS NULL )


No '=':
      AND ( "ARPB_TX_VOID"."DEL_REVERSE_DATE" IS NULL )


Then you have syntax issues in the group by with missing commas, etc...
0
 

Author Comment

by:SheriReyna
ID: 39189359
Thank you for pointing out those gross errors.  Unfortunately, though with that correction, I continue to get this error.  Any further ideas!  Your help is so appreciated!
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39189368
It's here:
    WHEN TO_CHAR (PAT_ENC.APPT_TIME, 'D') =1 THEN PAT_ENC.APPT_TIME +1 (PAT_ENC.APPT_TIME TRUNC(PAT_ENC.APPT_TIME, 'DD'))


You are missing a relational operator between:
PAT_ENC.APPT_TIME +1 (PAT_ENC.APPT

Notice the "+1 (PAT"

I assume a '-' based on previous sql.

You are also missing a relational operator here:

(PAT_ENC.APPT_TIME   [WHAT GOES HERE]  TRUNC(PAT_ENC.APPT_TIME, 'DD'))
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39189370
>>Your help is so appreciated!

Please see my post in your last question you just closed...
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Records that don't match 8 42
oracle 11g 23 77
Create table from select - oracle 6 38
Component is listed with a Protocol more than once 3 27
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question