?
Solved

FROM Keyword not found where expected

Posted on 2013-05-22
5
Medium Priority
?
652 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 77

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 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 77

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

752 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