Re: need help in framing a query.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 11 Oct 2006 04:10:24 -0700
Message-ID: <1160565024.841616.66740@e3g2000cwe.googlegroups.com>

bernard (bernard_at_bosvark.com) wrote:
> On Oct 11, 11:23 am, "Giridhar" <gkodaka..._at_gmail.com> wrote:
> > my requirement is like this. I need to get data in this format.
> > we need to prepare a string for 12 months of a year.
> ...
> > CSP_NUM PYBL_VALUE
> > 459200101 100|200|-|-|-|-|-|-|900|-|-|-|
>
> 12 Decodes and a read though
> http://asktom.oracle.com/pls/ask/f?p=4950:8:10251345418031856797::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:124812348063
>
> Regards
> Bernard

Good suggestion. Although, that is a pretty long discussion thread on asktom. Exploring the suggestion by Bernard, the starting point: SELECT
  CSP_NUM,

  DECODE(PYBL_MTH,1,CF_AMOUNT) M1,
  DECODE(PYBL_MTH,2,CF_AMOUNT) M2,
  DECODE(PYBL_MTH,3,CF_AMOUNT) M3,
  DECODE(PYBL_MTH,4,CF_AMOUNT) M4,
  DECODE(PYBL_MTH,5,CF_AMOUNT) M5,
  DECODE(PYBL_MTH,6,CF_AMOUNT) M6,
  DECODE(PYBL_MTH,7,CF_AMOUNT) M7,
  DECODE(PYBL_MTH,8,CF_AMOUNT) M8,
  DECODE(PYBL_MTH,9,CF_AMOUNT) M9,
  DECODE(PYBL_MTH,10,CF_AMOUNT) M10,
  DECODE(PYBL_MTH,11,CF_AMOUNT) M11,
  DECODE(PYBL_MTH,12,CF_AMOUNT) M12

FROM
  KGR; The above generates the appearance of 12 columns based on the value of the original PYBL_MTH column. Now, this result must be collapsed into a single row per CSP_NUM, rather than up to 12 rows per CSP_NUM. This can be accomplished by modifying the SQL statement to use GROUP BY, like this:
SELECT
  CSP_NUM,

  MAX(DECODE(PYBL_MTH,1,CF_AMOUNT)) M1,
  MAX(DECODE(PYBL_MTH,2,CF_AMOUNT)) M2,
  MAX(DECODE(PYBL_MTH,3,CF_AMOUNT)) M3,
  MAX(DECODE(PYBL_MTH,4,CF_AMOUNT)) M4,
  MAX(DECODE(PYBL_MTH,5,CF_AMOUNT)) M5,
  MAX(DECODE(PYBL_MTH,6,CF_AMOUNT)) M6,
  MAX(DECODE(PYBL_MTH,7,CF_AMOUNT)) M7,
  MAX(DECODE(PYBL_MTH,8,CF_AMOUNT)) M8,
  MAX(DECODE(PYBL_MTH,9,CF_AMOUNT)) M9,
  MAX(DECODE(PYBL_MTH,10,CF_AMOUNT)) M10,
  MAX(DECODE(PYBL_MTH,11,CF_AMOUNT)) M11,
  MAX(DECODE(PYBL_MTH,12,CF_AMOUNT)) M12

FROM
  KGR
GROUP BY
  CSP_NUM; To finish this exercise, you will need to perform a TO_CHAR operation on each of the 12 generated columns, detect when a value for a column does not exist, and join the 12 columns into a single column using ||

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Oct 11 2006 - 06:10:24 CDT