Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: How to add columns in decode statement

    Hello All,

    I am really not very expert in SQL and I am stuck with one query. I want to add more than one colums in my decode for e.g can I do sum of more than 1 activity in my decode statement


    SELECT OID,ACTIVITY_DATE,PLATFORM,

    SUM(DECODE(WEB.OID 35,WEB.TOTAL_COUNT,0)) AS PROFILECOUNT,

    SUM(DECODE(WEB.OID,119,WEB.TOTAL_COUNT,0)) AS RECIPEVIEW_COUNT,


    FROM WEB_ACTIVITY
    WHERE MARKETING_GROUP = 'BC'
    group by OID MARKETING_GROUP


    Can I do something like:

    SUM(DECODE(WEB.OID 35+36+37,WEB.TOTAL_COUNT,0)) AS PROFILECOUNT

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hello,

    you might be surprised, but DECODE function has its own syntax which you should follow. Fortunately it is described in SQL Language Reference book, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
    Please, consult the one for your Oracle version.

    It looks like you want something like this:
    Code:
    SUM(DECODE(WEB.OID, 35,WEB.TOTAL_COUNT,
                        36,WEB.TOTAL_COUNT,
                        37,WEB.TOTAL_COUNT, 0)) AS PROFILECOUNT
    Alternatively you may use CASE expression. It is described in the same book.

  3. #3
    Join Date
    Apr 2012
    Posts
    4
    Thanks!! I will try that!! in the same query I have to show the month of the Activity_date and I tried
    TO_char(ACTIVITY_DATE_NUM,'MONTH,YYYY') as MONTH_DATE

    But since The activity_date is in number format , it is giving me error? What else I can try??

    Thank you so much for your time!!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what datatype is ACTIVITY_DATE_NUM?

    what is content of ACTIVITY_DATE_NUM?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2012
    Posts
    4
    Activity_date_num has format like 20110904 and the data type is number which is making me more confused and I have to just show the month and year.


    Thanks a bunch!!

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Activity_date_num has format like 20110904

    so which month "09" or "04"?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    TO_CHAR(TO_DATE(to_char(activity_date_num,'9999999 9'),'YYYYMMDD'),'MONTH,YYYY') MONTH_DATE
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

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