Self join sub query syntax

[ready4data]

I haven' been using SQL to long and I'm trying to create a query.
The syntax works in MS Access but when brought into oracle(Toad) I get a message at the ". AS" ORA-00905: missing keyword. I replaced the [ ]'s with ( ) after the INNER JOIN statement because it didn't line the joined table name when I used them.

SELECT APPS.CST_ITEM_COST_TYPE_V.Item_number, APPS.CST_ITEM_COST_TYPE_V.Description, APPS.CST_ITEM_COST_TYPE_V.Cost_Type, APPS.CST_ITEM_COST_TYPE_V.Item_Cost
FROM APPS.CST_ITEM_COST_TYPE_V INNER JOIN (SELECT Item_Number, Max(Cost_Type) AS MaxCost_Type FROM APPS.CST_ITEM_COST_TYPE_V GROUP BY Item_Number). AS q ON (APPS.CST_ITEM_COST_TYPE_V.Cost_Type = q.MaxCost_Type) AND (APPS.CST_ITEM_COST_TYPE_V.Item_number = q.Item_Number)

Can anyone help out.
Thanks,
Scott

[guidomarcel]

HI,
there is a dot where it should not be. I formatted your SQL using the online formatter at www.sqlinform.com

SELECT 
    APPS.CST_ITEM_COST_TYPE_V.Item_number, 
    APPS.CST_ITEM_COST_TYPE_V.Description, 
    APPS.CST_ITEM_COST_TYPE_V.Cost_Type, 
    APPS.CST_ITEM_COST_TYPE_V.Item_Cost 
FROM 
    APPS.CST_ITEM_COST_TYPE_V 
INNER JOIN 
    (SELECT 
        Item_Number, 
        Max(Cost_Type) AS MaxCost_Type 
    FROM 
        APPS.CST_ITEM_COST_TYPE_V 
    GROUP BY 
        Item_Number
    )
    [COLOR=Red]. AS q [/COLOR]
    ON (APPS.CST_ITEM_COST_TYPE_V.Cost_Type    = q.MaxCost_Type) 
    AND (APPS.CST_ITEM_COST_TYPE_V.Item_number = q.Item_Number)

Hope this helps
Guidomarcel