Greetings, I often create output set elements for use with the wordfly integration using the TR_QUERY_ELEMENT_GROUP and TR_QUERY_ELEMENT tables. I am trying to use the following code in the element group table which runs in SSMS, but when I try to apply the output set to a list in tess, I get an error message that says "Incorrect syntax near the keyword 'AND'. Invalid usage of the option Next in the FETCH statement. Incorrect syntax near the keyword 'AND'. Incorrect syntax near the keyword 'AND'." Does this message provide any information that could help to troubleshoot the issue? I have tried everything I can think of to no avail...any suggestions would be appreciated.
(SELECT DISTINCT customer_no,
CASE WHEN customer_no IN (SELECT DISTINCT customer_no
FROM T_TICKET_HISTORY
WHERE perf_no = 7429)
THEN 'Prepay'
WHEN customer_no IN (SELECT DISTINCT customer_no
FROM TX_CUST_MEMBERSHIP
WHERE cur_record = 'Y' AND current_status = 2 AND memb_level IN ('L20','L30','L40','L50','L55','L60','L70','L80') AND memb_org_no = 2)
THEN 'Patron'
WHERE perf_no IN (7399,7516))
THEN 'Member'
ELSE 'Non-Member'
END AS segment
FROM T_CUSTOMER)
Joshua Peters said:"Incorrect syntax near the keyword 'AND'
Does the block of code starting "
SELECT DISTINCT customer_no
FROM TX_CUST_MEMBERSHIP "
...work by itself, without parentheses, in SSMS?
Hi Chris,
Yes. All the sub queries work individually and the entire block works as a whole in SSMS. It is only when I try to use it as an output set element that the error pops up.
Joshua Peters said:All the sub queries work individually and the entire block works as a whole in SSMS. It is only when I try to use it as an output set element that the error pops up.
I'd open a ticket with support, then. The error would appear to be generated by Tess itself when it tries to exec your code. It has happened now and then, particularly in Extractions, in my experience.