CODEWITHSUNDEEP
sqloracle-database
J.Griff2
J.Griff2

Reputation: 21

Using a function in a select statement with multiple tables while creating a view

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.

Upvotes: 1

Views: 66

Answers (1)

Parfait
Parfait

Reputation: 107567

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, ...

Upvotes: 1

Related Questions