NULL GUID fields in a Nested Query

[Guest]

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

 

[WenYuan Wang [MSFT]]

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.

 

[Guest]

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]]

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.