Hi all,
I am using the LIST in a subselect but not getting the expected result.

In the example below the 'RegionTerritoriesDescription' is empty.


SELECT *
FROM Region
WHERE RegionID =  1

returns:
RegionID | RegionDescription
           1  | Eastern


SELECT LIST(TerritoryDescription, ' / ')
FROM Territories
WHERE RegionID = 1

returns:
TerritoryDescription |
Westboro / Bedford / Georgetow / Boston / Cambridge / Braintree / Providence / Wilton / Morristown / Edison / New York / New York / Mellvile / Fairport / Neward / Rockville / Greensboro / Cary / Louisville


------ HOWEVER ------

SELECT *,
(SELECT LIST(TerritoryDescription, ' / ')
FROM Territories
WHERE RegionID = Region.RegionID) AS RegionTerritoriesDescription
FROM Region
WHERE RegionID =  1

returns:

RegionID | RegionDescription  | RegionTerritoriesDescription
           1  | Eastern                   |


:'( No RegionTerritoriesDescription is returned

........................................................
PascalNetwork.com
pascal - the language we love

Trinione


Without the data to try it my guess would be its getting confused with

WHERE RegionID = Region.RegionID

try altering to

WHERE Territories.RegionID = Region.RegionID

If that doesn't work can you post or email the table create script (just drag the table to a new sql page in edbmanger) including data and I'll have a play.

Roy Lambert

Roy Lambert wrote:

If that doesn't work can you post or email the table create script (just drag the table to a new sql page in edbmanger) including data and I'll have a play.
---------------------------------------------------------------------------------------

Roy,
Rather than 2 messages I am pasting the Regions table in here, and have attached the Territories table.

Thx
TriniOne
-------------



CREATE TABLE "Region"
(
"RegionID" INTEGER,
"RegionDescription" VARCHAR(50) COLLATE "UNI",
CONSTRAINT "PK_RegionID" UNIQUE ("RegionID")
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768!

INSERT INTO "Region" VALUES (1,
                 'Eastern')!

INSERT INTO "Region" VALUES (2,
                 'Western')!

INSERT INTO "Region" VALUES (3,
                 'Northern')!

INSERT INTO "Region" VALUES (4,
                 'Southern')!

<< :'( No RegionTerritoriesDescription is returned >>

I'm seeing one row with these values (using the data that you posted):

"RegionID"   "RegionDescription"   "RegionTerritoriesDescription"
1   "Eastern"   "Westboro / Bedford / Georgetow / Boston / Cambridge / Braintree / Providence / Wilton / Morristown / Edison / New York / New York / Mellvile / Fairport / Neward / Rockville / Greensboro / Cary / Louisville"

Is it possible that you've got a NULL value in one of your TerritoryDescription row columns that isn't the test data that you shared ?  That would cause the resultant expression to be NULL.

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
Is it possible that you've got a NULL value in one of your TerritoryDescription row columns that isn't the test data that you shared ?  That would cause the resultant expression to be NULL.
--------------------------------

Tim,
I dragged the table onto an empty SQL window to create the file/s I sent above. Going to look thru all the records now..... Nope. All records exist in both tables.

This is really strange.  I am running 2.31 Build 10 - Elevate DB Server (Win64).

........................................................
PascalNetwork.com
pascal - the language we love

Attached is an animated gif of the issue.

<< I dragged the table onto an empty SQL window to create the file/s I sent above. Going to look thru all the records now..... Nope. All records exist in both tables. >>

Hmm, I'm not sure what's going on.  The SQL that I'm running is this:

SELECT *,
(SELECT LIST(TerritoryDescription, ' / ')
FROM Territories
WHERE RegionID = Region.RegionID) AS RegionTerritoriesDescription
FROM Region
WHERE RegionID =  1

using the data that you posted with 2.31 B10.

Tim Young
Elevate Software
www.elevatesoft.com

Never mind - I was accidentally using an older build.

This looks like a bug, but I'll know more in a few minutes.

Tim Young
Elevate Software
www.elevatesoft.com

Yep! That's the exact code. I just copied from this screen and pasted it in and ran. Nothing. Nada. Zilch.

And, this happens on other tables/queries also. This is just a test database I am using.

I shall uninstall via the Control Panel | Applications and then reinstall and hope for the best.

Shall report back.

........................................................
PascalNetwork.com
pascal - the language we love

Tim,
Oh! Ok. Was typing at the same time, just saw your last message.

Let me know por favor.

........................................................
PascalNetwork.com
pascal - the language we love