Hi All,
Am I right in thinking the WHERE clause only applies to base tables and not to pivoted tables, further, it is applied before the pivoting occurs?
And that the HAVING clause is applied to each implicit GROUP in all the table pivots in the query. Presumably applied after the pivoting and as a restriction on the final row set produced.
This is in contrast to say the analytic functions ORDER BY clause which is scoped to the PARTITION specified and applied before any overall ORDER BY on the result set.
Any takers?
Marty
From: Martin Bowes
Sent: 22 February 2021 09:56
To:
info-ingres@lists.planetingres.org
Subject: Pivot and WHERE / HAVING restrictions
Hi All,
Can someone explain the scope of the WHERE and HAVING restrictions in the PIVOT() statement to me.
Given...
DECLARE GLOBAL TEMPORARY TABLE session.p(
pid integer4,
field_id integer4,
value integer4
) ON COMMIT PRESERVE ROWS WITH NORECOVERY;
INSERT INTO session.p VALUES
(1, 100, 17),
(1, 101, 18),
(1, 102, 8),
(2, 100, 10),
(2, 101, 9),
(2, 102, 7);
Then:
SELECT pid, pv.*
FROM session.p PIVOT (MAX(value) FOR field_id IN (100, 101, 102)) AS pv
ORDER BY pid;
‚€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ƒ
pid 100 101 102 †€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€‡
1 17 18 8
2 10 9 7 „€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€…
And:
/* Add WHERE clause restriction on base table.
** We still get two rows.
*/
SELECT pid, pv.*
FROM session.p PIVOT (MAX(value) FOR field_id IN (100, 101, 102)) AS pv
WHERE value > 15
ORDER BY pid;
‚€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ƒ
pid 100 101 102 †€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€‡
1 17 18
2 „€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€…
/* And a WHERE clause retriction on PIVOT table.
** This will produce: E_PS031C
*/
SELECT pid, pv.*
FROM session.p PIVOT (MAX(value) FOR field_id IN (100, 101, 102)) AS pv
WHERE pv."100" > 15
ORDER BY pid;
Executing . . .
E_PS031C Pivot column 'pv.100' can only be referenced from HAVING clause
or target list.
(Mon Feb 22 09:54:02 2021)
/* And a HAVING clause restriction on the PIVOT table.
** But now we get rows removed.
*/
SELECT pid, pv.*
FROM session.p PIVOT (MAX(value) FOR field_id IN (100, 101, 102)) AS pv
HAVING pv."100" > 15
ORDER BY pid;
‚€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ƒ
pid 100 101 102 †€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€‡
1 17 18 8 „€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€…
<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="
http://schemas.microsoft.com/office/2004/12/omml" xmlns="
http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=koi8-r">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)"> <style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Lucida Console";
panose-1:2 11 6 9 4 5 4 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">Hi All,<o:p></o:p></span></p> <p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="color:#1F497D">Am I right in thinking the WHERE clause only applies to base tables and not to pivoted tables, further, it is applied before the pivoting occurs?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="color:#1F497D">And that the HAVING clause is applied to each implicit GROUP in all the table pivots in the query. Presumably applied after the pivoting and as a restriction on the final row set produced.<o:p></o:p></span>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="color:#1F497D">This is in contrast to say the analytic functions ORDER BY clause which is scoped to the PARTITION specified and applied before any overall ORDER BY on the result set.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="color:#1F497D">Any takers?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="color:#1F497D">Marty<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="mso-fareast-language:EN-GB">From:</span></b><span lang="EN-US" style="mso-fareast-language:EN-GB"> Martin Bowes
<b>Sent:</b> 22 February 2021 09:56<br>
<b>To:</b>
info-ingres@lists.planetingres.org<br>
<b>Subject:</b> Pivot and WHERE / HAVING restrictions<o:p></o:p></span></p> </div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Hi All,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Can someone explain the scope of the WHERE and HAVING restrictions in the PIVOT() statement to me.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Given…<o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">DECLARE GLOBAL TEMPORARY TABLE session.p(<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> pid integer4,<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> field_id integer4,<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> value integer4<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">) ON COMMIT PRESERVE ROWS WITH NORECOVERY;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">INSERT INTO session.p VALUES<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> (1, 100, 17),<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> (1, 101, 18),<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> (1, 102, 8),<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> (2, 100, 10),<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> (2, 101, 9),<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> (2, 102, 7);<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal">Then:<o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">SELECT pid, pv.*<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">FROM session.p PIVOT (MAX(value) FOR field_id IN (100, 101, 102)) AS pv<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">ORDER BY pid;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">‚€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ƒ<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">pid 100 101 &
nbsp; 102 <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">†€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€‡<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> 1 17&
nbsp; 18 8<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> 2 10&
nbsp; 9 7<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">„€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€…<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">And:<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">/* Add WHERE clause restriction on base table.<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">** We still get two rows.<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">*/<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">SELECT pid, pv.*<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">FROM session.p PIVOT (MAX(value) FOR field_id IN (100, 101, 102)) AS pv<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">WHERE value > 15<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">ORDER BY pid;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">‚€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ƒ<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">pid 100 101 &
nbsp; 102 <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">†€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€‡<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> 1 17&
nbsp; 18 <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> 2
<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">„€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€…<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">/* And a WHERE clause retriction on PIVOT table.<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">** This will produce: E_PS031C<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">*/<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">SELECT pid, pv.*<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">FROM session.p PIVOT (MAX(value) FOR field_id IN (100, 101, 102)) AS pv<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">WHERE pv."100" > 15<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">ORDER BY pid;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">Executing . . .<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">E_PS031C Pivot column 'pv.100' can only be referenced from HAVING clause<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> or target list.<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> (Mon Feb 22 09:54:02 2021)<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">/* And a HAVING clause restriction on the PIVOT table.<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">** But now we get rows removed.<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">*/<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">SELECT pid, pv.*<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">FROM session.p PIVOT (MAX(value) FOR field_id IN (100, 101, 102)) AS pv<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">HAVING pv."100" > 15<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">ORDER BY pid;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">‚€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ƒ<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">pid 100 101 &
nbsp; 102 <o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">†€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€‡<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> 1 17&
nbsp; 18 8<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">„€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€…<o:p></o:p></span></p>
</div>
</body>
</html>
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)