Thread: QUERY SYNTAX
View Single Post
 
Old September 10th, 2003, 07:38 AM
singh_ginni singh_ginni is offline
Registered User
 
Join Date: Sep 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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