<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=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Webdings;
panose-1:5 3 1 2 1 5 9 6 7 3;}
@font-face
{font-family:"\@MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 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;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:black;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
span.EmailStyle18
{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 Darren,<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">I suspect the problem is caused by the case construct not being allowed within a subselect.<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">You might be better off recasting this query to use session temporary tables created from the subselects.<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">Martin Bowes<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 #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif";mso-fareast-language:JA">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif";mso-fareast-language:JA"> Darren Harvey
[mailto:Darren.Harvey@fusion5.com.au] <br>
<b>Sent:</b> 12 January 2017 05:21<br>
<b>To:</b> info-ingres@lists.planetingres.org<br>
<b>Subject:</b> [Info-ingres] SQL - Using a SELECT within a CASE statement<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black">Hi<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black">I am wanting to use a CASE statement within a SELECT statement, and the case statement has a SUB-SELECT that attempts to use a field selected from the main SELECT. When I try to run it returns an
error indicating the table prefix for the table in the main SELECT is invalid. Hopefully the following screen shots will help explain.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black">This is the SELECT and the problem reference is highlighted.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="mso-fareast-language:EN-AU"><img width="1160" height="146" id="Picture_x0020_1" src="cid:image001.png@01D26CB2.164513D0"></span><span lang="EN-AU" style="color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black">The error received is:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="mso-fareast-language:EN-AU"><img width="440" height="197" id="Picture_x0020_2" src="cid:image002.png@01D26CB2.164513D0"></span><span lang="EN-AU" style="color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black">I am translating this from an MSSQL statement that does work, but I can’t get the Ingres version to work. ( I have tried a simpler version of a CASE statement that does not contain a sub-select and
it works fine.)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black">Hoping someone has some thoughts/suggestions on where the problem is.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black">Thanks<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black">Darren<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:black"><o:p> </o:p></span></p>
<div>
<p class="MsoNormal"><b><span lang="EN-AU" style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#959D14;mso-fareast-language:EN-AU">Darren Harvey | Senior Developer</span></b><span lang="EN-AU" style="font-size:12.0pt;font-family:"Times New Roman","serif";color:black;mso-fareast-language:EN-AU"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><b><span lang="EN-AU" style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#38384E;mso-fareast-language:EN-AU">FUSION5 | Business Solutions<br>
<br>
</span></b><b><span lang="EN-AU" style="font-size:7.5pt;font-family:"Verdana","sans-serif";color:#38384E;mso-fareast-language:EN-AU">M
</span></b><span lang="EN-AU" style="font-size:7.5pt;font-family:"Verdana","sans-serif";color:#38384E;mso-fareast-language:EN-AU">+61 400 398 188 |
<b>P</b> +61 3 9922 5519 | <b>W </b><a href="http://www.fusion5.com.au"><span style="color:olive">www.fusion5.com.au</span></a><br>
Level 16, 60 Albert Road, South Melbourne, Vic 3205, Australia<br>
<br>
</span><span lang="EN-NZ" style="font-size:9.0pt;font-family:"Verdana","sans-serif";color:#38384E"><a href="http://www.fusion5.co.nz/infusion"><b><span style="color:#959D14">Click Here</span></b></a><b>
</b>for our latest Infusion Magazine</span><span lang="EN-NZ" style="font-size:7.5pt;font-family:"Verdana","sans-serif";color:#38384E;mso-fareast-language:EN-AU">
</span><span lang="EN-AU" style="font-size:12.0pt;font-family:"Times New Roman","serif";color:black;mso-fareast-language:EN-AU"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-AU" style="font-size:12.0pt;font-family:"Times New Roman","serif";color:black;mso-fareast-language:EN-AU"> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-AU" style="font-size:7.5pt;font-family:"Arial","sans-serif";color:gray;mso-fareast-language:EN-AU">This email and any attachments are confidential and intended exclusively for the person to whom the email is addressed. If
you are not the intended recipient, do not read, copy, disclose or use the contents in any way. Please notify us immediately by return email and destroy the email and all attachments. Fusion5 accepts no responsibility for any alterations made to this e-mail
(or accompanying documents) after initial transmission. The opinions expressed are not necessarily those of Fusion5. </span><span lang="EN-AU" style="font-size:7.5pt;font-family:"Times New Roman","serif";color:silver;mso-fareast-language:EN-AU">
</span><span lang="EN-AU" style="font-size:12.0pt;font-family:"Times New Roman","serif";color:black;mso-fareast-language:EN-AU"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-AU" style="font-size:12.0pt;font-family:"Times New Roman","serif";color:black;mso-fareast-language:EN-AU"> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-AU" style="font-size:18.0pt;font-family:Webdings;color:#959D14;mso-fareast-language:EN-AU">P</span><span lang="EN-AU" style="font-size:12.0pt;font-family:"Times New Roman","serif";color:#959D14;mso-fareast-language:EN-AU">
</span><span lang="EN-AU" style="font-size:7.5pt;font-family:"Tahoma","sans-serif";color:#959D14;mso-fareast-language:EN-AU">Please consider the environment before printing this email</span><span lang="EN-AU" style="font-size:12.0pt;font-family:"Times New Roman","serif";color:#959D14;mso-fareast-language:EN-AU">
</span><span lang="EN-AU" style="font-size:18.0pt;font-family:Webdings;color:green;mso-fareast-language:EN-AU"><o:p></o:p></span></p>
</div>
<p class="MsoNormal"><span lang="EN-AU" style="font-size:12.0pt;font-family:"Times New Roman","serif";color:black;mso-fareast-language:EN-AU"> </span><span lang="EN-AU"><o:p></o:p></span></p>
</div>
</body>
</html>