Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - Prakash

Pages: [1]
1
PSOUG / Re: Date
« on: October 07, 2013, 09:57:27 AM »
This is just an idea - it need to be tried on your schema and system. Thinking that you want a grand total (sum) for certain months.

Code: [Select]
SELECT "Grand Total - June 2009 - September 2009", SUM(plan_total)
FROM (SELECT TO_CHAR(c.END_DATE,'MM-YYYY') years,
   SUM (b.plan_total_lcl) plan_total,
   SUM (b.actual_total_LCL) actual_total
   FROM KCST_BUDGET_PERIOD_SUM a,
   KCST_CURRENCY_LINES b,
   KNTA_PERIODS c
   WHERE a.BUDGET_ID = (SELECT z.PRJ_BUDGET_ID FROM KCRT_FG_PFM_PROJECT z WHERE z.REQUEST_ID = 43985)
   AND a.currency_line_id = b.currency_line_id
   AND c.period_id = a.period_id
   GROUP BY TO_CHAR(c.END_DATE,'MM YYYY')
   ORDER BY 1) X
WHERE to_char(Years,'MM-YYYY') between to_char('01-06-2009', 'MM-YYYY') to to_char('01-09-2009','MM-YYYY');

2
PSOUG / Re: Date Question
« on: October 04, 2013, 02:04:41 PM »
/* Formatted on 10/4/2013 4:02:34 PM (QP5 v5.240.12305.39476) */
SELECT CASE
          WHEN TO_CHAR (TO_DATE (SYSDATE, 'dd/mm/yyyy'), 'DAY') = 'MONDAY'
          THEN
             SYSDATE + 4
          WHEN TO_CHAR (TO_DATE (SYSDATE, 'dd/mm/yyyy'), 'DAY') <> 'MONDAY'
          THEN
             SYSDATE - 1
       END
          expected_date
  FROM DUAL;

3
PSOUG / exact fetch returns more than requested number of rows
« on: October 03, 2013, 01:49:11 PM »

-- Example to show error message - "exact fetch returns more than requested number of rows"
Code: [Select]
DECLARE
   x   DUAL.dummy%TYPE;
BEGIN
   SELECT dummy
     INTO x
     FROM DUAL, (SELECT * FROM all_users);
END;

--Solution to correct this is by fetching rows that can be accommodated into the catching variable.
Code: [Select]
DECLARE
   x   DUAL.dummy%TYPE;
BEGIN
   SELECT dummy
     INTO x
     FROM DUAL;
   -- SELECT * FROM all_users; --Can't do this here
END;

-- This error occurs when more number of rows are retrieved and being assigned to a variable
-- that can hold only one single value. You need to retrieve only row(s) that can be held in the
-- catching variable

4
PSOUG / Date Diff Function Not provided by Oracle- Here is one
« on: September 25, 2013, 03:49:31 PM »
Code: [Select]
CREATE OR REPLACE FUNCTION datediff (p_d1 IN DATE, p_d2 IN DATE)
   RETURN NUMBER
AS
   l_result   NUMBER;
BEGIN
   SELECT (p_d2 - p_d1) + 1 INTO l_result FROM DUAL;

   RETURN l_result;
END;

5
PSOUG / sf_week_day_order
« on: September 25, 2013, 03:48:01 PM »
Is there an automated function or procedure that gives the day of the week based on day and sorts the results by week days like SUN- MON - TUE - WED - THU - FRI - SAT.

I wrote this below function that may help return an order for the passed in day

Code: [Select]
CREATE OR REPLACE FUNCTION sf_week_day_order (weekday IN VARCHAR2)
RETURN NUMBER
AUTHID current_user
IS
/******************************************************************************
   NAME:       sf_week_day_order
   PURPOSE:    Function to return the order of the day in a week SUN - WED - SAT

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        9/12/2013   prakash.gumudavelly       1. Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     sf_week_day_order
      Sysdate:         9/12/2013
      Date and Time:   9/12/2013, 10:57:46 AM, and 9/12/2013 10:57:46 AM
      Username:        prakash.gumudavelly (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
   IF weekday = 'SUNDAY'
   THEN
      RETURN 1;
   ELSIF weekday = 'MONDAY'
   THEN
      RETURN 2;
   ELSIF weekday = 'TUESDAY'
   THEN
      RETURN 3;
   ELSIF weekday = 'WEDNESDAY'
   THEN
      RETURN 4;
   ELSIF weekday = 'THURSDAY'
   THEN
      RETURN 5;
   ELSIF weekday = 'FRIDAY'
   THEN
      RETURN 6;
   ELSIF weekday = 'SATURDAY'
   THEN
      RETURN 7;
   END IF;
END sf_week_day_order;

Pages: [1]