Cfrgtkky

I'm using oracle SQL developer, the create view tool. I have a function called LastNameFirst that I want to use to reorganize the customers first name / last name to appear with the last name being first.

SELECT SALE.SaleID,SALE.SaleDate, CUSTOMER.LastNameFirst(LastName,FirstName), 

SALE_ITEM.SaleItemID,SALE_ITEM.ItemID,ITEM.ItemDescription, ITEM.ItemPrice



FROM SALE_ITEM

INNER JOIN SALE on SALE.SaleID = SALE_ITEM.SaleID

INNER JOIN ITEM on ITEM.ItemID = SALE_ITEM.ItemID

INNER JOIN CUSTOMER on CUSTOMER.CustomerID = SALE.CUSTOMERID;

The error i'm getting is :
ORA-00904: "CUSTOMER"."LASTNAMEFIRST": invalid identifier

How do I call the function in that part of my select statement? I can't seem to find any examples of functions being called in select statements where joins are involved, other than using "cross apply" or "outer apply" in some way. I understand (I think) that the syntax is

SELECT 'tableName'.'columnName'

and I know my function isn't a column and that's why I'm getting that error. But I'm really not sure of how to set up the code. I tried changing it to something like:

SELECT SALE.SaleID,SALE.SaleDate,CUSTOMER.LastName, CUSTOMER.FirstName AS LastNameFirst(LastName,FirstName),SALE_ITEM.SaleItemID,SALE_ITEM.ItemID,ITEM.ItemDescription, ITEM.ItemPrice

But nothing like that worked for me either. I'd appreciate any help anyone could give me.

Functions are not properties of tables to be allowed to period qualify them. However, functions and tables among other objects share the same namespace. As mentioned in docs:

The following schema objects share one namespace:

• Tables


• Views


• Sequences


• Private synonyms


• Stand-alone procedures


• Stand-alone stored functions


• Packages


• Materialized views


• User-defined types

Therefore, simply remove the CUSTOMER. qualifier as schema is implicitly defined:

SELECT SALE.SaleID, 

       SALE.SaleDate, 

       LastNameFirst(LastName, FirstName) AS Customer_Name, ...

Alternatively, explicitly qualify the schema name on all table and function objects in the manner: "schema"."table"."column" or "schema"."function":

SELECT myschema.SALE.SaleID, 

       myschema.SALE.SaleDate, 

       myschema.LastNameFirst(myschema.CUSTOMER.LastName, 

                              myschema.CUSTOMER.FirstName) AS Customer_Name, ...