Help with Query - FROM keyword not found where expected error

[cerebalbore]

Hi,

I have an old query (not written by me) which is running against an oracle database, but I get the error "FROM not found where expected". The from looks to me like it is in the right place!

I am not used to Oracle, but am ok with SQL. I am using MS Query because I don't know any other way of looking at the data.

Here's the query (I've tried to make it easier to read):

SELECT ee_empno, ee_title || ' ' || ee_inits || ' ' || ee_sname name, ee_dept, ee_company, ee_hol_enttlment + eels_carry_fwd hol_entitlement, ee_hol_taken,sum(decode(SIGN(to_char (msa_date, 'DDD') - to_char (sysdate, 'DDD')), -1, decode(msa_duration, -3, 1, 0.5), 0)) total_hol_taken,
sum(decode(msa_duration, -3, 1, 0.5)) total_hol_booked
FROM eels, msa, ms, ee
WHERE eels.eels_year + 1 = 2010
AND msa_date = ms_date
AND ms_ds_std_day <> '0'
AND ee_empno = msa_empno
AND (ee_lvdate is Null)
AND to_char (msa_date, 'YYYY') = 2010
AND msa_timecode = '300'
AND ms_empno = ee_empno
GROUP BY ee_empno, ee_title || ' ' || ee_inits || ' ' || ee_sname, ee_dept, ee_company, ee_hol_enttlment + eels_carry_fwd, ee_hol_taken
ORDER BY ee_empno, ee_dept

I have tried to Google but the only thing I have found is that using reserved keywords in the wrong place could throw errors. The only keyword I can see in this query is 'sysdate', and if I try to replace that with a subquery (SELECT sysdate FROM DUAL) it them tells me it cannot add the table ''DDD'))'.

Any help would be appreciated.

Thanks

Kat

I'm such a noob

 

[Dagon]

ORA-923 is such a basic error you probably wouldn't find much information. For example, you can get it if you do something like:

SELECT ee_empno, ee_title

and miss out the from clause. I would suspect you are not passing the whole query to the database. Maybe you have some blank lines in it or something like that and it's only passing the select part of the query.


For Oracle-related work, contact me through Linked-In.

 

[cerebalbore]

Hi,

I've tried removing any spaces in the query, but I noticed one thing. The eels table doesn't appear to be joined (there is no where clause to join it to anything)

So I've added it in (probably wrongly) and have this (now not formatted):
SELECT ee_empno, ee_title || ' ' || ee_inits || ' ' || ee_sname name, ee_dept, ee_company, ee_hol_enttlment + eels_carry_fwd hol_entitlement, ee_hol_taken, sum(decode(SIGN(to_char (msa_date, 'DDD') - to_char (sysdate, 'DDD')), -1, decode(msa_duration, -3, 1, 0.5), 0)) total_hol_taken, sum(decode(msa_duration, -3, 1, 0.5)) total_hol_booked FROM eels, msa, ms, ee WHERE eels.eels_year + 1 = 2010 AND msa_date = ms_date AND ms_ds_std_day <> '0' AND ee_empno = msa_empno AND (ee_lvdate is Null) AND to_char (msa_date, 'YYYY') = 2010 AND msa_timecode = '300' AND ms_empno = ee_empno AND eels_empno = ee_empno GROUP BY ee_empno, ee_title || ' ' || ee_inits || ' ' || ee_sname, ee_dept, ee_company, ee_hol_enttlment + eels_carry_fwd, ee_hol_taken ORDER BY ee_empno, ee_dept

I've also tried to move the SUM aggregates to the front of the select, but I still get the same error though :-(

I'm such a noob

 

[Dagon]

A missing where clause would not give you that error. Make the query as basic as you can by removing all the complicated expressions in the select - just select a single column with no functions around it. If that works, then your problem is probably that you have a missing bracket somewhere. Start building the query up one expression at a time until you can locate where the problem is.

For Oracle-related work, contact me through Linked-In.

 

[LKBrwnDBA]

Copy the following "formatted" query into text file with ".sql" suffix, for example: myQuery.sql

  SELECT ee_empno,
         ee_title || ' ' || ee_inits || ' ' || ee_sname name,
         ee_dept,
         ee_company,
         ee_hol_enttlment + eels_carry_fwd hol_entitlement,
         ee_hol_taken,
         SUM(DECODE (
                SIGN (TO_CHAR (msa_date, 'DDD') - TO_CHAR (SYSDATE, 'DDD')),
                -1,
                DECODE (msa_duration, -3, 1, 0.5),
                0))
            total_hol_taken,
         SUM (DECODE (msa_duration, -3, 1, 0.5)) total_hol_booked
    FROM eels,
         msa,
         ms,
         ee
   WHERE     eels.eels_year + 1 = 2010
         AND msa_date = ms_date
         AND ms_ds_std_day <> '0'
         AND ee_empno = msa_empno
         AND (ee_lvdate IS NULL)
         AND TO_CHAR (msa_date, 'YYYY') = 2010
         AND msa_timecode = '300'
         AND ms_empno = ee_empno
GROUP BY ee_empno,
         ee_title || ' ' || ee_inits || ' ' || ee_sname,
         ee_dept,
         ee_company,
         ee_hol_enttlment + eels_carry_fwd,
         ee_hol_taken
ORDER BY ee_empno, ee_dept

Then using sqlplus, do this:

set echo on
@myQuery.sql

And check out the lines where you get errors.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb