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.
HI, Joshua,
Wondering if you ever figured this out.
I'm running into the same problem (with a different query.) I originally thought my problem was some sort of conflict with however my query was being embedded, and I tried creating it as a view to keep things simpler in the TR_QUERY_ELEMENT_GROUP table. But I'm running into this error even though the view itself does exactly what I'm looking for.
Yes and no. I was able to make it work by adding "segment IS NOT NULL" in the "Data Where" column of the table. I am not fully sure why this was necessary, I can guess, but it doesn't full make sense from my understanding of SQL logic. Regardless, it worked. Hopefully you can use the same concept on your code.
I worked with Jon Leland from Tessitura on this today, and he thinks we've uncovered a bug in the system. When the master query for the output is being built an erroneous "AND" is being inserted, so that the final query has something like "where blah blah and and blah blah" which obviously messes everything up. He wonders if this is being triggered by the code checking for a "null" in the where clause field, but not for a blank value, which would also require omitting the extra AND.
We have an open ticket with Tess about this (600918) and Mae Linden is looking to see if this correlates with a known defect. But the workaround (as you sort of found) is to just add a dummy where clause of any kind to the where clause field - I did 1=1 and this solved the problem. Your "segment is not null" also worked, but was more specific to your query. It really just needs to be anything that will return a valid true boolean after the bogus and.
Hopefully this bug gets squashed soon!
That makes so much more sense. Thank you for updating me on your discovery!