Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 9th, 2003, 07:29 AM
Registered User
 
Join Date: Sep 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default QUERY SYNTAX

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


 
Old September 10th, 2003, 07:38 AM
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







Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Query Syntax hugh@kmcnetwork.com SQL Language 1 May 1st, 2007 06:42 AM
Syntax Query digby_dog SQL Server 2000 2 September 8th, 2005 08:37 AM
Query syntax problem mateenmohd Access 2 October 12th, 2004 11:33 PM
MySQL syntax in query Tachyon Beginning PHP 1 June 26th, 2004 08:42 PM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.