New to SQL. I am having some issues with a function.
Assignment is:
1. Develop and run a CREATE FUNCTION statement to creat the DAY_ORD_SF function. Use the DTCREATED column of the bb_basket table as the date the basket was created. Call the TO_CHAR function using the DAY option to retrieve the day of week for a date value.
1. Create a SELECT statement that lists the basket id and the day of the week placed for every basket.
3. Create a SELECT statement using a GROUP BY clause to list the total number of baskets per day of the week. Which is the most popular shopping day? (Should discover it is FRIDAY).
So far, I have the function:
CREATE OR REPLACE FUNCTION DAY_ORD_SF (P_ORDER_DATE IN BB_BASKET.DTORDERED%TYPE)
RETURN VARCHAR2
IS
LV_DAY_WK VARCHAR2(3);
BEGIN
SELECT TO_CHAR(P_ORDER_DATE, 'DAY')
INTO LV_DAY_WK
FROM BB_BASKET
WHERE P_ORDER_DATE = DTORDERED;
RETURN LV_DAY_WK;
END;
/
Have tried a select and getting invalid identifier on o_dt. have tried many different variables and cannot find the one that works.
SELECT DAY_ORD_SF(O_DT) "DAY", COUNT(*)
FROM BB_BASKET
GROUP BY DAY_ORD_SF(O_DT)
;
Any assistance would be appreciated.