Solved

Tried to write a query and got the error ORA-00923: FROM keyword not found where expected

Posted on 2007-04-02
6
372 Views
Last Modified: 2013-12-19
I had a problem when I was trying to generate data from a query that I am trying to create.  I got this error called "ORA-00923: FROM keyword not found where expected"  What am I doing wrong?  Thanks!

SELECT
    B.TICKET_OWNER,
    B.TXT_ASSIGNED_TO,
    B.ENU_STATUS,
    B.TXT_DIRVP_CUST_NAME,
    B.TXT_APPLICATION,
    B.TXT_SA_OUTAGE_DESCRIPTION,
    B.TXT_ICD_DESC,
    B.TXT_ICD,IMMED_ACTION,
    B.TXT_ICD_PERM_RESOLUTION,
    B.TXT_ICD_TECH_NOTES
    B.TXT_CRITICAL_TICKET_TYPE,
    B.TXT_TICKET_TRACKING_REQUEST_ID,
    B.TXT_SUBISSUE_REQUEST_ID,
    B.ENU_ICD_PREVENTABLE,
    B.ENU_ICD_ALARMING_IN_PLACE,
    B.ENU_ICD_ALARM_ID_PROBLEM,
    B.TXT_CAUSE,
    B.DTM_IMPACT_START_TIME,
    B.DTM_IMPACT_END_TIME,
    B.INT_LOCATION_COUNT,
    B.ENU_PRIMARY_OWNER,
    B.TXT_TIME_ZONE,
    B.TIM_SUNDAY_START_TIME,
    B.TIM_MONDAY_START_TIME,
    B.TIM_TUESDAY_START_TIME,
    B.TIM_WEDNESDAY_START_TIME,
    B.TIM_THURSDAY_START_TIME,
    B.TIM_FRIDAY_START_TIME,
    B.TIM_SATURDAY_START_TIME,
    B.TIM_SUNDAY_END_TIME,
    B.TIM_MONDAY_END_TIME,
    B.TIM_TUESDAY_END_TIME,
    B.TIM_WEDNESDAY_END_TIME,
    B.TIM_THURSDAY_END_TIME,
    B.TIM_FRIDAY_END_TIME,
    B.TIM_SATURDAY_END_TIME,
    B.TXT_LOCATION_NAME,
    B.INT_FUNCTIONAL_PCT,
    B.INT_IMPACT_DURATION,
    B.INT_USER_PCT,
    B.INT_SLOW_PCT,
    B.INT_TOD_PCT,
    B.ENU_SCORABLE,
    B.DTE_START_DATE,
    B.INT_DAILY_DURATION,
    B.INT_TOTAL_DURATION,
    B.ENU_EXEC_REPORTING,
    C.TXT_AREA,
    C.TXT_REGION,
    C.TXT_STATE,
    C.TXT_CITY,
    C.TXT_LOCATION_NAME,
    COUNT(C.DTE_START_DATE) AS "COUNT_DATES"
FROM
    RECUR_SITE_ANALY_QRY C,
    RECUR_SITE_ANALY_QRY B
GROUP BY
    C.TXT_AREA,
    C.TXT_REGION,
    C.TXT_STATE,
    C.TXT_CITY,
    C.TXT_LOCATION_NAME
WHERE
C.TXT_AREA = B.TXT_AREA,
C.TXT_REGION = B.TXT_REGION,
C.TXT_STATE = B.TXT_STATE,
C.TXT_CITY = B.TXT_CITY,
C.TXT_LOCATION_NAME = B.TXT_LOCATION_NAME
0
Comment
Question by:VBBRett
[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
6 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18837266
Try the below :
SELECT
    B.TICKET_OWNER,
    B.TXT_ASSIGNED_TO,
    B.ENU_STATUS,
    B.TXT_DIRVP_CUST_NAME,
    B.TXT_APPLICATION,
    B.TXT_SA_OUTAGE_DESCRIPTION,
    B.TXT_ICD_DESC,
    B.TXT_ICD,IMMED_ACTION,
    B.TXT_ICD_PERM_RESOLUTION,
    B.TXT_ICD_TECH_NOTES
    B.TXT_CRITICAL_TICKET_TYPE,
    B.TXT_TICKET_TRACKING_REQUEST_ID,
    B.TXT_SUBISSUE_REQUEST_ID,
    B.ENU_ICD_PREVENTABLE,
    B.ENU_ICD_ALARMING_IN_PLACE,
    B.ENU_ICD_ALARM_ID_PROBLEM,
    B.TXT_CAUSE,
    B.DTM_IMPACT_START_TIME,
    B.DTM_IMPACT_END_TIME,
    B.INT_LOCATION_COUNT,
    B.ENU_PRIMARY_OWNER,
    B.TXT_TIME_ZONE,
    B.TIM_SUNDAY_START_TIME,
    B.TIM_MONDAY_START_TIME,
    B.TIM_TUESDAY_START_TIME,
    B.TIM_WEDNESDAY_START_TIME,
    B.TIM_THURSDAY_START_TIME,
    B.TIM_FRIDAY_START_TIME,
    B.TIM_SATURDAY_START_TIME,
    B.TIM_SUNDAY_END_TIME,
    B.TIM_MONDAY_END_TIME,
    B.TIM_TUESDAY_END_TIME,
    B.TIM_WEDNESDAY_END_TIME,
    B.TIM_THURSDAY_END_TIME,
    B.TIM_FRIDAY_END_TIME,
    B.TIM_SATURDAY_END_TIME,
    B.TXT_LOCATION_NAME,
    B.INT_FUNCTIONAL_PCT,
    B.INT_IMPACT_DURATION,
    B.INT_USER_PCT,
    B.INT_SLOW_PCT,
    B.INT_TOD_PCT,
    B.ENU_SCORABLE,
    B.DTE_START_DATE,
    B.INT_DAILY_DURATION,
    B.INT_TOTAL_DURATION,
    B.ENU_EXEC_REPORTING,
    C.TXT_AREA,
    C.TXT_REGION,
    C.TXT_STATE,
    C.TXT_CITY,
    C.TXT_LOCATION_NAME,
    COUNT(C.DTE_START_DATE) AS "COUNT_DATES"
FROM
    RECUR_SITE_ANALY_QRY C,
    RECUR_SITE_ANALY_QRY B
WHERE
C.TXT_AREA = B.TXT_AREA,
C.TXT_REGION = B.TXT_REGION,
C.TXT_STATE = B.TXT_STATE,
C.TXT_CITY = B.TXT_CITY,
C.TXT_LOCATION_NAME = B.TXT_LOCATION_NAME
GROUP BY   ---------> group by should come after the where clause.
    C.TXT_AREA,
    C.TXT_REGION,
    C.TXT_STATE,
    C.TXT_CITY,
    C.TXT_LOCATION_NAME;

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18837278
but the above query will give 'not a group by expression error' because all the fields you are selecting are not in the group by list.

so try the below :

SELECT /* ---> i have commented out all the b.fields because they are not in the group by clause
    B.TICKET_OWNER,
    B.TXT_ASSIGNED_TO,
    B.ENU_STATUS,
    B.TXT_DIRVP_CUST_NAME,
    B.TXT_APPLICATION,
    B.TXT_SA_OUTAGE_DESCRIPTION,
    B.TXT_ICD_DESC,
    B.TXT_ICD,IMMED_ACTION,
    B.TXT_ICD_PERM_RESOLUTION,
    B.TXT_ICD_TECH_NOTES
    B.TXT_CRITICAL_TICKET_TYPE,
    B.TXT_TICKET_TRACKING_REQUEST_ID,
    B.TXT_SUBISSUE_REQUEST_ID,
    B.ENU_ICD_PREVENTABLE,
    B.ENU_ICD_ALARMING_IN_PLACE,
    B.ENU_ICD_ALARM_ID_PROBLEM,
    B.TXT_CAUSE,
    B.DTM_IMPACT_START_TIME,
    B.DTM_IMPACT_END_TIME,
    B.INT_LOCATION_COUNT,
    B.ENU_PRIMARY_OWNER,
    B.TXT_TIME_ZONE,
    B.TIM_SUNDAY_START_TIME,
    B.TIM_MONDAY_START_TIME,
    B.TIM_TUESDAY_START_TIME,
    B.TIM_WEDNESDAY_START_TIME,
    B.TIM_THURSDAY_START_TIME,
    B.TIM_FRIDAY_START_TIME,
    B.TIM_SATURDAY_START_TIME,
    B.TIM_SUNDAY_END_TIME,
    B.TIM_MONDAY_END_TIME,
    B.TIM_TUESDAY_END_TIME,
    B.TIM_WEDNESDAY_END_TIME,
    B.TIM_THURSDAY_END_TIME,
    B.TIM_FRIDAY_END_TIME,
    B.TIM_SATURDAY_END_TIME,
    B.TXT_LOCATION_NAME,
    B.INT_FUNCTIONAL_PCT,
    B.INT_IMPACT_DURATION,
    B.INT_USER_PCT,
    B.INT_SLOW_PCT,
    B.INT_TOD_PCT,
    B.ENU_SCORABLE,
    B.DTE_START_DATE,
    B.INT_DAILY_DURATION,
    B.INT_TOTAL_DURATION,
    B.ENU_EXEC_REPORTING, */
    C.TXT_AREA,
    C.TXT_REGION,
    C.TXT_STATE,
    C.TXT_CITY,
    C.TXT_LOCATION_NAME,
    COUNT(C.DTE_START_DATE) AS "COUNT_DATES"
FROM
    RECUR_SITE_ANALY_QRY C,
    RECUR_SITE_ANALY_QRY B
WHERE
C.TXT_AREA = B.TXT_AREA,
C.TXT_REGION = B.TXT_REGION,
C.TXT_STATE = B.TXT_STATE,
C.TXT_CITY = B.TXT_CITY,
C.TXT_LOCATION_NAME = B.TXT_LOCATION_NAME
GROUP BY   ---------> group by should come after the where clause.
    C.TXT_AREA,
    C.TXT_REGION,
    C.TXT_STATE,
    C.TXT_CITY,
    C.TXT_LOCATION_NAME;

Thanks
0
 

Author Comment

by:VBBRett
ID: 18837330
I still got the FROM Keyword not found where expected error.  What am I doing wrong?
0
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!

 
LVL 8

Expert Comment

by:YANN0S
ID: 18837373
I don't know if that is the only problem, but it is definatelly one: in the WHERE part you should commas with ANDs:

SELECT /* ---> i have commented out all the b.fields because they are not in the group by clause
    B.TICKET_OWNER,
    B.TXT_ASSIGNED_TO,
    B.ENU_STATUS,
    B.TXT_DIRVP_CUST_NAME,
    B.TXT_APPLICATION,
    B.TXT_SA_OUTAGE_DESCRIPTION,
    B.TXT_ICD_DESC,
    B.TXT_ICD,IMMED_ACTION,
    B.TXT_ICD_PERM_RESOLUTION,
    B.TXT_ICD_TECH_NOTES
    B.TXT_CRITICAL_TICKET_TYPE,
    B.TXT_TICKET_TRACKING_REQUEST_ID,
    B.TXT_SUBISSUE_REQUEST_ID,
    B.ENU_ICD_PREVENTABLE,
    B.ENU_ICD_ALARMING_IN_PLACE,
    B.ENU_ICD_ALARM_ID_PROBLEM,
    B.TXT_CAUSE,
    B.DTM_IMPACT_START_TIME,
    B.DTM_IMPACT_END_TIME,
    B.INT_LOCATION_COUNT,
    B.ENU_PRIMARY_OWNER,
    B.TXT_TIME_ZONE,
    B.TIM_SUNDAY_START_TIME,
    B.TIM_MONDAY_START_TIME,
    B.TIM_TUESDAY_START_TIME,
    B.TIM_WEDNESDAY_START_TIME,
    B.TIM_THURSDAY_START_TIME,
    B.TIM_FRIDAY_START_TIME,
    B.TIM_SATURDAY_START_TIME,
    B.TIM_SUNDAY_END_TIME,
    B.TIM_MONDAY_END_TIME,
    B.TIM_TUESDAY_END_TIME,
    B.TIM_WEDNESDAY_END_TIME,
    B.TIM_THURSDAY_END_TIME,
    B.TIM_FRIDAY_END_TIME,
    B.TIM_SATURDAY_END_TIME,
    B.TXT_LOCATION_NAME,
    B.INT_FUNCTIONAL_PCT,
    B.INT_IMPACT_DURATION,
    B.INT_USER_PCT,
    B.INT_SLOW_PCT,
    B.INT_TOD_PCT,
    B.ENU_SCORABLE,
    B.DTE_START_DATE,
    B.INT_DAILY_DURATION,
    B.INT_TOTAL_DURATION,
    B.ENU_EXEC_REPORTING, */
    C.TXT_AREA,
    C.TXT_REGION,
    C.TXT_STATE,
    C.TXT_CITY,
    C.TXT_LOCATION_NAME,
    COUNT(C.DTE_START_DATE) AS "COUNT_DATES"
FROM
    RECUR_SITE_ANALY_QRY C,
    RECUR_SITE_ANALY_QRY B
WHERE
C.TXT_AREA = B.TXT_AREA AND
C.TXT_REGION = B.TXT_REGION AND
C.TXT_STATE = B.TXT_STATE AND
C.TXT_CITY = B.TXT_CITY AND
C.TXT_LOCATION_NAME = B.TXT_LOCATION_NAME
GROUP BY   ---------> group by should come after the where clause.
    C.TXT_AREA,
    C.TXT_REGION,
    C.TXT_STATE,
    C.TXT_CITY,
    C.TXT_LOCATION_NAME;
0
 
LVL 8

Accepted Solution

by:
YANN0S earned 500 total points
ID: 18837421
Sorry I did not check your initial query, I just checked the on with the columns in comments. You forgot a comma in your SELECT list.

So fixing the missing comma in SELECT, the commas instead of ANDs in WHERE and the missing columns in GROUP BY, your query should be:

SELECT
    B.TICKET_OWNER,
    B.TXT_ASSIGNED_TO,
    B.ENU_STATUS,
    B.TXT_DIRVP_CUST_NAME,
    B.TXT_APPLICATION,
    B.TXT_SA_OUTAGE_DESCRIPTION,
    B.TXT_ICD_DESC,
    B.TXT_ICD,IMMED_ACTION,
    B.TXT_ICD_PERM_RESOLUTION,
    B.TXT_ICD_TECH_NOTES,
    B.TXT_CRITICAL_TICKET_TYPE,
    B.TXT_TICKET_TRACKING_REQUEST_ID,
    B.TXT_SUBISSUE_REQUEST_ID,
    B.ENU_ICD_PREVENTABLE,
    B.ENU_ICD_ALARMING_IN_PLACE,
    B.ENU_ICD_ALARM_ID_PROBLEM,
    B.TXT_CAUSE,
    B.DTM_IMPACT_START_TIME,
    B.DTM_IMPACT_END_TIME,
    B.INT_LOCATION_COUNT,
    B.ENU_PRIMARY_OWNER,
    B.TXT_TIME_ZONE,
    B.TIM_SUNDAY_START_TIME,
    B.TIM_MONDAY_START_TIME,
    B.TIM_TUESDAY_START_TIME,
    B.TIM_WEDNESDAY_START_TIME,
    B.TIM_THURSDAY_START_TIME,
    B.TIM_FRIDAY_START_TIME,
    B.TIM_SATURDAY_START_TIME,
    B.TIM_SUNDAY_END_TIME,
    B.TIM_MONDAY_END_TIME,
    B.TIM_TUESDAY_END_TIME,
    B.TIM_WEDNESDAY_END_TIME,
    B.TIM_THURSDAY_END_TIME,
    B.TIM_FRIDAY_END_TIME,
    B.TIM_SATURDAY_END_TIME,
    B.TXT_LOCATION_NAME,
    B.INT_FUNCTIONAL_PCT,
    B.INT_IMPACT_DURATION,
    B.INT_USER_PCT,
    B.INT_SLOW_PCT,
    B.INT_TOD_PCT,
    B.ENU_SCORABLE,
    B.DTE_START_DATE,
    B.INT_DAILY_DURATION,
    B.INT_TOTAL_DURATION,
    B.ENU_EXEC_REPORTING,
    C.TXT_AREA,
    C.TXT_REGION,
    C.TXT_STATE,
    C.TXT_CITY,
    C.TXT_LOCATION_NAME,
    COUNT(C.DTE_START_DATE) AS "COUNT_DATES"
FROM
    RECUR_SITE_ANALY_QRY C,
    RECUR_SITE_ANALY_QRY B
WHERE
C.TXT_AREA = B.TXT_AREA AND
C.TXT_REGION = B.TXT_REGION AND
C.TXT_STATE = B.TXT_STATE AND
C.TXT_CITY = B.TXT_CITY AND
C.TXT_LOCATION_NAME = B.TXT_LOCATION_NAME
GROUP BY    B.TICKET_OWNER,
    B.TXT_ASSIGNED_TO,
    B.ENU_STATUS,
    B.TXT_DIRVP_CUST_NAME,
    B.TXT_APPLICATION,
    B.TXT_SA_OUTAGE_DESCRIPTION,
    B.TXT_ICD_DESC,
    B.TXT_ICD,IMMED_ACTION,
    B.TXT_ICD_PERM_RESOLUTION,
    B.TXT_ICD_TECH_NOTES,
    B.TXT_CRITICAL_TICKET_TYPE,
    B.TXT_TICKET_TRACKING_REQUEST_ID,
    B.TXT_SUBISSUE_REQUEST_ID,
    B.ENU_ICD_PREVENTABLE,
    B.ENU_ICD_ALARMING_IN_PLACE,
    B.ENU_ICD_ALARM_ID_PROBLEM,
    B.TXT_CAUSE,
    B.DTM_IMPACT_START_TIME,
    B.DTM_IMPACT_END_TIME,
    B.INT_LOCATION_COUNT,
    B.ENU_PRIMARY_OWNER,
    B.TXT_TIME_ZONE,
    B.TIM_SUNDAY_START_TIME,
    B.TIM_MONDAY_START_TIME,
    B.TIM_TUESDAY_START_TIME,
    B.TIM_WEDNESDAY_START_TIME,
    B.TIM_THURSDAY_START_TIME,
    B.TIM_FRIDAY_START_TIME,
    B.TIM_SATURDAY_START_TIME,
    B.TIM_SUNDAY_END_TIME,
    B.TIM_MONDAY_END_TIME,
    B.TIM_TUESDAY_END_TIME,
    B.TIM_WEDNESDAY_END_TIME,
    B.TIM_THURSDAY_END_TIME,
    B.TIM_FRIDAY_END_TIME,
    B.TIM_SATURDAY_END_TIME,
    B.TXT_LOCATION_NAME,
    B.INT_FUNCTIONAL_PCT,
    B.INT_IMPACT_DURATION,
    B.INT_USER_PCT,
    B.INT_SLOW_PCT,
    B.INT_TOD_PCT,
    B.ENU_SCORABLE,
    B.DTE_START_DATE,
    B.INT_DAILY_DURATION,
    B.INT_TOTAL_DURATION,
    B.ENU_EXEC_REPORTING,
    C.TXT_AREA,
    C.TXT_REGION,
    C.TXT_STATE,
    C.TXT_CITY,
    C.TXT_LOCATION_NAME
0
 
LVL 32

Expert Comment

by:awking00
ID: 18838396
The problem is you're missing a comma after
B.TXT_ICD_TECH_NOTES
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses

630 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