Solved

FROM Keyword not found where expected

Posted on 2013-05-22
5
646 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 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 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

Technology Partners: 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

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

732 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