Home » RDBMS Server » Server Administration » Is it possible in a single Select Statement.?!
Is it possible in a single Select Statement.?! [message #370969] Wed, 26 July 2000 12:28 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Re: @corelated subquery
Next Topic: How to pass an array as input parameter??
Goto Forum:
  


Current Time: Sat Oct 24 04:13:01 CDT 2020