This discussion is archived
6 Replies Latest reply: Jan 3, 2013 6:13 AM by pallis RSS

Looking for Query..

pallis Newbie
Currently Being Moderated
sno name price date
1 a 200 jan 2012
2 b 300 feb 2012
3 c 400 sep 2012
4 d 260 dec 2012

Output need:

sno jan feb mar apr may jun jul aug sep oct nov dec
1 200
2 300
3 400
4 260


I tried query like this. But for every month i have to write decode. Instead of this is there any alternative way. I need to design in Oracle Reports 6i.

SELECT sno,
MAX(DECODE(date,jan, price))Jan,
MAX(DECODE(date,feb, price))feb,
MAX(DECODE(date,apr, price))mar,
-----
-----
MAX(DECODE(date,dec, price))dec
FROM PRODUCTS

Is ther any other way to write this sql
DB:9i

Thanks & Regards
pallis
  • 1. Re: Looking for Query..
    ranit B Expert
    Currently Being Moderated
    --Go for PIVOTing--

    Sorry, my mistake... i didn't notice that.

    Then i guess either DECODE or using CASE with MAX (or SUM) should work.
    But the basic idea remains the same, what you already have.

    Edited by: ranit B on Jan 3, 2013 5:31 PM
  • 2. Re: Looking for Query..
    pallis Newbie
    Currently Being Moderated
    Im using oracle 9im d2k reports 6i.
  • 3. Re: Looking for Query..
    Karthick_Arp Guru
    Currently Being Moderated
    I tried query like this. But for every month i have to write decode. Instead of this is there any alternative way. I need to design in Oracle Reports 6i.
    Thas correct the only way is to write decode for every month. There are only 12 months must not be a big trouble :)
  • 4. Re: Looking for Query..
    pallis Newbie
    Currently Being Moderated
    Can u help me how to achive in d2K reports 6i
  • 5. Re: Looking for Query..
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Pallis,
    pallis wrote:
    sno name price date
    1 a 200 jan 2012
    2 b 300 feb 2012
    3 c 400 sep 2012
    4 d 260 dec 2012
    Always post CREATE TABLE and INSERT statements for your sample data.
    See the forum FAQ {message:id=9360002}
    Output need:

    sno jan feb mar apr may jun jul aug sep oct nov dec
    1 200
    2 300
    3 400
    4 260
    That looks like the feb-dec columns are always NULL. Is that what you meant, or did you mean something like this:
    sno   jan   feb   mar   apr   may   jun   jul   aug   sep   oct   nov   dec
      1   200                                  
      2         300
      3                                             400
      4                                                                     260
    ? The same forum FAQ page explains how to use \
     tags to post formatted text on this site. 
    
    I tried query like this. But for every month i have to write decode. Instead of this is there any alternative way. I need to design in Oracle Reports 6i.

    SELECT sno,
    MAX(DECODE(date,jan, price))Jan,
    MAX(DECODE(date,feb, price))feb,
    MAX(DECODE(date,apr, price))mar,
    -----
    -----
    MAX(DECODE(date,dec, price))dec
    FROM PRODUCTS
    DATE is not a good column name. How are you defining variables such as jan that are used in the DECODE expressions?  You can use the SYSDATE and ADD_MONTHS functions to dynamically set them relative to today's date.  For the column aliases, you may need dynamic SQL.  (In SQL*Plus, this is fairly easy, using substitution variables.)  When you post the sample data, give a couple of different examples of output you would want from the exact same data.  E.g. "If I run the query today, or any time in January 2013, then I should get this output: ...  but if I run it in February 2013, then I want ..."
    Is ther any other way to write this sql
    Yes, therres another forum FAQ page on this topic. {message:id=9360005} Do you need 12 separate columns for the months, or would you accept one big string column, formatted to look like 12 columns?  String aggregation (which does *not* require dynamic SQL) might be an option.
    DB:9i
    Are you saying you're database version is Oracle 9?  What is the actual version number, e.g. 9.2.0.6.0? I don't know if Oracle Reports can pivot data.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 6. Re: Looking for Query..
    pallis Newbie
    Currently Being Moderated
    @ Frank I typed like that but i don't know how it changed.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points