[Info-ingres] SQL - Using a SELECT within a CASE statement

[Martin Bowes]

Hi Darren,

I suspect the problem is caused by the case construct not being allowed within a subselect.

You might be better off recasting this query to use session temporary tables created from the subselects.

Martin Bowes

From: Darren Harvey [mailto:Darren.Harvey at fusion5.com.au]
Sent: 12 January 2017 05:21
To: info-ingres at lists.planetingres.org
Subject: [Info-ingres] SQL - Using a SELECT within a CASE statement

Hi

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.

This is the SELECT and the problem reference is highlighted.

[cid:image001.png at 01D26CB2.164513D0]

The error received is:
[cid:image002.png at 01D26CB2.164513D0]

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.)

Hoping someone has some thoughts/suggestions on where the problem is.

Thanks

Darren


Darren Harvey | Senior Developer
FUSION5 | Business Solutions

M +61 400 398 188 | P +61 3 9922 5519 | W www.fusion5.com.au<http://www.fusion5.com.au>
Level 16, 60 Albert Road, South Melbourne, Vic 3205, Australia

Click Here<http://www.fusion5.co.nz/infusion> for our latest Infusion Magazine

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.

P  Please consider the environment before printing this email

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20170112/a0bddb7a/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15681 bytes
Desc: image001.png
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20170112/a0bddb7a/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 9240 bytes
Desc: image002.png
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20170112/a0bddb7a/attachment-0001.png>