Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Posts
    4

    Unanswered: Help on rewriting sql

    Is there a better way on rewriting the sql so that I don't have to join the same table for many times. Please do not use "union".

    select
    ...
    x1.value,
    x2.value,
    x3.value,
    x4.value,
    ..
    x10.value,

    from table-a, table-b, table-c x1, table-c x2, table-c x3, table-cx4...table-c x10
    where ... and x1.refcode = 'xx' and x2.refcode = 'yy' ....

    Thanks for help!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Too little information to say something definitely.
    It will be better to see whole query.

    But, here is an idea which came up in my mind.....
    Code:
    SELECT
           a.col1, a.col2, .....
         , b.colx, b.coly, .....
         , MAX(CASE x.refcode WHEN 'xx' THEN x.value END) AS xx_value
         , MAX(CASE x.refcode WHEN 'yy' THEN x.value END) AS yy_value
           .....
         , MAX(CASE x.refcode WHEN '??' THEN x.value END) AS ??_value
      FROM table-a a
         , table-b b
         , table-c x
     WHERE
           .....
     GROUP BY
           a.col1, a.col2, .....
         , b.colx, b.coly, .....
    ;

  3. #3
    Join Date
    Dec 2009
    Posts
    4
    Below is sql:

    With QAGW59(

    PERMITNUM,

    RPTREPORTFORMONTHYEAR,

    FACILITYNAME,

    FACILITYCOUNTY,

    FACILITYREGION,

    RPTREPORTFORMONTH,

    RPTREPORTFORYEAR,

    C82546,
    M82546,

    C00929,
    M00929,

    C00335,
    M00335,

    C31616,
    M31616,
    C31504,
    M31504,

    wellid,
    CollectedDt,
    ApprOdor,
    DryWell

    ) as
    (

    SELECT

    P.NUM,
    RTRIM(CHAR(RPT.REPORTFORMONTH)) || '-' || CHAR(RPT.REPORTFORYEAR),

    F.Name,

    C.Name,

    ltrim(rtrim(char(r.num))) || '-' ||R.Name,

    RPT.REPORTFORMONTH,

    RPT.REPORTFORYEAR,

    CELL82546.MEASUREMENT,

    case when CELL82546.MEASUREMENTMODIFIERREFCODE = 'LESSTHAN' then '<' when CELL82546.MEASUREMENTMODIFIERREFCODE = 'GRATRTHN' then '>' else '' end,

    CELL00929.MEASUREMENT,

    case when CELL00929.MEASUREMENTMODIFIERREFCODE = 'LESSTHAN' then '<' when CELL00929.MEASUREMENTMODIFIERREFCODE = 'GRATRTHN' then '>' else '' end,

    CELL00335.MEASUREMENT,

    case when CELL00335.MEASUREMENTMODIFIERREFCODE = 'LESSTHAN' then '<' when CELL00335.MEASUREMENTMODIFIERREFCODE = 'GRATRTHN' then '>' else '' end,

    CELL31616.MEASUREMENT,

    case when CELL31616.MEASUREMENTMODIFIERREFCODE = 'LESSTHAN' then '<' when CELL31616.MEASUREMENTMODIFIERREFCODE = 'GRATRTHN' then '>' else '' end,
    CELL31504.MEASUREMENT,

    case when CELL31504.MEASUREMENTMODIFIERREFCODE = 'LESSTHAN' then '<' when CELL31504.MEASUREMENTMODIFIERREFCODE = 'GRATRTHN' then '>' else '' end,

    siteEi.num || ' - '|| pei.num || ' - '||
    case when pei.permitteeSynonym is not null then pei.permitteeSynonym || ' - ' || EI.NUM || ' - ' || EIS.desc || ' - '|| siteEi.name
    else EI.NUM || ' - ' || EIS.desc || ' - '|| siteEi.name end,
    GW59PAGE.SAMPLECOLLECTIONDT,
    GW59PAGE.SAMPLEAPPEARANCEODOR,
    SMRML.NOTDISCHARGING

    FROM ((((((((((((((((
    (((((((((((
    ((((((
    ((((((
    ((((((
    ((((((
    ((((((
    (((((

    Permit P
    left outer join PARAMETER PAR00335 ON PAR00335.REFCODE = 'PAR00335')
    left outer join PARAMETER PAR00929 ON PAR00929.REFCODE = 'PAR00929')
    left outer join PARAMETER PAR82546 ON PAR82546.REFCODE = 'PAR82546')
    left outer join PARAMETER PAR31616 ON PAR31616.REFCODE = 'PAR31616')
    left outer join PARAMETER PAR31504 ON PAR31504.REFCODE = 'PAR31504')
    left outer join PARAMETER PAR70300 ON PAR70300.REFCODE = 'PAR70300')

    left outer join PARAMETER PAR00403 ON PAR00335.REFCODE = 'PAR00403')
    left outer join PARAMETER PAR00680 ON PAR00680.REFCODE = 'PAR00680')
    left outer join PARAMETER PAR00940 ON PAR00940.REFCODE = 'PAR00940')
    left outer join PARAMETER PAR01002 ON PAR01002.REFCODE = 'PAR01002')
    left outer join PARAMETER PAR00552 ON PAR00552.REFCODE = 'PAR00552')
    left outer join PARAMETER PAR32730 ON PAR32730.REFCODE = 'PAR32730')

    left outer join PARAMETER PAR00945 ON PAR00945.REFCODE = 'PAR00945')
    left outer join PARAMETER PAR00095 ON PAR00095.REFCODE = 'PAR00095')
    left outer join PARAMETER PAR00610 ON PAR00610.REFCODE = 'PAR00610')
    left outer join PARAMETER PAR00625 ON PAR00625.REFCODE = 'PAR00625')
    left outer join PARAMETER PAR00615 ON PAR00615.REFCODE = 'PAR00615')
    left outer join PARAMETER PAR00620 ON PAR00620.REFCODE = 'PAR00620')

    left outer join PARAMETER PAR00665 ON PAR00665.REFCODE = 'PAR00665')
    left outer join PARAMETER PAR70507 ON PAR70507.REFCODE = 'PAR70507')
    left outer join PARAMETER PAR01105 ON PAR01105.REFCODE = 'PAR01105')
    left outer join PARAMETER PAR01007 ON PAR01007.REFCODE = 'PAR01007')
    left outer join PARAMETER PAR00916 ON PAR00916.REFCODE = 'PAR00916')
    left outer join PARAMETER PAR01027 ON PAR01027.REFCODE = 'PAR01027')

    left outer join PARAMETER PAR01034 ON PAR01034.REFCODE = 'PAR01034')
    left outer join PARAMETER PAR01042 ON PAR01042.REFCODE = 'PAR01042')
    left outer join PARAMETER PAR01045 ON PAR01045.REFCODE = 'PAR01045')
    left outer join PARAMETER PAR71900 ON PAR71900.REFCODE = 'PAR71900')
    left outer join PARAMETER PAR00937 ON PAR00937.REFCODE = 'PAR00937')
    left outer join PARAMETER PAR00927 ON PAR00927.REFCODE = 'PAR00927')

    left outer join PARAMETER PAR01055 ON PAR01055.REFCODE = 'PAR01055')
    left outer join PARAMETER PAR01051 ON PAR01051.REFCODE = 'PAR01051')
    left outer join PARAMETER PAR01092 ON PAR01092.REFCODE = 'PAR01092')
    left outer join PARAMETER PAR00310 ON PAR00310.REFCODE = 'PAR00310')
    left outer join PARAMETER PAR78120 ON PAR78120.REFCODE = 'PAR78120')
    left outer join PARAMETER PAR38260 ON PAR38260.REFCODE = 'PAR38260')



    left outer join PARAMETER PAR01092 ON PAR01092.REFCODE = 'PAR01092')
    left outer join PARAMETER PAR00310 ON PAR00310.REFCODE = 'PAR00310')
    left outer join PARAMETER PAR78120 ON PAR78120.REFCODE = 'PAR78120')
    left outer join PARAMETER PAR78131 ON PAR78131.REFCODE = 'PAR78131')

    left outer join PARAMETER PAR00530 ON PAR00530.REFCODE = 'PAR00530')
    left outer join PARAMETER PAR01077 ON PAR01077.REFCODE = 'PAR01077')
    left outer join PARAMETER PAR01147 ON PAR01147.REFCODE = 'PAR01147')
    left outer join PARAMETER PAR77835 ON PAR77835.REFCODE = 'PAR77835')

    join Facility F on f.id = p.facilityId)

    join County C on c.id = f.countyId)

    join Region R on r.id = p.AdminRegionId)

    join PERMITENVINT PEI on P.ID = PEI.PERMITID)

    join ENVINT EI on EI.ID = PEI.ENVINTID)
    join EnvIntType eit ON ei.envIntTypeId = eit.id and eit.refcode = 'WELL')

    Join WELL W on W.ID = EI.ID)

    Join SUBMITTEDMONRPT RPT on p.id = RPT.PermitId)

    join MONITORREPORTTYPE MT ON MT.ID = RPT.MONITORRPTTYPEID AND MT.REFCODE = 'GW59')

    left outer join SUBMITTEDMONRPTMONLOC SMRML on RPT.ID = SMRML.SUBMITTEDMONRPTID and pei.id = SMRML.MonitorLocationId)

    left outer join SUBMITTEDMONRPTPAGE PAGE on SMRML.ID = PAGE.SUBMITTEDMONRPTMONLOCID and pei.id=page.monitorlocationid)

    left outer join SUBMITTEDGWMONITORINGREPORTPAGE GW59PAGE on GW59PAGE.id = PAGE.ID)

    left outer join SUBMITTEDMONRPTCOLUMN COL on PAGE.ID = COL.SUBMITTEDMONRPTPAGEID and pei.id=col.monitorlocationid)

    left outer join SUBMITTEDMONRPTCELL CELL00929 on COL.ID = CELL00929.SUBMITTEDMONRPTCOLUMNID and pei.id=cell00929.monitorlocationid AND
    COL.PARAMETERID = PAR00929.ID
    and (CELL00929.MONRPTCELLTYPEREFCODE = 'RPDLYMAX' or (CELL00929.MONRPTCELLTYPEREFCODE is null AND SMRML.NOTDISCHARGING = '1'))
    )
    left outer join SUBMITTEDMONRPTCELL CELL82546 on COL.ID = CELL82546.SUBMITTEDMONRPTCOLUMNID and pei.id=cell82546.monitorlocationid AND
    COL.PARAMETERID = PAR82546.ID
    and (CELL82546.MONRPTCELLTYPEREFCODE = 'RPDLYMAX' or (CELL82546.MONRPTCELLTYPEREFCODE is null AND SMRML.NOTDISCHARGING = '1'))
    )
    left outer join SUBMITTEDMONRPTCELL CELL00335 on COL.ID = CELL82546.SUBMITTEDMONRPTCOLUMNID and pei.id=cell00335.monitorlocationid AND
    COL.PARAMETERID = PAR00335.ID
    and (CELL82546.MONRPTCELLTYPEREFCODE = 'RPDLYMAX' or (CELL82546.MONRPTCELLTYPEREFCODE is null AND SMRML.NOTDISCHARGING = '1'))
    )
    left outer join SUBMITTEDMONRPTCELL CELL31616 on COL.ID = CELL31616.SUBMITTEDMONRPTCOLUMNID and pei.id=cell31616.monitorlocationid AND
    COL.PARAMETERID = PAR31616.ID
    and (CELL31616.MONRPTCELLTYPEREFCODE = 'RPDLYMAX' or (CELL31616.MONRPTCELLTYPEREFCODE is null AND SMRML.NOTDISCHARGING = '1'))
    )
    left outer join SUBMITTEDMONRPTCELL CELL31504 on COL.ID = CELL31504.SUBMITTEDMONRPTCOLUMNID and pei.id=cell31504.monitorlocationid AND
    COL.PARAMETERID = PAR31504.ID
    and (CELL31504.MONRPTCELLTYPEREFCODE = 'RPDLYMAX' or (CELL31504.MONRPTCELLTYPEREFCODE is null AND SMRML.NOTDISCHARGING = '1'))
    )
    --31504
    LEFT OUTER JOIN PermitEnvIntEnvInt peiEi ON pei.id = peiEi.permitEnvIntId AND peiEi.expirationDt is null)
    left outer join EnvInt siteEi ON peiEi.envIntId = siteEi.id )
    left outer join EnvIntStatus EIS ON ei.statusId = EIS.id )

    )

    Select

    PERMITNUM,

    RPTREPORTFORMONTHYEAR,

    FACILITYNAME,

    FACILITYCOUNTY,

    FACILITYREGION,

    RPTREPORTFORMONTH,

    RPTREPORTFORYEAR,
    --1
    sum( case when c82546 is null then 0.0 else c82546 end) as c82546,
    M82546,
    --2
    sum ( case when c00929 is null then 0.0 else c00929 end) as c00929,
    M00929,
    --3
    sum ( case when c00335 is null then 0.0 else c00335 end) as c00335,
    M00335,
    --4) 31616
    sum ( case when c31616 is null then 0.0 else c31616 end) as c31616,
    M31616,
    --5) 31504
    sum ( case when c31504 is null then 0.0 else c31504 end) as c31504,
    M31504,
    WELLID,
    CollectedDt,
    ApprOdor,
    DryWell

    From QAGW59
    where PERMITNUM = 'AWS890001' and RPTREPORTFORMONTHYEAR = '7-2009'
    --where PERMITNUM = 'WQ0017530' and RPTREPORTFORMONTHYEAR = '7-2009'
    --AND WELLID LIKE '%MW1%'

    group by
    PERMITNUM,

    RPTREPORTFORMONTHYEAR,

    FACILITYNAME,

    FACILITYCOUNTY,

    FACILITYREGION,

    RPTREPORTFORMONTH,

    RPTREPORTFORYEAR,

    M82546,

    M00929,

    M00335,
    M31616,
    M31504,
    WELLID,
    CollectedDt,
    ApprOdor,
    DryWell

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow... just wow...

    that is the most awesome, breathtaking, stunning query i have ever seen

    especially this part --
    Quote Originally Posted by john567 View Post
    FROM ((((((((((((((((
    (((((((((((
    ((((((
    ((((((
    ((((((
    ((((((
    ((((((
    (((((
    i am ~so~ totally gobsmacked
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Briefly looked and modified. There must be some syntax errors.

    Code:
    With QAGW59 as (
    SELECT
           P.NUM  AS PERMITNUM
         , RTRIM(CHAR(RPT.REPORTFORMONTH)) || '-' || CHAR(RPT.REPORTFORYEAR)  AS RPTREPORTFORMONTHYEAR
         , F.Name  AS FACILITYNAME
         , C.Name  AS FACILITYCOUNTY
         , ltrim(rtrim(char(r.num))) || '-' ||R.Name  AS FACILITYREGION
         , RPT.REPORTFORMONTH     AS RPTREPORTFORMONTH
         , RPT.REPORTFORYEAR      AS RPTREPORTFORYEAR
    
         , (SELECT CELL.MEASUREMENT
              FROM PARAMETER  P
             WHERE REFCODE = 'PAR82546'
               AND COL.PARAMETERID = P.ID
           )    AS C82546
         , case (SELECT CELL.MEASUREMENTMODIFIERREFCODE
                   FROM PARAMETER  P
                  WHERE REFCODE = 'PAR82546'
                    AND COL.PARAMETERID = P.ID
                )
           when 'LESSTHAN' then '<'
           when 'GRATRTHN' then '>'
           else ''
           end  AS M82546
    
         , (SELECT CELL.MEASUREMENT
              FROM PARAMETER  P
             WHERE REFCODE = 'PAR00929'
               AND COL.PARAMETERID = P.ID
           )    AS C00929
         , case (SELECT CELL.MEASUREMENTMODIFIERREFCODE
                   FROM PARAMETER  P
                  WHERE REFCODE = 'PAR00929'
                    AND COL.PARAMETERID = P.ID
                )
           when 'LESSTHAN' then '<'
           when 'GRATRTHN' then '>'
           else ''
           end  AS M00929
    
         , (SELECT CELL.MEASUREMENT
              FROM PARAMETER  P
             WHERE REFCODE = 'PAR00335'
               AND COL.PARAMETERID = P.ID
           )    AS C00335
         , case (SELECT CELL.MEASUREMENTMODIFIERREFCODE
                   FROM PARAMETER  P
                  WHERE REFCODE = 'PAR00335'
                    AND COL.PARAMETERID = P.ID
                )
           when 'LESSTHAN' then '<'
           when 'GRATRTHN' then '>'
           else ''
           end  AS M00335
    
         , (SELECT CELL.MEASUREMENT
              FROM PARAMETER  P
             WHERE REFCODE = 'PAR31616'
               AND COL.PARAMETERID = P.ID
           )    AS C31616
         , case (SELECT CELL.MEASUREMENTMODIFIERREFCODE
                   FROM PARAMETER  P
                  WHERE REFCODE = 'PAR31616'
                    AND COL.PARAMETERID = P.ID
                )
           when 'LESSTHAN' then '<'
           when 'GRATRTHN' then '>'
           else ''
           end  AS M31616
    
         , (SELECT CELL.MEASUREMENT
              FROM PARAMETER  P
             WHERE REFCODE = 'PAR31504'
               AND COL.PARAMETERID = P.ID
           )    AS C31504
         , case (SELECT CELL.MEASUREMENTMODIFIERREFCODE
                   FROM PARAMETER  P
                  WHERE REFCODE = 'PAR31504'
                    AND COL.PARAMETERID = P.ID
                )
           when 'LESSTHAN' then '<'
           when 'GRATRTHN' then '>'
           else ''
           end  AS M31504
    
         , (SELECT siteEi.num || ' - '|| pei.num || ' - '||
                   COALESCE( pei.permitteeSynonym , '' ) || ' - ' || EI.NUM || ' - ' || EIS.desc || ' - '|| siteEi.name
              FROM
                   PermitEnvIntEnvInt peiEi
              join EnvInt             siteEi
               ON  siteEi.id = peiEi.envIntId
              join EnvIntStatus       EIS
               ON  EIS.id = ei.statusId
             WHERE peiEi.permitEnvIntId = pei.id
               AND peiEi.expirationDt   is null
           )  AS wellid
         , GW59PAGE.SAMPLECOLLECTIONDT    AS CollectedDt
         , GW59PAGE.SAMPLEAPPEARANCEODOR  AS ApprOdor
         , SMRML.NOTDISCHARGING           AS DryWell
    
      FROM
           Permit             P
      join Facility           F
       on  f.id = p.facilityId
      join County             C
       on  c.id = f.countyId
      join Region             R
       on  r.id = p.AdminRegionId
      join PERMITENVINT       PEI
       on  P.ID = PEI.PERMITID
      join ENVINT             EI
       on  EI.ID = PEI.ENVINTID
    
    /* May be replaced by EXISTS */
      join EnvIntType         eit     -- Not in any other clauses
       ON  ei.envIntTypeId = eit.id
       and eit.refcode = 'WELL'
    /* Until here */
    
    /* May be replaced by EXISTS */
      Join WELL               W       -- Not in any other clauses
       on  W.ID = EI.ID
    /* Until here */
    
      Join SUBMITTEDMONRPT    RPT
       on  p.id = RPT.PermitId
    
    /* May be replaced by EXISTS */
      join MONITORREPORTTYPE  MT      -- Not in any other clauses
       ON  MT.ID = RPT.MONITORRPTTYPEID
       AND MT.REFCODE = 'GW59'
    /* Until here */
    
      left outer join
           SUBMITTEDMONRPTMONLOC            SMRML
       on  RPT.ID = SMRML.SUBMITTEDMONRPTID
       and pei.id = SMRML.MonitorLocationId
      left outer join
           SUBMITTEDMONRPTPAGE              PAGE
       on  SMRML.ID = PAGE.SUBMITTEDMONRPTMONLOCID
       and pei.id=page.monitorlocationid
      left outer join
           SUBMITTEDGWMONITORINGREPORTPAGE  GW59PAGE
       on  GW59PAGE.id = PAGE.ID
      left outer join
           SUBMITTEDMONRPTCOLUMN            COL
       on  PAGE.ID = COL.SUBMITTEDMONRPTPAGEID
       and pei.id=col.monitorlocationid
    
      left outer join
           SUBMITTEDMONRPTCELL CELL
       on  COL.ID = CELL.SUBMITTEDMONRPTCOLUMNID
       and pei.id = cell.monitorlocationid
       and (CELL.MONRPTCELLTYPEREFCODE = 'RPDLYMAX'
            or
            CELL.MONRPTCELLTYPEREFCODE is null
            AND SMRML.NOTDISCHARGING = '1'
           )
    
     WHERE
           EXISTS
           (SELECT *
              FROM EnvIntType         eit     -- Not in any other clauses
             WHERE ei.envIntTypeId = eit.id
               and eit.refcode = 'WELL'
           )
       AND EXISTS
           (SELECT *
              FROM MONITORREPORTTYPE  MT      -- Not in any other clauses
             WHERE MT.ID = RPT.MONITORRPTTYPEID
               AND MT.REFCODE = 'GW59'
           )
       AND EXISTS
           (SELECT *
              FROM WELL               W
             WHERE W.ID = EI.ID
           )
    )
    
    Select
           PERMITNUM
         , RPTREPORTFORMONTHYEAR
         , FACILITYNAME
         , FACILITYCOUNTY
         , FACILITYREGION
         , RPTREPORTFORMONTH
         , RPTREPORTFORYEAR
    --1
         , sum( COALESCE( c82546 , 0.0 ) as c82546
         , M82546
    --2
         , sum( COALESCE( c00929 , 0.0 ) as c00929
         , M00929
    --3
         , sum( COALESCE( c00335 , 0.0 ) as c00335
         , M00335
    --4) 31616
         , sum( COALESCE( c31616 , 0.0 ) as c31616
         , M31616
    --5) 31504
         , sum( COALESCE( c31504 , 0.0 ) as c31504
         , M31504
         , WELLID
         , CollectedDt
         , ApprOdor
         , DryWell
    
      From QAGW59
     where PERMITNUM = 'AWS890001'
       and RPTREPORTFORMONTHYEAR = '7-2009'
    --where PERMITNUM = 'WQ0017530' and RPTREPORTFORMONTHYEAR = '7-2009'
    --AND WELLID LIKE '%MW1%'
    
    group by
          PERMITNUM
        , RPTREPORTFORMONTHYEAR
        , FACILITYNAME
        , FACILITYCOUNTY
        , FACILITYREGION
        , RPTREPORTFORMONTH
        , RPTREPORTFORYEAR
        , M82546
        , M00929
        , M00335
        , M31616
        , M31504
        , WELLID
        , CollectedDt
        , ApprOdor
        , DryWell 
    ;

  6. #6
    Join Date
    Dec 2009
    Posts
    4
    I'll use your code to modify the sql.

    Can dba creat a vew to simplify the sql?

    Thanks so much for your help!

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Looking into your query, you would get at most 243(3 power 5) rows for each combination of
    PERMITNUM
    , RPTREPORTFORMONTHYEAR
    , FACILITYNAME
    , FACILITYCOUNTY
    , FACILITYREGION
    , RPTREPORTFORMONTH
    , RPTREPORTFORYEAR
    , WELLID
    , CollectedDt
    , ApprOdor
    , DryWell

    Because, there are three possible values('<', '>' or '') for each of M82546, M00929, M00335, M31616 and M31504.


    My question is:

    a) Do you realy want the result(at most 243(3 power 5) rows for each combinations).

    or

    b) Three rows for each conbinations(One row for each REFCODE). Something like:
    Code:
    REFCODE C82546     C00929     C00335     C31616     C31504
    ------- ---------- ---------- ---------- ---------- ----------
    (blank) xxxxx      xxxxx      xxxxx      xxxxx      xxxxx
    <       xxxxx      xxxxx      xxxxx      xxxxx      xxxxx
    >       xxxxx      xxxxx      xxxxx      xxxxx      xxxxx

  8. #8
    Join Date
    Dec 2009
    Posts
    4
    One row should be returned for a give permit number and month year and parameter. refcode-C82546 == concatenate refcode and Cs (tochar(cs))
    i.e.
    PNUM MonthYear .. refcode-C82546 refcode-C00929 refcode-C00335...
    ------- ---------- ---------- ---------- ---------- -------------------------------
    p1 10-2009 0.23 >1.0 <2.0
    p1 10-2009 >2.0 1.30 4.20
    p1 10-2009 1.0 2.0 0.21

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm sorry. I made mistake.
    Looking into your query, you would get at most 243(3 power 5) rows for each combination of
    .....
    .....
    "243(3 power 5) rows" should be "15(3 multiply 5) rows".

    For example:
    If QAGW59 returned:
    Code:
    SELECT * FROM QAGW59;
    ------------------------------------------------------------------------------
    
    PNUM MONTHYEAR C82546    M82546 C00929    M00929 C00335    M00335
    ---- --------- --------- ------ --------- ------ --------- ------
    p1   10-2009        1.20 <              - -              - -     
    p1   10-2009        2.30 >              - -              - -     
    p1   10-2009        3.45                - -              - -     
    p1   10-2009           - -          12.30 <              - -     
    p1   10-2009           - -          23.40 >              - -     
    p1   10-2009           - -          34.56                - -     
    p1   10-2009           - -              - -         123.40 <     
    p1   10-2009           - -              - -         234.50 >     
    p1   10-2009           - -              - -         345.67       
    
      9 record(s) selected.
    "GROUP BY PNUM, MonthYear, m82546, m00929, m00335" returns 9 rows.
    Code:
    SELECT
           PNUM,   MonthYear
         , m82546 || CHAR( SUM( COALESCE( c82546 , 0.0 ) ) ) AS "refcode-C82546"
         , m00929 || CHAR( SUM( COALESCE( c00929 , 0.0 ) ) ) AS "refcode-C00929"
         , m00335 || CHAR( SUM( COALESCE( c00335 , 0.0 ) ) ) AS "refcode-C00335"
      FROM QAGW59
     GROUP BY
           PNUM, MonthYear
         , m82546, m00929, m00335;
    ------------------------------------------------------------------------------
    
    PNUM MONTHYEAR refcode-C82546                     refcode-C00929                     refcode-C00335                    
    ---- --------- ---------------------------------- ---------------------------------- ----------------------------------
    p1   10-2009   3.45                               -                                  -                                 
    p1   10-2009   <1.20                              -                                  -                                 
    p1   10-2009   >2.30                              -                                  -                                 
    p1   10-2009   -                                  34.56                              -                                 
    p1   10-2009   -                                  <12.30                             -                                 
    p1   10-2009   -                                  >23.40                             -                                 
    p1   10-2009   -                                  -                                  345.67                            
    p1   10-2009   -                                  -                                  <123.40                           
    p1   10-2009   -                                  -                                  >234.50                           
    
      9 record(s) selected.
    Following example returns 3 rows.
    Code:
    SELECT
           PNUM,   MonthYear
         , refcode
         , SUM( CASE WHEN m82546 = refcode THEN c82546 ELSE 0.0 END ) AS C82546
         , SUM( CASE WHEN m00929 = refcode THEN c00929 ELSE 0.0 END ) AS C00929
         , SUM( CASE WHEN m00335 = refcode THEN c00335 ELSE 0.0 END ) AS C00335
      FROM QAGW59
         , (VALUES '', '<', '>' ) AS r(refcode)
     GROUP BY
           PNUM, MonthYear
         , refcode;
    ------------------------------------------------------------------------------
    
    PNUM MONTHYEAR REFCODE C82546                            C00929                            C00335                           
    ---- --------- ------- --------------------------------- --------------------------------- ---------------------------------
    p1   10-2009                                        3.45                             34.56                            345.67
    p1   10-2009   <                                    1.20                             12.30                            123.40
    p1   10-2009   >                                    2.30                             23.40                            234.50
    
      3 record(s) selected.

Posting Permissions

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