| Is it possible in a single Select Statement.?! [message #370969] |
Wed, 26 July 2000 12:28  |
Vasu
Messages: 9
Registered: June 1999
|
Junior Member |
|
|
Hi,
I am trying to write a select statement which should select total number of orders for a given customer.
The task is, if the total number of orders is greater than the number,which is passed into the query,then query should return TRUE other wise FALSE.
Any help is appreciated.
Thanks,
-Vasu.
|
|
|
|
| Re: Is it possible in a single Select Statement.?! [message #370980 is a reply to message #370969] |
Fri, 28 July 2000 09:49  |
Lee
Messages: 56
Registered: May 1999
|
Member |
|
|
I'm not sure if this is what you ment, and I didn't actualy run in but I think it should work. Let me know.
*************************************
CREATE FUNCTION cstmr_count
(P_CSTMR_ID IN VARCHAR2,
P_TOTAL_NUM NUMBER)
RETURN BOOLEAN IS
CSTMR_ORD_NUM NUMBER;
BEGIN
SELECT COUNT(*)
INTO CSTMR_ORD_NUM
FROM MY_TBL
GROUP BY P_CSTMR_ID;
IF CSTMR_ORD_NUM > P_TOTAL_NUM THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
/
|
|
|
|
| Re: Is it possible in a single Select Statement.?! [message #370985 is a reply to message #370969] |
Fri, 28 July 2000 16:33 |
Priyadarshini
Messages: 10
Registered: July 2000
|
Junior Member |
|
|
I do agree with Lee's code but since you had asked for a single SQL statement I hope the solution provided by me...also would be helpful
SELECT
DECODE(GREATEST(COUNT(order_no),3),COUNT(order_no),'TRUE','FALSE') FROM orders
GROUP BY customer_no;
the table ORDERS in the above statement has only 2 columns i.e order_no and customer_no. I am checking such that a given customer has 3 orders.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
