Results 1 to 7 of 7

Thread: decode

  1. #1
    Join Date
    Jan 2005
    Posts
    362

    Unanswered: decode

    I have a table with a field "period" as varchar that represent a mont (042006=04/2006).
    I want to use the decode function in a sql statement and to return some values from a field of this table that the period field is betwen 2 values (042006 and 012006 for example).
    How can I do it?
    Basically I want a way to find if that field (period) belong to a period of a 3 month or to the previous 3 months period.
    Dimis
    Last edited by dimis2500; 03-09-07 at 06:33.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As I understand it, DECODE has nothing to do with your question. BETWEEN might be a better choice, for example
    Code:
    SELECT * FROM your_table
    WHERE TO_DATE(period, 'mmyyyy') BETWEEN TO_DATE('042006', 'mmyyyy')
                                        AND TO_DATE('122006', 'mmyyyy');
    By the way, storing dates in a CHARACTER column is a bad idea. Why don't you keep dates in DATE columns?

    Finally, if code I suggested isn't what you are looking for, could you explain what you meant by "using the DECODE function"? How would you like to use it, where and why?

  3. #3
    Join Date
    Jan 2005
    Posts
    362

    Dates

    I use oracle reports and we want some sums within a big query.
    The sums are for these 3 months and the previous 3 moths.
    I tried to seperate the querys for the sumes but I want a field with has a sum of the results of both sums (previous months and now)and the type function i make did not work (the sums are at separate qyerys), so I thing I have to find these sums at one query.
    Meaning at the select satenent to find the right sums for the right periods.
    Code:
    select ....
    --If period betwen 012006 and 032006 sum(...) as old
    --If period betwen 042006 and 072006 sum(...) as new
    ....
    from....

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see; I'd rather suggest you to use CASE statement instead of DECODE - it is much easier to write it, understand and maintain. Although the same can be done using the DECODE function, it is really painful for even a little bit more complicated situations.

    Your query might look like this:
    Code:
    SELECT 
      CASE
        WHEN period BETWEEN  1 AND 10 THEN SUM(col1)
        ELSE 0
      END OLD,
      CASE
        WHEN period BETWEEN 11 AND 20 THEN SUM(col2)
        ELSE 0
      END NEW
    FROM ...
    Also, what about your date values in a character column? It is very likely to bring you problems, sooner or later.

  5. #5
    Join Date
    Jan 2005
    Posts
    362

    select case

    I use this 'case when', the query wants a group by period and the report show this
    Code:
    name  sum
    test    20
    test    10
    test     08
    else     23
    -----
    Maybe the group by return each field with the same period, how can I solve this and return the right?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    do it like this

    S
    Code:
    ELECT 
      sum(CASE
          WHEN period BETWEEN  1 AND 10 THEN col1
           ELSE 0
           END) OLD,
      sum( CASE
        WHEN period BETWEEN 11 AND 20 THEN col2
        ELSE 0
      END) NEW
    FROM ..
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Feb 2007
    Posts
    3

    Using DECODE

    I think the other suggestions are better, but this will use DECODE to return a value of 'T' if the month in period is in the 4 to 12 range, returns 'F' otherwise

    Code:
    SELECT DECODE(TO_CHAR(TO_DATE(period,'MMRRRR'),'MM')
    ,1,'F',2,'F',3,'F',4,'T',5,'T',6,'T',7,'T',8,'T',9,'T',10,'T',11,'T',12,'T') FROM dual

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •