Dear All,
Kindly help me to correct the syntax of the query. Also could any one explain why i am not able to see error after i compile this code in Oracle DBA Studio. The error window is always blank(really annoyed with DBA Studio behavior).

-----------------------QUERY BELOW-----------------------------------



PROCEDURE "STATUSTIME" (startDate DATE,endDate DATE)

AS
BEGIN
SELECT
  CASE
    WHEN startDate > yrMth THEN startDate
         ELSE yrMth END || ' - '||
  CASE
    WHEN endDate < LAST_DAY(yrMth)
    THEN endDate
    ELSE LAST_DAY(yrMth) END AS DateofComplaint,
          TotalComplaint AS TotalComplaint
          FROM

              (SELECT TO_DATE('01-'||SUBSTR(TO_DATE(ACTIVITYSTARTTIME, 'DD/MM/YY'),4),'DD/MM/YY') as yrMth,count(CURRENTACTIVITY) AS TotalComplaint
                  FROM TRXCOMPLOG
                  WHERE ACTIVITYSTARTTIME BETWEEN TO_DATE('21/06/2003','DD/MM/YY') AND TO_DATE('21/12/2003','DD/MM/YY')
                  AND CURRENTACTIVITY='new'
                  group by TO_DATE('01-'||SUBSTR(TO_DATE(ACTIVITYSTARTTIME, 'DD/MM/YY'),4),'DD/MM/YY'));

END STATUSTIME;

-----------------------------------------------------------


Any help shall be much appreciated

Regards,
Ginni

Hello All

My result set of inner query is

-Present result set(inner que)--- ---required result set after using(case when)---
1/08/2003 2 21/08/2003-31/08/2003 2
1/09/2003 1 1/09/2003-30/09/2003 1
1/10/2003 2 1/10/2003-21/10/2003 2
                                                          Total 5


I am just trying to convert the Present resultset into the required result as above,

In the procedure the startdate and enddate are variables which in above case carries values 21/08/2003 and 21/10/2003(any value can do)

WORKING CODE
Here is the final WORKING code BUT IT IS TOO CUMBERSOME
1) The inner query contains too many || for concatting and
2) Is there any other possible way to write the the same code without using execute immediate and CASE ?
3) is it possible to get that total at the end ?

---------------------------------------------------------------------------------------------------------
(startDate DATE,endDate DATE)
AS
 type mycursor is ref cursor;
 mycur mycursor;
 strsql varchar2(1000);
BEGIN
strsql := 'SELECT
    CASE
    WHEN startDate > yrMth THEN startDate
        ELSE yrMth END|| '-'||
   CASE
   WHEN endDate < LAST_DAY(yrMth)
   THEN endDate
   ELSE LAST_DAY(yrMth) END AS DateofComplaint,
         TotalComplaint AS TotalComplaint


        FROM

        (SELECT TRUNC(ACTIVITYSTARTTIME, ' || 'MM' ||') as yrMth, count(CURRENTACTIVITY) AS TotalComplaint
         FROM TRXCOMPLOG
         WHERE ACTIVITYSTARTTIME BETWEEN startDate AND endDate
         AND CURRENTACTIVITY='||'new'||'
         group by TRUNC(ACTIVITYSTARTTIME,'||'MM'||'))';

execute immediate strsql;
END STATUSTIME;
--------------------------------------------------------------------------------------------------------------------------------------------

Can anyone help me to solve the above mentioned 3 problems

Regards,
Ginni