Hi there,

Firstly, I hope this is in the right place!

I have a problem with a nested query I am generating at runtime.

My table (tblIndexValue) has values in every cell in all rows including the 
createdUserID column. The createdUserID's datatype is a GUID, and the table 
is in Access using Jet 4 to connect from .NET.

Here is the query, as captured in the immediate window: -

SELECT DocumentID, 
(SELECT value FROM tblIndexValue WHERE 
indexFieldID={7b47a555-9a8e-4336-acbf-12b170d46f71} AND 
departmentID={f7d75dd6-096c-43e1-9f25-285fbe74a547} AND 
DocumentID=Q.DocumentID) AS IV1, 
(SELECT createdUserID FROM tblIndexValue WHERE 
indexFieldID={7b47a555-9a8e-4336-acbf-12b170d46f71} AND 
departmentID={f7d75dd6-096c-43e1-9f25-285fbe74a547} AND 
DocumentID=Q.DocumentID) AS IVID1
FROM tblDocument AS Q 
GROUP BY Q.DocumentID;

IV1, i.e. the value field (a text value) displays fine. However, IVID1, the 
createdUserID file (a GUID) only shows NULLS. If I run the inner query on 
it's own (i.e. SELECT createdUserID FROM .... substituting the Q.DocumentID 
for any of my documentID GUIDs) it displays as expected.

Have I missed something here?

Many thanks,

David

Hello David,

It seems you meet an issue that Jet 4 provider could not get the GUID type 
filed in a Nested Query.

I have reproduced this issue on my side.

Under my investigation, I'm afraid this is an issue in Jet 4 rather than 
.net framework, because the same issue also occurs if I execute this sub 
query in Access 2003 directly. If I change the select sub query slight, the 
GUID really could be retrieved by Jet provider. But the type has been 
changed to String. Thus, I'm afraid this is an issue in the Jet 4 
implementation.
SELECT DocumentID, 
(SELECT ''+createdUserID 
FROM tblIndexValue WHERE DocumentID=Q.DocumentID) AS IVID1
FROM tblDocument AS Q 
GROUP BY Q.DocumentID;

Anyway, for you special issue, I don't think it is necessary to use Nested 
Query here. There is another approach, what about change it as below? In my 
opnion, the following code is clear and more effective. Hope this helps.

SELECT Q.DocumentID, T.tblIndexValue as IV1, T.createdUserID as TVID1
FROM tblDocument AS Q, tblIndexValue as T
where T.indexFieldID={7b47a555-9a8e-4336-acbf-12b170d46f71} AND 
T.departmentID={f7d75dd6-096c-43e1-9f25-285fbe74a547} AND 
T.DocumentID=Q.DocumentID

Please feel free to update here, If you have more concern. We are glad to 
assist you.
Have a great day, 
Sincerely,
Wen Yuan
Microsoft Online Community Support 
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Thank you Wen for an answer that works. The string conversion is great. 

The query I provided was seriosuly truncated for ease of reading here. 
Actually, it pulls back 'n' inner queries which require a table rotate. The 
number of inner queries vary depending on the number of fields the user has 
setup.

The ''+createUserID solves it for me, and I can convert the string to a GUID 
in code. Thanks again for such a quick response that works!

David

"WenYuan Wang [MSFT]" wrote:


> Hello David,
> 
> It seems you meet an issue that Jet 4 provider could not get the GUID type 
> filed in a Nested Query.
> 
> I have reproduced this issue on my side.
> 
> Under my investigation, I'm afraid this is an issue in Jet 4 rather than 
> .net framework, because the same issue also occurs if I execute this sub 
> query in Access 2003 directly. If I change the select sub query slight, the 
> GUID really could be retrieved by Jet provider. But the type has been 
> changed to String. Thus, I'm afraid this is an issue in the Jet 4 
> implementation.
> SELECT DocumentID, 
> (SELECT ''+createdUserID 
> FROM tblIndexValue WHERE DocumentID=Q.DocumentID) AS IVID1
> FROM tblDocument AS Q 
> GROUP BY Q.DocumentID;
> 
> Anyway, for you special issue, I don't think it is necessary to use Nested 
> Query here. There is another approach, what about change it as below? In my 
> opnion, the following code is clear and more effective. Hope this helps.
> 
> SELECT Q.DocumentID, T.tblIndexValue as IV1, T.createdUserID as TVID1
> FROM tblDocument AS Q, tblIndexValue as T
> where T.indexFieldID={7b47a555-9a8e-4336-acbf-12b170d46f71} AND 
> T.departmentID={f7d75dd6-096c-43e1-9f25-285fbe74a547} AND 
> T.DocumentID=Q.DocumentID
> 
> Please feel free to update here, If you have more concern. We are glad to 
> assist you.
> Have a great day, 
> Sincerely,
> Wen Yuan
> Microsoft Online Community Support 
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> 
> 

It's my pleasure. David.
You are welcome.

Sincerely,
Wen Yuan
Microsoft Online Community Support 
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.