Home » Developer & Programmer » Reports & Discoverer » how to capture count of sundays
| how to capture count of sundays [message #144182] |
Tue, 25 October 2005 07:04  |
sirishay
Messages: 40
Registered: August 2005
Location: Hyderabad
|
Member |
|
|
hai all,
i am working on reprots 2.5,i have the following query
SELECT x, theday
FROM (SELECT TRUNC (SYSDATE, 'MONTH') + ROWNUM - 1 x,
TO_CHAR (TRUNC (SYSDATE, 'MONTH') + ROWNUM - 1, 'DY') theday
FROM user_tab_columns
WHERE ROWNUM <= TO_CHAR (LAST_DAY (TRUNC
(SYSDATE, 'MONTH')), 'DD'))
WHERE theday = 'SUN';
output
------
X THE
-------- ---
02-10-05 SUN
09-10-05 SUN
16-10-05 SUN
23-10-05 SUN
30-10-05 SUN
now i want the count of sundays in my report. i am taking that in a formula column.but i am getting error.
function CF_1Formula return Char is
LVFLAG VARCHAR2(2);
LVDAY VARCHAR2(2);
begin
SELECT COUNT( A )
INTO LVFLAG
FROM ( SELECT (:PFROMDT + ROWNUM - 1) A,
TO_CHAR ( :PFROMDT + ROWNUM - 1, 'DY') THEDAY
FROM USER_TAB_COLUMNS
WHERE ROWNUM <= TO_CHAR ( LAST_DAY(:PFROMDT ), 'DD' )
)
WHERE THEDAY = 'SUN'
GROUP BY THEDAY;
RETURN ( LVFLAG );
EXCEPTION
WHEN OTHERS THEN
RETURN ( NULL );
END;
error:
encountered the symbol '('
can u plz help me out.thank u.
siri.
|
|
|
|
| Re: how to capture count of sundays [message #144214 is a reply to message #144182] |
Tue, 25 October 2005 08:45  |
benoitchabot
Messages: 15
Registered: October 2005
Location: Quebec , Canada
|
Junior Member |
|
|
Do like this :
SELECT count(*), theday
FROM
(
SELECT TRUNC (SYSDATE, 'MONTH') + ROWNUM - 1 x,
TO_CHAR (TRUNC (SYSDATE, 'MONTH') + ROWNUM - 1, 'DY') theday
FROM user_tab_columns
WHERE ROWNUM <= TO_CHAR (LAST_DAY (TRUNC(SYSDATE, 'MONTH')), 'DD')
)
WHERE theday = 'SUN'
GROUP BY theday;
|
|
|
|
| Re: how to capture count of sundays [message #144395 is a reply to message #144214] |
Wed, 26 October 2005 06:26 |
sirishay
Messages: 40
Registered: August 2005
Location: Hyderabad
|
Member |
|
|
i have given like that but even then it gives me error.its not taking the select statement in from clause(in the formula column)
i solved it using this query:
SELECT Sum ( Decode ( To_char(:pfromdt+rownum, 'D'), 1, 1, 0)) INTO LVFLAG
FROM USER_TAB_COLUMNS
WHERE ROWNUM <= TO_CHAR ( LAST_DAY ( :PFROMDT ), 'DD' );
i got the count 4 sundays(in september month).
i want to display the dates in my report(only the sundays).i am not getting that.plz help me out.
thanks,
siri.
|
|
|
|
| Re: how to capture count of sundays [message #144460 is a reply to message #144214] |
Wed, 26 October 2005 10:16 |
benoitchabot
Messages: 15
Registered: October 2005
Location: Quebec , Canada
|
Junior Member |
|
|
Hi,
you told that you were using this query in your reports:
SELECT x, theday
FROM (SELECT TRUNC (SYSDATE, 'MONTH') + ROWNUM - 1 x,
TO_CHAR (TRUNC (SYSDATE, 'MONTH') + ROWNUM - 1, 'DY') theday
FROM user_tab_columns
WHERE ROWNUM <= TO_CHAR (LAST_DAY (TRUNC
(SYSDATE, 'MONTH')), 'DD'))
WHERE theday = 'SUN';
NOW, if you add a 1 in your select, like this:
SELECT x, theday, 1 as nb
FROM (SELECT TRUNC (SYSDATE, 'MONTH') + ROWNUM - 1 x,
TO_CHAR (TRUNC (SYSDATE, 'MONTH') + ROWNUM - 1, 'DY') theday
FROM user_tab_columns
WHERE ROWNUM <= TO_CHAR (LAST_DAY (TRUNC
(SYSDATE, 'MONTH')), 'DD'))
WHERE theday = 'SUN';
You should now use a sumarize column instead of a formula column and make the sum of the nb column of your select, and it should work.
|
|
|
|
| Re: how to capture count of sundays [message #144483 is a reply to message #144395] |
Wed, 26 October 2005 14:11 |
sachinjainonweb
Messages: 24
Registered: October 2005
|
Junior Member |
|
|
the problem is due to inline view used in the query which is not supported in version 2.5, change ur query to
select
next_day(to_date('01-'||to_char(sysdate,'MM')||'-'||to_char(sysdate,'RR'),'DD-MM-RR'),'SUNDAY') +7*(rownum-1)
from user_tab_columns
where rownum<=5
and
to_char(next_day(to_date('01-'||to_char(sysdate,'MM')
||'-'||to_char(sysdate,'RR'),'DD-MM-RR'),'SUNDAY') +7*(rownum-1),'mm')=to_char(sysdate,'mm')
|
|
|
|
| Re: how to capture count of sundays [message #144540 is a reply to message #144483] |
Thu, 27 October 2005 00:10 |
sirishay
Messages: 40
Registered: August 2005
Location: Hyderabad
|
Member |
|
|
hai sachin,
thank u very much.i used that query, i am getting that, but when i am using that query in formula column its giving me error.
function CF_2Formula return DATE is
LVFLAG DATE;
begin
SELECT Next_Day(to_date('01-'||to_char(:PFROMDT,'MM')||'-'
||to_char(:PFROMDT,'RR'),'DD-MM-RR'),'SUNDAY') +7*(rownum-1)
INTO Lvflag
FROM User_Tab_Columns
WHERE RowNum <= 5
AND to_char(next_day(to_date('01-'||to_char(:PFROMDT,'MM')||'-'||
to_char(:PFROMDT,'RR'),'DD-MM-RR'),'SUNDAY') +7*(rownum-1),'mm')
= to_char(:PFROMDT,'mm');
RETURN ( LVFLAG );
end;
err: "single row sub-query returns more than one row".
in my report i ahve to retrieve the dates(sundays) from a formula column.......plz tell me how to do that.
thanks
siri.
|
|
|
|
|
|
| Re: how to capture count of sundays [message #144642 is a reply to message #144540] |
Thu, 27 October 2005 09:38 |
sachinjainonweb
Messages: 24
Registered: October 2005
|
Junior Member |
|
|
hi sri
try this function to return dates as CSVs
CREATE OR REPLACE FUNCTION get_sundays (
v_dt DATE
)
RETURN VARCHAR2
IS
t VARCHAR2 (120);
CURSOR c
IS
SELECT DECODE (
TO_CHAR (TRUNC (v_dt, 'mm'), 'dy'),
'sun',
TRUNC (v_dt, 'mm'),
NEXT_DAY (TRUNC (v_dt, 'mm'), 'SUNDAY')
)
+ 7 * (ROWNUM - 1) dt
FROM user_tab_columns
WHERE ROWNUM <= 5
AND TO_CHAR (
DECODE (
TO_CHAR (TRUNC (v_dt, 'mm'), 'dy'),
'sun',
TRUNC (v_dt, 'mm'),
NEXT_DAY (TRUNC (v_dt, 'mm'), 'SUNDAY')
),
'mm'
) = TO_CHAR (v_dt, 'mm');
BEGIN
BEGIN
FOR crec IN c
LOOP
t := t || crec.dt || ',';
END LOOP;
t := SUBSTR (t, 1, LENGTH (t) - 1);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
RETURN t;
END;
Luck Always
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Jun 25 19:32:22 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
