CODEWITHSUNDEEP
sqloracle-databasesyntaxora-00905
Sheldor
Sheldor

Reputation: 61

SQL / ORACLE Missing keyword

I am working on an sql file trying to make a script which brings back either all the data from the tables shown or all the data from the tables plus 3 months back of data. I keep getting an error that says error 00905 missing keyword and I don't understand why.

I looked at the syntax and it all looks right to me. I will put a marker at the point it says the error occurred(at the first inner join). I changed all the table names to make it look better since it's over the syntax.

Any help would be great thank you. This is only a section of the code but I'm sure it has to be in here.

WHEN Table_typ_cde = '1' -- Pulling all data --
  THEN
    INNER JOIN  Table   acp ON Table_oid = Table_oid
    INNER JOIN  Table   loss ON Table_oid = Table_oid
    LEFT OUTER JOIN Table veh ON Table_oid = Table_oid and Table_nbr=1
    INNER JOIN Table   brnch ON Table_OID = Table_oid
    INNER JOIN Table   a ON Table_OID = Table_oid
    INNER JOIN Table   c Table_OID = Table_oid
    INNER JOIN Table   s ON Table_OID = Table_oid
    INNER JOIN Table   r ON Table_OID = Table_oid
    INNER JOIN Table   grp ON Table_oid = Table_oid
    INNER JOIN Table on Table_id = Table_id
    WHERE Table_ID <> 2
 WHEN Table_cde = '2' -- Pulling all data within the last 3 months along with all present data --
 THEN
    INNER JOIN  Table   acp ON Table_oid = Table_oid
    INNER JOIN  Table   loss ON Table_oid = Table_oid
    LEFT OUTER JOIN Table veh ON Table_oid = Table_oid and Table_nbr=1
    INNER JOIN Table   brnch ON Table_OID = Table_oid
    INNER JOIN Table   a ON Table_OID = Table_oid
    INNER JOIN Table   c ON Table_OID = Table_oid
    INNER JOIN Table   s ON Table_OID = Table_oid
    INNER JOIN Table   r ON Table_OID = Table_oid
    INNER JOIN Table   grp ON Table_oid = Table_oid
    INNER JOIN Table on Table_id = Table_id
    WHERE Table_ID <> 2
    AND df.DATE_CREATED BETWEEN ADD_MONTHS(SYSDATE, -6)
    AND sysdate
END                                         
) core

Upvotes: 0

Views: 2300

Answers (2)

APC
APC

Reputation: 146239

You have two sets of joins which appear to be equal. Then you have a date filter which you wish to apply conditionally. You need to apply that in the WHERE clause not the FROM. Some like this 

FROM  ... INNER JOIN  Table   acp ON Table_oid = Table_oid
    INNER JOIN  Table   loss ON Table_oid = Table_oid
    LEFT OUTER JOIN Table veh ON Table_oid = Table_oid and Table_nbr=1
    INNER JOIN Table   brnch ON Table_OID = Table_oid
    INNER JOIN Table   a ON Table_OID = Table_oid
    INNER JOIN Table   c ON Table_OID = Table_oid
    INNER JOIN Table   s ON Table_OID = Table_oid
    INNER JOIN Table   r ON Table_OID = Table_oid
    INNER JOIN Table   grp ON Table_oid = Table_oid
    INNER JOIN Table on Table_id = Table_id
WHERE Table_ID <> 2
    AND (Table_cde = '1' 
          or ( Table_cde = '2' 
          and df.DATE_CREATED BETWEEN ADD_MONTHS(SYSDATE, -6) AND sysdate ))

Upvotes: 0

John Woo
John Woo

Reputation: 263723

You cannot break the JOIN using CASE. i've seen this in some part of your query,

THEN
   INNER JOIN  Table   acp ON Table_oid = Table_oid
   INNER JOIN  Table   loss ON Table_oid = Table_oid

that is wrong, you must supply the tablename before INNER JOIN keyword.

Upvotes: 1

Related Questions