Solved

FROM Keyword not found where expected

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 60
error in my cursor 5 41
Is their a way to log which lines were executed in an Oracle stored procedure? 2 23
plsql job on oracle 18 38
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

829 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