0

I created a user-defined function where you put in a code for an object and it finds the minimum of the prices. I'm having trouble on trying to define an alias 'Lowest Price' for the output column.

Every time I use AS 'Lowest Price' I get the error:

Incorrect syntax near the keyword 'AS'

CREATE FUNCTION findlowprice (@oc AS INT)
RETURNS INT
AS
BEGIN 
    DECLARE @return INT

    SELECT @return = MIN(price) AS 'Lowest Price'
    FROM online_warehouse
    WHERE @oc = object_code

    RETURN @return
END;

I tried AS 'Lowest Price' in almost every line except for Line 4 BEGIN and line 10 END; and I still get the error.

Is there a way to define an alias for the output column in a user-defined function?

Castaspella
  • 5
  • 1
  • Procedural code is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Jul 15 '23 at 04:48
  • If this is for SQL Server, then you cannot do this. This is a **scalar function** that doesn't return a result set with columns (where you *could* define a column alias) - it returns only a single atomic value (of type `INT`). You reference that value by means of the **name** of that function - so if you want a different name, just name your function accordingly. – marc_s Jul 15 '23 at 04:54

2 Answers2

0

Why would you want or need an alias? The value that is returned from the function is coming from the value that is in @return, and that value is totally independent of whatever alias you would put on the expression, assuming you would be allowed to do so.

A SELECT can be used for two things:

  1. Setting one or more variables, as you are doing.
  2. Producing a result set, with rows and columns.

Only for the second variant it is possible to use a column alias. A column alias is related to a result set that is being created. The first variant does not create a result set, so column aliases make no sense and are not allowed.

As a further example, if you would try to combine the two variants, e.g. SELECT @return = MIN(price), someOtherValue FROM online_warehouse then you would see this error:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

This shows that the two variants are actually distinguished by the compiler (and that they can't be combined).

Peter B
  • 22,460
  • 5
  • 32
  • 69
-1

I suggest adding semicolons after the DECLARE statement and the WHERE clause. The error could be happening because you assigned a variable within the SELECT statement. Maybe using alias when using the function would be helpful.

CREATE FUNCTION findlowprice (@oc AS INT)
RETURNS INT
AS
BEGIN 
    DECLARE @return INT;

    SELECT @return = MIN(price) AS 'Lowest Price'
    FROM online_warehouse
    WHERE @oc = object_code;

    RETURN @return
END;
dev0717
  • 177
  • 4
  • Tried this, so far I still get the "Incorrect syntax near the keyword 'AS'." error still. But thanks for the help – Castaspella Jul 15 '23 at 22:08