| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-11-09, 19:16
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 4
|
|
|
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!
|
|
12-11-09, 20:57
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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, .....
;
|
|
12-11-09, 22:36
|
|
Registered User
|
|
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
|
|
12-12-09, 01:50
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
wow... just wow...
that is the most awesome, breathtaking, stunning query i have ever seen
especially this part --
Quote:
Originally Posted by john567
FROM ((((((((((((((((
(((((((((((
((((((
((((((
((((((
((((((
((((((
(((((
|
i am ~so~ totally gobsmacked
|
|
12-12-09, 02:16
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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
;
|
|
12-12-09, 08:20
|
|
Registered User
|
|
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!
|
|
12-12-09, 10:41
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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
|
|
12-12-09, 23:52
|
|
Registered User
|
|
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
|
|
12-13-09, 07:11
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
I'm sorry. I made mistake.
Quote:
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Help on rewriting sql
