Home » SQL & PL/SQL » SQL & PL/SQL » Store Variable in SQL
Store Variable in SQL [message #217245] Thu, 01 February 2007 05:33 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,

mu query is like below

select deptno,count_user(deptno) from emp
group by deptno;


count_user is a user defined function, how can i get the result like

10 3
20 5
30 6

what needs to write inside the function to achieve the above result, i don't want to use count inside the count_user function

Thanks
Re: Store Variable in SQL [message #217250 is a reply to message #217245] Thu, 01 February 2007 05:43 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Shahnazurs,

I really hope I never have to work to a specification written by you, because I wouldn't have a clue what I was supposed to be doing.

What is the count_user function ? What is it supposed to do ? How are we supposed to work this out just by seeing the end result and not even seeing the input data ? And why do you need a "group by" in your query when you're not actually calling a group function ?
Re: Store Variable in SQL [message #217252 is a reply to message #217250] Thu, 01 February 2007 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
May I try? This is how I understood a question: create a function which will do COUNT(*), but without COUNT(*).
CREATE OR REPLACE FUNCTION count_user(par_deptno IN NUMBER)
RETURN NUMBER IS
  retval NUMBER := 0;
BEGIN
  FOR cur_r IN (SELECT empno FROM EMP WHERE deptno = par_deptno)
  LOOP
    retval := retval + 1;
  END LOOP;
  
  RETURN (retval);
END;
/

SELECT deptno, count_user(deptno), COUNT(*)
FROM EMP
GROUP BY deptno;

    DEPTNO COUNT_USER(DEPTNO)   COUNT(*)
---------- ------------------ ----------
        30                  6          6
        20                  5          5
        10                  3          3
Re: Store Variable in SQL [message #217256 is a reply to message #217252] Thu, 01 February 2007 06:03 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks so much.
Re: Store Variable in SQL [message #217271 is a reply to message #217245] Thu, 01 February 2007 06:59 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,
I am having a table xyz, please find the attachment for the content.
id is number
start_time & end_time is date
start_date is varchar2(11)

i was trying to write a query like below

select id,to_char(start_time,'hh24'),count_user(id,start_time,end_time) "Total Calls"
from xyz
goup by id,to_char(start_time,'hh24'),count_user(id,start_time,end_time)


in the attachment you can see 10 records, but i am counting the records from the field "Total Calls" i am getting less than that

find the function count_user in the attachment

could you please let me know how to solve this?

Thanks.


  • Attachment: function.txt
    (Size: 1.03KB, Downloaded 146 times)
Re: Store Variable in SQL [message #217274 is a reply to message #217245] Thu, 01 February 2007 07:12 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I would not expect you to get more than 1 for the count because records are only being counted if they have exactly the right start and end times. Bear in mind, these are datetimes, not just dates, so the time component is significant.

Beyond that, it looks as if Littlefoot had it about right...

Why are you trying to use PL/SQL for a problem which is perfectly solvable in ordinary SQL ? You can do something like:

select id, count(case when start_time = <start_time> and end_time = <end_time> then id end)
from xyz
group by id

[Updated on: Thu, 01 February 2007 07:13]

Report message to a moderator

Previous Topic: dynamic sql
Next Topic: TimeZone
Goto Forum:
  


Current Time: Tue Dec 06 06:28:34 CST 2016

Total time taken to generate the page: 0.30533 seconds