Solved

FROM Keyword not found where expected

Posted on 2013-05-22
5
632 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)
Comment Utility
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)
Comment Utility
>>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
Comment Utility
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
Comment Utility
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)
Comment Utility
>>Your help is so appreciated!

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now