We use TN6.0.1 with MS SQL server database. We have configured few Custom Attributes and those are captured by TN. When searched for specific value of Custom Attribute, the record though is not returned. I checked the query that TN use to get the results and found that it includes a Custom Attribute listed next to the attribute chosen in the where condition. I dont know, why it happens.
Query extracted from TN is below. My intention is to search for specific OrderNo. But it includes OrderDate (bda_0.AttributeID = ‘c0a8f8f7f70d759e00009a55’), another Custom Attribute in the select list and also in where condition.
SELECT
d.DocID,
d.DocTimestamp,
t.TypeName,
s.CorporationName AS SenderCorp,
s.OrgUnitName AS SenderUnit,
r.CorporationName AS ReceiverCorp,
r.OrgUnitName AS ReceiverUnit,
d.RoutingStatus,
d.UserStatus,
d.NativeID,
d.GroupID,
d.ConversationID,
bda_0.StringValue AS “OrderDate”,
bda_1.StringValue AS “OrderNo”
FROM
BizDocTypeDef t,
Partner s,
Partner r,
{oj BizDoc d
LEFT OUTER JOIN BizDocAttribute bda_1 ON d.DocID = bda_1.DocID AND bda_1.AttributeID = ‘c0a8f8f8f70d0b0500044413’},
BizDocAttribute bda_0
WHERE
d.DocTypeID = t.TypeID AND
d.SenderID = s.PartnerID AND
d.ReceiverID = r.PartnerID AND
d.DocID = bda_0.DocID AND bda_0.AttributeID = ‘c0a8f8f7f70d759e00009a55’ AND
d.DocTimestamp > {ts ‘2004-06-01 00:00:00.0’} AND
d.DocTimestamp < {ts ‘2004-07-01 00:00:00.0’} AND
bda_0.StringValue = ‘04/PO/1001’
The problem is occurring because you are using to custom attributes in your result set. Please remove the Order date field from ‘Selected Attributes’ list in the ‘Detail View’ tab. Then it should be ok. The error is because 2 joins cannot be performed in the same query.
-Rajesh Rao
Hello Krishnan,
The problem is occurring because you are using to custom attributes in your result set. Please remove the Order date field from ‘Selected Attributes’ list in the ‘Detail View’ tab. Then it should be ok. The error is because 2 joins cannot be performed in the same query.
I did not select OrderDate attribute in ‘Detail View’ Tab. I noticed that TN takes the attribute that is listed one above the selected attribute. Hence fails.
In the attached image you can see that the chosen attribute is POC_PONo and search value set for this ‘QCE-04J060-09-GO’. But the result with OrderNo having the search value whose POC_PONo is empty is returned.
I was not talking of the Custom Criteria tab but the Detail View tab, where I am pretty much sure you have selected ‘Order Date’. Please remove that and it will work.
In Detail View I have not selected ‘OrderDate’ when it failed.
A point to note; OrderDate and OrderNo are extracted from XML documents as defined in “Document Type Details”. Search for these attributes fails.
But, custom attribute values extracted from EDI documents are shown when searched. For EDI documents, since we cannot configure extraction of values using “Document Type Details”, value is extracted and updated to document using “wm.tn.doc:updateAttributes”. This field show up when searched.
