Need to create a function
Hands‐On Assignment 6‐4 with modifications: Identifying the day of the week for the order date
The day of the week that baskets are created is quite often analyzed to determine consumer shopping patterns. Create a function named DAY_ORD_SF that accepts an order date and returns the day of the week for those orders that have actually been placed. Use the function in a SELECT statement to display each basket id and the day of the week the order was created. Do a second SELECT statement using this function to display the total number of orders for each day of the week that has orders. (Hint: Call the TO_CHAR Function to retrieve the day of week from a date.)

This what I have so far would you be able to send some tweaks?

CREATE OR REPLACE FUNCTION day_ord_sf
(p_order_date IN DATE)
RETURN VARCHAR2
IS
lv_day_week VARCHAR2(9);
BEGIN
SELECT TO_CHAR(p_order_date)'fmdD' , "DAY")
INTO lv_day_week
FROM bb_basket
WHERE idBasket ;
RETURN lv_day_week;
END;

Tr using this

SELECT TO_CHAR(sysdate,'DAY') from dual

for the 2nd part of the question use the above query (modified as per your requirement) and use group by clause on the day.

Member Avatar for hfx642

Since you just want the DoW from the date parameter,
replace your Select/Return with...

Return To_Char (P_Order_Date, 'fmDay');


The second part (your analysis) would be...

Select Day_Ord_SF (Order_Date_Column), Count *
From BB_Basket
Group by Day_Ord_SF (Order_Date_Column);

in your function day_ord_sf you have to replace bb_basket table name with dummy table dual.
it is because you are already giving date value as perameter in your function.
right ??

so why you need to extract value from your bb_basket table in your function (actually it is a wrong syntex but i as thinking you were doing this only).

second in to_char function for formate string (as you have given :"DAY") it should be in single quotes 'DAY'.

CREATE OR REPLACE FUNCTION day_ord_sf
(p_order_date IN DATE)
RETURN VARCHAR2
IS
lv_day_week VARCHAR2(9);
BEGIN
SELECT TO_CHAR(p_order_date) , 'DAY') 
INTO lv_day_week
FROM dual;
RETURN lv_day_week;
END;

and for select

select Day_Ord_SF (Order_Date_Column) "Day", count(*)
from your_table_name
group by Day_Ord_SF (Order_Date_Column)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.